Active Record Library

revIgniter uses a modified version of the Active Record Database Pattern. This pattern allows information to be retrieved, inserted, and updated in your database with minimal scripting. In some cases only one or two lines of code are necessary to perform a database action.

Beyond simplicity, a major benefit to using the Active Record features is that it allows you to create database independent applications, since the query syntax is generated by each database adapter. It also allows for safer queries, since the values are escaped automatically by revIgniter.

Note: If you intend to write your own queries you can disable this library in your database config file, allowing the core database library and adapter to utilize fewer resources.

Selecting Data
Parentheses
Subqueries
Inserting Data
Updating Data
Deleting Data
Active Record Caching

Selecting Data

The following handlers allow you to build SQL SELECT statements.

rigDbGet()

Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table:

put rigDbGet("mytable") into tQuery

-- Produces: SELECT * FROM mytable

If you query more than one table the first parameter can be a comma-delimited list or an array too. The second and third parameters enable you to set a limit and offset clause:

put rigDbGet("mytable", 10, 20) into tQuery

-- Produces: SELECT * FROM mytable LIMIT 20, 10 (in MySQL. Other databases have slightly different syntax)

You'll notice that the above function is assigned to a variable named tQuery, which can be used to show the results:

put rigDbGet("mytable") into tQuery

repeat for each key tKey in tQuery["resultarray"]

	put tQuery["resultarray"][tKey] into tRow
	put tRow[2] & comma after tResultData
end repeat

In the example above index 2 of tRow defines the value in the second column of the current row. Note: If you need to keep the order of the result values, you need to add an incrementer:

put rigDbGet("mytable") into tQuery

put 0 into tIncr
repeat for each key tKey in tQuery["resultarray"]
	add 1 to tIncr

	put tQuery["resultarray"][tIncr] into tRow
	put tRow[2] & comma after tResultData
end repeat

Please visit the result handlers page for a full discussion regarding result generation.

Set the fourth parameter to true to get a subquery as a condition for the main query like:

put rigDbGet("employees", , , TRUE) into tSubQuery

Please see Subqueries.

For debugging purposes you can set the fifth parameter to true. This returns the query string without sending a request to the database (useful if you want to check the syntax of complex queries):

put rigDbGet( , , , , TRUE) into tQueryString

rigDbGetWhere()

Identical to the above function except that it permits you to add a "where" clause in the second parameter, instead of using the rigDbWhere handler:

put tID into tArray["id"]

put rigDbGetWhere("mytable", tArray, tLimit, tOffset) into tQuery

Please read about the where function below for more information.

rigDbSelect

Permits you to write the SELECT portion of your query:

rigDbSelect "title,content,date"

put rigDbGet("mytable") into tQuery

-- Produces: SELECT title, content, date FROM mytable

The parameter can be a comma-delimited list or an array.

Note: If you are selecting all (*) from a table you do not need to use this handler. When omitted, revIgniter assumes you wish to SELECT *

rigDbSelect accepts an optional second parameter. If you set it to FALSE, revIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

rigDbSelect "SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id=4) AS amount_paid", FALSE 
put rigDbGet("mytable") into tQuery

rigSelectMax

Writes a "SELECT MAX(field)" portion for your query. You can optionally include a second parameter to rename the resulting field.

rigSelectMax "age"
put rigDbGet("members") into tQuery

-- Produces: SELECT MAX(age) as age FROM members

rigSelectMax "age", "memberage"
put rigDbGet("members") into tQuery
-- Produces: SELECT MAX(age) as memberage FROM members

rigSelectMin

Writes a "SELECT MIN(field)" portion for your query. As with rigSelectMax, You can optionally include a second parameter to rename the resulting field.

rigSelectMin "age"
put rigDbGet("members") into tQuery
-- Produces: SELECT MIN(age) as age FROM members

rigSelectAvg

Writes a "SELECT AVG(field)" portion for your query. As with rigSelectMax, You can optionally include a second parameter to rename the resulting field.

rigSelectAvg "age"
put rigDbGet("members") into tQuery
-- Produces: SELECT AVG(age) as age FROM members

rigSelectSum

Writes a "SELECT SUM(field)" portion for your query. As with rigSelectMax, You can optionally include a second parameter to rename the resulting field.

rigSelectSum "age"
put rigDbGet("members") into tQuery
-- Produces: SELECT SUM(age) as age FROM members

rigDbFrom

Permits you to write the FROM portion of your query:

