'''
Create the web2py code needed to access your sqlite legacy db.

To make this work all the legacy tables you want to access need to have an "id" field.

Access your tables with:
db(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.

mysql version created by Falko Krause, minor modifications by Massimo Di Pierro and Ron McOuat

Changed to work with sqlite by Vasile Ermicioi
'''

import re
import sys
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',
        )

def fromsqlite(database_name):
	import sqlite3
	conn = sqlite3.connect('storage.sqlite')
	cursor = conn.cursor()
	q = "SELECT * FROM sqlite_master"
	cursor.execute(q)
	connection_string = "db = DAL('sqlite://%s')"%(database_name,)
	legacy_db_table_web2py_code = []
	for r in cursor:
		#get the sql create statement
		sql_create_stmnt = r[4]
		#remove garbage lines from sql statement
		sql_lines = sql_create_stmnt.split('\n')
		#generate the web2py code from the create statement
		web2py_table_code = ''
		table_name = r[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+(\S+)( .*)?', line)
			if hit!=None:
				name, d_type = hit.group(1), hit.group(2)
				d_type = re.sub(r'(\w+)\(.*',r'\1',d_type)
				d_type = re.sub(',','',d_type)
				name = re.sub('`','',name)				
				web2py_table_code += "\n    Field('%s','%s'),"%(name,data_type_map[d_type.lower()])
		web2py_table_code = "db.define_table('%s',%s\n    migrate=False)"%(table_name,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)
	return legacy_db_web2py_code

sql_str = fromsqlite(sys.argv[1])
open(sys.argv[2], "w").write(sql_str)