95 lines
3.2 KiB
Python
95 lines
3.2 KiB
Python
"""
|
|
migration_legacy_attendance_remote_indexes.py
|
|
================================================
|
|
Adds missing indexes to the REMOTE legacy database (the one described by
|
|
QrCodeLtServices.sql — contract / employee / locations / records) so the
|
|
Legacy Attendance feature's live queries don't full-table-scan on every
|
|
page load.
|
|
|
|
As shipped, that schema has NO index on:
|
|
records.employeeId, records.locationId, records.time, records.contractId
|
|
employee.id (only the meaningless auto-increment `index` is indexed)
|
|
locations.location
|
|
|
|
Adding an index does not change or risk any existing data — it only
|
|
speeds up reads. Safe to re-run: each ALTER is skipped if the index
|
|
already exists.
|
|
|
|
Uses pymysql directly (never SQLAlchemy ORM), consistent with every other
|
|
migration script in tools/ — this connects to REMOTE_DB_* (the legacy
|
|
server), not the app's own local database.
|
|
|
|
Run once per server (LT and GOV each point at their own legacy DB):
|
|
python3 tools/migration_legacy_attendance_remote_indexes.py
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
|
|
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
|
|
|
|
from dotenv import load_dotenv
|
|
load_dotenv()
|
|
|
|
import pymysql
|
|
|
|
# (table, column, index_name)
|
|
INDEXES_TO_ADD = [
|
|
('records', 'employeeId', 'idx_records_employeeId'),
|
|
('records', 'locationId', 'idx_records_locationId'),
|
|
('records', 'time', 'idx_records_time'),
|
|
('records', 'contractId', 'idx_records_contractId'),
|
|
('employee', 'id', 'idx_employee_id'),
|
|
('locations', 'location', 'idx_locations_location'),
|
|
]
|
|
|
|
|
|
def get_connection():
|
|
host = os.environ.get('REMOTE_DB_HOST', '')
|
|
port = int(os.environ.get('REMOTE_DB_PORT', '3306'))
|
|
user = os.environ.get('REMOTE_DB_USERNAME', '')
|
|
password = os.environ.get('REMOTE_DB_PASSWORD', '')
|
|
database = os.environ.get('REMOTE_DB_NAME', '')
|
|
|
|
if not host or not database:
|
|
print("[ERROR] REMOTE_DB_HOST / REMOTE_DB_NAME not set in .env — aborting.")
|
|
sys.exit(1)
|
|
|
|
print(f"[INFO] Connecting to legacy DB {user}@{host}:{port}/{database} ...")
|
|
return pymysql.connect(
|
|
host=host, port=port, user=user, password=password, database=database,
|
|
charset='utf8mb4', connect_timeout=10
|
|
)
|
|
|
|
|
|
def index_exists(cursor, table, index_name):
|
|
cursor.execute("""
|
|
SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s
|
|
""", (table, index_name))
|
|
return cursor.fetchone()[0] > 0
|
|
|
|
|
|
def main():
|
|
conn = get_connection()
|
|
try:
|
|
with conn.cursor() as cur:
|
|
for table, column, index_name in INDEXES_TO_ADD:
|
|
if index_exists(cur, table, index_name):
|
|
print(f"[SKIP] {table}.{index_name} already exists")
|
|
continue
|
|
print(f"[ADD] {table}.{index_name} ON ({column}) ...")
|
|
cur.execute(f"ALTER TABLE `{table}` ADD INDEX `{index_name}` (`{column}`)")
|
|
conn.commit()
|
|
print(f"[OK] {table}.{index_name} created")
|
|
print("[DONE] Legacy database indexes are up to date.")
|
|
except pymysql.MySQLError as e:
|
|
print(f"[ERROR] {e}")
|
|
sys.exit(1)
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
if __name__ == '__main__':
|
|
main()
|