Source code: com/cybertivity/powerjournal/database/EntryManager.java
1 package com.cybertivity.powerjournal.database;
2 import java.io.*;
3 import java.sql.*;
4 import java.util.ArrayList;
5 import java.util.HashMap;
6 import java.util.Iterator;
7 import java.util.NoSuchElementException;
8 import java.util.TreeSet;
9 import org.hsqldb.jdbcDriver;
10 import org.hsqldb.util.*;
11 import com.cybertivity.powerjournal.*;
12
13 /**
14 * Title: PowerJournal
15 * Description: $Id: EntryManager.java,v 1.8 2001/12/27 20:27:47 arrowood Exp $
16 * Copyright: Copyright (c) 2001
17 * Company: <A HREF="http://www.cybertivity.com">Cybertivity</A>
18 *
19 * @author <A HREF="mailto:chris.arrowood@cybertivity.com">Chris Arrowood</A>
20 * @created November 18, 2001
21 * @version 1.0
22 */
23
24 public class EntryManager extends DBManager {
25
26 /**
27 */
28 public final static String V01_TABLE_NAME = "ENTRIES";
29 private static EntryManager instance = null;
30 private TreeSet entryIds = null;
31 private ConfigManager configManager = null;
32 private JournalManager journalManager = null;
33 private String tableName = null;
34 private boolean suppressPrivateEntries = true;
35
36
37 public EntryManager(String journalName, JournalManager journalManagerArg, boolean isGuest) throws DBException {
38 super(journalManagerArg.url, journalManagerArg.driver, journalManagerArg.user, journalManagerArg.password);
39 tableName = journalName;
40 journalManager = journalManagerArg;
41 createDatabase(journalManager.dbType, journalManager.DB_NAME);
42 if (!tableExists(tableName)) {
43 createEntriesTables();
44 }
45 suppressPrivateEntries = isGuest;
46 }
47
48
49 public void changeJournal(String journalName) throws DBException {
50 tableName = journalName;
51 createTableIfMissing();
52 }
53
54
55 public boolean oldDatabaseVersionExists() throws DBException {
56 return tableExists(V01_TABLE_NAME);
57 }
58
59
60 public void importFromPreviousVersion(boolean dropOldTable) throws DBException {
61 ArrayList entries = getEntriesFromPreviousVersion();
62 for (int i = 0; i < entries.size(); i++) {
63 Entry entry = (Entry) entries.get(i);
64 insertEntryToTable(entry);
65 }
66 if (dropOldTable) {
67 executeSQL("DROP TABLE " + V01_TABLE_NAME);
68 }
69 }
70
71
72 public static EntryManager getInstance(String journalName, JournalManager journalManagerArg, boolean isGuest, boolean forceNew) throws DBException {
73 if (forceNew && instance != null) {
74 instance = null;
75 }
76 if (instance == null) {
77 instance = new EntryManager(journalName, journalManagerArg, isGuest);
78 }
79 return instance;
80 }
81
82
83 public static EntryManager getInstance(String journalName, JournalManager journalManagerArg, boolean isGuest) throws DBException {
84 return getInstance(journalName, journalManagerArg, isGuest, false);
85 }
86
87
88 public void insertEntryToTable(Entry entry) throws DBException {
89 String sql = "insert into " + tableName + " values ( ?, ?, ?, ?, ?)";
90 int isPrivate = 1;
91 //mysqql does not have bits
92 if (entry.getEntryPrivate()) {
93 isPrivate = 1;
94 }else{
95 isPrivate = 0;
96 }
97 try {
98 conn = getConnection();
99 statement = conn.createStatement();
100 PreparedStatement p = conn.prepareStatement(sql);
101 p.clearParameters();
102 p.setNull(1, Types.INTEGER);
103 p.setTimestamp(2, entry.getEntryDate());
104 p.setInt(3, isPrivate);
105 p.setString(5, entry.getEntryContents());
106 p.setString(4, entry.getEntryDescription());
107 p.executeUpdate();
108 statement.close();
109 cacheEntryIds(true);
110 } catch (SQLException e) {
111 throw new DBException("SQLException: " + e.getMessage());
112 }
113 }
114
115
116 private void cacheEntryIds(boolean force) throws DBException {
117 if (entryIds == null || force) {
118 try {
119 entryIds = new TreeSet();
120 conn = getConnection();
121 statement = conn.createStatement();
122 if (suppressPrivateEntries) {
123 //public only
124 resultSet = statement.executeQuery("select id from " + tableName + " where private='0'");
125 }
126 else {
127 resultSet = statement.executeQuery("select id from " + tableName + " ");
128 }
129 resultSet.setFetchSize(1000);
130 boolean more = resultSet.next();
131 while (more) {
132 entryIds.add(new Integer(resultSet.getInt("id")));
133 more = resultSet.next();
134 }
135 statement.close();
136 } catch (SQLException e) {
137 throw new DBException("SQLException: " + e.getMessage());
138 }
139 }
140 }
141
142
143 private void cacheEntryIds() throws DBException {
144 cacheEntryIds(false);
145 }
146
147
148 public ArrayList getEntries() throws DBException {
149 ArrayList entries = new ArrayList();
150 cacheEntryIds(true);
151 Iterator iter = entryIds.iterator();
152 while (iter.hasNext()) {
153 Integer nextID = (Integer) iter.next();
154 Entry entry = getEntryById("" + nextID.intValue());
155 entries.add(entry);
156 }
157 return entries;
158 }
159
160
161 private ArrayList getEntriesFromPreviousVersion() throws DBException {
162 ArrayList entries = new ArrayList();
163 try {
164 entryIds = new TreeSet();
165 conn = getConnection();
166 statement = conn.createStatement();
167 resultSet = statement.executeQuery("select * from " + V01_TABLE_NAME + " order by date ASC");
168 resultSet.setFetchSize(1000);
169 boolean more = resultSet.next();
170 while (more) {
171 entries.add(createEntryFromCurrentRowFromPreviousVersion());
172 more = resultSet.next();
173 }
174 statement.close();
175 } catch (SQLException e) {
176 throw new DBException("SQLException: " + e.getMessage());
177 }
178 return entries;
179 }
180
181
182 private Entry getEntryFromQuery(String query) throws DBException {
183 Entry returnEntry = null;
184 try {
185 conn = getConnection();
186 statement = conn.createStatement();
187 resultSet = statement.executeQuery(query);
188 resultSet.setFetchSize(1);
189 boolean more = resultSet.next();
190 if (more) {
191 returnEntry = createEntryFromCurrentRow();
192 }
193 statement.close();
194 } catch (SQLException e) {
195 throw new DBException("SQLException: " + e.getMessage());
196 }
197 return returnEntry;
198 }
199
200
201 public Entry moveToFirstEntry() throws DBException {
202 cacheEntryIds();
203 if (!entryIds.isEmpty()) {
204 Integer first = (Integer) entryIds.first();
205 return getEntryFromQuery("select * from " + tableName
206 + " where id = '" + first.intValue() + "' ");
207 } else {
208 return null;
209 }
210 }
211
212
213 public Entry moveToLastEntry() throws DBException {
214 cacheEntryIds();
215 if (!entryIds.isEmpty()) {
216 Integer last = (Integer) entryIds.last();
217 return getEntryFromQuery("select * from " + tableName
218 + " where id = '" + last.intValue() + "' ");
219 } else {
220 return null;
221 }
222 }
223
224
225 public Entry moveToNextEntry(String currentEntryID) throws DBException {
226 cacheEntryIds();
227 Integer next = null;
228 boolean found = false;
229 if (!entryIds.isEmpty()) {
230 try {
231 if (currentEntryID == SingleEntryView.NEW_ENTRY_NUMBER_LABEL) {
232 return moveToLastEntry();
233 }
234 Iterator iter = entryIds.iterator();
235 while (iter.hasNext() && !found) {
236 Integer test = (Integer) iter.next();
237 if (test.intValue() == Integer.parseInt(currentEntryID)) {
238 next = (Integer) iter.next();
239 found = true;
240 }
241 }
242 } catch (NumberFormatException e) {
243 //do nothing; handled by boolean "found"
244 } catch (NoSuchElementException e) {
245 //do nothing; handled by boolean "found"
246 }
247 }
248 if (found) {
249 return getEntryFromQuery("select * from " + tableName
250 + " where id = '" + next.intValue() + "' ");
251 } else {
252 return moveToLastEntry();
253 }
254 }
255
256
257 public void deleteEntry(String currentEntryID) throws DBException {
258 cacheEntryIds();
259 if (currentEntryID != tableName) {
260 executeSQL("delete from " + tableName + " where id = '" + currentEntryID + "'");
261 }
262 cacheEntryIds(true);
263 }
264
265
266 public Entry getEntryById(String currentEntryID) throws DBException {
267 if (currentEntryID==null || currentEntryID.length()<1) {
268 return null;
269 }
270 Entry entry = getEntryFromQuery("select * from " + tableName
271 + " where id = '" + currentEntryID + "' ");
272 if (entry!=null && !entry.getEntryPrivate()) {
273 //not private
274 return entry;
275 }
276 else if (entry!=null && entry.getEntryPrivate() && !suppressPrivateEntries) {
277 //is private but this is the owner not a guest
278 return entry;
279 }
280 else {
281 //either null or a private entry requested by a guest
282 return null;
283 }
284
285 }
286
287
288 public Entry moveToPreviousEntry(String currentEntryID) throws DBException {
289 cacheEntryIds();
290 boolean found = false;
291 Integer prev = null;
292 if (!entryIds.isEmpty()) {
293 try {
294 if (currentEntryID == SingleEntryView.NEW_ENTRY_NUMBER_LABEL) {
295 return moveToLastEntry();
296 }
297 Iterator iter = entryIds.iterator();
298 while (iter.hasNext() && !found) {
299 Integer test = (Integer) iter.next();
300 if (test.intValue() == Integer.parseInt(currentEntryID)) {
301 found = true;
302 } else {
303 prev = test;
304 }
305 }
306 } catch (NumberFormatException e) {
307 //do nothing; handled by boolean "found"
308 } catch (NoSuchElementException e) {
309 //do nothing; handled by boolean "found"
310 } catch (NullPointerException e) {
311 //do nothing; handled by boolean "found"
312 }
313 }
314 if (found && (prev != null)) {
315 return getEntryFromQuery("select * from " + tableName
316 + " where id = '" + prev.intValue() + "' ");
317 } else {
318 return moveToFirstEntry();
319 }
320 }
321
322
323 public void updateCurrentEntry(Entry entry, int idNumber) throws DBException {
324 String privateSQLString = null;
325 //mysqql does not have bits
326 if (entry.getEntryPrivate()) {
327 privateSQLString = "private = '1' ";
328 }else{
329 privateSQLString = "private = '0' ";
330 }
331
332 String sql = "update " + tableName + " set "
333 + "description = ?, "
334 + "contents = ?, "
335 + "date = ?, "
336 + privateSQLString
337 + "WHERE id = ? ";
338 try {
339 conn = getConnection();
340 statement = conn.createStatement();
341 PreparedStatement p = conn.prepareStatement(sql);
342 p.clearParameters();
343 p.setString(1,entry.getEntryDescription());
344 p.setString(2,entry.getEntryContents());
345 p.setTimestamp(3, entry.getEntryDate());
346 p.setInt(4, (new Integer(idNumber)).intValue());
347 p.executeUpdate();
348 statement.close();
349 } catch (SQLException e) {
350 throw new DBException("SQLException: " + e.getMessage());
351 }
352 }
353
354
355 private Entry createEntryFromCurrentRow() throws DBException {
356 Entry returnEntry = new Entry("", "");
357 try {
358 boolean isPrivate = true;
359 if (resultSet.getInt("private")==0) {
360 isPrivate = false;
361 }
362 returnEntry.setEntryDate(resultSet.getTimestamp("date"));
363 returnEntry.setEntryDescription(resultSet.getString("description"));
364 returnEntry.setEntryNumber(resultSet.getInt("id"));
365 returnEntry.setEntryPrivate(isPrivate);
366 returnEntry.setEntryContents(resultSet.getString("contents"));
367 returnEntry.setJournalName(tableName);
368 returnEntry.setEntryPositionMemo(getPosition(resultSet.getInt("id")) + " of " + entryIds.size());
369 } catch (SQLException e) {
370 throw new DBException("SQLException: " + e.getMessage());
371 }
372 return returnEntry;
373 }
374 private Entry createEntryFromCurrentRowFromPreviousVersion() throws DBException {
375 Entry returnEntry = new Entry("", "");
376 try {
377 returnEntry.setEntryDate(resultSet.getTimestamp("date"));
378 returnEntry.setEntryDescription(resultSet.getString("description"));
379 returnEntry.setEntryNumber(resultSet.getInt("id"));
380 returnEntry.setEntryPrivate(resultSet.getBoolean("private"));
381 returnEntry.setEntryContents(resultSet.getString("contents"));
382 returnEntry.setJournalName(tableName);
383 returnEntry.setEntryPositionMemo(getPosition(resultSet.getInt("id")) + " of " + entryIds.size());
384 } catch (SQLException e) {
385 throw new DBException("SQLException: " + e.getMessage());
386 }
387 return returnEntry;
388 }
389
390 private int getPosition(int id) throws DBException {
391 cacheEntryIds();
392 int count = 0;
393 if (!entryIds.isEmpty()) {
394 try {
395 Iterator iter = entryIds.iterator();
396 while (iter.hasNext()) {
397 Integer test = (Integer) iter.next();
398 count++;
399 if (test.intValue() == id) {
400 return count;
401 }
402 }
403 } catch (NumberFormatException e) {
404 return 0;
405 } catch (NoSuchElementException e) {
406 return 0;
407 } catch (NullPointerException e) {
408 return 0;
409 }
410 } else {
411 return 0;
412 }
413 return count;
414 }
415
416
417 public void createEntriesTables() throws DBException {
418 if (journalManager.dbType.equals(ConfigManager.VALUE_DB_VERSION_HSQL)) {
419 executeSQL("CREATE TABLE " + tableName + " (id INT NOT NULL IDENTITY, date DATETIME NOT NULL, private INT NOT NULL, description LONGVARCHAR, contents LONGVARCHAR)");
420 } else if (journalManager.dbType.equals(ConfigManager.VALUE_DB_VERSION_MYSQL)) {
421 executeSQL("CREATE TABLE " + tableName + " (id INT UNSIGNED NOT NULL AUTO_INCREMENT, date DATETIME NOT NULL, private TINYINT NOT NULL, description TEXT, contents TEXT, PRIMARY KEY(id), UNIQUE(id), INDEX(id)) ");
422 }
423 }
424
425
426 public int getEntryCount() throws DBException {
427 return getRowCount(tableName);
428 }
429
430
431 private void createTableIfMissing() throws DBException {
432 if (!tableExists(tableName)) {
433 createEntriesTables();
434 }
435 }
436
437
438 public void prepareDatabase() throws DBException {
439 createTableIfMissing();
440 if (getEntryCount() == 0) {
441 createExampleEntry();
442 }
443 }
444
445
446 private void createExampleEntry() throws DBException {
447 String desc = "Welcome To " + MainController.APP_NAME + "!";
448 String contents = ""
449 + "Thank you for trying " + MainController.APP_NAME + " and supporting open "
450 + "source software.\n\nThis sample entry contains tips to help "
451 + "you get started quickly. It is safe to delete this entry.\n\n"
452 + "In " + MainController.APP_NAME + ", your entries are automagically saved for you "
453 + "anytime you leave the entry you are currently viewing. There "
454 + "is no need need for the user to manually save their work since "
455 + "it is all done for you.\n\nYou move between the entries in your "
456 + "journal by using one of the buttons at the bottom labeled \"<<\", "
457 + "\"<\", \">\", or \">>\". They mean \"First\", \"Previous\", "
458 + "\"Next\" and \"Last\" respectively. This also where you find "
459 + "the buttons used to create a new entry and to delete the entry "
460 + "that is currently being displayed.\n\nEnjoy!";
461 Entry entry = new Entry(desc, contents);
462 insertEntryToTable(entry);
463 }
464 }