database-abstraction.txt

++ Modules
++ Export
+++ Introduction
The Export module provides methods for managing database structure. The methods can be grouped based on their responsibility: create, edit (alter or update), list or delete (drop) database elements. The following document lists the available methods, providing examples of their use. 

Every schema altering method in the Export module has an equivalent which returns the sql that is used for the altering operation. For example createTable() executes the query / queries returned by createTableSql().

In this chapter the following tables will be created, altered and finally dropped, in a database named "events_db":
events(id, name, datetime);
people(id, name);
event_participants(event_id, person_id);

+++ Creating a database
<code type="php">
$conn->export->createDatabase('events_db');
</code>
+++ Creating tables

Now that the database is created, we can proceed with adding some tables. The method createTable() takes three parameters: the table name, an array of field definition and some extra options (optional and RDBMS-specific). Now lets create the events table:

<code type="php">
$definition = array (
    'id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
    'name' => array (
        'type' => 'string',
        'length' => 255
    ),
    'datetime' => array (
        'type' => 'timestamp'
    )
);

$conn->export->createTable('events', $definition);
</code>
 
The keys of the definition array are the names of the fields in the table. The values are arrays containing the required key 'type' as well as other keys, depending on the value of 'type'. The values for the 'type' key are the same as the possible Doctrine datatypes. Depending on the datatype, the other options may vary.

||~ Datatype ||~ length ||~ default ||~ not null ||~ unsigned ||~ autoincrement ||
|| string    || x || x || x ||   ||   ||
|| boolean   ||   || x || x ||   ||   ||
|| integer   || x || x || x || x || x ||
|| decimal   ||   || x || x ||   ||   ||
|| float     ||   || x || x ||   ||   ||
|| timestamp ||   || x || x ||   ||   ||
|| time      ||   || x || x ||   ||   ||
|| date      ||   || x || x ||   ||   ||
|| clob      || x ||   || x ||   ||   ||
|| blob      || x ||   || x ||   ||   ||

Creating the people table:

<code type="php">
$options = array(
    'comment' => 'Repository of people',
    'character_set' => 'utf8',
    'collate' => 'utf8_unicode_ci',
    'type'    => 'innodb',
);
$definition = array (
    'id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
    'name' => array (
        'type' => 'string',
        'length' => 255
    )
);
$conn->export->createTable('people', $definition, $options);
</code>



+++ Creating foreign keys

Creating the event_participants table with a foreign key:

<code type="php">
$options = array(
    'foreignKeys' => array('local'   => 'event_id',
                           'foreign' => 'id'
                           'foreignTable' => 'events'
                           'onDelete' => 'CASCADE'),
    'primary' => array('event_id', 'person_id'),
);
$definition = array (
    'event_id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
    'person_id' => array (
        'type' => 'integer',
        'unsigned' => 1,
        'notnull' => 1,
        'default' => 0,
    ),
);

$conn->export->createTable('event_participants', $definition, $options);
</code>

Now lets say we want to add foreign key on person_id too. This can be achieved as follows:

<code type="php">
$definition = array('local'   => 'person_id',
                    'foreign' => 'id'
                    'foreignTable' => 'people'
                    'onDelete' => 'CASCADE'))

$conn->export->createForeignKey('event_participants', $definition);
</code>

+++ Altering table

Doctrine_Export drivers provide an easy database portable way of altering existing database tables.

NOTE: if you only want to get the generated sql (and not execute it) use Doctrine_Export::alterTableSql() 

<code type="php">
$dbh  = new PDO('dsn','username','pw');
$conn = Doctrine_Manager::getInstance()
        ->openConnection($dbh);

$a    = array('add' => array('name' => array('type' => 'string', 'length' => 255)));


$conn->export->alterTableSql('mytable', $a);

// On mysql this method returns: 
// ALTER TABLE mytable ADD COLUMN name VARCHAR(255)
</code>

Doctrine_Export::alterTable() takes two parameters:

