Stored SQL

You'll write .sql files in your BigDb app's sql/ dir. These get compiled and serialized into sql/queries for faster loading at runtime, then executed by file_name.query_name.

Docs

Sample Structure
Sample Stored create.sql
Paramater Binding
Naming Queries
Sample Stored article.sql (with paramater binding example)

Sample Structure

You can name your .sql files however you like. Below is merely a suggestion.

db/  
    migrate/ ...  
    orm/ ...  
    sql/  
        create.sql  -- For your CREATE TABLE and other statements only used during initialization/migrations.  
        main.sql    -- For commonly used or otherwise unorganized queries  
        blog.sql    -- For queries relating to the 'blog' table

Tip: To keep SQL versioned for migrations, create an sqlv1 dir, and call $db->addSqlDir(...) during migrate/v1/up.php.

Sample Stored create.sql

create.sql:

-- @query(article, -- END)   
DROP TABLE IF EXISTS `article`;  
CREATE TABLE IF NOT EXISTS `article` (  
    `id` int(11) PRIMARY KEY,  
    `title` varchar(256),  
    `description` TEXT,  
    `slug` varchar(256),  
    `created_at` datetime,   
    `status` varchar(30),  
    `related_article_id` int(11)  
) ;   
-- END  
  
-- @query(author, -- END)   
DROP TABLE IF EXISTS `author`;  
CREATE TABLE IF NOT EXISTS `author` (  
    `id` int(11) PRIMARY KEY,  
    `name` varchar(256),  
    `description` TEXT,  
    `slug` varchar(256),  
    `created_at` datetime,   
    `status` varchar(30)  
) ;   
-- END

Paramater Binding

Stored queries can have bindable paramaters.

WARNING: Neither uses the built-in binding feature on PDO, and should be used with caution.

:name: The passed-in value is PDO::quote()ed and :name is replaced with the quoted value.
{$name}: The passed-in value replaces {$name} as-is, without any PDO::quote()

Naming Queries

For @query(get_books_by_author) in file main.sql, the query's name is main.get_books_by_author.

A stored statement begins with:

-- @query(name, -- delimiter)

The SQL statement starts on the -- @query(...) line and ends on the -- delimiter line. The delimiter should start with -- because this denotes a comment in mysql.

If you do not include a delimiter, then a semicolon (;) is used:

-- @query(name)

The delimiter is needed if there are multiple sql statements, but I like to use it on most named queries, especially if they're more than one line.

Sample Stored article.sql

article.sql: (Notice :status and {$where_clause}. See 'Parmater Binding' above.)

-- @query(sample_data, -- END)  
INSERT INTO `article` (`id`, `title`, `description`, `slug`, `created_at`, `status`, `related_article_id`)  
    VALUES  
    (1, 'One', 'Desc 1', 'one', CURRENT_TIMESTAMP, 'public', NULL),  
    (2, 'Two', 'Desc 2', 'two', CURRENT_TIMESTAMP, 'public', NULL),  
    (3, 'Three', 'Desc 3', 'three', CURRENT_TIMESTAMP, 'public', 1),  
    (4, 'Four, Private', 'Desc 4', 'four-private', CURRENT_TIMESTAMP, 'private', 1)  
;  
-- END  
  
-- @query(get_public)  
SELECT * FROM `article` WHERE `status` LIKE 'public';  
  
-- @query(get_private)  
SELECT * FROM `article` WHERE `status` LIKE 'private';  
  
-- @query(get_where)  
SELECT * FROM `article` WHERE `status` LIKE :status;  
  
-- @query(insert_with_status, -- END)  
INSERT INTO `article` (`id`, `title`, `description`, `slug`, `created_at`, `status`, `related_article_id`)  
    VALUES  
    (5, 'Status', 'Status 1', 'status', CURRENT_TIMESTAMP, :status, NULL)  
;  
-- END  
  
-- @query(get_where_clause)  
SELECT * FROM `article` {$where_clause};

Taeluf.com