Syntax error with SQLite3 and PyDAL

[MaxMorais]

Hi All!

I have a very silly model, and I'm getting the below traceback

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/base.py", line 174, in __call__
    obj = super(MetaDAL, cls).__call__(*args, **kwargs)
  File "esquema/lds.py", line 70, in __init__
    self.define_schema("Schema", "Entity")
  File "esquema/lds.py", line 93, in define_schema
    obj = cls(self)
  File "esquema/lds.py", line 219, in __init__
    self.define_table()
  File "esquema/lds.py", line 238, in define_table
    primarykey=["name"]
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/base.py", line 834, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/base.py", line 873, in lazy_define_table
    polymodel=polymodel)
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py", line 482, in create_table
    self.create_sequence_and_triggers(query, table)
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py", line 1366, in create_sequence_and_triggers
    self.execute(query)
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py", line 1385, in execute
    return self.log_execute(*a, **b)
  File "/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py", line 1379, in log_execute
    ret = self.get_cursor().execute(command, *a[1:], **b)
sqlite3.OperationalError: near "parent_name": syntax error
>>> pm()
> /home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py(1379)log_execute()
-> ret = self.get_cursor().execute(command, *a[1:], **b)
(Pdb) query
*** NameError: name 'query' is not defined
(Pdb) up
> /home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py(1385)execute()
-> return self.log_execute(*a, **b)
(Pdb) a
self = <pydal.adapters.sqlite.SQLiteAdapter object at 0x7f71e58ca650>
a = (u'CREATE TABLE Entity(\n    name CHAR(512) NOT NULL UNIQUE,\n    parent CHAR(512), CONSTRAINT  "FK_Entity_parent__constraint" FOREIGN KEY (parent) REFERENCES Schema (name) ON DELETE CASCADE,\n    parent_name CHAR(512),\n    parent_entity CHAR(512),\n    idx INTEGER NOT NULL DEFAULT 1,\n    label CHAR(512),\n    entityname CHAR(512),\n    entitytype CHAR(512),\n    required CHAR(1),\n    is_index CHAR(1),\n    is_unique CHAR(1),\n    options TEXT,\n    def_val TEXT,\n    min DOUBLE,\n    max DOUBLE,\n    readonly CHAR(1),\n    compute CHAR(512),\n    dependsof CHAR(512),\n    description TEXT,\n    PRIMARY KEY(name)) ;',)
b = {}
(Pdb) 

For replicate this error, you only need define this schema:

db = DAL('sqlite://db.sqlite')
db.define_table('Schema',
  Field('name', unique=True, notnull=True),
  Field('type'),
  Field('custom', 'boolean', default=False),
  Field('description', notnull=True),
  primarykey=['name']
)

db.define_table('Entity', 
  Field('name', unique=True, notnull=True),
  Field("parent", "reference Schema", notnull=True),
  Field("parent_name", notnull=True),
  Field("parent_entity", notnull=True),
  Field("idx", "integer", notnull=True, default=1)  
  primarykey=['name']
)

[niphlod]

straight from the book (legacy database section)
"""
Referencing fields must use the reference tablename.fieldname format.
"""

[MaxMorais]

@niphlod in this case it's not a legacy database!

[BuhtigithuB]

Yes it is, how do you want pydal to increment your "name" primary key?

Richard

On Tue, Dec 1, 2015 at 4:20 PM, Maxwell [email protected] wrote:

@niphlod https://github.com/niphlod in this case it's not a legacy
database!

—
Reply to this email directly or view it on GitHub
#314 (comment).

[MaxMorais]

@BuhtigithuB I dont want, I'll define it, before insert the record!

[BuhtigithuB]

@maxwell,

Can you come over web2py user mailing-list (google group)... I think your
issue is more a comprehension one then a techinical one... Github issue is
supposed to be for technical/programmation issue...

web2py is the project that lead to pyDAL and where most user of pyDAL can
help you.

Regards

Richard

On Tue, Dec 1, 2015 at 4:33 PM, Maxwell [email protected] wrote:

@BuhtigithuB https://github.com/BuhtigithuB I dont want, I'll define
it, before insert the record!

—
Reply to this email directly or view it on GitHub
#314 (comment).

[MaxMorais]

@BuhtigithuB, I'm a member of Web2py forums, from Brazil and Internation, have 15 days that I posted my doubts in this foruns, whithout success!

For me this issue is clear around the behavior of PyDAL, how I explained in the web2py forums, the SQL is fully valid, but the statement is bad positioned.

https://groups.google.com/forum/#!topic/web2py/uhBIrbemKKI
https://groups.google.com/forum/#!topic/web2py-users-brazil/QJmFXshU7l4

[niphlod]

you didn't reply either (at least in the default group). Legacy or not legacy, the same limitation applies: you need to use the reference tablename.fieldname notation.

[MaxMorais]

@niphlod the issue is, in the generated SQL, using table.fieldname or not using it the statement is wrong!
This is the SQL generated by PyDAL

CREATE TABLE Entity(
    name CHAR(512) NOT NULL UNIQUE,
    parent CHAR(512), CONSTRAINT  "FK_Entity_parent__constraint" FOREIGN KEY (parent) REFERENCES Schema (name) ON DELETE CASCADE,
    parent_name CHAR(512),
    parent_entity CHAR(512),
    idx INTEGER NOT NULL DEFAULT 1,
    label CHAR(512),
    entityname CHAR(512),
    entitytype CHAR(512),
    required CHAR(1),
    is_index CHAR(1),
    is_unique CHAR(1),
    options TEXT,
    def_val TEXT,
    min DOUBLE,
    max DOUBLE,
    readonly CHAR(1),
    compute CHAR(512),
    dependsof CHAR(512),
    description TEXT,
    PRIMARY KEY(name)) ;

This is the manually edited SQL that works

CREATE TABLE Entity(
    name CHAR(512) NOT NULL UNIQUE,
    parent CHAR(512),
    parent_name CHAR(512),
    parent_entity CHAR(512),
    idx INTEGER NOT NULL DEFAULT 1,
    label CHAR(512),
    entityname CHAR(512),
    entitytype CHAR(512),
    required CHAR(1),
    is_index CHAR(1),
    is_unique CHAR(1),
    options TEXT,
    def_val TEXT,
    min DOUBLE,
    max DOUBLE,
    readonly CHAR(1),
    compute CHAR(512),
    dependsof CHAR(512),
    description TEXT,
    PRIMARY KEY(name),
    CONSTRAINT  "FK_Entity_parent__constraint" FOREIGN KEY (parent) REFERENCES Schema (name) ON DELETE CASCADE)
) ;

The CONSTRAINT statement is being generated AT the middle of the SQL, but It should be generated at the end, or should be generated whithout the ,;

[ourway]

Any updates?

[MaxMorais]

I deprecated pydal in favor or peewee, since I could not able to get it working in my project!

[niphlod]

@gi0baro : has this been fixed ?

[gi0baro]

@niphlod nope.

[tritone11]

@gi0baro any update?

[ourway]

is this bug solved?