package votorola.s.wap.store; // Copyright 2012, Michael Allan. Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Votorola Software"), to deal in the Votorola Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicence, and/or sell copies of the Votorola Software, and to permit persons to whom the Votorola Software is furnished to do so, subject to the following conditions: The preceding copyright notice and this permission notice shall be included in all copies or substantial portions of the Votorola Software. THE VOTOROLA SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE VOTOROLA SOFTWARE OR THE USE OR OTHER DEALINGS IN THE VOTOROLA SOFTWARE. import java.sql.*; import java.util.concurrent.atomic.AtomicBoolean; import votorola.g.lang.*; import votorola.g.sql.*; /** The relational backing for the {@linkplain StoreWAP StoreWAP} web API. */ public @ThreadSafe final class StoreTable { /** Contructs a StoreTable for use under the Wicket web interface. */ public StoreTable( final votorola.a.web.wic.VOWicket vApp ) throws SQLException { this( vApp.vsRun().database() ); } /** Contructs a StoreTable for use in a web API. */ public StoreTable( final votorola.a.web.wap.WAP wap ) throws SQLException { this( wap.vsRun().database() ); } private StoreTable( Database _database ) throws SQLException { // First construction clears the table. This is a cheap and effective means of // garbage collection for ephemeral web data such as this, provided it mostly // happens when the web interfaces are down. To avoid violating this assumption, // constructors are exposed only to code in the servlet container's runtime. database = _database; final String sKey = statementKeyBase + "init"; synchronized( database ) { PreparedStatement s = database.statementCache().get( sKey ); if( s == null ) { s = database.connection().prepareStatement( "CREATE TABLE IF NOT EXISTS \"" + tableName + "\"" + " (client character varying," + " key character varying," + " value character varying NOT NULL," + " PRIMARY KEY (client, key))" ); database.statementCache().put( sKey, s ); } s.execute(); if( !isConstructedA.getAndSet( true )) { s = database.connection().prepareStatement( "DELETE FROM \"" + tableName + "\"" ); s.executeUpdate(); } } } private static final AtomicBoolean isConstructedA = new AtomicBoolean(); // -------------------------------------------------------------------------------- // /** Removes a value from the table if one is stored there. // * // * @return true if a value was actually removed, false otherwise. // */ // boolean delete( final String client, final String key ) throws SQLException // { // final String sKey = statementKeyBase + "deleteK"; // synchronized( database ) // { // PreparedStatement s = database.statementCache().get( sKey ); // if( s == null ) // { // s = database.connection().prepareStatement( // "DELETE FROM \"" + tableName + "\" WHERE client = ? AND key = ?" ); // database.statementCache().put( sKey, s ); // } // s.setString( 1, client ); // s.setString( 2, key ); // final int updatedRows = s.executeUpdate(); // assert updatedRows == 0 || updatedRows == 1; // return updatedRows > 0; // } // } /** Removes a given value from the table if it is stored there. * * @return true if the value was actually removed, false otherwise. */ public boolean delete( final String client, final String key, final String value ) throws SQLException { final String sKey = statementKeyBase + "deleteKV"; synchronized( database ) { PreparedStatement s = database.statementCache().get( sKey ); if( s == null ) { s = database.connection().prepareStatement( "DELETE FROM \"" + tableName + "\" WHERE client = ? AND key = ? AND value = ?" ); database.statementCache().put( sKey, s ); } s.setString( 1, client ); s.setString( 2, key ); s.setString( 3, value ); final int updatedRows = s.executeUpdate(); assert updatedRows == 0 || updatedRows == 1; return updatedRows > 0; } } /** Returns the specified value from the table, or null if none is stored. * * @see StoreWAP#clientSignature(javax.servlet.http.HttpServletRequest) */ public String get( final String client, final String key ) throws SQLException { final String sKey = statementKeyBase + "get"; synchronized( database ) { PreparedStatement s = database.statementCache().get( sKey ); if( s == null ) { s = database.connection().prepareStatement( "SELECT value FROM \"" + tableName + "\"" + " WHERE client = ? AND key = ?" ); database.statementCache().put( sKey, s ); } s.setString( 1, client ); s.setString( 2, key ); final ResultSet r = s.executeQuery(); try { return r.next()? r.getString(1): null; } finally{ r.close(); } } } /** Stores a value in the table. * * @see StoreWAP#clientSignature(javax.servlet.http.HttpServletRequest) */ public void put( final String client, final String key, final String value ) throws SQLException { synchronized( database ) { // effect an "upsert" in PostgreSQL // http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql/6527838#6527838 final Connection c = database.connection(); { final String sKey = statementKeyBase + "putU"; PreparedStatement s = database.statementCache().get( sKey ); if( s == null ) { s = c.prepareStatement( "UPDATE \"" + tableName + "\"" + " SET value = ? WHERE client = ? AND key = ?" ); database.statementCache().put( sKey, s ); } s.setString( 1, value ); s.setString( 2, client ); s.setString( 3, key ); final int updatedRows = s.executeUpdate(); if( updatedRows > 0 ) { assert updatedRows == 1; return; } } { final String sKey = statementKeyBase + "putI"; PreparedStatement s = database.statementCache().get( sKey ); if( s == null ) { s = c.prepareStatement( "INSERT INTO \"" + tableName + "\"" + " (client, key, value) SELECT ?, ?, ? WHERE NOT EXISTS" + " (SELECT 1 FROM \"" + tableName + "\" WHERE client = ? AND key = ?)" ); database.statementCache().put( sKey, s ); } s.setString( 1, client ); s.setString( 2, key ); s.setString( 3, value ); s.setString( 4, client ); s.setString( 5, key ); s.executeUpdate(); } } } /** The name of the table, which is {@value}. */ public static final String tableName = "store_wap"; //// P r i v a t e /////////////////////////////////////////////////////////////////////// private @Warning("thread restricted object") final Database database; private static final String statementKeyBase = StoreTable.class.getName() + ":" + tableName + "."; }