= Categorieën =

== Model ==
[[Image(categorieen.png)]]

== !Structurele problemen! ==
 * De tabel ''section_category'' bevat entries met ''category_id'' = 0. Deze category bestaat niet in de tabel ''categories'' waardoor de integriteit van deze tabel verloren gaat. Meer uitleg over ''referential integrity'': http://en.wikipedia.org/wiki/Referential_integrity
 * De huidige key van de tabel is ''(section_id,category_id,uid)'', wat betekent dat volgende entries mogelijk zijn:
{{{
+------------+-------------+-----+----------+
| section_id | category_id | uid | position |
+------------+-------------+-----+----------+
|          1 |           1 |   1 |        0 |
|          1 |           2 |   1 |        0 |
|          1 |           3 |   1 |        0 |
+------------+-------------+-----+----------+
}}}
De gebruiker 1 heeft sectie 1 in zowel category 1,2 en 3. Terwijl de relatie van deze entiteiten is dat een gebruiker 1 sectie in 1 categorie kan onderbrengen. 
De primary key op deze tabel zou moeten zijn ''(section_id,category_id)''. De kolom ''uid'' is overbodig want de rijen kunnen geidentificeerd worden aan de hand van ''user_id'' in ''categories'' en ''section_user''.

 * De query om alle secties van een gebruiker op te halen per categorie is nu:
{{{
(
        SELECT su.`permission` , s.`xml_uri` , s.`title` AS `section_title` , s.`author_id` , s.`id` AS `section_id` ,
                        sc.`category_id` , c.`title` , c.`position` AS `position` , c.`collapsed` , sc.`position` AS `section_position`
        FROM `section_category` sc
        LEFT JOIN `categories` c ON c.`id` = `sc`.`category_id`
        LEFT JOIN `section_user` su ON su.`section_id` = sc.`section_id` AND su.`user_id` = 126
        JOIN `section` s ON s.`id` = sc.`section_id`
        WHERE sc.`uid` = 126
        )
        UNION
        (
        SELECT su.`permission` , NULL AS `xml_uri` , NULL AS `section_title` , NULL AS `author_id` , NULL AS `section_id` ,
                        c.`id` AS `category_id` , c.`title` , c.`position` AS `position` , c.`collapsed` , sc.`position` AS `section_position`
        FROM `categories` c
        LEFT JOIN `section_category` sc ON c.`id` = `sc`.`category_id` AND sc.`uid` = 126
        LEFT JOIN `section_user` su ON su.`section_id` = sc.`section_id`
        WHERE c.`uid` = 126
        AND sc.section_id is NULL
        )
        ORDER BY `position` ASC , `section_position`;
}}}

Dit kan ook op een meer eenvoudig en duidelijkere manier opgehaald worden (reeds rekening houdend met de aanpassing van het weglaten van 0 als categorieloos en uid verwijderd):
{{{
(SELECT `su`.`permission` , `s`.`xml_uri` , `s`.`title` AS `section_title` , `s`.`author_id` , `s`.`id` AS `section_id` , 
                `sc`.`category_id` , `c`.`title` , `c`.`position` AS `position` , `c`.`collapsed` , `sc`.`position` AS `section_position` FROM `section_user` `su`
 LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
 LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
 WHERE `su`.`user_id` = 126 AND `s`.`id` IS NOT NULL)
UNION
(SELECT NULL AS `permission` , NULL AS `xml_uri` , NULL AS `section_title` , NULL AS `author_id` , NULL AS `section_id` , 
                    c.`id` AS `category_id` , c.`title` , c.`position` AS `position` , c.`collapsed`, NULL AS `section_position` FROM `categories` `c`
 LEFT JOIN `section_category` `sc` ON `c`.`id` = `sc`.`category_id` 
 WHERE `c`.`uid` = 126 AND `sc`.`category_id` IS NULL) 
ORDER BY `position` ASC, `section_position`;
}}}

