Hi
I was trying to create model/db.py for RequestTracker mysql schema, and was
fail.
I found quite good plugin_legacymysql and fixed for my needs
- added few types
- remove 'SET' lines same as remarks
- mysql data types without params (like text,)
- import form_factory from sqlhtml
Please find fixed py attached.
Thanks
'''
Create the web2py code needed to access your mysql legacy db.
To make this work all the legacy tables you want to access need to have an "id" field.
This plugin needs:
mysql
mysqldump
installed and globally available.
Under Windows you will probably need to add the mysql executable directory to the PATH variable,
you will also need to modify mysql to mysql.exe and mysqldump to mysqldump.exe below.
Just guessing here :)
Access your tables with:
legacy_db(legacy_db.mytable.id>0).select()
If the script crashes this is might be due to that fact that the data_type_map dictionary below is incomplete.
Please complete it, improve it and continue.
Created by Falko Krause
'''
from gluon.sqlhtml import form_factory
import subprocess
import re
data_type_map = dict(
varchar = 'string',
int = 'integer',
tinyint = 'integer',
smallint = 'integer',
mediumint = 'integer',
binary = 'text',
varbinary = 'text',
text = 'text',
longtext = 'text',
date = 'date',
float = 'double',
char = 'string',
decimal = 'integer',
timestamp = 'datetime',
datetime = 'datetime',
blob = 'text',
longblob = 'text',
)
def index():
table2sql = ''
if not request.vars.database_name:
form = form_factory(
Field('database_name', requires = IS_NOT_EMPTY()),
Field('user_name', requires = IS_NOT_EMPTY()),
Field('password', requires = IS_NOT_EMPTY()),
)
else:
p = subprocess.Popen(['mysql','--user=%s'%request.vars.user_name, '--password=%s'%request.vars.password, '--execute=show tables;', request.vars.database_name],stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
sql_showtables, stderr = p.communicate()
form = form_factory(
Field('database_name', requires = IS_NOT_EMPTY(), default = request.vars.database_name),
Field('user_name', requires = IS_NOT_EMPTY(), default = request.vars.user_name),
Field('password', requires = IS_NOT_EMPTY(), default = request.vars.password),
Field('write_to', default = 'db_legacymysql.py', requires = IS_NOT_EMPTY()),
Field('write', 'boolean', default = False),
Field('tables', requires = IS_IN_SET([re.sub('\|\s+([^\|*])\s+.*', '\1', x) for x in sql_showtables.split()[1:]], multiple = True)),
)
if form.accepts(request.vars, session, keepvalues = True):
#----------------------------------------
#get a list of tables that should be copied
connection_string = "legacy_db = DAL('mysql://%s:%s@localhost/%s')"%(form.vars.user_name, form.vars.password, form.vars.database_name)
table2sql = {'connection string': CODE(connection_string)}
legacy_db_table_web2py_code = []
for table_name in form.vars.tables:
#----------------------------------------
#get the sql create statement
p = subprocess.Popen(['mysqldump','--user=%s'%form.vars.user_name, '--password=%s'%form.vars.password, '--skip-add-drop-table', '--no-data', form.vars.database_name, table_name],stdin=subprocess.PIPE,stdout=subprocess.PIPE,stderr=subprocess.PIPE)
sql_create_stmnt,stderr = p.communicate()
if stderr:
table2sql[table_name] = stderr
if 'CREATE' in sql_create_stmnt:#check if the table exists
#table2sql[table_name] = stdout if stdout else stderr #DEBUG
#----------------------------------------
#remove garbage lines from sql statement
sql_lines = sql_create_stmnt.split('\n')
sql_lines = [x for x in sql_lines if not(x.startswith('--') or x.startswith('/*') or x =='' or x.startswith('SET'))]
table2sql[table_name] = XML('<br/>'.join(sql_lines) ) #DEBUG
#generate the web2py code from the create statement
web2py_table_code = ''
table_name = re.search('CREATE TABLE .(\S+). \(', sql_lines[0]).group(1)
fields = []
for line in sql_lines[1:-1]:
if re.search('KEY', line) or re.search('PRIMARY', line) or re.search(' ID', line) or line.startswith(')'):
continue
hit = re.search('(\S+) (\S+) .*', re.sub(',',' ',line))
name, d_type = hit.group(1), hit.group(2)
d_type = re.sub(r'(\w+)\(.*',r'\1',d_type)
name = re.sub('`','',name)
web2py_table_code += "\n Field('%s','%s'),"%(name,data_type_map[d_type])
web2py_table_code = "legacy_db.define_table('%s',%s\n migrate=False)"%(table_name,web2py_table_code)
table2sql[table_name] = CODE(web2py_table_code)
legacy_db_table_web2py_code.append(web2py_table_code)
#----------------------------------------
#write the legacy db to file
legacy_db_web2py_code = connection_string+"\n\n"
legacy_db_web2py_code += "\n\n#--------\n".join(legacy_db_table_web2py_code)
if form.vars.write == True:
open(os.path.join(request.env.web2py_path,'applications',request.application,'models', form.vars.write_to), 'w').write(legacy_db_web2py_code)
return dict(form = form, tables = table2sql)
