>> Table of Contents >> Developer's Manual

Working with databases

General introduction

Which features are supported?

The Yana Framework offers a API for working with databases, which is based on PEAR-DB. This API extends the abilities of PEAR by the following features:

Which features are not supported?

There are several features which the flat file database currently does not provide, but are planned for future versions.

There are several features which database schemata currently do not provide, but are planned for future versions.

How do I open a connection to a database?

This requires a schema file. The schema files has to be present in the folder "config/db/" and must have the file extension ".config".

Open a database connection with the current connecting data
<?php 
global $YANA;
$structure 'guestbook';
$db_connection $YANA->connect($structure);
?>

The connecting data for the database (like host address, user name and password) is entered by the user in the administration menu. So you DON'T have to include this in your code.

If you don't want to use a structure file, you may leave it blank. However: not that this is not recommended within a productive environment. In this case the framework will try to determine the missing information itself. If this fails the database connection will not be usable.

Open a connection to a database with individual connecting data

If you want to open a connection to a database, but want to set the connection information yourself in the code, proceed as follows:

<?php 
/* connection information is provided as associative array: */
$connection = array(
    'DBMS' => 'mysql',
    'HOST' => 'localhost',
    'PORT' => 0,
    'USERNAME' => 'user',
    'PASSWORD' => 'pass',
    'DATABASE' => 'database_name'
);

/* To use the YANA-API to communicate with the database, write: */

$db_server = new DbServer($connection);
$yana_api = new DbStream($db_server);

/* To use the PEAR-API to communicate with the database, write: */

$db_server = new DbServer($connection);
$pear_api $db_server->get();

/*To use the PEAR-API with the default connection data, write: */

$db_server = new DbServer();
$pear_api $db_server->get();
?>

How do I know if the database has been activated through the administrator's menu?

<?php 
if (YANA_DATABASE_ACTIVE === true) {
    print "Database is active";
} else if (YANA_DATABASE_ACTIVE === false) {
    print "Database is NOT active";
}
?>

How do I create a database query?

Therefor the API offers the function $db->get(string $key). This executes a SELECT query on the database and returns the value indicated by the argument $key.

<?php 
global $YANA;
$db $YANA->connect('guestbook');

/*
   The following syntax may be used:

   $db->get(
            string "$table.$row.$column",
            string $where,
            string $order_by,
            int $offset,
            int $limit
           );

   Example:
   $value = $db->get("table.1.field","row1=val1,row2=val2","row1",0,1);

   will create the following SQL statement:
   SELECT field from table where primary_key = "1" and row1 like '%val1%' and row2 like '%val2%' order by row1 limit 1;
*/

/* output field */
$value $db->get("table.1.field");
/*
   will create the following SQL statement:
   SELECT field from table where primary_key = "1";
*/

/* output column: */
$column $db->get("table.*.field");
foreach ($column as $row => $value)
{
    print "<p>Value of 'field' in row '$row' = $value</p>";
}
/*
   will create the following SQL statement:
   SELECT field from table;
*/

/* output row: */
$row $db->get("table.2");
foreach ($row as $column => $value)
{
    echo "<p>Value of column '$column' in row '2' = $value</p>";
}
/*
   will create the following SQL statement:
   SELECT * from table where primary_key = "2";
*/

/* output table: */
$table $db->get("table");
foreach ($table as $index => $row)
{
    foreach ($row as $column => $value)
    {
        echo "<p>Value at 'table.$index.$column' = $value</p>";
    }
}
/*
   will create the following SQL statement:
   SELECT * from table;
*/
?>

How do I execute INSERT- / UPDATE- statements?

Use the function $db->insert($key,$value). This will insert the value "value" at position "key". This may either be a row or a cell. The insertion of whole tables or columns is not supported.

With the first call of this function a transaction is created automatically. Use the function $db->write() to send a COMMIT. If any of the statements fail a CALLBACK is send automatically.

If the row does not exist, a INSERT statement will be created, otherwise a UPDATE statement.

The function returns "true" on success and "false" otherwise.

Please note: the SQL-statement is not executed unless you call $db->write().

Take a look at the following examples:

<?php 
global $YANA;
$db $YANA->connect("guestbook");

/* insert new row: */
$db->insert("table.*",array("row1"=>"val1","row2"=>"val2"));
$db->write();

/* update row: */
$db->update("table.2",array("row1"=>"val1","row2"=>"val2"));
$db->write();

/* update cell: */
$db->update("table.2.row1","val1");
$db->write();

