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}