: string //$name// : name of the table that is intended to be changed. 

: array //$changes// : associative array that contains the details of each type of change that is intended to be performed.

An optional third parameter (default: false) is accepted in alterTable and alterTableSql; it is named //$check// and it identifies if the DBMS driver can perform the requested table alterations if the value is true or actually perform them otherwise.

The types of changes that are currently supported are defined as follows:

* //name//
New name for the table.

* //add//

Associative array with the names of fields to be added as indexes of the array. The value of each entry of the array should be set to another associative array with the properties of the fields to be added. The properties of the fields should be the same as defined by the Doctrine parser.

* //remove//

Associative array with the names of fields to be removed as indexes of the array. Currently the values assigned to each entry are ignored. An empty array should be used for future compatibility.

* //rename//

Associative array with the names of fields to be renamed as indexes of the array. The value of each entry of the array should be set to another associative array with the entry named name with the new field name and the entry named Declaration that is expected to contain the portion of the field declaration already in DBMS specific SQL code as it is used in the CREATE TABLE statement.

* //change//

Associative array with the names of the fields to be changed as indexes of the array. Keep in mind that if it is intended to change either the name of a field and any other properties, the change array entries should have the new names of the fields as array indexes.

The value of each entry of the array should be set to another associative array with the properties of the fields to that are meant to be changed as array entries. These entries should be assigned to the new values of the respective properties. The properties of the fields should be the same as defined by the Doctrine parser.

<code type="php">
$a = array('name' => 'userlist',
           'add' => array(
                    'quota' => array(
                        'type' => 'integer',
                        'unsigned' => 1
                        )
                    ),
            'remove' => array(
                    'file_limit' => array(),
                    'time_limit' => array()
                    ),
            'change' => array(
                    'name' => array(
                        'length' => '20',
                        'definition' => array(
                            'type' => 'string',
                            'length' => 20
                            )
                        )
                    ),
            'rename' => array(
                    'sex' => array(
                        'name' => 'gender',
                        'definition' => array(
                            'type' => 'string',
                            'length' => 1,
                            'default' => 'M'
                            )
                        )
                    )
            
            );

$dbh  = new PDO('dsn','username','pw');
$conn = Doctrine_Manager::getInstance()->openConnection($dbh);

$conn->export->alterTable('mytable', $a);
</code>
+++ Creating indices
To create an index, the method createIndex() is used, which has similar signature as createConstraint(), so it takes table name, index name and a definition array. The definition array has one key fields with a value which is another associative array containing fields that will be a part of the index. The fields are defined as arrays with possible keys: 


sorting, with values ascending and descending 

length, integer value 

Not all RDBMS will support index sorting or length, in these cases the drivers will ignore them. In the test events database, we can assume that our application will show events occuring in a specific timeframe, so the selects will use the datetime field in WHERE conditions. It will help if there is an index on this field.


<code type="php">
$definition = array(
    'fields' => array(
        'datetime' => array()
    )
);
$conn->export->createIndex('events', 'event_timestamp', $definition);
</code>
+++ Deleting database elements
For every create*() method as shown above, there is a corresponding drop*() method to delete a database, a table, field, index or constraint. The drop*() methods do not check if the item to be deleted exists, so it's developer's responsibility to check for exceptions.

<code type="php">
// drop a sequence
try {
    $conn->export->dropSequence('nonexisting');
} catch(Doctrine_Exception $e) {

}
// another sequence
$result = $conn->export->dropSequence('people');

// drop a constraint
$conn->export->dropConstraint('events', 'PRIMARY', true);
// note: the third parameter gives a hint
//       that this is a primary key constraint
$conn->export->dropConstraint('event_participants', 'unique_participant');

// drop an index
$conn->export->dropIndex('events', 'event_timestamp');

// drop a table
$conn->export->dropTable('events');

// drop the database already!
$conn->export->dropDatabase('events_db');
</code>

 
 


 
 

++ Import
+++ Introduction

To see what's in the database, you can use the list*() family of functions in the Import module.

