1 /* 2 * Copyright (C) 2004-2014 IBM Corporation and Others. All Rights Reserved. 3 */ 4 package org.unicode.cldr.web; 5 6 import java.io.File; 7 import java.io.IOException; 8 import java.io.PrintWriter; 9 import java.io.StringWriter; 10 import java.io.UnsupportedEncodingException; 11 import java.io.Writer; 12 import java.lang.ref.Reference; 13 import java.lang.ref.SoftReference; 14 import java.lang.reflect.Method; 15 import java.sql.Connection; 16 import java.sql.DatabaseMetaData; 17 import java.sql.Driver; 18 import java.sql.DriverManager; 19 import java.sql.PreparedStatement; 20 import java.sql.ResultSet; 21 import java.sql.ResultSetMetaData; 22 import java.sql.SQLException; 23 import java.sql.SQLFeatureNotSupportedException; 24 import java.sql.Statement; 25 import java.sql.Timestamp; 26 import java.util.ArrayList; 27 import java.util.Date; 28 import java.util.HashMap; 29 import java.util.Map; 30 import java.util.concurrent.ConcurrentHashMap; 31 32 import javax.naming.Context; 33 import javax.naming.InitialContext; 34 import javax.naming.NamingException; 35 import javax.sql.DataSource; 36 37 import org.apache.derby.jdbc.EmbeddedDataSource; 38 import org.json.JSONArray; 39 import org.json.JSONException; 40 import org.json.JSONObject; 41 import org.unicode.cldr.util.CLDRConfig; 42 import org.unicode.cldr.util.CLDRConfigImpl; 43 import org.unicode.cldr.util.CLDRLocale; 44 import org.unicode.cldr.util.CldrUtility; 45 import org.unicode.cldr.util.PathHeader; 46 import org.unicode.cldr.util.StackTracker; 47 import org.unicode.cldr.web.SurveyMain.Phase; 48 49 import com.ibm.icu.dev.util.ElapsedTimer; 50 import com.ibm.icu.text.UnicodeSet; 51 52 /** 53 * Singleton utility class for simple(r) DB access. 54 */ 55 public class DBUtils { 56 private static final boolean DEBUG = false;// CldrUtility.getProperty("TEST", 57 // false); 58 private static final boolean DEBUG_QUICKLY = false;// CldrUtility.getProperty("TEST", 59 // false); 60 61 private static final boolean DEBUG_SQL = false; // show "all" SQL 62 private static DBUtils instance = null; 63 private static final String JDBC_SURVEYTOOL = ("jdbc/SurveyTool"); 64 private static DataSource datasource = null; 65 private String connectionUrl = null; 66 // DB stuff 67 public static String db_driver = null; 68 public static String db_protocol = null; 69 public static String CLDR_DB_U = null; 70 public static String CLDR_DB_P = null; 71 public static String cldrdb_u = null; 72 public static String CLDR_DB; 73 // public static String cldrdb = null; 74 public static String CLDR_DB_CREATESUFFIX = null; 75 public static String CLDR_DB_SHUTDOWNSUFFIX = null; 76 public static boolean db_Derby = false; 77 public static boolean db_Mysql = false; 78 79 /** 80 * Return a string as to which SQL flavor is in use. 81 * 82 * @return 83 */ getDBKind()84 public static final String getDBKind() { 85 if (db_Derby) { 86 return "Derby"; 87 } else if (db_Mysql) { 88 return "MySql"; 89 } else { 90 return "Unknown"; 91 } 92 } 93 getDBInfo()94 public String getDBInfo() { 95 return dbInfo; 96 } 97 98 // === DB workarounds :( - derby by default 99 public static String DB_SQL_IDENTITY = "GENERATED ALWAYS AS IDENTITY"; 100 public static String DB_SQL_VARCHARXPATH = "varchar(1024)"; 101 public static String DB_SQL_WITHDEFAULT = "WITH DEFAULT"; 102 public static String DB_SQL_TIMESTAMP0 = "TIMESTAMP"; 103 public static String DB_SQL_CURRENT_TIMESTAMP0 = "CURRENT_TIMESTAMP"; 104 public static String DB_SQL_MIDTEXT = "VARCHAR(1024)"; 105 public static String DB_SQL_BIGTEXT = "VARCHAR(16384)"; 106 public static String DB_SQL_UNICODE = "VARCHAR(16384)"; // unicode type 107 // string 108 public static String DB_SQL_LAST_MOD_TYPE = "TIMESTAMP"; 109 public static String DB_SQL_LAST_MOD = " last_mod TIMESTAMP NOT NULL WITH DEFAULT CURRENT_TIMESTAMP "; 110 public static String DB_SQL_ALLTABLES = "select tablename from SYS.SYSTABLES where tabletype='T'"; 111 public static String DB_SQL_BINCOLLATE = ""; 112 public static String DB_SQL_ENGINE_INNO = ""; 113 public static String DB_SQL_MB4 = ""; 114 public static int db_number_open = 0; 115 public static int db_number_used = 0; 116 private static int db_UnicodeType = java.sql.Types.VARCHAR; /* 117 * for setNull - 118 * see 119 * java.sql.Types 120 */ 121 private static final StackTracker tracker = DEBUG ? new StackTracker() : null; // new 122 123 // StackTracker(); 124 // - 125 // enable, 126 // to 127 // track 128 // unclosed 129 // connections 130 stats(Appendable output)131 public Appendable stats(Appendable output) throws IOException { 132 return output.append("DBUtils: currently open: " + db_number_open).append(", max open: " + db_max_open) 133 .append(", total used: " + db_number_used); 134 } 135 statsShort(Appendable output)136 public Appendable statsShort(Appendable output) throws IOException { 137 return output.append("" + db_number_open).append("/" + db_max_open); 138 } 139 closeDBConnection(Connection conn)140 public static void closeDBConnection(Connection conn) { 141 if (conn != null) { 142 if (SurveyMain.isUnofficial() && tracker != null) { 143 tracker.remove(conn); 144 } 145 try { 146 if (db_Derby && 147 datasource instanceof EmbeddedDataSource && 148 !conn.isClosed() && 149 !conn.getAutoCommit()) { 150 // commit on close if we are using Derby directly 151 conn.commit(); 152 } 153 conn.close(); 154 } catch (SQLException e) { 155 System.err.println(DBUtils.unchainSqlException(e)); 156 e.printStackTrace(); 157 } 158 db_number_open--; 159 } 160 } 161 escapeBasic(byte what[])162 public static final String escapeBasic(byte what[]) { 163 return escapeLiterals(what); 164 } 165 escapeForMysql(byte what[])166 public static final String escapeForMysql(byte what[]) { 167 boolean hasEscapeable = false; 168 boolean hasNonEscapeable = false; 169 for (byte b : what) { 170 int j = (b) & 0xff; 171 char c = (char) j; 172 if (escapeIsBasic(c)) { 173 continue; 174 } else if (escapeIsEscapeable(c)) { 175 hasEscapeable = true; 176 } else { 177 hasNonEscapeable = true; 178 } 179 } 180 if (hasNonEscapeable) { 181 return escapeHex(what); 182 } else if (hasEscapeable) { 183 return escapeLiterals(what); 184 } else { 185 return escapeBasic(what); 186 } 187 } 188 escapeForMysql(String what)189 public static String escapeForMysql(String what) throws UnsupportedEncodingException { 190 if (what == null) { 191 return "NULL"; 192 } else if (what.length() == 0) { 193 return "\"\""; 194 } else { 195 return escapeForMysql(what.getBytes("ASCII")); 196 } 197 } 198 escapeForMysqlUtf8(String what)199 public static String escapeForMysqlUtf8(String what) throws UnsupportedEncodingException { 200 if (what == null) { 201 return "NULL"; 202 } else if (what.length() == 0) { 203 return "\"\""; 204 } else { 205 return escapeForMysql(what.getBytes("UTF-8")); 206 } 207 } 208 escapeHex(byte what[])209 public static final String escapeHex(byte what[]) { 210 StringBuffer out = new StringBuffer("x'"); 211 for (byte b : what) { 212 int j = (b) & 0xff; 213 if (j < 0x10) { 214 out.append('0'); 215 } 216 out.append(Integer.toHexString(j)); 217 } 218 out.append("'"); 219 return out.toString(); 220 } 221 escapeIsBasic(char c)222 public static final boolean escapeIsBasic(char c) { 223 return ((c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || (c == ' ' || c == '.' || c == '/' 224 || c == '[' || c == ']' || c == '=' || c == '@' || c == '_' || c == ',' || c == '&' || c == '-' || c == '(' 225 || c == ')' || c == '#' || c == '$' || c == '!')); 226 } 227 escapeIsEscapeable(char c)228 public static final boolean escapeIsEscapeable(char c) { 229 return (c == 0 || c == '\'' || c == '"' || c == '\b' || c == '\n' || c == '\r' || c == '\t' || c == 26 || c == '\\'); 230 } 231 escapeLiterals(byte what[])232 public static final String escapeLiterals(byte what[]) { 233 StringBuffer out = new StringBuffer("'"); 234 for (byte b : what) { 235 int j = (b) & 0xff; 236 char c = (char) j; 237 switch (c) { 238 case 0: 239 out.append("\\0"); 240 break; 241 case '\'': 242 out.append("'"); 243 break; 244 case '"': 245 out.append("\\"); 246 break; 247 case '\b': 248 out.append("\\b"); 249 break; 250 case '\n': 251 out.append("\\n"); 252 break; 253 case '\r': 254 out.append("\\r"); 255 break; 256 case '\t': 257 out.append("\\t"); 258 break; 259 case 26: 260 out.append("\\z"); 261 break; 262 case '\\': 263 out.append("\\\\"); 264 break; 265 default: 266 out.append(c); 267 } 268 } 269 out.append("'"); 270 return out.toString(); 271 } 272 peekInstance()273 public static DBUtils peekInstance() { 274 return instance; 275 } 276 getInstance()277 public synchronized static DBUtils getInstance() { 278 if (instance == null) { 279 instance = new DBUtils(); 280 } 281 return instance; 282 } 283 284 /** 285 * For testing use, injecting a specific data source. 286 * For production use, call getInstance(). 287 */ makeInstanceFrom(DataSource dataSource2, String url)288 public synchronized static void makeInstanceFrom(DataSource dataSource2, String url) { 289 if (instance == null) { 290 instance = new DBUtils(dataSource2, url); 291 } else { 292 throw new IllegalArgumentException("Already initted."); 293 } 294 } 295 getStringUTF8(ResultSet rs, String which)296 public static String getStringUTF8(ResultSet rs, String which) throws SQLException { 297 if (db_Derby) { // unicode 298 String str = rs.getString(which); 299 if (rs.wasNull()) 300 return null; 301 return str; 302 } 303 byte rv[] = rs.getBytes(which); 304 if (rs.wasNull()) 305 return null; 306 if (rv != null) { 307 String unicode; 308 try { 309 unicode = new String(rv, "UTF-8"); 310 } catch (UnsupportedEncodingException e) { 311 e.printStackTrace(); 312 throw new InternalError(e.toString()); 313 } 314 return unicode; 315 } else { 316 return null; 317 } 318 } 319 320 // fix the UTF-8 fail getStringUTF8(ResultSet rs, int which)321 public static final String getStringUTF8(ResultSet rs, int which) throws SQLException { 322 if (db_Derby) { // unicode 323 String str = rs.getString(which); 324 if (rs.wasNull()) 325 return null; 326 return str; 327 } 328 byte rv[] = rs.getBytes(which); 329 if (rs.wasNull()) 330 return null; 331 if (rv != null) { 332 String unicode; 333 try { 334 unicode = new String(rv, "UTF-8"); 335 } catch (UnsupportedEncodingException e) { 336 e.printStackTrace(); 337 throw new InternalError(e.toString()); 338 } 339 return unicode; 340 } else { 341 return null; 342 } 343 } 344 hasTable(Connection conn, String table)345 public static boolean hasTable(Connection conn, String table) { 346 String canonName = canonTableName(table); 347 try { 348 ResultSet rs = null; 349 Statement s = null; 350 try { 351 if (db_Derby) { 352 DatabaseMetaData dbmd = conn.getMetaData(); 353 rs = dbmd.getTables(null, null, canonName, null); 354 } else { 355 s = conn.createStatement(); 356 rs = s.executeQuery("show tables like '" + canonName + "'"); 357 } 358 359 if (rs.next() == true) { 360 SurveyLog.warnOnce("table " + canonName + " did exist."); 361 return true; 362 } else { 363 SurveyLog.warnOnce("table " + canonName + " did not exist."); 364 return false; 365 } 366 } finally { 367 DBUtils.close(s, rs); 368 } 369 } catch (SQLException se) { 370 SurveyMain.busted("While looking for table '" + table + "': ", se); 371 return false; // NOTREACHED 372 } 373 } 374 canonTableName(String table)375 private static String canonTableName(String table) { 376 String canonName = db_Derby ? table.toUpperCase() : table; 377 return canonName; 378 } 379 tableHasColumn(Connection conn, String table, String column)380 public static boolean tableHasColumn(Connection conn, String table, String column) { 381 final String canonTable = canonTableName(table); 382 final String canonColumn = canonTableName(column); 383 try { 384 if (db_Derby) { 385 ResultSet rs; 386 DatabaseMetaData dbmd = conn.getMetaData(); 387 rs = dbmd.getColumns(null, null, canonTable, canonColumn); 388 if (rs.next() == true) { 389 rs.close(); 390 //System.err.println("column " + table +"."+column + " did exist."); 391 return true; 392 } else { 393 SurveyLog.debug("column " + table + "." + column + " did not exist."); 394 return false; 395 } 396 } else { 397 return sqlCount(conn, "select count(*) from information_schema.COLUMNS where table_name=? and column_name=?", canonTable, canonColumn) > 0; 398 } 399 } catch (SQLException se) { 400 SurveyMain.busted("While looking for column '" + table + "." + column + "': ", se); 401 return false; // NOTREACHED 402 } 403 } 404 encode_u8(String what)405 private static final byte[] encode_u8(String what) { 406 byte u8[]; 407 if (what == null) { 408 u8 = null; 409 } else { 410 try { 411 u8 = what.getBytes("UTF-8"); 412 } catch (UnsupportedEncodingException e) { 413 e.printStackTrace(); 414 throw new InternalError(e.toString()); 415 } 416 } 417 return u8; 418 } 419 setStringUTF8(PreparedStatement s, int which, String what)420 public static final void setStringUTF8(PreparedStatement s, int which, String what) throws SQLException { 421 if (what == null) { 422 s.setNull(which, db_UnicodeType); 423 } 424 if (db_Derby) { 425 s.setString(which, what); 426 } else { 427 s.setBytes(which, encode_u8(what)); 428 } 429 } 430 prepareUTF8(String what)431 public static final Object prepareUTF8(String what) { 432 if (what == null) return null; 433 if (db_Derby) { 434 return what; // sanity 435 } else { 436 return encode_u8(what); 437 } 438 } 439 440 /** 441 * Returns an integer value (such as a count) from the specified sql. 442 * @param sql 443 * @param args 444 * @return 445 */ sqlCount(String sql, Object... args)446 public static int sqlCount(String sql, Object... args) { 447 Connection conn = null; 448 try { 449 conn = DBUtils.getInstance().getDBConnection(); 450 return sqlCount(conn, sql, args); 451 } finally { 452 DBUtils.close(conn); 453 } 454 } 455 sqlCount(Connection conn, String sql, Object... args)456 public static int sqlCount(Connection conn, String sql, Object... args) { 457 PreparedStatement ps = null; 458 try { 459 ps = prepareForwardReadOnly(conn, sql); 460 setArgs(ps, args); 461 return sqlCount(conn, ps); 462 } catch (SQLException sqe) { 463 SurveyLog.logException(sqe, "running sqlcount " + sql); 464 return -1; 465 } finally { 466 DBUtils.close(ps); 467 } 468 } 469 hasTable(String table)470 public static boolean hasTable(String table) { 471 Connection conn = null; 472 try { 473 conn = DBUtils.getInstance().getDBConnection(); 474 return hasTable(conn, table); 475 } finally { 476 DBUtils.close(conn); 477 } 478 } 479 sqlCount(Connection conn, PreparedStatement ps)480 static int sqlCount(Connection conn, PreparedStatement ps) throws SQLException { 481 int rv = -1; 482 ResultSet rs = ps.executeQuery(); 483 if (rs.next()) { 484 rv = rs.getInt(1); 485 } 486 rs.close(); 487 return rv; 488 } 489 sqlCount(WebContext ctx, Connection conn, PreparedStatement ps)490 static int sqlCount(WebContext ctx, Connection conn, PreparedStatement ps) { 491 try { 492 return sqlCount(conn, ps); 493 } catch (SQLException se) { 494 String complaint = " Couldn't query count - " + unchainSqlException(se) + " - ps"; 495 System.err.println(complaint); 496 ctx.println("<hr><font color='red'>ERR: " + complaint + "</font><hr>"); 497 return -1; 498 } 499 } 500 sqlCount(WebContext ctx, Connection conn, String sql)501 static int sqlCount(WebContext ctx, Connection conn, String sql) { 502 int rv = -1; 503 try { 504 Statement s = conn.createStatement(); 505 ResultSet rs = s.executeQuery(sql); 506 if (rs.next()) { 507 rv = rs.getInt(1); 508 } 509 rs.close(); 510 s.close(); 511 } catch (SQLException se) { 512 String complaint = " Couldn't query count - " + unchainSqlException(se) + " - " + sql; 513 System.err.println(complaint); 514 ctx.println("<hr><font color='red'>ERR: " + complaint + "</font><hr>"); 515 } 516 return rv; 517 } 518 sqlQueryArray(Connection conn, String str)519 public static String[] sqlQueryArray(Connection conn, String str) throws SQLException { 520 return sqlQueryArrayArray(conn, str)[0]; 521 } 522 sqlQueryArrayArray(Connection conn, String str)523 public static String[][] sqlQueryArrayArray(Connection conn, String str) throws SQLException { 524 Statement s = null; 525 ResultSet rs = null; 526 try { 527 s = conn.createStatement(); 528 try { 529 rs = s.executeQuery(str); 530 } catch (SQLException se) { 531 SurveyLog.logException(se, "Error [SQL was: " + str + "]"); 532 throw se; // rethrow 533 } 534 ArrayList<String[]> al = new ArrayList<>(); 535 while (rs.next()) { 536 al.add(arrayOfResult(rs)); 537 } 538 return al.toArray(new String[al.size()][]); 539 } finally { 540 if (rs != null) { 541 rs.close(); 542 } 543 if (s != null) { 544 s.close(); 545 } 546 } 547 } 548 549 // 550 // private String[] arrayOfResult(ResultSet rs) throws SQLException { 551 // ResultSetMetaData rsm = rs.getMetaData(); 552 // String ret[] = new String[rsm.getColumnCount()]; 553 // for(int i=0;i<ret.length;i++) { 554 // ret[i]=rs.getString(i+1); 555 // } 556 // return ret; 557 // } sqlQuery(Connection conn, String str)558 public static String sqlQuery(Connection conn, String str) throws SQLException { 559 return sqlQueryArray(conn, str)[0]; 560 } 561 sqlUpdate(WebContext ctx, Connection conn, PreparedStatement ps)562 public static int sqlUpdate(WebContext ctx, Connection conn, PreparedStatement ps) { 563 int rv = -1; 564 try { 565 rv = ps.executeUpdate(); 566 } catch (SQLException se) { 567 String complaint = " Couldn't sqlUpdate - " + unchainSqlException(se) + " - ps"; 568 System.err.println(complaint); 569 ctx.println("<hr><font color='red'>ERR: " + complaint + "</font><hr>"); 570 } 571 return rv; 572 } 573 unchainSqlException(SQLException e)574 public static final String unchainSqlException(SQLException e) { 575 String echain = "SQL exception: \n "; 576 SQLException laste = null; 577 while (e != null) { 578 laste = e; 579 echain = echain + " -\n " + e.toString(); 580 e = e.getNextException(); 581 } 582 String stackStr = "\n unknown Stack"; 583 try { 584 StringWriter asString = new StringWriter(); 585 laste.printStackTrace(new PrintWriter(asString)); 586 stackStr = "\n Stack: \n " + asString.toString(); 587 } catch (Throwable tt) { 588 stackStr = "\n unknown stack (" + tt.toString() + ")"; 589 } 590 return echain + stackStr; 591 } 592 593 File dbDir = null; 594 // File dbDir_u = null; 595 static String dbInfo = null; 596 isBogus()597 public boolean isBogus() { 598 return (datasource == null); 599 } 600 DBUtils()601 private DBUtils() { 602 // Initialize DB context 603 System.err.println("Loading datasource: java:comp/env " + JDBC_SURVEYTOOL); 604 ElapsedTimer et = new ElapsedTimer(); 605 try { 606 Context initialContext = new InitialContext(); 607 Context eCtx = (Context) initialContext.lookup("java:comp/env"); 608 datasource = (DataSource) eCtx.lookup(JDBC_SURVEYTOOL); 609 // datasource = (DataSource) envContext.lookup("ASDSDASDASDASD"); 610 611 if (datasource != null) { 612 System.err.println("Got datasource: " + datasource.toString() + " in " + et); 613 } 614 Connection c = null; 615 try { 616 if (datasource != null) { 617 c = datasource.getConnection(); 618 DatabaseMetaData dmd = c.getMetaData(); 619 dbInfo = dmd.getDatabaseProductName() + " v" + dmd.getDatabaseProductVersion() + " " + 620 "driver " + dmd.getDriverName() + " ver " + dmd.getDriverVersion(); 621 setupSqlForServerType(); 622 SurveyLog.debug("Metadata: " + dbInfo); 623 } 624 } catch (SQLException t) { 625 datasource = null; 626 throw new IllegalArgumentException(getClass().getName() + ": WARNING: we require a JNDI datasource. " + "'" 627 + JDBC_SURVEYTOOL + "'" + ".getConnection() returns : " + t.toString() + "\n" + unchainSqlException(t)); 628 } finally { 629 if (c != null) 630 try { 631 c.close(); 632 } catch (Throwable tt) { 633 System.err.println("Couldn't close datasource's conn: " + tt.toString()); 634 tt.printStackTrace(); 635 } 636 } 637 } catch (NamingException nc) { 638 nc.printStackTrace(); 639 datasource = null; 640 throw new Error("Couldn't load context " + JDBC_SURVEYTOOL + " - not using datasource.", nc); 641 } 642 } 643 DBUtils(DataSource dataSource2, String curl)644 public DBUtils(DataSource dataSource2, String curl) { 645 datasource = dataSource2; 646 Connection c = null; 647 try { 648 if (datasource != null) { 649 c = datasource.getConnection(); 650 } else if(curl != null && !curl.isEmpty()) { 651 this.connectionUrl = curl; 652 c = getDBConnection(); 653 DatabaseMetaData dmd = c.getMetaData(); 654 dbInfo = dmd.getDatabaseProductName() + " v" + dmd.getDatabaseProductVersion(); 655 setupSqlForServerType(); 656 } else { 657 throw new NullPointerException("DBUtils(): DataSource and URL are both null/empty"); 658 } 659 DatabaseMetaData dmd = c.getMetaData(); 660 dbInfo = dmd.getDatabaseProductName() + " v" + dmd.getDatabaseProductVersion(); 661 setupSqlForServerType(); 662 c.setAutoCommit(false); 663 boolean autoCommit = c.getAutoCommit(); 664 if (autoCommit == true) { 665 throw new IllegalArgumentException("autoCommit was true, expected false. Check your configuration."); 666 } 667 SurveyLog.debug("Metadata: " + dbInfo + ", autocommit: " + autoCommit); 668 } catch (SQLException t) { 669 datasource = null; 670 throw new IllegalArgumentException(getClass().getName() + ": WARNING: we require a JNDI datasource. " + "'" 671 + JDBC_SURVEYTOOL + "'" + ".getConnection() returns : " + t.toString() + "\n" + unchainSqlException(t)); 672 } finally { 673 if (c != null) 674 try { 675 c.close(); 676 } catch (Throwable tt) { 677 System.err.println("Couldn't close datasource's conn: " + tt.toString()); 678 tt.printStackTrace(); 679 } 680 } 681 } 682 setupSqlForServerType()683 private void setupSqlForServerType() { 684 SurveyLog.debug("setting up SQL for database type " + dbInfo); 685 System.err.println("setting up SQL for database type " + dbInfo); 686 if (dbInfo.contains("Derby")) { 687 db_Derby = true; 688 System.err.println("Note: Derby (embedded) mode. ** some features may not work as expected **"); 689 db_UnicodeType = java.sql.Types.VARCHAR; 690 } else if (dbInfo.contains("MySQL")) { 691 System.err.println("Note: MySQL mode"); 692 db_Mysql = true; 693 DB_SQL_IDENTITY = "AUTO_INCREMENT PRIMARY KEY"; 694 DB_SQL_BINCOLLATE = " COLLATE latin1_bin "; 695 DB_SQL_MB4 = " CHARACTER SET utf8mb4 COLLATE utf8mb4_bin"; 696 DB_SQL_VARCHARXPATH = "TEXT(1000)"; 697 DB_SQL_WITHDEFAULT = "DEFAULT"; 698 DB_SQL_TIMESTAMP0 = "DATETIME"; 699 DB_SQL_LAST_MOD_TYPE = "TIMESTAMP"; 700 DB_SQL_LAST_MOD = " last_mod TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP "; 701 DB_SQL_CURRENT_TIMESTAMP0 = "'1999-12-31 23:59:59'"; // NOW? 702 DB_SQL_MIDTEXT = "TEXT(1024)"; 703 DB_SQL_BIGTEXT = "TEXT(16384)"; 704 DB_SQL_UNICODE = "BLOB"; 705 db_UnicodeType = java.sql.Types.BLOB; 706 DB_SQL_ALLTABLES = "show tables"; 707 708 DB_SQL_ENGINE_INNO = "ENGINE=InnoDB"; 709 } else { 710 System.err.println("*** WARNING: Don't know what kind of database is " + dbInfo + " - don't know what kind of hacky nonportable SQL to use!"); 711 } 712 } 713 doShutdown()714 public void doShutdown() throws SQLException { 715 try { 716 DBUtils.close(datasource); 717 } catch (IllegalArgumentException iae) { 718 System.err.println("DB Shutdown in progress, ignoring: " + iae); 719 } 720 datasource = null; 721 if (db_Derby) { 722 try { 723 DriverManager.getConnection("jdbc:derby:;shutdown=true"); 724 } catch (Throwable t) { 725 // ignore 726 } 727 } 728 if (DBUtils.db_number_open > 0) { 729 System.err.println("DBUtils: removing my instance. " + DBUtils.db_number_open + " still open?\n" + tracker); 730 } 731 if (tracker != null) 732 tracker.clear(); 733 instance = null; 734 } 735 736 /** 737 * @deprecated Use {@link #getDBConnection()} instead 738 */ 739 @Deprecated getDBConnection(SurveyMain surveyMain)740 public final Connection getDBConnection(SurveyMain surveyMain) { 741 return getDBConnection(); 742 } 743 744 /** 745 * This connection MAY NOT be held in an object. Hold it and then close it ( DBUtils.close() ) 746 * @return 747 */ getDBConnection()748 public final Connection getDBConnection() { 749 return getDBConnection(""); 750 } 751 752 /** 753 * @deprecated Use {@link #getDBConnection(String)} instead 754 */ 755 @Deprecated getDBConnection(SurveyMain surveyMain, String options)756 public final Connection getDBConnection(SurveyMain surveyMain, String options) { 757 return getDBConnection(options); 758 } 759 760 long lastMsg = -1; 761 private int db_max_open = 0; 762 getDBConnection(String options)763 public Connection getDBConnection(String options) { 764 if(connectionUrl != null) { 765 try { 766 Connection c = DriverManager.getConnection(connectionUrl); 767 c.setAutoCommit(false); 768 return c; 769 } catch (SQLException e) { 770 throw new RuntimeException("getConnection() failed for url", e); 771 } 772 } 773 try { 774 db_max_open = Math.max(db_max_open, db_number_open++); 775 db_number_used++; 776 777 if (DEBUG) { 778 long now = System.currentTimeMillis(); 779 if (now - lastMsg > (DEBUG_QUICKLY ? 6000 : 3600000) /* 780 * || ( 781 * db_number_used 782 * ==5000) 783 */) { 784 lastMsg = now; 785 System.err.println("DBUtils: " + db_number_open + " open, " + db_max_open + " max, " + db_number_used 786 + " used. " + StackTracker.currentStack()); 787 } 788 } 789 790 Connection c = datasource.getConnection(); 791 c.setAutoCommit(false); 792 if (SurveyMain.isUnofficial() && tracker != null) 793 tracker.add(c); 794 return c; 795 } catch (SQLException se) { 796 se.printStackTrace(); 797 SurveyMain.busted("Fatal in getDBConnection", se); 798 return null; 799 } 800 } 801 setupDBProperties(SurveyMain surveyMain, CLDRConfig survprops)802 void setupDBProperties(SurveyMain surveyMain, CLDRConfig survprops) { 803 // db_driver = cldrprops.getProperty("CLDR_DB_DRIVER", 804 // "org.apache.derby.jdbc.EmbeddedDriver"); 805 // db_protocol = cldrprops.getProperty("CLDR_DB_PROTOCOL", 806 // "jdbc:derby:"); 807 // CLDR_DB_U = cldrprops.getProperty("CLDR_DB_U", null); 808 // CLDR_DB_P = cldrprops.getProperty("CLDR_DB_P", null); 809 // CLDR_DB = survprops.getProperty("CLDR_DB", "cldrdb"); 810 // dbDir = new File(SurveyMain.cldrHome, CLDR_DB); 811 // cldrdb = survprops.getProperty("CLDR_DB_LOCATION", 812 // dbDir.getAbsolutePath()); 813 CLDR_DB_CREATESUFFIX = survprops.getProperty("CLDR_DB_CREATESUFFIX", ";create=true"); 814 CLDR_DB_SHUTDOWNSUFFIX = survprops.getProperty("CLDR_DB_SHUTDOWNSUFFIX", "jdbc:derby:;shutdown=true"); 815 } 816 startupDB(SurveyMain sm, CLDRProgressIndicator.CLDRProgressTask progress)817 public void startupDB(SurveyMain sm, CLDRProgressIndicator.CLDRProgressTask progress) { 818 System.err.println("StartupDB: datasource=" + datasource); 819 if (datasource == null) { 820 throw new RuntimeException(" - JNDI required: " + getDbBrokenMessage()); 821 } 822 823 progress.update("Using datasource..." + dbInfo); // restore 824 825 } 826 827 /** 828 * Shortcut for certain statements. 829 * 830 * @param conn 831 * @param str 832 * @return 833 * @throws SQLException 834 */ prepareForwardReadOnly(Connection conn, String str)835 public static final PreparedStatement prepareForwardReadOnly(Connection conn, String str) throws SQLException { 836 if (DEBUG_SQL) System.out.println("SQL: " + str); 837 return conn.prepareStatement(str, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 838 } 839 840 /** 841 * Shortcut for certain statements. 842 * 843 * @param conn 844 * @param str 845 * @return 846 * @throws SQLException 847 */ prepareForwardUpdateable(Connection conn, String str)848 public static final PreparedStatement prepareForwardUpdateable(Connection conn, String str) throws SQLException { 849 return conn.prepareStatement(str, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); 850 } 851 852 /** 853 * prepare statements for this connection 854 * 855 * @throws SQLException 856 **/ prepareStatementForwardReadOnly(Connection conn, String name, String sql)857 public static final PreparedStatement prepareStatementForwardReadOnly(Connection conn, String name, String sql) 858 throws SQLException { 859 PreparedStatement ps = null; 860 try { 861 ps = prepareForwardReadOnly(conn, sql); 862 } finally { 863 if (ps == null) { 864 System.err.println("Warning: couldn't initialize " + name + " from " + sql); 865 } 866 } 867 // if(false) System.out.println("EXPLAIN EXTENDED " + 868 // sql.replaceAll("\\?", "'?'")+";"); 869 // } catch ( SQLException se ) { 870 // String complaint = "Vetter: Couldn't prepare " + name + " - " + 871 // DBUtils.unchainSqlException(se) + " - " + sql; 872 // logger.severe(complaint); 873 // throw new RuntimeException(complaint); 874 // } 875 return ps; 876 } 877 878 /** 879 * prepare statements for this connection. Assumes generated keys. 880 * 881 * @throws SQLException 882 **/ prepareStatement(Connection conn, String name, String sql)883 public static final PreparedStatement prepareStatement(Connection conn, String name, String sql) throws SQLException { 884 PreparedStatement ps = null; 885 try { 886 ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 887 } finally { 888 if (ps == null) { 889 System.err.println("Warning: couldn't initialize " + name + " from " + sql); 890 } 891 } 892 // if(false) System.out.println("EXPLAIN EXTENDED " + 893 // sql.replaceAll("\\?", "'?'")+";"); 894 // } catch ( SQLException se ) { 895 // String complaint = "Vetter: Couldn't prepare " + name + " - " + 896 // DBUtils.unchainSqlException(se) + " - " + sql; 897 // logger.severe(complaint); 898 // throw new RuntimeException(complaint); 899 // } 900 return ps; 901 } 902 903 /** 904 * Close all of the objects in order, if not null. Knows how to close 905 * Connection, Statement, ResultSet, otherwise you'll get an IAE. 906 * 907 * @param a1 908 * @throws SQLException 909 */ close(Object... list)910 public static void close(Object... list) { 911 for (Object o : list) { 912 // if(o!=null) { 913 // System.err.println("Closing " + 914 // an(o.getClass().getSimpleName())+" " + o.getClass().getName()); 915 // } 916 try { 917 if (o == null) { 918 continue; 919 } else if (o instanceof Connection) { 920 DBUtils.closeDBConnection((Connection) o); 921 } else if (o instanceof Statement) { 922 ((Statement) o).close(); 923 } else if (o instanceof ResultSet) { 924 ((ResultSet) o).close(); 925 } else if (o instanceof DBCloseable) { 926 ((DBCloseable) o).close(); 927 } else { 928 final Class theClass = o.getClass(); 929 final String simpleName = theClass.getSimpleName(); 930 if (simpleName.equals("BasicDataSource")) { // could expand this later, if we want to generically call close() 931 try { 932 // try to find a "close" 933 final Method m = theClass.getDeclaredMethod("close"); 934 if (m != null) { 935 System.err.println("Attempting to call close() on " + theClass.getName()); 936 m.invoke(o); 937 } 938 } catch (Exception nsm) { 939 nsm.printStackTrace(); 940 System.err.println("Caught exception " + nsm + " - so, don't know how to close " + an(simpleName) + " " 941 + theClass.getName()); 942 } 943 } else { 944 throw new IllegalArgumentException("Don't know how to close " + an(simpleName) + " " 945 + theClass.getName()); 946 } 947 } 948 } catch (SQLException e) { 949 System.err.println(unchainSqlException(e)); 950 } 951 } 952 } 953 954 private static final UnicodeSet vowels = new UnicodeSet("[aeiouAEIOUhH]"); 955 956 /** 957 * Print A or AN appropriately. 958 * 959 * @param str 960 * @return 961 */ an(String str)962 private static String an(String str) { 963 boolean isVowel = vowels.contains(str.charAt(0)); 964 return isVowel ? "an" : "a"; 965 } 966 hasDataSource()967 public boolean hasDataSource() { 968 return (datasource != null); 969 } 970 971 /** 972 * @param conn 973 * @param sql 974 * @param args 975 * @return 976 * @throws SQLException 977 */ prepareStatementWithArgs(Connection conn, String sql, Object... args)978 public static PreparedStatement prepareStatementWithArgs(Connection conn, String sql, Object... args) throws SQLException { 979 PreparedStatement ps; 980 ps = conn.prepareStatement(sql); 981 982 // while (args!=null&&args.length==1&&args[0] instanceof Object[]) { 983 // System.err.println("Unwrapping " + args + " to " + args[0]); 984 // } 985 setArgs(ps, args); 986 return ps; 987 } 988 989 /** 990 * @param conn 991 * @param sql 992 * @param args 993 * @return 994 * @throws SQLException 995 */ prepareStatementWithArgsFRO(Connection conn, String sql, Object... args)996 public static PreparedStatement prepareStatementWithArgsFRO(Connection conn, String sql, Object... args) throws SQLException { 997 PreparedStatement ps; 998 ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 999 1000 setArgs(ps, args); 1001 return ps; 1002 } 1003 1004 /** 1005 * @param ps 1006 * @param args 1007 * @throws SQLException 1008 */ setArgs(PreparedStatement ps, Object... args)1009 public static void setArgs(PreparedStatement ps, Object... args) throws SQLException { 1010 if (args != null) { 1011 for (int i = 0; i < args.length; i++) { 1012 Object o = args[i]; 1013 if (o == null) { 1014 ps.setNull(i + 1, java.sql.Types.NULL); 1015 } else if (o instanceof String) { 1016 ps.setString(i + 1, (String) o); 1017 } else if (o instanceof byte[]) { 1018 ps.setBytes(i + 1, (byte[]) o); 1019 } else if (o instanceof Integer) { 1020 ps.setInt(i + 1, (Integer) o); 1021 } else if (o instanceof java.sql.Date) { 1022 ps.setDate(i + 1, (java.sql.Date) o); 1023 } else if (o instanceof java.sql.Timestamp) { 1024 ps.setTimestamp(i + 1, (java.sql.Timestamp) o); 1025 } else if (o instanceof CLDRLocale) { /* 1026 * toString compatible 1027 * things 1028 */ 1029 ps.setString(i + 1, ((CLDRLocale) o).getBaseName()); 1030 } else { 1031 System.err.println("DBUtils: Warning: using toString for unknown object " + o.getClass().getName()); 1032 ps.setString(i + 1, o.toString()); 1033 } 1034 } 1035 } 1036 } 1037 resultToArrayArray(ResultSet rs)1038 public static String[][] resultToArrayArray(ResultSet rs) throws SQLException { 1039 ArrayList<String[]> al = new ArrayList<>(); 1040 while (rs.next()) { 1041 al.add(arrayOfResult(rs)); 1042 } 1043 return al.toArray(new String[al.size()][]); 1044 } 1045 resultToArrayArrayObj(ResultSet rs)1046 public static Object[][] resultToArrayArrayObj(ResultSet rs) throws SQLException { 1047 ArrayList<Object[]> al = new ArrayList<>(); 1048 ResultSetMetaData rsm = rs.getMetaData(); 1049 int colCount = rsm.getColumnCount(); 1050 while (rs.next()) { 1051 al.add(arrayOfResultObj(rs, colCount, rsm)); 1052 } 1053 return al.toArray(new Object[al.size()][]); 1054 } 1055 1056 @SuppressWarnings("unchecked") resultToArrayAssoc(ResultSet rs)1057 private static Map<String, Object>[] resultToArrayAssoc(ResultSet rs) throws SQLException { 1058 ResultSetMetaData rsm = rs.getMetaData(); 1059 ArrayList<Map<String, Object>> al = new ArrayList<>(); 1060 while (rs.next()) { 1061 al.add(assocOfResult(rs, rsm)); 1062 } 1063 return al.toArray(new Map[al.size()]); 1064 } 1065 assocOfResult(ResultSet rs)1066 public static Map<String, Object> assocOfResult(ResultSet rs) throws SQLException { 1067 return assocOfResult(rs, rs.getMetaData()); 1068 } 1069 assocOfResult(ResultSet rs, ResultSetMetaData rsm)1070 private static Map<String, Object> assocOfResult(ResultSet rs, ResultSetMetaData rsm) throws SQLException { 1071 Map<String, Object> m = new HashMap<>(rsm.getColumnCount()); 1072 for (int i = 1; i <= rsm.getColumnCount(); i++) { 1073 Object obj = extractObject(rs, rsm, i); 1074 m.put(rsm.getColumnName(i).toLowerCase(), obj); 1075 } 1076 1077 return m; 1078 } 1079 1080 /** 1081 * @param rs 1082 * @param rsm 1083 * @param i 1084 * @return 1085 * @throws SQLException 1086 */ extractObject(ResultSet rs, ResultSetMetaData rsm, int i)1087 private static Object extractObject(ResultSet rs, ResultSetMetaData rsm, int i) throws SQLException { 1088 Object obj = null; 1089 int colType = rsm.getColumnType(i); 1090 if (colType == java.sql.Types.BLOB) { 1091 obj = DBUtils.getStringUTF8(rs, i); 1092 } else if (colType == java.sql.Types.TIMESTAMP) { 1093 obj = rs.getTimestamp(i); 1094 } else if (colType == java.sql.Types.DATE) { 1095 obj = rs.getDate(i); 1096 } else { // generic 1097 obj = rs.getObject(i); 1098 if (obj != null && obj.getClass().isArray()) { 1099 obj = DBUtils.getStringUTF8(rs, i); 1100 } 1101 } 1102 return obj; 1103 } 1104 sqlQuery(Connection conn, String sql, Object... args)1105 public static String sqlQuery(Connection conn, String sql, Object... args) throws SQLException { 1106 return sqlQueryArray(conn, sql, args)[0]; 1107 } 1108 sqlQueryArray(Connection conn, String sql, Object... args)1109 public static String[] sqlQueryArray(Connection conn, String sql, Object... args) throws SQLException { 1110 return sqlQueryArrayArray(conn, sql, args)[0]; 1111 } 1112 sqlQueryArrayArray(Connection conn, String str, Object... args)1113 public static String[][] sqlQueryArrayArray(Connection conn, String str, Object... args) throws SQLException { 1114 PreparedStatement ps = null; 1115 ResultSet rs = null; 1116 try { 1117 ps = prepareStatementWithArgs(conn, str, args); 1118 1119 rs = ps.executeQuery(); 1120 return resultToArrayArray(rs); 1121 } finally { 1122 DBUtils.close(rs, ps); 1123 } 1124 } 1125 sqlQueryArrayArrayObj(Connection conn, String str, Object... args)1126 public static Object[][] sqlQueryArrayArrayObj(Connection conn, String str, Object... args) throws SQLException { 1127 PreparedStatement ps = null; 1128 ResultSet rs = null; 1129 try { 1130 ps = prepareStatementWithArgs(conn, str, args); 1131 1132 rs = ps.executeQuery(); 1133 return resultToArrayArrayObj(rs); 1134 } finally { 1135 DBUtils.close(rs, ps); 1136 } 1137 } 1138 sqlUpdate(Connection conn, String str, Object... args)1139 public static int sqlUpdate(Connection conn, String str, Object... args) throws SQLException { 1140 PreparedStatement ps = null; 1141 try { 1142 ps = prepareStatementWithArgs(conn, str, args); 1143 1144 return (ps.executeUpdate()); 1145 } finally { 1146 DBUtils.close(ps); 1147 } 1148 } 1149 1150 @SuppressWarnings("rawtypes") sqlQueryArrayAssoc(Connection conn, String sql, Object... args)1151 public Map[] sqlQueryArrayAssoc(Connection conn, String sql, Object... args) throws SQLException { 1152 PreparedStatement ps = null; 1153 ResultSet rs = null; 1154 try { 1155 ps = prepareStatementWithArgs(conn, sql, args); 1156 1157 rs = ps.executeQuery(); 1158 return resultToArrayAssoc(rs); 1159 } finally { 1160 DBUtils.close(rs, ps); 1161 } 1162 } 1163 1164 /** 1165 * Standardized way of versioning a string. 1166 * @param sb 1167 * @param forVersion 1168 * @param isBeta 1169 * @return 1170 */ appendVersionString(StringBuilder sb, String forVersion, Boolean isBeta)1171 public static StringBuilder appendVersionString(StringBuilder sb, String forVersion, Boolean isBeta) { 1172 if (forVersion != null) { 1173 sb.append('_'); 1174 sb.append(forVersion.toLowerCase()); 1175 } 1176 if (isBeta != null && isBeta) { 1177 sb.append("_beta"); 1178 } 1179 return sb; 1180 } 1181 1182 /** 1183 * Append a versioned string 1184 */ appendVersionString(StringBuilder sb)1185 public static StringBuilder appendVersionString(StringBuilder sb) { 1186 return appendVersionString(sb, SurveyMain.getNewVersion(), SurveyMain.phase() == SurveyMain.Phase.BETA); 1187 } 1188 arrayOfResult(ResultSet rs)1189 private static String[] arrayOfResult(ResultSet rs) throws SQLException { 1190 ResultSetMetaData rsm = rs.getMetaData(); 1191 String ret[] = new String[rsm.getColumnCount()]; 1192 for (int i = 0; i < ret.length; i++) { 1193 ret[i] = rs.getString(i + 1); 1194 } 1195 return ret; 1196 } 1197 arrayOfResultObj(ResultSet rs, int colCount, ResultSetMetaData rsm)1198 private static Object[] arrayOfResultObj(ResultSet rs, int colCount, ResultSetMetaData rsm) throws SQLException { 1199 Object ret[] = new Object[colCount]; 1200 for (int i = 0; i < ret.length; i++) { 1201 Object obj = extractObject(rs, rsm, i + 1); 1202 ret[i] = obj; 1203 } 1204 return ret; 1205 } 1206 1207 /** 1208 * Interface to an object that contains a held Connection 1209 * 1210 * @author srl 1211 * 1212 */ 1213 public interface ConnectionHolder { 1214 /** 1215 * @return alias to held connection 1216 */ getConnectionAlias()1217 public Connection getConnectionAlias(); 1218 } 1219 1220 /** 1221 * Interface to an object that DBUtils.close can close. 1222 * 1223 * @author srl 1224 * 1225 */ 1226 public interface DBCloseable { 1227 /** 1228 * Close this object 1229 */ close()1230 public void close() throws SQLException; 1231 } 1232 writeCsv(ResultSet rs, Writer out)1233 public static void writeCsv(ResultSet rs, Writer out) throws SQLException, IOException { 1234 ResultSetMetaData rsm = rs.getMetaData(); 1235 int cc = rsm.getColumnCount(); 1236 for (int i = 1; i <= cc; i++) { 1237 if (i > 1) { 1238 out.write(','); 1239 } 1240 WebContext.csvWrite(out, rsm.getColumnName(i).toUpperCase()); 1241 } 1242 out.write('\r'); 1243 out.write('\n'); 1244 1245 while (rs.next()) { 1246 for (int i = 1; i <= cc; i++) { 1247 if (i > 1) { 1248 out.write(','); 1249 } 1250 String v; 1251 try { 1252 v = rs.getString(i); 1253 } catch (SQLException se) { 1254 if (se.getSQLState().equals("S1009")) { 1255 v = "0000-00-00 00:00:00"; 1256 } else { 1257 throw se; 1258 } 1259 } 1260 if (v != null) { 1261 if (rsm.getColumnType(i) == java.sql.Types.LONGVARBINARY) { 1262 String uni = DBUtils.getStringUTF8(rs, i); 1263 WebContext.csvWrite(out, uni); 1264 } else { 1265 WebContext.csvWrite(out, v); 1266 } 1267 } 1268 } 1269 out.write('\r'); 1270 out.write('\n'); 1271 } 1272 } 1273 getJSON(ResultSet rs)1274 public static JSONObject getJSON(ResultSet rs) throws SQLException, IOException, JSONException { 1275 ResultSetMetaData rsm = rs.getMetaData(); 1276 int cc = rsm.getColumnCount(); 1277 JSONObject ret = new JSONObject(); 1278 JSONObject header = new JSONObject(); 1279 JSONArray data = new JSONArray(); 1280 //JSONArray rsm2 = new JSONArray(); 1281 1282 int hasxpath = -1; 1283 int haslocale = -1; 1284 1285 for (int i = 1; i <= cc; i++) { 1286 String colname = rsm.getColumnName(i).toUpperCase(); 1287 final int columnType = rsm.getColumnType(i); 1288 if (colname.equals("XPATH") && 1289 (columnType == java.sql.Types.INTEGER)) { 1290 hasxpath = i; 1291 } 1292 if (colname.equals("LOCALE")) 1293 haslocale = i; 1294 header.put(colname, i - 1); 1295 // rsm2.put(i-1, rsm.getColumnType(i)); 1296 } 1297 int cn = cc; 1298 if (hasxpath >= 0) { 1299 header.put("XPATH_STRING", cn++); 1300 header.put("XPATH_STRHASH", cn++); 1301 header.put("XPATH_CODE", cn++); 1302 } 1303 if (haslocale >= 0) { 1304 header.put("LOCALE_NAME", cn++); 1305 } 1306 1307 ret.put("header", header); 1308 //ret.put("types", rsm2); 1309 final STFactory stFactory = CookieSession.sm.getSTFactory(); 1310 1311 while (rs.next()) { 1312 JSONArray item = new JSONArray(); 1313 Integer xpath = null; 1314 String locale_name = null; 1315 for (int i = 1; i <= cc; i++) { 1316 String v; 1317 try { 1318 v = rs.getString(i); 1319 if (i == hasxpath && v != null) { 1320 xpath = rs.getInt(i); 1321 } 1322 if (i == haslocale && v != null) { 1323 locale_name = CLDRLocale.getInstance(v).getDisplayName(); 1324 } 1325 } catch (SQLException se) { 1326 if (se.getSQLState().equals("S1009")) { 1327 v = "0000-00-00 00:00:00"; 1328 } else { 1329 throw se; 1330 } 1331 } 1332 if (v != null) { 1333 int type = rsm.getColumnType(i); 1334 switch (type) { 1335 case java.sql.Types.LONGVARBINARY: 1336 String uni = DBUtils.getStringUTF8(rs, i); 1337 item.put(uni); 1338 break; 1339 case java.sql.Types.INTEGER: 1340 case java.sql.Types.TINYINT: 1341 case java.sql.Types.BIGINT: 1342 item.put(rs.getInt(i)); 1343 break; 1344 case java.sql.Types.TIMESTAMP: 1345 item.put(rs.getTimestamp(i).getTime()); // truncates 1346 break; 1347 default: 1348 item.put(v); 1349 } 1350 } else { 1351 item.put(false); 1352 } 1353 } 1354 if (hasxpath >= 0 && xpath != null) { 1355 final String xpathString = CookieSession.sm.xpt.getById(xpath); 1356 item.put(xpathString != null ? xpathString : ""); // XPATH_STRING 1357 item.put(xpathString != null ? (XPathTable.getStringIDString(xpathString)) : ""); // add 1358 // XPATH_STRHASH 1359 // column 1360 if (xpathString == null || xpathString.isEmpty()) { 1361 item.put(""); 1362 } else { 1363 final PathHeader ph = stFactory.getPathHeader(xpathString); 1364 if (ph != null) { 1365 item.put(ph.toString()); // add XPATH_CODE 1366 } else { 1367 item.put(""); 1368 } 1369 } 1370 } 1371 if (haslocale >= 0 && locale_name != null) { 1372 item.put(locale_name); // add LOCALE_NAME column 1373 } 1374 data.put(item); 1375 } 1376 ret.put("data", data); 1377 return ret; 1378 } 1379 queryToJSON(String string, Object... args)1380 public static JSONObject queryToJSON(String string, Object... args) throws SQLException, IOException, JSONException { 1381 return queryToJSONLimit(null, string, args); 1382 } 1383 queryToJSONLimit(Integer limit, String string, Object... args)1384 public static JSONObject queryToJSONLimit(Integer limit, String string, Object... args) throws SQLException, IOException, JSONException { 1385 if (limit != null && DBUtils.db_Mysql) { 1386 string = string + " limit " + limit; 1387 } 1388 Connection conn = null; 1389 PreparedStatement s = null; 1390 ResultSet rs = null; 1391 try { 1392 conn = getInstance().getDBConnection(); 1393 s = DBUtils.prepareForwardReadOnly(conn, string); 1394 setArgs(s, args); 1395 if (limit != null && !DBUtils.db_Mysql) { 1396 s.setMaxRows(limit); 1397 } 1398 rs = s.executeQuery(); 1399 return getJSON(rs); 1400 } finally { 1401 close(rs, s, conn); 1402 } 1403 } 1404 1405 private Map<String, Reference<JSONObject>> cachedJsonQuery = new ConcurrentHashMap<>(); 1406 1407 /** 1408 * Run a query, caching the JSON response 1409 * TODO: cache exceptions.. 1410 * @param id 1411 * @param cacheAge 1412 * @param query 1413 * @param args 1414 * @return 1415 * @throws SQLException 1416 * @throws IOException 1417 * @throws JSONException 1418 */ queryToCachedJSON(String id, long cacheAge, String query, Object... args)1419 public static JSONObject queryToCachedJSON(String id, long cacheAge, String query, Object... args) throws SQLException, IOException, JSONException { 1420 if (SurveyMain.isSetup == false || SurveyMain.isBusted()) { 1421 return null; 1422 } 1423 1424 /** 1425 * Debug the cachedJSON 1426 */ 1427 final boolean CDEBUG = SurveyMain.isUnofficial() && CldrUtility.getProperty("CLDR_QUERY_CACHEDEBUG", false); 1428 DBUtils instance = getInstance(); // don't want the cache to be static 1429 Reference<JSONObject> ref = instance.cachedJsonQuery.get(id); 1430 JSONObject result = null; 1431 if (ref != null) result = ref.get(); 1432 long now = System.currentTimeMillis(); 1433 if (CDEBUG) { 1434 System.out.println("cachedjson: id " + id + " ref=" + ref + "res?" + (result != null)); 1435 } 1436 if (result != null) { 1437 long age = now - (Long) result.get("birth"); 1438 if (age > cacheAge) { 1439 if (CDEBUG) { 1440 System.out.println("cachedjson: id " + id + " expiring because age " + age + " > " + cacheAge); 1441 } 1442 result = null; 1443 } 1444 } 1445 1446 if (result == null) { // have to fetch it 1447 if (CDEBUG) { 1448 System.out.println("cachedjson: id " + id + " fetching: " + query); 1449 } 1450 result = queryToJSON(query, args); 1451 long queryms = System.currentTimeMillis() - now; 1452 result.put("birth", (Long) now); 1453 if (CDEBUG) { 1454 System.out.println("cachedjson: id " + id + " fetched in " + Double.toString(queryms / 1000.0) + "s"); 1455 } 1456 result.put("queryms", (Long) (queryms)); 1457 result.put("id", id); 1458 ref = new SoftReference<>(result); 1459 instance.cachedJsonQuery.put(id, ref); 1460 } 1461 1462 return result; 1463 } 1464 1465 /** 1466 * Get the first row of the first column. Useful when the query is very simple, such as a count. 1467 * @param obj 1468 * @return the int 1469 * @throws JSONException 1470 */ getFirstInt(JSONObject json)1471 public static final int getFirstInt(JSONObject json) throws JSONException { 1472 return json.getJSONArray("data").getJSONArray(0).getInt(0); 1473 } 1474 1475 /** 1476 * query to an array associative maps 1477 * @param string 1478 * @param args 1479 * @return 1480 * @throws SQLException 1481 * @throws IOException 1482 */ queryToArrayAssoc(String string, Object... args)1483 public static Map<String, Object>[] queryToArrayAssoc(String string, Object... args) throws SQLException, IOException { 1484 Connection conn = null; 1485 PreparedStatement s = null; 1486 ResultSet rs = null; 1487 try { 1488 conn = getInstance().getDBConnection(); 1489 s = DBUtils.prepareForwardReadOnly(conn, string); 1490 setArgs(s, args); 1491 rs = s.executeQuery(); 1492 return resultToArrayAssoc(rs); 1493 } finally { 1494 close(rs, s, conn); 1495 } 1496 } 1497 1498 /** 1499 * query to an array of arrays of objects 1500 * @param string 1501 * @param args 1502 * @return 1503 * @throws SQLException 1504 * @throws IOException 1505 */ queryToArrayArrayObj(String string, Object... args)1506 public static Object[][] queryToArrayArrayObj(String string, Object... args) throws SQLException, IOException { 1507 Connection conn = null; 1508 PreparedStatement s = null; 1509 ResultSet rs = null; 1510 try { 1511 conn = getInstance().getDBConnection(); 1512 s = DBUtils.prepareForwardReadOnly(conn, string); 1513 setArgs(s, args); 1514 rs = s.executeQuery(); 1515 return resultToArrayArrayObj(rs); 1516 } finally { 1517 close(rs, s, conn); 1518 } 1519 } 1520 getDbBrokenMessage()1521 public static String getDbBrokenMessage() { 1522 return 1523 "</pre><script src='../js/cldr-setup.js'></script>" + 1524 "see <a href='http://cldr.unicode.org/development/running-survey-tool/cldr-properties/db'> http://cldr.unicode.org/development/running-survey-tool/cldr-properties/db </a>" + 1525 "For MySQL, click: <button onclick='return mysqlhelp()'>MySQL Configurator</button><pre>"; 1526 } 1527 sqlNow()1528 public static java.sql.Timestamp sqlNow() { 1529 return new java.sql.Timestamp(new Date().getTime()); 1530 } 1531 getLastId(PreparedStatement s)1532 public static Integer getLastId(PreparedStatement s) throws SQLException { 1533 if (s == null) return null; 1534 ResultSet rs = s.getGeneratedKeys(); 1535 if (!rs.next()) return null; 1536 return rs.getInt(1); 1537 } 1538 1539 /** 1540 * Table name management. 1541 * Manage table names according to versions. 1542 */ 1543 public enum Table { 1544 /* These constants represent names and other attributes of database tables. 1545 * 1546 * The (false, false) constructor makes isVersioned, hasBeta both false for 1547 * the FORUM_POSTS and FORUM_TYPES tables: these tables are not version-specific. 1548 * 1549 * The LOCKED_XPATHS(false, true) constructor makes isVersioned, hasBeta both false. 1550 * 1551 * Other constants here have default constructor equivalent to (true, true). 1552 */ 1553 VOTE_VALUE, VOTE_VALUE_ALT, VOTE_FLAGGED, FORUM_POSTS(false, false), FORUM_TYPES(false, false), 1554 REVIEW_HIDE, REVIEW_POST, IMPORT, IMPORT_AUTO, LOCKED_XPATHS(false, false); 1555 1556 /** 1557 * Construct a Table constant with explicit parameters for isVersioned, hasBeta. 1558 * 1559 * @param isVersioned true for tables whose name depends on version like cldr_vote_value_33, 1560 * false for tables whose name is version independent, like forum_posts 1561 * @param hasBeta true for tables whose name is different for beta versions like cldr_vote_value_32_beta, 1562 * false for tables whose name doesn't change for beta. 1563 */ Table(boolean isVersioned, boolean hasBeta)1564 Table(boolean isVersioned, boolean hasBeta) { 1565 this.isVersioned = isVersioned; 1566 this.hasBeta = hasBeta; 1567 } 1568 1569 /** 1570 * Construct a Table constant with isVersioned, hasBeta both true. 1571 */ Table()1572 Table() { 1573 this.isVersioned = true; 1574 this.hasBeta = true; 1575 } 1576 1577 final boolean isVersioned, hasBeta; 1578 1579 String defaultString = null; 1580 1581 /** 1582 * High runner case. 1583 * WARNING: Do not use in constant strings 1584 */ 1585 @Override toString()1586 public synchronized String toString() { 1587 if (defaultString == null) { 1588 if (!SurveyMain.isConfigSetup && CLDRConfig.getInstance().getEnvironment() != CLDRConfig.Environment.UNITTEST) { 1589 throw new InternalError("Error: don't use Table.toString before CLDRConfig is setup."); 1590 } 1591 defaultString = forVersion(SurveyMain.getNewVersion(), SurveyMain.phase() == Phase.BETA).toString(); 1592 } 1593 return defaultString; 1594 } 1595 forVersion(String forVersion, boolean isBeta)1596 public CharSequence forVersion(String forVersion, boolean isBeta) { 1597 StringBuilder sb = new StringBuilder("cldr_"); 1598 sb.append(name().toLowerCase()); 1599 DBUtils.appendVersionString(sb, isVersioned ? forVersion : null, hasBeta ? isBeta : null); 1600 return sb; 1601 } 1602 } 1603 1604 static boolean tryUpdates = true; 1605 1606 /** 1607 * 1608 * @param rs 1609 * @param string 1610 * @param sqlnow 1611 * @return false if caller needs to 'manually' update the item. 1612 * @throws SQLException 1613 */ updateTimestamp(ResultSet rs, String string, Timestamp sqlnow)1614 public static final boolean updateTimestamp(ResultSet rs, String string, Timestamp sqlnow) throws SQLException { 1615 if (tryUpdates) { 1616 try { 1617 rs.updateTimestamp(string, sqlnow); 1618 return true; // success- caller doesn't need to do an update. 1619 } catch (SQLFeatureNotSupportedException sfns) { 1620 tryUpdates = false; 1621 SurveyLog.warnOnce("SQL: Apparently updates aren't supported: " + sfns.toString() + " - falling back."); 1622 } 1623 } 1624 return false; // caller needs to do an update 1625 } 1626 1627 /** 1628 * Set an Integer object, either as an int or as a null 1629 * @param ps 1630 * @param i 1631 * @param withVote 1632 * @throws SQLException 1633 */ setInteger(PreparedStatement ps, int i, Integer withVote)1634 public static void setInteger(PreparedStatement ps, int i, Integer withVote) throws SQLException { 1635 if (withVote == null) { 1636 ps.setNull(i, java.sql.Types.INTEGER); 1637 } else { 1638 ps.setInt(i, withVote); 1639 } 1640 } 1641 } 1642