src/main/java/net/bionicmessage/objects/MultipleSourceObjectTracking.java

   1 /*
   2  * MultipleSourceObjectTracking.java
   3  *
   4  * Original file created on 26 August 2006, 13:24
   5  * Copyright (c) 2006-2008 Mathew McBride / "BionicMessage.net"
   6  * Permission is hereby granted, free of charge, to any person obtaining a
   7  * copy of this software and associated documentation files (the "Software"),
   8  * to deal in the Software without restriction, including without limitation
   9  * the rights to use, copy, modify, merge, publish, distribute, sublicense,
  10  * and/or sell copies of the Software, and to permit persons to whom the
  11  * Software is furnished to do so, subject to the following conditions:
  12  *
  13  * The above copyright notice and this permission notice shall be included in
  14  * all copies or substantial portions of the Software.
  15  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
  16  * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
  17  * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
  18  * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
  19  * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
  20  * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS
  21  * IN THE SOFTWARE.
  22  *
  23  */
  24 package net.bionicmessage.objects;
  25
  26 import java.io.File;
  27 import java.io.IOException;
  28 import java.sql.*;
  29 import java.util.ArrayList;
  30 import net.bionicmessage.extutils.Base64;
  31 import net.bionicmessage.utils.JDBCConnectionFactory;
  32
  33 /**
  34  *
  35  * @ObjMultipleSourceObjectTracking
  36  */
  37 public class MultipleSourceObjectTracking {
  38
  39     private String dbpath = "";
  40     private Connection conn = null;
  41     private String dburl = "";
  42     private static final String OBJLIST = "objlist";
  43     private static final String ETAGLIST = "etaglist";
  44     private static final String NAMELIST = "namelist";
  45     private static final String OBJECTS = "objects";
  46     private static final String URLLIST = "urllist";
  47     private static final String DATELIST = "datelist";
  48     private static final String NOTWANTLIST = "notwanted";
  49     private JDBCConnectionFactory jdbcFactory = null;
  50
  51     /** Creates a newObjMultipleSourceObjectTrackingjectTracking */
  52     public MultipleSourceObjectTracking(String dbpath) {
  53         this.dbpath = dbpath;
  54         dburl = "jdbc:smallsql:" + dbpath + "?create=true";
  55         jdbcFactory = JDBCConnectionFactory.getInstance();
  56     }
  57
  58     public void terminate() throws SQLException {
  59         conn.close();
  60         JDBCConnectionFactory.removeConnection(dburl);
  61     }
  62
  63     public void init() throws Exception {
  64         Class.forName("smallsql.database.SSDriver");
  65         //conn = java.sql.DriverManager.getConnection(dburl);
  66         if (jdbcFactory.doesConnectionExist(dburl)) {
  67             initClean();
  68         } else {
  69             conn = jdbcFactory.getConnection(dburl);
  70             if (conn == null) {
  71                 throw new Exception("Critical error: No SmallSQL connection. DBURL:" + dburl);
  72             }
  73             createTablesIfNeeded();
  74         }
  75     }
  76
  77     /** Function designed for Funambol slow syncs. If an errored instance exists
  78      * in the VM, close its connection, purge, and then make ours.
  79      * @throws java.sql.SQLException
  80      * @throws java.io.IOException
  81      */
  82     public void initClean() throws SQLException, IOException, ClassNotFoundException {
  83         Class.forName("smallsql.database.SSDriver");
  84         if (JDBCConnectionFactory.doesConnectionExist(dburl)) {
  85             conn = JDBCConnectionFactory.getConnection(dburl);
  86             terminate();
  87         }
  88         File dbDir = new File(dbpath);
  89         if (dbDir.exists()) {
  90             String[] files = dbDir.list();
  91             if (dbDir.exists() && files.length > 0) {
  92                 for (int i = 0; i < files.length; i++) {
  93                     String string = files[i];
  94                     File toDelete = new File(dbDir, string);
  95                     boolean deleted = toDelete.delete();
  96                     System.out.println("Delete " + toDelete.getAbsolutePath() + "..." + deleted);
  97                 }
  98             }
  99             boolean deleted = dbDir.delete();
 100             int one = 2;
 101         }
 102         conn = JDBCConnectionFactory.getConnection(dburl);
 103         if (conn == null) {
 104             throw new SQLException("Critical error: No SmallSQL connection. DBURL:" + dburl);
 105         }
 106         createTablesIfNeeded();
 107     }
 108
 109     public void createTablesIfNeeded() throws SQLException {
 110         boolean create = false;
 111         try {
 112             doesObjectExist("/dev/null");
 113         } catch (SQLException ex) {
 114             create = true;
 115         }
 116         if (create) {
 117             executeSQL(CREATE_ETAG_LIST);
 118             executeSQL(CREATE_NAME_LIST);
 119             executeSQL(CREATE_OBJECT);
 120             executeSQL(CREATE_OBJLIST);
 121             executeSQL(CREATE_URLLIST);
 122             executeSQL(CREATE_DATELIST);
 123             executeSQL(CREATE_NOTWANTED);
 124         }
 125     }
 126
 127     /**
 128      * Adds an object to the store
 129      * @param sourceName The server source for that object
 130      * @param uid The UID for the object
 131      * @param url The URL from which the server copy of the object can be
 132      * obtained from
 133      * @param etag The etag/revision stamp for the object
 134      * @param name The description/name for the object
 135      * @param contents The data of the object itself
 136      * @param dtstart The Unix time at which this object starts
 137      * @param dtend The Unix time at which this object ends
 138      * @throws java.sql.SQLException Thrown when the store encounters a problem adding an object to the database
 139      */
 140     public void createObject(String sourceName,
 141             String uid,
 142             String url,
 143             String etag,
 144             String name,
 145             String contents,
 146             long dtstart,
 147             long dtend) throws SQLException {
 148         String newuid = String.format("INSERT INTO %s (\"obj_source\", \"obj_uid\") VALUES ('%s','%s')",
 149                 OBJLIST, sourceName, uid);
 150         executeSQL(newuid);
 151         String newurl = String.format("INSERT INTO %s (\"obj_source\", \"obj_uid\",\"obj_url\") VALUES ('%s','%s','%s')",
 152                 URLLIST, sourceName, uid, url);
 153         executeSQL(newurl);
 154         String newetag = "INSERT INTO " + ETAGLIST + "(\"obj_uid\",\"obj_etag\") VALUES ('" + uid + "','" + etag + "')";
 155         executeSQL(newetag);
 156         name = name.replace("'", "\"");
 157         String newname = "INSERT INTO " + NAMELIST + "(\"obj_uid\",\"obj_name\") VALUES ('" + uid + "','" + name + "')";
 158         executeSQL(newname);
 159         String newobject = "INSERT INTO " + OBJECTS + "(\"obj_uid\",\"obj_contents\") VALUES (?,?)";
 160         PreparedStatement innewobject = conn.prepareStatement(newobject);
 161         innewobject.setString(1, uid);
 162         innewobject.setBytes(2, contents.getBytes());
 163         innewobject.execute();
 164         String insertdates = "INSERT INTO " + DATELIST + "(\"obj_uid\",\"obj_dtstart\",\"obj_dtend\") VALUES ('" + uid + "','" + dtstart + "','" + dtend + "')";
 165         executeSQL(insertdates);
 166     }
 167
 168     public void deleteObject(String uid) throws SQLException {
 169         String deleteuid = "DELETE FROM " + OBJLIST + " WHERE \"obj_uid\" = '" + uid + "'";
 170         executeSQL(deleteuid);
 171         String deleteetag = "DELETE FROM " + ETAGLIST + " WHERE \"obj_uid\" = '" + uid + "'";
 172         executeSQL(deleteetag);
 173         String deletename = "DELETE FROM " + NAMELIST + " WHERE \"obj_uid\" = '" + uid + "'";
 174         executeSQL(deletename);
 175         String deleteobject = "DELETE FROM " + OBJECTS + " WHERE \"obj_uid\" = '" + uid + "'";
 176         executeSQL(deleteobject);
 177         String deleteurl = "DELETE FROM " + URLLIST + " WHERE \"obj_uid\" = '" + uid + "'";
 178         executeSQL(deleteurl);
 179         String deletedate = "DELETE FROM " + DATELIST + " WHERE \"obj_uid\" = '" + uid + "'";
 180         executeSQL(deletedate);
 181     }
 182
 183     public boolean doesUidMatchTimes(String uid, long dstime, long detime) throws SQLException {
 184         String findDate = String.format("SELECT \"obj_uid\" FROM \"%s\" WHERE \"obj_uid\" = '%s' AND  \"obj_dtstart\" = %d AND \"obj_dtend\" = %d", DATELIST, uid, dstime, detime);
 185         String result = get1x1SQL(findDate);
 186         if (result != null) {
 187             return true;
 188         }
 189         return false;
 190     }
 191
 192     public String findObjectByURL(String url) throws SQLException {
 193         String findUid = "SELECT \"obj_uid\" FROM \"" + URLLIST + "\" WHERE \"obj_url\" = '" + url + "'";
 194         String result = get1x1SQL(findUid);
 195         return result;
 196     }
 197
 198     public String findObjectByName(String name) throws SQLException {
 199         name = name.replace("'", "\"");
 200         String findUid = "SELECT \"obj_uid\" FROM \"" + NAMELIST + "\" WHERE \"obj_name\" = '" + name + "'";
 201         String result = get1x1SQL(findUid);
 202         return result;
 203     }
 204
 205     public ArrayList findObjectsByName(String name) throws SQLException {
 206         ArrayList<String> results = new ArrayList();
 207         name = name.replace("'", "\"");
 208         String findUid = "SELECT \"obj_uid\" FROM \"" + NAMELIST + "\" WHERE \"obj_name\" = '" + name + "'";
 209         String result = get1x1SQL(findUid);
 210         Statement stmt = conn.createStatement();
 211         stmt.execute(findUid);
 212         ResultSet rs = stmt.getResultSet();
 213         while (rs.next()) {
 214             String id = rs.getString("obj_uid");
 215             results.add(id);
 216         }
 217         return results;
 218     }
 219
 220     public String getObjectContents(String uid) throws SQLException {
 221         String findObject = "SELECT \"obj_contents\" FROM \"" + OBJECTS + "\" WHERE \"obj_uid\" = '" + uid + "'";
 222         Statement stmt = conn.createStatement();
 223         stmt.execute(findObject);
 224         ResultSet rs = stmt.getResultSet();
 225         rs.next();
 226         if (rs.getRow() == 0) {
 227             return null;
 228         }
 229         String cn = new String(rs.getBytes("obj_contents"));
 230         return cn;
 231     }
 232
 233     public String getObjectEtag(String uid) throws SQLException {
 234         String findObject = "SELECT obj_etag FROM " + ETAGLIST + " WHERE obj_uid = '" + uid + "'";
 235         String result = get1x1SQL(findObject);
 236         ;
 237         return result;
 238     }
 239
 240     public String getObjectName(String uid) throws SQLException {
 241         String findObject = "SELECT \"obj_name\" FROM \"" + NAMELIST + "\" WHERE \"obj_uid\" = '" + uid + "'";
 242         String result = get1x1SQL(findObject);
 243         ;
 244         return result;
 245     }
 246
 247     public String getObjectURL(String uid) throws SQLException {
 248         String findObject = "SELECT \"obj_url\" FROM \"" + URLLIST + "\" WHERE \"obj_uid\" = '" + uid + "'";
 249         String result = get1x1SQL(findObject);
 250         return result;
 251     }
 252
 253     public String getObjectSource(String uid) throws SQLException {
 254         String findObject = String.format("SELECT \"obj_uid\" FROM %s WHERE \"obj_uid\" = '%s'", OBJLIST, uid);
 255         String result = get1x1SQL(findObject);
 256         return result;
 257     }
 258
 259     public void updateEtag(String uid, String etag) throws SQLException {
 260         String deleteetag = "DELETE FROM " + ETAGLIST + " WHERE \"obj_uid\" = '" + uid + "'";
 261         executeSQL(deleteetag);
 262         String newetag = "INSERT INTO " + ETAGLIST + "(\"obj_uid\",\"obj_etag\") VALUES ('" + uid + "','" + etag + "')";
 263         executeSQL(newetag);
 264     }
 265
 266     public void updateName(String uid, String name) throws SQLException {
 267         name = name.replace("'", "\"");
 268         String deletename = "DELETE FROM " + NAMELIST + " WHERE \"obj_uid\" = '" + uid + "'";
 269         executeSQL(deletename);
 270         String newname = "INSERT INTO " + NAMELIST + "(\"obj_uid\",\"obj_name\") VALUES ('" + uid + "','" + name + "')";
 271         executeSQL(newname);
 272     }
 273
 274     public void updateObject(String uid, String content) throws SQLException {
 275         String deleteobject = "DELETE FROM " + OBJECTS + " WHERE \"obj_uid\" = '" + uid + "'";
 276         executeSQL(deleteobject);
 277         String objectBase64 = Base64.encodeBytes(content.getBytes());
 278         String newobject = "INSERT INTO " + OBJECTS + "(\"obj_uid\",\"obj_contents\") VALUES (?,?)";
 279         PreparedStatement innewobject = conn.prepareStatement(newobject);
 280         innewobject.setString(1, uid);
 281         innewobject.setBytes(2, content.getBytes());
 282         boolean executed = innewobject.execute();
 283     }
 284
 285     public void updateDate(String uid, long dtstart, long dtend) throws SQLException {
 286         String deletedate = "DELETE FROM " + DATELIST + " WHERE \"obj_uid\" = '" + uid + "'";
 287         executeSQL(deletedate);
 288         String newdate = "INSERT INTO " + DATELIST + "(\"obj_uid\",\"obj_dtstart\",\"obj_dtend\") VALUES ('" + uid + "'," + dtstart + "," + dtend + ")";
 289         executeSQL(newdate);
 290     }
 291
 292     public boolean doesObjectExist(String uid) throws SQLException {
 293         String findObject = "SELECT \"obj_uid\" FROM \"" + OBJLIST + "\" WHERE \"obj_uid\" = '" + uid + "'";
 294         Statement stmt = conn.createStatement();
 295         stmt.execute(findObject);
 296         ResultSet rs = stmt.getResultSet();
 297         return rs.next();
 298     }
 299
 300     public boolean doesURLExist(String url) throws SQLException {
 301         String findObject = "SELECT \"obj_url\" FROM \"" + URLLIST + "\" WHERE \"obj_url\" = '" + url + "'";
 302         Statement stmt = conn.createStatement();
 303         stmt.execute(findObject);
 304         ResultSet rs = stmt.getResultSet();
 305         return rs.next();
 306     }
 307
 308     public boolean doesObjectApplyAtTime(String uid, int time) throws SQLException {
 309         String findObject = "SELECT \"obj_uid\" FROM \"" + DATELIST + "\" WHERE \"obj_uid\" = '" + uid + "' AND WHERE \"obj_dtstart\" <= " + time + " AND \"obj_dtend\" >= " + time;
 310         Statement stmt = conn.createStatement();
 311         stmt.execute(findObject);
 312         ResultSet rs = stmt.getResultSet();
 313         return rs.next();
 314     }
 315
 316     public long getDtStartForUid(String uid) throws SQLException {
 317         String findUid = "SELECT \"obj_dtstart\" FROM \"" + DATELIST + "\" WHERE \"obj_uid\" = '" + uid + "'";
 318         Statement stmt = conn.createStatement();
 319         stmt.execute(findUid);
 320         ResultSet rs = stmt.getResultSet();
 321         rs.next();
 322         return rs.getLong("obj_dtstart");
 323     }
 324
 325     public long getDtEndFromUid(String uid) throws SQLException {
 326         String findUid = "SELECT \"obj_dtend\" FROM \"" + DATELIST + "\" WHERE \"obj_uid\" = '" + uid + "'";
 327         Statement stmt = conn.createStatement();
 328         stmt.execute(findUid);
 329         ResultSet rs = stmt.getResultSet();
 330         rs.next();
 331         return rs.getLong("obj_dtend");
 332     }
 333
 334     public ArrayList getUIDList() throws SQLException {
 335         String uidList = "SELECT \"obj_uid\" FROM \"" + OBJLIST + "\"";
 336         Statement stmt = conn.createStatement();
 337         stmt.execute(uidList);
 338         ResultSet rs = stmt.getResultSet();
 339         ArrayList uidlist = new ArrayList();
 340         while (rs.next()) {
 341             String uid = rs.getString("obj_uid");
 342             uidlist.add(uid);
 343         }
 344         stmt = null;
 345         rs = null;
 346         return uidlist;
 347     }
 348
 349     public ArrayList getURLList() throws SQLException {
 350         String urlList = "SELECT \"obj_url\" FROM \"" + URLLIST + "\"";
 351         Statement stmt = conn.createStatement();
 352         stmt.execute(urlList);
 353         ResultSet rs = stmt.getResultSet();
 354         ArrayList urllist = new ArrayList();
 355         while (rs.next()) {
 356             String url = rs.getString("obj_url");
 357             urllist.add(url);
 358         }
 359         stmt = null;
 360         rs = null;
 361         return urllist;
 362     }
 363
 364     public ArrayList getURLListForSource(String sourceName) throws SQLException {
 365         String urlList = String.format("SELECT \"obj_url\" FROM %s  WHERE \"obj_source\" = '%s'", URLLIST, sourceName);
 366         Statement stmt = conn.createStatement();
 367         stmt.execute(urlList);
 368         ResultSet rs = stmt.getResultSet();
 369         ArrayList urllist = new ArrayList();
 370         while (rs.next()) {
 371             String url = rs.getString("obj_url");
 372             urllist.add(url);
 373         }
 374         stmt = null;
 375         rs = null;
 376         return urllist;
 377     }
 378
 379     public void addForgottenURL(String url, String etag) throws SQLException {
 380         String newurl = "INSERT INTO " + NOTWANTLIST + "(\"obj_url\",\"obj_etag\") VALUES ('" + url + "','" + etag + "')";
 381         executeSQL(newurl);
 382     }
 383
 384     public ArrayList getForgottenList() throws SQLException {
 385         String urlList = "SELECT \"obj_url\" FROM \"" + NOTWANTLIST + "\"";
 386         Statement stmt = conn.createStatement();
 387         stmt.execute(urlList);
 388         ResultSet rs = stmt.getResultSet();
 389         ArrayList urllist = new ArrayList();
 390         while (rs.next()) {
 391             String url = rs.getString("obj_url");
 392             urllist.add(url);
 393         }
 394         stmt = null;
 395         rs = null;
 396         return urllist;
 397     }
 398
 399     public void deleteForgottenURL(String url) throws SQLException {
 400         String deleteurl = "DELETE FROM " + NOTWANTLIST + " WHERE \"obj_url\" = '" + url + "'";
 401         executeSQL(deleteurl);
 402     }
 403
 404     public boolean isURLForgotten(String url) throws SQLException {
 405         String findObject = "SELECT \"obj_url\" FROM \"" + NOTWANTLIST + "\" WHERE \"obj_url\" = '" + url + "'";
 406         Statement stmt = conn.createStatement();
 407         stmt.execute(findObject);
 408         ResultSet rs = stmt.getResultSet();
 409         return rs.next();
 410     }
 411
 412     public String getEtagForForgottenURL(String url) throws SQLException {
 413         String findObject = "SELECT \"obj_etag\" FROM \"" + NOTWANTLIST + "\" WHERE \"obj_url\" = '" + url + "'";
 414         return get1x1SQL(findObject);
 415
 416     }
 417     /* Execute SQL statement which doesn't need ResultSet etc. */
 418
 419     private void executeSQL(String sql) throws SQLException {
 420         Statement stmt = conn.createStatement();
 421         stmt.execute(sql);
 422         stmt = null;
 423     }
 424
 425     /**
 426      * Return a result where there is only one row and one column
 427      * @param sql SQL to Execute
 428      * @throws java.sql.SQLException
 429      * @return Null if no rows returned
 430      */
 431     private String get1x1SQL(String sql) throws SQLException {
 432         Statement stmt = conn.createStatement();
 433         stmt.execute(sql);
 434         ResultSet rs = stmt.getResultSet();
 435         rs.next();
 436         if (rs.getRow() == 0) {
 437             return null;
 438         }
 439         return rs.getString(1);
 440     }
 441     public static final String CREATE_ETAG_LIST =
 442             "CREATE TABLE etaglist\n" +
 443             "(\n" +
 444             "obj_uid VARCHAR(255) not null,\n" +
 445             "obj_etag VARCHAR(255) not null\n" +
 446             ")";
 447     public static final String CREATE_NAME_LIST =
 448             "CREATE TABLE namelist(\n" +
 449             "obj_uid VARCHAR(255) not null,\n" +
 450             "obj_name VARCHAR(255) not null\n" +
 451             ")";
 452     public static final String CREATE_OBJECT =
 453             "CREATE TABLE objects(\n" +
 454             "obj_uid VARCHAR(255) not null,\n" +
 455             "obj_contents LONGVARBINARY\n" +
 456             ")";
 457     public static final String CREATE_OBJLIST =
 458             "CREATE TABLE objlist(\n" +
 459             "obj_source VARCHAR(255) not null," +
 460             "obj_uid VARCHAR(255) not null" +
 461             ")";
 462     public static final String CREATE_URLLIST =
 463             "CREATE TABLE urllist(\n" +
 464             "obj_source VARCHAR(255) not null," +
 465             "obj_uid VARCHAR(255) not null," +
 466             "obj_url VARCHAR(2048) not null" +
 467             ")";
 468     public static final String CREATE_DATELIST =
 469             "CREATE TABLE datelist(\n" +
 470             "obj_uid VARCHAR(255) not null," +
 471             "obj_dtstart INT not null," +
 472             "obj_dtend INT not null" +
 473             ")";
 474     public static final String CREATE_NOTWANTED =
 475             "CREATE TABLE notwanted(\n" +
 476             "obj_url VARCHAR(255) not null," +
 477             "obj_etag VARCHAR(255) not null" +
 478             ")";
 479 }