orm missing features (features request)

[Vsevolod Balashov]

in gluon.sql i can`t find some useful table definion features

1. NOT NULL
SQLField('field_name', required = True)
i`m expect result SQL like field_name <FIELD TYPE> NOT NULL

2. UNIQUE (single field)
code like this SQLField('field_name', unique = True)
may produce SQL:
field_name <FIELD TYPE> UNIQUE
it`s applicable for all sql dialects

3. no way for index, multiple field unique and other costraint
definion.
may be just clone it from SQLAlchemy?
http://www.sqlalchemy.org/docs/04/metadata.html#metadata_constraints

4. schema... meine liebe postgres schema =)

this code SQLTable('schema.table', ...
worked if i comment gluon.sql.cleanup body. easy changes - enable dot.
alternative syntax are possigle SQLTable(table = 'table', schema =
'schema'

postgres, oracle, mysql support schema. sqlite... just add to table
name it.

please upgrade it from education to production. big thanx

[mdipierro]

I can add those but I am not sure in which order

NOT NULL and UNIQUE

go in CREATE TABLE in the different dialects. Can you point me to some
examples?

I did not get the schema part. What are you asking?

Massimo

On Jan 15, 2:58 pm, Vsevolod Balashov <vsevolod.balas...@gmail.com>
wrote:

> in gluon.sql i can`t find some useful table definion features
>
> 1. NOT NULL
> SQLField('field_name', required = True)
> i`m expect result SQL like field_name <FIELD TYPE> NOT NULL
>
> 2. UNIQUE (single field)
> code like this SQLField('field_name', unique = True)
> may produce SQL:
> field_name <FIELD TYPE> UNIQUE
> it`s applicable for all sql dialects
>
> 3. no way for index, multiple field unique and other costraint
> definion.

> may be just clone it from SQLAlchemy?http://www.sqlalchemy.org/docs/04/metadata.html#metadata_constraints

[Horst]

On Jan 17, 2:19 am, mdipierro <mdipie...@cs.depaul.edu> wrote:

> I did not get the schema part. What are you asking?

"schemas" are namespaces in Postgresql
They are very handy to prevent namespace conflicts

One way to implement it would be to ignore anything prior to a period
in a tablename for backends not supporting namespaces (schemas),
eg table myapp.users would be table "users", schema "myapp" in
postgres, but simply table "users" or better "myapp_users" in sqlite.

OTOH, lots of work for probably little benefit in the typical web app
scenario

Horst

[voltron]

Hmm, I´m not sure about schemas, I read many discussions about this on
the net, many came to the conclusion that this permits not portable
code and should be avoided because not every database type implements
schemas. I really like your other suggestions though.

I would also like to see a database implementation of "onupdate" as in
SQLAlchemy.

[Vsevolod Balashov]

Hi!

sqlite, mysql, postgres, oracle pass these commands

CREATE TABLE test_1( a INT NOT NULL UNIQUE );
CREATE TABLE test_2( a INT, UNIQUE( a ) );
CREATE TABLE test_3( a INT NOT NULL, UNIQUE( a ) );
CREATE TABLE test_4( a INT, b INT, UNIQUE( a, b ) );

you may read about CREATE TABLE dialects:

http://www.sqlite.org/lang_createtable.html
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://www.postgresql.org/docs/8.3/static/sql-createtable.html
http://download.oracle.com/docs/cd/B19188_01/doc/B15917/sqcmd.htm#sthref478
http://download.oracle.com/docs/cd/B19188_01/doc/B15917/sqcmd.htm#i1005656

shema... from orm side shema like as dot in table name. a quick
example:

=> CREATE TABLE users (id serial PRIMARY KEY, nick VARCHAR NOT NULL
UNIQUE);
=> CREATE SCHEMA blog;
=> CREATE SCHEMA forum;
=> CREATE TABLE blog.comments(user_id INT REFERENCES users (id), body
TEXT);
=> CREATE TABLE forum.comments (user_id INT REFERENCES users (id),
body TEXT, posted DATE);

=> SELECT body, nick FROM users, blog.comments WHERE users.id =
blog.comments.user_id;

when i write

db.define_table('blog.comments', SQLField('body'))

i get exception with traceback

1. Traceback (most recent call last):
2. File "/home/sevkin/web2py/gluon/restricted.py", line 65, in
restricted
3. exec ccode in environment
4. File "applications/welcome/models/db.py", line 4, in
<module>
5. db.define_table('blog.comments', SQLField('body'))
6. File "/home/sevkin/web2py/gluon/sql.py", line 294, in
define_table
7. tablename=cleanup(tablename)
8. File "/home/sevkin/web2py/gluon/sql.py", line 144, in
cleanup
9. raise Exception, 'only [0-9a-zA-Z_] allowed in table and
field names'
10. Exception: only [0-9a-zA-Z_] allowed in table and field names

