Working with data

Creating your database

You use the global TAFFY function to create a new database.

// Create a new empty database
var db = TAFFY();

// Create a new database a single object (first record)
var db = TAFFY({record:1,text:"example"})

// Create a new database using an array
var db = TAFFY([{record:1,text:"example"}])

// Create a new database using a JSON string
var db = TAFFY('[{"record":1,"text":"example"}]')

DB Queries

Once you have a database you'll be able to call your variable as function. This will setup a query and return a collection of methods to work with the results of that query.

db() Takes:
One or more Strings, Records, A Filter Objects, Arrays, or Functions.
Returns:
A Query Object
Used to query the database and return a method collection to start working with the data.
db(); // returns all rows

DB Methods

In addition to the query function you have a couple of top level methods to use.

db.insert() Takes:
An object or array of objects/json strings to insert.

Optional: Call Event override
true runs onInsert event (default)
false does not onInsert event
Returns:
A query pointing to the inserted records
Inserts records into the database.
db.merge() Takes:
An object or array of objects/json strings to insert.

Optional: Key
identity column to be used to match records against the existing db. Default "id".
true runs onInsert for new records and onUpdate for changed records

Optional: Call Event override
false does not run insert or update event (default)
true runs onInsert for new records and onUpdate for changed records
Returns:
A query pointing to the changed or inserted records
Inserts or replaces records in an existing db.
db.insert({column:"value"}); // inserts one record
						
db.insert({column:"value"},false); // insert but do not call onInsert event
db.order() Takes:
A string listing columns to order by separated by commas.
Optional: Use asec, desc, logical, and logicaldesc to influencence sort direction.
Returns:
true
Sorts the db by column. Note that logical sorting is default.
Note: db.sort() is different than db().order() in that sort() changes the order of the records in the database while order() only impacts the order of results returned from a query.
db.order("columnname"); // orders collection by columnname
db.store() Takes:
A string with the storage name for the DB
Returns:
true if data was returned from localStorage, false if no data returned
Sets up a localStorage collection for a given name. If data exists already the data will be loaded into the collection. Changes are auto synced to localStorage.

Pass in false to terminate storing the collection (data in localstorage will be unchanged).

Note: localStorage is not avaliable in all browsers. If localStorage is not found then .store() will be ignored.
db.store("name"); // starts storing records in local storage
db.settings() Takes:
An object.
Returns:
The settings object
Used to modify the following settings on each DB:
  • template: this is a template object {} that is merged with each new record as it is added
  • forcePropertyCase: (defaults null) Can be "lower" or "higher" and forces properties to a case on insert, update.
  • onInsert: function to run on record insert. The this keyword will be the new record.
  • onUpdate: function to run on record update. The this keyword will be the new record. Arg 1 will be old record. Arg 2 will be the changes.
  • onRemove: function to run on record remove. The this keyword will be the removed record.
  • onDBChange: function to run on change to the DB. The this keyword will be the entire DB.
  • cacheSize: size of cached query collection. Default to 100. Set to 0 to turn off caching.
  • name: string name for the db table to be used at a later date.
db.settings({template:{show:true}}); // sets the template to have a show value set to true by default on all records

db.settings({onUpdate:function () {alert(this)}}); // sets the onUpdate event

					

Query Object Methods

Any call to data collections root function will return a new query object. The methods below can be used to interact with the data.

db().update() Takes:
Any:
An object to be merged with the matching objects.
A function that will return the modified record for update.
Two arguments: a string column name and a value to set the column to

Optional Call Event override
true runs onUpdate event (default)
false does not onUpdate event
Returns:
Query Object
Updates the records in the query set.
db().update({column:"value"}); // sets column to "value" for all matching records

db().update("column","value"); // sets column to "value" for all matching records

db().update(function () {this.column = "value";return this;}); // sets column to "value" for all matching records


db().update({column:"value"},false); // update but do not call onUpdate event
db().remove() Takes:

Optional Call Event override
true runs onUpdate event (default)
false does not onUpdate event
Returns:
Count of removed records.
Removes records from the database
db().remove(); // removes all matching records from the database
						
db().remove(true); // removes but do not call onRemove event
db().filter() Takes:
One or more Strings, Records, A Filter Objects, Arrays, or Functions.
Returns:
Query Object
See Filtering
Applies a sub filter and returns a new Query Object to let you access the results of the filter.
db().filter({column:value});
						
db().filter({column:value}).count();

collection({column:{gte:value}}).filter({column:{lte:value}}).count();
					
db().order() Takes:
A string listing columns to order by separated by commas.
Optional: Use asec, desc, logical, and logicaldesc to influence sort direction.
Returns:
Query Object
Sorts the query results by column based. Note that logical sorting is default. Note: db.sort() is different than db().order() in that sort() changes the order of the records in the database while order() only impacts the order of results returned from a query.
db().order("col1"); // sorts by col1 using a logical sort

