src/net/bionicmessage/objects/MultipleSourceObjectTracking.java

   1 /*
   2  * MultipleSourceObjectTracking.java
   3  *
   4  * Original file created on 26 August 2006, 13:24
   5  * Copyright (c) 2006-2007 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 import java.sql.*;
  26 import java.util.ArrayList;
  27 import net.bionicmessage.extutils.Base64;
  28 /**
  29  *
  30  * @ObjMultipleSourceObjectTracking
  31  */
  32 public class MultipleSourceObjectTracking {
  33     private String dbpath = "";
  34     private Connection conn = null;
  35     private String dburl = "";
  36     private static final String OBJLIST = "objlist";
  37     private static final String ETAGLIST = "etaglist";
  38     private static final String NAMELIST = "namelist";
  39     private static final String OBJECTS = "objects";
  40     private static final String URLLIST = "urllist";
  41     private static final String DATELIST = "datelist";
  42     private static final String NOTWANTLIST = "notwanted";
  43     /** Creates a newObjMultipleSourceObjectTrackingjectTracking */
  44     public MultipleSourceObjectTracking(String dbpath) {
  45         this.dbpath = dbpath;
  46         dburl = "jdbc:smallsql:"+dbpath+"?create=true";
  47     }
  48     public void terminate() throws SQLException {
  49         conn.close();
  50     }
  51     public void init() throws Exception {
  52         Class.forName( "smallsql.database.SSDriver" );
  53         conn = java.sql.DriverManager.getConnection(dburl);
  54         createTablesIfNeeded();
  55     }
  56     public void createTablesIfNeeded() throws SQLException {
  57         boolean create = false;
  58         try {
  59             doesObjectExist("/dev/null");
  60         } catch (SQLException ex) {
  61             create = true;
  62         }
  63         if (create) {
  64             executeSQL(CREATE_ETAG_LIST);
  65             executeSQL(CREATE_NAME_LIST);
  66             executeSQL(CREATE_OBJECT);
  67             executeSQL(CREATE_OBJLIST);
  68             executeSQL(CREATE_URLLIST);
  69             executeSQL(CREATE_DATELIST);
  70             executeSQL(CREATE_NOTWANTED);
  71         }
  72     }
  73
  74     /**
  75      * Adds an object to the store
  76      * @param sourceName The server source for that object
  77      * @param uid The UID for the object
  78      * @param url The URL from which the server copy of the object can be
  79      * obtained from
  80      * @param etag The etag/revision stamp for the object
  81      * @param name The description/name for the object
  82      * @param contents The data of the object itself
  83      * @param dtstart The Unix time at which this object starts
  84      * @param dtend The Unix time at which this object ends
  85      * @throws java.sql.SQLException Thrown when the store encounters a problem adding an object to the database
  86      */
  87     public void createObject(String sourceName,
  88             String uid,
  89             String url,
  90             String etag,
  91             String name,
  92             String contents,
  93             long dtstart,
  94             long dtend) throws SQLException {
  95         String newuid = String.format("INSERT INTO %s (\"obj_source\", \"obj_uid\") VALUES ('%s','%s')",
  96                 OBJLIST, sourceName, uid);
  97         executeSQL(newuid);
  98         String newurl = String.format("INSERT INTO %s (\"obj_source\", \"obj_uid\",\"obj_url\") VALUES ('%s','%s','%s')",
  99                 URLLIST, sourceName, uid, url);
 100         executeSQL(newurl);
 101         String newetag = "INSERT INTO " + ETAGLIST + "(\"obj_uid\",\"obj_etag\") VALUES ('"+uid+"','"+etag+"')";
 102         executeSQL(newetag);
 103         name = name.replace("'","\"");
 104         String newname = "INSERT INTO " + NAMELIST + "(\"obj_uid\",\"obj_name\") VALUES ('"+uid+"','"+name+"')";
 105         executeSQL(newname);
 106         String newobject = "INSERT INTO " + OBJECTS + "(\"obj_uid\",\"obj_contents\") VALUES (?,?)";
 107         PreparedStatement innewobject = conn.prepareStatement(newobject);
 108         innewobject.setString(1, uid);
 109         innewobject.setBytes(2, contents.getBytes());
 110         innewobject.execute();
 111         String insertdates = "INSERT INTO " + DATELIST + "(\"obj_uid\",\"obj_dtstart\",\"obj_dtend\") VALUES ('"+uid+"','"+dtstart+"','"+dtend+"')";
 112         executeSQL(insertdates);
 113     }
 114     public void deleteObject(String uid) throws SQLException {
 115         String deleteuid = "DELETE FROM " + OBJLIST + " WHERE \"obj_uid\" = '"+uid+"'";
 116         executeSQL(deleteuid);
 117         String deleteetag = "DELETE FROM " + ETAGLIST + " WHERE \"obj_uid\" = '"+uid+"'";
 118         executeSQL(deleteetag);
 119         String deletename = "DELETE FROM " + NAMELIST + " WHERE \"obj_uid\" = '"+uid+"'";
 120         executeSQL(deletename);
 121         String deleteobject= "DELETE FROM " + OBJECTS + " WHERE \"obj_uid\" = '"+uid+"'";
 122         executeSQL(deleteobject);
 123         String deleteurl = "DELETE FROM " + URLLIST  + " WHERE \"obj_uid\" = '"+uid+"'";
 124         executeSQL(deleteurl);
 125         String deletedate = "DELETE FROM " + DATELIST + " WHERE \"obj_uid\" = '"+uid+"'";
 126         executeSQL(deletedate);
 127     }
 128     public boolean doesUidMatchTimes(String uid, long dstime, long detime) throws SQLException {
 129         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 );
 130         String result = get1x1SQL(findDate);
 131         if (result != null)
 132             return true;
 133         return false;
 134     }
 135     public String findObjectByURL(String url) throws SQLException {
 136         String findUid = "SELECT \"obj_uid\" FROM \"" + URLLIST + "\" WHERE \"obj_url\" = '" + url +"'";
 137         String result = get1x1SQL(findUid);
 138         return result;
 139     }
 140     public String findObjectByName(String name) throws SQLException {
 141         name = name.replace("'","\"");
 142         String findUid = "SELECT \"obj_uid\" FROM \"" + NAMELIST + "\" WHERE \"obj_name\" = '" + name +"'";
 143         String result = get1x1SQL(findUid);
 144         return result;
 145     }
 146     public ArrayList findObjectsByName(String name) throws SQLException {
 147         ArrayList<String> results = new ArrayList();
 148         name = name.replace("'","\"");
 149         String findUid = "SELECT \"obj_uid\" FROM \"" + NAMELIST + "\" WHERE \"obj_name\" = '" + name +"'";
 150         String result = get1x1SQL(findUid);
 151         Statement stmt = conn.createStatement();
 152         stmt.execute(findUid);
 153         ResultSet rs = stmt.getResultSet();
 154         while(rs.next()) {
 155             String id = rs.getString("obj_uid");
 156             results.add(id);
 157         }
 158         return results;
 159     }
 160     public String getObjectContents(String uid) throws SQLException {
 161         String findObject = "SELECT \"obj_contents\" FROM \"" + OBJECTS + "\" WHERE \"obj_uid\" = '"+uid+"'";
 162         Statement stmt = conn.createStatement();
 163         stmt.execute(findObject);
 164         ResultSet rs = stmt.getResultSet();
 165         rs.next();
 166         if (rs.getRow() == 0) {
 167             return null;
 168         }
 169         String cn = new String(rs.getBytes("obj_contents"));
 170         return cn;
 171     }
 172     public String getObjectEtag(String uid) throws SQLException {
 173         String findObject = "SELECT obj_etag FROM "+ ETAGLIST + " WHERE obj_uid = '"+uid+"'";
 174         String result = get1x1SQL(findObject);;
 175         return result;
 176     }
 177     public String getObjectName(String uid) throws SQLException {
 178         String findObject = "SELECT \"obj_name\" FROM \"" + NAMELIST + "\" WHERE \"obj_uid\" = '"+uid+"'";
 179         String result = get1x1SQL(findObject);;
 180         return result;
 181     }
 182     public String getObjectURL(String uid) throws SQLException {
 183         String findObject = "SELECT \"obj_url\" FROM \"" + URLLIST + "\" WHERE \"obj_uid\" = '"+uid+"'";
 184         String result = get1x1SQL(findObject);
 185         return result;
 186     }
 187     public String getObjectSource(String uid) throws SQLException {
 188         String findObject = String.format("SELECT \"obj_uid\" FROM %s WHERE \"obj_uid\" = '%s'", OBJLIST, uid);
 189         String result = get1x1SQL(findObject);
 190         return result;
 191     }
 192     public void updateEtag(String uid, String etag) throws SQLException {
 193         String deleteetag = "DELETE FROM " + ETAGLIST + " WHERE \"obj_uid\" = '"+uid+"'";
 194         executeSQL(deleteetag);
 195         String newetag = "INSERT INTO " + ETAGLIST + "(\"obj_uid\",\"obj_etag\") VALUES ('"+uid+"','"+etag+"')";
 196         executeSQL(newetag);
 197     }
 198     public void updateName(String uid, String name) throws SQLException {
 199         name = name.replace("'","\"");
 200         String deletename = "DELETE FROM " + NAMELIST + " WHERE \"obj_uid\" = '"+uid+"'";
 201         executeSQL(deletename);
 202         String newname = "INSERT INTO " + NAMELIST + "(\"obj_uid\",\"obj_name\") VALUES ('"+uid+"','"+name+"')";
 203         executeSQL(newname);
 204     }
 205     public void updateObject(String uid, String content) throws SQLException {
 206         String deleteobject= "DELETE FROM " + OBJECTS + " WHERE \"obj_uid\" = '"+uid+"'";
 207         executeSQL(deleteobject);
 208         String objectBase64 = Base64.encodeBytes(content.getBytes());
 209         String newobject = "INSERT INTO " + OBJECTS + "(\"obj_uid\",\"obj_contents\") VALUES (?,?)";
 210         PreparedStatement innewobject = conn.prepareStatement(newobject);
 211         innewobject.setString(1, uid);
 212         innewobject.setBytes(2, content.getBytes());
 213         boolean executed = innewobject.execute();
 214     }
 215     public void updateDate(String uid, long dtstart, long dtend) throws SQLException {
 216         String deletedate = "DELETE FROM " + DATELIST + " WHERE \"obj_uid\" = '"+uid+"'";
 217         executeSQL(deletedate);
 218         String newdate = "INSERT INTO " + DATELIST + "(\"obj_uid\",\"obj_dtstart\",\"obj_dtend\") VALUES ('"+uid+"',"+dtstart+","+dtend+")";
 219         executeSQL(newdate);
 220     }
 221     public boolean doesObjectExist(String uid) throws SQLException {
 222         String findObject = "SELECT \"obj_uid\" FROM \"" + OBJLIST + "\" WHERE \"obj_uid\" = '"+uid+"'";
 223         Statement stmt = conn.createStatement();
 224         stmt.execute(findObject);
 225         ResultSet rs = stmt.getResultSet();
 226         return rs.next();
 227     }
 228     public boolean doesURLExist(String url) throws SQLException {
 229         String findObject = "SELECT \"obj_url\" FROM \"" + URLLIST + "\" WHERE \"obj_url\" = '"+url+"'";
 230         Statement stmt = conn.createStatement();
 231         stmt.execute(findObject);
 232         ResultSet rs = stmt.getResultSet();
 233         return rs.next();
 234     }
 235     public boolean doesObjectApplyAtTime(String uid, int time) throws SQLException {
 236         String findObject = "SELECT \"obj_uid\" FROM \"" + DATELIST + "\" WHERE \"obj_uid\" = '"+ uid +"' AND WHERE \"obj_dtstart\" <= " + time + " AND \"obj_dtend\" >= " + time;
 237         Statement stmt = conn.createStatement();
 238         stmt.execute(findObject);
 239         ResultSet rs = stmt.getResultSet();
 240         return rs.next();
 241     }
 242     public long getDtStartForUid(String uid) throws SQLException {
 243         String findUid = "SELECT \"obj_dtstart\" FROM \""+ DATELIST + "\" WHERE \"obj_uid\" = '" + uid +"'";
 244         Statement stmt = conn.createStatement();
 245         stmt.execute(findUid);
 246         ResultSet rs = stmt.getResultSet();
 247         rs.next();
 248         return rs.getLong("obj_dtstart");
 249     }
 250     public long getDtEndFromUid(String uid) throws SQLException {
 251         String findUid = "SELECT \"obj_dtend\" FROM \""+ DATELIST + "\" WHERE \"obj_uid\" = '" + uid +"'";
 252         Statement stmt = conn.createStatement();
 253         stmt.execute(findUid);
 254         ResultSet rs = stmt.getResultSet();
 255         rs.next();
 256         return rs.getLong("obj_dtend");
 257     }
 258     public ArrayList getUIDList() throws SQLException {
 259         String uidList = "SELECT \"obj_uid\" FROM \"" + OBJLIST + "\"";
 260         Statement stmt = conn.createStatement();
 261         stmt.execute(uidList);
 262         ResultSet rs = stmt.getResultSet();
 263         ArrayList uidlist = new ArrayList();
 264         while(rs.next()) {
 265             String uid = rs.getString("obj_uid");
 266             uidlist.add(uid);
 267         }
 268         stmt = null;
 269         rs = null;
 270         return uidlist;
 271     }
 272     public ArrayList getURLList() throws SQLException {
 273         String urlList = "SELECT \"obj_url\" FROM \"" + URLLIST+ "\"";
 274         Statement stmt = conn.createStatement();
 275         stmt.execute(urlList);
 276         ResultSet rs = stmt.getResultSet();
 277         ArrayList urllist = new ArrayList();
 278         while(rs.next()) {
 279             String url = rs.getString("obj_url");
 280             urllist.add(url);
 281         }
 282         stmt = null;
 283         rs = null;
 284         return urllist;
 285     }
 286     public ArrayList getURLListForSource(String sourceName) throws SQLException {
 287         String urlList = String.format("SELECT \"obj_url\" FROM %s  WHERE \"obj_source\" = '%s'",URLLIST, sourceName);
 288         Statement stmt = conn.createStatement();
 289         stmt.execute(urlList);
 290         ResultSet rs = stmt.getResultSet();
 291         ArrayList urllist = new ArrayList();
 292         while(rs.next()) {
 293             String url = rs.getString("obj_url");
 294             urllist.add(url);
 295         }
 296         stmt = null;
 297         rs = null;
 298         return urllist;
 299     }
 300
 301     public void addForgottenURL(String url,String etag) throws SQLException {
 302         String newurl = "INSERT INTO " + NOTWANTLIST + "(\"obj_url\",\"obj_etag\") VALUES ('"+url+"','"+etag+"')";
 303         executeSQL(newurl);
 304     }
 305     public ArrayList getForgottenList() throws SQLException {
 306         String urlList = "SELECT \"obj_url\" FROM \"" + NOTWANTLIST+ "\"";
 307         Statement stmt = conn.createStatement();
 308         stmt.execute(urlList);
 309         ResultSet rs = stmt.getResultSet();
 310         ArrayList urllist = new ArrayList();
 311         while(rs.next()) {
 312             String url = rs.getString("obj_url");
 313             urllist.add(url);
 314         }
 315         stmt = null;
 316         rs = null;
 317         return urllist;
 318     }
 319     public void deleteForgottenURL(String url) throws SQLException {
 320         String deleteurl = "DELETE FROM " + NOTWANTLIST  + " WHERE \"obj_url\" = '"+url+"'";
 321         executeSQL(deleteurl);
 322     }
 323     public boolean isURLForgotten(String url) throws SQLException {
 324         String findObject = "SELECT \"obj_url\" FROM \"" + NOTWANTLIST + "\" WHERE \"obj_url\" = '"+url+"'";
 325         Statement stmt = conn.createStatement();
 326         stmt.execute(findObject);
 327         ResultSet rs = stmt.getResultSet();
 328         return rs.next();
 329     }
 330     public String getEtagForForgottenURL(String url) throws SQLException {
 331         String findObject = "SELECT \"obj_etag\" FROM \"" + NOTWANTLIST + "\" WHERE \"obj_url\" = '"+url+"'";
 332         return get1x1SQL(findObject);
 333
 334     }
 335     /* Execute SQL statement which doesn't need ResultSet etc. */
 336     private void executeSQL(String sql) throws SQLException {
 337         Statement stmt = conn.createStatement();
 338         stmt.execute(sql);
 339         stmt = null;
 340     }
 341     /**
 342      * Return a result where there is only one row and one column
 343      * @param sql SQL to Execute
 344      * @throws java.sql.SQLException
 345      * @return Null if no rows returned
 346      */
 347     private String get1x1SQL(String sql) throws SQLException {
 348         Statement stmt = conn.createStatement();
 349         stmt.execute(sql);
 350         ResultSet rs = stmt.getResultSet();
 351         rs.next();
 352         if (rs.getRow() == 0) {
 353             return null;
 354         }
 355         return rs.getString(1);
 356     }
 357     public static void main(String args[]) {
 358         ObjectTracking obtrack = new ObjectTracking("C:\\trackstore");
 359         try {
 360             obtrack.init();
 361             obtrack.deleteObject("testobject1");
 362             obtrack.createObject("testobject1","http://server/groupdav/object","2222","Test event","BEGIN:VCALENDAR\r\nEND:VCALENDAR",1161250627,1161250646);
 363             System.out.println(obtrack.doesObjectExist("testobject1"));
 364             System.out.println(obtrack.doesObjectExist("nonexistent"));
 365             ArrayList uids = obtrack.getUIDList();
 366             for (int i = 0; i < uids.size(); i++) {
 367                 String uid = (String)uids.get(i);
 368                 String url = obtrack.getObjectURL(uid);
 369                 String etag = obtrack.getObjectEtag(uid);
 370                 String name = obtrack.getObjectName(uid);
 371                 String data = obtrack.getObjectContents(uid);
 372                 long dtstart = obtrack.getDtStartForUid(uid);
 373                 long dtend = obtrack.getDtEndFromUid(uid);
 374                 System.out.println("----------");
 375                 System.out.println("UID:"+uid);
 376                 System.out.println("URL:"+url);
 377                 System.out.println("ETAG:"+etag);
 378                 System.out.println("NAME:"+name);
 379                 System.out.println("DTSTART:"+dtstart);
 380                 System.out.println("DTEND:"+dtend);
 381                 System.out.println("DATA FOLLOWS:");
 382                 System.out.println(data);
 383                 System.out.println("----------");
 384             }
 385             obtrack.updateEtag("testobject1","4444");
 386             System.out.println("New etag: "+obtrack.getObjectEtag("testobject1"));
 387             obtrack.updateObject("testobject1","BEGIN:VCALENDAR\r\nBEGIN:VEVENT\r\nEND:VEVENT\r\nBEGIN:VCALENDAR");
 388             System.out.println(obtrack.getObjectContents("testobject1"));
 389             obtrack.deleteObject("testobject1");
 390         } catch (Exception ex) {
 391             ex.printStackTrace();
 392         }
 393     }
 394     public static final String CREATE_ETAG_LIST =
 395             "CREATE TABLE etaglist\n" +
 396             "(\n" +
 397             "obj_uid VARCHAR(255) not null,\n" +
 398             "obj_etag VARCHAR(255) not null\n" +
 399             ")";
 400     public static final String CREATE_NAME_LIST =
 401             "CREATE TABLE namelist(\n"+
 402             "obj_uid VARCHAR(255) not null,\n"+
 403             "obj_name VARCHAR(255) not null\n"+
 404             ")";
 405     public static final String CREATE_OBJECT =
 406             "CREATE TABLE objects(\n"+
 407             "obj_uid VARCHAR(255) not null,\n"+
 408             "obj_contents LONGVARBINARY\n"+
 409             ")";
 410     public static final String CREATE_OBJLIST =
 411             "CREATE TABLE objlist(\n"+
 412             "obj_source VARCHAR(255) not null," +
 413             "obj_uid VARCHAR(255) not null"+
 414             ")";
 415     public static final String CREATE_URLLIST =
 416             "CREATE TABLE urllist(\n"+
 417             "obj_source VARCHAR(255) not null,"+
 418             "obj_uid VARCHAR(255) not null,"+
 419             "obj_url VARCHAR(2048) not null"+
 420             ")";
 421     public static final String CREATE_DATELIST =
 422             "CREATE TABLE datelist(\n"+
 423             "obj_uid VARCHAR(255) not null,"+
 424             "obj_dtstart INT not null,"+
 425             "obj_dtend INT not null"+
 426             ")";
 427     public static final String CREATE_NOTWANTED =
 428             "CREATE TABLE notwanted(\n"+
 429             "obj_url VARCHAR(255) not null,"+
 430             "obj_etag VARCHAR(255) not null"+
 431             ")";
 432 }