80 lines
2.4 KiB
Python
80 lines
2.4 KiB
Python
import sqlite3
|
|
import os
|
|
import sys
|
|
|
|
# Locate database
|
|
possible_paths = [
|
|
os.path.join(os.path.dirname(__file__), 'instance', 'edu_space.db'),
|
|
os.path.join(os.path.dirname(__file__), 'instance', 'app.db'),
|
|
os.path.join(os.path.dirname(__file__), 'edu_space.db'),
|
|
os.path.join(os.path.dirname(__file__), 'app.db'),
|
|
]
|
|
|
|
db_path = None
|
|
for p in possible_paths:
|
|
if os.path.exists(p):
|
|
db_path = p
|
|
break
|
|
|
|
if not db_path:
|
|
# Check current directory
|
|
for root, dirs, files in os.walk(os.path.dirname(__file__)):
|
|
for f in files:
|
|
if f.endswith('.db'):
|
|
db_path = os.path.join(root, f)
|
|
break
|
|
if db_path:
|
|
break
|
|
|
|
print(f"Connecting to database: {db_path}")
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
# Check existing columns in users table
|
|
cursor.execute("PRAGMA table_info(users)")
|
|
columns = [row[1] for row in cursor.fetchall()]
|
|
print(f"Existing columns in 'users': {columns}")
|
|
|
|
new_columns = [
|
|
('first_name', 'VARCHAR(100)'),
|
|
('last_name', 'VARCHAR(100)'),
|
|
('phone', 'VARCHAR(50)'),
|
|
('address', 'VARCHAR(255)'),
|
|
('document_type', 'VARCHAR(20) DEFAULT "DNI"'),
|
|
('document_number', 'VARCHAR(30)')
|
|
]
|
|
|
|
for col_name, col_type in new_columns:
|
|
if col_name not in columns:
|
|
print(f"Adding column '{col_name}'...")
|
|
cursor.execute(f"ALTER TABLE users ADD COLUMN {col_name} {col_type}")
|
|
else:
|
|
print(f"Column '{col_name}' already exists.")
|
|
|
|
conn.commit()
|
|
|
|
# Create index on document_number if not exists
|
|
try:
|
|
cursor.execute("CREATE INDEX IF NOT EXISTS idx_users_document_number ON users(document_number)")
|
|
conn.commit()
|
|
print("Index on document_number verified.")
|
|
except Exception as e:
|
|
print(f"Notice on index: {e}")
|
|
|
|
# Migrate existing users: split name into first_name and last_name if empty
|
|
cursor.execute("SELECT id, name, first_name, last_name FROM users")
|
|
users = cursor.fetchall()
|
|
migrated = 0
|
|
for u_id, name, fn, ln in users:
|
|
if not fn and name:
|
|
parts = name.strip().split()
|
|
first_n = parts[0] if parts else ''
|
|
last_n = ' '.join(parts[1:]) if len(parts) > 1 else ''
|
|
cursor.execute("UPDATE users SET first_name = ?, last_name = ? WHERE id = ?", (first_n, last_n, u_id))
|
|
migrated += 1
|
|
|
|
conn.commit()
|
|
print(f"Successfully migrated {migrated} users with first_name and last_name from name.")
|
|
conn.close()
|
|
print("Migration completed successfully!")
|