File size: 4,112 Bytes
d69447e |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
"""
Database initialization script for FleetMind
Run this script to create the PostgreSQL database and tables
"""
import sys
from pathlib import Path
# Add parent directory to path to import database module
sys.path.insert(0, str(Path(__file__).parent.parent))
from database.connection import init_database, get_db_connection, test_connection
from database.schema import get_full_schema
import logging
logging.basicConfig(
level=logging.INFO,
format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)
def main():
"""Initialize the FleetMind PostgreSQL database"""
try:
logger.info("=" * 60)
logger.info("FleetMind Database Initialization")
logger.info("=" * 60)
# Test connection first
logger.info("\nStep 1: Testing PostgreSQL connection...")
if not test_connection():
logger.error("Failed to connect to PostgreSQL database")
logger.error("Please check your .env file and ensure PostgreSQL is running")
sys.exit(1)
logger.info("✓ PostgreSQL connection successful")
# Initialize database with full schema
logger.info("\nStep 2: Creating database schema...")
init_database()
# Verify tables were created
logger.info("\nStep 3: Verifying table creation...")
conn = get_db_connection()
cursor = conn.cursor()
# Get list of all tables
cursor.execute("""
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name
""")
tables = cursor.fetchall()
if tables:
logger.info(f"✓ Successfully created {len(tables)} tables:")
for table in tables:
logger.info(f" - {table['table_name']}")
# Get column count for each table
cursor.execute(f"""
SELECT COUNT(*) as column_count
FROM information_schema.columns
WHERE table_name = %s
""", (table['table_name'],))
col_count = cursor.fetchone()
logger.info(f" ({col_count['column_count']} columns)")
else:
logger.warning("No tables were created")
# Specifically check orders table
logger.info("\nStep 4: Verifying orders table...")
cursor.execute("""
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position
""")
columns = cursor.fetchall()
if columns:
logger.info(f"✓ Orders table created with {len(columns)} columns:")
for col in columns[:10]: # Show first 10 columns
nullable = "NULL" if col['is_nullable'] == 'YES' else "NOT NULL"
logger.info(f" - {col['column_name']}: {col['data_type']} ({nullable})")
if len(columns) > 10:
logger.info(f" ... and {len(columns) - 10} more columns")
else:
logger.error("✗ Orders table was not created")
conn.close()
logger.info("\n" + "=" * 60)
logger.info("Database initialization completed successfully!")
logger.info("=" * 60)
logger.info("\nYou can now:")
logger.info(" 1. Test the database: python scripts/test_db.py")
logger.info(" 2. Start the application: python ui/app.py")
except Exception as e:
logger.error(f"\n✗ Database initialization failed: {e}")
logger.error("\nTroubleshooting:")
logger.error(" 1. Ensure PostgreSQL is installed and running")
logger.error(" 2. Create a database named 'fleetmind' in PostgreSQL")
logger.error(" Command: createdb fleetmind")
logger.error(" 3. Update .env file with correct database credentials")
logger.error(" 4. Verify PostgreSQL user has necessary permissions")
sys.exit(1)
if __name__ == "__main__":
main()
|