rigDbSelect "title,content,date"
rigDbFrom "mytable"

put rigDbGet() into tQuery

-- Produces: SELECT title, content, date FROM mytable

If you query more than one table the parameter can be a comma-delimited list or an array too.

Note: As shown earlier, the FROM portion of your query can be specified in the rigDbGet() function, so use whichever handler you prefer.

rigDbJoin

Permits you to write the JOIN portion of your query:

rigDbSelect "*"
rigDbFrom "blogs"
rigDbJoin "comments", "comments.id = blogs.id"

put rigDbGet() into tQuery

-- Produces: 
-- SELECT * FROM blogs
-- JOIN comments ON comments.id = blogs.id

Multiple handler calls can be made if you need several joins in one query.

If you need something other than a natural JOIN you can specify it via the optional third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

rigDbJoin "comments", "comments.id = blogs.id", "left"

-- Produces: LEFT JOIN comments ON comments.id = blogs.id

rigDbJoin accepts an optional fourth parameter. If you set it to FALSE, revIgniter will not try to protect your field or table names with backticks.

rigDbJoin "comments", "comments.id = blogs.id", "left", FALSE

rigDbJoin supports multiple conditions like:

rigDbJoin "comments", "comments.id = blogs.id OR comments.title = blogs.title", "", FALSE

rigDbJoin supports the USING clause like:

rigDbJoin "comments", "id", "left", FALSE

-- Produces: LEFT JOIN comments USING (id)

rigDbWhere

This handler enables you to set WHERE clauses using one of four methods:

Note: All values passed to this handler are escaped automatically, producing safer queries.

Simple key/value method:

rigDbWhere "name", tName
	
-- Produces: WHERE name = 'Joe'

Notice that the equal sign is added for you.

If you use multiple function calls they will be chained together with AND between them:

rigDbWhere "name", tName
rigDbWhere "title", tTitle
rigDbWhere "status", tStatus
	
-- WHERE name 'Joe' AND title = 'boss' AND status = 'active'

Custom key/value method:
You can include an operator in the first parameter in order to control the comparison:
```
rigDbWhere "name !=", tName
rigDbWhere "id <", tID
	
-- Produces: WHERE name != 'Joe' AND id < 45	
```

Associative array method:

put tName into tArray["name"]
put tTitle into tArray["title"]
put tStatus into tArray["status"]
	
rigDbWhere tArray
	
-- Produces: WHERE name = 'Joe' AND title = 'boss' AND status = 'active'

You can include your own operators using this handler as well:

put tName into tArray["name !="]
put tID into tArray["id <"]
put tDate into tArray["date >"]
		
rigDbWhere tArray

Custom string:

You can write your own clauses manually if you set the third parameter to FALSE:

put "name='Joe' AND status='boss' OR status='active'" into tWhere

rigDbWhere tWhere, "", FALSE

rigDbWhere accepts an optional third parameter. If you set it to FALSE, revIgniter will not try to protect your field or table names with backticks.

rigDbWhere "MATCH (field) AGAINST (" & quote & value & quote & ")", "", FALSE

Note: To check for fields that are empty you need to set the value to a space like:

rigDbWhere "name =", " "

rigDbOrWhere

This handler is identical to the one above, except that multiple instances are joined by OR:

rigDbWhere "name !=", tName
rigDbOrWhere "id >", tID

-- Produces: WHERE name != 'Joe' OR id > 50

rigDbWhereIn

Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate

put "Frank" into tNames[1]
put "Todd" into tNames[2]
put "James" into tNames[3]
rigDbWhereIn "username", tNames
-- Produces: WHERE username IN ('Frank', 'Todd', 'James')

rigDbOrWhereIn

Generates a WHERE field IN ('item', 'item') SQL query joined with OR if appropriate

put "Frank" into tNames[1]
put "Todd" into tNames[2]
put "James" into tNames[3]
rigDbOrWhereIn "username", tNames
-- Produces: OR username IN ('Frank', 'Todd', 'James')

rigDbWhereNotIn

Generates a WHERE field NOT IN ('item', 'item') SQL query joined with AND if appropriate

put "Frank" into tNames[1]
put "Todd" into tNames[2]
put "James" into tNames[3]
rigDbWhereNotIn "username", tNames
-- Produces: WHERE username NOT IN ('Frank', 'Todd', 'James')

rigDbOrWhereNotIn

Generates a WHERE field NOT IN ('item', 'item') SQL query joined with OR if appropriate

