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