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 per app and engine in admin/sql/{mysqli,pgsql,sqlite}/ and app/sql/{mysqli,pgsql,sqlite}/ (the autoloader falls back to admin/ when a file is missing in the active app; plugin models live in plugins/{slug}/sql/{engine}/).

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
	)
	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 storage/model/<app>/productsql.mysqli.php (i.e. DIR_STORAGE . 'model/' . APP . '/' . $name . '.' . DB_ENGINE . '.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)

Helper Macros

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'

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' ]
	]
];		

---

Result Formatting & Aliases

_ table alias

By default, SqlP wraps results in an array keyed by the {table name}. Use the _ alias to flatten the result.

• Standard: ['product' => ['name' => 'Phone']]
• With AS _: ['name' => 'Phone']

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 AS _
		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

Access data from a previous query within the same procedure.

• @result.table_name: Returns the last insert ID or row from that table.
• @result.column_name: If using the _ alias, access the column value directly.

		-- 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;