SqlP

SqlP is a bridge between PHP and SQL that allows querying the database with raw SQL avoiding the spagetti of treating SQL queries as strings in PHP.

SQL files are stored in app/sql/db-engine where db-engine can be mysqli, pgsql or sqlite.

Structure

For each .sql file a class is generated on the fly where the methods of the class are sql procedures defined in the .sql file.

For example product.sql

-- Products

    -- get one product

    CREATE PROCEDURE get(
        IN product_id INT,
        IN slug CHAR,
        OUT fetch_row 
    )
    BEGIN
        -- product
        SELECT *
            FROM product as _ -- (underscore) _ means that data will be kept in main array
        WHERE 1 = 1 

        @IF isset(:product_id)
        THEN
            AND product_id = :product_id
        END @IF             

        @IF isset(:slug)
        THEN
            AND slug = :slug
        END @IF 

        LIMIT 1;    

    END


    -- Edit product

    CREATE PROCEDURE edit(
        IN product ARRAY,
        IN product_id INT,
        OUT affected_rows
    )
    BEGIN

        -- SELECT * FROM product_option WHERE product_id = :product_id;

        -- allow only table fields and set defaults for missing values
        @FILTER(:product, product);

        UPDATE product 

            SET @LIST(:product) 

        WHERE product_id = :product_id
    END 


    -- Add new product

    CREATE PROCEDURE add(
        IN product ARRAY,
        IN site_id INT,
        OUT insert_id,
        OUT insert_id
    )
    BEGIN

        -- allow only table fields and set defaults for missing values
        :product  = @FILTER(:product, product);

        INSERT INTO product 

            ( @KEYS(:product) )

        VALUES ( :product );

        INSERT INTO product_to_site 

            ( product_id, site_id )

        VALUES ( @result.product, :site_id );


    END


    -- get all products 

    CREATE PROCEDURE getAll(

        -- variables
        IN  language_id INT,
        IN  user_group_id INT,
        IN  site_id INT,
        IN  search CHAR,

        -- pagination
        IN  start INT,
        IN  limit INT,

        -- return array of products for products query
        OUT fetch_all,
        -- return products count for count query
        OUT fetch_one,
    )
    BEGIN

        SELECT * FROM product AS product

            LEFT JOIN product_to_site p2s ON (product.product_id = p2s.product_id) 
            WHERE p2s.site_id = :site_id

            @IF isset(:search)
            THEN 

                AND name LIKE :search

            END @IF         

        LIMIT :start, :limit;


        SELECT count(*) FROM (

            @SQL_COUNT(product.product_id, product) -- this takes previous query removes limit and replaces select columns with parameter product_id

        ) as count;


    END


    -- delete product

    CREATE PROCEDURE delete(
        IN  product_id ARRAY,
        OUT affected_rows
    )
    BEGIN

        DELETE FROM product WHERE product_id IN (:product_id)

    END

Will generate a file productsql.mysqli.php with the following structure:

<?php 

/*
Generated from /home/www/vvveb/vvveb/admin/sql/mysqli//product.sql
*/
namespace Vvveb\Sql;

use \Vvveb\System\Db;

class ProductSQL {

    function get($params = array()) {
    }       

    function edit($params = array()) {
    }       


    function add($params = array()) {
    }       

    function getAll($params = array()) {
    }       

    function delete($params = array()) {
    }       

}

Then you can use the class in php like this:

use Vvveb\Sql\ProductSQL;

$product = new ProductSql();

