Source code

Download source archive ↓

upstream/tools/query_tabular/load_db.py

1#!/usr/bin/env python
2
3from __future__ import print_function
4
5import re
6import sys
7
8from filters import TabularReader
9
10
11SQLITE_KEYWORDS = [
12    'ABORT',
13    'ACTION',
14    'ADD',
15    'AFTER',
16    'ALL',
17    'ALTER',
18    'ANALYZE',
19    'AND',
20    'AS',
21    'ASC',
22    'ATTACH',
23    'AUTOINCREMENT',
24    'BEFORE',
25    'BEGIN',
26    'BETWEEN',
27    'BY',
28    'CASCADE',
29    'CASE',
30    'CAST',
31    'CHECK',
32    'COLLATE',
33    'COLUMN',
34    'COMMIT',
35    'CONFLICT',
36    'CONSTRAINT',
37    'CREATE',
38    'CROSS',
39    'CURRENT_DATE',
40    'CURRENT_TIME',
41    'CURRENT_TIMESTAMP',
42    'DATABASE',
43    'DEFAULT',
44    'DEFERRABLE',
45    'DEFERRED',
46    'DELETE',
47    'DESC',
48    'DETACH',
49    'DISTINCT',
50    'DROP',
51    'EACH',
52    'ELSE',
53    'END',
54    'ESCAPE',
55    'EXCEPT',
56    'EXCLUSIVE',
57    'EXISTS',
58    'EXPLAIN',
59    'FAIL',
60    'FOR',
61    'FOREIGN',
62    'FROM',
63    'FULL',
64    'GLOB',
65    'GROUP',
66    'HAVING',
67    'IF',
68    'IGNORE',
69    'IMMEDIATE',
70    'IN',
71    'INDEX',
72    'INDEXED',
73    'INITIALLY',
74    'INNER',
75    'INSERT',
76    'INSTEAD',
77    'INTERSECT',
78    'INTO',
79    'IS',
80    'ISNULL',
81    'JOIN',
82    'KEY',
83    'LEFT',
84    'LIKE',
85    'LIMIT',
86    'MATCH',
87    'NATURAL',
88    'NO',
89    'NOT',
90    'NOTNULL',
91    'NULL',
92    'OF',
93    'OFFSET',
94    'ON',
95    'OR',
96    'ORDER',
97    'OUTER',
98    'PLAN',
99    'PRAGMA',
100    'PRIMARY',
101    'QUERY',
102    'RAISE',
103    'RECURSIVE',
104    'REFERENCES',
105    'REGEXP',
106    'REINDEX',
107    'RELEASE',
108    'RENAME',
109    'REPLACE',
110    'RESTRICT',
111    'RIGHT',
112    'ROLLBACK',
113    'ROW',
114    'SAVEPOINT',
115    'SELECT',
116    'SET',
117    'TABLE',
118    'TEMP',
119    'TEMPORARY',
120    'THEN',
121    'TO',
122    'TRANSACTION',
123    'TRIGGER',
124    'UNION',
125    'UNIQUE',
126    'UPDATE',
127    'USING',
128    'VACUUM',
129    'VALUES',
130    'VIEW',
131    'VIRTUAL',
132    'WHEN',
133    'WHERE',
134    'WITH',
135    'WITHOUT'
136]
137
138
139def get_valid_column_name(name):
140    valid_name = name
141    if not name or not name.strip():
142        return None
143    elif name.upper() in SQLITE_KEYWORDS:
144        valid_name = '"%s"' % name
145    elif re.match(r'^[a-zA-Z]\w*$', name):
146        pass
147    elif re.match(r'^"[^"]+"$', name):
148        pass
149    elif re.match(r'^\[[^\[\]]*\]$', name):
150        pass
151    elif re.match(r"^`[^`]+`$", name):
152        pass
153    elif name.find('"') < 0:
154        valid_name = '"%s"' % name
155    elif name.find('[') < 0 and name.find(']') < 0:
156        valid_name = '[%s]' % name
157    elif name.find('`') < 0:
158        valid_name = '`%s`' % name
159    elif name.find("'") < 0:
160        valid_name = "'%s'" % name
161    return valid_name
162
163
164def getValueType(val):
165    if val or 0. == val:
166        try:
167            int(val)
168            return 'INTEGER'
169        except ValueError:
170            try:
171                float(val)
172                return 'REAL'
173            except ValueError:
174                return 'TEXT'
175    return None
176
177
178def get_column_def(file_path, table_name, skip=0, comment_char='#',
179                   column_names=None, max_lines=1000, load_named_columns=False,
180                   firstlinenames=False, filters=None):
181    col_pref = ['TEXT', 'REAL', 'INTEGER', None]
182    col_types = []
183    col_idx = None
184    col_names = []
185    try:
186        tr = TabularReader(file_path, skip=skip, comment_char=comment_char,
187                           col_idx=None, filters=filters)
188        for linenum, fields in enumerate(tr):
189            if linenum == 0 and firstlinenames:
190                col_names = [get_valid_column_name(name) or 'c%d' % (i + 1)
191                             for i, name in enumerate(fields)]
192                # guarantee col_types in case of empty data
193                while len(col_types) < len(fields):
194                    col_types.append(None)
195                continue
196            if linenum > max_lines:
197                break
198            try:
199                while len(col_types) < len(fields):
200                    col_types.append(None)
201                for i, val in enumerate(fields):
202                    colType = getValueType(val)
203                    if col_pref.index(colType) < col_pref.index(col_types[i]):
204                        col_types[i] = colType
205            except Exception as e:
206                print('Failed at line: %d err: %s' % (linenum, e),
207                      file=sys.stderr)
208    except Exception as e:
209        print('Failed: %s' % (e), file=sys.stderr)
210    for i, col_type in enumerate(col_types):
211        if not col_type:
212            col_types[i] = 'TEXT'
213    if not col_names:
214        col_names = ['c%d' % i for i in range(1, len(col_types) + 1)]
215    if column_names:
216        cnames = [cn.strip() for cn in column_names.split(',')]
217        if load_named_columns:
218            col_idx = []
219            colnames = []
220            for i, cname in enumerate(cnames):
221                # guarantee col_types in case of empty data
222                if i >= len(col_types):
223                    col_types.append('TEXT')
224                if cname != '':
225                    col_idx.append(i)
226                    colnames.append(cname)
227            col_types = [col_types[i] for i in col_idx]
228            col_names = colnames
229        else:
230            if col_names:
231                for i, cname in enumerate(cnames):
232                    if cname and i < len(col_names):
233                        col_names[i] = cname
234            else:
235                col_names = [x if x else 'c%d' % (i) for i, x in enumerate(cnames)]
236    col_def = []
237    for i, col_name in enumerate(col_names):
238        if i >= len(col_types):
239            col_types.append('TEXT')
240        col_def.append('%s %s' % (col_names[i], col_types[i]))
241    return col_names, col_types, col_def, col_idx
242
243
244def create_table(conn, file_path, table_name, skip=0, comment_char='#',
245                 pkey_autoincr=None, column_names=None,
246                 load_named_columns=False, firstlinenames=False,
247                 filters=None, unique_indexes=[], indexes=[]):
248    col_names, col_types, col_def, col_idx = \
249        get_column_def(file_path, table_name, skip=skip,
250                       comment_char=comment_char, column_names=column_names,
251                       load_named_columns=load_named_columns,
252                       firstlinenames=firstlinenames,
253                       filters=filters)
254    col_func = [float if t == 'REAL' else int
255                if t == 'INTEGER' else str for t in col_types]
256    table_def = 'CREATE TABLE %s (\n    %s%s\n);' % (
257                table_name,
258                '%s INTEGER PRIMARY KEY AUTOINCREMENT,' %
259                pkey_autoincr if pkey_autoincr else '',
260                ', \n    '.join(col_def))
261    # print >> sys.stdout, table_def
262    insert_stmt = 'INSERT INTO %s(%s) VALUES(%s)' % (
263                  table_name, ','.join(col_names),
264                  ','.join(["?" for x in col_names]))
265    # print >> sys.stdout, insert_stmt
266    data_lines = 0
267    try:
268        c = conn.cursor()
269        c.execute(table_def)
270        conn.commit()
271        c.close()
272
273        c = conn.cursor()
274        tr = TabularReader(file_path, skip=skip, comment_char=comment_char,
275                           col_idx=col_idx, filters=filters)
276        for linenum, fields in enumerate(tr):
277            if linenum == 0 and firstlinenames:
278                continue
279            data_lines += 1
280            try:
281                vals = [col_func[i](x)
282                        if x else None for i, x in enumerate(fields)]
283                c.execute(insert_stmt, vals)
284            except Exception as e:
285                print('Load %s Failed line: %d err: %s' % (file_path, linenum, e),
286                      file=sys.stderr)
287                for i, val in enumerate(fields):
288                    try:
289                        col_func[i](val)
290                    except Exception:
291                        colType = getValueType(val)
292                        col_func[i] = float if colType == 'REAL' else int if colType == 'INTEGER' else str
293                        print('Changing %s from %s to %s' % (col_names[i], col_types[i], colType),
294                              file=sys.stderr)
295                        col_types[i] = colType
296                vals = [col_func[i](x)
297                        if x else None for i, x in enumerate(fields)]
298                print('%s  %s' % (insert_stmt, vals),
299                      file=sys.stderr)
300                try:
301                    c.execute(insert_stmt, vals)
302                except Exception as e:
303                    print('Insert %s line: %d Failed err: %s' % (file_path, linenum, e),
304                          file=sys.stderr)
305        conn.commit()
306        c.close()
307        for i, index in enumerate(unique_indexes):
308            index_name = 'idx_uniq_%s_%d' % (table_name, i)
309            index_columns = index.split(',')
310            create_index(conn, table_name, index_name, index_columns,
311                         unique=True)
312        for i, index in enumerate(indexes):
313            index_name = 'idx_%s_%d' % (table_name, i)
314            index_columns = index.split(',')
315            create_index(conn, table_name, index_name, index_columns)
316    except Exception as e:
317        exit('Error: %s' % (e))
318
319
320def create_index(conn, table_name, index_name, index_columns, unique=False):
321    index_def = 'CREATE %s INDEX %s on %s(%s)' % (
322                'UNIQUE' if unique else '', index_name,
323                table_name, ','.join(index_columns))
324    try:
325        c = conn.cursor()
326        c.execute(index_def)
327        conn.commit()
328        c.close()
329    except Exception as e:
330        print('Failed: %s err: %s' % (index_def, e), file=sys.stderr)
331        raise e