00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028 package org.openmobileis.synchro.algo.replication.utils.impl;
00029
00030 import java.sql.*;
00031
00032 import org.openmobileis.common.util.exception.DatabaseException;
00033
00034
00042 public class MSSQLAtomicObjectQueryManager extends AtomicObjectQueryManager {
00043
00044 private static String queryUpdate =
00045 "UPDATE SYNC_UID SET TIME_STAMP=%0%, CHECKSUM='%1%', MODIFICATION_TYPE =%2% , USERLINK ='%3%' " +
00046 "WHERE UID='%4%' AND SERVICEID='%5%'";
00047
00048 private static String queryCreate =
00049 "INSERT INTO SYNC_UID (UID , SERVICEID, TIME_STAMP, CHECKSUM, MODIFICATION_TYPE, CREATION_DATE, USERLINK) " +
00050 "VALUES ('%0%', '%1%', %2%, '%3%', %4%, %5%, '%6%')";
00051
00052 private static String createIndex =
00053 "CREATE INDEX serviceid ON SYNC_UID (SERVICEID)";
00054
00055 private static String uidIndex =
00056 "CREATE INDEX uidid ON SYNC_UID (UID, SERVICEID)";
00057
00058 private static String modifIndex =
00059 "CREATE INDEX modifid ON SYNC_UID (SERVICEID, TIME_STAMP, MODIFICATION_TYPE)";
00060
00061
00062 private static String queryCreateTable =
00063 "CREATE TABLE SYNC_UID ("
00064 +"UID VARCHAR(250) NOT NULL,"
00065 +"SERVICEID VARCHAR(50) NOT NULL,"
00066 +"TIME_STAMP BIGINT,"
00067 +"CHECKSUM VARCHAR(25) NOT NULL,"
00068 +"MODIFICATION_TYPE SMALLINT,"
00069 +"CREATION_DATE BIGINT,"
00070 +"USERLINK VARCHAR(25) NOT NULL,"
00071 +" CONSTRAINT uid PRIMARY KEY (UID, SERVICEID)"
00072 +") ";
00073
00074
00075 private static String purgeDetetedAtomicObject =
00076 "DELETE FROM SYNC_UID WHERE SERVICEID = '%0%' AND TIME_STAMP < %1% AND MODIFICATION_TYPE=3";
00077
00078
00079 private static String queryGetAtomicObject =
00080 "SELECT UID, CHECKSUM, TIME_STAMP, MODIFICATION_TYPE, CREATION_DATE FROM SYNC_UID WHERE UID='%0%' AND SERVICEID = '%1%'";
00081
00082
00083 private static String queryGetAllModifiedAtomicObjectForServiceSince =
00084 "SELECT UID, MODIFICATION_TYPE, CREATION_DATE FROM SYNC_UID WHERE SERVICEID = '%0%' AND TIME_STAMP > %1% AND USERLINK='%2%'";
00085
00086 public void createAtomicObject(String[] parameters) throws DatabaseException {
00087 this.executeUpdate(queryCreate, parameters);
00088 }
00089
00090
00091
00092
00093 public void createTable() throws DatabaseException, java.sql.SQLException {
00094 try {
00095 Connection con = this.getDbManager().getConnection();
00096 DatabaseMetaData dbmd = con.getMetaData();
00097 String[] tableType = {"TABLE"};
00098 ResultSet result = dbmd.getTables (null,null,"sync_uid", tableType);
00099 if (!result.next()) {
00100 this.executeUpdate(queryCreateTable, null);
00101 this.executeUpdate(createIndex, null);
00102 this.executeUpdate(uidIndex, null);
00103 this.executeUpdate(modifIndex, null);
00104 String[] data = {"0", "0", "0","0", "0", "0", ""};
00105 this.createAtomicObject(data);
00106 }
00107 } finally {
00108 this.getDbManager().garbageOpenedConnection();
00109 }
00110 }
00111
00112 public void updateAtomicObject(String[] parameters) throws DatabaseException {
00113 this.executeUpdate(queryUpdate, parameters);
00114 }
00115
00116 public void purgeDeletedAtomicObject(String[] parameters) throws DatabaseException {
00117 this.executeUpdate(purgeDetetedAtomicObject, parameters);
00118 }
00119
00120
00121
00122
00123
00124
00125 public ResultSet getAtomicObject(String parameters[]) throws DatabaseException {
00126 return (this.executeQuery(queryGetAtomicObject, parameters));
00127 }
00128
00129
00130
00131
00132
00133
00134 public ResultSet getAllModifiedAtomicObjectForServiceSince (String parameters[]) throws DatabaseException {
00135 return (this.executeQuery(queryGetAllModifiedAtomicObjectForServiceSince, parameters));
00136 }
00137
00138 }