I thought I had posted this about an hour ago but don't see it as a topic so I'll repost. Forgive if this is a duplicate.
I'm trying to build an Oracle version of the extract_mysql_models.py and extract_pgsql_models.py, these files look at a currently existing database and write out a web2py model definition for that database. I've got a legacy database with almost 800 tables some with almost 200 columns and there are changes with each new release of the software which is roughtly twice a year, so you could see my motivation for not creating the models by hand. I'm using the postgres version of the extract models script as my template and trying to swap out the pieces that are postgres specific with pieces that work with Oracle. I'm stuck on two parts, which I would greatly appreciate some help/advice on. 1. The function “references” (extract_pgsql_models.py line 194 or so) is confusing for me. I understand that it gets FK relationships but I'm not sure which side of the relationship it gets and what it is ultimately returning to the calling function. The main SQL looks like this: rows1 = query(conn, """SELECT table_name, column_name, constraint_name, update_rule, delete_rule, ordinal_position FROM information_schema.key_column_usage NATURAL JOIN information_schema.referential_constraints NATURAL JOIN information_schema.table_constraints WHERE information_schema.key_column_usage.table_name=%s AND information_schema.key_column_usage.column_name=%s AND information_schema.table_constraints.constraint_type='FOREIGN KEY' ;""", table, field) What is this SQL returning? Why does the function fail if this returns more then one reference? Does that mean I can't have a table that has two FK references? 2. Secondly it is unclear to me how or if the script handles dependencies. For example if I have a Unit table and in that table is a reference to a location (one location many units) when the script is processing the unit table will it check for (and create if needed) the location table so that all dependent tables are created before a FK reference is made from a table? I'm open to other suggestions/tools. I was able to get sqlautocode to generate sqlalchemy models but in looking at the source it didn't seem straightforward to modify so that it output web2py models. Any help is appreciated :)
