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 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 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
