def generateResetSequencesSQL(cur): """Return SQL that will reset table sequences to match the data in them. """ stmt = [] for schema, sequence, table, column in listSequences(cur): if table is None or column is None: continue sql = "SELECT max(%s) FROM %s" % ( quoteIdentifier(column), quoteIdentifier(table) ) cur.execute(sql) last_value = cur.fetchone()[0] if last_value is None: last_value = 1 flag = 'false' else: flag = 'true' sql = "setval(%s, %d, %s)" % ( quote('%s.%s' % (schema, sequence)), int(last_value), flag ) stmt.append(sql) if stmt: stmt = 'SELECT ' + ', '.join(stmt) return stmt else: return ''
def fqn(namespace, name): """Return the fully qualified name by combining the namespace and name. Quoting is done for the non trivial cases. >>> print fqn('public', 'foo') public.foo >>> print fqn(' foo ', '$bar') " foo "."$bar" """ if re.search(r"[^a-z_]", namespace) is not None: namespace = quoteIdentifier(namespace) if re.search(r"[^a-z_]", name) is not None: name = quoteIdentifier(name) return "%s.%s" % (namespace, name)
def getHoldingTableName(self, tablename, suffix=''): """Name for a holding table to hold data being copied in tablename. Return value is properly quoted for use as an SQL identifier. """ raw_name = self.getRawHoldingTableName(tablename, suffix) return quoteIdentifier(raw_name)
def generateResetSequencesSQL(cur): """Return SQL that will reset table sequences to match the data in them. """ stmt = [] for schema, sequence, table, column in listSequences(cur): if table is None or column is None: continue sql = "SELECT max(%s) FROM %s" % (quoteIdentifier(column), quoteIdentifier(table)) cur.execute(sql) last_value = cur.fetchone()[0] if last_value is None: last_value = 1 flag = 'false' else: flag = 'true' sql = "setval(%s, %d, %s)" % (quote( '%s.%s' % (schema, sequence)), int(last_value), flag) stmt.append(sql) if stmt: stmt = 'SELECT ' + ', '.join(stmt) return stmt else: return ''
def main(): parser = OptionParser() db_options(parser) parser.add_option( "-f", "--from", dest="from_date", default=None, metavar="DATE", help="Only count new files since DATE (yyyy/mm/dd)") parser.add_option( "-u", "--until", dest="until_date", default=None, metavar="DATE", help="Only count new files until DATE (yyyy/mm/dd)") options, args = parser.parse_args() if len(args) > 0: parser.error("Too many command line arguments.") # Handle date filters. We use LibraryFileContent.datecreated rather # than LibraryFileAlias.datecreated as this report is about actual # disk space usage. A new row in the database linking to a # previously existing file in the Librarian takes up no new space. if options.from_date is not None: from_date = 'AND LFC.datecreated >= %s' % sqlvalues( options.from_date) else: from_date = '' if options.until_date is not None: until_date = 'AND LFC.datecreated <= %s' % sqlvalues( options.until_date) else: until_date = '' con = connect() cur = con.cursor() # Collect direct references to the LibraryFileAlias table. references = set( (from_table, from_column) # Note that listReferences is recursive, which we don't # care about in this simple report. We also ignore the # irrelevant constraint type update and delete flags. for from_table, from_column, to_table, to_column, update, delete in listReferences(cur, 'libraryfilealias', 'id') if to_table == 'libraryfilealias' ) totals = set() for referring_table, referring_column in sorted(references): if referring_table == 'libraryfiledownloadcount': continue quoted_referring_table = quoteIdentifier(referring_table) quoted_referring_column = quoteIdentifier(referring_column) cur.execute(""" SELECT COALESCE(SUM(filesize), 0), pg_size_pretty(CAST(COALESCE(SUM(filesize), 0) AS bigint)), COUNT(*) FROM ( SELECT DISTINCT ON (LFC.id) LFC.id, LFC.filesize FROM LibraryFileContent AS LFC, LibraryFileAlias AS LFA, %s WHERE LFC.id = LFA.content AND LFA.id = %s.%s AND ( LFA.expires IS NULL OR LFA.expires > CURRENT_TIMESTAMP AT TIME ZONE 'UTC') %s %s ORDER BY LFC.id ) AS Whatever """ % ( quoted_referring_table, quoted_referring_table, quoted_referring_column, from_date, until_date)) total_bytes, formatted_size, num_files = cur.fetchone() totals.add((total_bytes, referring_table, formatted_size, num_files)) for total_bytes, tab_name, formatted_size, num_files in sorted( totals, reverse=True): print '%-10s %s in %d files' % (formatted_size, tab_name, num_files) return 0
def main(): parser = OptionParser() db_options(parser) parser.add_option("-f", "--from", dest="from_date", default=None, metavar="DATE", help="Only count new files since DATE (yyyy/mm/dd)") parser.add_option("-u", "--until", dest="until_date", default=None, metavar="DATE", help="Only count new files until DATE (yyyy/mm/dd)") options, args = parser.parse_args() if len(args) > 0: parser.error("Too many command line arguments.") # Handle date filters. We use LibraryFileContent.datecreated rather # than LibraryFileAlias.datecreated as this report is about actual # disk space usage. A new row in the database linking to a # previously existing file in the Librarian takes up no new space. if options.from_date is not None: from_date = 'AND LFC.datecreated >= %s' % sqlvalues(options.from_date) else: from_date = '' if options.until_date is not None: until_date = 'AND LFC.datecreated <= %s' % sqlvalues( options.until_date) else: until_date = '' con = connect() cur = con.cursor() # Collect direct references to the LibraryFileAlias table. references = set( (from_table, from_column) # Note that listReferences is recursive, which we don't # care about in this simple report. We also ignore the # irrelevant constraint type update and delete flags. for from_table, from_column, to_table, to_column, update, delete in listReferences(cur, 'libraryfilealias', 'id') if to_table == 'libraryfilealias') totals = set() for referring_table, referring_column in sorted(references): if referring_table == 'libraryfiledownloadcount': continue quoted_referring_table = quoteIdentifier(referring_table) quoted_referring_column = quoteIdentifier(referring_column) cur.execute(""" SELECT COALESCE(SUM(filesize), 0), pg_size_pretty(CAST(COALESCE(SUM(filesize), 0) AS bigint)), COUNT(*) FROM ( SELECT DISTINCT ON (LFC.id) LFC.id, LFC.filesize FROM LibraryFileContent AS LFC, LibraryFileAlias AS LFA, %s WHERE LFC.id = LFA.content AND LFA.id = %s.%s AND ( LFA.expires IS NULL OR LFA.expires > CURRENT_TIMESTAMP AT TIME ZONE 'UTC') %s %s ORDER BY LFC.id ) AS Whatever """ % (quoted_referring_table, quoted_referring_table, quoted_referring_column, from_date, until_date)) total_bytes, formatted_size, num_files = cur.fetchone() totals.add((total_bytes, referring_table, formatted_size, num_files)) for total_bytes, tab_name, formatted_size, num_files in sorted( totals, reverse=True): print '%-10s %s in %d files' % (formatted_size, tab_name, num_files) return 0