# http://pyrocko.org - GPLv3 # # The Pyrocko Developers, 21st Century # ---|P------/S----------~Lg----------
''' Shared meta-information database used by Squirrel. '''
except sqlite3.OperationalError: raise error.SquirrelError( 'Cannot connect to database: %s' % database_path)
self._conn.set_trace_callback(self._log_statement)
logger.debug(statement)
c.execute( ''' SELECT name FROM sqlite_master WHERE type = 'table' AND name = '{files}' '''))): return
'''PRAGMA recursive_triggers = true''')
''' CREATE TABLE IF NOT EXISTS files ( file_id integer PRIMARY KEY, path text, format text, mtime float, size integer) '''))
''' CREATE UNIQUE INDEX IF NOT EXISTS index_files_path ON files (path) ''')
''' CREATE TABLE IF NOT EXISTS nuts ( nut_id integer PRIMARY KEY AUTOINCREMENT, file_id integer, file_segment integer, file_element integer, kind_id integer, kind_codes_id integer, tmin_seconds integer, tmin_offset integer, tmax_seconds integer, tmax_offset integer, kscale integer) '''))
''' CREATE UNIQUE INDEX IF NOT EXISTS index_nuts_file_element ON nuts (file_id, file_segment, file_element) ''')
''' CREATE TABLE IF NOT EXISTS kind_codes ( kind_codes_id integer PRIMARY KEY, kind_id integer, codes text, deltat float) '''))
''' CREATE UNIQUE INDEX IF NOT EXISTS index_kind_codes ON kind_codes (kind_id, codes, deltat) ''')
''' CREATE TABLE IF NOT EXISTS kind_codes_count ( kind_codes_id integer PRIMARY KEY, count integer) '''))
''' CREATE INDEX IF NOT EXISTS index_nuts_file_id ON nuts (file_id) ''')
''' CREATE TRIGGER IF NOT EXISTS delete_nuts_on_delete_file BEFORE DELETE ON files FOR EACH ROW BEGIN DELETE FROM nuts where file_id == old.file_id; END ''')
# trigger only on size to make silent update of mtime possible ''' CREATE TRIGGER IF NOT EXISTS delete_nuts_on_update_file BEFORE UPDATE OF size ON files FOR EACH ROW BEGIN DELETE FROM nuts where file_id == old.file_id; END ''')
''' CREATE TRIGGER IF NOT EXISTS increment_kind_codes BEFORE INSERT ON nuts FOR EACH ROW BEGIN INSERT OR IGNORE INTO kind_codes_count VALUES (new.kind_codes_id, 0); UPDATE kind_codes_count SET count = count + 1 WHERE new.kind_codes_id == kind_codes_id; END ''')
''' CREATE TRIGGER IF NOT EXISTS decrement_kind_codes BEFORE DELETE ON nuts FOR EACH ROW BEGIN UPDATE kind_codes_count SET count = count - 1 WHERE old.kind_codes_id == kind_codes_id; END ''')
''' Store or update content meta-information.
Given ``nuts`` are assumed to represent an up-to-date and complete inventory of a set of files. Any old information about these files is first pruned from the database (via database triggers). If such content is part of a live selection, it is also removed there. Then the new content meta-information is inserted into the main database. The content is not automatically inserted into the live selections again. It is in the responsibility of the selection object to perform this step. '''
nut.file_path, nut.file_format, nut.file_mtime, nut.file_size))
'INSERT OR IGNORE INTO files VALUES (NULL,?,?,?,?)', files)
'''UPDATE files SET format = ?, mtime = ?, size = ? WHERE path == ? ''', ((x[1], x[2], x[3], x[0]) for x in files))
'INSERT OR IGNORE INTO kind_codes VALUES (NULL,?,?,?)', kind_codes)
''' INSERT INTO nuts VALUES (NULL, ( SELECT file_id FROM files WHERE path == ? ),?,?,?, ( SELECT kind_codes_id FROM kind_codes WHERE kind_id == ? AND codes == ? AND deltat == ? ), ?,?,?,?,?) ''', ((nut.file_path, nut.file_segment, nut.file_element, nut.kind_id, nut.kind_id, nut.codes, nut.deltat or 0.0, nut.tmin_seconds, nut.tmin_offset, nut.tmax_seconds, nut.tmax_offset, nut.kscale) for nut in nuts))
SELECT files.path, files.format, files.mtime, files.size, nuts.file_segment, nuts.file_element, kind_codes.kind_id, kind_codes.codes, nuts.tmin_seconds, nuts.tmin_offset, nuts.tmax_seconds, nuts.tmax_offset, kind_codes.deltat FROM files INNER JOIN nuts ON files.file_id = nuts.file_id INNER JOIN kind_codes ON nuts.kind_codes_id == kind_codes.kind_codes_id WHERE path == ? '''
for row in self._conn.execute(sql, (path,))]
SELECT files.path, files.format, files.mtime, files.size, nuts.file_segment, nuts.file_element, kind_codes.kind_id, kind_codes.codes, nuts.tmin_seconds, nuts.tmin_offset, nuts.tmax_seconds, nuts.tmax_offset, kind_codes.deltat FROM files INNER JOIN nuts ON files.file_id == nuts.file_id INNER JOIN kind_codes ON nuts.kind_codes_id == kind_codes.kind_codes_id '''
return None
''' Prune content meta-inforamation about a given file.
All content pieces belonging to file ``path`` are removed from the main database and any attached live selections (via database triggers). '''
self._conn.execute( 'DELETE FROM files WHERE path = ?', (path,))
''' Prune information associated with a given file, but keep the file path.
This method is called when reading a file failed. File attributes, format, size and modification time are set to NULL. File content meta-information is removed from the database and any attached live selections (via database triggers). '''
''' UPDATE files SET format = NULL, mtime = NULL, size = NULL WHERE path = ? ''', (path,))
''' Update modification time of file without initiating reindexing.
Useful to prolong validity period of data with expiration date. '''
raise FileLoadError( 'Silent update for file "%s" failed: size has changed.' % path)
UPDATE files SET mtime = ? WHERE path = ? '''
sel = 'AND kind_codes.kind_id == ?' args.append(to_kind_id(kind))
SELECT kind_codes.kind_id, kind_codes.codes, kind_codes.deltat, %(kind_codes_count)s.count FROM %(kind_codes_count)s INNER JOIN kind_codes ON %(kind_codes_count)s.kind_codes_id == kind_codes.kind_codes_id WHERE %(kind_codes_count)s.count > 0 ''' + sel + ''' ''') % {'kind_codes_count': kind_codes_count}
to_kind(kind_id), tuple(codes.split(separator)), deltat), count
SELECT DISTINCT kind_codes.deltat FROM %(kind_codes_count)s INNER JOIN kind_codes ON %(kind_codes_count)s.kind_codes_id == kind_codes.kind_codes_id WHERE %(kind_codes_count)s.count > 0 ''' + sel + ''' ORDER BY kind_codes.deltat ''') % {'kind_codes_count': kind_codes_count}
SELECT DISTINCT kind_codes.codes FROM %(kind_codes_count)s INNER JOIN kind_codes ON %(kind_codes_count)s.kind_codes_id == kind_codes.kind_codes_id WHERE %(kind_codes_count)s.count > 0 ''' + sel + ''' ORDER BY kind_codes.codes ''') % {'kind_codes_count': kind_codes_count}
SELECT DISTINCT kind_codes.kind_id FROM %(kind_codes_count)s INNER JOIN kind_codes ON %(kind_codes_count)s.kind_codes_id == kind_codes.kind_codes_id WHERE %(kind_codes_count)s.count > 0 ''' + sel + ''' ORDER BY kind_codes.kind_id ''') % {'kind_codes_count': kind_codes_count}
else:
else:
SELECT SUM(files.size) FROM files '''
nfiles=self.get_nfiles(), nnuts=self.get_nnuts(), kinds=self.get_kinds(), codes=self.get_codes(), counts=self.get_counts(), total_size=self.get_total_size())
def __str__(self): return str(self.get_stats())
'files', 'nuts', 'kind_codes', 'kind_codes_count']:
stream = sys.stdout
def __repr__(self): return self
max((len(repr(x)) if x is not None else 0) for x in col) for col in zip(*tab)]
(repr(x).ljust(wid) if x is not None else ''.ljust(wid, '-')) for (x, wid) in zip(row, widths)))
''' Container to hold statistics about contents cached in meta-information db. '''
help='number of files in database') help='number of index nuts in database') Tuple.T(content_t=String.T()), help='available code sequences in database, e.g. ' '(agency, network, station, location) for stations nuts.') String.T(), help='available content types in database') help='aggregated file size of files referenced in database') String.T(), Dict.T(Tuple.T(content_t=String.T()), Int.T()), help='breakdown of how many nuts of any content type and code ' 'sequence are available in database, ``counts[kind][codes]``')
def __str__(self): kind_counts = dict( (kind, sum(self.counts[kind].values())) for kind in self.kinds)
codes = ['.'.join(x) for x in self.codes]
if len(codes) > 20: scodes = '\n' + util.ewrap(codes[:10], indent=' ') \ + '\n [%i more]\n' % (len(codes) - 20) \ + util.ewrap(codes[-10:], indent=' ') else: scodes = '\n' + util.ewrap(codes, indent=' ') \ if codes else '<none>'
s = ''' Available codes: %s Number of files: %i Total size of known files: %s Number of index nuts: %i Available content kinds: %s''' % ( scodes, self.nfiles, util.human_bytesize(self.total_size), self.nnuts, ', '.join('%s: %i' % ( kind, kind_counts[kind]) for kind in sorted(self.kinds)))
return s
'Database', 'DatabaseStats', ] |