Smarty db

From PeacockWiki

(Difference between revisions)

Revision as of 14:43, 30 August 2005

1 Introduction
2 Principles
- 2.1 Directory Structure
- 2.2 Table Queries
3 Coding
4 TODO
5 Classes

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

Every table that is to be accessable by Smarty_DB has a autonumbered primary key field 'id'.
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).

Smarty-HTML

db_action

generate_data_object

var_dump

PHP

TODO

caching
smarty html plugins
smarty sql plugins
php coding
generator queries
manual table queries
manual field queries

Classes

DatabaseModel

public function __construct(ObjectDatabase $db = null) {
public static function get_db() {

Smarty_Database extends Smarty

function Smarty_Database() {
function display($tpl_path) {
function fetch($tpl_path = null) {
function template($tpl_path) {

DBDataObject extends DatabaseModel

function __construct(ObjectDatabase $db = null, $params = array (), $table = null) {
function __get($nm) {
function clear_cache($nm = null) {
function get($nm) {
function has_data($nm) {
function set($nm, $val) {
function __set($nm, $val) {
function get_item($nm) {
function run_query($query, $table = null) {
function __call($n, $p) {
static function new_object($table) {
static function action($query, $params = array ()) {
static function generate($query, $params = array ()) {
static function do_generate($query, $params = array ()) {
function table_name() {
function changed() {
function update($add = false) {
function add() {
function delete()

ObjectDatabase extends Database

function __construct() {
public static function register_class($class_name, $table_name) {
function smarty_query_exists($script, $table = null) {
function smarty_query_exists_action($script, $table = null) {
function smarty_query_exists_generator($script, $table = null) {
function run_template($filename, $params = array ()) {
public function data_object($result, $params = array ()) {
function new_object($table, $result=null, $params=null) {
function smarty_query_filename_action($script) {
function smarty_query_filename_generator($script) {
function smarty_query_filename($script, $table = null) {

Retrieved from "http://wiki.peacocktech.com/wiki/Smarty_db"