//get product with product_id = 3
$data = $product->get(['product_id' => 3);

//get first 3 products
$data = $product->getAll(['limit' => 3);

//add new product
$product->add(['product' => ['name' => 'Product 1', 'price' => 100]]);

//edit product with product_id = 1
$product->edit(['product' => ['name' => 'Product 2', 'price' => 100], 'product_id' => 1]);

//delete product with product_id = 3
$data = $product->delete(['product_id' => 3);

Procedure declaration

PROCEDURE getAll(
        IN product_id INT,
        IN slug CHAR,
        IN language_id INT,
        OUT fetch_row, 
        OUT fetch_all, 
        OUT fetch_one,
        OUT insert_id,
        OUT affected_rows
    )
    BEGIN
    END

The procedure has two kinds of parameters with the IN parameters you define what variables and type the procedure accepts that and can be used in the queries.

The OUT parameters define what kind of output the query should have, a definition is expected for each query in the procedure and it can be of the following kind:

• multiple rows with fetch_all
• a single row with fetch_row usually queries with LIMIT 1
• a single value with fetch_one, usually queries with LIMIT 1 and one column
• the last insert id with insert_id for INSERT queries
• the number of affected rows with affected_rows for UPDATE queries

Macros

If macro

    @IF isset(:product_id)
    THEN
        AND product_id = :product_id
    END @IF     

With @IF you can conditionally include sql code, the condition is valid php code, you can use any php code with multiple conditions or different functions like

@IF isset(:product_id) AND !empty(:product_id)

If else macro

    @IF isset(:product_id)
    THEN
        AND product_id = :product_id
    @ELSE
        AND product_id = 0
    END @IF     

With @IF @ELSE macro

@IF isset(:product_id) AND !empty(:product_id)

SQL_COUNT

Use this macro to run sql count for the previous query to avoid duplicating the query code. The macro will automatically replace all the columns (product_id, name) from the query with the first parameter products.product_id, the second parameter is the table name.

    SELECT product_id, name FROM products 
        INNER JOIN product_content pc ON pc.product_id = product.product_id
    WHERE vendor_id = 1; 

    SELECT count(*) FROM (

        @SQL_COUNT(products.product_id, product) -- this takes previous query removes limit and replaces select columns with parameter product_id

    ) as count;

LIST

Will expand the array into a column=value list in the format used by UPDATE queries.

column1=value, column2=value2, column3=value3

UPDATE product 
    SET @LIST(:product_update) 

EACH

Repeat the query for each item in the array, :each is the item in the array

    @EACH(:product_digital_asset) 
        INSERT INTO  product_to_digital_asset 

            ( digital_asset_id, product_id)

        VALUES ( :each, :product_id );

    -- example with KEYS macro

    @EACH(:product_attribute) 
        INSERT INTO product_attribute 

            ( @KEYS(:each), product_id)

        VALUES ( :each, :product_id );

KEYS

Keys macro will use the key instead of value for array values similar to php array_keys In the following example

    @EACH(:product_attribute) 
        INSERT INTO product_attribute 

            ( @KEYS(:each), product_id)

        VALUES ( :each, :product_id );

$product_attribute = [1 => ['name' => 'Product 1', 'price' => 30], 2 => 'Product 2', 'price' => 50];

:each will be

'Product 1', '30'

and @KEYS(:each) will be

'name', 'price'

SQL limit

Use this macro to avoid LIMIT syntax differences between pgsql and mysql

    @IF isset(:limit)
    THEN
        @SQL_LIMIT(:start, :limit)
    END @IF;        

array_key and array_value column alias

With array_key and array_value column aliases you can change the format of the returned results to be more compact

For example

        -- stock status 
        SELECT 

            stock_status_id
            name

        FROM stock_status as stock_status_id;

will return

['stock_status_id' => 
    [   
        0 => ['stock_status_id' => 5, 'name' => 'status 1' ],
        1 => ['stock_status_id' => 8, 'name' => 'status 2' ],
        2 => ['stock_status_id' => 9, 'name' => 'status 3' ]
    ]
];      

with the aliases

        -- stock status 
        SELECT 

            stock_status_id as array_key, -- stock_status_id as key
            name as array_value -- only set name as value and return  

        FROM stock_status as stock_status_id;

the return data will be

['stock_status_id' => 
    [   
        5 => 'status 1',
        8 => 'status 2',
        9 => 'status 3'
    ]
];      

with only array_key alias

        -- stock status 
        SELECT 

            stock_status_id as array_key, -- stock_status_id as key
            name

        FROM stock_status as stock_status_id;

the return data will be

['stock_status_id' => 
    [   
        5 => ['stock_status_id' => 5, 'name' => 'status 1' ],
        8 => ['stock_status_id' => 8, 'name' => 'status 2' ],
        9 => ['stock_status_id' => 9, 'name' => 'status 3' ]
    ]
];      

_ table alias

With _ table alias the returned values will be added to the top most level in the array.

For example with

        -- select example

        -- product
        SELECT product.*
            FROM product
        WHERE product.vendor_id = 1 LIMIT 1;        

The values will be returned in the array using the table name for array key

['product' => [ 
        ['name' => 'product 1], 
        ['name' => 'product 2], 
    ]
]

With the _ alias

        -- select example

        -- product
        SELECT product.*
            FROM product
        WHERE product.vendor_id = 1 LIMIT 1;        

The values will be returned without the table name as key and the values will be appended to the array directly

[ 
        ['name' => 'product 1], 
        ['name' => 'product 2], 
]

@result.table_name

When you need to use a value from a previous query like a last_insert id or a column id you can use @result.table_name

        -- select example

        -- product
        SELECT product.*
            FROM product
        WHERE product.vendor_id = 1 LIMIT 1;        

        -- subscription
        SELECT product_subscription.*
            FROM product_subscription
        WHERE product_subscription.product_id = @result.product;        


        -- insert example

        -- product

        INSERT INTO product 

            ( @KEYS(:product_data) )

        VALUES ( :product_data );

        -- subscription
        SELECT product_subscription.*
            FROM product_subscription
        WHERE product_subscription.product_id = @result.product;        

When you use _ for table name alias because values are in the top level array you can use @result.column_name directly

        -- select example

        -- product
        SELECT product.*
            FROM product AS _
        WHERE product.vendor_id = 1 LIMIT 1;        

        -- subscription
        SELECT product_subscription.*
            FROM product_subscription
        WHERE product_subscription.product_id = @result.product_id;