""" Migrate machines table to assets + extension tables. This script migrates data from the legacy machines table to the new Asset architecture with plugin-owned extension tables. Strategy: 1. Preserve IDs: assets.assetid = original machines.machineid 2. Create asset record, then type-specific extension record 3. Map machine types to asset types: - MachineType = Equipment -> equipment extension - MachineType = PC -> computers extension - MachineType = Network/Camera/etc -> network_devices extension - Printers -> handled separately by printers plugin Usage: python -m scripts.migration.migrate_assets --source """ import argparse import logging from datetime import datetime from sqlalchemy import create_engine, text from sqlalchemy.orm import sessionmaker logging.basicConfig(level=logging.INFO) logger = logging.getLogger(__name__) def get_machine_type_mapping(): """Map legacy machine type IDs to asset types.""" return { # Equipment types 'CNC': 'equipment', 'CMM': 'equipment', 'Lathe': 'equipment', 'Grinder': 'equipment', 'EDM': 'equipment', 'Mill': 'equipment', 'Press': 'equipment', 'Robot': 'equipment', 'Part Marker': 'equipment', # PC types 'PC': 'computer', 'Workstation': 'computer', 'Laptop': 'computer', 'Server': 'computer', # Network types 'Switch': 'network_device', 'Router': 'network_device', 'Access Point': 'network_device', 'Camera': 'network_device', 'IDF': 'network_device', 'MDF': 'network_device', 'Firewall': 'network_device', } def migrate_machine_to_asset(machine_row, asset_type_id, target_session): """ Create an Asset record from a Machine record. Args: machine_row: Row from source machines table asset_type_id: Target asset type ID target_session: SQLAlchemy session for target database Returns: Created asset ID """ # Insert into assets table target_session.execute(text(""" INSERT INTO assets ( assetid, assetnumber, name, serialnumber, assettypeid, statusid, locationid, businessunitid, mapleft, maptop, notes, isactive, createddate, modifieddate ) VALUES ( :assetid, :assetnumber, :name, :serialnumber, :assettypeid, :statusid, :locationid, :businessunitid, :mapleft, :maptop, :notes, :isactive, :createddate, :modifieddate ) """), { 'assetid': machine_row['machineid'], 'assetnumber': machine_row['machinenumber'], 'name': machine_row.get('alias'), 'serialnumber': machine_row.get('serialnumber'), 'assettypeid': asset_type_id, 'statusid': machine_row.get('statusid', 1), 'locationid': machine_row.get('locationid'), 'businessunitid': machine_row.get('businessunitid'), 'mapleft': machine_row.get('mapleft'), 'maptop': machine_row.get('maptop'), 'notes': machine_row.get('notes'), 'isactive': machine_row.get('isactive', True), 'createddate': machine_row.get('createddate', datetime.utcnow()), 'modifieddate': machine_row.get('modifieddate', datetime.utcnow()), }) return machine_row['machineid'] def migrate_equipment(machine_row, asset_id, target_session): """Create equipment extension record.""" target_session.execute(text(""" INSERT INTO equipment ( assetid, equipmenttypeid, vendorid, modelnumberid, requiresmanualconfig, islocationonly, isactive, createddate ) VALUES ( :assetid, :equipmenttypeid, :vendorid, :modelnumberid, :requiresmanualconfig, :islocationonly, :isactive, :createddate ) """), { 'assetid': asset_id, 'equipmenttypeid': machine_row.get('machinetypeid'), # May need mapping 'vendorid': machine_row.get('vendorid'), 'modelnumberid': machine_row.get('modelnumberid'), 'requiresmanualconfig': machine_row.get('requiresmanualconfig', False), 'islocationonly': machine_row.get('islocationonly', False), 'isactive': True, 'createddate': datetime.utcnow(), }) def migrate_computer(machine_row, asset_id, target_session): """Create computer extension record.""" target_session.execute(text(""" INSERT INTO computers ( assetid, computertypeid, vendorid, operatingsystemid, hostname, currentuserid, lastuserid, lastboottime, lastzabbixsync, isvnc, isactive, createddate ) VALUES ( :assetid, :computertypeid, :vendorid, :operatingsystemid, :hostname, :currentuserid, :lastuserid, :lastboottime, :lastzabbixsync, :isvnc, :isactive, :createddate ) """), { 'assetid': asset_id, 'computertypeid': machine_row.get('pctypeid'), 'vendorid': machine_row.get('vendorid'), 'operatingsystemid': machine_row.get('operatingsystemid'), 'hostname': machine_row.get('hostname'), 'currentuserid': machine_row.get('currentuserid'), 'lastuserid': machine_row.get('lastuserid'), 'lastboottime': machine_row.get('lastboottime'), 'lastzabbixsync': machine_row.get('lastzabbixsync'), 'isvnc': machine_row.get('isvnc', False), 'isactive': True, 'createddate': datetime.utcnow(), }) def migrate_network_device(machine_row, asset_id, target_session): """Create network device extension record.""" target_session.execute(text(""" INSERT INTO networkdevices ( assetid, networkdevicetypeid, vendorid, hostname, firmwareversion, portcount, ispoe, ismanaged, isactive, createddate ) VALUES ( :assetid, :networkdevicetypeid, :vendorid, :hostname, :firmwareversion, :portcount, :ispoe, :ismanaged, :isactive, :createddate ) """), { 'assetid': asset_id, 'networkdevicetypeid': machine_row.get('machinetypeid'), # May need mapping 'vendorid': machine_row.get('vendorid'), 'hostname': machine_row.get('hostname'), 'firmwareversion': machine_row.get('firmwareversion'), 'portcount': machine_row.get('portcount'), 'ispoe': machine_row.get('ispoe', False), 'ismanaged': machine_row.get('ismanaged', False), 'isactive': True, 'createddate': datetime.utcnow(), }) def run_migration(source_conn_str, target_conn_str, dry_run=False): """ Run the full migration from machines to assets. Args: source_conn_str: Connection string for source (VBScript) database target_conn_str: Connection string for target (Flask) database dry_run: If True, don't commit changes """ source_engine = create_engine(source_conn_str) target_engine = create_engine(target_conn_str) SourceSession = sessionmaker(bind=source_engine) TargetSession = sessionmaker(bind=target_engine) source_session = SourceSession() target_session = TargetSession() try: # Get asset type mappings from target database asset_types = {} result = target_session.execute(text("SELECT assettypeid, assettype FROM assettypes")) for row in result: asset_types[row.assettype] = row.assettypeid # Get machine type to asset type mapping type_mapping = get_machine_type_mapping() # Fetch all machines from source machines = source_session.execute(text(""" SELECT m.*, mt.machinetype FROM machines m LEFT JOIN machinetypes mt ON m.machinetypeid = mt.machinetypeid """)) migrated = 0 errors = 0 for machine in machines: machine_dict = dict(machine._mapping) try: # Determine asset type machine_type_name = machine_dict.get('machinetype', '') asset_type_name = type_mapping.get(machine_type_name, 'equipment') asset_type_id = asset_types.get(asset_type_name) if not asset_type_id: logger.warning(f"Unknown asset type for machine {machine_dict['machineid']}: {machine_type_name}") errors += 1 continue # Create asset record asset_id = migrate_machine_to_asset(machine_dict, asset_type_id, target_session) # Create extension record based on type if asset_type_name == 'equipment': migrate_equipment(machine_dict, asset_id, target_session) elif asset_type_name == 'computer': migrate_computer(machine_dict, asset_id, target_session) elif asset_type_name == 'network_device': migrate_network_device(machine_dict, asset_id, target_session) migrated += 1 if migrated % 100 == 0: logger.info(f"Migrated {migrated} machines...") except Exception as e: logger.error(f"Error migrating machine {machine_dict.get('machineid')}: {e}") errors += 1 if dry_run: logger.info("Dry run - rolling back changes") target_session.rollback() else: target_session.commit() logger.info(f"Migration complete: {migrated} migrated, {errors} errors") finally: source_session.close() target_session.close() def main(): parser = argparse.ArgumentParser(description='Migrate machines to assets') parser.add_argument('--source', required=True, help='Source database connection string') parser.add_argument('--target', help='Target database connection string (default: app config)') parser.add_argument('--dry-run', action='store_true', help='Dry run without committing') args = parser.parse_args() target = args.target if not target: # Load from Flask config import os import sys sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.dirname(__file__)))) from shopdb import create_app app = create_app() target = app.config['SQLALCHEMY_DATABASE_URI'] run_migration(args.source, target, args.dry_run) if __name__ == '__main__': main()