shopdb/sql/archive/migration_phase3

Phase 3 Migration: Network Devices to machines Table

Created: 2025-11-10 Status: Ready for Testing Follows: Phase 2 (PC Migration - Completed Successfully)

---

Overview

This migration consolidates all network infrastructure devices (servers, switches, cameras, access points, IDFs) into the unified machines table, completing the infrastructure unification started with Phase 2.

What Gets Migrated:

• Servers → machines (machinetypeid 30)
• Switches → machines (machinetypeid 31)
• Cameras → machines (machinetypeid 32)
• Access Points → machines (machinetypeid 33) if table exists
• IDFs → machines (machinetypeid 34) if table exists

What Stays Separate:

• Printers → Remain in printers table (by design)

---

Migration Scripts

01_create_network_machinetypes.sql          Creates machinetypes 30-36
02_migrate_servers_to_machines.sql          Migrates servers table
03_migrate_switches_to_machines.sql         Migrates switches table
04_migrate_cameras_to_machines.sql          Migrates cameras table
07_migrate_network_communications.sql       Migrates IP addresses
08_create_network_relationship_types.sql    Creates new relationship types
09_update_views_for_network_devices.sql     Creates/updates views

VERIFY_PHASE3_MIGRATION.sql                 Comprehensive verification
RUN_ALL_PHASE3_SCRIPTS.sql                  Executes all scripts in order
ROLLBACK_PHASE3.sql                         Emergency rollback

---

Quick Start

Prerequisites:

# 1. Backup database
mysqldump -u root -p shopdb > shopdb_backup_$(date +%Y%m%d).sql

# 2. Verify Phase 2 is stable
mysql -u root -p shopdb < ../VERIFY_PHASE2.sql  # if available

# 3. Check current state
mysql -u root -p shopdb -e "SELECT COUNT(*) FROM servers; SELECT COUNT(*) FROM switches; SELECT COUNT(*) FROM cameras;"

Test on Backup First:

# 1. Create test database
mysql -u root -p -e "CREATE DATABASE shopdb_test;"
mysql -u root -p shopdb_test < shopdb_backup_YYYYMMDD.sql

# 2. Run migration on test
mysql -u root -p shopdb_test < RUN_ALL_PHASE3_SCRIPTS.sql

# 3. Review results
mysql -u root -p shopdb_test < VERIFY_PHASE3_MIGRATION.sql

Production Migration:

# Only after successful test!
mysql -u root -p shopdb < RUN_ALL_PHASE3_SCRIPTS.sql

---

Expected Results

Before Migration:

servers:   50 records
switches:  20 records
cameras:   30 records
Total:     100 devices

After Migration:

machines (machinetypeid 30): 50 servers
machines (machinetypeid 31): 20 switches
machines (machinetypeid 32): 30 cameras
Total:                       100 devices

communications:  100+ IP addresses
relationshiptypes: +5 new types
views: 3 new views created

---

Verification Checklist

Run VERIFY_PHASE3_MIGRATION.sql and check for:

• All record counts match (old vs new tables)
• All 7 machinetypes created (30-36)
• No NULL required fields
• All IP addresses migrated
• 5 new relationship types created
• 3 views created successfully
• No duplicate serial numbers
• No duplicate machinenumbers
• Old tables preserved (rollback safety)
• All tests show "PASS"

---

Relationship Examples

Camera → Switch → IDF

-- Camera connected to switch
INSERT INTO machinerelationships (machineid, related_machineid, relationshiptypeid)
SELECT
    (SELECT machineid FROM machines WHERE machinenumber = 'CAM-Shop-01'),
    (SELECT machineid FROM machines WHERE machinenumber = 'SW-Core-01'),
    (SELECT relationshiptypeid FROM relationshiptypes WHERE relationshiptype = 'Connected To');

-- Switch connected to IDF
INSERT INTO machinerelationships (machineid, related_machineid, relationshiptypeid)
SELECT
    (SELECT machineid FROM machines WHERE machinenumber = 'SW-Core-01'),
    (SELECT machineid FROM machines WHERE machinenumber = 'IDF-Building-A'),
    (SELECT relationshiptypeid FROM relationshiptypes WHERE relationshiptype = 'Connected To');

Query Network Topology

-- Show all cameras and their connections
SELECT
    cam.machinenumber AS camera,
    sw.machinenumber AS switch,
    idf.machinenumber AS idf,
    cam_ip.address AS camera_ip
