SQL-only Programming and Templating Techniques

[matthewlarkin]

SQL for Programming and Templating

While using SQLPage, I now find myself asking "how can I do this in SQL?" for use cases I might have typically reached for PHP or Python for.

So I've come across some techniques for using SQL (in these exapmles, SQLite) for tasks typically associated with the above mentioned programming languages, with focus on recursive looping and data templating.

Here are some simple examples that illustrate some core concepts. I wonder if you have any to share? 🙂

1. Simple Ordered Sequence
2. Fibonacci Sequence
3. Hierarchical Category Rendering
4. Calendar Dates

🌿 - - 🌿 - - 🌿

⭐️ Creating Sequences with Recursive CTEs

Creating a simple number sequence can also be achieved using Recursive CTEs in SQLite.

SQL Query for Number Sequence

WITH RECURSIVE
  number_sequence (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM number_sequence WHERE n < 10
)
SELECT n FROM number_sequence;

Number Sequence Output

n
1
2
...
9
10

🌿 - - 🌿 - - 🌿

⭐️ Generating a Fibonacci Sequence

Generating a Fibonacci sequence is a classic example of a task involving loops in programming. SQLite can achieve this using a Recursive Common Table Expression (CTE).

SQL Query for Fibonacci Sequence

-- Create a static table for storing the Fibonacci sequence
CREATE TABLE static_fibonacci (id INTEGER PRIMARY KEY AUTOINCREMENT, fib_number);

-- Generate the Fibonacci sequence using a Recursive CTE
WITH RECURSIVE temp_fibonacci (val1, val2, level) AS (
    SELECT 0, 1, 1
    UNION ALL
    SELECT val2, val1 + val2, level + 1 FROM temp_fibonacci WHERE level < 1500
)
INSERT INTO static_fibonacci (fib_number)
-- convert the values to REAL to avoid integer overflow
SELECT val1 FROM temp_fibonacci;

-- Retrieve the results
SELECT * FROM static_fibonacci;

Fibonacci Sequence Output

ID	Fibonacci Number
1	0
2	1
3	1
4	2
5	3
6	5
...	...
9	21
10	34
..	..
93	7540113804746346429
94	1.22001604151219e+19
..	..
1476	8.07763763215622e+307
1477	1.3069892237634e+308
1478	Inf
1479	Inf

Note: You'll notice at row 94, we start using exponential notation to represent the numbers, and at row 1478, we get Inf for infinity due to the precision limits of 8-byte floating point numbers.

🌿 - - 🌿 - - 🌿

⭐️ Managing Hierarchical Data

Managing hierarchical data, such as organizing blog posts into categories and subcategories, is a complex task that SQLite can handle effectively. This showcases how SQL can be used for templating (very typically done with languages like PHP, JavaScript, or Python).

SQL Setup for Blog Posts and Categories

We create tables for blog posts and categories, with the latter including a self-referencing foreign key for hierarchy, and insert some sample records.

-- Create a table for categories with a self-referencing foreign key for hierarchy
CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- Create a table for blog posts
CREATE TABLE blog_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Insert categories and subcategories
INSERT INTO categories (name, parent_id) VALUES 
('Technology', NULL), 
('Programming', 1), 
('Databases', 2), 
('Travel', NULL), 
('Europe', 4);

-- Insert some blog posts
INSERT INTO blog_posts (title, content, category_id) VALUES 
('SQL for Beginners', 'Content about SQL...', 3),
('A Trip to Paris', 'Content about Paris...', 5);

Querying Hierarchical Data

Now, we can use a recursive CTE to retrieve the blog posts along with their categories and subcategories, resulting in a breadcrumb-like path.

-- Query to retrieve blog posts along with their category path using a recursive CTE
WITH RECURSIVE category_path (id, name, path) AS (
    SELECT id, name, name AS path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, cp.path || ' > ' || c.name
    FROM category_path cp JOIN categories c ON cp.id = c.parent_id
)
SELECT bp.title, bp.content, cp.path
FROM blog_posts bp
JOIN category_path cp ON bp.category_id = cp.id;

Hierarchical Data Output

The recursive query above produces a list of blog posts with their category paths 🙌

Title	Content	Category Path
SQL for Beginners	Content about SQL...	Technology > Programming > Databases
A Trip to Paris	Content about Paris...	Travel > Europe

🌿 - - 🌿 - - 🌿

⭐️ Generating Calendar Dates

Generating a calendar with dates and weekdays can be a useful task for scheduling applications or reports. SQLite, through its recursive CTEs, can be employed to create a date series along with their corresponding weekdays.

SQL Query for Calendar Generation

WITH RECURSIVE date_series (date) AS (
    SELECT '2023-01-01' -- Start date
    UNION ALL
    SELECT date(date, '+1 day')
    FROM date_series
    WHERE date < '2023-02-01' -- End date
)
SELECT 
    date, 
    strftime('%w', date) as weekday_index, -- Sunday = 0, Monday = 1, ..., Saturday = 6
    CASE strftime('%w', date)
        WHEN '0' THEN 'Sunday'
        WHEN '1' THEN 'Monday'
        WHEN '2' THEN 'Tuesday'
        WHEN '3' THEN 'Wednesday'
        WHEN '4' THEN 'Thursday'
        WHEN '5' THEN 'Friday'
        WHEN '6' THEN 'Saturday'
    END as weekday_name
FROM date_series;

Calendar Output

This query generates a list of dates for a month, along with their respective day of the week, -- a handy tool for calendar-related operations.

Date	Weekday Index	Weekday Name
2023-01-01	0	Sunday
2023-01-02	1	Monday
...	...	...
2023-01-29	0	Sunday
2023-01-30	1	Monday
2023-01-31	2	Tuesday

🌿 - - 🌿 - - 🌿

So, SQL's capabilities can extend beyond simple data storage and retrieval. As demonstrated, it can handle more complex tasks like generating sequences and managing hierarchical data.

I find this exciting, and I'm looking forward to exploring these concepts further with SQLPage! If you have other examples or want to build upon these, I'd love to see what techniques you use!

[lovasoa]

Thank you very much for these useful tips. When working with sqlite and wanting more advanced features another useful thing to know is that SQLPage supports sqlite extensions. There are many useful extensions; I like this list: https://github.com/nalgeon/sqlean

[matthewlarkin]

Thank you for the resource! I see they recommend a nifty SQLite package manger too -- like pip or composer for SQLite -- very intriguing! Thanks again! 🙏

[DSMejantel]

Hi,
Good idea to share this examples !
What i love with Sqlpage, it's the fact we have no limits for our imagination and so we can write good code for all ours ideas.
I wrote this morning a CTE to follow queen's bee character here on line 127 : https://github.com/DSMejantel/Carnet-de-Rucher/blob/main/ruche.sql
Thanks for your job !