001package 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. 002 003import java.io.*; 004import java.sql.*; 005import java.util.*; 006import javax.xml.stream.*; 007import votorola.a.*; 008import votorola.a.voter.*; 009import votorola.g.*; 010import votorola.g.lang.*; 011import votorola.g.sql.*; 012 013 014/** The relational store of count nodes that (in part) backs a mounted {@linkplain Count 015 * count}. 016 */ 017public @ThreadSafe final class CountTable 018{ 019 020 // cf. a/trust/TraceNodeW.Table 021 022 023 /** Constructs a CountTable. 024 * 025 * @see #readyDirectory() 026 * @see #database() 027 */ 028 CountTable( ReadyDirectory _readyDirectory, Database _database ) throws IOException, SQLException 029 { 030 readyDirectory = _readyDirectory; 031 database = _database; 032 synchronized( database ) { database.ensureSchema( SCHEMA_NAME ); } 033 final String snapSuffix = OutputStore.suffix( readyDirectory.snapDirectory().getName() ); 034 if( !OutputStore.isY4MDS( snapSuffix )) 035 { 036 throw new VotorolaRuntimeException( 037 "improperly suffixed snap directory parent of ready directory: " + readyDirectory ); 038 } 039 040 tableName = snapSuffix.substring(1) + OutputStore.SUFFIX_DELIMITER 041 + "count_node" + OutputStore.suffix(readyDirectory.getCanonicalFile().getName()); 042 statementKeyBase = getClass().getName() + ":" + SCHEMA_NAME + "/" + tableName + "."; 043 } 044 045 046 047 private final String statementKeyBase; 048 049 050 051 private final String tableName; 052 053 054 055 // -------------------------------------------------------------------------------- 056 057 058 /** A snippet of SQL {@value} that specifies a base candidate. Appended to a WHERE 059 * clause, it limits the result set to the candidates sitting at the bottom of their 060 * respective cascades, where they sit either because they are not voting or because 061 * they are voting in cycles. 062 * 063 * @see <a href='http://reluk.ca/project/votorola/d/theory.xht#base-candidate' target='_top' 064 * >theory.xht#base-candidate</a> 065 */ 066 public static final String BASE_CANDIDATE_TAIL = 067 "AND directVoterCount > 0 AND (isCycler OR NOT isCast)"; 068 069 070 071 /** Creates this table in the database. 072 */ 073 final void create() throws SQLException 074 { 075 final String sKey = statementKeyBase + "create"; 076 synchronized( database ) 077 { 078 PreparedStatement s = database.statementCache().get( sKey ); 079 if( s == null ) 080 { 081 s = database.connection().prepareStatement( 082 "CREATE TABLE \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 083 + " (serviceName character varying," 084 + " email character varying," 085 + " bar character varying," 086 + " candidateEmail character varying," 087 + " carryVolume bigint NOT NULL," 088 + " dartSector smallint NOT NULL," 089 + " directVoterCount bigint NOT NULL," 090 + " isCast boolean NOT NULL," 091 + " isCycler boolean NOT NULL," 092 + " rank bigint NOT NULL," 093 + " rankIndex bigint NOT NULL," // created as index in createIndices() 094 + " receiveVolume bigint NOT NULL," 095 + " time bigint NOT NULL," 096 + " xml character varying," 097 + " PRIMARY KEY ( serviceName, email ))" ); 098 // Changing table structure? Then also increment Count.serialVersionUID. 099 database.statementCache().put( sKey, s ); 100 } 101 s.execute(); 102 } 103 } 104 105 106 107 /** Creates additional indices for this table. Some queries are expected to be much 108 * faster afterwards. On the other hand, all updates will be slightly slower. 109 */ 110 final void createIndices() throws SQLException 111 { 112 // This is intended mainly for generating ordered and/or paged views of the 113 // results. ORDER BY can execute faster on primary keys and other indices, and 114 // simple range tests on indices can execute faster for purposes of paging than 115 // ORDER BY followed by LIMIT and OFFSET. (Creating an ordered SQL view is no 116 // substitute for this optimization. A view does not create indices, but rather 117 // re-sorts the table on every query.) 118 final String sKey = statementKeyBase + "createIndices"; 119 synchronized( database ) 120 { 121 PreparedStatement s = database.statementCache().get( sKey ); 122 if( s == null ) 123 { 124 s = database.connection().prepareStatement( // tableName in index name seems redundant, but the examples in the manual use it 125 "CREATE INDEX \"" + tableName + "_rankIndex\"" // unique only per poll 126 + " ON \"" + SCHEMA_NAME + "\".\"" + tableName + "\" (rankIndex)" ); 127 database.statementCache().put( sKey, s ); 128 } 129 s.execute(); 130 } 131 } 132 133 134 135 /** A snippet of SQL {@value} that specifies a dart sectored node, suitable for 136 * appending to a WHERE clause. 137 */ 138 public static final String DART_SECTORED_TAIL = "AND dartSector != 0"; 139 140 141 142 /** The database in which this table is stored. 143 */ 144 @Warning("thread restricted object") final Database database() { return database; } 145 146 147 private final Database database; 148 149 150 151 /** Drops this table from the database if it exists. 152 * 153 * @return true if any rows were actually removed as a result, false otherwise. 154 */ 155 final boolean drop() throws SQLException 156 { 157 final String sKey = statementKeyBase + "drop"; 158 synchronized( database ) 159 { 160 PreparedStatement s = database.statementCache().get( sKey ); 161 if( s == null ) 162 { 163 s = database.connection().prepareStatement( 164 "DROP TABLE IF EXISTS \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" ); 165 database.statementCache().put( sKey, s ); 166 } 167 final int updatedRows = s.executeUpdate(); 168 return updatedRows > 0; 169 } 170 } 171 172 173 174 175 176 177 178 179 /** Stores a node. 180 */ 181 public final void put( final CountNodeW node ) throws SQLException 182 { 183 final String qTable = "\"" + SCHEMA_NAME + "\".\"" + tableName + "\""; 184 final String xml; 185 { 186 XMLColumnBuilder bC = null; 187 bC = append( "displayTitle", node.displayTitle(), bC ); 188 bC = append( "location", node.getLocation(), bC ); 189 bC = append( "source", node.getSource(), bC ); 190 if( bC == null ) xml = null; 191 else 192 { 193 final StringBuilder b = (StringBuilder)bC.sink(); 194 b.insert( 0, "<X" ).append( "/>" ); 195 xml = b.toString(); 196 } 197 } 198 synchronized( database ) 199 { 200 // effect an "upsert" in PostgreSQL 201 // http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql/6527838#6527838 202 final Connection c = database.connection(); 203 { 204 final String sKey = statementKeyBase + "putU"; 205 PreparedStatement s = database.statementCache().get( sKey ); 206 if( s == null ) 207 { 208 s = c.prepareStatement( "UPDATE " + qTable 209 + " SET bar = ?," 210 + " candidateEmail = ?," 211 + " carryVolume = ?," 212 + " dartSector = ?," 213 + " directVoterCount = ?," 214 + " isCast = ?," 215 + " isCycler = ?," 216 + " rank = ?," 217 + " rankIndex = ?," 218 + " receiveVolume = ?," 219 + " time = ?," 220 + " xml = ?" 221 + " WHERE serviceName = ? AND email = ?" ); 222 database.statementCache().put( sKey, s ); 223 } 224 int p = 1; 225 s.setString( p++, node.getBar() ); 226 s.setString( p++, node.getCandidateEmail() ); 227 s.setLong( p++, node.carryVolume() ); 228 s.setShort( p++, node.dartSector() ); 229 s.setLong( p++, node.directVoterCount() ); 230 s.setBoolean( p++, node.isCast() ); 231 s.setBoolean( p++, node.isCycler() ); 232 s.setLong( p++, node.getRank() ); 233 s.setLong( p++, node.getRankIndex() ); 234 s.setLong( p++, node.receiveVolume() ); 235 s.setLong( p++, node.getTime() ); 236 s.setString( p++, xml ); 237 s.setString( p++, node.tablePV().serviceName ); 238 s.setString( p, node.email() ); 239 final int updatedRows = s.executeUpdate(); 240 if( updatedRows > 0 ) { assert updatedRows == 1; return; } 241 } 242 { 243 final String sKey = statementKeyBase + "putI"; 244 PreparedStatement s = database.statementCache().get( sKey ); 245 if( s == null ) 246 { 247 s = c.prepareStatement( "INSERT INTO " + qTable 248 + " (serviceName, email, bar, candidateEmail, carryVolume, dartSector," 249 + " directVoterCount, isCast, isCycler, rank, rankIndex, receiveVolume," 250 + " time, xml)" 251 + " SELECT ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? WHERE NOT EXISTS" 252 + " (SELECT 1 FROM " + qTable + " WHERE serviceName = ? AND email = ?)" ); 253 database.statementCache().put( sKey, s ); 254 } 255 int p = 1; 256 s.setString( p++, node.tablePV().serviceName ); 257 s.setString( p++, node.email() ); 258 s.setString( p++, node.getBar() ); 259 s.setString( p++, node.getCandidateEmail() ); 260 s.setLong( p++, node.carryVolume() ); 261 s.setShort( p++, node.dartSector() ); 262 s.setLong( p++, node.directVoterCount() ); 263 s.setBoolean( p++, node.isCast() ); 264 s.setBoolean( p++, node.isCycler() ); 265 s.setLong( p++, node.getRank() ); 266 s.setLong( p++, node.getRankIndex() ); 267 s.setLong( p++, node.receiveVolume() ); 268 s.setLong( p++, node.getTime() ); 269 s.setString( p++, xml ); 270 s.setString( p++, node.tablePV().serviceName ); 271 s.setString( p, node.email() ); 272 s.executeUpdate(); 273 } 274 } 275 } 276 277 278 279 /** The file-based counterpart to this table. 280 */ 281 public final ReadyDirectory readyDirectory() { return readyDirectory; } 282 283 284 private final ReadyDirectory readyDirectory; // final after init 285 286 287 288 /** The name of the table's schema. 289 */ 290 public static final String SCHEMA_NAME = "out_count"; 291 292 293 294 // ================================================================================ 295 296 297 /** A programmatic view of a count table restricted to a particular poll. The number 298 * of polls is potentially large (millions), so rather than create a separate SQL 299 * view of each, the views are created on the fly. Hence this programmatic 300 * implementation. 301 */ 302 public class PollView 303 { 304 305 306 /** Creates a PollView. 307 * 308 * @param _serviceName the identifier of the poll 309 */ 310 PollView( String _serviceName ) { serviceName = _serviceName; } 311 312 313 314 final String serviceName; 315 316 317 318 private final String statementKeyBase = 319 getClass().getName() + ":" + SCHEMA_NAME + "/" + tableName + "."; 320 321 322 323 // -------------------------------------------------------------------------------- 324 325 326 /** Returns the number of nodes that are base candidates. 327 * 328 * @see <a href='http://reluk.ca/project/votorola/d/theory.xht#base-candidate' target='_top' 329 * >theory.xht#base-candidate</a> 330 */ 331 public final long countBaseCandidates() throws SQLException 332 { 333 final String sKey = statementKeyBase + "countBaseCandidates"; 334 synchronized( database ) 335 { 336 PreparedStatement s = database.statementCache().get( sKey ); 337 if( s == null ) 338 { 339 s = database.connection().prepareStatement( 340 "SELECT count(*) FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 341 + " WHERE serviceName = ? " + BASE_CANDIDATE_TAIL ); 342 database.statementCache().put( sKey, s ); 343 } 344 s.setString( 1, serviceName ); 345 final ResultSet r = s.executeQuery(); 346 try 347 { 348 r.next(); 349 return r.getLong( 1 ); 350 } 351 finally{ r.close(); } 352 } 353 } 354 355 356 357 /** Retrieves a node from this view. 358 * 359 * @return node as stored in the view; or null, if none is stored. 360 */ 361 public CountNodeW get( final String email ) throws SQLException, XMLStreamException 362 { 363 if( email == null ) throw new NullPointerException(); // fail fast 364 365 final String sKey = statementKeyBase + "get"; 366 synchronized( database ) 367 { 368 PreparedStatement s = database.statementCache().get( sKey ); 369 if( s == null ) 370 { 371 s = database.connection().prepareStatement( 372 "SELECT bar, candidateEmail, carryVolume, dartSector, directVoterCount," 373 + " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml" 374 + " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 375 + " WHERE serviceName = ? AND email = ?" ); 376 database.statementCache().put( sKey, s ); 377 } 378 s.setString( 1, serviceName ); 379 s.setString( 2, email ); 380 final ResultSet r = s.executeQuery(); 381 try 382 { 383 if( !r.next() ) return null; 384 385 int p = 1; 386 return new CountNodeW( PollView.this, IDPair.fromEmail(email), r.getString(p++), 387 r.getString(p++), r.getLong(p++), r.getByte(p++), r.getLong(p++), 388 r.getBoolean(p++), r.getBoolean(p++), r.getLong(p++), r.getLong(p++), 389 r.getLong(p++), r.getLong(p++), r.getString(p) ); 390 } 391 finally{ r.close(); } 392 } 393 } 394 395 396 397 /** Retrieves the primary indeces (email) of all nodes in this view. The caller 398 * is responsible for closing the returned result set when finished with it. 399 */ 400 @ThreadRestricted( "holds CountTable.this.database" ) 401 public ResultSet getByIndeces() throws SQLException 402 { 403 assert Thread.holdsLock( database ); 404 final String sKey = statementKeyBase + "getByIndeces"; 405 PreparedStatement s = database.statementCache().get( sKey ); 406 if( s == null ) 407 { 408 s = database.connection().prepareStatement( 409 "SELECT email FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 410 + " WHERE serviceName = ?" ); 411 database.statementCache().put( sKey, s ); 412 } 413 s.setString( 1, serviceName ); 414 return s.executeQuery(); 415 } 416 417 418 419 /** Retrieves a node from this view, or, if none is stored, a {@linkplain 420 * CountNodeIC CountNodeIC} with default values. 421 */ 422 public CountNodeW getOrCreate( final String email ) throws SQLException, XMLStreamException 423 { 424 CountNodeW node = get( email ); 425 if( node == null ) node = new CountNodeIC( PollView.this, IDPair.fromEmail(email) ); 426 427 return node; 428 } 429 430 431 432 /** Retrieves a list of nodes by rank indeces. The list includes all nodes 433 * indexed from first (inclusive) to lastBound (exclusive). It is pre-sorted 434 * from low to high. 435 */ 436 public final List<CountNodeW> listByRankIndeces( final long first, final long lastBound ) 437 throws SQLException, XMLStreamException 438 { 439 final String sKey = statementKeyBase + "listByRankIndeces"; 440 synchronized( database ) 441 { 442 PreparedStatement s = database.statementCache().get( sKey ); 443 if( s == null ) 444 { 445 s = database.connection().prepareStatement( 446 "SELECT email, bar, candidateEmail, carryVolume, dartSector, directVoterCount," 447 + " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml" 448 + " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 449 + " WHERE serviceName = ? AND rankIndex >= ? AND rankIndex < ?" 450 + " ORDER BY rankIndex" ); 451 database.statementCache().put( sKey, s ); 452 } 453 s.setString( 1, serviceName ); 454 s.setLong( 2, first ); 455 s.setLong( 3, lastBound ); 456 final ResultSet r = s.executeQuery(); 457 final ArrayList<CountNodeW> nodeList = 458 new ArrayList<CountNodeW>( /*initial capacity*/(int)(lastBound - first) ); 459 try 460 { 461 while( r.next() ) 462 { 463 int p = 1; 464 nodeList.add( new CountNodeW( PollView.this, 465 IDPair.fromEmail(r.getString(p++)), r.getString(p++), r.getString(p++), 466 r.getLong(p++), r.getByte(p++), r.getLong(p++), r.getBoolean(p++), 467 r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++), 468 r.getLong(p++), r.getString(p) )); 469 } 470 } 471 finally{ r.close(); } 472 return nodeList; 473 } 474 } 475 476 477 478 /** Pass all nodes of this view through the specified runner. 479 * 480 * @param sqlTail the tail of the SQL command for appending after the WHERE 481 * clause. This is currently mandatory only because all clients require it 482 * anyway. 483 */ 484 public final void run( final String sqlTail, final CountNodeW.Runner runner ) 485 throws SQLException, XMLStreamException 486 { 487 final String sKey = statementKeyBase + "run " + sqlTail; 488 synchronized( database ) 489 { 490 PreparedStatement s = database.statementCache().get( sKey ); 491 if( s == null ) 492 { 493 s = database.connection().prepareStatement( 494 "SELECT email, bar, candidateEmail, carryVolume, dartSector, directVoterCount," 495 + " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml" 496 + " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 497 + " WHERE serviceName = ? " + sqlTail ); 498 database.statementCache().put( sKey, s ); 499 } 500 s.setString( 1, serviceName ); 501 final ResultSet r = s.executeQuery(); 502 try 503 { 504 while( r.next() ) 505 { 506 int p = 1; 507 runner.run( new CountNodeW( PollView.this, 508 IDPair.fromEmail(r.getString(p++)), r.getString(p++), r.getString(p++), 509 r.getLong(p++), r.getByte(p++), r.getLong(p++), r.getBoolean(p++), 510 r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++), 511 r.getLong(p++), r.getString(p) )); 512 } 513 } 514 finally{ r.close(); } 515 } 516 } 517 518 519 520 /** Pass through the specified runner all nodes that are voting directly for the 521 * specified candidate. 522 * 523 * @param sqlTail the tail of the SQL command for appending after the WHERE 524 * clause. This is formally mandatory only because all clients happen to 525 * require it. 526 */ 527 public final void runCasters( final String candidateEmail, final String sqlTail, 528 final CountNodeW.Runner runner ) throws SQLException, XMLStreamException 529 { 530 final String sKey = statementKeyBase + "runCasters " + sqlTail; 531 synchronized( database ) 532 { 533 PreparedStatement s = database.statementCache().get( sKey ); 534 if( s == null ) 535 { 536 s = database.connection().prepareStatement( 537 "SELECT email, bar, carryVolume, dartSector, directVoterCount, isCycler," 538 + " rank, rankIndex, receiveVolume, time, xml" 539 + " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 540 + " WHERE serviceName = ? AND isCast AND candidateEmail = ? " + sqlTail ); 541 database.statementCache().put( sKey, s ); 542 } 543 s.setString( 1, serviceName ); 544 s.setString( 2, candidateEmail ); 545 final ResultSet r = s.executeQuery(); 546 try 547 { 548 while( r.next() ) 549 { 550 int p = 1; 551 runner.run( new CountNodeW( PollView.this, 552 IDPair.fromEmail(r.getString(p++)), r.getString(p++), candidateEmail, 553 r.getLong(p++), r.getByte(p++), r.getLong(p++), /*isCast*/true, 554 r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++), 555 r.getLong(p++), r.getString(p) )); 556 } 557 } 558 finally{ r.close(); } 559 } 560 } 561 562 563 564 /** Pass through the specified runner all nodes of a group centered on a 565 * candidate. This means the candidate node itself, plus each node that is 566 * voting directly for the candidate. 567 * 568 * @param sqlTail the tail of the SQL command for appending after the WHERE 569 * clause. This is formally mandatory only because all clients happen to 570 * require it. The tail must end with a right bracket ')'. 571 */ 572 public final void runGroup( final String groupCandidateEmail, final String sqlTail, 573 final CountNodeW.Runner runner ) throws SQLException, XMLStreamException 574 { 575 final String sKey = statementKeyBase + "runGroup " + sqlTail; 576 synchronized( database ) 577 { 578 PreparedStatement s = database.statementCache().get( sKey ); 579 if( s == null ) 580 { 581 s = database.connection().prepareStatement( 582 "SELECT email, bar, candidateEmail, carryVolume, dartSector, directVoterCount," 583 + " isCast, isCycler, rank, rankIndex, receiveVolume, time, xml" 584 + " FROM \"" + SCHEMA_NAME + "\".\"" + tableName + "\"" 585 + " WHERE serviceName = ? AND (email = ? OR candidateEmail = ? " + sqlTail ); 586 // no need to test isCast in order to exclude self-voting candidate, 587 // candidate included regardless 588 database.statementCache().put( sKey, s ); 589 } 590 s.setString( 1, serviceName ); 591 s.setString( 2, groupCandidateEmail ); 592 s.setString( 3, groupCandidateEmail ); 593 final ResultSet r = s.executeQuery(); 594 try 595 { 596 while( r.next() ) 597 { 598 int p = 1; 599 runner.run( new CountNodeW( PollView.this, 600 IDPair.fromEmail(r.getString(p++)), r.getString(p++), r.getString(p++), 601 r.getLong(p++), r.getByte(p++), r.getLong(p++), r.getBoolean(p++), 602 r.getBoolean(p++), r.getLong(p++), r.getLong(p++), r.getLong(p++), 603 r.getLong(p++), r.getString(p) )); 604 } 605 } 606 finally{ r.close(); } 607 } 608 } 609 610 611 612 /** The larger table of this view. 613 */ 614 public final CountTable table() { return CountTable.this; } 615 616 617 } 618 619 620 621//// P r i v a t e /////////////////////////////////////////////////////////////////////// 622 623 624 private static XMLColumnBuilder append( final String name, final String value, 625 XMLColumnBuilder bC ) 626 { 627 if( value != null ) 628 { 629 if( bC == null ) bC = new XMLColumnBuilder( new StringBuilder() ); // lazily 630 bC.appendAttribute( name, value ); 631 } 632 return bC; 633 } 634 635 636}