Note: 's'.'id' IS NOT NULL is te wijten aan invalid data aanwezig in ''section_user'' waar ''section_id'' entries aanwezig zijn die niet meer bestaan in ''section''.
=== Query ===
Haal alle secties op van een user via de tabel ''section_user'':
{{{
SELECT `su`.* FROM `section_user` `sc` WHERE `su`.`user_id` = 126
73 rows in set (0.00 sec)
}}}
Breidt de resultaten uit met entries uit ''section_category'', d.w.z. zoek de categorie voor de gevonden secties uit ''section_user'' :
{{{
SELECT `su`.*,`sc`.* FROM `section_user` `su` LEFT JOIN `section_category` `sc` ON `su`.`section_id` = `sc`.`section_id` WHERE `su`.`user_id` = 126;
116 rows in set (0.00 sec)
}}}
Omdat de relatie zonder bovenstaande wijziging toelaat dat secties tot categorieen behoren die over meerdere gebruikers worden gedeeld (de 0 categorie) is de resultset uitgebreid naar 116 rijen.

Dit probleem wordt opgelost door de query uit te breiden naar de ''categories'' tabel waar de gegevens voor gevonden ''category_id'' worden aangevuld:
{{{
SELECT `su`.*,`sc`.*,`c`.* FROM `section_user` `su` 
LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
WHERE `su`.`user_id` = 126;
73 rows in set (0.00 sec)
}}}

Om de detailgegevens van de secties op te vragen dient de tabel ''section'' toegevoegd te worden:
{{{
SELECT `su`.*,`sc`.*,`c`.*,`s`.* FROM `section_user` `su`
 LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
 LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
 WHERE `su`.`user_id` = 126;
73 rows in set (0.00 sec)
}}}

Uit de resultaten kunnen NULL gegevens gevonden worden bij de details van de sectie, dit komt door inconsistentie van de data in ''section_user'' waar verwijzingen staan naar ''section_id''s die niet meer bestaan in de ''section'' tabel. Dit wordt opgelost door te controleren op ''section_id'' via ''section'' tabel:
{{{
SELECT `su`.*,`sc`.*,`c`.*,`s`.* FROM `section_user` `su`
 LEFT JOIN (`section_category` `sc`, `categories` `c`) ON (`su`.`section_id` = `sc`.`section_id` AND `sc`.`category_id` = `c`.`id` AND `c`.`uid` = 126)
 LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
 WHERE `su`.`user_id` = 126 AND `s`.`id` IS NOT NULL;
48 rows in set (0.00 sec)
}}}

Om de resultaten uit te breiden met categorieen die geen secties bevatten dient een ''union'' uitgevoerd te worden op de ''categories'' tabel waar de ''category_id'' niet voorkomt in ''section_category'':
{{{
SELECT `c`.* FROM `categories` `c` LEFT JOIN `section_category` `sc` ON `c`.`id` = `sc`.`category_id`  WHERE `c`.`uid` = 126 AND `sc`.`category_id` IS NULL
}}}

=== Performance query ===
Oude query:
{{{
+----+--------------+------------+--------+--------------------+-----------------+---------+----------------------------+------+-------------------------+
| id | select_type  | table      | type   | possible_keys      | key             | key_len | ref                        | rows | Extra                   |
+----+--------------+------------+--------+--------------------+-----------------+---------+----------------------------+------+-------------------------+
|  1 | PRIMARY      | s          | ALL    | PRIMARY            | NULL            | NULL    | NULL                       | 5459 |                         |
|  1 | PRIMARY      | sc         | ref    | PRIMARY            | PRIMARY         | 4       | curios.s.id                |    1 | Using where             |
|  1 | PRIMARY      | c          | eq_ref | PRIMARY            | PRIMARY         | 4       | curios.sc.category_id      |    1 |                         |
|  1 | PRIMARY      | su         | eq_ref | PRIMARY,section_id | PRIMARY         | 8       | const,curios.sc.section_id |    1 |                         |
|  2 | UNION        | c          | ref    | uid                | uid             | 4       | const                      |    2 |                         |
|  2 | UNION        | sc         | ref    | idx_category_id    | idx_category_id | 4       | curios.c.id                | 1326 | Using where; Not exists |
|  2 | UNION        | su         | ref    | section_id         | section_id      | 4       | curios.sc.section_id       |    1 |                         |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL               | NULL            | NULL    | NULL                       | NULL | Using filesort          |
+----+--------------+------------+--------+--------------------+-----------------+---------+----------------------------+------+-------------------------+
}}}

