SqlP

SqlP is a powerful ORM-like bridge between PHP and SQL in Vvveb CMS. It allows developers to write native SQL queries in .sql files while automatically generating PHP classes to interface with them. This approach maintains the performance of raw SQL without the "spaghetti code" of embedding long query strings inside PHP files.

---

File Structure

SQL definitions are stored based on the database engine to ensure cross-compatibility: app/sql/{db-engine}/{filename}.sql

• db-engine: mysqli, pgsql, or sqlite.
• Automatic Generation: For every .sql file, Vvveb generates a corresponding PHP class on the fly (e.g., product.sql becomes ProductSQL class).

---

Procedure Definition

Procedures are defined using a syntax similar to standard SQL CREATE PROCEDURE, but they include special IN and OUT parameters to handle PHP data mapping.

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

Parameter Types

Type	Description
IN	Input variables passed from PHP (e.g., INT, CHAR, ARRAY).
OUT	Defines the return format of the query results.

Output Types (OUT)

• fetch_all: Returns an array of all matching rows.
• fetch_row: Returns a single associative array (for LIMIT 1).
• fetch_one: Returns a single scalar value (e.g., a count or a specific column).
• insert_id: Returns the ID generated by the last INSERT statement.
• affected_rows: Returns the number of rows modified by UPDATE or DELETE.

---

SQL 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()) {
	}		

}

PHP Usage

Once the .sql file is saved, use the generated class in your PHP code:

use Vvveb\Sql\ProductSQL;

$db = new ProductSQL();

// Fetch data (returns array/object based on OUT param)
$product = $db->get(['product_id' => 3]);

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

// Insert data
$newId = $db->add([
    'product' => ['name' => 'New Camera', 'price' => 599],
    'site_id' => 1
]);

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

Macros

SqlP uses custom macros to handle dynamic logic directly within the SQL file.

1. Logic Macros (@IF, @ELSE)

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 native 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)

2. Helper Macros

• @SQL_COUNT(col, table): Automatically generates a count query based on the preceding SELECT statement, removing limits and optimizing columns.
• @LIST(:array): Expands an array into col1=val1, col2=val2 (ideal for UPDATE).
• @KEYS(:array): Returns a comma-separated list of array keys (ideal for INSERT).
• @SQL_LIMIT(:start, :limit): Handles cross-database syntax differences for pagination.

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) 

3. Loop Macro (@EACH)

EACH

Repeats a query for every item in a PHP 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. Use these aliases to return custom associative arrays instead of standard nested lists.

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