CAS and postgres

[Tito Garrido]

Somebody have problems using CAS and postgres?

When web2py tries to create the table user I'm getting the following
error message:

ERROR: syntax error at or near "user"
LINE 1: CREATE TABLE user(
^

********** Erro **********

ERROR: syntax error at or near "user"
SQL state: 42601
Caracter: 14

Thanks

Tito
--
Linux User #387870
____
_/_õ|__|
º[ .-.___.-._| . . . .
_(o).__(o).:_____

[Massimo Di Pierro]

Auch! I think 'user' is a keyword in postgres.

We/you may have to rewrite 'user' into 'cas_user' or something else
everywhere, unless there is a way to tell postgres to allow 'user'.

Sorry about this.

[yarko]

According to the Postgresql documentation, this is a SQL keyword in
SQL:92, SQL:1999 and SQL:2003

Check out http://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix.html


On Jun 12, 10:48 am, Massimo Di Pierro <mdipie...@cs.depaul.edu>
wrote:

[yarko]

Note this from Postgresql docs:

"The SQL standard will not define a key word that contains digits or
starts or ends with an underscore, so identifiers of this form are
safe against possible conflict with future extensions of the
standard."

On Jun 12, 2:52 pm, yarko <yark...@gmail.com> wrote:
> According to the Postgresql documentation, this is a SQL keyword in
> SQL:92, SQL:1999 and SQL:2003
>

> Check outhttp://www.postgresql.org/docs/8.3/interactive/sql-keywords-appendix....

[NetAdmin]

Note that user() is also a function in MySQL

[Massimo Di Pierro]

OK. we need to change this table name in cas.
I will try do this in a couple of days, unless somebody does it
before me.

Massimo

[yarko]

Would a convention such as "when a table conflicts with an SQL
reserved keyword, the orm will convert it to 'name_' for SQL" work?
Does anyone see any problems this might present?

Would be nice to (at the web2py / python level) just use whatever name
you want, and let the ORM translate.... to avoid SQL conflicts.
Just a thought.

[Tito Garrido]

I tried to change the name "user" but I got some errors.

I have changed in model/db.py and controller/cas.py. There are other
files that need to change?

Tito

[Massimo Di Pierro]

I have been opposed to adding database namespaces before because they
are not that transparent. Anyway, there may be value in them. Let me
check how difficult it would be to implement them.

Massimo

[Massimo Di Pierro]

there may be some references in the views/*/*.html

[Tito Garrido]

The error that I'm receiving:

Traceback (most recent call last):
File "/srv/web2py/gluon/restricted.py", line 62, in restricted
exec ccode in environment
File "/srv/web2py/applications/suporte/controllers/cas.py", line
182, in <module>
File "/srv/web2py/applications/suporte/controllers/cas.py", line 59, in login
insert_ticket(session)
File "/srv/web2py/applications/suporte/controllers/cas.py", line 36,
in insert_ticket
url=session.service,ctime=now)
File "/srv/web2py/gluon/sql.py", line 579, in insert
query=self._insert(**fields)
File "/srv/web2py/gluon/sql.py", line 555, in _insert
raise SyntaxError, 'invalid field name'
SyntaxError: invalid field name

--

[Massimo Di Pierro]

Try this!

[Tito Garrido]

Some problems trying to login:

Traceback (most recent call last):
File "/srv/web2py/gluon/restricted.py", line 62, in restricted
exec ccode in environment
File "/srv/web2py/applications/suporte/controllers/cas.py", line
182, in <module>

File "/srv/web2py/applications/suporte/controllers/cas.py", line 95,
in register
if form.accepts(request.vars,session) and \
File "/srv/web2py/gluon/html.py", line 132, in accepts
self.rec_accepts(vars)
File "/srv/web2py/gluon/html.py", line 137, in rec_accepts
if hasattr(c,'rec_accepts'): c.rec_accepts(vars)
File "/srv/web2py/gluon/html.py", line 137, in rec_accepts
if hasattr(c,'rec_accepts'): c.rec_accepts(vars)
File "/srv/web2py/gluon/html.py", line 137, in rec_accepts
if hasattr(c,'rec_accepts'): c.rec_accepts(vars)
File "/srv/web2py/gluon/html.py", line 137, in rec_accepts
if hasattr(c,'rec_accepts'): c.rec_accepts(vars)
File "/srv/web2py/gluon/html.py", line 371, in rec_accepts
value,errors=validator(value)
File "/srv/web2py/gluon/validators.py", line 143, in __call__
fetched=self.dbset("%s=%s AND %s<>%s" %
(fieldname,value_field,id_field,value_id)).select('count(*)')
File "/srv/web2py/gluon/sql.py", line 844, in select
r=response(query)
File "/srv/web2py/gluon/sql.py", line 840, in response
self._db._execute(query)
File "/srv/web2py/gluon/sql.py", line 322, in <lambda>
self._execute=lambda *a,**b: self._cursor.execute(*a,**b)
ProgrammingError: rro de sintaxe em ou próximo a "."
LINE 1: SELECT count(*) FROM user WHERE user.email='tito...'

On Fri, Jun 13, 2008 at 12:36 PM, Massimo Di Pierro

[Tito Garrido]

We need to change line 89 and 136 in /controllers/cas.py

there are user.mail entries... change to cas_user.mail

[Tito Garrido]

How can I force web2py to look for changes in database?
I've droped my database and I'm receiving an error that my table doesn't exist.

[Kyle Smith]

There's also a databases directory which contains database information in I believe a pickled state. If your trying to start over with a fresh db you can also just clean out that directory.

Kyle

[Tito Garrido]

Thanks!

[Massimo Di Pierro]

I reposted on appliances with the change. Thanks for fixing.

Since you are using this. Do you want to take over the maintenance of
CAS?

I will be happy if other people want to take over the maintenance of
various web2py apps (except KPAX since I am working on it right now).

Massimo

[Tito Garrido]

Sure... I'll try to make some improvements ;)
I has some problems with the size of the field in postgres, error when
I tried to login.
I've changed the length of the ticket fields. What length would be a good value?

On Mon, Jun 16, 2008 at 8:41 PM, Massimo Di Pierro

[Massimo Di Pierro]

On Jun 16, 2008, at 8:34 PM, Tito Garrido wrote:

>
> Sure... I'll try to make some improvements ;)
> I has some problems with the size of the field in postgres, error when
> I tried to login.
> I've changed the length of the ticket fields. What length would be
> a good value?

I do not recall but this is fixed by the key length. 64 to be safe.
Perhaps the key should just be a uuid.uuid4()

[Horst]

On Jun 17, 12:08 pm, Massimo Di Pierro <mdipie...@cs.depaul.edu>
wrote:

> I do not recall but this is fixed by the key length. 64 to be safe.  
> Perhaps the key should just be a uuid.uuid4()

In most modern database systems there is *NO* advantage specifying/
restricting field length - all it does is slow the database down a bit
since the field length restriction is handled as just another
constraint and subsequently checked.

Modern database systems are reasonably frugal with disk space and
almost always use variable field length storage - at least in Postgres
it is more efficient to use the type TEXT than using VARCHAR(n) - so
why restrict that field needlessly?

Horst

[voltron]

Interesting, I did not know that, so using TEXT instead of string
everywhere is an advantage?

[Massimo Di Pierro]

the don't restrict it.

[Massimo Di Pierro]

I am not sure this is true everywhere. Is there any document to back
this up.