94b421f73a
- Move completed migration docs to docs/archive/ - Move session summaries to docs/archive/sessions/ - Rename API_ASP_DOCUMENTATION.md to docs/API.md - Archive redundant Claude reference files - Update docs/README.md as simplified index - Reduce active docs from 45+ files to 8 essential files Remaining docs: - CLAUDE.md (AI context) - TODO.md (task tracking) - docs/README.md, API.md, QUICK_REFERENCE.md - docs/ASP_DEVELOPMENT_GUIDE.md, STANDARDS.md 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
167 lines
4.6 KiB
Markdown
167 lines
4.6 KiB
Markdown
# Database Migration - Status Summary
|
|
|
|
**Last Updated:** 2025-11-25
|
|
**Current Phase:** Phase 2 COMPLETE, Phase 3 PLANNED
|
|
|
|
---
|
|
|
|
## Migration Status
|
|
|
|
| Phase | Status | Description | Completed |
|
|
|-------|--------|-------------|-----------|
|
|
| **Phase 1** | COMPLETE | Schema changes (tables, columns, indexes) | Nov 6, 2025 |
|
|
| **Phase 2** | COMPLETE | PC migration to machines table | Nov 10, 2025 |
|
|
| **Phase 3** | COMPLETE | Network devices - legacy tables dropped | Nov 25, 2025 |
|
|
|
|
---
|
|
|
|
## Phase 1: Schema Changes (COMPLETE)
|
|
|
|
**Completed:** November 6, 2025
|
|
|
|
### New Tables Created (7)
|
|
1. `comstypes` - Communication types (IP, Serial, etc.)
|
|
2. `communications` - Unified network interfaces
|
|
3. `warranties` - Warranty tracking
|
|
4. `compliance` - Compliance tracking
|
|
5. `compliancescans` - Scan history
|
|
6. `relationshiptypes` - Relationship type definitions
|
|
7. `machinerelationships` - Machine-to-machine relationships
|
|
|
|
### Tables Modified (2)
|
|
1. `machines` - Added 11 columns (hostname, serialnumber, osid, pctypeid, etc.)
|
|
2. `businessunits` - Added liaison fields
|
|
|
|
### Tables Renamed
|
|
- `pcstatus` → `machinestatus`
|
|
|
|
---
|
|
|
|
## Phase 2: PC Migration (COMPLETE)
|
|
|
|
**Completed:** November 10, 2025
|
|
|
|
### Data Migrated
|
|
- **277 PCs** migrated from `pc` table → `machines` table
|
|
- **705+ network interfaces** → `communications` table
|
|
- **Dualpath relationships** → `machinerelationships` table
|
|
- **PC→Equipment relationships** → `machinerelationships` table
|
|
|
|
### Schema Changes
|
|
- PCs identified by `pctypeid IS NOT NULL` in machines table
|
|
- Network interfaces use `communications.address` field
|
|
- Relationships use `machinerelationships` table
|
|
|
|
### Pages Updated
|
|
- displaypcs.asp, displaypc.asp, editpc.asp
|
|
- displaymachines.asp, displaymachine.asp
|
|
- network_map.asp, network_devices.asp
|
|
- All save/update device pages
|
|
|
|
### API Fixes
|
|
- Fixed 36+ IIf() bugs in api.asp
|
|
- Fixed PC→Machine relationship creation
|
|
- PowerShell data collection fully working
|
|
|
|
---
|
|
|
|
## Phase 3: Network Devices (COMPLETE)
|
|
|
|
**Completed:** November 25, 2025
|
|
|
|
### What Happened
|
|
- Legacy tables were essentially empty (only 3 servers had data)
|
|
- Network devices were already being added directly to `machines` table
|
|
- Dropped all legacy network device tables
|
|
|
|
### Tables Dropped
|
|
- `servers` (3 records - not migrated, stale data)
|
|
- `switches` (empty)
|
|
- `cameras` (empty)
|
|
- `accesspoints` (empty)
|
|
- `idfs` (empty)
|
|
|
|
### Current Network Device Types in machines Table
|
|
| machinetypeid | Type |
|
|
|---------------|------|
|
|
| 16 | Access Point |
|
|
| 17 | IDF |
|
|
| 18 | Camera |
|
|
| 19 | Switch |
|
|
| 20 | Server |
|
|
|
|
### Printers
|
|
- Stay in separate `printers` table (by design - unique fields, workflows)
|
|
|
|
---
|
|
|
|
## Current Architecture
|
|
|
|
```
|
|
machines table (unified)
|
|
├── Equipment (machinetypeid 1-24, pctypeid IS NULL)
|
|
├── PCs (machinetypeid 25-29, pctypeid IS NOT NULL)
|
|
└── [Future] Network Devices (machinetypeid 30-36)
|
|
|
|
printers table (separate)
|
|
|
|
communications table (all network interfaces)
|
|
|
|
machinerelationships table (all relationships)
|
|
```
|
|
|
|
---
|
|
|
|
## Key Queries
|
|
|
|
```sql
|
|
-- All PCs
|
|
SELECT * FROM machines WHERE pctypeid IS NOT NULL;
|
|
|
|
-- All Equipment (non-PC)
|
|
SELECT * FROM machines WHERE pctypeid IS NULL;
|
|
|
|
-- PCs with network info
|
|
SELECT m.hostname, m.serialnumber, c.address
|
|
FROM machines m
|
|
LEFT JOIN communications c ON m.machineid = c.machineid
|
|
WHERE m.pctypeid IS NOT NULL;
|
|
|
|
-- PC → Equipment relationships
|
|
SELECT
|
|
pc.hostname AS pc_name,
|
|
eq.machinenumber AS equipment_name
|
|
FROM machinerelationships mr
|
|
JOIN machines pc ON mr.machineid = pc.machineid
|
|
JOIN machines eq ON mr.related_machineid = eq.machineid
|
|
JOIN relationshiptypes rt ON mr.relationshiptypeid = rt.relationshiptypeid
|
|
WHERE rt.relationshiptype = 'Controls';
|
|
```
|
|
|
|
---
|
|
|
|
## Deprecated Tables (Phase 2)
|
|
|
|
These tables are deprecated but kept for rollback safety:
|
|
|
|
- `pc` → Use `machines WHERE pctypeid IS NOT NULL`
|
|
- `pc_network_interfaces` → Use `communications`
|
|
- `pc_comm_config` → Use `communications`
|
|
- `pc_dualpath_assignments` → Use `machinerelationships`
|
|
- `pcstatus` → Use `machinestatus`
|
|
|
|
**Recommendation:** Drop after 30 days of stable operation
|
|
|
|
---
|
|
|
|
## Related Documentation
|
|
|
|
- [DATABASE_MIGRATION_FINAL_DESIGN.md](DATABASE_MIGRATION_FINAL_DESIGN.md) - Phase 1 spec
|
|
- [PC_MACHINES_CONSOLIDATION_PLAN.md](PC_MACHINES_CONSOLIDATION_PLAN.md) - Phase 2 plan
|
|
- [PHASE3_NETWORK_DEVICES_MIGRATION_PLAN.md](PHASE3_NETWORK_DEVICES_MIGRATION_PLAN.md) - Phase 3 plan
|
|
- [MIGRATION_QUICK_REFERENCE.md](MIGRATION_QUICK_REFERENCE.md) - Quick lookup
|
|
|
|
---
|
|
|
|
**Last Updated:** 2025-11-25
|