001: /*
002: * This file is part of the GeOxygene project source files.
003: *
004: * GeOxygene aims at providing an open framework which implements OGC/ISO specifications for
005: * the development and deployment of geographic (GIS) applications. It is a open source
006: * contribution of the COGIT laboratory at the Institut Géographique National (the French
007: * National Mapping Agency).
008: *
009: * See: http://oxygene-project.sourceforge.net
010: *
011: * Copyright (C) 2005 Institut Géographique National
012: *
013: * This library is free software; you can redistribute it and/or modify it under the terms
014: * of the GNU Lesser General Public License as published by the Free Software Foundation;
015: * either version 2.1 of the License, or any later version.
016: *
017: * This library is distributed in the hope that it will be useful, but WITHOUT ANY
018: * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
019: * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
020: *
021: * You should have received a copy of the GNU Lesser General Public License along with
022: * this library (see file LICENSE if present); if not, write to the Free Software
023: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024: *
025: */
026:
027: package fr.ign.cogit.geoxygene.util.loader;
028:
029: import java.math.BigDecimal;
030: import java.sql.CallableStatement;
031: import java.sql.Connection;
032: import java.sql.ResultSet;
033: import java.sql.Statement;
034: import java.util.ArrayList;
035: import java.util.Iterator;
036: import java.util.List;
037:
038: import fr.ign.cogit.geoxygene.datatools.Geodatabase;
039:
040: /**
041: * Genere les identifiant d'une table (colonne COGITID).
042: * Dans l'ideal, plusieurs types de generation devraient etre possibles :
043: * Simple (i.e. de 1 a N), unicite sur toutes les tables geographiques de la base,
044: * empreinte numerique, recopie d'une autre colonne ...
045: * Pour l'instant seuls simple,
046: * et unicite sur toutes les tables geographiques de la base (par l'algo du max) fonctionnent.
047: *
048: * @author Thierry Badard & Arnaud Braun
049: * @version 1.1
050: *
051: */
052:
053: public class GenerateIds {
054:
055: ///////////////////////////////////////////////////////////////////////////////////////////////
056: ///////////////////////////////////////////////////////////////////////////////////////////////
057: private Geodatabase data;
058: private String tableName;
059: private int maxID = 0; // identifiant maximum des cogitid dans tout le jeu
060: private boolean unique; // veut-on des identifiants uniques sur toute la base ?
061:
062: private final static String ORACLE_COLUMN_QUERY = "SELECT TABLE_NAME FROM USER_SDO_GEOM_METADATA";
063: private final static String POSTGIS_COLUMN_QUERY = "SELECT F_TABLE_NAME FROM GEOMETRY_COLUMNS";
064:
065: ///////////////////////////////////////////////////////////////////////////////////////////////
066: ///////////////////////////////////////////////////////////////////////////////////////////////
067: public GenerateIds(Geodatabase Data, String TableName,
068: boolean Unique) {
069: data = Data;
070: tableName = TableName;
071: unique = Unique;
072: }
073:
074: ///////////////////////////////////////////////////////////////////////////////////////////////
075: ///////////////////////////////////////////////////////////////////////////////////////////////
076: public void genere() {
077: dropColumnID();
078: addColumnID();
079: if (unique) {
080: if (data.getDBMS() == Geodatabase.ORACLE)
081: maxCOGITID(ORACLE_COLUMN_QUERY);
082: else if (data.getDBMS() == Geodatabase.POSTGIS)
083: maxCOGITID(POSTGIS_COLUMN_QUERY);
084: }
085: if (data.getDBMS() == Geodatabase.ORACLE)
086: genereIDOracle();
087: else if (data.getDBMS() == Geodatabase.POSTGIS)
088: genereIDPostgres();
089: }
090:
091: ///////////////////////////////////////////////////////////////////////////////////////////////
092: ///////////////////////////////////////////////////////////////////////////////////////////////
093: // ajoute une colonne "COGITID" et appelle la methode genereID
094: void addColumnID() {
095: try {
096: Connection conn = data.getConnection();
097: conn.commit();
098: Statement stm = conn.createStatement();
099: String query = "ALTER TABLE " + tableName
100: + " ADD COGITID INTEGER";
101: stm.executeUpdate(query);
102: System.out.println(tableName + " : colonne CogitID creee");
103: stm.close();
104: conn.commit();
105: } catch (Exception e) {
106: e.printStackTrace();
107: }
108: }
109:
110: ///////////////////////////////////////////////////////////////////////////////////////////////
111: ///////////////////////////////////////////////////////////////////////////////////////////////
112: // supprime la colonne cogitid
113: void dropColumnID() {
114: try {
115: Connection conn = data.getConnection();
116: conn.commit();
117: Statement stm = conn.createStatement();
118: try {
119: String query = "ALTER TABLE " + tableName
120: + " DROP COLUMN COGITID";
121: stm.executeUpdate(query);
122: System.out.println(tableName
123: + " : colonne CogitID effacee");
124: } catch (Exception ee) { // pas de colonne cogitid !!
125: conn.commit();
126: }
127: stm.close();
128: conn.commit();
129: } catch (Exception e) {
130: e.printStackTrace();
131: }
132: }
133:
134: ///////////////////////////////////////////////////////////////////////////////////////////////
135: ///////////////////////////////////////////////////////////////////////////////////////////////
136: // genere les identifiants dans la colonne COGITID, puis cree une cle primaire sur cette colonne
137: void genereIDOracle() {
138: try {
139: int i = 0;
140: Connection conn = data.getConnection();
141: Statement stm = conn.createStatement();
142: String query = "SELECT COUNT(*) FROM " + tableName;
143: ResultSet rs = (ResultSet) stm.executeQuery(query);
144: while (rs.next()) {
145: int nbCount = ((BigDecimal) rs.getObject(1)).intValue();
146: System.out.println(nbCount + " objets dans la table "
147: + tableName
148: + " ... generation des identifiants ...");
149: }
150:
151: // creation de la procedure PL/SQL de generation des clés
152: // A FAIRE : y a moyen de faire plus simple : utiliser séquence ?
153: // Ou utiliser la fonction 'cursor for update' et 'current of'
154: String proc = "CREATE OR REPLACE PROCEDURE genere_cogitid AS";
155: proc = proc + " BEGIN";
156: proc = proc + " DECLARE";
157: proc = proc + " i integer := " + maxID + ";";
158: proc = proc + " cursor c is select rowid from " + tableName
159: + ";";
160: proc = proc + " therowid rowid;";
161: proc = proc + " BEGIN";
162: proc = proc + " if i is null then i := 0; end if;";
163: proc = proc + " open c;";
164: proc = proc + " LOOP";
165: proc = proc + " fetch c into therowid;";
166: proc = proc + " exit when c%notfound;";
167: proc = proc + " i := i+1;";
168: proc = proc + " update " + tableName
169: + " set cogitid=i where rowid=therowid;";
170: proc = proc + " END LOOP;";
171: proc = proc + " close c;";
172: proc = proc + " END;";
173: proc = proc + " END genere_cogitid;";
174: stm.execute(proc);
175:
176: // execution de la procedure
177: CallableStatement cstm = conn
178: .prepareCall("begin GENERE_COGITID; end;");
179: cstm.execute();
180: cstm.close();
181:
182: // on enleve si ancienne cle primaire
183: try {
184: String update = "ALTER TABLE " + tableName
185: + " DROP PRIMARY KEY";
186: stm.executeUpdate(update);
187: System.out.println("cle primaire sur " + tableName
188: + " supprimee");
189: } catch (Exception e1) {
190: System.out.println("aucune cle primaire sur "
191: + tableName);
192: }
193:
194: // ajout de la cle primaire
195: String update = "ALTER TABLE " + tableName
196: + " ADD PRIMARY KEY (COGITID)";
197: stm.executeUpdate(update);
198: System.out.println("cle primaire sur " + tableName
199: + " ajoutee (colonne COGITID)");
200:
201: // fin
202: stm.close();
203: conn.commit();
204: } catch (Exception e) {
205: System.out.println(tableName);
206: e.printStackTrace();
207: }
208: }
209:
210: ///////////////////////////////////////////////////////////////////////////////////////////////
211: ///////////////////////////////////////////////////////////////////////////////////////////////
212: // genere les identifiants dans la colonne COGITID, puis cree une cle primaire sur cette colonne
213: void genereIDPostgres() {
214: try {
215: int i = 0;
216: Connection conn = data.getConnection();
217: conn.commit();
218: Statement stm = conn.createStatement();
219: String query = "SELECT COUNT(*) FROM " + tableName;
220: ResultSet rs = (ResultSet) stm.executeQuery(query);
221: while (rs.next()) {
222: int nbCount = ((Number) rs.getObject(1)).intValue();
223: System.out.println(nbCount + " objets dans la table "
224: + tableName
225: + " ... generation des identifiants ...");
226: }
227:
228: // Création d'une séquence
229: try {
230: String update = "create SEQUENCE seq_genere_cogitid";
231: stm.executeUpdate(update);
232: } catch (Exception ee) {
233: // La séquence existe déjà !
234: conn.commit();
235: }
236: conn.commit();
237:
238: // Affectation du maxID + 1 à la séquence
239: query = "SELECT setval ('seq_genere_cogitid', " + maxID + 1
240: + ")";
241: rs = (ResultSet) stm.executeQuery(query);
242: while (rs.next()) {
243: }
244: conn.commit();
245:
246: // Mise à jour de la table à l'aide de la sequence
247: String update = "update " + tableName
248: + " set cogitid = nextval('seq_genere_cogitid')";
249: stm.executeUpdate(update);
250: conn.commit();
251:
252: // on enleve si ancienne cle primaire
253: // Arnaud 28 oct : modif
254: query = "select con.conname, con.contype from pg_constraint con, pg_class cl";
255: query = query + " where con.conrelid = cl.oid";
256: query = query + " and cl.relname='" + tableName + "'";
257: rs = (ResultSet) stm.executeQuery(query);
258: String conName = "";
259: while (rs.next()) {
260: String conType = rs.getString(2);
261: if (conType.compareToIgnoreCase("p") == 0)
262: conName = rs.getString(1);
263: }
264: if (conName.compareTo("") != 0) {
265: update = "ALTER TABLE " + tableName
266: + " DROP CONSTRAINT " + conName;
267: stm.executeUpdate(update);
268: System.out.println("cle primaire sur " + tableName
269: + " supprimé : " + conName);
270: }
271:
272: // ajout de la cle primaire
273: update = "ALTER TABLE " + tableName
274: + " ADD PRIMARY KEY (COGITID)";
275: stm.executeUpdate(update);
276: System.out.println("cle primaire sur " + tableName
277: + " ajoutee (colonne COGITID)");
278:
279: // fin
280: stm.close();
281: conn.commit();
282: } catch (Exception e) {
283: System.out.println(tableName);
284: e.printStackTrace();
285: }
286: }
287:
288: ///////////////////////////////////////////////////////////////////////////////////////////////
289: ///////////////////////////////////////////////////////////////////////////////////////////////
290: // recherche du COGITID maximum parmi les tables géographiques (variable globale maxID)
291: public void maxCOGITID(String query) {
292: try {
293: Connection conn = data.getConnection();
294: conn.commit();
295: Statement stm = conn.createStatement();
296: ResultSet rs = stm.executeQuery(query);
297: List listOfTables = new ArrayList();
298: while (rs.next())
299: listOfTables.add(rs.getString(1));
300: Iterator it = listOfTables.iterator();
301: while (it.hasNext()) {
302: String tableName = (String) it.next();
303: try {
304: query = "SELECT MAX(COGITID) FROM " + tableName;
305: rs = stm.executeQuery(query);
306: int max = 0;
307: while (rs.next())
308: max = ((Number) rs.getObject(1)).intValue();
309: if (max > maxID)
310: maxID = max;
311: } catch (Exception ee) { // pas de colonne cogitID
312: conn.commit();
313: }
314: }
315: stm.close();
316: } catch (Exception e) {
317: e.printStackTrace();
318: }
319: }
320:
321: }
|