Monday, September 2, 2024

Enhancing the Python Program with Triggers, Logging, and Expanded Constraints

 The Python program has been significantly improved from its original form to add advanced features like triggers for logging changes and more complex data integrity checks. These changes not only help streamline database management but also provide better data consistency and traceability. Below, we’ll go through the key changes made to the Python code, explaining how each modification improves the program’s functionality.

Changes Made to the Python Program

1. Expanded Tables and Sample Data

Original:

  • The original program used only two tables: jtype and podocs.
  • Sample data was limited to these two tables, with document types restricted to 'pj' and 'pjp'.

Improved:

  • New Tables Added: sodocs was introduced to support the new document types (sj and sjs). This allows jref values to refer to different lookup tables based on the jdoctype.
  • Expanded Sample Data: Additional document types ('sj' and 'sjs') were added to the jtype table, along with corresponding references in podocs and sodocs.

Code Changes:

1
2
3
4
5
6
7
# Creating the `sodocs` table and inserting sample data
cursor.execute('''
CREATE TABLE IF NOT EXISTS sodocs (
    jref TEXT PRIMARY KEY
)
''')
cursor.execute("INSERT OR IGNORE INTO sodocs (jref) VALUES ('SOD_REF001'), ('SOD_REF002')")

2. Enhanced Constraints in the journals Table

Original:

  • The journals table used simple constraints to ensure that 'pj' types didn’t require a reference, while 'pjp' types did.

Improved:

  • Complex Checks: The CHECK constraint now includes conditions for four document types:
    • 'pj' and 'sj' types do not require a reference (jref can be NULL).
    • 'pjp' type requires a reference (jref) that exists in the podocs table.
    • 'sjs' type requires a reference (jref) that exists in the sodocs table.

Code Changes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Updated `journals` table with more complex constraints and foreign key references
cursor.execute('''
CREATE TABLE IF NOT EXISTS journals (
    jdoc INTEGER PRIMARY KEY,
    jdoctype TEXT,
    jref TEXT,
    FOREIGN KEY (jdoctype) REFERENCES jtype(jdoctype),
    FOREIGN KEY (jref) REFERENCES podocs(jref) ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (jref) REFERENCES sodocs(jref) ON UPDATE CASCADE ON DELETE SET NULL,
    CHECK (
        (jdoctype IN ('pj', 'sj') AND jref IS NULL) OR 
        (jdoctype = 'pjp' AND jref IN (SELECT jref FROM podocs)) OR 
        (jdoctype = 'sjs' AND jref IN (SELECT jref FROM sodocs))
    )
)
''')

3. Logging Table for Change Tracking

Improved:

  • journals_log Table: A new table was created to record every insert and update action performed on the journals table. This table captures:
    • jdoc: The journal number.
    • action: Whether the record was inserted or updated.
    • field_changed: The specific field that was updated.
    • new_value: The new value of the field after the update.
    • date_time: The timestamp when the action occurred.

Code Changes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
# Create the `journals_log` table for tracking changes
cursor.execute('''
CREATE TABLE IF NOT EXISTS journals_log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    jdoc INTEGER,
    action TEXT,
    field_changed TEXT,
    new_value TEXT,
    date_time TEXT,
    FOREIGN KEY (jdoc) REFERENCES journals(jdoc)
)
''')

4. Triggers for Insert and Update Operations

Improved:

  • Insert Trigger: Automatically logs each insertion into the journals table by recording the journal number and timestamp.
  • Update Trigger: Logs each update, capturing which fields were changed (such as jdoctype or jref), their new values, and the timestamp. This helps maintain a full audit trail.

Code Changes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# Trigger for INSERT operations
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS log_journal_insert
AFTER INSERT ON journals
FOR EACH ROW
BEGIN
    INSERT INTO journals_log (jdoc, action, field_changed, new_value, date_time)
    VALUES (NEW.jdoc, 'INSERT', NULL, NULL, DATETIME('now'));
END;
''')

# Trigger for UPDATE operations
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS log_journal_update
AFTER UPDATE ON journals
FOR EACH ROW
WHEN NEW.jdoctype != OLD.jdoctype OR NEW.jref != OLD.jref
BEGIN
    INSERT INTO journals_log (jdoc, action, field_changed, new_value, date_time)
    VALUES (NEW.jdoc, 'UPDATE', 'jdoctype', NEW.jdoctype, DATETIME('now'));
    INSERT INTO journals_log (jdoc, action, field_changed, new_value, date_time)
    VALUES (NEW.jdoc, 'UPDATE', 'jref', NEW.jref, DATETIME('now'));
END;
''')

5. Update Functionality

Improved:

  • Update Function: A new function, update_journal, allows updating records in the journals table. The use of COALESCE in the SQL update statement ensures that only the specified fields are updated, preserving other existing values.

Code Changes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
# Function to update data in the `journals` table
def update_journal(jdoc, jdoctype=None, jref=None):
    try:
        cursor.execute('''
        UPDATE journals
        SET jdoctype = COALESCE(?, jdoctype),
            jref = COALESCE(?, jref)
        WHERE jdoc = ?
        ''', (jdoctype, jref, jdoc))
        conn.commit()
        print("Record updated successfully")
    except sqlite3.Error as e:
        print(f"Database Error: {e}")

Conclusion

