{"action":"created","issue":{"url":"https://api.github.com/repos/web2py/pydal/issues/314","labels_url":"https://api.github.com/repos/web2py/pydal/issues/314/labels{/name}","comments_url":"https://api.github.com/repos/web2py/pydal/issues/314/comments","events_url":"https://api.github.com/repos/web2py/pydal/issues/314/events","html_url":"https://github.com/web2py/pydal/issues/314","id":119668951,"number":314,"title":"Syntax error with SQLite3 and PyDAL","user":{"login":"MaxMorais","id":1288676,"avatar_url":"https://avatars.githubusercontent.com/u/1288676?v=3","gravatar_id":"","url":"https://api.github.com/users/MaxMorais","html_url":"https://github.com/MaxMorais","followers_url":"https://api.github.com/users/MaxMorais/followers","following_url":"https://api.github.com/users/MaxMorais/following{/other_user}","gists_url":"https://api.github.com/users/MaxMorais/gists{/gist_id}","starred_url":"https://api.github.com/users/MaxMorais/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MaxMorais/subscriptions","organizations_url":"https://api.github.com/users/MaxMorais/orgs","repos_url":"https://api.github.com/users/MaxMorais/repos","events_url":"https://api.github.com/users/MaxMorais/events{/privacy}","received_events_url":"https://api.github.com/users/MaxMorais/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":8,"created_at":"2015-12-01T08:48:00Z","updated_at":"2015-12-02T14:35:14Z","closed_at":null,"body":"Hi All!\r\n\r\nI have a very silly model, and I'm getting the below traceback\r\n\r\n```python\r\nTraceback (most recent call last):\r\n  File \"<stdin>\", line 1, in <module>\r\n  File \"/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/base.py\", line 174, in __call__\r\n    obj = super(MetaDAL, cls).__call__(*args, **kwargs)\r\n  File \"esquema/lds.py\", line 70, in __init__\r\n    self.define_schema(\"Schema\", \"Entity\")\r\n  File \"esquema/lds.py\", line 93, in define_schema\r\n    obj = cls(self)\r\n  File \"esquema/lds.py\", line 219, in __init__\r\n    self.define_table()\r\n  File \"esquema/lds.py\", line 238, in define_table\r\n    primarykey=[\"name\"]\r\n  File \"/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/base.py\", line 834, in define_table\r\n    table = self.lazy_define_table(tablename,*fields,**args)\r\n  File \"/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/base.py\", line 873, in lazy_define_table\r\n    polymodel=polymodel)\r\n  File \"/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py\", line 482, in create_table\r\n    self.create_sequence_and_triggers(query, table)\r\n  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\r\n    self.execute(query)\r\n  File \"/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py\", line 1385, in execute\r\n    return self.log_execute(*a, **b)\r\n  File \"/home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py\", line 1379, in log_execute\r\n    ret = self.get_cursor().execute(command, *a[1:], **b)\r\nsqlite3.OperationalError: near \"parent_name\": syntax error\r\n>>> pm()\r\n> /home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py(1379)log_execute()\r\n-> ret = self.get_cursor().execute(command, *a[1:], **b)\r\n(Pdb) query\r\n*** NameError: name 'query' is not defined\r\n(Pdb) up\r\n> /home/maxwell/Development/github.com/maxmorais/esquema-env/lib/python2.7/site-packages/pydal/adapters/base.py(1385)execute()\r\n-> return self.log_execute(*a, **b)\r\n(Pdb) a\r\nself = <pydal.adapters.sqlite.SQLiteAdapter object at 0x7f71e58ca650>\r\na = (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)) ;',)\r\nb = {}\r\n(Pdb) \r\n```\r\n\r\nFor replicate this error, you only need define this schema:\r\n\r\n```python\r\ndb = DAL('sqlite://db.sqlite')\r\ndb.define_table('Schema',\r\n  Field('name', unique=True, notnull=True),\r\n  Field('type'),\r\n  Field('custom', 'boolean', default=False),\r\n  Field('description', notnull=True),\r\n  primarykey=['name']\r\n)\r\n\r\ndb.define_table('Entity', \r\n  Field('name', unique=True, notnull=True),\r\n  Field(\"parent\", \"reference Schema\", notnull=True),\r\n  Field(\"parent_name\", notnull=True),\r\n  Field(\"parent_entity\", notnull=True),\r\n  Field(\"idx\", \"integer\", notnull=True, default=1)\t\r\n  primarykey=['name']\r\n)\r\n```"},"comment":{"url":"https://api.github.com/repos/web2py/pydal/issues/comments/161317250","html_url":"https://github.com/web2py/pydal/issues/314#issuecomment-161317250","issue_url":"https://api.github.com/repos/web2py/pydal/issues/314","id":161317250,"user":{"login":"MaxMorais","id":1288676,"avatar_url":"https://avatars.githubusercontent.com/u/1288676?v=3","gravatar_id":"","url":"https://api.github.com/users/MaxMorais","html_url":"https://github.com/MaxMorais","followers_url":"https://api.github.com/users/MaxMorais/followers","following_url":"https://api.github.com/users/MaxMorais/following{/other_user}","gists_url":"https://api.github.com/users/MaxMorais/gists{/gist_id}","starred_url":"https://api.github.com/users/MaxMorais/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MaxMorais/subscriptions","organizations_url":"https://api.github.com/users/MaxMorais/orgs","repos_url":"https://api.github.com/users/MaxMorais/repos","events_url":"https://api.github.com/users/MaxMorais/events{/privacy}","received_events_url":"https://api.github.com/users/MaxMorais/received_events","type":"User","site_admin":false},"created_at":"2015-12-02T14:35:13Z","updated_at":"2015-12-02T14:35:13Z","body":"@niphlod the issue is, in the generated SQL, using `table.fieldname` or not using it the statement is wrong!\r\n This is the SQL generated by PyDAL\r\n\r\n```SQL\r\nCREATE TABLE Entity(\r\n    name CHAR(512) NOT NULL UNIQUE,\r\n    parent CHAR(512), CONSTRAINT  \"FK_Entity_parent__constraint\" FOREIGN KEY (parent) REFERENCES Schema (name) ON DELETE CASCADE,\r\n    parent_name CHAR(512),\r\n    parent_entity CHAR(512),\r\n    idx INTEGER NOT NULL DEFAULT 1,\r\n    label CHAR(512),\r\n    entityname CHAR(512),\r\n    entitytype CHAR(512),\r\n    required CHAR(1),\r\n    is_index CHAR(1),\r\n    is_unique CHAR(1),\r\n    options TEXT,\r\n    def_val TEXT,\r\n    min DOUBLE,\r\n    max DOUBLE,\r\n    readonly CHAR(1),\r\n    compute CHAR(512),\r\n    dependsof CHAR(512),\r\n    description TEXT,\r\n    PRIMARY KEY(name)) ;\r\n```\r\n\r\nThis is the manually edited SQL that works\r\n\r\n```SQL\r\nCREATE TABLE Entity(\r\n    name CHAR(512) NOT NULL UNIQUE,\r\n    parent CHAR(512),\r\n    parent_name CHAR(512),\r\n    parent_entity CHAR(512),\r\n    idx INTEGER NOT NULL DEFAULT 1,\r\n    label CHAR(512),\r\n    entityname CHAR(512),\r\n    entitytype CHAR(512),\r\n    required CHAR(1),\r\n    is_index CHAR(1),\r\n    is_unique CHAR(1),\r\n    options TEXT,\r\n    def_val TEXT,\r\n    min DOUBLE,\r\n    max DOUBLE,\r\n    readonly CHAR(1),\r\n    compute CHAR(512),\r\n    dependsof CHAR(512),\r\n    description TEXT,\r\n    PRIMARY KEY(name),\r\n    CONSTRAINT  \"FK_Entity_parent__constraint\" FOREIGN KEY (parent) REFERENCES Schema (name) ON DELETE CASCADE)\r\n) ;\r\n```\r\n\r\nThe `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 `, `;"}}