db().order("col1 asec, col2 asec"); // sorts by col1 then col2

db().order("col1 desc"); // sorts by col1 descending

db().order("col1 logicaldesc"); // sorts by col1 using a logical sort descending
					
db().limit() Takes:
A number.
Returns:
Query Object
Limits the number of results in a query set.
db().limit(15); // Limits returned results to first 15
db().start() Takes:
A number.
Returns:
Query Object
Sets the starting record number. Used for offset and paging.
db().start(15); // Starts returning results at record 15
db().each() Takes:
A function.
Returns:
Query Object
Runs one or more functions once for every record in the query.
db().each(function (record,recordnumber) {
	alert(record["balance"]);
}); // alerts the value of the balance column for each record
db().map() Takes:
A function.
Returns:
An array of results from your function being run against the records in the query.
Runs your function against each record in the query and returns the results as an array.
db().map(function (record,recordnumber) {
	return record.balance*0.2;
}); // returns an array of numbers that are each 20% of the actual balance for each record
db().callback() Takes:
A function and an (optional) delay.
Used for lazy query execution and to prevent blocking. Provided function will be called with current Quote Object after query has run in a setTimeout block.
db().callback(function () {
    alert(this.count()); // alert count of matching records					
});
db().get() Returns:
An array of all matching records.
Prefered method for extracting data. Returns an array of matching records. Also used for exporting records form the database.
db().get(); // returns an array of all matching records
db().stringify() Returns:
A JSON representation of an array of all matching records.
Used for exporting data as JSON text. Returns a JSON array filled with JSON objects for each record.
db().stringify(); // returns a JSON array of all matching records
db().first() Returns:
A single record.
Returns the first record in a record set.
db().first(); // returns the first record out of all matching records.
db().last() Returns:
A single record.
Returns the last record in a record set.
db().last(); // returns the last record out of all matching records.
db().sum() Takes:
One or more column names.
Returns:
A number.
Returns the sum total of the column or columns passed into it.
db().last("balance"); // returns the sum of the "balance" column.
db().min() Takes:
Column name.
Returns:
A number or value.
Returns the min value for the column passed in.
db().min("balance"); // returns the lowest value of the "balance" column.
db().max() Takes:
Column name.
Returns:
A number or value.
Returns the max value for the column passed in.
db().max("balance"); 
// returns the highest value of the "balance" column.
db().select() Takes:
One or more column names.
Returns:
For one column: An array of values.
For two or more columns: An array of arrays of values.
Used to select columns out the database. Pass in column names to get back an array of values.
db().select("charges","credits","balance"); 
// returns an array of arrays in this format [[-24,20,-4]]

db().select("balance"); 
// returns an array of values in this format [-4,6,7,10]
					
db().distinct() Takes:
One or more column names.
Returns:
For one column: An array of values.
For two or more columns: An array of arrays of values.
Used to select distinct values from the database for one or more columns. Pass in column names to get back an array of distinct values.
db().distinct("title","gender"); 
// returns an array of arrays in this format [["Mr.","Male"],["Ms.","Female"]]

db().distinct("title"); 
// returns an array of values in this format ["Mr.","Ms.","Mrs."]
db().supplant() Takes:
A string template.
Optional return array flag.
Returns:
Defaults to a string. If return array flag is true then an array of strings with an entry for each record.
Used to merge records with a template formated with {key} values to be replaced with real values from the records.
db().supplant("<tr><td>{balance}</td></tr>"); 
// returns a string in this format "<tr><td>-4</td></tr><tr><td>6</td></tr>"

db().supplant("<tr><td>{balance}</td></tr>",true); 
// returns an array of strings format ["<tr><td>-4</td></tr>","<tr><td>6</td></tr>"]
db().join() Takes:
1. A second db or reference to a db query.
2. One or more functions to be used as join conditions or
An an array of one or more join conditions
Returns:
A db query object containing the joined rows.
Used to join two dbs and/or queries together and produce a hybrid query containing the joined data.

[EXPERIMENTAL FEATURE]
// City DB
city_db = TAFFY([
  {name:'New York',state:'NY'},
  {name:'Las Vegas',state:'NV'},
  {name:'Boston',state:'MA'}
]);

// State DB
state_db = TAFFY([
  {name: 'New York', abbreviation: 'NY'},
  {name: 'Nevada', abbreviation: 'NV'},
  {name: 'Massachusetts', abbreviation: 'MA'}
]);

// Conditional Join
city_db()
  .join( state_db, [ 'state', 'abbreviation' ]);

// Conditional Join with optional ===
city_db()
  .join( state_db, [ 'state', '===', 'abbreviation' ]);
  
// Conditional Join using function
city_db()
  .join( state_db, function (l, r) {
      return (l.state === r.abbreviation);
    });