Spaces:
Sleeping
Sleeping
File size: 12,532 Bytes
429a26d |
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 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 |
#!/usr/bin/env python3
"""
Database migration script to add missing columns
"""
import sqlite3
import os
def migrate_database():
"""Add missing columns to the database"""
# Find the database file
db_paths = [
'instance/farm_management.db',
'farm_management.db',
'farms.db'
]
db_path = None
for path in db_paths:
if os.path.exists(path):
db_path = path
break
if not db_path:
print("Database file not found!")
return False
print(f"Found database at: {db_path}")
try:
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Check if weather_alerts_enabled column exists
cursor.execute("PRAGMA table_info(farms)")
columns = [row[1] for row in cursor.fetchall()]
if 'weather_alerts_enabled' not in columns:
print("Adding weather_alerts_enabled column to farms table...")
cursor.execute("ALTER TABLE farms ADD COLUMN weather_alerts_enabled BOOLEAN DEFAULT 1")
print("✓ Added weather_alerts_enabled column")
else:
print("✓ weather_alerts_enabled column already exists")
# Create weather_alerts table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS weather_alerts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farm_id INTEGER,
alert_type VARCHAR(100),
severity VARCHAR(50),
message TEXT,
recommendations TEXT,
is_active BOOLEAN DEFAULT 1,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (farm_id) REFERENCES farms (id)
)
""")
print("✓ weather_alerts table ready")
# Create market_prices table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS market_prices (
id INTEGER PRIMARY KEY AUTOINCREMENT,
crop_type VARCHAR(100),
market_name VARCHAR(200),
price_per_unit FLOAT,
unit VARCHAR(50),
trend VARCHAR(20),
date DATE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
""")
print("✓ market_prices table ready")
# Create disease_detections table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS disease_detections (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farm_id INTEGER,
disease_name VARCHAR(200),
confidence_score FLOAT,
treatment_recommendation TEXT,
image_path VARCHAR(500),
detected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (farm_id) REFERENCES farms (id)
)
""")
print("✓ disease_detections table ready")
# Add new livestock fields to farms table
try:
# Check if farm_type column exists
cursor.execute("PRAGMA table_info(farms)")
columns = [column[1] for column in cursor.fetchall()]
if 'farm_type' not in columns:
cursor.execute("ALTER TABLE farms ADD COLUMN farm_type TEXT DEFAULT 'crop'")
print("✓ Added farm_type column to farms table")
if 'livestock_types' not in columns:
cursor.execute("ALTER TABLE farms ADD COLUMN livestock_types TEXT")
print("✓ Added livestock_types column to farms table")
if 'livestock_count' not in columns:
cursor.execute("ALTER TABLE farms ADD COLUMN livestock_count INTEGER")
print("✓ Added livestock_count column to farms table")
if 'housing_type' not in columns:
cursor.execute("ALTER TABLE farms ADD COLUMN housing_type TEXT")
print("✓ Added housing_type column to farms table")
if 'feeding_system' not in columns:
cursor.execute("ALTER TABLE farms ADD COLUMN feeding_system TEXT")
print("✓ Added feeding_system column to farms table")
if 'breed_info' not in columns:
cursor.execute("ALTER TABLE farms ADD COLUMN breed_info TEXT")
print("✓ Added breed_info column to farms table")
except Exception as e:
print(f"Error adding livestock columns: {e}")
print("✓ Multi-sector farm fields ready")
except Exception as e:
print(f"Error updating farms table: {str(e)}")
# Create livestock_records table
try:
cursor.execute('''
CREATE TABLE IF NOT EXISTS livestock_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farm_id INTEGER NOT NULL,
farmer_id INTEGER NOT NULL,
animal_id TEXT NOT NULL,
animal_type TEXT NOT NULL,
breed TEXT,
gender TEXT,
date_of_birth DATE,
date_acquired DATE NOT NULL DEFAULT CURRENT_DATE,
acquisition_method TEXT,
current_weight REAL,
vaccination_status TEXT,
health_status TEXT DEFAULT 'healthy',
breeding_status TEXT,
milk_production_avg REAL,
egg_production_avg INTEGER,
is_active BOOLEAN DEFAULT 1,
status TEXT DEFAULT 'active',
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (farm_id) REFERENCES farms (id),
FOREIGN KEY (farmer_id) REFERENCES farmers (id)
)
''')
print("✓ livestock_records table ready")
except Exception as e:
print(f"Error creating livestock_records table: {str(e)}")
# Create production_records table
try:
cursor.execute('''
CREATE TABLE IF NOT EXISTS production_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farm_id INTEGER NOT NULL,
farmer_id INTEGER NOT NULL,
livestock_id INTEGER,
production_date DATE NOT NULL DEFAULT CURRENT_DATE,
production_type TEXT NOT NULL,
quantity REAL NOT NULL,
unit TEXT NOT NULL,
quality_grade TEXT,
unit_price REAL,
total_value REAL,
buyer_info TEXT,
notes TEXT,
weather_conditions TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (farm_id) REFERENCES farms (id),
FOREIGN KEY (farmer_id) REFERENCES farmers (id),
FOREIGN KEY (livestock_id) REFERENCES livestock_records (id)
)
''')
print("✓ production_records table ready")
except Exception as e:
print(f"Error creating production_records table: {str(e)}")
# Create feed_records table
try:
cursor.execute('''
CREATE TABLE IF NOT EXISTS feed_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farm_id INTEGER NOT NULL,
farmer_id INTEGER NOT NULL,
feed_date DATE NOT NULL DEFAULT CURRENT_DATE,
feed_type TEXT NOT NULL,
feed_name TEXT NOT NULL,
quantity REAL NOT NULL,
unit TEXT NOT NULL DEFAULT 'kg',
cost_per_unit REAL,
total_cost REAL,
supplier TEXT,
target_animals TEXT,
animals_count INTEGER,
protein_content REAL,
energy_content REAL,
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (farm_id) REFERENCES farms (id),
FOREIGN KEY (farmer_id) REFERENCES farmers (id)
)
''')
print("✓ feed_records table ready")
except Exception as e:
print(f"Error creating feed_records table: {str(e)}")
# Create health_records table
try:
cursor.execute('''
CREATE TABLE IF NOT EXISTS health_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farm_id INTEGER NOT NULL,
farmer_id INTEGER NOT NULL,
livestock_id INTEGER NOT NULL,
event_date DATE NOT NULL DEFAULT CURRENT_DATE,
event_type TEXT NOT NULL,
vaccine_name TEXT,
vaccine_batch TEXT,
next_due_date DATE,
symptoms TEXT,
diagnosis TEXT,
treatment_given TEXT,
medication TEXT,
dosage TEXT,
vet_name TEXT,
vet_contact TEXT,
cost REAL,
follow_up_required BOOLEAN DEFAULT 0,
follow_up_date DATE,
recovery_status TEXT,
notes TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (farm_id) REFERENCES farms (id),
FOREIGN KEY (farmer_id) REFERENCES farmers (id),
FOREIGN KEY (livestock_id) REFERENCES livestock_records (id)
)
''')
print("✓ health_records table ready")
except Exception as e:
print(f"Error creating health_records table: {str(e)}")
# Create daily_tasks table
cursor.execute("""
CREATE TABLE IF NOT EXISTS daily_tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
farmer_id INTEGER,
farm_id INTEGER,
task_date DATE DEFAULT (date('now')),
task_type VARCHAR(100) NOT NULL,
task_title VARCHAR(200) NOT NULL,
task_description TEXT NOT NULL,
priority VARCHAR(20) DEFAULT 'medium',
estimated_duration INTEGER,
weather_dependent BOOLEAN DEFAULT 0,
crop_specific VARCHAR(100),
is_completed BOOLEAN DEFAULT 0,
completed_at DATETIME,
completion_notes TEXT,
completion_rating INTEGER,
sent_via_telegram BOOLEAN DEFAULT 0,
telegram_sent_at DATETIME,
reminder_sent BOOLEAN DEFAULT 0,
reminder_sent_at DATETIME,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
created_by VARCHAR(50) DEFAULT 'system',
FOREIGN KEY (farmer_id) REFERENCES farmers (id),
FOREIGN KEY (farm_id) REFERENCES farms (id)
)
""")
print("✓ daily_tasks table ready")
# Create task_completions table if it doesn't exist
cursor.execute("""
CREATE TABLE IF NOT EXISTS task_completions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
task_id INTEGER,
farmer_id INTEGER,
completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
completion_method VARCHAR(50) DEFAULT 'dashboard',
completion_status VARCHAR(50) DEFAULT 'completed',
notes TEXT,
rating INTEGER,
issues_faced TEXT,
time_taken INTEGER,
FOREIGN KEY (task_id) REFERENCES daily_tasks (id),
FOREIGN KEY (farmer_id) REFERENCES farmers (id)
)
""")
print("✓ task_completions table ready")
conn.commit()
conn.close()
print("Database migration completed successfully!")
return True
except Exception as e:
print(f"Error during migration: {e}")
return False
if __name__ == "__main__":
migrate_database()
|