Week 15: Stored Procedures & Triggers

This week, I was placed into a new position at my day job, sort of a promotion, hooray! The bad news is I didn't have the energy to keep up with my study schedule, so overall it's an underperforming week.

Where I am at:

• CS50: Final week

• Google Advanced Data Analytics (GADA): Course 1 Week 3

• Power BI Course on Logikbot: 18%

• Building simple prompts with LangChain

So while none of these is interesting enough to spend time talking about, I'd like to discuss two advanced topics of SQL as part of the knowledge gap of SQL I still have to fill:

1. Stored Procedures

2. Triggers

Stored Procedures:

A stored procedure is a precompiled set of SQL statements that can be stored in a database and can be invoked repetitively whenever required. However, don't mistake stored procedures as custom functions. Here are the differences:

Stored procedures play a critical role in DML operations and enable more complex operations on the database entities. But how exactly the feature empowers users?

1. Stored procedures are reusable.

2. You can grant permission to users to execute a stored procedure instead of giving full access to the underlying tables to maintain data safety.

3. Only the procedure name and parameters are passed over the network upon being called, not its code, thus reducing network traffic.

4. #3 also implies that stored procedures are compiled and stored in the database, they execute faster than queries.

Consider you have a database for a library system with a table books and you often need to check the availability of a book. You might create a stored procedure for this common task.

books TABLE:

-- CREATE TABLE statement declares 'books'
CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100),
    checked_out BOOLEAN
);

/* 
CREATE PROCEDURE statement
Purpose: To check book availiability
*/
CREATE PROCEDURE checkbook
    -- Parameter declaration along with its data type
    @BookID INT
AS
-- BEGIN marks the start of the code block that is executed
-- when the stored procedure is invoked
BEGIN
    SELECT 
        -- CASE statement is used for conditional logic
        CASE 
            WHEN COUNT(*) > 0 THEN 'Available'
            ELSE 'Not Available'
        --  This END marks the completion of the CASE statement and
        --  assigns an alias 'Status' to the output
        END as Status
    FROM 
        books
    -- Checking conditions: matching book_id and book availability
    WHERE 
        book_id = @BookID AND checked_out = 0
-- This END marks the end of the code block that started with BEGIN
END

This stored procedure takes one parameter, @BookID, and returns whether the book is available or not. You could use it in your application code like this:

-- This would return 'Not Available'
EXEC check_book @BookID = 2

-- This would return 'Available'
EXEC check_book @BookID = 5

-- This would return 'Not Available' becasue COUNT(*) = 0
EXEC check_book @BookID = 6

And if you submit the wrong data type for example: @BookID = 'Book A', it will also not execute correctly because @BookID expects an integer value, not a string.

Triggers:

A trigger in SQL is a type of stored procedure that is automatically executed or fired when a specified event occurs within the database. Triggers are typically associated with a specific table and event(s) such as an INSERT, UPDATE, or DELETE operation. They are used to maintain the integrity of the information in the database, automate certain operations, enforce complex business rules, or prevent unauthorized access.

Triggers are made up of two parts:

1. An event that will set off the trigger.

2. An action that the trigger will perform when it is set off.

Aside from data integrity and automation, a trigger also provides the benefit of auditing and monitoring data changes. Specifically, a trigger can be utilized to track modifications in sensitive data and record these changes in an audit table. This results in a comprehensive log of activities, including what, when and by whom changes were made. This is crucial for error detection, security breach identification, and regulatory compliance.

Let's go back to the same example, the library database with a books table and now a second table book_audit that logs changes to the books table.

The book_audit table might look something like this:

CREATE TABLE book_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    book_id INT,
    /*
    The DEFAULT value clause specifies that if an insert or update operation
    doesn't provide a specific value for audit_date, the database should 
    automatically use the current date and time as the value.
    */
    audit_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    event VARCHAR(50),
    old_status BOOLEAN,
    new_status BOOLEAN
);

Now, we'll create a trigger that logs an audit record every time a book's checked_out status is updated.

CREATE TRIGGER status_update
-- The trigger fires ONLY after an UPDATE operation is performed
AFTER UPDATE ON books
-- The trigger executes once for every row affected by the UPDATE operation
FOR EACH ROW
BEGIN
    -- Whenever availiability status is changed
    IF NEW.checked_out != OLD.checked_out THEN
        INSERT INTO book_audit (book_id, event, old_status, new_status) 
        VALUES (OLD.book_id, 'UPDATE', OLD.checked_out, NEW.checked_out);
    END IF;
END;

So, if someone were to run a statement like UPDATE books SET checked_out = 1 WHERE book_id = 1;, the trigger would automatically create a corresponding audit record in the book_audit table, effectively saving you half the time to manage the data of your database.

ML project:

Before we end this week, let's take a look at my new ML project roadmap:

For the last couple weeks I've been learning bit by bit how the LangChain module works including finishing a short LangChain course provided by deeplearning.AI which is free for a limited time. By the end of this week I should be done with learning how to use the basic chain and single sequential.

import os
from apikey import apikey
from langchain.chat_models import ChatOpenAI  
from langchain.prompts import ChatPromptTemplate  
from langchain.chains import LLMChain 


os.environ["OPENAI_API_KEY"] = apikey

llm = ChatOpenAI(temperature=0.1)  # 0 = fact, 1 = creative
prompt = ChatPromptTemplate.from_template(  # Declare the prompt
    "Tell me a joke about {subject}."
)
chain = LLMChain(llm=llm, prompt=prompt)  # Declare the chain


subject = "Vegan"
print(chain.run(subject))

A little bit cleaner and organized compared to my personal assistant 1.0 and with just this amount of code, you can get something like these:

1. Why did the vegan go to the art gallery?
   Because they heard there was a lot of interesting "mixed veggie" media!


2. Why did the vegan go to the dentist?
   Because they heard they could get a good root canal!

While I remain committed to meeting the deadline I set forth for delivering this project towards the end of September, I will provide continuous updates whenever there's a major breakthrough in the process. The short-term goal is to complete the learning process for all four stages of LangChain, as demonstrated in the project roadmap, and then start working towards a commendable personal assistant powered by LLM(s).