From c196027c94d28b45c386b1a8f0052225475bc2e9 Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Tue, 26 Aug 2014 19:57:36 +0000 Subject: Pull from trunk. svn path=/branches/tk705/; revision=5936 --- potpourri/validation-status-sql.py | 80 +++++++++++++++++++++++++------------- 1 file changed, 52 insertions(+), 28 deletions(-) (limited to 'potpourri/validation-status-sql.py') diff --git a/potpourri/validation-status-sql.py b/potpourri/validation-status-sql.py index 81d79b48..fc52e64b 100755 --- a/potpourri/validation-status-sql.py +++ b/potpourri/validation-status-sql.py @@ -63,11 +63,13 @@ class Parser(object): self.args = args self.init_sql() self.init_hook() + self.index1() self.parsed = 1 for self.current, self.iterval in enumerate(self.iterator, 1): self.parse_xml() if self.parsed > 1: sys.stderr.write("\n") + self.index2() self.db.close() @@ -75,52 +77,74 @@ class Parser(object): creating = not os.path.exists(self.args.database) self.db = sqlite3.connect(self.args.database) self.db.text_factory = str - self.db.execute("PRAGMA foreign_keys = on") + self.db.executescript(''' + PRAGMA foreign_keys = off; + PRAGMA synchronous = off; + PRAGMA count_changes = off; + ''') if creating: self.db.executescript(''' CREATE TABLE sessions ( - id INTEGER PRIMARY KEY NOT NULL, - session DATETIME UNIQUE NOT NULL, - handle TEXT UNIQUE NOT NULL); + session_id INTEGER PRIMARY KEY NOT NULL, + session DATETIME NOT NULL, + handle TEXT NOT NULL + ); CREATE TABLE uris ( - id INTEGER PRIMARY KEY NOT NULL, - uri TEXT UNIQUE NOT NULL); + uri_id INTEGER PRIMARY KEY NOT NULL, + uri TEXT NOT NULL + ); CREATE TABLE codes ( - id INTEGER PRIMARY KEY NOT NULL, - code TEXT UNIQUE NOT NULL); + code_id INTEGER PRIMARY KEY NOT NULL, + code TEXT NOT NULL + ); CREATE TABLE generations ( - id INTEGER PRIMARY KEY NOT NULL, - generation TEXT UNIQUE); + generation_id INTEGER PRIMARY KEY NOT NULL, + generation TEXT NOT NULL + ); CREATE TABLE events ( id INTEGER PRIMARY KEY NOT NULL, timestamp DATETIME NOT NULL, - session_id INTEGER NOT NULL REFERENCES sessions(id) ON DELETE RESTRICT ON UPDATE RESTRICT, - generation_id INTEGER NOT NULL REFERENCES generations(id) ON DELETE RESTRICT ON UPDATE RESTRICT, - code_id INTEGER NOT NULL REFERENCES codes(id) ON DELETE RESTRICT ON UPDATE RESTRICT, - uri_id INTEGER NOT NULL REFERENCES uris(id) ON DELETE RESTRICT ON UPDATE RESTRICT, - UNIQUE (timestamp, generation_id, code_id, uri_id)); + session_id INTEGER NOT NULL REFERENCES sessions (session_id) ON DELETE RESTRICT ON UPDATE RESTRICT, + generation_id INTEGER NOT NULL REFERENCES generations (generation_id) ON DELETE RESTRICT ON UPDATE RESTRICT, + code_id INTEGER NOT NULL REFERENCES codes (code_id) ON DELETE RESTRICT ON UPDATE RESTRICT, + uri_id INTEGER NOT NULL REFERENCES uris (uri_id) ON DELETE RESTRICT ON UPDATE RESTRICT + ); CREATE VIEW status AS - SELECT events.id, handle, session, timestamp, generation, uri, code - FROM events - JOIN sessions ON sessions.id = events.session_id - JOIN uris ON uris.id = events.uri_id - JOIN codes ON codes.id = events.code_id - JOIN generations ON generations.id = events.generation_id; + SELECT id, handle, session, timestamp, generation, code, uri + FROM events + NATURAL JOIN sessions + NATURAL JOIN uris + NATURAL JOIN codes + NATURAL JOIN generations; ''') + + def index1(self): + self.db.executescript(''' + CREATE UNIQUE INDEX IF NOT EXISTS sessions_index ON sessions (session); + CREATE UNIQUE INDEX IF NOT EXISTS handles_index ON sessions (handle); + CREATE UNIQUE INDEX IF NOT EXISTS uris_index ON uris (uri); + CREATE UNIQUE INDEX IF NOT EXISTS codes_index ON codes (code); + CREATE UNIQUE INDEX IF NOT EXISTS generations_index ON generations (generation); + ''') + + + def index2(self): + self.db.executescript(''' + CREATE UNIQUE INDEX IF NOT EXISTS events_index ON events (uri_id, timestamp, code_id, generation_id); + ''') + + def string_id(self, table, value): field = table.rstrip("s") try: - if value is None: - return self.db.execute("SELECT id FROM %s WHERE %s IS NULL" % (table, field)).fetchone()[0] - else: - return self.db.execute("SELECT id FROM %s WHERE %s = ?" % (table, field), (value,)).fetchone()[0] + return self.db.execute("SELECT %s_id FROM %s WHERE %s = ?" % (field, table, field), (value,)).fetchone()[0] except: return self.db.execute("INSERT INTO %s (%s) VALUES (?)" % (table, field), (value,)).lastrowid @@ -132,13 +156,13 @@ class Parser(object): return xml = self.read_xml() with self.db: - session_id = self.db.execute("INSERT INTO sessions (session, handle) VALUES (datetime(?), ?)", + session_id = self.db.execute("INSERT INTO sessions (session, handle) VALUES (strftime('%s', ?), ?)", (xml.get("date"), self.handle)).lastrowid self.db.executemany("INSERT INTO events (session_id, timestamp, generation_id, code_id, uri_id) " - "VALUES (?, datetime(?), ?, ?, ?)", + "VALUES (?, strftime('%s', ?), ?, ?, ?)", ((session_id, x.get("timestamp"), - self.string_id("generations", x.get("generation")), + self.string_id("generations", x.get("generation", "none")), self.string_id("codes", x.get("status")), self.string_id("uris", x.text.strip())) for x in xml.findall("validation_status"))) -- cgit v1.2.3