MySQL DAL questions

[Dave S]

Trying to translate someone else's MySQL CREATE TABLE into a define_table, which of course means I need to know what to do with 2 left shoes.

1) Can I translate a BIT field as a 'boolean'?

2) For legacy tables, does a keyed table work when the ID-like field on the MySQL side is a VARCHAR marked as the PRIMARY KEY, and there is UNIQUE INDEX set based on that field?

Thanks.

/dps

[Dave S]

Also, mapping BIGINT to bigint seems straightforward enough, but do TINYINT, SMALLINT, and INT all map to integer?

/dps
 

[Niphlod]

if you need to access legacy tables, all you need - effectively - is not creating the tables: just parse the results to proper python-corresponding types. Same thing applies with inserting/updating: TINYINT, SMALLINT, INT are just integers...with a "forced" lower/upper limit. Those translates to a proper model as 

Field('thefield', 'integer', requires=IS_INT_IN_RANGE(...,....))

but that only helps you if you need to do validation before inserting. Python will manage all of those as integers anyways.

As for the primary key, as long it's unique, everything works, EXCEPT something at reaaaally high level as the grid. 

Another thing to count for is that web2py assumes a "modern" model in which you CAN'T choose the PK beforehand (this maps to an autoincrementing integer usually): using db.thetable.insert(...) could be troublesome in some edge cases.

[Dave S]

On Wednesday, August 3, 2016 at 2:09:09 PM UTC-7, Niphlod wrote:

if you need to access legacy tables, all you need - effectively - is not creating the tables: just parse the results to proper python-corresponding types. Same thing applies with inserting/updating: TINYINT, SMALLINT, INT are just integers...with a "forced" lower/upper limit. Those translates to a proper model as 

Field('thefield', 'integer', requires=IS_INT_IN_RANGE(...,....))

but that only helps you if you need to do validation before inserting. Python will manage all of those as integers anyways.

As for the primary key, as long it's unique, everything works, EXCEPT something at reaaaally high level as the grid. 

Another thing to count for is that web2py assumes a "modern" model in which you CAN'T choose the PK beforehand (this maps to an autoincrementing integer usually): using db.thetable.insert(...) could be troublesome in some edge cases.

Thanks for the clarification.  I'm hoping to avoid doing inserts, and instead just make queries as a restful front for the data.  Consumer rather than provider.

/dps

[Dave S]

On Wednesday, August 3, 2016 at 3:59:37 PM UTC-7, Dave S wrote:

[...]

Thanks for the clarification.  I'm hoping to avoid doing inserts, and instead just make queries as a restful front for the data.  Consumer rather than provider.

Uh, oh ... that table name includes '.' ; does that mean parse_as_rest() won't be able to handle the pattern and URL?

/dps
 

[Massimo Di Pierro]

The raw names can include "." but the web2py names cannot possibly contain a "." in the name.

[Dave S]

On Saturday, August 6, 2016 at 12:12:45 AM UTC-7, Massimo Di Pierro wrote:

The raw names can include "." but the web2py names cannot possibly contain a "." in the name.

Yes, I'm trying the rname feature.  At the moment, I'm still in the "setting the table" stage, but I hope to get a URL to the real DB soon, so I can test how well I respond to coaching.

(I'm not fond of how the DB was set up, but I didn't get asked to design it.)

/dps