/** * Test importing an unsupported field e.g. datetime, the program should continue and assign null * value to the field. * * @throws Exception */ @Test public void testImportUnsupportedField() throws Exception { ConnectionProperties p = getConnectionProperties(); Connection cn = DatabaseConnection.getConnection(p); try { List<Field> verifiedFields = new Vector<Field>(); String[] fields = "ListingId, Title".split(","); String tableName = "Listings"; DatabaseConnection.verifyTable(p, tableName, fields, verifiedFields); Collection<JsonNode> importNodes = new LinkedList<JsonNode>(); JsonNodeFactory f = JsonNodeFactory.instance; int listingId = 1559350; ObjectNode n; n = new ObjectNode(f); n.put("ListingId", listingId); importNodes.add(n); JsonArrayImporter importer = new JsonArrayImporter(p); importer.doImport(tableName, verifiedFields, importNodes); Statement st = cn.createStatement(); ResultSet rs = st.executeQuery("SELECT ListingId, Title FROM Listings"); assertTrue("Expected result set to contain a record", rs.next()); assertEquals(listingId, rs.getInt("ListingId")); assertEquals(null, rs.getString("Title")); } finally { cn.close(); } }
/** * Special login method - it cannot be assumed that the database schema is unchanged, so do not * use standard methods. */ public static boolean login(String username, String password) throws WikiException { WikiVersion oldVersion = new WikiVersion(Environment.getValue(Environment.PROP_BASE_WIKI_VERSION)); if (!oldVersion.before(0, 7, 0)) { try { return (WikiBase.getDataHandler().authenticate(username, password)); } catch (DataAccessException e) { logger.severe("Unable to authenticate user during upgrade", e); throw new WikiException(new WikiMessage("upgrade.error.fatal", e.getMessage())); } } try { Connection conn = DatabaseConnection.getConnection(); WikiPreparedStatement stmt = new WikiPreparedStatement( "select 1 from jam_wiki_user_info where login = ? and encoded_password = ?"); if (!StringUtils.isBlank(password)) { password = Encryption.encrypt(password); } stmt.setString(1, username); stmt.setString(2, password); WikiResultSet rs = stmt.executeQuery(conn); return (rs.size() > 0); } catch (SQLException e) { logger.severe("Database failure while authenticating user", e); throw new WikiException(new WikiMessage("upgrade.error.fatal", e.getMessage())); } }
public static List<WikiMessage> upgrade063(List<WikiMessage> messages) { TransactionStatus status = null; try { status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); // increase the size of ip address columns WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_063_ALTER_USER_CREATE_IP", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_063_ALTER_USER_LAST_LOGIN_IP", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "create_ip_address", "jam_wiki_user")); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "last_login_ip_address", "jam_wiki_user")); } catch (SQLException e) { // do not throw this error and halt the upgrade process - changing the column size // is not required for systems that have already been successfully installed, it // is simply being done to keep new installs consistent with existing installs. messages.add(new WikiMessage("upgrade.error.nonfatal", e.getMessage())); logger.warning( "Failure while updating database for IPv6 support. See UPGRADE.txt for instructions on how to manually complete this optional step.", e); DatabaseConnection.rollbackOnException(status, e); status = null; // so we do not try to commit } if (status != null) { DatabaseConnection.commit(status); } return messages; }
public static List<WikiMessage> upgrade061(List<WikiMessage> messages) throws WikiException { TransactionStatus status = null; try { status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); // delete ROLE_DELETE WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_061_DELETE_ROLE_MAP_ROLE_DELETE", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_061_DELETE_ROLE_ROLE_DELETE", conn); messages.add(new WikiMessage("upgrade.message.db.data.deleted", "jam_role_map")); messages.add(new WikiMessage("upgrade.message.db.data.deleted", "jam_role")); } catch (SQLException e) { DatabaseConnection.rollbackOnException(status, e); logger.severe("Database failure during upgrade", e); throw new WikiException(new WikiMessage("upgrade.error.fatal", e.getMessage())); } DatabaseConnection.commit(status); return messages; }
/** * Test importing string, int, and decimal values work successfully * * @throws Exception */ @Test public void testImportSimple() throws Exception { ConnectionProperties p = getConnectionProperties(); Connection cn = DatabaseConnection.getConnection(p); try { List<Field> verifiedFields = new Vector<Field>(); String[] fields = "ListingId, Title, StartPrice".split(","); String tableName = "Listings"; DatabaseConnection.verifyTable(p, tableName, fields, verifiedFields); Collection<JsonNode> importNodes = new LinkedList<JsonNode>(); JsonNodeFactory f = JsonNodeFactory.instance; int listingId = 1559350; String title = "Product Pro1-Beta-0066"; BigDecimal startPrice = BigDecimal.valueOf(20.64); ObjectNode n; n = new ObjectNode(f); n.put("ListingId", listingId); n.put("Title", title); n.put("StartPrice", startPrice); importNodes.add(n); JsonArrayImporter importer = new JsonArrayImporter(p); importer.doImport(tableName, verifiedFields, importNodes); Statement st = cn.createStatement(); ResultSet rs = st.executeQuery("SELECT ListingId, Title, StartPrice FROM Listings"); assertTrue("Expected result set to contain a record", rs.next()); assertEquals(listingId, rs.getInt("ListingId")); assertEquals(title, rs.getString("Title")); assertEquals(startPrice, rs.getBigDecimal("StartPrice")); assertFalse("Expect result set to only contain one record", rs.next()); } finally { cn.close(); } }
private static Connection getConnection() throws Exception { // add a connection to the conn array. BE SURE TO RELEASE IT! Connection conn = DatabaseConnection.getConnection(); conn.setAutoCommit(false); return conn; }
private void truncateTable() throws SQLException, ClassNotFoundException { ConnectionProperties p = getConnectionProperties(); Connection cn = DatabaseConnection.getConnection(p); Statement st = cn.createStatement(); st.execute("TRUNCATE TABLE Listings"); }
public static List<WikiMessage> upgrade080(List<WikiMessage> messages) throws WikiException { String dbType = Environment.getValue(Environment.PROP_DB_TYPE); TransactionStatus status = null; try { status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); if (StringUtils.equals(dbType, DataHandler.DATA_HANDLER_POSTGRES)) { WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_GROUP_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_GROUP_ID", conn); WikiBase.getDataHandler().executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_GROUP_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "group_id", "jam_group")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_GROUP_MEMBERS_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_GROUP_MEMBERS_ID", conn); WikiBase.getDataHandler() .executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_GROUP_MEMBERS_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "id", "jam_group_members")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_TOPIC_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_TOPIC_ID", conn); WikiBase.getDataHandler().executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_TOPIC_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "topic_id", "jam_topic")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_TOPIC_VERSION_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_TOPIC_VERSION_ID", conn); WikiBase.getDataHandler() .executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_TOPIC_VERSION_ID", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "topic_version_id", "jam_topic_version")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_VIRTUAL_WIKI_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_VIRTUAL_WIKI_ID", conn); WikiBase.getDataHandler() .executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_VIRTUAL_WIKI_ID", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "virtual_wiki_id", "jam_virtual_wiki")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_WIKI_FILE_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_WIKI_FILE_ID", conn); WikiBase.getDataHandler() .executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_WIKI_FILE_ID", conn); messages.add(new WikiMessage("upgrade.message.db.column.modified", "file_id", "jam_file")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_WIKI_FILE_VERSION_ID", conn); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ALTER_WIKI_FILE_VERSION_ID", conn); WikiBase.getDataHandler() .executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_WIKI_FILE_VERSION_ID", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "file_version_id", "jam_file_version")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_SEQUENCE_WIKI_USER_ID", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_WIKI_USER_ID", conn); WikiBase.getDataHandler() .executeUpgradeQuery("UPGRADE_080_SET_SEQUENCE_WIKI_USER_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "wiki_user_id", "jam_wiki_user")); } else if (StringUtils.equals(dbType, DataHandler.DATA_HANDLER_MYSQL)) { WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_GROUP_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "group_id", "jam_group")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_GROUP_MEMBERS_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "id", "jam_group_members")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_TOPIC_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "topic_id", "jam_topic")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_TOPIC_VERSION_ID", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "topic_version_id", "jam_topic_version")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_VIRTUAL_WIKI_ID", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "virtual_wiki_id", "jam_virtual_wiki")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_WIKI_FILE_ID", conn); messages.add(new WikiMessage("upgrade.message.db.column.modified", "file_id", "jam_file")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ALTER_WIKI_FILE_VERSION_ID", conn); messages.add( new WikiMessage( "upgrade.message.db.column.modified", "file_version_id", "jam_file_version")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_ALTER_WIKI_USER_ID", conn); messages.add( new WikiMessage("upgrade.message.db.column.modified", "wiki_user_id", "jam_wiki_user")); } // add jam_log table WikiBase.getDataHandler().executeUpgradeUpdate("STATEMENT_CREATE_LOG_TABLE", conn); messages.add(new WikiMessage("upgrade.message.db.table.added", "jam_log")); // add wiki_user_display column to jam_topic_version WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_TOPIC_VERSION_USER_DISPLAY", conn); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_UPDATE_TOPIC_VERSION_USER_DISPLAY", conn); messages.add( new WikiMessage( "upgrade.message.db.column.added", "wiki_user_display", "jam_topic_version")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_DROP_TOPIC_VERSION_IP_ADDRESS", conn); messages.add( new WikiMessage( "upgrade.message.db.column.dropped", "wiki_user_ip_address", "jam_topic_version")); // add wiki_user_display column to jam_file_version WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_FILE_VERSION_USER_DISPLAY", conn); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_UPDATE_FILE_VERSION_USER_DISPLAY", conn); messages.add( new WikiMessage( "upgrade.message.db.column.added", "wiki_user_display", "jam_file_version")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_DROP_FILE_VERSION_IP_ADDRESS", conn); messages.add( new WikiMessage( "upgrade.message.db.column.dropped", "wiki_user_ip_address", "jam_file_version")); // add version_param column to jam_topic_version WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_ADD_TOPIC_VERSION_VERSION_PARAMS", conn); messages.add( new WikiMessage( "upgrade.message.db.column.added", "version_params", "jam_topic_version")); // drop and restore the jam_recent_change table WikiBase.getDataHandler().executeUpgradeUpdate("STATEMENT_DROP_RECENT_CHANGE_TABLE", conn); messages.add(new WikiMessage("upgrade.message.db.table.dropped", "jam_recent_change")); WikiBase.getDataHandler().executeUpgradeUpdate("STATEMENT_CREATE_RECENT_CHANGE_TABLE", conn); messages.add(new WikiMessage("upgrade.message.db.table.added", "jam_recent_change")); } catch (SQLException e) { DatabaseConnection.rollbackOnException(status, e); logger.severe("Database failure during upgrade", e); throw new WikiException(new WikiMessage("upgrade.error.fatal", e.getMessage())); } DatabaseConnection.commit(status); try { // perform a separate transaction to update existing data. this code is in its own // transaction since if it fails the upgrade can still be considered successful. status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); // update the edit type field for topic versions WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_UPDATE_TOPIC_VERSION_UPLOAD_EDIT_TYPE", conn); messages.add(new WikiMessage("upgrade.message.db.data.updated", "jam_topic_version")); } catch (SQLException e) { messages.add(new WikiMessage("upgrade.error.nonfatal", e.getMessage())); // do not throw this error and halt the upgrade process - populating the field // is not required for existing systems. logger.warning( "Failure while updating edit_type value in jam_topic_version. See UPGRADE.txt for instructions on how to manually complete this optional step.", e); try { DatabaseConnection.rollbackOnException(status, e); } catch (Exception ex) { // ignore } status = null; // so we do not try to commit } if (status != null) { DatabaseConnection.commit(status); } try { // perform a separate transaction to update existing data. this code is in its own // transaction since if it fails the upgrade can still be considered successful. status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); // assign ROLE_IMPORT WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_080_INSERT_ROLE_ROLE_IMPORT", conn); messages.add(new WikiMessage("upgrade.message.db.data.added", "jam_role")); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_080_INSERT_AUTHORITIES_ROLE_IMPORT", conn); messages.add(new WikiMessage("upgrade.message.db.data.added", "jam_authorities")); } catch (SQLException e) { messages.add(new WikiMessage("upgrade.error.nonfatal", e.getMessage())); // do not throw this error and halt the upgrade process - populating the field // is not required for existing systems. logger.warning( "Failure while updating ROLE_IMPORT in the jam_authorities table. See UPGRADE.txt for instructions on how to manually complete this optional step.", e); try { DatabaseConnection.rollbackOnException(status, e); } catch (Exception ex) { // ignore } status = null; // so we do not try to commit } if (status != null) { DatabaseConnection.commit(status); } return messages; }
public static List<WikiMessage> upgrade070(List<WikiMessage> messages) throws WikiException { TransactionStatus status = null; try { status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); // add characters_changed column to jam_topic_version WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_070_ADD_TOPIC_VERSION_CHARACTERS_CHANGED", conn); messages.add( new WikiMessage( "upgrade.message.db.column.added", "characters_changed", "jam_topic_version")); // add characters_changed column to jam_recent_change WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_070_ADD_RECENT_CHANGE_CHARACTERS_CHANGED", conn); messages.add( new WikiMessage( "upgrade.message.db.column.added", "characters_changed", "jam_recent_change")); // copy columns from jam_wiki_user_info into jam_wiki_user WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_ADD_USER_EMAIL", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_UPDATE_USER_EMAIL", conn); messages.add(new WikiMessage("upgrade.message.db.column.added", "email", "jam_wiki_user")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_ADD_USER_EDITOR", conn); messages.add(new WikiMessage("upgrade.message.db.column.added", "editor", "jam_wiki_user")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_ADD_USER_SIGNATURE", conn); messages.add( new WikiMessage("upgrade.message.db.column.added", "signature", "jam_wiki_user")); WikiBase.getDataHandler().executeUpgradeUpdate("STATEMENT_CREATE_USERS_TABLE", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_INSERT_USERS", conn); messages.add(new WikiMessage("upgrade.message.db.table.added", "jam_users")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_DROP_USER_REMEMBER_KEY", conn); messages.add( new WikiMessage("upgrade.message.db.column.dropped", "remember_key", "jam_wiki_user")); WikiBase.getDataHandler().executeUpgradeUpdate("STATEMENT_CREATE_AUTHORITIES_TABLE", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_INSERT_AUTHORITIES", conn); messages.add(new WikiMessage("upgrade.message.db.table.added", "jam_authorities")); WikiBase.getDataHandler() .executeUpgradeUpdate("STATEMENT_CREATE_GROUP_AUTHORITIES_TABLE", conn); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_INSERT_GROUP_AUTHORITIES", conn); messages.add(new WikiMessage("upgrade.message.db.table.added", "jam_group_authorities")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_DROP_ROLE_MAP", conn); messages.add(new WikiMessage("upgrade.message.db.table.dropped", "jam_role_map")); WikiBase.getDataHandler().executeUpgradeUpdate("STATEMENT_CREATE_GROUP_MEMBERS_TABLE", conn); // FIXME - avoid hard coding String sql = "select group_id from jam_group where group_name = '" + WikiGroup.GROUP_REGISTERED_USER + "'"; WikiResultSet rs = DatabaseConnection.executeQuery(sql, conn); int groupId = rs.getInt("group_id"); // FIXME - avoid hard coding sql = "select username from jam_users "; rs = DatabaseConnection.executeQuery(sql, conn); int id = 1; while (rs.next()) { // FIXME - avoid hard coding sql = "insert into jam_group_members ( " + "id, username, group_id " + ") values ( " + id + ", '" + StringEscapeUtils.escapeSql(rs.getString("username")) + "', " + groupId + ") "; DatabaseConnection.executeUpdate(sql, conn); id++; } messages.add(new WikiMessage("upgrade.message.db.table.added", "jam_group_members")); WikiBase.getDataHandler().executeUpgradeUpdate("UPGRADE_070_DROP_USER_INFO", conn); messages.add(new WikiMessage("upgrade.message.db.table.dropped", "jam_wiki_user_info")); } catch (SQLException e) { DatabaseConnection.rollbackOnException(status, e); try { DatabaseConnection.executeUpdate(AnsiQueryHandler.STATEMENT_DROP_GROUP_MEMBERS_TABLE); } catch (Exception ex) { } try { DatabaseConnection.executeUpdate(AnsiQueryHandler.STATEMENT_DROP_GROUP_AUTHORITIES_TABLE); } catch (Exception ex) { } try { DatabaseConnection.executeUpdate(AnsiQueryHandler.STATEMENT_DROP_AUTHORITIES_TABLE); } catch (Exception ex) { } try { DatabaseConnection.executeUpdate(AnsiQueryHandler.STATEMENT_DROP_USERS_TABLE); } catch (Exception ex) { } logger.severe("Database failure during upgrade", e); throw new WikiException(new WikiMessage("upgrade.error.fatal", e.getMessage())); } DatabaseConnection.commit(status); // for some reason HSQL hangs when populating the characters_changed column. since this step is // optional just skip it for HSQL. String dbType = Environment.getValue(Environment.PROP_DB_TYPE); if (!StringUtils.equals(dbType, DataHandler.DATA_HANDLER_HSQL)) { try { // perform a second transaction to populate the new columns. this code is in its own // transaction since if it fails the upgrade can still be considered successful. status = DatabaseConnection.startTransaction(getTransactionDefinition()); Connection conn = DatabaseConnection.getConnection(); WikiBase.getDataHandler() .executeUpgradeUpdate("UPGRADE_070_UPDATE_TOPIC_VERSION_CHARACTERS_CHANGED", conn); messages.add( new WikiMessage( "upgrade.message.db.column.populated", "characters_changed", "jam_topic_version")); } catch (SQLException e) { messages.add(new WikiMessage("upgrade.error.nonfatal", e.getMessage())); // do not throw this error and halt the upgrade process - populating the field // is not required for existing systems. logger.warning( "Failure while populating characters_changed colum in jam_topic_version. See UPGRADE.txt for instructions on how to manually complete this optional step.", e); try { DatabaseConnection.rollbackOnException(status, e); } catch (Exception ex) { // ignore } status = null; // so we do not try to commit } if (status != null) { DatabaseConnection.commit(status); } } return messages; }