FROM machines cam
-- Camera to Switch
JOIN machinerelationships mr1 ON cam.machineid = mr1.machineid
JOIN relationshiptypes rt1 ON mr1.relationshiptypeid = rt1.relationshiptypeid AND rt1.relationshiptype = 'Connected To'
JOIN machines sw ON mr1.related_machineid = sw.machineid AND sw.machinetypeid = 31
-- Switch to IDF
LEFT JOIN machinerelationships mr2 ON sw.machineid = mr2.machineid
LEFT JOIN relationshiptypes rt2 ON mr2.relationshiptypeid = rt2.relationshiptypeid AND rt2.relationshiptype = 'Connected To'
LEFT JOIN machines idf ON mr2.related_machineid = idf.machineid AND idf.machinetypeid = 34
-- IP address
LEFT JOIN communications cam_ip ON cam.machineid = cam_ip.machineid AND cam_ip.isprimary = 1
WHERE cam.machinetypeid = 32;

---

Useful Queries

All Network Devices

SELECT * FROM vw_network_devices_summary;

All Infrastructure (Equipment + PCs + Network)

SELECT * FROM vw_all_infrastructure
WHERE device_category = 'Network Device';

Network Devices on Specific Subnet

SELECT m.machinenumber, mt.machinetype, c.address
FROM machines m
JOIN machinetypes mt ON m.machinetypeid = mt.machinetypeid
JOIN communications c ON m.machineid = c.machineid
WHERE mt.machinetypeid BETWEEN 30 AND 36
AND c.address LIKE '192.168.1.%';

Network Topology

SELECT * FROM vw_network_topology
WHERE relationshiptype = 'Connected To';

---

Troubleshooting

Migration Fails:

1. Review error message
2. Check if old tables exist
3. Run VERIFY script to identify issue
4. Run ROLLBACK if needed
5. Fix issue and retry

Rollback:

# 1. Open ROLLBACK script
# 2. Uncomment rollback steps
# 3. Run rollback
mysql -u root -p shopdb < ROLLBACK_PHASE3.sql

# 4. Verify rollback
mysql -u root -p shopdb -e "SELECT COUNT(*) FROM machines WHERE machinetypeid BETWEEN 30 AND 36;"
# Should return 0

Emergency Restore:

# If rollback doesn't work, restore from backup
mysql -u root -p shopdb < shopdb_backup_YYYYMMDD.sql

---

Post-Migration Tasks

Immediate (After Successful Migration):

1. Run verification script
2. Test application pages
3. Verify relationships display correctly
4. Check all device types accessible

Short-Term (1-7 Days):

1. Update displaymachines.asp (add network device filters)
2. Update machine_edit.asp (if needed)
3. Test adding new network devices
4. Monitor for issues

Long-Term (30+ Days):

1. Confirm migration stable
2. Drop old tables:

   DROP TABLE servers;
   DROP TABLE switches;
   DROP TABLE cameras;
   

3. Update documentation
4. Celebrate!

---

Important Notes

Data Preserved:

• All serial numbers
• All IP addresses
• All descriptions
• All map coordinates
• All models/vendors
• All active/inactive states

Data Generated:

• machinenumber: Generated as SVR-XXXX, SW-XXXX, CAM-XXXX
• alias: Populated from description field
• pctypeid: Set to NULL (not PCs)
• machinetypeid: Set to 30-36 based on device type

Not Migrated (Old Tables Don't Have):

• MAC addresses (would be NULL)
• Business units (not applicable)
• Detailed notes (field empty in old tables)

---

📞 Support

Issues?

1. Check VERIFY_PHASE3_MIGRATION.sql results
2. Review error logs
3. Check old tables still exist
4. Run ROLLBACK if needed
5. Contact database administrator

Questions?

• Review docs/PHASE3_NETWORK_DEVICES_MIGRATION_PLAN.md
• Check SESSION_SUMMARY_2025-11-10.md
• Review Phase 2 migration (similar pattern)

---

✨ Benefits After Migration

Unified Data Model:

• Single query for all infrastructure
• Consistent filtering and searching
• Better reporting capabilities

Powerful Relationships:

• Camera → Switch → IDF topology
• Network device dependencies
• Better visualization options

Better Maintenance:

• Less code duplication
• Easier to add new device types
• Consistent UI/UX
• CMDB-style asset management

---

Ready to migrate? Start with testing on backup database!

# Test first!
mysql -u root -p shopdb_test < RUN_ALL_PHASE3_SCRIPTS.sql

**Good luck! **