1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 package goldengate.common.database.model;
22
23 import goldengate.common.logging.GgInternalLogger;
24 import goldengate.common.logging.GgInternalLoggerFactory;
25
26 import java.sql.DriverManager;
27 import java.sql.SQLException;
28 import java.sql.Types;
29
30 import goldengate.common.database.DbConstant;
31 import goldengate.common.database.DbPreparedStatement;
32 import goldengate.common.database.DbRequest;
33 import goldengate.common.database.DbSession;
34 import goldengate.common.database.data.DbDataModel;
35 import goldengate.common.database.exception.GoldenGateDatabaseNoConnectionException;
36 import goldengate.common.database.exception.GoldenGateDatabaseNoDataException;
37 import goldengate.common.database.exception.GoldenGateDatabaseSqlException;
38
39
40
41
42
43
44 public abstract class DbModelPostgresql extends DbModelAbstract {
45
46
47
48 private static final GgInternalLogger logger = GgInternalLoggerFactory
49 .getLogger(DbModelPostgresql.class);
50
51 public static DbType type = DbType.PostGreSQL;
52
53
54
55
56 @Override
57 public DbType getDbType() {
58 return type;
59 }
60
61
62
63
64
65 public DbModelPostgresql() throws GoldenGateDatabaseNoConnectionException {
66 if (DbModelFactory.classLoaded) {
67 return;
68 }
69 try {
70 DriverManager.registerDriver(new org.postgresql.Driver());
71 DbModelFactory.classLoaded = true;
72 } catch (SQLException e) {
73
74 logger.error("Cannot register Driver " + type.name()+ "\n"+e.getMessage());
75 DbSession.error(e);
76 throw new GoldenGateDatabaseNoConnectionException(
77 "Cannot load database drive:" + type.name(), e);
78 }
79
80
81
82
83
84
85
86
87
88
89 }
90
91 @Override
92 public void validConnection(DbSession dbSession)
93 throws GoldenGateDatabaseNoConnectionException {
94
95 validConnectionSelect(dbSession);
96 }
97
98
99
100 protected static enum DBType {
101 CHAR(Types.CHAR, " CHAR(3) "),
102 VARCHAR(Types.VARCHAR, " VARCHAR(254) "),
103 LONGVARCHAR(Types.LONGVARCHAR, " TEXT "),
104 BIT(Types.BIT, " BOOLEAN "),
105 TINYINT(Types.TINYINT, " INT2 "),
106 SMALLINT(Types.SMALLINT, " SMALLINT "),
107 INTEGER(Types.INTEGER, " INTEGER "),
108 BIGINT(Types.BIGINT, " BIGINT "),
109 REAL(Types.REAL, " REAL "),
110 DOUBLE(Types.DOUBLE, " DOUBLE PRECISION "),
111 VARBINARY(Types.VARBINARY, " BYTEA "),
112 DATE(Types.DATE, " DATE "),
113 TIMESTAMP(Types.TIMESTAMP, " TIMESTAMP ");
114
115 public int type;
116
117 public String constructor;
118
119 private DBType(int type, String constructor) {
120 this.type = type;
121 this.constructor = constructor;
122 }
123
124 public static String getType(int sqltype) {
125 switch (sqltype) {
126 case Types.CHAR:
127 return CHAR.constructor;
128 case Types.VARCHAR:
129 return VARCHAR.constructor;
130 case Types.LONGVARCHAR:
131 return LONGVARCHAR.constructor;
132 case Types.BIT:
133 return BIT.constructor;
134 case Types.TINYINT:
135 return TINYINT.constructor;
136 case Types.SMALLINT:
137 return SMALLINT.constructor;
138 case Types.INTEGER:
139 return INTEGER.constructor;
140 case Types.BIGINT:
141 return BIGINT.constructor;
142 case Types.REAL:
143 return REAL.constructor;
144 case Types.DOUBLE:
145 return DOUBLE.constructor;
146 case Types.VARBINARY:
147 return VARBINARY.constructor;
148 case Types.DATE:
149 return DATE.constructor;
150 case Types.TIMESTAMP:
151 return TIMESTAMP.constructor;
152 default:
153 return null;
154 }
155 }
156 }
157
158 @Override
159 public void createTables(DbSession session) throws GoldenGateDatabaseNoConnectionException {
160
161 String createTableH2 = "CREATE TABLE ";
162 String primaryKey = " PRIMARY KEY ";
163 String notNull = " NOT NULL ";
164
165
166 String action = createTableH2 + DbDataModel.table + "(";
167 DbDataModel.Columns[] ccolumns = DbDataModel.Columns
168 .values();
169 for (int i = 0; i < ccolumns.length - 1; i ++) {
170 action += ccolumns[i].name() +
171 DBType.getType(DbDataModel.dbTypes[i]) + notNull +
172 ", ";
173 }
174 action += ccolumns[ccolumns.length - 1].name() +
175 DBType.getType(DbDataModel.dbTypes[ccolumns.length - 1]) +
176 primaryKey + ")";
177 logger.warn(action);
178 DbRequest request = new DbRequest(session);
179 try {
180 request.query(action);
181 } catch (GoldenGateDatabaseNoConnectionException e) {
182 logger.warn("CreateTables Error", e);
183 return;
184 } catch (GoldenGateDatabaseSqlException e) {
185 logger.warn("CreateTables Error", e);
186 return;
187 } finally {
188 request.close();
189 }
190
191 action = "CREATE INDEX IDX_RUNNER ON "+ DbDataModel.table + "(";
192 DbDataModel.Columns[] icolumns = DbDataModel.indexes;
193 for (int i = 0; i < icolumns.length-1; i ++) {
194 action += icolumns[i].name()+ ", ";
195 }
196 action += icolumns[icolumns.length-1].name()+ ")";
197 logger.warn(action);
198 try {
199 request.query(action);
200 } catch (GoldenGateDatabaseNoConnectionException e) {
201 logger.warn("CreateTables Error", e);
202 return;
203 } catch (GoldenGateDatabaseSqlException e) {
204 return;
205 } finally {
206 request.close();
207 }
208
209
210 action = "CREATE SEQUENCE " + DbDataModel.fieldseq +
211 " MINVALUE " + (DbConstant.ILLEGALVALUE + 1);
212 logger.warn(action);
213 try {
214 request.query(action);
215 } catch (GoldenGateDatabaseNoConnectionException e) {
216 logger.warn("CreateTables Error", e);
217 return;
218 } catch (GoldenGateDatabaseSqlException e) {
219 logger.warn("CreateTables Error", e);
220 return;
221 } finally {
222 request.close();
223 }
224 }
225
226
227
228
229
230
231 @Override
232 public void resetSequence(DbSession session, long newvalue) throws GoldenGateDatabaseNoConnectionException {
233 String action = "ALTER SEQUENCE " + DbDataModel.fieldseq +
234 " RESTART WITH " + newvalue;
235 DbRequest request = new DbRequest(session);
236 try {
237 request.query(action);
238 } catch (GoldenGateDatabaseNoConnectionException e) {
239 logger.warn("ResetSequence Error", e);
240 return;
241 } catch (GoldenGateDatabaseSqlException e) {
242 logger.warn("ResetSequence Error", e);
243 return;
244 } finally {
245 request.close();
246 }
247 logger.warn(action);
248 }
249
250
251
252
253
254
255 @Override
256 public long nextSequence(DbSession dbSession)
257 throws GoldenGateDatabaseNoConnectionException,
258 GoldenGateDatabaseSqlException, GoldenGateDatabaseNoDataException {
259 long result = DbConstant.ILLEGALVALUE;
260 String action = "SELECT NEXTVAL('" + DbDataModel.fieldseq + "')";
261 DbPreparedStatement preparedStatement = new DbPreparedStatement(
262 dbSession);
263 try {
264 preparedStatement.createPrepareStatement(action);
265
266 preparedStatement.executeQuery();
267 if (preparedStatement.getNext()) {
268 try {
269 result = preparedStatement.getResultSet().getLong(1);
270 } catch (SQLException e) {
271 throw new GoldenGateDatabaseSqlException(e);
272 }
273 return result;
274 } else {
275 throw new GoldenGateDatabaseNoDataException(
276 "No sequence found. Must be initialized first");
277 }
278 } finally {
279 preparedStatement.realClose();
280 }
281 }
282
283
284
285
286
287 @Override
288 protected String validConnectionString() {
289 return "select 1";
290 }
291
292
293
294
295 @Override
296 public String limitRequest(String allfields, String request, int nb) {
297 return request+" LIMIT "+nb;
298 }
299
300 }