Skip to content

README-SQLITE.md

Latest commit

 

History

History
129 lines (96 loc) · 3.51 KB

README-SQLITE.md

File metadata and controls

129 lines (96 loc) · 3.51 KB

DOE Mock APIs - SQLite Edition

Note: The SQLite database file is not included in the repository due to size constraints (262MB). The database is automatically generated from the CSV data during build/deployment.

🚀 Quick Start

1. Generate Database Locally

# Install dependencies
npm install

# Create SQLite database from CSV (required for first run)
npm run create-db

# Start SQLite-powered server
npm run start:sqlite

2. Docker Deployment (Recommended)

# Build and run with Docker Compose (database auto-generated)
npm run docker:compose

# Or build manually
npm run docker:build && npm run docker:run

📊 Database Information

  • Source: NYC_Chromebook_ASN_Import_350K.csv (~350K records)
  • Generated File: doe_devices.db (~262MB)
  • Tables Created:
    • devices - Original CSV data
    • intune_devices - Mock Intune API data
    • devicehub_assets - Mock DeviceHub API data

🌐 API Endpoints

  • Base URL: http://localhost:3200
  • Documentation: GET /
  • Health Check: GET /health
  • Intune API: GET /api/intune/deviceManagement/managedDevices
  • DeviceHub API: GET /api/devicehub/assets

Filter Examples

# Search by serial number
curl "http://localhost:3200/api/intune/deviceManagement/managedDevices?$filter=NYC-SN65467"

# Get DeviceHub asset by serial
curl "http://localhost:3200/api/devicehub/assets/serial/NYC-SN65467"

🔧 ServiceNow Integration

The ServiceNow integration scripts automatically use the local SQLite APIs:

// Updated in DOE_HardwareAssetIntegration.js
this.LOCAL_API_BASE_URL = 'http://localhost:3200';

Test with any serial from the CSV:

  • Update SERIAL_NUMBER_TO_TEST in DOE_SingleDeviceTest_BackgroundScript.js
  • All 350K devices are now available (e.g., NYC-SN65467)

🐳 Docker Build Process

The Docker containers automatically:

  1. Copy CSV data and database creation script
  2. Run Python script to generate SQLite database
  3. Start Node.js server with full dataset

No manual database creation needed in containerized deployments!

📁 File Structure

src/
├── server-sqlite.js           # SQLite-powered server
├── routes/
│   ├── intune-sqlite.js       # Intune API with database queries
│   └── devicehub-sqlite.js    # DeviceHub API with database queries
└── services/
    └── database.js            # Database connection and query service

create_database.py             # Database generation script
Dockerfile.multistage          # Optimized Docker build
docker-compose.sqlite.yml      # Docker Compose configuration
DEPLOYMENT-SQLITE.md           # Detailed deployment guide

🚨 Important Notes

  • Database Generation: Required on first local run (npm run create-db)
  • Docker Builds: Database automatically created during build
  • CSV Data: Must be present (NYC_Chromebook_ASN_Import_350K.csv)
  • Performance: SQLite provides much faster queries than CSV parsing
  • Storage: Database file ~262MB (excluded from git for size reasons)

🔍 Troubleshooting

Database Not Found

# Regenerate database
npm run create-db

Container Issues

# Check logs
docker logs doe-apis

# Verify health
curl http://localhost:3200/health

ServiceNow Testing

# Ensure API server is running
npm run start:sqlite

# Test specific serial numbers from CSV
# NYC-SN61131 to NYC-SN65467 (and many more)

Full documentation: See DEPLOYMENT-SQLITE.md for complete deployment options.