48 lines
1.3 KiB
Python
48 lines
1.3 KiB
Python
import sys
|
|
from pathlib import Path
|
|
|
|
# Add parent directory to Python path so we can import helpers
|
|
parent_dir = Path(__file__).parent.parent
|
|
sys.path.insert(0, str(parent_dir))
|
|
|
|
from helpers.config import get_db_connection
|
|
|
|
# Output folder: ITSA/Database Backups/SQL Views/
|
|
ITSA_ROOT = Path(__file__).parent.parent.parent.parent
|
|
OUTPUT_DIR = ITSA_ROOT / "Database Backups" / "SQL Views"
|
|
|
|
|
|
def dump_views():
|
|
OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
|
|
|
|
conn = get_db_connection()
|
|
cur = conn.cursor()
|
|
|
|
# Fetch all user-defined views (exclude system schemas)
|
|
cur.execute("""
|
|
SELECT schemaname, viewname, definition
|
|
FROM pg_views
|
|
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
|
|
ORDER BY schemaname, viewname
|
|
""")
|
|
|
|
views = cur.fetchall()
|
|
print(f"Found {len(views)} views\n")
|
|
|
|
for schema, view_name, definition in views:
|
|
sql = f"CREATE OR REPLACE VIEW {schema}.{view_name} AS\n{definition.rstrip()};\n"
|
|
|
|
filename = f"{schema}.{view_name}.sql" if schema != "public" else f"{view_name}.sql"
|
|
filepath = OUTPUT_DIR / filename
|
|
|
|
filepath.write_text(sql, encoding="utf-8")
|
|
print(f" Dumped: {filename}")
|
|
|
|
cur.close()
|
|
conn.close()
|
|
print(f"\nDone — {len(views)} views saved to:\n {OUTPUT_DIR}")
|
|
|
|
|
|
if __name__ == "__main__":
|
|
dump_views()
|