Author: athimel Date: 2014-04-18 18:28:46 +0200 (Fri, 18 Apr 2014) New Revision: 3075 Url: http://forge.nuiton.org/projects/topia/repository/revisions/3075 Log: Guide de migration SQL pour indexed vs ordered Added: trunk/src/site/rst/migrate_to_3.0_ordered_vs_indexed_migration.rst Modified: trunk/src/site/rst/migrate_to_3.0.rst Modified: trunk/src/site/rst/migrate_to_3.0.rst =================================================================== --- trunk/src/site/rst/migrate_to_3.0.rst 2014-04-18 15:48:33 UTC (rev 3074) +++ trunk/src/site/rst/migrate_to_3.0.rst 2014-04-18 16:28:46 UTC (rev 3075) @@ -62,8 +62,8 @@ org.nuiton.topia.templates -Migration du modèle -=================== +Refonte des indexed/ordered +=========================== |MANDATORY| @@ -77,6 +77,7 @@ * Les types des collections générées ont changés afin d'utiliser un contrat plus proche du modèle (selon que les stéréotypes "unique" et/ou "ordered" ont été posés). +Vous trouverez des examples de scripts de migration dans la `section dediée`_. Code déprécié ============= @@ -311,6 +312,7 @@ grep -m 1 -nr 'TopiaContextFactory.CONFIG_' . | awk -F ':' '{print $1}' | xargs sed -i -e 's/TopiaContextFactory.CONFIG_/TopiaConfigurationConstants.CONFIG_/g' +.. _section dediée: ./migrate_to_3.0_ordered_vs_indexed_migration.html .. |RECOMMENDED| image:: recommended.png .. |MANDATORY| image:: mandatory.png Added: trunk/src/site/rst/migrate_to_3.0_ordered_vs_indexed_migration.rst =================================================================== --- trunk/src/site/rst/migrate_to_3.0_ordered_vs_indexed_migration.rst (rev 0) +++ trunk/src/site/rst/migrate_to_3.0_ordered_vs_indexed_migration.rst 2014-04-18 16:28:46 UTC (rev 3075) @@ -0,0 +1,132 @@ +.. - +.. * #%L +.. * ToPIA +.. * $Id$ +.. * $HeadURL$ +.. * %% +.. * Copyright (C) 2004 - 2014 CodeLutin +.. * %% +.. * This program is free software: you can redistribute it and/or modify +.. * it under the terms of the GNU Lesser General Public License as +.. * published by the Free Software Foundation, either version 3 of the +.. * License, or (at your option) any later version. +.. * +.. * This program is distributed in the hope that it will be useful, +.. * but WITHOUT ANY WARRANTY; without even the implied warranty of +.. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +.. * GNU General Lesser Public License for more details. +.. * +.. * You should have received a copy of the GNU General Lesser Public +.. * License along with this program. If not, see +.. * <http://www.gnu.org/licenses/lgpl-3.0.html>. +.. * #L% +.. - + +============================================= +Migration vers ToPIA 3.0 - ordered vs indexed +============================================= + + +Vous devez migrer votre modèle zargo depuis la beta-3 suite à une confusion entre ordered et indexed (stéréotypes +à placer sur une association-end d'un lien entre deux entités). + + * Le stéréotype "indexed" est déprécié, vous devez le remplacer par "ordered" + * Le stéréotype "ordered" maintient l'ordre d'insertion en base, il faut donc vous assurer qu'une colonne + _idx est bien présente dans la base et au besoin écrire la migration nécessaire pour ajouter la colonne + et fixer les indexes manquants (en commençant à 0). + * Les types des collections générées ont changés afin d'utiliser un contrat plus proche du modèle (selon + que les stéréotypes "unique" et/ou "ordered" ont été posés). + + +Exemples de scripts de migration SQL +==================================== + +Cas simple d'une relation 1..n +------------------------------ + +:: + + -- + -- relation is : Car -----[*]- Tyre + -- + + -- Add index to be able to order tyres + ALTER TABLE tyre ADD COLUMN car_idx integer; + + -- Generate unique car_idx according to topiaCreateDate and topiaId + UPDATE tyre SET car_idx = ( + SELECT COUNT(topiaId) + FROM tyre t + WHERE t.car = tyre.car + AND (t.topiaCreateDate || t.topiaId) < (tyre.topiaCreateDate || tyre.topiaId) + ); + + +Cas d'une relation 1..n vers une entité abstraite +------------------------------------------------- + +Note : cela n'est nécessaire que pour la stratégie d'héritage union-subclass. + + +:: + + + -- + -- relation is : Person -----[*]- Pet (abstract) + -- ^ + -- | + -- ----------- + -- | | + -- Cat Dog + -- + + -- create a temporary table to be able to orders cats and dogs + CREATE TABLE person_to_pet_tmp ( + person character varying(255) NOT NULL, + type character varying(255) NOT NULL, + petTopiaId character varying(255) NOT NULL, + petTopiaCreateDate timestamp without time zone, + person_idx integer + ); + + -- populate table with cats + INSERT INTO person_to_pet_tmp (person, type, petTopiaId, petTopiaCreateDate) + SELECT person, 'cat', topiaId, topiaCreateDate FROM cat; + + -- populate table with dogs + INSERT INTO person_to_pet_tmp (person, type, petTopiaId, petTopiaCreateDate) + SELECT person, 'dog', topiaId, topiaCreateDate FROM dog; + + -- compute index for cats and dogs together + UPDATE person_to_pet_tmp SET person_idx = ( + SELECT COUNT(ptpt.person) + FROM person_to_pet_tmp ptpt + WHERE ptpt.person = person_to_pet_tmp.person + AND (ptpt.petTopiaCreateDate || ptpt.petTopiaId) < (person_to_pet_tmp.petTopiaCreateDate || person_to_pet_tmp.petTopiaId) + ); + + -- add index to be able to order cats + ALTER TABLE cat ADD COLUMN person_idx integer; + + -- add indexes to cats + UPDATE cat SET person_idx = ( + SELECT person_idx + FROM person_to_pet_tmp ptpt + WHERE ptpt.petTopiaId = cat.topiaId + ); + + -- add index to be able to order dogs + ALTER TABLE dog ADD COLUMN person_idx integer; + + -- add indexes to dogs + UPDATE dog SET person_idx = ( + SELECT person_idx + FROM person_to_pet_tmp ptpt + WHERE ptpt.petTopiaId = dog.topiaId + ); + + -- remove temporary table + DROP TABLE person_to_pet_tmp; + + + Property changes on: trunk/src/site/rst/migrate_to_3.0_ordered_vs_indexed_migration.rst ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native