Nieuwe query:
{{{
+----+--------------+------------+--------+-------------------------+-----------------+---------+----------------------------------+------+--------------------------------------+
| id | select_type  | table      | type   | possible_keys           | key             | key_len | ref                              | rows | Extra                                |
+----+--------------+------------+--------+-------------------------+-----------------+---------+----------------------------------+------+--------------------------------------+
|  1 | PRIMARY      | su         | ref    | PRIMARY,section_id      | PRIMARY         | 4       | const                            |   73 |                                      |
|  1 | PRIMARY      | c          | ref    | PRIMARY,uid             | uid             | 4       | const                            |    0 |                                      |
|  1 | PRIMARY      | sc         | ref    | PRIMARY,idx_category_id | PRIMARY         | 8       | curios.su.section_id,curios.c.id |    1 |                                      |
|  1 | PRIMARY      | s          | eq_ref | PRIMARY                 | PRIMARY         | 8       | curios.su.section_id             |    1 | Using where                          |
|  2 | UNION        | c          | ref    | uid                     | uid             | 4       | const                            |    2 |                                      |
|  2 | UNION        | sc         | ref    | idx_category_id         | idx_category_id | 4       | curios.c.id                      | 1326 | Using where; Using index; Not exists |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL                    | NULL            | NULL    | NULL                             | NULL | Using filesort                       |
+----+--------------+------------+--------+-------------------------+-----------------+---------+----------------------------------+------+--------------------------------------+
}}}
== Integriteit ==
Controleer de integriteit van de bovenstaande tabellen m.b.v. de volgende queries:

 * ''uid'' in de tabel ''categories'' moet bestaan in de ''user'' tabel:
{{{
SELECT COUNT( `c`.`uid` )
 FROM `categories` `c`
 LEFT JOIN `user` `u` ON `c`.`uid` = `u`.`id`
 WHERE `u`.`id` IS NULL 
}}}

 * ''category_id'' in de tabel ''section_category'' moet bestaan in de ''categories'' tabel, tenzij ''category_id'' == 0 (categorieloze sectie):
{{{
SELECT COUNT( `sc`.`category_id` )
 FROM `section_category` `sc`
 LEFT JOIN `categories` `c` ON `sc`.`category_id` = `c`.`id`
 WHERE `sc`.`category_id` != 0 AND `c`.`id` IS NULL 
}}}

 * ''section_id'' in de tabel ''section_category'' moet bestaan in de ''section'' tabel:
{{{
SELECT COUNT( `sc`.`section_id` )
FROM `section_category` `sc`
LEFT JOIN `section` `s` ON `sc`.`section_id` = `s`.`id`
WHERE `s`.`id` IS NULL
}}}

 * ''uid'' in de tabel ''section_category'' moet bestaan in de ''user'' tabel:
{{{
SELECT COUNT( `sc`.`uid` )
 FROM `section_category` `sc`
 LEFT JOIN `user` `u` ON `sc`.`uid` = `u`.`id`
 WHERE `u`.`id` IS NULL 
}}}

 * ''author_id'' in de tabel ''section'' moet bestaan in de ''user'' tabel:
{{{
SELECT COUNT( `s`.`author_id` )
 FROM `section` `s`
 LEFT JOIN `user` `u` ON `s`.`author_id` = `u`.`id`
 WHERE `u`.`id` IS NULL 
}}}

 * ''section_id'' in de tabel ''section_user'' moet bestaan in de ''section'' tabel:
{{{
SELECT COUNT( `su`.`section_id` )
FROM `section_user` `su`
LEFT JOIN `section` `s` ON `su`.`section_id` = `s`.`id`
WHERE `s`.`id` IS NULL
}}}

 * ''user_id'' in de tabel ''section_user'' moet bestaan in de ''user'' tabel:
{{{
SELECT COUNT( `su`.`user_id` )
 FROM `section_user` `su`
 LEFT JOIN `user` `u` ON `su`.`user_id` = `u`.`id`
 WHERE `u`.`id` IS NULL 
}}}