Proposal for "Database_SQL_Intervals"

» Metadata » Status
  • Category: Database
  • Proposer: Samuel ROZE 
  • License: LGPL
  • Status: Proposed
» Description
Database_SQL_Intervals does an abstraction between an SQL table which is formated as a hierarchy table using intervals. It help developper to use a hierarchy without many reflexions. The class is able to:
- Create elements [into others]
- Remove elements (and its children)
- Move element into another

A new hierarchy gestion
Hierarchy gestion is a complex think and in a database or in a static file, this hierachy have to be structured to be (re)used after. The hierarchy gestion using intervals is a way to structure your hierarchy tables. Contrary to recursive method, the intervals method provide a very simple DB structure which can be (partitaly or not) export even more simply !

A more complex table
In fact, (just!) into the table, values structure is more complex. It's why using a library to manage these tables is great : you're sure that you'll not break the table integrity with your table management.

Very simple to use:
<?php
$sql = PDO( ... );
$intervalles = new Database_SQL_Intervals($sql);

$intervalles->add('my_table', $parent, array('field' => 'value'));
$intervalles->move('my_table', $object, $target);
$intervalles->remove('my_table', $object);
?>

Example
Consider a table "my_table" like this:


<?php
require_once 'Database/SQL_Intervals.php';
require_once 'Database/SQL_Intervals_mysql.php';

define('TABLE', 'my_table');

// Creation of the database access
$sql = new PDO('mysql:host=localhost;dbname=tests', 'root', '');

// Creation of the Database_SQL_Intervals instance
$intervals = new Database_SQL_Intervals($sql);

// Creation of elements...
$first = $intervals->add(TABLE, 1, array('other1' => 'first'));
$second = $intervals->add(TABLE, 1, array('other1' => 'first2', 'other2' => 'value2'));

// ... and children
$intervals->add(TABLE, $second, array('other1' => 'child');
$last = $intervals->add(TABLE, $second, array('other2' => 'child');
?>

At the moment, we have a table like:


Which is provide this hierarchy:


<?php
// Move
$intervals->move(TABLE, $second, $first);
$intervals->move(TABLE, $last, 1); // 1 = Top-Father
?>

At the end of the first function, we have a hierarchy tree like:


And at the end of the second function:


Which it structured into the table:


To remove an object, you just have to call "remove" method:
<?php
$intervals->remove(TABLE, $second);
?>

And the tree's like that:


Note: package.xml & documentations will be generated after corrections which you'll give me will be done. :)
See test/tests.php
» Dependencies » Links
  • PHP >= 5
» Timeline » Changelog
  • First Draft: 2009-08-19
  • Proposal: 2009-08-19

  • Samuel ROZE
    [2009-08-19 23:57 UTC]

    New download link : it include a new file, "structure.sql" which is just a SQL query to create the init table.

  • Samuel ROZE
    [2009-08-20 15:58 UTC]

    There's a new long description of the package.