These enhancements demonstrate the powerful use of triggers and complex constraints in database management. By integrating these features into the Python program, we achieve a system that automatically maintains data integrity, logs changes for accountability, and minimizes manual intervention. This makes the program not only a great shortcut for programmers but also a robust solution for managing complex journal entries with various dependencies.

However, while triggers and extensive database logic offer significant advantages, they can also introduce performance considerations. It is crucial to ensure that the database design scales well and does not become a bottleneck, especially under high concurrency conditions. As a best practice, keep the application logic clear and manageable by balancing the load between the application and the database layers.

Here is the complete code listing:


  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
import sqlite3
from datetime import datetime

# Step 1: Create the database connection and cursor
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Step 2: Create the `jtype`, `podocs`, and `sodocs` tables (lookup tables for foreign keys)
cursor.execute('''
CREATE TABLE IF NOT EXISTS jtype (
    jdoctype TEXT PRIMARY KEY
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS podocs (
    jref TEXT PRIMARY KEY
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS sodocs (
    jref TEXT PRIMARY KEY
)
''')

# Insert sample data into `jtype`, `podocs`, and `sodocs` tables
cursor.execute("INSERT OR IGNORE INTO jtype (jdoctype) VALUES ('pj'), ('sj'), ('pjp'), ('sjs')")
cursor.execute("INSERT OR IGNORE INTO podocs (jref) VALUES ('POD_REF001'), ('POD_REF002')")
cursor.execute("INSERT OR IGNORE INTO sodocs (jref) VALUES ('SOD_REF001'), ('SOD_REF002')")

# Step 3: Create the `journals` table with the required constraints
cursor.execute('''
CREATE TABLE IF NOT EXISTS journals (
    jdoc INTEGER PRIMARY KEY,
    jdoctype TEXT,
    jref TEXT,
    FOREIGN KEY (jdoctype) REFERENCES jtype(jdoctype),
    FOREIGN KEY (jref) REFERENCES podocs(jref) ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY (jref) REFERENCES sodocs(jref) ON UPDATE CASCADE ON DELETE SET NULL,
    CHECK (
        (jdoctype IN ('pj', 'sj') AND jref IS NULL) OR 
        (jdoctype = 'pjp' AND jref IN (SELECT jref FROM podocs)) OR 
        (jdoctype = 'sjs' AND jref IN (SELECT jref FROM sodocs))
    )
)
''')

# Step 4: Create the `journals_log` table for logging changes
cursor.execute('''
CREATE TABLE IF NOT EXISTS journals_log (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    jdoc INTEGER,
    action TEXT,
    field_changed TEXT,
    new_value TEXT,
    date_time TEXT,
    FOREIGN KEY (jdoc) REFERENCES journals(jdoc)
)
''')

# Step 5: Create triggers to log inserts and updates
# Trigger for INSERT operations
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS log_journal_insert
AFTER INSERT ON journals
FOR EACH ROW
BEGIN
    INSERT INTO journals_log (jdoc, action, field_changed, new_value, date_time)
    VALUES (NEW.jdoc, 'INSERT', NULL, NULL, DATETIME('now'));
END;
''')

# Trigger for UPDATE operations
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS log_journal_update
AFTER UPDATE ON journals
FOR EACH ROW
WHEN NEW.jdoctype != OLD.jdoctype OR NEW.jref != OLD.jref
BEGIN
    INSERT INTO journals_log (jdoc, action, field_changed, new_value, date_time)
    VALUES (NEW.jdoc, 'UPDATE', 'jdoctype', NEW.jdoctype, DATETIME('now'));
    INSERT INTO journals_log (jdoc, action, field_changed, new_value, date_time)
    VALUES (NEW.jdoc, 'UPDATE', 'jref', NEW.jref, DATETIME('now'));
END;
''')

# Step 6: Function to insert data into the `journals` table
def insert_journal(jdoc, jdoctype, jref=None):
    try:
        cursor.execute('''
        INSERT INTO journals (jdoc, jdoctype, jref)
        VALUES (?, ?, ?)
        ''', (jdoc, jdoctype, jref))
        conn.commit()
        print("Record inserted successfully")
    except sqlite3.IntegrityError as e:
        print(f"Integrity Error: {e}")
    except sqlite3.Error as e:
        print(f"Database Error: {e}")

# Function to update data in the `journals` table
def update_journal(jdoc, jdoctype=None, jref=None):
    try:
        cursor.execute('''
        UPDATE journals
        SET jdoctype = COALESCE(?, jdoctype),
            jref = COALESCE(?, jref)
        WHERE jdoc = ?
        ''', (jdoctype, jref, jdoc))
        conn.commit()
        print("Record updated successfully")
    except sqlite3.Error as e:
        print(f"Database Error: {e}")

# Example Insertions
insert_journal(1, 'pj')               # Should succeed: 'pj' type doesn't require jref
insert_journal(2, 'sj')               # Should succeed: 'sj' type doesn't require jref
insert_journal(3, 'pjp', 'POD_REF001')  # Should succeed: 'pjp' type requires jref in `podocs`
insert_journal(4, 'sjs', 'SOD_REF001')  # Should succeed: 'sjs' type requires jref in `sodocs`

# Example Updates
update_journal(1, 'pjp', 'POD_REF002')  # Should create a log entry for update

# Close the connection
conn.close()

By at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)