Python Bits and Pieces with Cyber Security

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:

# 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:

# 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:

# 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:

# 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:

# 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:

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()

Sunday, September 1, 2024

Minimizing Code with Conditional Foreign Keys in SQLite: A Shortcut for Efficient Data Management

Introduction

When working with databases, one of the main goals for programmers is to write efficient and maintainable code. A common way to achieve this is by using database constraints, which can help reduce code redundancy and ensure data integrity. In this blog post, we will explore a Python program that demonstrates how to set up conditional foreign keys and constraints in SQLite to simplify data validation and minimize the need for extensive backend checks. While this approach is powerful, it's important to balance processing between the application and the database to avoid potential performance bottlenecks.

Step-by-Step Guide to the Program

Here's a step-by-step breakdown of the program and its advantages:

Step 1: Creating the Database Connection and Cursor

1 2	conn = sqlite3.connect('example.db') cursor = conn.cursor()

The program starts by establishing a connection to an SQLite database named example.db. The cursor object is used to execute SQL commands. This setup is essential for interacting with the database.

Step 2: Creating Lookup Tables (`jtype` and `podocs`)

cursor.execute('''
CREATE TABLE IF NOT EXISTS jtype (
    jdoctype TEXT PRIMARY KEY
)
''')

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

# Insert sample data into `jtype` and `podocs` tables
cursor.execute("INSERT OR IGNORE INTO jtype (jdoctype) VALUES ('pj'), ('pjp')")
cursor.execute("INSERT OR IGNORE INTO podocs (jref) VALUES ('REF001'), ('REF002')")

These commands create two lookup tables: jtype and podocs. The jtype table holds document types, while podocs contains reference values. Both tables use primary keys to ensure uniqueness. Sample data is inserted into these tables, which will be referenced by the main journals table.

