File size: 9,685 Bytes
d34ac77
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
"""
Migration script to migrate data from SQLite to PostgreSQL.

This script:
1. Reads all products from SQLite database
2. Creates PostgreSQL tables with pgvector extension
3. Migrates all product data to PostgreSQL
4. Optionally migrates vector embeddings to pgvector

Usage:
    # Using command line argument:
    python scripts/migrate_to_postgres.py \
        --sqlite-path app/data/products.db \
        --postgres-url postgresql://user:password@host:port/database
    
    # Using environment variable (recommended for Neon):
    export DATABASE_CONNECTION_STRING="postgresql://user:password@host:port/database"
    python scripts/migrate_to_postgres.py --sqlite-path app/data/products.db

For Neon PostgreSQL:
    1. Create a project at https://neon.tech
    2. Copy the connection string from the dashboard
    3. Use it as --postgres-url or set DATABASE_CONNECTION_STRING env var
    4. Neon automatically includes pgvector extension
"""

import argparse
import sqlite3
import json
import sys
import os
from pathlib import Path
from typing import List, Dict, Any, Optional
from datetime import datetime

try:
    import psycopg2
    from psycopg2.extras import execute_values
    from psycopg2 import sql
except ImportError:
    print("ERROR: psycopg2 is required. Install it with: pip install psycopg2")
    print("Note: You may also need: apt-get install libpq-dev python3-dev (Linux) or brew install postgresql (macOS)")
    sys.exit(1)


def create_postgres_tables(conn, vector_dimension: int = 768) -> None:
    """Create PostgreSQL tables with pgvector extension."""
    with conn.cursor() as cur:
        # Enable pgvector extension
        cur.execute("CREATE EXTENSION IF NOT EXISTS vector;")
        
        # Create products table
        cur.execute("""
            CREATE TABLE IF NOT EXISTS products (
                id VARCHAR(255) PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT,
                price REAL NOT NULL,
                currency VARCHAR(10) DEFAULT 'XAF',
                category TEXT NOT NULL,
                brand TEXT,
                image_url TEXT NOT NULL,
                availability VARCHAR(50) DEFAULT 'in_stock',
                rating REAL,
                review_count INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                metadata JSONB
            )
        """)
        
        # Create indexes
        cur.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)")
        cur.execute("CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand)")
        cur.execute("CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)")
        cur.execute("CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating)")
        cur.execute("CREATE INDEX IF NOT EXISTS idx_products_availability ON products(availability)")
        
        # Create vector embeddings table with pgvector
        cur.execute(f"""
            CREATE TABLE IF NOT EXISTS product_embeddings (
                product_id VARCHAR(255) PRIMARY KEY,
                vector vector({vector_dimension}),
                metadata JSONB,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
            )
        """)
        
        # Create vector index for similarity search
        cur.execute("""
            CREATE INDEX IF NOT EXISTS product_embeddings_vector_idx 
            ON product_embeddings 
            USING ivfflat (vector vector_cosine_ops)
            WITH (lists = 100)
        """)
        
        conn.commit()
        print("✓ PostgreSQL tables created successfully")


