Function convert

convert_to_sqlite.py:347–422

()

def convert():
    print("Creating SQLite database with improved schema...")

    if os.path.exists(DB_FILE):
        os.remove(DB_FILE)

    db = sqlite3.connect(DB_FILE)
    create_sqlite_schema(db)
    
    # Disable FK checks during import for speed and to handle cross-references
    db.execute("PRAGMA foreign_keys = OFF")
    
    table_counts = {t: 0 for t in TABLE_ORDER}
    
    print(f"Reading {SQL_FILE}...")
    
    for table, columns, rows in stream_inserts(SQL_FILE):
        if table in TABLE_ORDER:
            ncols = len(columns)
            placeholders = ','.join(['?'] * ncols)
            insert_sql = f"INSERT INTO {table} VALUES ({placeholders})"
            
            db.executemany(insert_sql, rows)
            table_counts[table] += len(rows)
            if table_counts[table] <= len(rows) or table_counts[table] % 5000 == 0:
                print(f"  {table}: {table_counts[table]} rows...")
    
    # Re-enable FK checks
    db.execute("PRAGMA foreign_keys = ON")
    db.execute("PRAGMA foreign_key_check")
    db.commit()
    
    db.commit()
    
    # Populate lookup tables after data
    print("  Populating juzs and hizbs lookup tables...")
    populate_lookup_tables(db)
    
    # Create views
    db.execute("""
        CREATE VIEW IF NOT EXISTS surah_stats AS
        SELECT s.id, s.name_ar, s.name_en, s.name_en_translation, s.type,
               COUNT(a.id) as ayat_count,
               MIN(a.id) as start_ayah_id,
               MAX(a.id) as end_ayah_id
        FROM surahs s
        JOIN ayahs a ON a.surah_id = s.id
        GROUP BY s.id
    """)
    
    db.execute("""
        CREATE VIEW IF NOT EXISTS ayah_with_translation AS
        SELECT a.id, a.surah_id, a.number_in_surah, a.text as arabic,
               ae.data as translation, e.language, e.name as edition_name
        FROM ayahs a
        JOIN ayah_edition ae ON ae.ayah_id = a.id
        JOIN editions e ON e.id = ae.edition_id
    """)