Working with database (direct SQL)

When executing server scripts, rr object of RR prototype (available in functions and controllers as this) contains a reference to database connection (object of LNSQL prototype).

Main methods are described bellow:

Name	Parameters	Returns	Description
Public methods
rollback			Rollbacks current transaction
commit			Commits current transaction (also will be automatically executed at the moment of HTTP-request end)
execute	filename String, params Object		Executes SQL-query located in a file
execute_and_fetch	filename String, params Object	Array of Object	Executes SQL-query located in a file and returns an array of result objects
execute_and_fetch_one	filename String, params Object	Object	Executes SQL-quest located in a file and return first result row as an object (undefined, if query returned no rows)
execute_and_fetch_h	filename String, params Object	Array of Object	Recursively executes SQL-query located in a file to get tree-like (hierarchical) result.
execute_single	query String, params Object		Same as execute, but first parameter is not a filename but a query text itself
execute_and_fetch_single	query String, params Object	Array of Object	Same as execute_and_fetch, but first parameter is not a filename but a query text itself
execute_and_fetch_one_single	query String, params Object	Object	Same as execute_and_fetch_one, but first parameter is not a filename but a query text itself
execute_and_fetch_h_single	query String, params Object	Array of Object	Same as execute_and_fetch_h, but first parameter is not a filename but a query text itself
rebuild_execute	filename String, params Object
rebuild_execute_and_fetch	filename String, params Object	Array of Object
rebuild_execute_and_fetch_one	filename String, params Object	Object
explain	filename String	html String	Returns a prettified HTML-table with query execution plan
explain_analyze	filename String,params Object	html String
explain_single	query String	html String
explain_analyze_single	query String,params Object	html String
System methods
last_insert_id	tablename String	Integer	Returns a last inserted primary key of given table.
load	filename String	String	Loads file from disk
rebuild	query String, params Object	String	Rebuilds SQL-query using SQL-template engine. Returns text of new SQL query
logs_to_array		Array
logs_to_html_comment		html String	Returns statistics of SQL queries execution formatted as HTML comment

Details of execute*

Functions, accepting filename as a first argument, accept filename without ".sql" and "sql/", so for a file "code/sql/users/list_active.sql" you will need to pass "users/list_active".

Prior execution, a query is prepared (prepare), with all named bind variables to be associated either with values of params object, or (if such parameter is not in params object), with params of rr.fields object.

Bind example

// (somewhere inside controller)
var results=this.site.sql.execute_and_fetch("files/list_filtered",{folder_id:100});

code/sql/files/list_filtered.sql

select id,ext,orig_filename
from Tfiles
where
    file_folder_id=:folder_id and
    filesize>=:minsize and
    filesize<=coalesce(:maxsize,100000000)

When calling URL http://mysite.com/?minsize=500, following variable will be bound:

Variable	Value	Data source
folder_id	100	params
minsize	500	rr.fields
maxsize	undefined	-nowhere-

Details of execute_and_fetch_h

This query is used for sequential (recursive) reading from the database - for each element the query is called again with a new value of the parent_id parameter.

This method is currently considered deprecated and its use is not recommended, since it generates a lot of queries to the database and it is better to create a query using with recursive.

Details of rebuild

TODO