001package 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. 002 003import java.sql.*; 004import java.util.concurrent.atomic.AtomicBoolean; 005import votorola.g.lang.*; 006import votorola.g.sql.*; 007 008 009/** The relational backing for the {@linkplain StoreWAP StoreWAP} web API. 010 */ 011public @ThreadSafe final class StoreTable 012{ 013 014 015 /** Contructs a StoreTable for use under the Wicket web interface. 016 */ 017 public StoreTable( final votorola.a.web.wic.VOWicket vApp ) throws SQLException 018 { 019 this( vApp.vsRun().database() ); 020 } 021 022 023 024 /** Contructs a StoreTable for use in a web API. 025 */ 026 public StoreTable( final votorola.a.web.wap.WAP wap ) throws SQLException 027 { 028 this( wap.vsRun().database() ); 029 } 030 031 032 033 private StoreTable( Database _database ) throws SQLException 034 { 035 // First construction clears the table. This is a cheap and effective means of 036 // garbage collection for ephemeral web data such as this, provided it mostly 037 // happens when the web interfaces are down. To avoid violating this assumption, 038 // constructors are exposed only to code in the servlet container's runtime. 039 database = _database; 040 final String sKey = statementKeyBase + "init"; 041 synchronized( database ) 042 { 043 PreparedStatement s = database.statementCache().get( sKey ); 044 if( s == null ) 045 { 046 s = database.connection().prepareStatement( 047 "CREATE TABLE IF NOT EXISTS \"" + tableName + "\"" 048 + " (client character varying," 049 + " key character varying," 050 + " value character varying NOT NULL," 051 + " PRIMARY KEY (client, key))" ); 052 database.statementCache().put( sKey, s ); 053 } 054 s.execute(); 055 if( !isConstructedA.getAndSet( true )) 056 { 057 s = database.connection().prepareStatement( "DELETE FROM \"" + tableName + "\"" ); 058 s.executeUpdate(); 059 } 060 } 061 } 062 063 064 065 private static final AtomicBoolean isConstructedA = new AtomicBoolean(); 066 067 068 069 // -------------------------------------------------------------------------------- 070 071 072 // /** Removes a value from the table if one is stored there. 073 // * 074 // * @return true if a value was actually removed, false otherwise. 075 // */ 076 // boolean delete( final String client, final String key ) throws SQLException 077 // { 078 // final String sKey = statementKeyBase + "deleteK"; 079 // synchronized( database ) 080 // { 081 // PreparedStatement s = database.statementCache().get( sKey ); 082 // if( s == null ) 083 // { 084 // s = database.connection().prepareStatement( 085 // "DELETE FROM \"" + tableName + "\" WHERE client = ? AND key = ?" ); 086 // database.statementCache().put( sKey, s ); 087 // } 088 // s.setString( 1, client ); 089 // s.setString( 2, key ); 090 // final int updatedRows = s.executeUpdate(); 091 // assert updatedRows == 0 || updatedRows == 1; 092 // return updatedRows > 0; 093 // } 094 // } 095 096 097 098 /** Removes a given value from the table if it is stored there. 099 * 100 * @return true if the value was actually removed, false otherwise. 101 */ 102 public boolean delete( final String client, final String key, final String value ) 103 throws SQLException 104 { 105 final String sKey = statementKeyBase + "deleteKV"; 106 synchronized( database ) 107 { 108 PreparedStatement s = database.statementCache().get( sKey ); 109 if( s == null ) 110 { 111 s = database.connection().prepareStatement( 112 "DELETE FROM \"" + tableName + "\" WHERE client = ? AND key = ? AND value = ?" ); 113 database.statementCache().put( sKey, s ); 114 } 115 s.setString( 1, client ); 116 s.setString( 2, key ); 117 s.setString( 3, value ); 118 final int updatedRows = s.executeUpdate(); 119 assert updatedRows == 0 || updatedRows == 1; 120 return updatedRows > 0; 121 } 122 } 123 124 125 126 /** Returns the specified value from the table, or null if none is stored. 127 * 128 * @see StoreWAP#clientSignature(javax.servlet.http.HttpServletRequest) 129 */ 130 public String get( final String client, final String key ) throws SQLException 131 { 132 final String sKey = statementKeyBase + "get"; 133 synchronized( database ) 134 { 135 PreparedStatement s = database.statementCache().get( sKey ); 136 if( s == null ) 137 { 138 s = database.connection().prepareStatement( 139 "SELECT value FROM \"" + tableName + "\"" + " WHERE client = ? AND key = ?" ); 140 database.statementCache().put( sKey, s ); 141 } 142 s.setString( 1, client ); 143 s.setString( 2, key ); 144 final ResultSet r = s.executeQuery(); 145 try { return r.next()? r.getString(1): null; } 146 finally{ r.close(); } 147 } 148 } 149 150 151 152 /** Stores a value in the table. 153 * 154 * @see StoreWAP#clientSignature(javax.servlet.http.HttpServletRequest) 155 */ 156 public void put( final String client, final String key, final String value ) throws SQLException 157 { 158 synchronized( database ) 159 { 160 // effect an "upsert" in PostgreSQL 161 // http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql/6527838#6527838 162 final Connection c = database.connection(); 163 { 164 final String sKey = statementKeyBase + "putU"; 165 PreparedStatement s = database.statementCache().get( sKey ); 166 if( s == null ) 167 { 168 s = c.prepareStatement( "UPDATE \"" + tableName + "\"" 169 + " SET value = ? WHERE client = ? AND key = ?" ); 170 database.statementCache().put( sKey, s ); 171 } 172 s.setString( 1, value ); 173 s.setString( 2, client ); 174 s.setString( 3, key ); 175 final int updatedRows = s.executeUpdate(); 176 if( updatedRows > 0 ) { assert updatedRows == 1; return; } 177 } 178 { 179 final String sKey = statementKeyBase + "putI"; 180 PreparedStatement s = database.statementCache().get( sKey ); 181 if( s == null ) 182 { 183 s = c.prepareStatement( "INSERT INTO \"" + tableName + "\"" 184 + " (client, key, value) SELECT ?, ?, ? WHERE NOT EXISTS" 185 + " (SELECT 1 FROM \"" + tableName + "\" WHERE client = ? AND key = ?)" ); 186 database.statementCache().put( sKey, s ); 187 } 188 s.setString( 1, client ); 189 s.setString( 2, key ); 190 s.setString( 3, value ); 191 s.setString( 4, client ); 192 s.setString( 5, key ); 193 s.executeUpdate(); 194 } 195 } 196 } 197 198 199 200 /** The name of the table, which is {@value}. 201 */ 202 public static final String tableName = "store_wap"; 203 204 205 206//// P r i v a t e /////////////////////////////////////////////////////////////////////// 207 208 209 private @Warning("thread restricted object") final Database database; 210 211 212 213 private static final String statementKeyBase = StoreTable.class.getName() + ":" + tableName 214 + "."; 215 216 217}