About my 'Let's dance' -comment - there is already opened issue
https://github.com/web2py/pydal/issues/314 (SQLite syntax error in case of
reference to keyed table)
I opened *one* <https://github.com/web2py/pydal/issues/336>yet about
reference to FK-field
On Sunday, March 13, 2016 at 3:51:59 AM UTC+3, Anthony wrote:
>
> You can submit an issue to the pyDAL repo.
>
> On Saturday, March 12, 2016 at 6:03:10 PM UTC-5, Val K wrote:
>>
>>
>> I've solved my problem - just additional condition
>> in dal\pydal\adapters\base.py:
>>
>> 298.
>> 299.
>> 300.
>> 301.
>> 302.
>> 303.
>>
>> 304.
>> 305.
>> 306.
>> 307.
>>
>>
>> # must be PK reference or unique
>> if rfield.type[:10] != 'reference ' and rfield.type[:14] !=
>> 'big-reference ' and \
>> getattr(rtable, '_primarykey', None) and rfieldname in
>> rtable._primarykey or \
>> rfield.unique:
>> ftype = types[rfield.type[:9]] % \
>> dict(length=rfield.length)
>>
>> # multicolumn primary key reference?
>> if not rfield.unique and len(rtable._primarykey)>1:
>> # then it has to be a table level FK
>> if rtablename not in TFK:
>>
>>
>> works fine!
>>
>>
>>
>>
>> On Sunday, March 13, 2016 at 12:17:06 AM UTC+3, Val K wrote:
>>>
>>> OK! Let's dance
>>>
>>> This doesn't work (with SQLite at least):
>>> db.define_table('a_tbl', Field('id', 'integer'), Field('name'),
>>> primarykey=['id'] ) # one keyed table
>>> db.define_table('b_tbl', Field('id', 'reference a_tbl.id'),
>>> Field('name'), primarykey=['id']) # another keyed table
>>>
>>> <class 'sqlite3.OperationalError'> near "name": syntax error
>>>
>>>
>>> sql.log:
>>> *CREATE TABLE b_tbl(*
>>> * id INTEGER, CONSTRAINT "FK_b_tbl_id__constraint" FOREIGN KEY (id)
>>> REFERENCES a_tbl (id) ON DELETE CASCADE,** name CHAR(512),**
>>> PRIMARY KEY(id)) ;*
>>>
>>>
>>> This doesn't work too:
>>> db.define_table('a_tbl', Field('id', 'integer'), Field('name'),
>>> primarykey=['id'] ) # one keyed table
>>> db.define_table('b_tbl', Field('id', db.a_tbl), Field('name'),
>>> primarykey=['id']) # another keyed table
>>> it produces the same sql.log and causes the same error - expected
>>>
>>>
>>> But this works!:
>>> db.define_table('a_tbl', Field('id', 'id'), Field('name') ) # not keyed
>>> table
>>> db.define_table('b_tbl', Field('id', 'reference a_tbl'), Field('name'),
>>> primarykey=['id']) # keyed table that references to not keyed table
>>>
>>>
>>> it produces another sql.log:
>>> CREATE TABLE b_tbl(
>>> id INTEGER REFERENCES a_tbl (id) ON DELETE CASCADE NOT NULL ,
>>> name CHAR(512),
>>> PRIMARY KEY(id)) ;
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Saturday, March 12, 2016 at 8:54:37 PM UTC+3, Anthony wrote:
>>>>
>>>> Not sure if it's a bug, but the book does say:
>>>>
>>>> - Keyed tables can only reference other keyed tables.
>>>> - Referencing fields must use the reference tablename.fieldname
>>>> format.
>>>>
>>>> Anthony
>>>>
>>>> On Saturday, March 12, 2016 at 4:52:53 AM UTC-5, Val K wrote:
>>>>>
>>>>> Hi!
>>>>> I have a problem - look at example:
>>>>>
>>>>> db.define_table('a_tbl', Field('name'))
>>>>> db.define_table('b_tbl', Field('id', 'reference a_tbl')
>>>>> ,Field('name'), primarykey=['id'])
>>>>> db.define_table('c_tbl', Field('b_tbl_id', 'reference b_tbl')
>>>>> ,Field('name'))
>>>>>
>>>>>
>>>>> *c_tbl* definition causes an error (see below) because there is
>>>>> *b_tbl_id* field that references to *b_tbl* that has *id* reference
>>>>> to *a_tbl*
>>>>>
>>>>> Is this a bug?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ----------------------------------------------------------------------------------------------------------
>>>>>
>>>>> <type 'exceptions.KeyError'> 'foreign_key'
>>>>>
>>>>> web2py™Version 2.13.4-stable+timestamp.2015.12.26.04.59.39PythonPython
>>>>> 2.7.9:
>>>>>
>>>>>
>>>>> *File D:\web2py_last\web2py\gluon\packages\dal\pydal\adapters\base.py
>>>>> in create_table at line 303*
>>>>> Function argument list
>>>>>
>>>>> (self=<pydal.adapters.sqlite.SQLiteAdapter object>, table=<Table c_tbl
>>>>> (id,name)>, migrate=True, fake_migrate=False, polymodel=None)
>>>>> Code listing
>>>>>
>>>>> 298.
>>>>> 299.
>>>>> 300.
>>>>> 301.
>>>>> 302.
>>>>> 303.
>>>>>
>>>>> 304.
>>>>> 305.
>>>>> 306.
>>>>> 307.
>>>>>
>>>>>
>>>>> # must be PK reference or unique
>>>>> if getattr(rtable, '_primarykey', None) and rfieldname in
>>>>> rtable._primarykey or \
>>>>> rfield.unique:
>>>>> ftype = types[rfield.type[:9]] % \
>>>>> dict(length=rfield.length)
>>>>>
>>>>> # multicolumn primary key reference?
>>>>> if not rfield.unique and len(rtable._primarykey)>1:
>>>>> # then it has to be a table level FK
>>>>> if rtablename not in TFK:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