* listDatabases()

* listFunctions()

* listSequences(): takes optional database name as a parameter. If not supplied, the currently selected database is assumed.

* listTableConstraints(): takes a table name

* listTableFields(): takes a table name

* listTableIndexes(): takes a table name

* listTables(): takes an optional database name

* listTableTriggers(): takes a table name

* listTableViews(): takes a table name

* listUsers()

* listViews(): takes an optional database name


+++ Listing databases
<code type="php">
$dbs = $conn->import->listDatabases();
print_r($dbs);
</code>
+++ Listing sequences

<code type="php">
$seqs = $conn->import->listSequences('events_db');
print_r($seqs);
</code>

+++ Listing constraints

<code type="php">
$cons = $conn->import->listTableConstraints('event_participants');
</code>

+++ Listing table fields

<code type="php">
$fields = $conn->import->listTableFields('events');
print_r($fields);
/*
prints:
Array
(
    [0] => id
    [1] => name
    [2] => datetime
)
*/
</code>

+++ Listing table indices
<code type="php">
$idx = $conn->import->listTableIndexes('events');
print_r($idx);
/*
prints:
Array
(
    [0] => event_timestamp
)
*/
</code>

+++ Listing tables

<code type="php">
$tables = $conn->import->listTables();
print_r($tables);
/*
prints:
Array
(
    [0] => event_participants
    [1] => events
    [2] => people
)
*/
</code>

+++ Listing views

<code type="php">
// currently there is no method to create a view,
// so let's do it "manually"
$sql = "CREATE VIEW names_only AS SELECT name FROM people";
$conn->exec($sql);
$sql = "CREATE VIEW last_ten_events AS SELECT * FROM events ORDER BY id DESC LIMIT 0,10";
$conn->exec($sql);
// list views
$views = $conn->import->listViews();
print_r($views);
/*
prints:
Array
(
    [0] => last_ten_events
    [1] => names_only
)
*/
</code>

++ DataDict
+++ Introduction
Doctrine uses DataDict module internally to convert native RDBMS types to Doctrine types and the reverse. DataDict module uses two methods for the conversions:
1. getPortableDeclaration(), which is used for converting native RDBMS type declaration to portable Doctrine declaration
2. getNativeDeclaration(), which is used for converting portable Doctrine declaration to driver specific type declaration


+++ Getting portable declaration
<code type="php">
$dbh  = new PDO('mysql:host=localhost;dbname=test', 'username', 'pw');
$conn = Doctrine_Manager::getInstance()->openConnection($dbh);

$decl = $conn->dataDict->getPortableDeclaration('VARCHAR(255)');

print_r($decl);
/*
array('type' => 'string',
      'length' => 255,
      'fixed'  => false,
      'unsigned' => false
      );
*/
</code>
+++ Getting native declaration
<code type="php">
$dbh  = new PDO('mysql:host=localhost;dbname=test', 'username', 'pw');
$conn = Doctrine_Manager::getInstance()->openConnection($dbh);

$portableDecl = array('type' => 'string',
                             'length' => 20,
                             'fixed' => true);
$nativeDecl = $conn->dataDict->getNativeDeclaration($portableDecl);

print $nativeDecl; // CHAR(20)
</code>

++ Drivers
+++ Mysql
++++ Setting table type

<code type="php">
$dbh  = new PDO('dsn','username','pw');
$conn = Doctrine_Manager::getInstance()->openConnection($dbh);

$fields = array('id' => array(
                    'type' => 'integer',
                    'autoincrement' => true),
                'name' => array(
                    'type' => 'string',
                    'fixed' => true,
                    'length' => 8)
                );
// the following option is mysql specific and
// skipped by other drivers
$options = array('type' => 'MYISAM');

$conn->export->createTable('mytable', $fields);

// on mysql this executes query:
// CREATE TABLE mytable (id INT AUTO_INCREMENT PRIMARY KEY,
//        name CHAR(8));
</code>