101 lines
3.7 KiB
Python
101 lines
3.7 KiB
Python
import sqlite3
|
|
import datetime
|
|
|
|
DB_FILE = './dge_traefik_logs.db3'
|
|
TABLES = ['LOGS_2023_07', 'LOGS_2023_08', 'LOGS_2023_09', 'LOGS_2023_10', 'LOGS_2023_11', 'LOGS_2023_12']
|
|
|
|
sql_create_id_index = '''
|
|
CREATE UNIQUE INDEX IF NOT EXISTS "ix_logs_{tk_date}_id" ON "{table}" (
|
|
"id"
|
|
);
|
|
'''
|
|
|
|
sql_create_service_index = '''
|
|
CREATE INDEX IF NOT EXISTS "ix_logs_{tk_date}_service" ON "{table}" (
|
|
"ogc_service"
|
|
);
|
|
'''
|
|
|
|
sql_create_application_index = '''
|
|
CREATE INDEX IF NOT EXISTS "ix_logs_{tk_date}_application" ON "{table}" (
|
|
"dge_application"
|
|
);
|
|
'''
|
|
|
|
sql_select_nb_lines = "SELECT COUNT(id) AS nb_lines FROM {table};"
|
|
sql_select_nb_requests = "SELECT COUNT(id) AS nb_requests FROM {table};"
|
|
sql_select_nb_requests_by_service = "SELECT ogc_service AS service, COUNT(id) AS requests FROM {table} WHERE dge_application IN ('geoserver', 'geonetwork') and ogc_service IN ('wms', 'wfs', 'wmts', 'csw') GROUP BY ogc_service ORDER BY ogc_service;"
|
|
sql_select_content_size = "SELECT SUM(org_content_size) AS size FROM {table};"
|
|
sql_select_content_size_by_service = "SELECT ogc_service AS service, SUM(org_content_size) AS size FROM {table} WHERE dge_application IN ('geoserver', 'geonetwork') and ogc_service IN ('wms', 'wfs', 'wmts', 'csw') GROUP BY ogc_service ORDER BY ogc_service;"
|
|
|
|
|
|
def read_tables(db_file):
|
|
|
|
result = {}
|
|
|
|
with sqlite3.connect(DB_FILE) as conn:
|
|
c = conn.cursor()
|
|
|
|
for table in TABLES:
|
|
print('Table:', table)
|
|
start_table = datetime.datetime.now()
|
|
|
|
tk_date = table.replace('LOGS_', '')
|
|
c.execute(sql_create_service_index.format(tk_date=tk_date, table=table))
|
|
c.execute(sql_create_application_index.format(tk_date=tk_date, table=table))
|
|
|
|
result[table] = {}
|
|
|
|
c.execute(sql_select_nb_lines.format(table=table))
|
|
result[table]['nb_lines'] = c.fetchone()[0]
|
|
|
|
c.execute(sql_select_nb_requests_by_service.format(table=table))
|
|
for row in c.fetchall():
|
|
result[table]['nb_' + row[0]] = row[1]
|
|
|
|
c.execute(sql_select_nb_requests.format(table=table))
|
|
result[table]['nb_all'] = c.fetchone()[0]
|
|
|
|
c.execute(sql_select_content_size_by_service.format(table=table))
|
|
for row in c.fetchall():
|
|
result[table]['size_' + row[0]] = row[1]
|
|
|
|
c.execute(sql_select_content_size.format(table=table))
|
|
result[table]['size_all'] = c.fetchone()[0]
|
|
|
|
end_table = datetime.datetime.now()
|
|
print("Duration:", str(end_table - start_table).split('.')[0])
|
|
|
|
return result
|
|
|
|
|
|
def print_result(result):
|
|
print('-'*80)
|
|
lines = []
|
|
first = True
|
|
for table in result:
|
|
if first:
|
|
line = ['TABLE']
|
|
line.extend([str(key).upper() for key in result[table]])
|
|
lines.append(','.join(line))
|
|
first = False
|
|
line = [table]
|
|
line.extend([str(result[table][key]) for key in result[table]])
|
|
lines.append(','.join(line))
|
|
print('\n'.join(lines))
|
|
print('-'*80)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
print('=' * 80)
|
|
start = datetime.datetime.now()
|
|
print("Start:", start.strftime("%H:%M:%S"))
|
|
|
|
result = read_tables(DB_FILE)
|
|
print_result(result)
|
|
|
|
end = datetime.datetime.now()
|
|
print("End:", end.strftime("%H:%M:%S"))
|
|
print("Total duration:", str(end - start).split('.')[0])
|
|
print('=' * 80)
|