Step 3: Creating the `journals` Table with 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),
    CHECK ((jdoctype = 'pj' AND jref IS NULL) OR (jdoctype = 'pjp' AND jref IS NOT NULL))
)
''')

The journals table is the core of the program. It contains:

jdoc: The primary key, uniquely identifying each record.
jdoctype: A foreign key linked to the jtype table, specifying the type of document.
jref: A foreign key linked to the podocs table, specifying a reference value.

The key feature here is the CHECK constraint, which enforces specific rules based on the value of jdoctype:

If jdoctype is 'pj', jref must be NULL.
If jdoctype is 'pjp', jref must not be NULL.

This logic minimizes the need for extra validation code in the application, as these rules are directly enforced by the database.

Step 4: Inserting 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"Error: {e}")

This function inserts data into the journals table. It accepts jdoc, jdoctype, and an optional jref. The function attempts to insert the record, and if the constraints are violated, an IntegrityError is raised, and the error message is printed.

Example Usage

# Example Insertion
insert_journal(1, 'pj')         # This should succeed (jref not required)
insert_journal(2, 'pjp', 'REF001')  # This should succeed (jref is required)
insert_journal(3, 'pjp')        # This should fail (jref is required but not provided)

These examples demonstrate the program's logic:

The first insert succeeds because jdoctype is 'pj', so jref is allowed to be NULL.
The second insert succeeds because jdoctype is 'pjp', and a jref is provided.
The third insert fails because jdoctype is 'pjp', but no jref is provided.

Conclusion

This approach of using conditional foreign keys and constraints in SQLite greatly reduces the need for additional code to handle data validation in your application. It makes the code cleaner, easier to maintain, and leverages the database's built-in capabilities for ensuring data integrity. However, it's important to note that excessive processing in the database, especially when many users are accessing it simultaneously, can create performance bottlenecks. The balance between application-side logic and database constraints is key to building scalable and efficient systems.

By implementing these strategies, developers can enjoy the benefits of minimized code complexity and enhanced data validation, making this a great shortcut for programmers working on database-driven applications.

Friday, August 30, 2024

Visualizing Database Table Relationships with PyQt6

In the realm of software development, understanding the relationships between database tables is crucial for building, customizing, and enhancing applications. Visualization tools can significantly aid developers in grasping these relationships, making the development process smoother and more efficient. This blog post will guide you through visualizing database table relationships using Python's PyQt6 framework, specifically leveraging QGraphicsView and QGraphicsScene to create a clear, interactive graphical representation of these relationships.

Figure 1: Sample output gnerated by the program.

Why Visualizing Database Relationships Matters

Database relationships, especially those involving foreign keys, define how tables interact with each other. A well-structured database is foundational to any software, enabling efficient data retrieval and manipulation. For developers working on software customization and enhancement projects, understanding these relationships is essential to avoid potential pitfalls, such as data inconsistency or integrity issues.

Visualizing these relationships:

Enhances Clarity: It provides a clear picture of how tables are interconnected, which is crucial during debugging or when onboarding new team members.
Speeds Up Development: By having a visual reference, developers can quickly understand the data flow, reducing the time spent deciphering complex SQL queries.
Improves Communication: Visual representations make it easier to communicate the database structure to non-technical stakeholders, ensuring everyone is aligned on the application's data model.

Building the Visualization with PyQt6

Below is a Python program using PyQt6 that generates a graphical representation of database table relationships based on foreign keys. Let’s walk through it step by step:

Step 1: Setting Up the Environment

We begin by importing the necessary modules from PyQt6 and setting up a connection to our SQLite database.

import sys
import sqlite3
from PyQt6.QtWidgets import QApplication, QGraphicsView, QGraphicsScene, QGraphicsRectItem, QGraphicsLineItem, QGraphicsTextItem
from PyQt6.QtCore import QPointF

Step 2: Fetching Foreign Keys and Fields

The function get_foreign_keys_and_fields() connects to the SQLite database and fetches all tables, their fields, and foreign key relationships.

def get_foreign_keys_and_fields(db_path):
    connection = sqlite3.connect(db_path)
    cursor = connection.cursor()

    # Get the list of all tables
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()

    foreign_keys = {}
    fields = {}

    for table in tables:
        table_name = table[0]

        # Fetch fields for the table
        cursor.execute(f"PRAGMA table_info({table_name})")
        fields[table_name] = [row[1] for row in cursor.fetchall()]

        # Fetch foreign keys for the table
        cursor.execute(f"PRAGMA foreign_key_list({table_name})")
        keys = cursor.fetchall()

        if keys:
            foreign_keys[table_name] = []
            for key in keys:
                foreign_keys[table_name].append({
                    "table": key[2],  # The referenced table
                    "from": key[3],   # The foreign key column in the current table
                    "to": key[4]      # The referenced column in the other table
                })

    connection.close()
    return foreign_keys, fields

This function retrieves all relevant data about tables and their relationships, which will be used later to draw the schema.

Step 3: Creating the Visualization with PyQt6

The SchemaView class inherits from QGraphicsView and uses QGraphicsScene to manage the layout and rendering of tables and their relationships.

class SchemaView(QGraphicsView):
    def __init__(self, foreign_keys, fields):
        super().__init__()
        self.scene = QGraphicsScene(self)
        self.setScene(self.scene)

        self.tables = {}
        self.fields = fields
        self.draw_schema(foreign_keys, fields)

Step 4: Drawing the Tables and Relationships

The draw_schema() method organizes tables and draws connections based on foreign keys. Tables are arranged with parent tables on the left and child tables on the right.

def draw_schema(self, foreign_keys, fields):
    parent_x, child_x = 0, 300  # X positions for parent and child tables
    y = 0
    table_width, table_height = 150, 30
    field_height = 20
    vertical_margin = 100  # Space between stacked child tables
    arrow_length = 40  # Length of the arrows

    # Draw parent tables on the left
    for table_name, field_names in fields.items():
        # Determine if the table is a parent (not a foreign key in another table)
        is_parent = all(table_name != fk["table"] for fks in foreign_keys.values() for fk in fks)

        if is_parent:
            total_height = table_height + len(field_names) * field_height
            rect = QGraphicsRectItem(parent_x, y, table_width, total_height)
            self.scene.addItem(rect)

            # Draw table name at the top
            table_text = QGraphicsTextItem(table_name)
            table_text.setPos(parent_x + 10, y + 5)
            self.scene.addItem(table_text)

            # Draw fields with horizontal lines
            field_positions = {}
            for index, field_name in enumerate(field_names):
                field_text = QGraphicsTextItem(field_name)
                field_y = y + table_height + index * field_height
                field_text.setPos(parent_x + 10, field_y)
                self.scene.addItem(field_text)
                field_positions[field_name] = QPointF(parent_x + table_width, field_y + field_height / 2)

                # Draw separator line between fields
                if index > 0:
                    line = QGraphicsLineItem(parent_x, field_y, parent_x + table_width, field_y)
                    self.scene.addItem(line)

            # Store parent table and its field positions
            self.tables[table_name] = {'rect': rect, 'fields': field_positions}
            y += total_height + vertical_margin

This code section handles drawing each table as a rectangle with fields listed vertically. It also ensures that parent tables are positioned correctly on the left.

Step 5: Drawing Foreign Key Arrows

For each foreign key relationship, we draw two arrows to visually connect the related fields of the parent and child tables.

# Draw foreign key relationships with horizontal arrows outside of rectangles
for table_name, keys in foreign_keys.items():
    for key in keys:
        from_table = self.tables.get(table_name)
        to_table = self.tables.get(key['table'])

        if from_table and to_table:
            # Get the positions of the fields for drawing arrows
            from_point = from_table['fields'][key['from']]
            to_point = to_table['fields'][key['to']]

            # Draw the first horizontal arrow for the child field
            child_arrow_start = QPointF(from_point.x() + 10, from_point.y())
            child_arrow_end = QPointF(from_point.x() + arrow_length, from_point.y())
            child_arrow_line = QGraphicsLineItem(child_arrow_start.x(), child_arrow_start.y(),
                                                 child_arrow_end.x(), child_arrow_end.y())
            self.scene.addItem(child_arrow_line)

            # Draw the arrowhead for the child field arrow
            child_arrow_head1 = QGraphicsLineItem(child_arrow_end.x() - 5, child_arrow_end.y() - 5,
                                                  child_arrow_end.x(), child_arrow_end.y())
            child_arrow_head2 = QGraphicsLineItem(child_arrow_end.x() - 5, child_arrow_end.y() + 5,
                                                  child_arrow_end.x(), child_arrow_end.y())
            self.scene.addItem(child_arrow_head1)
            self.scene.addItem(child_arrow_head2)

            # Draw the second horizontal arrow for the parent field
            parent_arrow_start = QPointF(to_point.x() - arrow_length - 10, to_point.y())
            parent_arrow_end = QPointF(to_point.x() - 10, to_point.y())
            parent_arrow_line = QGraphicsLineItem(parent_arrow_start.x(), parent_arrow_start.y(),
                                                  parent_arrow_end.x(), parent_arrow_end.y())
            self.scene.addItem(parent_arrow_line)

            # Draw the arrowhead for the parent field arrow
            parent_arrow_head1 = QGraphicsLineItem(parent_arrow_start.x() + 5, parent_arrow_start.y() - 5,
                                                   parent_arrow_start.x(), parent_arrow_start.y())
            parent_arrow_head2 = QGraphicsLineItem(parent_arrow_start.x() + 5, parent_arrow_start.y() + 5,
                                                   parent_arrow_start.x(), parent_arrow_start.y())
            self.scene.addItem(parent_arrow_head1)
            self.scene.addItem(parent_arrow_head2)

            # Connect both arrows with a vertical line
            connection_line = QGraphicsLineItem(child_arrow_end.x(), child_arrow_end.y(),
                                                parent_arrow_start.x(), parent_arrow_start.y())
            self.scene.addItem(connection_line)

Each relationship is represented by arrows that connect specific fields of the child and parent tables. Arrows are drawn outside the table rectangles and aligned with their respective fields, making it visually clear which fields are connected.

Step 6: Running the Application

The main part of the script sets up the application and displays the schema view.

if __name__ == '__main__':
    app = QApplication(sys.argv)

    # Path to your SQLite database
    db_path = 'accounting.db'
    foreign_keys, fields = get_foreign_keys_and_fields(db_path)

    view = SchemaView(foreign_keys, fields)
    view.setWindowTitle('Database Schema')
    view.resize(1200, 600)
    view.show()

    sys.exit(app.exec())

This final section initializes the PyQt application, loads the database, and displays the schema view in a window.

Conclusion

Visualizing database relationships using PyQt6's QGraphicsView and QGraphicsScene can greatly assist developers in understanding the complex interconnections within a database. This visual tool is invaluable in software development, particularly in customization and enhancement projects where understanding the data model is critical. By leveraging such visualizations, developers can more easily maintain data integrity, optimize query performance, and ensure the overall robustness of their applications.

Wednesday, August 7, 2024

Interact with Your Own Data Using Ollama Hosted Phi3

Phi3 is a very small large language model, with only 4 billion parameters compared to ChatGPT's 175 billion. Despite its smaller size, Phi3 offers a unique advantage: it can directly interact with your data without needing an API. In contrast, ChatGPT requires an API, and the free access to this API expires after three months unless you upgrade to a paid plan at $25 per month.

One of the main benefits of Ollama hosted Phi3 is that it doesn't require internet access and is completely free. However, running Phi3 locally does have its drawbacks. For optimal performance, it requires a high-spec PC or Mac, particularly one with a powerful GPU. While it can run on lower-spec machines, the performance will be significantly slower.

To demonstrate how Phi3 works, I have prepared a simple program that uploads a text file, processes it, and allows you to ask questions about the data. For this example, I used data about the national anthem of the Philippines. When I asked the untrained Phi3 who wrote the national anthem, it provided an incorrect answer. This is due to the limited amount of training data available for Phi3, as mentioned earlier. The following picture shows the reply of Phi3(without embeddings) from the DOS prompt:

I obtained the data from Wikipedia by just copying a few paragraphs and pasting it to Notepad and saving it as .txt file.

Here is the program and some explanations:

from langchain_chroma import Chroma
from langchain_community.document_loaders import TextLoader
from langchain_community.embeddings.sentence_transformer import (
    SentenceTransformerEmbeddings,
)
from langchain_text_splitters import CharacterTextSplitter

from langchain_community.embeddings import OllamaEmbeddings
from langchain_text_splitters import RecursiveCharacterTextSplitter
from langchain_community.vectorstores import Chroma

from langchain.prompts import ChatPromptTemplate, PromptTemplate
from langchain_core.output_parsers import StrOutputParser
from langchain_community.chat_models import ChatOllama
from langchain_core.runnables import RunnablePassthrough
from langchain.retrievers.multi_query import MultiQueryRetriever

loader = TextLoader("lupanghinirang.txt")
documents = loader.load()

# Split the document into chunks
text_splitter = CharacterTextSplitter(chunk_size=1000, chunk_overlap=0)
docs = text_splitter.split_documents(documents)

# Create the open-source embedding function
embedding_function = SentenceTransformerEmbeddings(model_name="all-MiniLM-L6-v2")

# Load it into Chroma
db = Chroma.from_documents(docs, embedding_function)

# Query the database
query = "What is the National Anthem of the Philippines?"
docs = db.similarity_search(query)

# Split and chunk the documents
text_splitter = RecursiveCharacterTextSplitter(chunk_size=7500, chunk_overlap=100)
chunks = text_splitter.split_documents(docs)

# Add to vector database
vector_db = Chroma.from_documents(
    documents=chunks, 
    embedding=OllamaEmbeddings(model="nomic-embed-text", show_progress=True),
    collection_name="local-rag"
)

# LLM from Ollama
local_model = "phi3"
llm = ChatOllama(model=local_model)

QUERY_PROMPT = PromptTemplate(
    input_variables=["question"],
    template="""You are an AI language model assistant. Your task is to generate five
    different versions of the given user question to retrieve relevant documents from
    a vector database. By generating multiple perspectives on the user question, your
    goal is to help the user overcome some of the limitations of the distance-based
    similarity search. Provide these alternative questions separated by newlines.
    Original question: {question}""",
)

retriever = MultiQueryRetriever.from_llm(
    vector_db.as_retriever(), 
    llm,
    prompt=QUERY_PROMPT
)

# RAG prompt
template = """Answer the question based ONLY on the following context:
{context}
Question: {question}
"""

prompt = ChatPromptTemplate.from_template(template)

chain = (
    {"context": retriever, "question": RunnablePassthrough()}
    | prompt
    | llm
    | StrOutputParser()
)

chain.invoke("who composed the national anthem of the Philippines?")

Explanation

Loading and Splitting Documents:
- The program starts by loading a text file (lupanghinirang.txt) using TextLoader.
- The text is then split into chunks using CharacterTextSplitter.
Embedding and Storing Data:
- An embedding function is created using the SentenceTransformerEmbeddings model.
- The split documents are embedded and stored in a Chroma database.
Querying the Database:
- A query is made to the database to find relevant documents.
- The retrieved documents are further split into chunks using RecursiveCharacterTextSplitter.
Adding to Vector Database:
- The chunks are embedded using OllamaEmbeddings and stored in another Chroma database.
Setting Up the Language Model:
- The ChatOllama model (Phi3) is initialized.
- A prompt template is created to generate multiple versions of a user query.
Retrieving Relevant Documents:
- MultiQueryRetriever is used to retrieve relevant documents from the vector database.
Answering the Query:
- A final prompt template is set up to answer the question based on the retrieved context.
- The chain is executed to generate the answer to the question.

Here is the result when running the program:

This program demonstrates the capabilities of Phi3 in processing and interacting with local data. Despite its smaller size compared to ChatGPT, Phi3 can be a powerful tool for specific applications where internet access is limited or data privacy is a concern.

Tuesday, August 6, 2024

Upgrading the Ollama Hosted Phi3 Offline Chat Program

In my previous post, "Chat with Ollama Phi3 in Python Offline," I introduced a basic program that allowed you to chat with Ollama's Phi3 language model offline using Python. Today, I’m excited to share an upgraded version of this program. This new version incorporates a graphical user interface (GUI) using PyQt6 and includes a text-to-speech (TTS) engine to read aloud Phi3's responses. The chat logs are displayed in a QTextEdit widget, making the interaction more user-friendly and accessible. This program is created in Python 3.10 same experience I encountered when I first create a chat program using Langchain and Openai. also a good pc/mac will deliver a better experience but it will still run on a Intel M3-100y but it will be very slow considering that I have downloaded the phi3 4b quantized version.

Upgraded Features

Graphical User Interface (GUI): A chat window created using PyQt6.
Text-to-Speech: Utilizes pyttsx3 to read aloud Phi3's responses.
Chat Logs: Displays the conversation history in a QTextEdit widget.

Code Explanation

Below is the complete code for the upgraded program, followed by detailed explanations of each part.

import pyttsx3
from PyQt6.QtGui import *
from PyQt6.QtCore import *
from PyQt6.QtWidgets import *
from langchain_community.llms import Ollama

class Window(QMainWindow):
    def __init__(self):
        super(Window, self).__init__()

        # Set up the layout
        layout = QVBoxLayout()
        hlayout = QHBoxLayout()
        central_widget = QWidget()
        central_widget.setLayout(layout)
        self.setCentralWidget(central_widget)

        # Initialize the language model
        self.llm = Ollama(model="phi3")
        
        # Add widgets to the layout
        layout.addLayout(hlayout)
        self.te = QLineEdit(self)
        self.le = QTextEdit(self)
        self.le.setReadOnly(True)
        self.btnStart = QPushButton("Ask", self)
        hlayout.addWidget(self.te)
        hlayout.addWidget(self.btnStart)
        layout.addWidget(self.le)
        
        # Connect button click to function
        self.btnStart.clicked.connect(self.onClickedStart)
        
        # Set window properties
        self.setGeometry(25, 45, 900, 500)
        self.setWindowTitle('Speak')
 
    def onClickedStart(self):
        global text
        
        # Get user input and display it in the chat log
        user_input = self.te.text()
        self.le.append(f'You: {user_input}')
        
        # Get response from the language model
        response = self.llm.invoke(user_input)
        
        # Display the response in the chat log
        self.le.append(f'Phi3: {response}')
        
        # Prepare text for text-to-speech
        text = response
        self.worker = WorkerThread()
        self.worker.start()
   
text = ''
engine = []
error = ''
i = 0

class WorkerThread(QThread):
    def run(self):
        global text, engine, error, i
        
        try:
            i += 1
            engine.append(pyttsx3.init())
            engine[i-1].setProperty('rate', 120)
            engine[i-1].say(text)
            engine[i-1].startLoop()
        except Exception as err:
            error = str(err)

if __name__ == "__main__":
    import sys

    app = QApplication(sys.argv)
    w = Window()
    w.show()
    sys.exit(app.exec())

Detailed Explanation

Imports

pyttsx3: A text-to-speech conversion library in Python.
PyQt6: A set of Python bindings for the Qt application framework, used to create the GUI.
Ollama: A module from the langchain_community package for interacting with the Phi3 language model.

`Window` Class

Initialization (__init__ method):
- Sets up the main layout and widgets (QLineEdit, QTextEdit, QPushButton).
- Initializes the Phi3 language model using Ollama.
- Connects the "Ask" button to the onClickedStart method.
- Configures the window size and title.
onClickedStart Method:
- Gets the user's input from the QLineEdit widget.
- Appends the user's input to the QTextEdit widget for display.
- Sends the input to the Phi3 language model and gets the response.
- Appends the response to the QTextEdit widget.
- Initiates a text-to-speech conversion of the response using the WorkerThread class.

`WorkerThread` Class

A QThread subclass that handles the text-to-speech functionality.
run Method:
- Initializes a new pyttsx3 engine for each response.
- Sets the speech rate and converts the response text to speech.
- Starts the speech engine loop to read the response aloud.

Main Block

Creates a QApplication instance and a Window instance.
Displays the Window and starts the event loop.

Conclusion

This upgraded version of the Ollama Phi3 offline chat program provides a more interactive and user-friendly experience. The integration of a GUI and text-to-speech functionality makes it easier to interact with the Phi3 model without needing to modify the underlying code. Give it a try and see how these enhancements improve your interactions with Phi3!

Monday, August 5, 2024

Separating Data Points

Separating data points is a fundamental goal in many machine learning and data science tasks, particularly in classification problems. Here are several reasons why separating data points is important:

1. Classification

Goal: The primary objective in classification problems is to assign labels to data points based on their features.
Separation: By separating data points of different classes, a model can make accurate predictions about the class of new, unseen data points. Effective separation leads to higher classification accuracy.

2. Reducing Error

Minimizing Misclassification: Separating data points helps to minimize the number of misclassified instances, thereby improving the overall performance of the model.
Error Metrics: Common metrics such as accuracy, precision, recall, and F1-score are all improved when data points are well-separated according to their respective classes.

3. Improving Generalization

Overfitting and Underfitting: A well-separated dataset helps in creating models that generalize better to new data. Models that fail to separate data points effectively might overfit (learn noise) or underfit (fail to capture the underlying trend).
Decision Boundaries: Clear separation helps in defining decision boundaries that work well on both training data and unseen test data.

4. Interpretability

Understanding the Model: When data points are well-separated, it is easier to understand and interpret the model’s decisions. This is especially useful in fields where interpretability is crucial, such as medical diagnostics or finance.
Visualization: In lower dimensions (2D or 3D), well-separated data points can be visualized more clearly, helping stakeholders to understand the model's behavior.

5. Performance of Algorithms

Algorithm Efficiency: Some machine learning algorithms, like Support Vector Machines (SVMs), work by finding the optimal separation between classes. The performance of these algorithms is directly linked to how well the data points can be separated.
Convergence: Well-separated data points can lead to faster convergence in training algorithms, making the model training process more efficient.

6. Clustering and Anomaly Detection

Clustering: In unsupervised learning, separating data points into distinct clusters helps in understanding the natural grouping within the data, which can be useful for exploratory data analysis.
Anomaly Detection: Separating normal data points from anomalies helps in identifying outliers, which is critical in fields such as fraud detection, network security, and quality control.

7. Noise Reduction

Handling Noisy Data: Separation helps in distinguishing between signal and noise. Effective separation techniques can help in identifying and mitigating the impact of noisy data points, leading to more robust models.

Example: Support Vector Machines (SVMs)

Support Vector Machines (SVMs) aim to find the hyperplane that best separates data points of different classes. This separation is achieved by maximizing the margin between the classes, which helps in improving the model’s robustness and accuracy. The use of kernel functions in SVMs allows for the separation of non-linearly separable data by projecting it into higher-dimensional spaces.

Conclusion

Separating data points is crucial for achieving high performance in various machine learning tasks. It leads to more accurate, interpretable, and generalizable models. Whether through classification, clustering, or anomaly detection, effective separation of data points underpins the success of many data science applications.

Projecting two-dimensional data into an infinite-dimensional space

Projecting two-dimensional data into an infinite-dimensional space is a concept used in data science, particularly in the context of kernel methods. Here are some reasons why this might be desirable:

1. Handling Non-Linearly Separable Data

In many real-world problems, data points are not linearly separable in their original feature space. By projecting the data into a higher (potentially infinite) dimensional space, non-linear relationships can become linear. This allows for the application of linear algorithms in the new space, which can be more efficient and easier to implement.

2. Utilizing the Kernel Trick

The kernel trick is a technique that allows us to compute the dot product in the high-dimensional space without explicitly computing the transformation. Instead of mapping the data to the high-dimensional space and then computing the dot product, the kernel trick computes the dot product directly using a kernel function. This makes the computation feasible even if the high-dimensional space is infinite.

3. Improved Model Performance

Mapping data to a higher-dimensional space can improve the performance of certain algorithms, such as Support Vector Machines (SVMs). In this new space, it becomes easier to find a hyperplane that separates the data points of different classes. This often results in better classification accuracy.

4. Capturing Complex Relationships

High-dimensional projections can capture complex relationships and interactions between features that are not apparent in the original feature space. This allows for more expressive models that can better fit the data.

5. Flexibility in Choice of Kernel

Different kernel functions (such as polynomial, radial basis function (RBF), and sigmoid kernels) correspond to different ways of projecting data into higher-dimensional spaces. This flexibility allows practitioners to choose a kernel that best captures the underlying structure of the data for a given problem.

Example: The Radial Basis Function (RBF) Kernel

The RBF kernel is commonly used in SVMs and other kernel-based methods. It effectively projects the data into an infinite-dimensional space, enabling the separation of data points that are not linearly separable in the original space. The RBF kernel is defined as:

Here, $x$ and $x'$ are data points, $\|\cdot\|$ denotes the Euclidean distance, and $\sigma$ is a parameter that defines the width of the Gaussian function. The RBF kernel measures similarity in a way that considers all possible dimensions, effectively projecting the data into an infinite-dimensional space.

Conclusion

Projecting two-dimensional (or low-dimensional) data into an infinite-dimensional space through the use of kernels allows for the handling of complex, non-linear relationships in a computationally efficient manner. This is a powerful technique in machine learning, enabling the development of more accurate and robust models.

Monday, September 2, 2024

Changes Made to the Python Program

1. Expanded Tables and Sample Data

2. Enhanced Constraints in the journals Table

3. Logging Table for Change Tracking

4. Triggers for Insert and Update Operations

5. Update Functionality

Conclusion

Sunday, September 1, 2024

Step 1: Creating the Database Connection and Cursor

Step 2: Creating Lookup Tables (jtype and podocs)

Step 3: Creating the journals Table with Constraints

Step 4: Inserting Data into the journals Table

Example Usage

Conclusion

Friday, August 30, 2024

Why Visualizing Database Relationships Matters

Building the Visualization with PyQt6

Step 1: Setting Up the Environment

Step 2: Fetching Foreign Keys and Fields

Step 3: Creating the Visualization with PyQt6

Step 4: Drawing the Tables and Relationships

Step 5: Drawing Foreign Key Arrows

Step 6: Running the Application

Conclusion

Wednesday, August 7, 2024

Explanation

Tuesday, August 6, 2024

Upgraded Features

Code Explanation

Detailed Explanation

Imports

Window Class

WorkerThread Class

Main Block

Conclusion

Monday, August 5, 2024

1. Classification

2. Reducing Error

3. Improving Generalization

4. Interpretability

5. Performance of Algorithms

6. Clustering and Anomaly Detection

7. Noise Reduction

Example: Support Vector Machines (SVMs)

Conclusion

1. Handling Non-Linearly Separable Data

2. Utilizing the Kernel Trick

3. Improved Model Performance

4. Capturing Complex Relationships

5. Flexibility in Choice of Kernel

Example: The Radial Basis Function (RBF) Kernel

Conclusion

2. Enhanced Constraints in the `journals` Table

Step 2: Creating Lookup Tables (`jtype` and `podocs`)

Step 3: Creating the `journals` Table with Constraints

Step 4: Inserting Data into the `journals` Table

`Window` Class

`WorkerThread` Class