put "Frank" into tNames[1]
put "Todd" into tNames[2]
put "James" into tNames[3]
rigDbOrWhereNotIn "username", tNames
-- Produces: OR username NOT IN ('Frank', 'Todd', 'James')

rigDbLike

This handler enables you to generate LIKE clauses, useful for doing searches.

Note: All values passed to this handler are escaped automatically.

Simple key/value method:

rigDbLike "title", "match"
	
-- Produces: WHERE title LIKE '%match%'

If you use multiple handler calls they will be chained together with AND between them:

rigDbLike "title", "match"
rigDbLike "body", "match"
	
-- WHERE title LIKE '%match%' AND  body LIKE '%match%

If you want to control where the wildcard (%) is placed, you can use an optional third argument. Your options are 'before', 'after' and 'both' (which is the default).

rigDbLike "title", "match", "before"

-- Produces: WHERE title LIKE '%match'
		
rigDbLike "title", "match", "after"
-- Produces: WHERE title LIKE 'match%' 

rigDbLike "title", "match", "both"
-- Produces: WHERE title LIKE '%match%'

Associative array method:

put tMatch into tArray["title"]
put tMatch into tArray["page1"]
put tMatch into tArray["page2"]
	
rigDbLike tArray
	
-- WHERE title LIKE '%match%' AND  page1 LIKE '%match%' AND  page2 LIKE '%match%'

rigDbOrLike

This handler is identical to the one above, except that multiple instances are joined by OR:

rigDbLike "title", "match"
rigDbOrLike "body", tMatch

-- WHERE title LIKE '%match%' OR  body LIKE '%match%'

rigDbNotLike

This handler is identical to rigDbLike, except that it generates NOT LIKE statements:

rigDbNotLike "title", "match"

-- WHERE title NOT LIKE '%match%

rigDbOrNotLike

This handler is identical to rigDbNotLike, except that multiple instances are joined by OR:

rigDbLike "title", "match"
rigDbOrNotLike "body", "match"

-- WHERE title  LIKE '%match% OR body NOT LIKE '%match%'

rigDbGroupBy

Permits you to write the GROUP BY portion of your query:

rigDbGroupBy "title"

-- Produces: GROUP BY title

You can also pass an array of multiple values as well:

put "title" into tArray[1]
put "date" into tArray[2]
rigDbGroupBy tArray

-- Produces: GROUP BY title, date

rigDbDistinct

Adds the "DISTINCT" keyword to a query

rigDbDistinct
put rigDbGet("table") into tQuery
		
-- Produces: SELECT DISTINCT * FROM table

rigDbHaving

Permits you to write the HAVING portion of your query. There are 2 possible syntaxe, 1 argument or 2:

rigDbHaving "user_id = 45"

-- Produces: HAVING user_id = 45

rigDbHaving "user_id",  45
-- Produces: HAVING user_id = 45

You can also pass an array of multiple values as well:

put "My Title" into tArray["title"]
put tID into tArray["id <"]
rigDbHaving tArray
		
-- Produces: HAVING title = 'My Title', id < 45

If you are using a database that revIgniter escapes queries for, you can prevent escaping content by passing an optional third argument, and setting it to FALSE.

rigDbHaving "user_id",  45
-- Produces: HAVING `user_id` = 45 in some databases such as MySQL
		
rigDbHaving "user_id",  45, FALSE
-- Produces: HAVING user_id = 45

rigDbOrHaving

Identical to rigDbHaving, only separates multiple clauses with "OR".

rigDbOrderBy

Lets you set an ORDER BY clause. The first parameter contains the name of the column you would like to order by. The second parameter lets you set the direction of the result. Options are asc or desc, or random.

rigDbOrderBy "title", "desc"

-- Produces: ORDER BY title DESC

You can also pass your own string in the first parameter:

rigDbOrderBy "title desc, name asc"

-- Produces: ORDER BY title DESC, name ASC

Or multiple function calls can be made if you need multiple fields.

rigDbOrderBy "title", "desc"
rigDbOrderBy "name", "asc"
	
-- Produces: ORDER BY title DESC, name ASC

rigDbLimit

Lets you limit the number of rows you would like returned by the query:

rigDbLimit 10

-- Produces: LIMIT 10

The second parameter lets you set a result offset.

rigDbLimit 10, 20

-- Produces: LIMIT 20, 10 (in MySQL.  Other databases have slightly different syntax)

rigDbCountAllResults()

Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as rigDbWhere, rigDbOrWhere, rigDbLike, rigDbOrLike, etc. Example:

