Search
Table of ContentsDeveloper's Manual
API documentation Index Working with the schema of a database

### 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:

- Support for database schemata
- ... by support for schemata the following additional features may be provided, which are not available through PEAR-DB alone:
  - automatic validation of queries against the database schema
  - resolving foreign key relations automatically
  - automatic type checking before executing input/updates, with additional data types: "SELECT", "URL", "IP" and "mail"
  - automatic logging of database events
  - automatic creation of backups for deleted data records in log files
  - automatic validation of user-defined constraints in PHP syntax (independent of the DBMS)
  - automatic treatment of parallel updates and transactions (it avoids racing conditions between concurrent updates by several users)
  - form generator for generating a graphic user interface based on a database schema
  - reverse engineering of databases to database structure files
  - read-only flag on databases, tables and columns
  - DBMS-independent trigger using PHP syntax
  - schema files may import further schema files
- intuitive API with query-builder
- ... features of the query-builder:
  - automatic quoting of identifiers and values
  - automatic production of queues of INSERT/UPDATE statements
  - automatic caching if INSERT/UPDATE statements
  - automatic wrapping of SQL-Anweisungen in transactions
  - broad protection from many variants of SQL Injections
  - optional bypass to PEAR-DB
  - Function to dump the current database structur to a new schema file
  - automatic dissolving of table-inheritance
- own flat file database
- ... takes over automatically, if no database is available or the end-user decides not to use a database. Thus database features are available also on servers without MySQL support. The flat file database has the following features:
  - support for auto-increment like in MySQL
  - unique-constraints and indexes
  - support for transactions
  - read-stability isolation level
  - Function for the synchronisation of tables between a MySQL database and the flat file database

##### Which features are not supported?

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

- Random access for fast access to large tables

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

- support of compound primary keys

#### 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 useable.

##### 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.

- If "key" references a single cell, the returned value will be scalar.
- If "key" references a whole table, the returned value will be a two-dimensional associative array of rows.
- If "key" references a row, the returned value will be a one-dimensional associative array of cells, where the names are the names of the columns.
- If "key" refrences a columns eine Spalte, the returned value will be a one-dimensional associative array of cells, where the names will be the values of the primary keys.

    <?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","order by 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;
    */

    /* Zeile ausgeben: */
    $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;
    $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;
    $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;
    $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.

Figure: Opening a connection and installating 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 sub-directory, 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 sub-directories to be used. They don't have to provide own files for all supported DBMS. If a requried 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);
    ?>

API documentation Index Working with the schema of a database

Thomas Meyer, www.yanaframework.net