File size: 8,083 Bytes
74bf532 | 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 | """
Migration script: Copy all data from local SQLite database (uraas.db)
to the production PostgreSQL database.
"""
import os
import sys
from sqlalchemy import create_engine, MetaData, text
from sqlalchemy.orm import sessionmaker
# Add project root to path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from uraas.database import Base, Community, Collection, Author, Item, File, item_authors, item_collections
def migrate():
# SQLite URL
sqlite_url = "sqlite:///uraas.db"
# Postgres URL (get from environment variable)
postgres_url = os.getenv("DATABASE_URL")
if not postgres_url:
print("[ERR] DATABASE_URL environment variable is not set!")
print("Please run this command with DATABASE_URL set, for example:")
print("DATABASE_URL=postgresql://user:pass@host:port/dbname python scripts/migrate_sqlite_to_postgres.py")
sys.exit(1)
# Standardize Render's postgres:// prefix to postgresql:// if needed
if postgres_url.startswith("postgres://"):
postgres_url = postgres_url.replace("postgres://", "postgresql://", 1)
print(f"Source SQLite database: {sqlite_url}")
print(f"Destination PostgreSQL database: {postgres_url.split('@')[-1] if '@' in postgres_url else postgres_url}")
print("\nInitializing connections...")
sqlite_engine = create_engine(sqlite_url)
postgres_engine = create_engine(postgres_url)
SqliteSession = sessionmaker(bind=sqlite_engine)
PostgresSession = sessionmaker(bind=postgres_engine)
sqlite_session = SqliteSession()
postgres_session = PostgresSession()
try:
print("Recreating destination database tables if they do not exist...")
Base.metadata.create_all(bind=postgres_engine)
print("Clearing existing data in PostgreSQL tables to prevent collisions...")
# Order matters for foreign key constraints
postgres_session.execute(text("TRUNCATE TABLE files, item_authors, item_collections, items, authors, collections, communities CASCADE"))
postgres_session.commit()
# 1. Migrate Communities
print("Migrating Communities...")
communities = sqlite_session.query(Community).all()
for comm in communities:
new_comm = Community(
id=comm.id,
name=comm.name,
ror_id=comm.ror_id,
institution=comm.institution,
ror=comm.ror
)
postgres_session.add(new_comm)
postgres_session.flush()
print(f" Migrated {len(communities)} communities.")
# 2. Migrate Collections
print("Migrating Collections...")
collections = sqlite_session.query(Collection).all()
for coll in collections:
new_coll = Collection(
id=coll.id,
community_id=coll.community_id,
name=coll.name,
email_domains=coll.email_domains,
keywords=coll.keywords
)
postgres_session.add(new_coll)
postgres_session.flush()
print(f" Migrated {len(collections)} collections.")
# 3. Migrate Authors
print("Migrating Authors...")
authors = sqlite_session.query(Author).all()
for auth in authors:
new_auth = Author(
id=auth.id,
name=auth.name,
normalized_name=auth.normalized_name,
profile_url=auth.profile_url,
orcid=auth.orcid,
ror=auth.ror
)
postgres_session.add(new_auth)
postgres_session.flush()
print(f" Migrated {len(authors)} authors.")
# 4. Migrate Items
print("Migrating Items...")
items = sqlite_session.query(Item).all()
for item in items:
new_item = Item(
id=item.id,
title=item.title,
abstract=item.abstract,
doi=item.doi,
publication_date=item.publication_date,
url=item.url,
source_repository=item.source_repository,
pdf_url=item.pdf_url,
dc_title=item.dc_title,
dc_date_issued=item.dc_date_issued,
dc_identifier_uri=item.dc_identifier_uri,
dc_identifier_doi=item.dc_identifier_doi,
dc_description_provenance=item.dc_description_provenance,
dc_rights=item.dc_rights,
dc_type=item.dc_type,
dc_language=item.dc_language,
dc_subject=item.dc_subject,
docid=item.docid,
docid_assigned_at=item.docid_assigned_at,
ror=item.ror,
institution=item.institution,
content_type=item.content_type,
tk_label=item.tk_label,
tk_community=item.tk_community,
patent_id=item.patent_id,
patent_date=item.patent_date,
language_code=item.language_code,
is_african_language=item.is_african_language,
sdg_tags=item.sdg_tags,
ai_keywords=item.ai_keywords,
special_collection_score=item.special_collection_score,
special_collection_categories=item.special_collection_categories,
created_at=item.created_at
)
postgres_session.add(new_item)
postgres_session.flush()
print(f" Migrated {len(items)} items.")
# 5. Migrate Files
print("Migrating Files...")
files = sqlite_session.query(File).all()
for file in files:
new_file = File(
id=file.id,
item_id=file.item_id,
file_path=file.file_path,
sha256_hash=file.sha256_hash,
access_policy=file.access_policy,
downloaded_at=file.downloaded_at
)
postgres_session.add(new_file)
postgres_session.flush()
print(f" Migrated {len(files)} files.")
# 6. Migrate association tables (item_authors and item_collections)
print("Migrating Item-Author associations...")
item_author_rows = sqlite_session.execute(item_authors.select()).all()
for row in item_author_rows:
postgres_session.execute(
item_authors.insert().values(item_id=row.item_id, author_id=row.author_id)
)
print(f" Migrated {len(item_author_rows)} item-author mappings.")
print("Migrating Item-Collection associations...")
item_coll_rows = sqlite_session.execute(item_collections.select()).all()
for row in item_coll_rows:
postgres_session.execute(
item_collections.insert().values(
item_id=row.item_id,
collection_id=row.collection_id,
confidence_score=row.confidence_score
)
)
print(f" Migrated {len(item_coll_rows)} item-collection mappings.")
postgres_session.commit()
print("[SUCCESS] Data migrated to PostgreSQL successfully!")
# Reset sequences in Postgres so future inserts don't collide
print("Resetting PostgreSQL primary key sequences...")
tables = ["communities", "collections", "authors", "items", "files"]
for table in tables:
postgres_session.execute(text(
f"SELECT setval(pg_get_serial_sequence('{table}', 'id'), COALESCE(MAX(id), 1) + 1) FROM {table}"
))
postgres_session.commit()
print("[SUCCESS] Sequences advanced.")
except Exception as e:
print(f"[ERR] Migration failed: {e}")
postgres_session.rollback()
raise
finally:
sqlite_session.close()
postgres_session.close()
if __name__ == "__main__":
migrate()
|