The legacy database to Web2py conversion would be a great add-on. I
get the following errors when I tried this script on a database with
tables having primary key 'id' set to autoincrement.
C:\Program Files (x86)\web2py\scripts>extract_mysql_models.py
bio:b...@nrcf
Traceback (most recent call last):
File "C:\Program Files (x86)\web2py\scripts
\extract_mysql_models.py", line 106
, in <module>
print mysql(m.group(3),m.group(1),m.group(2))
File "C:\Program Files (x86)\web2py\scripts
\extract_mysql_models.py", line 82,
in mysql
table_name = re.search('CREATE TABLE .(\S+). \(',
sql_lines[0]).group(1)
AttributeError: 'NoneType' object has no attribute 'group'
Has anyone figured out how to get this script working?
Thanks,
S
On Sep 17, 10:25 am, ron_m <ron.mco...@gmail.com> wrote:
> Program mysqldump produces a line like this for an id field
>
> `id` int(11) NOT NULL AUTO_INCREMENT,
>
> and because there is no special case processing int becomes integer.
>
> It looks like a possible solution would be to recognise AUTO_INCREMENT
> and for that case over ride the int translation to 'integer' replacing
> with 'id' instead.
>
> I am running the database created by a set of model files through the
> script to test the result, not exactly a legacy database but a round-
> trip test is a good test too. In my case the id field should be
> dropped because it is auto generated. That brings the question if the
> field is named id and is AUTO_INCREMENT should it be dropped? If the
> source is a legacy database then that might be okay because the legacy
> use is consistent with the way web2py uses the id field.
>
> Another one that would be nice is the varchar(len) could be translated
> to 'string', length=len in the emitted Field constructor.
>
> Another problem I see is a boolean in the model becomes a char(1) in
> the db which becomes a string in the extract but maybe I am pushing
> the envelope since my source is a model to start with. Would it be
> better to recognisu a char(1) as a boolean? Possibly not.
>
> At this point the script provides an initial cut at a model.py file
> and then "some human intelligence" needs to be applied to finish the
> result. Of course it would be nice to have nirvana which would be a
> 100% accurate and complete translation with all possible Field()
> parameters filled in for a given database which requires a lot more
> work. I am willing to help out but my workload is high right now. Of
> course it should not crash as it does for Johann right now.
>
> If you would like to provide comments on what you think is most
> important I can help out.
>
> Ron
>
> On Sep 17, 6:24 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > I see a problem here:
>
> > Field('id','integer'),
>
> > should be
>
> > Field('id','id'),
>
> > On Sep 9, 12:01 am, ron_m <ron.mco...@gmail.com> wrote:
>
> > > On Sep 8, 1:30 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > > > So maybe tonight do you want me to go through the manual and find all
> > > > > the missing datatypes and try to add them to the map?
>
> > > > I would not stop you. ;-)
>
> > > Here is a replacement data_type_map, it was shuffled a bit to put like
> > > types together for easier maintenance. I didn't know what to do with
> > > the YEAR type so it is commented out. I am not sure the decimal type
> > > should be mapped to integer since the DAL accepts decimal(n,M) as a
> > > field type.
>
> > > data_type_map = dict(
> > > varchar = 'string',
> > > int = 'integer',
> > > integer = 'integer',
> > > tinyint = 'integer',
> > > smallint = 'integer',
> > > mediumint = 'integer',
> > > bigint = 'integer',
> > > float = 'double',
> > > double = 'double',
> > > char = 'string',
> > > decimal = 'integer',
> > > date = 'date',
> > > #year = 'date',
> > > time = 'time',
> > > timestamp = 'datetime',
> > > datetime = 'datetime',
> > > binary = 'blob',
> > > blob = 'blob',
> > > tinyblob = 'blob',
> > > mediumblob = 'blob',
> > > longblob = 'blob',
> > > text = 'text',
> > > tinytext = 'text',
> > > mediumtext = 'text',
> > > longtext = 'text',
> > > )
>
> > > I also fixed the remaining problem in the line match re.search so line
> > > 75 or line 87 after above dict is changed
>
> > > 75c87
> > > < hit = re.search('(\S+)\s+(\S+)( .*)?', line)
> > > ---
>
> > > > hit = re.search('(\S+)\s+(\S+)(,| )( .*)?', line)
>
> > > this fixes the matching on lines like
>
> > > `description` longtext,
>
> > > The comma immediately after the type got included as part of the type
> > > string match in error.
>
> > > Here is the output for the auth_event table
>
> > > legacy_db.define_table('auth_event',
> > > Field('id','integer'),
> > > Field('time_stamp','datetime'),
> > > Field('client_ip','string'),
> > > Field('user_id','integer'),
> > > Field('origin','string'),
> > > Field('description','text'),
> > > migrate=False)
>
> > > The id field should not be printed since it will be added
> > > automatically.
>
> > > The varchar fields have a length in () e.g. varchar(64) which could be
> > > used to add on a length=N value for the Field constructor. The same
> > > holds true for int fields but I don't think there is a real use for
> > > that.
>
> > > There is no recognition of foreign keys, fixing this would probably be
> > > a significant effort. Some human intervention required as it
> > > stands. ;-)
>
> > > It certainly is a great start to getting a model file for an existing
> > >MySQLdatabase.
>
> > > Ron
>
>
