[dev] Splitting datatree by application

[Michael Rubinsky]

I just finished doing this for ansel since we had a significant number 
of images in the datatree.  Personally, I used the phpMyAdmin 
application to perform the bulk of the data movement.  IIRC, this is 
basically what I did.  This may not be appropriate if you have a large 
database in production, but I offer it up for the record anyway...

1) Using the phpMyAdmin interface I created two new tables by coping 
the table structures of horde_datetree and horde_datatree_attributes.

2) Use a "INSERT INTO" sql statement like the following:
     INSERT INTO horde_datatree_ansel (`datatree_id`, `group_uid`, 
`user_uid`, `datatree_name`, `datatree_parents`, `datatree_order`, 
`datatree_data`, `datatree_serialized`, `datatree_updated`) SELECT * 
FROM horde_datatree WHERE horde_datatree.group_uid='horde.shares.ansel';

3) To get the matching records in the horde_datatree_attribute table I 
kindof cheated a bit.  I couldn't get -  WHERE datatree_id IN (SELECT 
DISTINCT datatree_id from horde_datatree WHERE 
group_uid='horde.shares.ansel')  - to work.  Not sure if it was my 
stupidity or perhaps mySQL can't do that type of embedded query? 
Anyway, what I did was copy *all* of the data to the new table.  I then 
used phpMyAdmin's linked table features to find the 'orphan' entries 
and delete them.


Thanks,
mike

--
The Horde Project (www.horde.org)
mrubinsk at horde.org


----- Message from ben at horde.org ---------
     Date: Sun, 04 Dec 2005 21:07:57 -0600
     From: Ben Chavet <ben at horde.org>
  Subject: [dev] Splitting datatree by application
       To: dev at lists.horde.org


> I'm trying to split my datatree tables, such that each application has
> their own set.  By doing so, I'm hoping to see an increase in
> performance.
>
> For example, I'd like to move all members of horde.shares.trean into
> trean_datatree and trean_datatree_attbributes tables.  trean_datatree
> is pretty straightforward, but trean_datatree_attributes is not.
>
> Sadly, SQL is not my strong point, so if anyone could help me develop a
> series of SQL statements that would move (or copy) the data, I'd
> greatly appreciate it.
>
> --Ben


----- End message from ben at horde.org -----