52 lines
1.4 KiB
Python
52 lines
1.4 KiB
Python
|
"""
|
||
|
|
Generate Complete L2 Schema Documentation
|
||
|
|
"""
|
||
|
|
import sqlite3
|
||
|
|
|
||
|
|
conn = sqlite3.connect('database/L2/L2.db')
|
||
|
|
cursor = conn.cursor()
|
||
|
|
|
||
|
|
# Get all table names
|
||
|
|
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
|
||
|
|
tables = [row[0] for row in cursor.fetchall()]
|
||
|
|
|
||
|
|
print('='*80)
|
||
|
|
print('L2 DATABASE COMPLETE SCHEMA')
|
||
|
|
print('='*80)
|
||
|
|
print()
|
||
|
|
|
||
|
|
for table in tables:
|
||
|
|
if table == 'sqlite_sequence':
|
||
|
|
continue
|
||
|
|
|
||
|
|
# Get table creation SQL
|
||
|
|
cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table}'")
|
||
|
|
create_sql = cursor.fetchone()[0]
|
||
|
|
|
||
|
|
# Get row count
|
||
|
|
cursor.execute(f'SELECT COUNT(*) FROM {table}')
|
||
|
|
count = cursor.fetchone()[0]
|
||
|
|
|
||
|
|
# Get column count
|
||
|
|
cursor.execute(f'PRAGMA table_info({table})')
|
||
|
|
cols = cursor.fetchall()
|
||
|
|
|
||
|
|
print(f'TABLE: {table}')
|
||
|
|
print(f'Rows: {count:,} | Columns: {len(cols)}')
|
||
|
|
print('-'*80)
|
||
|
|
print(create_sql + ';')
|
||
|
|
print()
|
||
|
|
|
||
|
|
# Show column details
|
||
|
|
print('COLUMNS:')
|
||
|
|
for col in cols:
|
||
|
|
col_id, col_name, col_type, not_null, default_val, pk = col
|
||
|
|
pk_marker = ' [PK]' if pk else ''
|
||
|
|
notnull_marker = ' NOT NULL' if not_null else ''
|
||
|
|
default_marker = f' DEFAULT {default_val}' if default_val else ''
|
||
|
|
print(f' {col_name:30s} {col_type:15s}{pk_marker}{notnull_marker}{default_marker}')
|
||
|
|
print()
|
||
|
|
print()
|
||
|
|
|
||
|
|
conn.close()
|