1Ddafg9e

1'''
2Create the web2py model code needed to access your sqlite legacy db.
3Usage:
4    python extract_sqlite_models.py
5
6Access your tables with:
7    db(legacy_db.mytable.id>0).select()
8
9extract_sqlite_models.py -- Copyright (C) Michele Comitini
10
11This code is distributed with web2py.
12The regexp code and the dictionary type map was extended from
13extact_mysql_models.py that comes with web2py.
14extact_mysql_models.py is Copyright (C) Falko Krause.
15
16this code was modified and fixed for Juacy Willian in 12/2018
17'''
18import re
19import sys
20import sqlite3
21
22header = '''
23# coding: utf-8
24""" Extract models from sqlite database
25
26File Generated automatically by extract_sqlite_models.py
27
28
29
30"""\n\n
31'''
32
33data_type_map = dict(
34    varchar='string',
35    int='integer',
36    integer='integer',
37    tinyint='integer',
38    smallint='integer',
39    mediumint='integer',
40    bigint='integer',
41    float='double',
42    double='double',
43    char='string',
44    decimal='integer',
45    date='date',
46    time='time',
47    timestamp='datetime',
48    datetime='datetime',
49    binary='blob',
50    blob='blob',
51    tinyblob='blob',
52    mediumblob='blob',
53    longblob='blob',
54    text='text',
55    tinytext='text',
56    mediumtext='text',
57    longtext='text',
58    bit='boolean',
59    nvarchar='text',
60    numeric='decimal(30,15)',
61    real='decimal(30,15)',
62    )
63
64
65def get_foreign_keys(sql_lines):
66    fks = dict()
67    for line in sql_lines[1:-1]:
68        if 'references' not in line.lower():
69            continue
70        hit = re.search(
71            r'(\S+) .*? REFERENCES ((\S+) \((\S+)\))', line)
72        if hit:
73            fks[hit.group(1)] = hit.groups()[2:]
74
75    return fks
76
77
78def get_formated_sql(sql_):
79    return sql_.replace('(', '(\n', 1) \
80        .replace('"', '') \
81        .replace(',', ',\n')
82
83
84def sqlite(database_name):
85    conn = sqlite3.connect(database_name)
86    c = conn.cursor()
87    r = c.execute(
88        r"select name,sql from sqlite_master where type='table' and not name like '\\_%' and not lower(name) like 'sqlite_%'")
89    tables = r.fetchall()
90    connection_string = "from pydal import DAL, Field\n\n\n"
91    connection_string += "db = DAL('sqlite://%s')" % database_name
92    legacy_db_table_web2py_code = []
93    for table_name, sql_create_stmnt in tables:
94        if table_name.startswith('_'):
95            continue
96
97        if 'CREATE' in sql_create_stmnt:  # check if the table exists
98            sql_lines = get_formated_sql(sql_create_stmnt).split('\n')
99            sql_lines = [x for x in sql_lines if not (
100                x.startswith('--') or x.startswith('/*')
101            )]
102
103            web2py_table_code = ''
104            fields = []
105            fks = get_foreign_keys(sql_lines)
106
107            for line in sql_lines:
108                if re.search('KEY', line) or re.search('PRIMARY', line) \
109                        or re.search('"ID"', line) or line.startswith('CREATE')\
110                        or line.startswith(')'):
111                    continue
112
113                hit = re.search(r'(\S+)\ ([\w\d]+(\(.*?\))?) ?.*?$', line)
114                if hit is not None:
115
116                    name, d_type = hit.group(1), hit.group(2)
117                    d_type = re.sub(r'(\w+)\(.*', r'\1', d_type)
118                    name = str(re.sub('`', '', name))
119                    if name in fks.keys():
120                        if fks[name][1].lower() == 'id':
121                            field_type = 'reference %s' % (fks[name][0])
122                        else:
123                            field_type = 'reference %s.%s' % (
124                                fks[name][0], fks[name][1])
125                    else:
126                        field_type = data_type_map.get(d_type.lower())
127                        if field_type is None:
128                            continue
129
130                    web2py_table_code += "\n    Field('%s','%s')," % (
131                        name, field_type)
132            web2py_table_code = "db.define_table('%s',%s\n    migrate=False)" % (
133                table_name, web2py_table_code)
134            legacy_db_table_web2py_code.append(web2py_table_code)
135    # ----------------------------------------
136    # write the legacy db to file
137    legacy_db_web2py_code = connection_string + "\n\n"
138    legacy_db_web2py_code += "\n\n#--------\n".join(
139        legacy_db_table_web2py_code)
140    return legacy_db_web2py_code
141
142
143if len(sys.argv) < 2:
144    print('USAGE:\n\n    extract_mysql_models.py data_basename output_file.py \n\n')
145else:
146    db_string = sqlite(sys.argv[1])
147
148    output_file = sys.argv[-1]
149    with open(f'{output_file}.py', 'w') as file:
150        file.write(header)
151        file.writelines(db_string)
152        file.write('')
153    print(f'file {output_file}.py created')