get rigDbCountAllResults("mytable")

-- Produces an integer, like 25

rigDbLike "title", "match"
rigDbFrom "mytable"
get rigDbCountAllResults()
-- Produces an integer, like 17

rigDbCountAll()

Permits you to determine the number of rows in a particular table. Submit the table name in the first parameter. Example:

get rigDbCountAll("my_table")

-- Produces an integer, like 25

Parentheses

rigDbOpenParenthesis

Adds an open parenthesis to SQL SELECT statements.

rigDbCloseParenthesis

Adds a close parenthesis to SQL SELECT statements.

Parentheses example:

rigDbFrom "recipe"
	
rigDbWhere "name !=", "Pasta"
rigDbWhere "id <", "12"
rigDbLike "name", "anana"
	
rigDbOpenParenthesis
	
put "Pasta" into tArray[1]
put "Banana Bread" into tArray[2]
put "Pancakes" into tArray[3]
rigDbWhereIn "name", tArray
	
rigDbLike "name", "Pancakes"
rigDbOrNotLike "name", "Soup"
	
rigDbCloseParenthesis
	
put rigDBget() into tQueryResult
	
-- Produces: SELECT * FROM (`recipe`) WHERE `name` != "Pasta" AND `id` < "12" AND (`name` IN ("Pasta", "Banana Bread", "Pancakes") AND `name` LIKE "%anana%" AND `name` LIKE "%Pancakes%" OR `name` NOT LIKE "%soup%")

Note: In case you have "like" keywords combined with "where" keywords between an open parenthesis and a close parenthesis there should not follow any "where" statements like rigDbWhere or rigDbWhereIn or rigDbOrWhere etc.

Subqueries

To get a subquery as a condition for the main query set the fourth parameter of rigDbGet() to true, example:

# SUBQUERY
put TRUE into tReturnSubqueryString
rigDbSelect "id"
rigDbWhere "salary >", 22000
put rigDbGet("employees", , , tReturnSubqueryString) into tSubQuery

# MAIN QUERY
rigDbWhere "id IN", tSubQuery
put rigDbGet("employees") into tQueryResult

-- Produces SELECT * FROM employees WHERE id IN (SELECT id FROM employees WHERE salary > 22000)

Note: Always use rigDbWhere with subqueries. Handlers like rigDbOrWhere, rigDbWhereIn, rigDbOrWhereIn etc. do not work to set where clauses in subqueries.

Inserting Data

rigDbInsert()

Generates an insert string based on the data you supply, and runs the query. Here is an example:

put "My Title" into tData["title"]
put "My Name" into tData["name"]
put "My Date" into tData["date"]

get rigDbInsert("mytable", tData)

-- Produces: INSERT INTO mytable (title, name, date) VALUES ('My Title', 'My Name', 'My Date')

The first parameter will contain the table name, the second is an associative array of values.

Note: All values are escaped automatically producing safer queries.

Note: If an array element is empty the corresponding table field is left untouched pretty much as if the corresponding array element was absent. Using the active record library there is no way to turn NULL (or whatever the default value is) to empty. This goes conform to how empty values passed to the active record library are treated to construct where clauses.

If you set the optional third parameter to TRUE the function returns the last automatically generated ID value for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. Example:

put rigDbInsert("mytable", tData, TRUE) into tLastInsertID
	
if tLastInsertID <> FALSE then
	# code...
end if

Note: In order to get the last insert ID using PostgreSQL it is assumed that the name in the first position of the field names is the primary key. This means the first column should contain automatically generated IDs.

The function returns FALSE if something went wrong.

rigDbValuesSet()

This function enables you to set values for inserts or updates.

It can be used instead of passing a data array directly to the insert or update functions:

get rigDbValuesSet("name", tName)

get rigDbInsert("mytable")

-- Produces: "INSERT INTO mytable (name) VALUES ('" & tName & "')"

If you use multiple function calls they will be assembled properly based on whether you are doing an insert or an update:

get rigDbValuesSet("name", tName)
get rigDbValuesSet("title", tTitle)
get rigDbValuesSet("status", tStatus)
get rigDbInsert("mytable")

rigDbValuesSet() will also accept an optional third parameter (pEscape), that will prevent data from being escaped if set to FALSE. To illustrate the difference, here is rigDbValuesSet() used both with and without the escape parameter.

get rigDbValuesSet("field", "field+1", FALSE)
get rigDbInsert("mytable")
-- gives INSERT INTO mytable (field) VALUES (field+1)
	
