cvs: pear /DB_NestedSet/docs migrateDB.txt mysql.sql

datenpunk Mon Oct 6 19:41:25 2003 EDT Added files: /pear/DB_NestedSet/docs migrateDB.txt Modified files: /pear/DB_NestedSet/docs mysql.sql Log: Added short howto on how to move to the 1.3+ db sheme Index: pear/DB_NestedSet/docs/mysql.sql diff -u pear/DB_NestedSet/docs/mysql.sql:1.1.1.1 pear/DB_NestedSet/docs/mysql.sql:1.2 --- pear/DB_NestedSet/docs/mysql.sql:1.1.1.1 Wed Apr 9 18:28:14 2003 +++ pear/DB_NestedSet/docs/mysql.sql Mon Oct 6 19:41:25 2003 @@ -1,19 +1,21 @@ # The table which holds the structure CREATE TABLE tb_nodes ( - STRID int(11) NOT NULL auto_increment, - ROOTID int(11) NOT NULL default '0', - l int(11) NOT NULL default '0', - r int(11) NOT NULL default '0', - STREH tinyint(4) NOT NULL default '0', - LEVEL int(11) NOT NULL default '0', - STRNA char(128) NOT NULL default '', - PRIMARY KEY (STRID), - KEY ROOTID (ROOTID), - KEY STREH (STREH), - KEY l (l), - KEY r (r), - KEY SRLR (STRID,ROOTID,l,r), - KEY LEVEL (LEVEL) + STRID int(11) NOT NULL auto_increment, + ROOTID int(11) NOT NULL default '0', + l int(11) NOT NULL default '0', + r int(11) NOT NULL default '0', + PARENT int(11) NOT NULL default '0', + STREH int(11) NOT NULL default '0', + LEVEL int(11) NOT NULL default '0', + STRNA char(128) NOT NULL default '', + PRIMARY KEY (STRID), + KEY ROOTID (ROOTID), + KEY STREH (STREH), + KEY l (l), + KEY r (r), + KEY LEVEL (LEVEL), + KEY SRLR (ROOTID,l,r), + KEY parent (PARENT) ) TYPE=MyISAM COMMENT='NestedSet table'; # A table which is used for a little table locking to avoid conflicts Index: pear/DB_NestedSet/docs/migrateDB.txt +++ pear/DB_NestedSet/docs/migrateDB.txt # $Id: migrateDB.txt,v 1.1 2003/10/06 23:41:25 datenpunk Exp $ # # Short guide on how to migrate a < 1.3 tree to 1.3+ # # @author Daniel Khan <dk@webcluster.at> # @package DB_NestedSet # @version $Revision: 1.1 $ 1. Backup your data!!! 2. Create a copy of your current node table (copy only the structure). Maybe you want to add an integer column to tb_nodes2 which will contain the magic parent field (gains some performance). Let's call it PARENT. Don't forget to set it inside $params afterwards Now you should have something like tb_nodes1 and tb_nodes2. tb_nodes2 is empty for now. 3. Create a DB_NestedSet instance for each table: <?php $params = array( "STRID" => "id", // "id" must exist "ROOTID" => "rootid", // "rootid" must exist "l" => "l", // "l" must exist "r" => "r", // "r" must exist "STREH" => "norder", // "order" must exist "LEVEL" => "level", // "level" must exist "STRNA" => "name" // Custom - specify as many fields you want ); $params2 = array( "STRID" => "id", // "id" must exist "ROOTID" => "rootid", // "rootid" must exist "l" => "l", // "l" must exist "r" => "r", // "r" must exist "STREH" => "norder", // "order" must exist "LEVEL" => "level", // "level" must exist "STRNA" => "name", // Custom - specify as many fields you want "PARENT" => "parent" // Not needed but recommended!!! ); $db_driver = 'DB'; $db_dsn = 'mysql://user:password@localhost/test'; $NeSe = DB_NestedSet::factory($db_driver, $db_dsn, $params); $NeSe->setAttr(array ( 'node_table' => 'tb_nodes', 'lock_table' => 'tb_locks', 'lockTTL' => 1, 'debug' => 0) ); $NeSe2 = DB_NestedSet::factory($db_driver, $db_dsn, $params2); $NeSe2->setAttr(array ( 'node_table' => 'tb_nodes2', // Note the difference - it's tb_nodes2 'lock_table' => 'tb_locks', 'lockTTL' => 1, 'debug' => 0) ); 4. Now pass this 2 objects to the static method convertTreeModel(); DB_NestedSet::convertTreeModel($NeSe, $NeSe2); 5. Now tb_nodes2 should be filled with a new representation of tb_nodes. 6. Verify that the row count didn't change and that everything's O.K. 7. Be happy with your new 1.3+ table. ?>