MySQL migration issues with DAL outside of web2py

[Jim S]

Hi - I use MySQL for my database.  In my production environment I'm specifying the following:

db = DAL(infoCenterUtil.getDalString(), migrate=False,
 migrate_enabled=False, bigint_id=True)

On my production machine I'm also trying to use the DAL outside of web2py with the following:

import sys
sys.path.append('c:/prod/web2py')
from gluon import DAL, Field
db = DAL('mysql://username:password@server/database',folder='c:/prod/web2py/applications/InfoCenter/databases', auto_import=True)

print db.tables

But, I get an empty list when I print db.tables.  On my test machine where all of my auto-migrations happen, it works fine.  

Am I barking up the wrong tree in thinking that migration has something to do with my problem (print db.tables give empty list).  If not, what am I doing wrong?  I don't want auto-migrations on my production box.  What is the proper way to have no migration on a machine, but allow for DAL outside of web2py?

-Jim

[Niphlod]

is your databases folder filled with the .table files relative to the tables ?

[Jim Steil]

It is empty.

--
 
 
 

[Niphlod]

auto_import scans the table files for tables. That's the whole point of not redefining models (because they are stored in table files that can be read).
Normal behaviour is:
DAL(..., migrate=True)
let it define tables, then
DAL(, migrate=False) #or migrate_enabled=False
so table files are never touched again, and can be imported.
PS: big_id isn't saved into table definitions, so you must specify that parameter also outside (and it's safe I guess turning migrations off also for external access).

To solve your problem, try one round of
DAL(..,migrate_enabled=False, fake_migrate_all=True)
fake_migrate_all will fake all table creations and generates the relative .table files (of course you must be sure that your table definitions are synced with your db structure)
From then on, you should be able to import them
DAL(...,migrate_enabled=False, big_id=True, auto_import=True, folder='...')

[Jim Steil]

Ok, I've got it now to where there are files in the databases directory, but still getting empty list for print db.tables

-Jim

--
 
 
 

[Jim Steil]

Found it.  I had inconsistent case specified in my database name.  When running in web2py it was infoCenter2, when running outside, I had infocenter2.  Changing to infoCenter2 caused it to work correctly.

Niphlod - Thanks for all the help.  I truly appreciate it.

-Jim

[Niphlod]

yep, that can cause troubles.
table files are named with an hash composed with the uri string, so you can import, let's say, table definitions for 10 dbs all in the same folder.
If table files were created with an uri, and you try to auto_import with another uri, web2py will "search" for the "wrong" filenames.

[Jim Steil]

Ok, now adding on to this situation.

In my db.py I have the following defined:

district = db.define_table('district',
Field('districtId', 'id', readable=False),
Field('district', length=5, required=True, unique=True),
Field('districtNumber', 'integer', required=True,
unique=True, label='District Number'),
Field('name', length=50, required=True, unique=True),
Field('salesmanId', db.auth_user, required=True,
label='District Manager'),
Field('includeInArAging', 'boolean', required=True,
label='Include in A/R Aging'),
Field('regionNumber', 'integer', label='Region Number'),
Field('manager',
compute=lambda id: '%s - %s' % (id.district,
db.auth_user(id.salesmanId).firstLast)),
format='%(manager)s')

district.salesmanId.represent = db.auth_user.first_name
district.district.requires = [IS_NOT_EMPTY(),
IS_NOT_IN_DB(db, 'district.district'),
IS_UPPER()]
district.districtNumber.requires = [IS_NOT_EMPTY(),
IS_NOT_IN_DB(db, 'district.districtNumber')]
district.name.requires = [IS_NOT_EMPTY(),
IS_NOT_IN_DB(db, 'district.name')]
district.salesmanId.requires = IS_IN_DB(db, db.auth_user,
'%(lastFirst)s',
zero='.choose.')
district._plural = 'Districts'

On my test machine I used a MySQL database hosted on an ubuntu box.

On my production machine (where I ran the fake_migrate last week) I'm using the same setup.  Looking in the databases directory for my app on each server I see that the field names are all lower case on my test machine, but mixed case on my production machine (where the fake_migrate was run).  I've attached the two .table files.  Is this an oversite on web2py's part or something I have setup wrong that has caused the case-confusion between my environments?

--
 
 
 

[Niphlod]

this is a question for Massimo, although ... we know your test is on ubuntu, but your production is on linux too ?

PS: I see a comment like this in DAL
# make sure all field names are lower case to avoid
# migrations because of case cahnge

so it's likely that those differencies won't trigger a migration.
The "issue" is: where the system defined tables "with camelcase" the fields are accessible by camelcase pointers only and where it is all lower they are accessible only with lower names ?
It would be an error to have a code working with
db.district.includeinaraging
in one instance and patch it to work on another instance to
db.district.includeInArAging

[Jim S]

It seems then like it converts everything to lower case in the .table file if you create it from scratch but if you run a fake_migrate then it doesn't convert to lower case.  Just appears (on the surface) that it is inconsistent behavior.  I've been a camel-case guy since my powerbuilder days back in the 90s and it's one habit I've yet to break.  And now I have a pile of code that using it so I'm kinda stuck with this app.  I know, shame on me.

Massimo, if you get the time could you weigh in on this?

-Jim

[Massimo Di Pierro]

Please open a ticket about this. I will fix it in the next couple of days.

[Jim S]

Submitted:  http://code.google.com/p/web2py/issues/detail?id=1126

-Jim