def migrate_products(sqlite_path: str, postgres_url: str) -> int:
    """Migrate products from SQLite to PostgreSQL."""
    # Connect to SQLite
    sqlite_conn = sqlite3.connect(sqlite_path)
    sqlite_conn.row_factory = sqlite3.Row
    
    # Connect to PostgreSQL
    pg_conn = psycopg2.connect(postgres_url)
    
    try:
        # Create tables
        create_postgres_tables(pg_conn)
        
        # Read all products from SQLite
        cursor = sqlite_conn.execute("SELECT * FROM products")
        products = cursor.fetchall()
        
        if not products:
            print("No products found in SQLite database")
            return 0
        
        print(f"Found {len(products)} products to migrate")
        
        # Prepare data for bulk insert
        products_data = []
        for row in products:
            metadata_json = None
            if row['metadata']:
                try:
                    metadata_json = json.loads(row['metadata'])
                except:
                    metadata_json = {}
            
            products_data.append((
                row['id'],
                row['title'],
                row['description'],
                row['price'],
                row.get('currency', 'XAF'),
                row['category'],
                row.get('brand'),
                row['image_url'],
                row.get('availability', 'in_stock'),
                row.get('rating'),
                row.get('review_count'),
                row.get('created_at'),
                row.get('updated_at'),
                json.dumps(metadata_json) if metadata_json else None
            ))
        
        # Bulk insert into PostgreSQL
        with pg_conn.cursor() as cur:
            insert_query = """
                INSERT INTO products 
                (id, title, description, price, currency, category, brand, image_url, 
                 availability, rating, review_count, created_at, updated_at, metadata)
                VALUES %s
                ON CONFLICT (id) DO UPDATE SET
                    title = EXCLUDED.title,
                    description = EXCLUDED.description,
                    price = EXCLUDED.price,
                    currency = EXCLUDED.currency,
                    category = EXCLUDED.category,
                    brand = EXCLUDED.brand,
                    image_url = EXCLUDED.image_url,
                    availability = EXCLUDED.availability,
                    rating = EXCLUDED.rating,
                    review_count = EXCLUDED.review_count,
                    updated_at = EXCLUDED.updated_at,
                    metadata = EXCLUDED.metadata
            """
            
            execute_values(
                cur,
                insert_query,
                products_data,
                template=None,
                page_size=100
            )
            pg_conn.commit()
        
        print(f"✓ Migrated {len(products)} products to PostgreSQL")
        return len(products)
        
    finally:
        sqlite_conn.close()
        pg_conn.close()


def main():
    parser = argparse.ArgumentParser(description="Migrate SQLite database to PostgreSQL")
    parser.add_argument(
        "--sqlite-path",
        type=str,
        default="app/data/products.db",
        help="Path to SQLite database file"
    )
    parser.add_argument(
        "--postgres-url",
        type=str,
        default=None,
        help="PostgreSQL connection URL (postgresql://user:password@host:port/database). "
             "If not provided, will use DATABASE_CONNECTION_STRING environment variable."
    )
    parser.add_argument(
        "--vector-dimension",
        type=int,
        default=768,
        help="Vector dimension for pgvector (default: 768 for clip-ViT-L-14)"
    )
    
    args = parser.parse_args()
    
    # Get PostgreSQL URL from argument or environment variable
    postgres_url = args.postgres_url or os.getenv("DATABASE_CONNECTION_STRING")
    if not postgres_url:
        print("ERROR: PostgreSQL connection URL is required!")
        print("Either provide --postgres-url argument or set DATABASE_CONNECTION_STRING environment variable")
        print("\nExample:")
        print("  export DATABASE_CONNECTION_STRING='postgresql://user:password@host:port/database'")
        print("  python scripts/migrate_to_postgres.py")
        sys.exit(1)
    
    # Ensure it's a PostgreSQL URL
    if not postgres_url.lower().startswith("postgresql://") and not postgres_url.lower().startswith("postgres://"):
        print("ERROR: Connection string must start with 'postgresql://' or 'postgres://'")
        print(f"Got: {postgres_url[:20]}...")
        sys.exit(1)
    
    # Check if SQLite file exists
    if not Path(args.sqlite_path).exists():
        print(f"ERROR: SQLite database not found at {args.sqlite_path}")
        sys.exit(1)
    
    print(f"Starting migration from {args.sqlite_path} to PostgreSQL...")
    # Hide credentials in output - show only host and database
    if "@" in postgres_url:
        url_parts = postgres_url.split("@")
        if len(url_parts) > 1:
            print(f"PostgreSQL: {url_parts[-1]}")
    else:
        print("PostgreSQL: [connection string provided]")
    
    try:
        count = migrate_products(args.sqlite_path, postgres_url)
        print(f"\n✓ Migration completed successfully! Migrated {count} products.")
        print("\nNext steps:")
        print("1. Update DATABASE_CONNECTION_STRING in your environment variables")
        print("2. Set VECTOR_STORE_TYPE=pgvector")
        print("3. Set PGVECTOR_CONNECTION_STRING to the same PostgreSQL URL")
        print("4. Restart your application to use PostgreSQL")
    except Exception as e:
        print(f"ERROR: Migration failed: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)


if __name__ == "__main__":
    main()