243 lines
7.9 KiB
Python
243 lines
7.9 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
PyraFiles Migration Script: is_public to visibility column
|
|
=========================================================
|
|
|
|
This script migrates existing PyraFiles databases from the old is_public boolean column
|
|
to the new visibility string column with three states: 'public', 'private', 'unlisted'.
|
|
|
|
Migration logic:
|
|
- is_public = True → visibility = 'public'
|
|
- is_public = False → visibility = 'private'
|
|
- All is_public values are then set to FALSE (for compatibility)
|
|
|
|
The is_public column is kept in the database but set to a consistent value.
|
|
|
|
Usage:
|
|
python migrate_visibility.py [--data-dir /path/to/data] [--dry-run]
|
|
|
|
Options:
|
|
--data-dir Path to PyraFiles data directory (default: ./data)
|
|
--dry-run Show what would be migrated without making changes
|
|
--help Show this help message
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
import argparse
|
|
import sqlite3
|
|
import glob
|
|
import logging
|
|
from pathlib import Path
|
|
|
|
# Set up logging
|
|
logging.basicConfig(
|
|
level=logging.INFO,
|
|
format='%(asctime)s - %(levelname)s - %(message)s'
|
|
)
|
|
log = logging.getLogger(__name__)
|
|
|
|
|
|
def check_table_structure(cursor, table_name):
|
|
"""Check if table exists and get its column information."""
|
|
cursor.execute(f"PRAGMA table_info({table_name})")
|
|
columns = cursor.fetchall()
|
|
column_names = [col[1] for col in columns]
|
|
return columns, column_names
|
|
|
|
|
|
def migrate_database(db_path, dry_run=False):
|
|
"""Migrate a single database file."""
|
|
log.info(f"Processing database: {db_path}")
|
|
|
|
try:
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
# Check if media table exists
|
|
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='media'")
|
|
if not cursor.fetchone():
|
|
log.info(f" No media table found in {db_path}, skipping")
|
|
conn.close()
|
|
return True
|
|
|
|
# Get current table structure
|
|
columns, column_names = check_table_structure(cursor, 'media')
|
|
|
|
has_is_public = 'is_public' in column_names
|
|
has_visibility = 'visibility' in column_names
|
|
|
|
log.info(f" Table structure: has_is_public={has_is_public}, has_visibility={has_visibility}")
|
|
|
|
if not has_is_public and has_visibility:
|
|
log.info(f" Database already migrated, skipping")
|
|
conn.close()
|
|
return True
|
|
|
|
if not has_is_public:
|
|
log.warning(f" No is_public column found in {db_path}, skipping")
|
|
conn.close()
|
|
return True
|
|
|
|
# Get count of records to migrate
|
|
cursor.execute("SELECT COUNT(*) FROM media")
|
|
total_records = cursor.fetchone()[0]
|
|
|
|
if total_records == 0:
|
|
log.info(f" No records to migrate in {db_path}")
|
|
conn.close()
|
|
return True
|
|
|
|
log.info(f" Found {total_records} records to migrate")
|
|
|
|
if dry_run:
|
|
# Show what would be migrated
|
|
cursor.execute("SELECT id, filename, is_public FROM media LIMIT 10")
|
|
sample_records = cursor.fetchall()
|
|
log.info(f" Sample records that would be migrated:")
|
|
for record in sample_records:
|
|
id_val, filename, is_public = record
|
|
new_visibility = 'public' if is_public else 'private'
|
|
log.info(f" {filename}: is_public={is_public} → visibility='{new_visibility}'")
|
|
if total_records > 10:
|
|
log.info(f" ... and {total_records - 10} more records")
|
|
|
|
conn.close()
|
|
return True
|
|
|
|
# Perform the actual migration
|
|
log.info(f" Starting migration...")
|
|
|
|
# Step 1: Add visibility column if it doesn't exist
|
|
if not has_visibility:
|
|
log.info(f" Adding visibility column...")
|
|
cursor.execute("ALTER TABLE media ADD COLUMN visibility TEXT DEFAULT 'public'")
|
|
|
|
# Step 2: Migrate data
|
|
log.info(f" Migrating data...")
|
|
cursor.execute("""
|
|
UPDATE media
|
|
SET visibility = CASE
|
|
WHEN is_public = 1 THEN 'public'
|
|
WHEN is_public = 0 THEN 'private'
|
|
ELSE 'public'
|
|
END
|
|
""")
|
|
|
|
migrated_count = cursor.rowcount
|
|
log.info(f" Updated {migrated_count} records")
|
|
|
|
# Step 3: Verify migration
|
|
cursor.execute("SELECT visibility, COUNT(*) FROM media GROUP BY visibility")
|
|
visibility_counts = cursor.fetchall()
|
|
log.info(f" Post-migration visibility distribution:")
|
|
for visibility, count in visibility_counts:
|
|
log.info(f" {visibility}: {count} records")
|
|
|
|
# Step 4: Set is_public to FALSE (0) for all records
|
|
# We'll keep the column but just set it to a consistent state
|
|
log.info(f" Setting is_public to FALSE for all records...")
|
|
cursor.execute("UPDATE media SET is_public = 0")
|
|
|
|
# Commit changes
|
|
conn.commit()
|
|
log.info(f" ✅ Migration completed successfully!")
|
|
|
|
except Exception as e:
|
|
log.error(f" ❌ Error migrating {db_path}: {e}")
|
|
if 'conn' in locals():
|
|
conn.rollback()
|
|
return False
|
|
finally:
|
|
if 'conn' in locals():
|
|
conn.close()
|
|
|
|
return True
|
|
|
|
|
|
def find_namespace_databases(data_dir):
|
|
"""Find all namespace database files."""
|
|
pattern = os.path.join(data_dir, "namespace_*.db")
|
|
namespace_dbs = glob.glob(pattern)
|
|
return namespace_dbs
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(
|
|
description='Migrate PyraFiles databases from is_public to visibility column',
|
|
formatter_class=argparse.RawDescriptionHelpFormatter,
|
|
epilog=__doc__
|
|
)
|
|
parser.add_argument(
|
|
'--data-dir',
|
|
default='./data',
|
|
help='Path to PyraFiles data directory (default: ./data)'
|
|
)
|
|
parser.add_argument(
|
|
'--dry-run',
|
|
action='store_true',
|
|
help='Show what would be migrated without making changes'
|
|
)
|
|
|
|
args = parser.parse_args()
|
|
|
|
data_dir = Path(args.data_dir)
|
|
|
|
if not data_dir.exists():
|
|
log.error(f"Data directory does not exist: {data_dir}")
|
|
sys.exit(1)
|
|
|
|
if not data_dir.is_dir():
|
|
log.error(f"Data directory is not a directory: {data_dir}")
|
|
sys.exit(1)
|
|
|
|
log.info(f"Starting PyraFiles migration...")
|
|
log.info(f"Data directory: {data_dir.absolute()}")
|
|
log.info(f"Dry run: {args.dry_run}")
|
|
|
|
# Find all namespace databases
|
|
namespace_dbs = find_namespace_databases(str(data_dir))
|
|
|
|
if not namespace_dbs:
|
|
log.info("No namespace databases found. Nothing to migrate.")
|
|
sys.exit(0)
|
|
|
|
log.info(f"Found {len(namespace_dbs)} namespace databases to check:")
|
|
for db_path in sorted(namespace_dbs):
|
|
log.info(f" {os.path.basename(db_path)}")
|
|
|
|
if args.dry_run:
|
|
log.info("\n" + "="*50)
|
|
log.info("DRY RUN MODE - No changes will be made")
|
|
log.info("="*50)
|
|
|
|
# Migrate each database
|
|
success_count = 0
|
|
error_count = 0
|
|
|
|
for db_path in sorted(namespace_dbs):
|
|
if migrate_database(db_path, dry_run=args.dry_run):
|
|
success_count += 1
|
|
else:
|
|
error_count += 1
|
|
|
|
# Summary
|
|
log.info("\n" + "="*50)
|
|
log.info("MIGRATION SUMMARY")
|
|
log.info("="*50)
|
|
log.info(f"Total databases: {len(namespace_dbs)}")
|
|
log.info(f"Successfully processed: {success_count}")
|
|
log.info(f"Errors: {error_count}")
|
|
|
|
if args.dry_run:
|
|
log.info("\nThis was a dry run. No changes were made.")
|
|
log.info("Run without --dry-run to perform the actual migration.")
|
|
elif error_count == 0:
|
|
log.info("\n✅ All databases migrated successfully!")
|
|
else:
|
|
log.warning(f"\n⚠️ {error_count} databases had errors. Check logs above.")
|
|
sys.exit(1)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|