Smarty db

From PeacockWiki

Jump to: navigation, search

Contents

Introduction

Smarty_DB is a php object model written by Trevor peacock for use with the TimeShare project. Smarty_DB generates an object model of a database (currently only MySQL is supported). Smarty_DB uses the Smarty Templating Engine for rendering SQL commands, and also provides plugins to smarty for use in normal HTML templates, allowing many database queries to be done directly from smarty templates with no PHP code required.

Principles

Smarty makes a few assumptions about your database

  1. Every table that is to be accessable by Smarty_DB has a autonumbered primary key field 'id'.
  2. Wherever one table references another table, the foreign key field will have the same name as the foreign table, and the foreign key field will have the value of the key in the foreign table, where the key is the field 'id'.
 +-----------+
 | author    |
 +-----------+
 | id        |
 | name      |
 | dob       |
 |           |
 +-----------+

 +-----------+
 | book      |
 +-----------+
 | id        |
 | name      |
 | pub_date  |
 | author    |
 |           |
 +-----------+

This example follows rule #1 and #2. Each table has an 'id' field, and the author field in the book table is a pointer to the id of the author table. This allows Smarty_DB to recognise the link, and automatically allow the programmer to simply reference the foreign table.

//$book is a Smarty_DB object containing a single book object.
$author = $book->author;

In this example Smarty_DB recognises the link between book and author, and automatically runs a SQL query to fetch the data related to the author, and returns it as a Smarty_DB object. This can be quite a powerful mechanism for performing lookups

print "Book Title: ".$book->name 
print "Author: ".$book->author->name;

Directory Structure

Smarty_db uses a specific directory structure as follows:

 /templates
     root templates folder defined by $smarty->template_dir.
     contains 6 templates used for basic operations by smarty_db
   /actions
       contains sql scripts to perform actions on the database
   /generator
       contains sql scripts that perform lookups on the database, and are used as starting points for getting data from the database.
   /table
       contains queries relating to database tables. see table queries.
   add.ssql
   child_query.ssql
   default.ssql
   delete.ssql
   parent_query.ssql
   update.ssql

Table Queries

Smarty_db will function without any user-created sql scripts (.ssql files). A generate may be done to fetch a record (from any table) with a specific id. Files may be added to the table directory to specify any calculated fields required for the database tables, e.g. formatted dates. Whenever Smarty_db looks up a table, it will use the query to get the data. Such queries should return all records from the table, while adding additional formatting. The following is an example.

 SELECT *, DATEDIFF(NOW(), dob) div 365 as `age` FROM `author`

This will give the appearance that the author table contains the field age, which in actually is a calculated value.

As well as adding calculated values to tables, lookups can be added to tables. The table directory may contain directorys matchin the names of database tables. Queries may be added under these directorys to simulate lookups. For example, a file /templates/table/book/others_by_same_author.ssql may be added with the following code

 {result_table name="book" single_row=false}
 SELECT * FROM {insert name="table" table="book"} WHERE `author`='{$caller->author}'

The smarty tags ({ and }) will be explained later, the point is calling $book->others_by_same_author will automatically invoke the query, returning all books by the same author as the author of the current (calling) book.

Coding

Smarty-SQL

result_table

Every ssql file should contain (preferably on the first line) a call to the smarty function result_table.

This function allows a query to define what it is going to return. It has two paramaters, name, and single_row. Name defines the name of the table (if any) that the data is being returned from. This alows smarty_db to make use of any table queries defined for that table. single_row specifies if the query is designed to return a single row from the table, or more than one row. If a query defined as single_row returns more than one row, an exception will be thrown.

insert table

Whenever a query references another table in its FROM clause, it may be included with the use of this function, rather than added as normal. This substutes the user-generated query, including any calculated fields. Although it is not required, it is stronly suggested that it is used at least for the table matching the result table (defined by result_table).

PHP

DatabaseModel::get_db()

Returns the current database object, creating one if necessary.

DBDataObject::new_object($table)

Creates a new DB object representing a record in the specified table. After populating the object with data, add() may be called to add the record to the database.

DBDataObject::generate($query, $params = array ())

Performs a SQL query, and returns the results. The query is specified by the first parameter, and represents a ssql file in the generator directory. If the query is designed to return a single object (result_table smarty function) the function returns a DBDataObject, othweise it returns an array containing zero or more DBDataObjects. Parameters may be passed to the query in the form of an associative array. Alternativly a single value may be passed, which will be recieved by the query as the parameter 'id'.

DBDataObject::action($query, $params = array ())

Executes a script to perform an action on the database. Returns nothing. (future: return a result of the action) The first parameter referes to a .ssql file in the actions directory. Similar to generate, the function accepts parameters that are passed to the query. A single value may be passed as a parameter, which will be sent to the query as the parameter 'id'.

DBDataObject->table_name()

Returns the table from which the DBDataObject represents data from.

DBDataObject->changed()

Returns true if the data has been changed since the object has been constructed.

DBDataObject->update($add = false)

Updates the database with data stored in the object. It updates the record with a matching 'id' field from the table returned by table_name().

DBDataObject->add()

Adds a new record to the database to the table returned by table_name().

DBDataObject->delete()

Deletes a record from the database. The deleted record is the one matching the 'id' field of the object from the table returned by table_name().

DBDataObject->clear_cache($nm = null)

Clears the cache of other DBDataObject's related to this object.

DBDataObject->get($nm)

Returns the value of the specified field stored in the DBDataObject.

DBDataObject->__get($nm)

Gets the value of that field stored in the DBDataObject. If the field name matches a table name, a query will automatically be performed to return the records from the linked table (either if the current table links to the foreign table, or the foreign table links to the current table)

DBDataObject->has_data($nm)

Returns true if the object has data stored for a field with the name specified.

DBDataObject->set($nm, $val)

Sets the value of a field.

DBDataObject->__set($nm, $val)

Synonymous with DBDataObject->set($nm, $val).

Smarty-HTML

generate_data_object

Calls DBDataObject::generate($query, $params = array ()), passing any parameteres to the query. Required 2 parameters, 'query' specifies the query to be called, and 'var' specifies the smarty variable in which the result is to be stored.

{generate_data_object query="book" var="selected_book" id=$smarty.get.book}

This allows queries to be perfomed directly from smarty. Generally only one generate is required for one page (given that a page generally shows information about a single record). Any related (linked) data from other tables should be accessable therough the generated object providing the database is constructed according to the Smarty_DB principles.

var_dump

A debugging function that performs the php var_dump function, placing the resulting text between <pre> tags. One required parmeter (apart from name required to activate the function) 'var' specifies the smarty variable to be displayed.

{insert name="var_dump" var=$selected_book}

db_action

Calls DBDataObject::action($query, $params = array ()). Similar syntax to generate_data_object. 1 required parameter 'query' specifies the action. Other parameters are passed into the query.

TODO

  1. manual table queries
  2. manual field queries
Personal tools