package votorola.a.count; // Copyright 2007-2013, 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.io.*;
import java.sql.*;
import java.util.*;
import javax.xml.stream.*;
import votorola.a.*;
import votorola.a.voter.*;
import votorola.g.*;
import votorola.g.lang.*;
import votorola.g.sql.*;
/** The relational store of count nodes that (in part) backs a mounted {@linkplain Count
* count}.
*/
public @ThreadSafe final class CountTable
{
// cf. a/trust/TraceNodeW.Table
/** Constructs a CountTable.
*
* @see #readyDirectory()
* @see #database()
*/
CountTable( ReadyDirectory _readyDirectory, Database _database ) throws IOException, SQLException
{
readyDirectory = _readyDirectory;
database = _database;
synchronized( database ) { database.ensureSchema( SCHEMA_NAME ); }
final String snapSuffix = OutputStore.suffix( readyDirectory.snapDirectory().getName() );
if( !OutputStore.isY4MDS( snapSuffix ))
{
throw new VotorolaRuntimeException(
"improperly suffixed snap directory parent of ready directory: " + readyDirectory );
}
tableName = snapSuffix.substring(1) + OutputStore.SUFFIX_DELIMITER
+ "count_node" + OutputStore.suffix(readyDirectory.getCanonicalFile().getName());
statementKeyBase = getClass().getName() + ":" + SCHEMA_NAME + "/" + tableName + ".";
}
private final String statementKeyBase;
private final String tableName;
// --------------------------------------------------------------------------------
/** A snippet of SQL {@value} that specifies a base candidate. Appended to a WHERE
* clause, it limits the result set to the candidates sitting at the bottom of their
* respective cascades, where they sit either because they are not voting or because
* they are voting in cycles.
*
* @see theory.xht#base-candidate
*/
public static final String BASE_CANDIDATE_TAIL =
"AND directVoterCount > 0 AND (isCycler OR NOT isCast)";
/** Creates this table in the database.
*/
final void create() throws SQLException
{
final String sKey = statementKeyBase + "create";
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"CREATE TABLE \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " (serviceName character varying,"
+ " email character varying,"
+ " bar character varying,"
+ " candidateEmail character varying,"
+ " carryVolume bigint NOT NULL,"
+ " dartSector smallint NOT NULL,"
+ " directVoterCount bigint NOT NULL,"
+ " isCast boolean NOT NULL,"
+ " isCycler boolean NOT NULL,"
+ " rank bigint NOT NULL,"
+ " rankIndex bigint NOT NULL," // created as index in createIndices()
+ " receiveVolume bigint NOT NULL,"
+ " time bigint NOT NULL,"
+ " xml character varying,"
+ " PRIMARY KEY ( serviceName, email ))" );
// Changing table structure? Then also increment Count.serialVersionUID.
database.statementCache().put( sKey, s );
}
s.execute();
}
}
/** Creates additional indices for this table. Some queries are expected to be much
* faster afterwards. On the other hand, all updates will be slightly slower.
*/
final void createIndices() throws SQLException
{
// This is intended mainly for generating ordered and/or paged views of the
// results. ORDER BY can execute faster on primary keys and other indices, and
// simple range tests on indices can execute faster for purposes of paging than
// ORDER BY followed by LIMIT and OFFSET. (Creating an ordered SQL view is no
// substitute for this optimization. A view does not create indices, but rather
// re-sorts the table on every query.)
final String sKey = statementKeyBase + "createIndices";
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement( // tableName in index name seems redundant, but the examples in the manual use it
"CREATE INDEX \"" + tableName + "_rankIndex\"" // unique only per poll
+ " ON \"" + SCHEMA_NAME + "\".\"" + tableName + "\" (rankIndex)" );
database.statementCache().put( sKey, s );
}
s.execute();
}
}
/** A snippet of SQL {@value} that specifies a dart sectored node, suitable for
* appending to a WHERE clause.
*/
public static final String DART_SECTORED_TAIL = "AND dartSector != 0";
/** The database in which this table is stored.
*/
@Warning("thread restricted object") final Database database() { return database; }
private final Database database;
/** Drops this table from the database if it exists.
*
* @return true if any rows were actually removed as a result, false otherwise.
*/
final boolean drop() throws SQLException
{
final String sKey = statementKeyBase + "drop";
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"DROP TABLE IF EXISTS \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" );
database.statementCache().put( sKey, s );
}
final int updatedRows = s.executeUpdate();
return updatedRows > 0;
}
}
/** Stores a node.
*/
public final void put( final CountNodeW node ) throws SQLException
{
final String qTable = "\"" + SCHEMA_NAME + "\".\"" + tableName + "\"";
final String xml;
{
XMLColumnBuilder bC = null;
bC = append( "displayTitle", node.displayTitle(), bC );
bC = append( "location", node.getLocation(), bC );
bC = append( "source", node.getSource(), bC );
if( bC == null ) xml = null;
else
{
final StringBuilder b = (StringBuilder)bC.sink();
b.insert( 0, "" );
xml = b.toString();
}
}
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 " + qTable
+ " SET bar = ?,"
+ " candidateEmail = ?,"
+ " carryVolume = ?,"
+ " dartSector = ?,"
+ " directVoterCount = ?,"
+ " isCast = ?,"
+ " isCycler = ?,"
+ " rank = ?,"
+ " rankIndex = ?,"
+ " receiveVolume = ?,"
+ " time = ?,"
+ " xml = ?"
+ " WHERE serviceName = ? AND email = ?" );
database.statementCache().put( sKey, s );
}
int p = 1;
s.setString( p++, node.getBar() );
s.setString( p++, node.getCandidateEmail() );
s.setLong( p++, node.carryVolume() );
s.setShort( p++, node.dartSector() );
s.setLong( p++, node.directVoterCount() );
s.setBoolean( p++, node.isCast() );
s.setBoolean( p++, node.isCycler() );
s.setLong( p++, node.getRank() );
s.setLong( p++, node.getRankIndex() );
s.setLong( p++, node.receiveVolume() );
s.setLong( p++, node.getTime() );
s.setString( p++, xml );
s.setString( p++, node.tablePV().serviceName );
s.setString( p, node.email() );
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 " + qTable
+ " (serviceName, email, bar, candidateEmail, carryVolume, dartSector,"
+ " directVoterCount, isCast, isCycler, rank, rankIndex, receiveVolume,"
+ " time, xml)"
+ " SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? WHERE NOT EXISTS"
+ " (SELECT 1 FROM " + qTable + " WHERE serviceName = ? AND email = ?)" );
database.statementCache().put( sKey, s );
}
int p = 1;
s.setString( p++, node.tablePV().serviceName );
s.setString( p++, node.email() );
s.setString( p++, node.getBar() );
s.setString( p++, node.getCandidateEmail() );
s.setLong( p++, node.carryVolume() );
s.setShort( p++, node.dartSector() );
s.setLong( p++, node.directVoterCount() );
s.setBoolean( p++, node.isCast() );
s.setBoolean( p++, node.isCycler() );
s.setLong( p++, node.getRank() );
s.setLong( p++, node.getRankIndex() );
s.setLong( p++, node.receiveVolume() );
s.setLong( p++, node.getTime() );
s.setString( p++, xml );
s.setString( p++, node.tablePV().serviceName );
s.setString( p, node.email() );
s.executeUpdate();
}
}
}
/** The file-based counterpart to this table.
*/
public final ReadyDirectory readyDirectory() { return readyDirectory; }
private final ReadyDirectory readyDirectory; // final after init
/** The name of the table's schema.
*/
public static final String SCHEMA_NAME = "out_count";
// ================================================================================
/** A programmatic view of a count table restricted to a particular poll. The number
* of polls is potentially large (millions), so rather than create a separate SQL
* view of each, the views are created on the fly. Hence this programmatic
* implementation.
*/
public class PollView
{
/** Creates a PollView.
*
* @param _serviceName the identifier of the poll
*/
PollView( String _serviceName ) { serviceName = _serviceName; }
final String serviceName;
private final String statementKeyBase =
getClass().getName() + ":" + SCHEMA_NAME + "/" + tableName + ".";
// --------------------------------------------------------------------------------
/** Returns the number of nodes that are base candidates.
*
* @see theory.xht#base-candidate
*/
public final long countBaseCandidates() throws SQLException
{
final String sKey = statementKeyBase + "countBaseCandidates";
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT count(*) FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ? " + BASE_CANDIDATE_TAIL );
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
final ResultSet r = s.executeQuery();
try
{
r.next();
return r.getLong( 1 );
}
finally{ r.close(); }
}
}
/** Retrieves a node from this view.
*
* @return node as stored in the view; or null, if none is stored.
*/
public CountNodeW get( final String email ) throws SQLException, XMLStreamException
{
if( email == null ) throw new NullPointerException(); // fail fast
final String sKey = statementKeyBase + "get";
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT bar, candidateEmail, carryVolume, dartSector, directVoterCount,"
+ " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml"
+ " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ? AND email = ?" );
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
s.setString( 2, email );
final ResultSet r = s.executeQuery();
try
{
if( !r.next() ) return null;
int p = 1;
return new CountNodeW( PollView.this, IDPair.fromEmail(email), r.getString(p++),
r.getString(p++), r.getLong(p++), r.getByte(p++), r.getLong(p++),
r.getBoolean(p++), r.getBoolean(p++), r.getLong(p++), r.getLong(p++),
r.getLong(p++), r.getLong(p++), r.getString(p) );
}
finally{ r.close(); }
}
}
/** Retrieves the primary indeces (email) of all nodes in this view. The caller
* is responsible for closing the returned result set when finished with it.
*/
@ThreadRestricted( "holds CountTable.this.database" )
public ResultSet getByIndeces() throws SQLException
{
assert Thread.holdsLock( database );
final String sKey = statementKeyBase + "getByIndeces";
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT email FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ?" );
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
return s.executeQuery();
}
/** Retrieves a node from this view, or, if none is stored, a {@linkplain
* CountNodeIC CountNodeIC} with default values.
*/
public CountNodeW getOrCreate( final String email ) throws SQLException, XMLStreamException
{
CountNodeW node = get( email );
if( node == null ) node = new CountNodeIC( PollView.this, IDPair.fromEmail(email) );
return node;
}
/** Retrieves a list of nodes by rank indeces. The list includes all nodes
* indexed from first (inclusive) to lastBound (exclusive). It is pre-sorted
* from low to high.
*/
public final List listByRankIndeces( final long first, final long lastBound )
throws SQLException, XMLStreamException
{
final String sKey = statementKeyBase + "listByRankIndeces";
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT email, bar, candidateEmail, carryVolume, dartSector, directVoterCount,"
+ " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml"
+ " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ? AND rankIndex >= ? AND rankIndex < ?"
+ " ORDER BY rankIndex" );
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
s.setLong( 2, first );
s.setLong( 3, lastBound );
final ResultSet r = s.executeQuery();
final ArrayList nodeList =
new ArrayList( /*initial capacity*/(int)(lastBound - first) );
try
{
while( r.next() )
{
int p = 1;
nodeList.add( new CountNodeW( PollView.this,
IDPair.fromEmail(r.getString(p++)), r.getString(p++), r.getString(p++),
r.getLong(p++), r.getByte(p++), r.getLong(p++), r.getBoolean(p++),
r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++),
r.getLong(p++), r.getString(p) ));
}
}
finally{ r.close(); }
return nodeList;
}
}
/** Pass all nodes of this view through the specified runner.
*
* @param sqlTail the tail of the SQL command for appending after the WHERE
* clause. This is currently mandatory only because all clients require it
* anyway.
*/
public final void run( final String sqlTail, final CountNodeW.Runner runner )
throws SQLException, XMLStreamException
{
final String sKey = statementKeyBase + "run " + sqlTail;
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT email, bar, candidateEmail, carryVolume, dartSector, directVoterCount,"
+ " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml"
+ " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ? " + sqlTail );
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
final ResultSet r = s.executeQuery();
try
{
while( r.next() )
{
int p = 1;
runner.run( new CountNodeW( PollView.this,
IDPair.fromEmail(r.getString(p++)), r.getString(p++), r.getString(p++),
r.getLong(p++), r.getByte(p++), r.getLong(p++), r.getBoolean(p++),
r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++),
r.getLong(p++), r.getString(p) ));
}
}
finally{ r.close(); }
}
}
/** Pass through the specified runner all nodes that are voting directly for the
* specified candidate.
*
* @param sqlTail the tail of the SQL command for appending after the WHERE
* clause. This is formally mandatory only because all clients happen to
* require it.
*/
public final void runCasters( final String candidateEmail, final String sqlTail,
final CountNodeW.Runner runner ) throws SQLException, XMLStreamException
{
final String sKey = statementKeyBase + "runCasters " + sqlTail;
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT email, bar, carryVolume, dartSector, directVoterCount, isCycler,"
+ " rank, rankIndex, receiveVolume, time, xml"
+ " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ? AND isCast AND candidateEmail = ? " + sqlTail );
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
s.setString( 2, candidateEmail );
final ResultSet r = s.executeQuery();
try
{
while( r.next() )
{
int p = 1;
runner.run( new CountNodeW( PollView.this,
IDPair.fromEmail(r.getString(p++)), r.getString(p++), candidateEmail,
r.getLong(p++), r.getByte(p++), r.getLong(p++), /*isCast*/true,
r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++),
r.getLong(p++), r.getString(p) ));
}
}
finally{ r.close(); }
}
}
/** Pass through the specified runner all nodes of a group centered on a
* candidate. This means the candidate node itself, plus each node that is
* voting directly for the candidate.
*
* @param sqlTail the tail of the SQL command for appending after the WHERE
* clause. This is formally mandatory only because all clients happen to
* require it. The tail must end with a right bracket ')'.
*/
public final void runGroup( final String groupCandidateEmail, final String sqlTail,
final CountNodeW.Runner runner ) throws SQLException, XMLStreamException
{
final String sKey = statementKeyBase + "runGroup " + sqlTail;
synchronized( database )
{
PreparedStatement s = database.statementCache().get( sKey );
if( s == null )
{
s = database.connection().prepareStatement(
"SELECT email, bar, candidateEmail, carryVolume, dartSector, directVoterCount,"
+ " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml"
+ " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\""
+ " WHERE serviceName = ? AND (email = ? OR candidateEmail = ? " + sqlTail );
// no need to test isCast in order to exclude self-voting candidate,
// candidate included regardless
database.statementCache().put( sKey, s );
}
s.setString( 1, serviceName );
s.setString( 2, groupCandidateEmail );
s.setString( 3, groupCandidateEmail );
final ResultSet r = s.executeQuery();
try
{
while( r.next() )
{
int p = 1;
runner.run( new CountNodeW( PollView.this,
IDPair.fromEmail(r.getString(p++)), r.getString(p++), r.getString(p++),
r.getLong(p++), r.getByte(p++), r.getLong(p++), r.getBoolean(p++),
r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++),
r.getLong(p++), r.getString(p) ));
}
}
finally{ r.close(); }
}
}
/** The larger table of this view.
*/
public final CountTable table() { return CountTable.this; }
}
//// P r i v a t e ///////////////////////////////////////////////////////////////////////
private static XMLColumnBuilder append( final String name, final String value,
XMLColumnBuilder bC )
{
if( value != null )
{
if( bC == null ) bC = new XMLColumnBuilder( new StringBuilder() ); // lazily
bC.appendAttribute( name, value );
}
return bC;
}
}