FMD Introduction

[ONiX]

FMD is short for our FastMemoryDataset ~ a TDataset descendant.

It was built to assist in our DB benchmark project (codename ball buster).

Over a decade ago, we acquired the Halcyon Database components from Richard Griffin. Since then, I have redesigned the code to perform beyond the original gs6 product. However, I noticed a core design that needed to be redesigned. Adding a File I/O Buffer.

So, as I started building an abstract virtual disk ~ I started comparing my Memory Dataset against the few that I had purchased over the decades. Once I could beat them in a bulky CRUD (Create, Read, Update, Delete) test scenario. I kept building something I thought was original. A. Support multiple memory tables at the same time ~ so I could make Master/Detail relationships. B. To keep my tests going from day to day, it needed to introduce persistence. However, I didn't want to slack-off, and simply do SaveToFile, LoadFromFile. Instead, I introduced a background thread that introduced ACID compliance with a full "to disk" journal. Perfect for the not-so wonderful power we have in Crestview Florida. Now I had a product to mention to people.

[ONiX]

However, the few people I mentioned the early version of FMD to ~ wanted SQL, even the basic SQL:1992 as they had no idea how you would work with a database using entirely TDataset commands. We even supplied a collection of tutorials, explaining TDataset navigation, filtering, creating an index, and basic CRUD concepts using TDataset.

After a couple months of dealing with the less educated developer. I started my endeavor of implementing the basic SQL:1999 (aka SQL3) parser. However, anyone who knows me, I could not just stop at SQL:1999. I read-up on SQL:2003 standard, also known as ISO/IEC 9075:2003 and compliance requirements... FMD already had most of the hard parts already built. It just required a years of effort to wire a full blown tokenizer, parser/lexer, and an AST compiler to produce an SQL:2003 parser that was robust enough to handle complex joins = refers to the use of explicit, standard-compliant JOIN syntax for combining data from multiple tables, as opposed to older, less clear implicit syntax. The standard encourages placing join conditions within the FROM clause using ON or USING keywords, which improves readability and manageability, especially when multiple join types (e.g., INNER, LEFT, RIGHT, FULL OUTER, CROSS) are used in the same query.

1. The standard introduced the SEQUENCE object, providing a standardized way to generate unique, sequential numbers, often used for primary key generation.

2. The MERGE statement was introduced, allowing for the conditional insertion, update, or deletion of rows in a table based on matching conditions with another table or query. This statement combines the functionality of INSERT, UPDATE, and DELETE into a single, efficient operation.

3. Extensions to the CREATE TABLE statement were included, such as CREATE TABLE AS (to create a new table based on the result of a SELECT statement) and CREATE TABLE LIKE (to create a new table with the same structure as an existing one).

[ONiX]

-- FastMemDataSet Demo Script
-- Create memory tables
CREATE MEMORY TABLE people (id INTEGER, name STRING, age INTEGER, active BOOLEAN);
CREATE MEMORY TABLE customers (id INTEGER, organization_id INTEGER, contact_name STRING);
CREATE MEMORY TABLE organizations (id INTEGER, name STRING, type STRING);
CREATE MEMORY TABLE products (id INTEGER, name STRING, price FLOAT);
CREATE MEMORY TABLE leads (id INTEGER, customer_id INTEGER, product_id INTEGER, status STRING); -- Master-detail: leads detail to customers

-- Create complex indexes
CREATE INDEX idx_people_name ON people (name) TYPE BTree UNIQUE;
CREATE INDEX idx_customers_org ON customers (organization_id) TYPE BTree;
CREATE INDEX idx_leads_customer ON leads (customer_id) TYPE BTree;
CREATE COMPOSITE INDEX idx_leads_full ON leads (customer_id, product_id) TYPE BTree; -- Complex composite index

-- Link indexes to simulate "link table" (via joined query)
SELECT * FROM leads INNER JOIN customers ON leads.customer_id = customers.id INNER JOIN products ON leads.product_id = products.id WHERE leads.status = 'open';

-- Import CSVs (100,000 rows each, simulate fast inserts)
IMPORT CSV 'people.csv' INTO people;
IMPORT CSV 'customers.csv' INTO customers;
IMPORT CSV 'organizations.csv' INTO organizations;
IMPORT CSV 'products.csv' INTO products;
IMPORT CSV 'leads.csv' INTO leads;

-- Demonstrate compression (background algorithm reduces memory footprint)
COMPRESS DATA; -- Triggers compression, log memory before/after

-- Crash simulation (for recovery demo)
-- In demo, this will "crash" mid-execution; recovery replays logs

[ONiX]

When you purchase FMD, it contains a tutorials.zip (A full 90 day class I designed to help you utilitize FMD's capabilities in your application. * Remember, FMD is an embedded SQL:2003 engine... not a standalone SQL server (yet).

-- This curriculum provides a progressive learning path from basics to advanced
-- Structure: Days 1-30 (Fundamentals), Days 31-60 (Intermediate), Days 61-90 (Advanced)

[ONiX]

-- DAY 1: Introduction to Databases and SELECT Statement
-- -----------------------------------------------------------------------------
-- Sample Database Setup
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_date DATE,
gpa DECIMAL(3,2)
);

-- Basic SELECT - Retrieve all columns
SELECT * FROM students;

-- Select specific columns
SELECT first_name, last_name, email FROM students;

-- EXERCISES:
-- 1. Select only student names (first and last)
-- 2. Select all student information
-- 3. Select student_id and gpa

[ONiX]

