Table of ContentsDeveloper's Manual
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:
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.
This requires a schema file. The schema files has to be present in the folder "config/db/" and must have the file extension ".config".
API documentation
<?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.
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();
?><?php
if (YANA_DATABASE_ACTIVE === true) {
print "Database is active";
} else if (YANA_DATABASE_ACTIVE === false) {
print "Database is NOT active";
}
?>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;
*/
?>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();
?>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;
*/
?><?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.";
}
?><?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.";
}
?>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.
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".
<?php
global $YANA;
$db = $YANA->connect('guestbook');
$db->importSQL('data.sql');
?><?php
global $YANA;
$db = $YANA->connect('guestbook');
$csv = $db->toString('table');
file_put_contents("table.csv", $csv);
?>
Thomas Meyer, www.yanaframework.net