get rigDbValuesSet("field", "field+1")
get rigDbInsert("mytable")
-- gives INSERT INTO mytable (field) VALUES ('field+1')

You can also pass an associative array to this function:

put tName into tArray["name"]
put tTitle into tArray["title"]
put tStatus into tArray["status"]


get rigDbValuesSet(tArray)
get rigDbInsert("mytable")

Updating Data

rigDbUpdate()

Generates an update string and runs the query based on the data you supply. Here is an example:

put tTitle into tData["title"]
put tName into tData["name"]
put tDate into tData["date"]

rigDbWhere "id", tID
get rigDbUpdate("mytable", tData)

-- Produces:
-- "UPDATE mytable 
-- SET title = '" & tTitle & "', name = '" & tName & "', date = '" & tDate & "'
-- WHERE id = " & tID

Note: All values are escaped automatically producing safer queries.

Note: If an array element is empty the corresponding table field is left untouched pretty much as if the corresponding array element was absent. Using the active record library there is no way to turn NULL (or whatever the default value is) or any other field value to empty. This goes conform to how empty values passed to the active record library are treated to construct where clauses.

You'll notice the use of the rigDbWhere handler, enabling you to set the WHERE clause. You can optionally pass this information directly into the update function as a string:

get rigDbUpdate("mytable", tData, "id = 4")

Or as an array:

put tID into tArray["id"]
get rigDbUpdate("mytable", tData, tArray)

You may also use the rigDbValuesSet() function described above when performing updates.

Deleting Data

rigDbDelete()

Generates a delete SQL string and runs the query.

put tID into tArray["id"]
get rigDbDelete("mytable", tArray)

-- Produces:
-- "DELETE FROM mytable 
-- WHERE id = " & tID

The first parameter is the table name, the second is the where clause. You can also use the rigDbWhere, rigDbOrWhere or rigDbWhereIn handlers instead of passing the data to the second parameter of the function:

rigDbWhere "id", tID
get rigDbDelete("mytable")
	
-- Produces:
-- "DELETE FROM mytable 
-- WHERE id = " & tID

An array of table names can be passed into rigDbDelete() if you would like to delete data from more than 1 table.

put "table1" into tTables[1]
put "table2" into tTables[2]
put "table3" into tTables[3]

rigDbWhere "id", 5
get rigDbDelete(tTables)

If you want to delete all data from a table, you can use the rigDbTruncate() function, or rigDbEmptyTable().

rigDbEmptyTable()

Generates a delete SQL string and runs the query.

get rigDbEmptyTable("mytable")
	
-- Produces
-- DELETE FROM mytable

rigDbTruncate()

Generates a truncate SQL string and runs the query.

rigDbFrom "mytable"
get rigDbTruncate()
-- or 
get rigDbTruncate("mytable")

-- Produce:
-- TRUNCATE mytable

Note: If the TRUNCATE command isn't available, rigDbTruncate() will execute as "DELETE FROM table".

Active Record Caching

While not "true" caching, Active Record enables you to save (or "cache") certain parts of your queries for reuse at a later point in your script's execution. Normally, when an Active Record call is completed, all stored information is reset for the next call. With caching, you can prevent this reset, and reuse information easily.

Cached calls are cumulative. If you make 2 cached rigDbSelect calls, and then 2 uncached rigDbSelect calls, this will result in 4 rigDbSelect calls. There are three Caching handlers available:

rigDbStartCache

This handler must be called to begin caching. All Active Record queries of the correct type (see below for supported queries) are stored for later use.

rigDbStopCache

This handler can be called to stop caching.

rigDbFlushCache

This handler deletes all items from the Active Record cache.

Here's a usage example:

rigDbStartCache
rigDbSelect "field1"
rigDbStopCache

put rigDbGet("tablename") into tQuery

--Generates: SELECT `field1` FROM (`tablename`)

rigDbSelect "field2"
put rigDbGet("tablename") into tQuery

--Generates:  SELECT `field1`, `field2` FROM (`tablename`)

rigDbFlushCache

rigDbSelect "field2"
put rigDbGet("tablename") into tQuery

--Generates:  SELECT `field2` FROM (`tablename`)

Note: The following statements can be cached: rigDbSelect, rigDbFrom, rigDbJoin, rigDbWhere, rigDbLike, rigDbGroupBy, rigDbHaving, rigDbOrderBy, rigDbValuesSet()