-- DAY 2: Filtering Data with WHERE Clause
-- -----------------------------------------------------------------------------
INSERT INTO students VALUES
(1, 'Alice', 'Johnson', 'alice.j@email.com', DATE '2023-09-01', 3.75),
(2, 'Bob', 'Smith', 'bob.s@email.com', DATE '2023-09-01', 3.50),
(3, 'Carol', 'Williams', 'carol.w@email.com', DATE '2023-09-02', 3.90),
(4, 'David', 'Brown', 'david.b@email.com', DATE '2023-09-03', 3.20),
(5, 'Eve', 'Davis', 'eve.d@email.com', DATE '2023-09-03', 3.85);

-- WHERE with comparison operators
SELECT * FROM students WHERE gpa > 3.5;

SELECT * FROM students WHERE enrollment_date = DATE '2023-09-01';

-- Multiple conditions with AND, OR
SELECT * FROM students
WHERE gpa >= 3.7 AND enrollment_date >= DATE '2023-09-01';

SELECT * FROM students
WHERE gpa > 3.8 OR last_name = 'Smith';

-- EXERCISES:
-- 1. Find students with GPA less than 3.5
-- 2. Find students enrolled on or after September 2nd
-- 3. Find students with GPA between 3.5 and 3.8

[ONiX]

-- DAY 3: Sorting Results with ORDER BY
-- -----------------------------------------------------------------------------
-- Sort ascending (default)
SELECT * FROM students ORDER BY gpa;

-- Sort descending
SELECT * FROM students ORDER BY gpa DESC;

-- Multiple column sorting
SELECT * FROM students
ORDER BY enrollment_date DESC, last_name ASC;

-- EXERCISES:
-- 1. Sort students by last name alphabetically
-- 2. Sort students by GPA (highest first)
-- 3. Sort by enrollment date (newest first), then by GPA (highest first)

[ONiX]

-- DAY 4: DISTINCT, LIMIT, and OFFSET
-- -----------------------------------------------------------------------------
CREATE TABLE courses (
course_id INTEGER PRIMARY KEY,
course_name VARCHAR(100) NOT NULL,
department VARCHAR(50),
credits INTEGER,
instructor VARCHAR(100)
);

INSERT INTO courses VALUES
(101, 'Introduction to Programming', 'Computer Science', 3, 'Dr. Smith'),
(102, 'Data Structures', 'Computer Science', 4, 'Dr. Johnson'),
(103, 'Calculus I', 'Mathematics', 4, 'Dr. Williams'),
(104, 'English Literature', 'English', 3, 'Dr. Brown'),
(105, 'Physics I', 'Physics', 4, 'Dr. Davis'),
(106, 'Database Systems', 'Computer Science', 3, 'Dr. Smith');

-- DISTINCT - Remove duplicates
SELECT DISTINCT department FROM courses;

SELECT DISTINCT instructor FROM courses;

-- LIMIT - Restrict number of rows (SQL:2003 uses FETCH FIRST)
SELECT * FROM courses
ORDER BY course_id
FETCH FIRST 3 ROWS ONLY;

-- OFFSET with FETCH (pagination)
SELECT * FROM courses
ORDER BY course_id
OFFSET 2 ROWS
FETCH FIRST 3 ROWS ONLY;

-- EXERCISES:
-- 1. Get unique list of departments
-- 2. Get first 2 courses ordered by course_name
-- 3. Get courses 3-5 when ordered by credits

[ONiX]

-- DAY 5: Working with NULL Values
-- -----------------------------------------------------------------------------
CREATE TABLE assignments (
assignment_id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id INTEGER,
assignment_name VARCHAR(100),
submission_date DATE,
grade DECIMAL(5,2)
);

INSERT INTO assignments VALUES
(1, 1, 101, 'Homework 1', DATE '2023-09-15', 95.0),
(2, 1, 102, 'Homework 1', DATE '2023-09-16', NULL),
(3, 2, 101, 'Homework 1', NULL, NULL),
(4, 3, 103, 'Homework 1', DATE '2023-09-14', 88.5),
(5, 4, 101, 'Homework 1', DATE '2023-09-17', 92.0);

-- IS NULL
SELECT * FROM assignments WHERE grade IS NULL;

-- IS NOT NULL
SELECT * FROM assignments WHERE submission_date IS NOT NULL;

-- COALESCE - Return first non-NULL value
SELECT
assignment_id,
student_id,
COALESCE(grade, 0) AS grade_with_default
FROM assignments;

-- EXERCISES:
-- 1. Find all assignments without grades
-- 2. Find all assignments that have been submitted
-- 3. Display grades, showing 'Not Graded' for NULL values (use CASE)

[ONiX]

-- DAY 6: Basic String Operations
-- -----------------------------------------------------------------------------
-- CONCAT - Combine strings
SELECT
student_id,
first_name || ' ' || last_name AS full_name
FROM students;

-- UPPER, LOWER
SELECT
UPPER(first_name) AS upper_name,
LOWER(last_name) AS lower_name
FROM students;

-- SUBSTRING
SELECT
first_name,
SUBSTRING(first_name FROM 1 FOR 3) AS first_three_chars
FROM students;

-- LENGTH
SELECT
first_name,
CHARACTER_LENGTH(first_name) AS name_length
FROM students;

-- TRIM
SELECT TRIM(' Hello World ') AS trimmed_text;

-- EXERCISES:
-- 1. Create email usernames from first and last names (lowercase)
-- 2. Get the first letter of each student's first name
-- 3. Find students whose first name is longer than 5 characters