193 lines
8.1 KiB
Python
193 lines
8.1 KiB
Python
"""
|
|
Consumption Sheets Module - Database Migrations
|
|
Contains schema for all consumption tracking tables
|
|
"""
|
|
|
|
def get_schema():
|
|
"""
|
|
Returns the complete schema SQL for this module.
|
|
This is used when the module is installed.
|
|
"""
|
|
return """
|
|
-- cons_processes - Master list of consumption sheet process types
|
|
CREATE TABLE IF NOT EXISTS cons_processes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
process_key TEXT UNIQUE NOT NULL,
|
|
process_name TEXT NOT NULL,
|
|
template_file BLOB,
|
|
template_filename TEXT,
|
|
rows_per_page INTEGER DEFAULT 30,
|
|
detail_start_row INTEGER DEFAULT 10,
|
|
detail_end_row INTEGER,
|
|
page_height INTEGER,
|
|
print_start_col TEXT DEFAULT 'A',
|
|
print_end_col TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
created_by INTEGER NOT NULL,
|
|
is_active INTEGER DEFAULT 1,
|
|
FOREIGN KEY (created_by) REFERENCES Users(user_id)
|
|
);
|
|
|
|
-- cons_process_fields - Custom field definitions for each process
|
|
CREATE TABLE IF NOT EXISTS cons_process_fields (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
process_id INTEGER NOT NULL,
|
|
table_type TEXT NOT NULL CHECK(table_type IN ('header', 'detail')),
|
|
field_name TEXT NOT NULL,
|
|
field_label TEXT NOT NULL,
|
|
field_type TEXT NOT NULL CHECK(field_type IN ('TEXT', 'INTEGER', 'REAL', 'DATE', 'DATETIME')),
|
|
max_length INTEGER,
|
|
is_required INTEGER DEFAULT 0,
|
|
is_duplicate_key INTEGER DEFAULT 0,
|
|
is_active INTEGER DEFAULT 1,
|
|
sort_order INTEGER DEFAULT 0,
|
|
excel_cell TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (process_id) REFERENCES cons_processes(id)
|
|
);
|
|
|
|
-- cons_sessions - Staff scanning sessions
|
|
CREATE TABLE IF NOT EXISTS cons_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
process_id INTEGER NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
status TEXT DEFAULT 'active' CHECK(status IN ('active', 'archived')),
|
|
FOREIGN KEY (process_id) REFERENCES cons_processes(id),
|
|
FOREIGN KEY (created_by) REFERENCES Users(user_id)
|
|
);
|
|
|
|
-- cons_session_header_values - Flexible storage for header field values
|
|
CREATE TABLE IF NOT EXISTS cons_session_header_values (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
session_id INTEGER NOT NULL,
|
|
field_id INTEGER NOT NULL,
|
|
field_value TEXT,
|
|
FOREIGN KEY (session_id) REFERENCES cons_sessions(id),
|
|
FOREIGN KEY (field_id) REFERENCES cons_process_fields(id)
|
|
);
|
|
|
|
|
|
-- Indexes
|
|
CREATE INDEX IF NOT EXISTS idx_cons_process_fields_process ON cons_process_fields(process_id, table_type);
|
|
CREATE INDEX IF NOT EXISTS idx_cons_process_fields_active ON cons_process_fields(process_id, is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_cons_sessions_process ON cons_sessions(process_id, status);
|
|
CREATE INDEX IF NOT EXISTS idx_cons_sessions_user ON cons_sessions(created_by, status);
|
|
"""
|
|
|
|
|
|
def get_migrations():
|
|
"""
|
|
Returns list of migrations specific to this module.
|
|
Format: [(version, name, up_function), ...]
|
|
"""
|
|
|
|
def migration_001_add_is_duplicate_key(conn):
|
|
"""Add is_duplicate_key column to cons_process_fields"""
|
|
cursor = conn.cursor()
|
|
|
|
# Check if column exists
|
|
cursor.execute('PRAGMA table_info(cons_process_fields)')
|
|
columns = [row[1] for row in cursor.fetchall()]
|
|
|
|
if 'is_duplicate_key' not in columns:
|
|
cursor.execute('ALTER TABLE cons_process_fields ADD COLUMN is_duplicate_key INTEGER DEFAULT 0')
|
|
print(" Added is_duplicate_key column to cons_process_fields")
|
|
|
|
def migration_002_add_detail_end_row(conn):
|
|
"""Add detail_end_row column to cons_processes"""
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute('PRAGMA table_info(cons_processes)')
|
|
columns = [row[1] for row in cursor.fetchall()]
|
|
|
|
if 'detail_end_row' not in columns:
|
|
cursor.execute('ALTER TABLE cons_processes ADD COLUMN detail_end_row INTEGER')
|
|
print(" Added detail_end_row column to cons_processes")
|
|
|
|
def migration_003_add_page_height(conn):
|
|
"""Add page_height column to cons_processes"""
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute('PRAGMA table_info(cons_processes)')
|
|
columns = [row[1] for row in cursor.fetchall()]
|
|
|
|
if 'page_height' not in columns:
|
|
cursor.execute('ALTER TABLE cons_processes ADD COLUMN page_height INTEGER')
|
|
print(" Added page_height column to cons_processes")
|
|
|
|
def migration_004_add_print_columns(conn):
|
|
"""Add print_start_col and print_end_col to cons_processes"""
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute('PRAGMA table_info(cons_processes)')
|
|
columns = [row[1] for row in cursor.fetchall()]
|
|
|
|
if 'print_start_col' not in columns:
|
|
cursor.execute('ALTER TABLE cons_processes ADD COLUMN print_start_col TEXT DEFAULT "A"')
|
|
print(" Added print_start_col column to cons_processes")
|
|
|
|
if 'print_end_col' not in columns:
|
|
cursor.execute('ALTER TABLE cons_processes ADD COLUMN print_end_col TEXT')
|
|
print(" Added print_end_col column to cons_processes")
|
|
|
|
def migration_005_create_router_table(conn):
|
|
"""Create table for IFTTT routing rules"""
|
|
cursor = conn.cursor()
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS cons_process_router (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
process_id INTEGER NOT NULL,
|
|
line_number INTEGER NOT NULL,
|
|
rule_name TEXT,
|
|
match_pattern TEXT NOT NULL, -- The Regex/Format to match
|
|
actions_json TEXT NOT NULL, -- The sequence of THEN steps
|
|
is_active INTEGER DEFAULT 1,
|
|
FOREIGN KEY (process_id) REFERENCES cons_processes(id)
|
|
)
|
|
''')
|
|
print(" Created cons_process_router table")
|
|
|
|
def migration_006_add_deleted_status(conn):
|
|
"""Add 'deleted' to the status CHECK constraint"""
|
|
cursor = conn.cursor()
|
|
|
|
# SQLite doesn't support ALTER COLUMN, so we need to recreate the table
|
|
# First, create a new table with the updated constraint
|
|
cursor.execute('''
|
|
CREATE TABLE cons_sessions_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
process_id INTEGER NOT NULL,
|
|
created_by INTEGER NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
status TEXT DEFAULT 'active' CHECK(status IN ('active', 'archived', 'deleted')),
|
|
FOREIGN KEY (process_id) REFERENCES cons_processes(id),
|
|
FOREIGN KEY (created_by) REFERENCES Users(user_id)
|
|
)
|
|
''')
|
|
|
|
# Copy data from old table
|
|
cursor.execute('''
|
|
INSERT INTO cons_sessions_new (id, process_id, created_by, created_at, status)
|
|
SELECT id, process_id, created_by, created_at, status
|
|
FROM cons_sessions
|
|
''')
|
|
|
|
# Drop old table and rename new one
|
|
cursor.execute('DROP TABLE cons_sessions')
|
|
cursor.execute('ALTER TABLE cons_sessions_new RENAME TO cons_sessions')
|
|
|
|
# Recreate indexes
|
|
cursor.execute('CREATE INDEX IF NOT EXISTS idx_cons_sessions_process ON cons_sessions(process_id, status)')
|
|
cursor.execute('CREATE INDEX IF NOT EXISTS idx_cons_sessions_user ON cons_sessions(created_by, status)')
|
|
|
|
print(" Updated cons_sessions status constraint to include 'deleted'")
|
|
|
|
return [
|
|
(1, 'add_is_duplicate_key', migration_001_add_is_duplicate_key),
|
|
(2, 'add_detail_end_row', migration_002_add_detail_end_row),
|
|
(3, 'add_page_height', migration_003_add_page_height),
|
|
(4, 'add_print_columns', migration_004_add_print_columns),
|
|
(5, 'create_router_table', migration_005_create_router_table),
|
|
(6, 'add_deleted_status', migration_006_add_deleted_status),
|
|
] |