Database Object
To access information in a database, the standard object 'DB' is provided, with a variety of methods to retrieve and save information from the database. These methods are analagous to SQL statements, but take care of the overhead of database connections, caching, Javascript conversions, and permissioning.Retrieving an object
The most commonly used method for retrieving an item from the database is the getObject call, which takes an object parameter and a key parameter. The object returned contains fields based on the name of the items in that table. In this example, we will retrieve a supporter object and the associated email address.
DB.getObject(objectName, key)
<? var supporter = DB.getObject('supporter', '1234'); ?>
Email address = <?= supporter.Email ?>
Manipulating an object
DB.saveObject(objectName, object, key)
In this case, we will create a new supporter in a new object called mySupporter and give it Email, First_Name, and Last_Name attributes. Because it is a new supporter, we won't provide a key. If we were to provide a key, the new values would override those in the existing object with the given key.
<?
var mySupporter = new Object();
mySupporter.Email = 'username@domain.com';
mySupporter.First_Name = 'Chris';
mySupporter.Last_Name = 'L';
DB.saveObject('supporter', mySupporter);
?>
DB.deleteObject(objectName, key)
This example will simply delete the supporter with key 1234.
<? DB.deleteObject('supporter', '1234'); ?>
Retrieving many objects
DB.getObjects(objectName, conditions, orderBy, limit, includes);
The DB.getObjects method is useful for returning many objects. It requires an objectName. Other parameters are optional and limit the set of results to be returned. In its simplest form, DB.getObjects will return an array all of the objects available.
<? var supporters = DB.getObjects('supporter'); ?>
supporters will be an array of all available supporters. More commonly, limited results will be more desirable.
DB.getObjects takes several optional parameters:
The conditions parameter is an array of Condition objects. To construct a Condition object, call the constructor:
new Condition(field, 'operator', value)
The field parameter is the name of any attribute of the given object.
The operator accepts a variety of entries, with different values allowed for each
- for operator: =, != - value is a string or number (e.g. 'Smith' or 1)
- for operator: >, >=, <, <= - value is a string or number (e.g. 'Smith' or 1). String comparisons will be done for string entries
- for operator: in, not in - value is a comma-delimited list of strings or numbers (e.g. "Smith,Jones,Daisy" or "1,2,3")
- for operator: is empty, is not empty - value should be empty
In this example, we will create a simple condition specifying the First_Name must be Chris.
<? var condition = new Condition('First_Name', '=', 'Chris'); ?>
orderBy is an array of fields to sort the results by.
limit [offset,] row_count is a maximum number of results to return. You can use an optional preset value to start it (e.g. "35,10" would return 10 rows, starting at entry 35)
includes is an array of fields to return. By default, all are returned.
In this example, we will use DB.getObjects to get an array of supporters named Chris with a status of Active or Temporarily Inactive:
<?
var conditions = [
new Condition('First_Name', '=', 'Chris'),
new Condition('Status', 'in', 'Active,Temporarily Inactive')];
var supporters = DB.getObjects('supporter', conditions);
?>
DB.getLeftJoin(tableNames, conditions, orderBy, limit, includes)
Much like DB.getObjects, DB.getLeftJoin returns a group of results. In this case, the results will be a combination of two or more objects joined by object keys. DB.getLeftJoin is analagous to doing a left join between two or more tables in SQL. In this example, we will get all supporter information for supporters who attended events since May 1st, 2008.
<?
var conditions = [
new Condition('supporter_event.Date_Created', '>=', '2008-05-01'),
new Condition('supporter_event._Status', '=', 'Attended')];
var supporter_events = DB.getLeftJoin('supporter_event,supporter', conditions);
?>
It would then be possible to loop through the results and retrieve attributes of supporter_event and supporter objects:
<?
for each (supporter_event in supporter_events) { ?>
Date attended: <?= supporter_event.Date_Created ?><br/>
First name: <?= supporter_event.First_Name ?><br/>
Last name: <?= supporter_event.Last_Name ?><br/>
Email: <?= supporter_event.Email ?><br/>
<? }
?>
Sums and Counts
Often you'll want to get counts of items in the database. While you could pull out all the objects using a getObjects call and iterate through them, that is slow and inefficient, and is not effective for large numbers of objects. Alternatively you can use the methods:
DB.getCount(objectName, conditions, countColumn)
DB.getSum(objectName, conditions, countColumn)
DB.getMax(objectName, conditions, countColumn)
DB.getMin(objectName, conditions, countColumn)
This sample retrieves the counts, mins, maxes, and sums of the amount from the 'donation' object
<li>Count = <?= DB.getCount('donation', null, 'amount') ?>
<li>Sum = <?= DB.getSum('donation', null, 'amount') ?>
<li>Max = <?= DB.getMax('donation', null, 'amount') ?>
<li>Min = <?= DB.getMin('donation', null, 'amount') ?>
Counts by group
Counts by group, or subset, is also a frequently used query. This is analogous to a SQL query with a "count(*)" , and one or many "group by" fields.
DB.getCounts(objectName, groupBy, conditions, countColumn, orderBy, limit)
<? var donationCounts = DB.getCounts('donation', 'supporter_KEY', null, 'amount'); ?>
Length = <?= donations.length ?>
<? for each (var d in donationCounts) { ?>
<li>
<?= d.supporter_KEY ?>
sum = <?= d.sum ?>
max = <?= d.max ?>
min = <?= d.min ?>
count = <?= d.count ?>
</li>
<? } ?>
Tagging objects
You can tag an object in SalsaScript using the tagObject method.
DB.tagObject(tag(s), objectName, objectKey)
Tag an object with two tags, a normal tag "MyTag1", and a tag "MyTag2" with a prefix
"donationprefix"
<? DB.tagObject('MyTag1,donationprefix:MyTag2', 'donation', 1234); ?>
Retrieving tagged objects
DB.getTags(objectName[,key])
DB.getTags, when given an object name, will return an array of all tags applied to the objects of the given type. With the optional 'key' parameter, it will return only tags applied on a given ojbect.
This example will retrieve all of the tags applied to all supporters:
<? var tags = DB.getTags('supporter'); ?>
This example will retrieve all of the tags applied to supporter #12345:
<? var tags = DB.getTags('supporter', 12345); ?>
DB.getTaggedObjects(tag, objectName, conditions, orderBy, limit, includes)
DB.getTaggedObjects will return all objects of the given type that are tagged with the given tag. Its results are similar to that of a DB.getObjects call.
This example will return all supporters tagged with "test tag"
<? var taggedSupporters = salsa.getTaggedObjects('test tag', 'supporter'); ?>
Type Conversions
Objects retrieved from the database are converted from SQL types to javascript types.
varchar, text, enum, set -> javascript String
float, int, double -> javascript Number
tinyint -> javascript Boolean
datetime, date, timestamp -> javascript Date