/* execute transaction: */
$db->insert("table.*",array("row1"=>"wert1","row2"=>"wert2"));
$db->insert("table.*",array("row1"=>"wert3","row2"=>"wert4"));
$db->update("table.1.row3","wert1");
$db->write();
?>

How do I execute a DELETE statement?

Use the function $db->remove($key). This will remove the data set "key" from the table. The function returns "true" on success and "false" otherwise. Only rows can be deleted. No tables, cells or columns.

Note the following restriction: for security reasons only 1 row is deleted with each call. To delete more rows, call the function repeatedly. This restriction is to prevent that someone can delete an entire table by inadvertence or by mistake.

Please note: the SQL-statement is not executed unless you call $db->write().

<?php 
global $YANA;<br/>$db $YANA->connect('guestbook');

/* Remove 2nd row: */
$db->remove("table.2");
$db->write();
/**
 * will create the following SQL statement:
 * DELETE FROM table WHERE primary_key = "2" LIMIT 1;
 */

/* remove whole table: */
for ($i=0$i $db->length($table); $i++)
{
    $db->remove("table.*");
}
$db->write();
/**
 * will create the following SQL statement:
 * DELETE FROM table WHERE primary_key = "2" LIMIT 1;
 */
?>

How do I know how many rows a table has?

<?php 
global $YANA;<br/>$db $YANA->connect('guestbook');

if ($db->length('table') === 0) {
    print "The table is empty.";
} else {
    print "The table contains ".$db->length('table')."  rows.";
}
?>

How do I know if a table / row exists?

<?php 
global $YANA;<br/>$db $YANA->connect('guestbook');

/* check connection: */
if ($db->exists() === true) {
    print "Database connection available.";
} else if ($db->exists() === false) {
    print "Database connection NOT available";
}

/* check if table exists: */
if ($db->exists('table') === true) {
    print "Table exists.";
} else if ($db->exists('table') === false) {
    print "No such table";
}

/* check if row exists: */
if ($db->exists("table.2") === true) {
    print "The row '2' exists.";
} else if ($db->exists("table.2") === false) {
    print "No row '2' in table.";
}

/* check if cell exists and is not null: */
if ($db->exists("table.2.field") === true) {
    print "There is a cell 'field' in row '2' which has a value.";
} else if ($db->exists("table.2.field") === false) {
    print "The cell does not exist or is NULL.";
}

/* Check if there is at least one field that is not NULL: */
if ($db->exists("table.*.field") === true) {
    print "There is a value in column 'field'.";
} else if ($db->exists("table.*.field") === false) {
    print "The column 'field' does not exist or contains no values.";
}
?>

How do I create an installation routine for my tables?

Manual providing of installation routines for the Yana Framework is not necessary.

The Yana Framework has a generic installation routine for databases, which you will find in the administrator's menu, at the "database setup". Using this menu a user can install all tables or synchronize contents between the DBMS and the flat-file database.

Screenshot
Figure: Opening a connection and installing databases

The Framework generates the necessary SQL instructions automatically from the structure file of your database. The following source code shows, how you can see the code generated by the Framework.

<?php 
$db $YANA->connect('guestbook');
$dbe = new DbExtractor($db);

// Generates "Create Table ..."-statements for MySQL
$sql $dbe->createMySQL();

// there are more functions for other DBMS
$sql $dbe->createPostgreSQL();
$sql $dbe->createMSSQL();
$sql $dbe->createMSAccess();
$sql $dbe->createDB2();
$sql $dbe->createOracleDB();

// show result
print implode("\n"$sql);
?>

If the generated code does not correspond to your expectations, you can replace it by your own SQL file. Please copy your files to the directory "config/db/.install/{DBMS}", whereby you select the subdirectory, which corresponds to the desired DBMS instead of {DBMS}. The file "config/db/.install/readme.txt", contains a list of the supported DBMS and the names for the intended subdirectories to be used. They don't have to provide own files for all supported DBMS. If a required file does not exist, the Framework (like before) will produce the necessary SQL instructions itself automatically.

For further details see the API documentation of class: "DbExtractor".

How do I import a SQL file containing DDL instructions?

<?php 
global $YANA;
$db $YANA->connect('guestbook');
$db->importSQL('data.sql');
?>

How do I import data from a CSV-file?

<?php 
global $YANA;
$db $YANA->connect('guestbook');
$csv $db->toString('table');
file_put_contents("table.csv"$csv);
?>

Author: Thomas Meyer, www.yanaframework.net