· 8 years ago · May 14, 2018, 12:32 AM
1#!/usr/bin/python3
2
3import logging
4import os
5import os.path as osp
6import sys
7import sqlite3
8
9
10schema = '''
11 CREATE TABLE db (
12 dbId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
13 dbName TEXT UNIQUE NOT NULL);
14
15 CREATE TABLE run (
16 dbId INTEGER NOT NULL,
17 runId INTEGER NOT NULL,
18 runName TEXT NOT NULL,
19 simtimeExp INTEGER NOT NULL,
20 PRIMARY KEY (runId, dbId) );
21
22 CREATE TABLE runattr (
23 dbId INTEGER NOT NULL,
24 runId INTEGER NOT NULL,
25 attrName TEXT NOT NULL,
26 attrValue TEXT NOT NULL,
27 FOREIGN KEY (runId, dbId) REFERENCES run(runId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
28
29 CREATE TABLE runparam (
30 dbId INTEGER NOT NULL,
31 runId INTEGER NOT NULL,
32 parName TEXT NOT NULL,
33 parValue TEXT NOT NULL,
34 FOREIGN KEY (runId, dbId) REFERENCES run(runId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
35
36 CREATE TABLE scalar (
37 dbId INTEGER NOT NULL,
38 scalarId INTEGER NOT NULL,
39 runId INTEGER NOT NULL,
40 moduleName TEXT NOT NULL,
41 scalarName TEXT NOT NULL,
42 scalarValue REAL,
43 PRIMARY KEY (scalarId, dbId),
44 FOREIGN KEY (runId, dbId) REFERENCES run(runId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
45
46 CREATE TABLE scalarattr (
47 dbId INTEGER NOT NULL,
48 scalarId INTEGER NOT NULL,
49 attrName TEXT NOT NULL,
50 attrValue TEXT NOT NULL,
51 FOREIGN KEY (scalarId, dbId) REFERENCES scalar(scalarId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
52
53 CREATE TABLE statistic (
54 dbId INTEGER NOT NULL,
55 statId INTEGER NOT NULL,
56 runId INTEGER NOT NULL,
57 moduleName TEXT NOT NULL,
58 statName TEXT NOT NULL,
59 statCount INTEGER NOT NULL,
60 statMean REAL,
61 statStddev REAL,
62 statSum REAL,
63 statSqrsum REAL,
64 statMin REAL,
65 statMax REAL,
66 statWeights REAL,
67 statWeightedSum REAL,
68 statSqrSumWeights REAL,
69 statWeightedSqrSum REAL,
70 PRIMARY KEY (statId, dbId),
71 FOREIGN KEY (runId, dbId) REFERENCES run(runId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
72
73 CREATE TABLE statisticattr (
74 dbId INTEGER NOT NULL,
75 statId INTEGER NOT NULL,
76 attrName TEXT NOT NULL,
77 attrValue TEXT NOT NULL,
78 FOREIGN KEY (statId, dbId) REFERENCES statistic(statId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
79
80 CREATE TABLE histbin (
81 dbId INTEGER NOT NULL,
82 statId INTEGER NOT NULL,
83 baseValue NUMERIC NOT NULL,
84 cellValue INTEGER NOT NULL,
85 FOREIGN KEY (statId, dbId) REFERENCES statistic(statId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
86
87 CREATE TABLE vector (
88 dbId INTEGER NOT NULL,
89 vectorId INTEGER NOT NULL,
90 runId INTEGER NOT NULL,
91 moduleName TEXT NOT NULL,
92 vectorName TEXT NOT NULL,
93 vectorCount INTEGER,
94 vectorMin REAL,
95 vectorMax REAL,
96 vectorSum REAL,
97 vectorSumSqr REAL,
98 startEventNum INTEGER,
99 endEventNum INTEGER,
100 startSimtimeRaw INTEGER,
101 endSimtimeRaw INTEGER,
102 PRIMARY KEY (vectorId, dbId),
103 FOREIGN KEY (runId, dbId) REFERENCES run(runId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
104
105 CREATE TABLE vectorattr (
106 dbId INTEGER NOT NULL,
107 vectorId INTEGER NOT NULL,
108 attrName TEXT NOT NULL,
109 attrValue TEXT NOT NULL,
110 FOREIGN KEY (vectorId, dbId) REFERENCES vector(vectorId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
111
112 CREATE TABLE vectordata (
113 dbId INTEGER NOT NULL,
114 vectorId INTEGER NOT NULL,
115 eventNumber INTEGER NOT NULL,
116 simtimeRaw INTEGER NOT NULL,
117 value NUMERIC NOT NULL,
118 FOREIGN KEY (vectorId, dbId) REFERENCES vector(vectorId, dbId) ON DELETE CASCADE ON UPDATE CASCADE );
119'''
120
121
122def merge_dbs(output, result_dir, force=False):
123 exts = ('.sca', '.vec')
124 dbs = {osp.join(result_dir, db) for db in os.listdir(result_dir) if osp.splitext(db)[1] in exts}
125
126 def db_id(path):
127 return osp.splitext(osp.basename(path))[0]
128
129 if osp.exists(output):
130 if force:
131 logging.info('Overwriting existing file')
132 os.remove(output)
133 else:
134 raise ValueError('Output file exists')
135
136 with sqlite3.connect(output) as c:
137 c.executescript(schema)
138 c.executemany('INSERT INTO db(dbName) VALUES (?)', set((db_id(db),) for db in dbs))
139 c.commit()
140
141 ignored_tables = ('sqlite_sequence', 'db')
142 table_stmt = ("SELECT name FROM sqlite_master WHERE type == 'table' AND name NOT IN ({})"
143 .format(', '.join(('?',) * len(ignored_tables))))
144 tables = [t[0] for t in c.execute(table_stmt, ignored_tables).fetchall()]
145
146 for db in dbs:
147 logging.info('Adding {}'.format(db))
148 c.execute("ATTACH '{}' AS new".format(db))
149
150 dbId = c.execute('SELECT dbId FROM db WHERE dbName = ?', (db_id(db),)).fetchone()[0]
151
152 shared_tables = ('run', 'runattr', 'runparam')
153
154 for table in tables:
155 if table in shared_tables:
156 # Check, whether the entries of this table have already been inserted
157 new_entries_stmt = 'SELECT ? as dbId, * FROM new.{table} EXCEPT SELECT * FROM {table}'
158 already_inserted = not c.execute(new_entries_stmt.format(table=table), (dbId,)).fetchone()
159 if already_inserted:
160 continue
161
162 c.execute('INSERT INTO {table} SELECT ? AS dbId, * FROM new.{table}'.format(table=table), (dbId,))
163
164 c.commit()
165 c.execute('DETACH new')
166
167
168def main():
169 import argparse
170
171 parser = argparse.ArgumentParser(description='Merge OmNET++ SQLite result files')
172 parser.add_argument('-f', '--force', action='store_true', help="Overwrite exisiting output file")
173 parser.add_argument('output', help="Path to the combined database")
174 parser.add_argument('result_dir', help="Directory, containing OmNET++ SQLite result files")
175 args = parser.parse_args()
176
177 merge_dbs(args.output, args.result_dir, args.force)
178
179
180if __name__ == '__main__':
181 sys.exit(main())