I`m think, "schema support" is just pass dot in table name.

code, enable it may look like this:

def define_table(self,tablename,*fields,**args):
...
# tablename = cleanup(tablename)
tablename=cleanuptable(tablename)
if self._dbname == 'sqlite':
tablename = tablename.replace('.', '_')
...

where cleanuptable pass <table> or <schema>.<table>

also please think about indexes

[Massimo Di Pierro]

Thank you. I will try do this over the week-end.

Massimo

[Massimo Di Pierro]

Thank you this was useful:

NOT NULL and UNIQUE are now options in v1.18.3beta

Indices are a mess because they affect migrations. It is not easy and
it will not in 1.18. Perhaps in v1.19.

I think it may be confusing to some users to have a . in the table
name which is then automatically replaced by _.

Nevertheless I think it would be good practice to have all table
names preceded by something like <schema>_ and I will say that in the
documentation I am working on.

Massimo

On Jan 17, 2008, at 12:54 PM, Vsevolod Balashov wrote:

>
> Hi!
>
> sqlite, mysql, postgres, oracle pass these commands
>
> CREATE TABLE test_1( a INT NOT NULL UNIQUE );
> CREATE TABLE test_2( a INT, UNIQUE( a ) );
> CREATE TABLE test_3( a INT NOT NULL, UNIQUE( a ) );
> CREATE TABLE test_4( a INT, b INT, UNIQUE( a, b ) );
>
> you may read about CREATE TABLE dialects:
>
> http://www.sqlite.org/lang_createtable.html
> http://dev.mysql.com/doc/refman/5.0/en/create-table.html
> http://www.postgresql.org/docs/8.3/static/sql-createtable.html

> http://download.oracle.com/docs/cd/B19188_01/doc/B15917/
> sqcmd.htm#sthref478
> http://download.oracle.com/docs/cd/B19188_01/doc/B15917/
> sqcmd.htm#i1005656
>

[voltron]

Hello Vsevollod!

I recognized your name from the Storm ORM forum :-) You are the one
that wrote "middlestorm" are´nt you :-)

[Vsevolod Balashov]

Yes, I`m ;)

middlestorm is a my way for escaping from sqlalhiemy in pylons
projects.
more details http://vsevolod.balashov.name/middlestorm/ now on russian
only, i`m sorry.. but you may see code fragments as usage examples

[Massimo Di Pierro]

I like storm. You advice on how to improve the web2py orm would be
very much appreciated.

would you be willing to run some benchmark code of web2py vs storm vs
sqlalchemy?
I have the benchmark code.

Massimo

[voltron]

Hi Massimo, we thought of the same thing :-)

On Jan 20, 10:08 pm, Massimo Di Pierro <mdipie...@cs.depaul.edu>
wrote:

> I like storm. You advice on how to improve the web2py orm would be
> very much appreciated.
>
> would you be willing to run some benchmark code of web2py vs storm vs
> sqlalchemy?
> I have the benchmark code.
>
> Massimo
>
> On Jan 20, 2008, at 2:00 PM, Vsevolod Balashov wrote:
>
>
>
> > Yes, I`m ;)
>
> > middlestorm is a my way for escaping from sqlalhiemy in pylons
> > projects.

> > more detailshttp://vsevolod.balashov.name/middlestorm/now on russian

[Vsevolod Balashov]

Benchmarking? Why, if you have already done so?
I just looked at the size of the code.

SQLAlchemy - huge, Storm - small, gluon.sql - tiny.

Today I mainly use Storm + Pylons, although I like the concept web2py:
controller as module compared with Pylons controller as class.
I accidentally found web2py and watch for his development. I see
several problems that hinder its use in production, but on the whole
project is very interesting.

Damn! Google Translate better than my English.

On Jan 21, 12:08 am, Massimo Di Pierro <mdipie...@cs.depaul.edu>
wrote:

> I like storm. You advice on how to improve the web2py orm would be
> very much appreciated.
>
> would you be willing to run some benchmark code of web2py vs storm vs
> sqlalchemy?
> I have the benchmark code.
>
> Massimo
>
> On Jan 20, 2008, at 2:00 PM, Vsevolod Balashov wrote:
>
>
>
> > Yes, I`m ;)
>
> > middlestorm is a my way for escaping from sqlalhiemy in pylons
> > projects.

> > more detailshttp://vsevolod.balashov.name/middlestorm/now on russian

[Massimo Di Pierro]

You just game an idea....

link T to google translator!

Massimo

[voltron]

Hi Vsevolod!

Could you list some of the problems that you think would hinder Web2py
use in production, they might be solved quickly.



On Jan 22, 9:57 pm, Vsevolod Balashov <vsevolod.balas...@gmail.com>
wrote:

> Benchmarking? Why, if you have already done so?
> I just looked at the size of the code.
>
> SQLAlchemy - huge, Storm - small, gluon.sql - tiny.
>
> Today I mainly use Storm + Pylons, although I like the concept web2py:
> controller as module compared with Pylons controller as class.
> I accidentally found web2py and watch for his development. I see
> several problems that hinder its use in production, but on the whole
> project is very interesting.
>
> Damn! Google Translate better than my English.
>
> On Jan 21, 12:08 am, Massimo Di Pierro <mdipie...@cs.depaul.edu>
> wrote:
>
> > I like storm. You advice on how to improve the web2py orm would be
> > very much appreciated.
>
> > would you be willing to run some benchmark code of web2py vs storm vs
> > sqlalchemy?
> > I have the benchmark code.
>
> > Massimo
>
> > On Jan 20, 2008, at 2:00 PM, Vsevolod Balashov wrote:
>
> > > Yes, I`m ;)
>
> > > middlestorm is a my way for escaping from sqlalhiemy in pylons
> > > projects.

> > > more detailshttp://vsevolod.balashov.name/middlestorm/nowon russian