liquibase.structure.core.Table Java Examples

The following examples show how to use liquibase.structure.core.Table. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source Project: jweb-cms   Author: chifei   File: AbstractJdbcDatabase.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
@Override
public boolean isSystemObject(final DatabaseObject example) {
    if (example == null) {
        return false;
    }
    if ((example.getSchema() != null) && (example.getSchema().getName() != null) && "information_schema"
        .equalsIgnoreCase(example.getSchema().getName())) {
        return true;
    }
    if ((example instanceof Table) && getSystemTables().contains(example.getName())) {
        return true;
    }

    return (example instanceof View) && getSystemViews().contains(example.getName());

}
 
Example #2
Source Project: jweb-cms   Author: chifei   File: AbstractJdbcDatabase.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
@Override
public boolean isLiquibaseObject(final DatabaseObject object) {
    if (object instanceof Table) {
        Schema liquibaseSchema = new Schema(getLiquibaseCatalogName(), getLiquibaseSchemaName());
        if (DatabaseObjectComparatorFactory.getInstance().isSameObject(object, new Table().setName(getDatabaseChangeLogTableName()).setSchema(liquibaseSchema), null, this)) {
            return true;
        }
        return DatabaseObjectComparatorFactory.getInstance().isSameObject(object, new Table().setName(getDatabaseChangeLogLockTableName()).setSchema(liquibaseSchema), null, this);
    } else if (object instanceof Column) {
        return isLiquibaseObject(((Column) object).getRelation());
    } else if (object instanceof Index) {
        return isLiquibaseObject(((Index) object).getRelation());
    } else if (object instanceof PrimaryKey) {
        return isLiquibaseObject(((PrimaryKey) object).getTable());
    }
    return false;
}
 
Example #3
Source Project: liquibase-impala   Author: eselyavka   File: HiveTagDatabaseGenerator.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public Sql[] generateSql(TagDatabaseStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String catalogName = database.getLiquibaseCatalogName();
    String schemaName = database.getDefaultSchemaName();
    String tableName = database.getDatabaseChangeLogTableName();
    String tableNameEscaped = database.escapeObjectName(tableName, Table.class);
    String dateColumnNameEscaped = database.escapeObjectName("DATEEXECUTED", Column.class);
    String tagColumnNameEscaped = database.escapeObjectName("TAG", Column.class);
    String tempTable = UUID.randomUUID().toString().replaceAll("-", "");
    CreateTableAsSelectStatement createTableAsSelectStatement = new CreateTableAsSelectStatement(catalogName, schemaName, tableName, tempTable)
            .addColumnNames("ID", "AUTHOR", "FILENAME", "DATEEXECUTED", "ORDEREXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "TAG", "LIQUIBASE", "CONTEXTS", "LABELS", "DEPLOYMENT_ID")
            .setWhereCondition(tableNameEscaped + "." + dateColumnNameEscaped + " NOT IN (SELECT MAX(" + tableNameEscaped + "." + dateColumnNameEscaped + ") " +
                    "FROM " + tableNameEscaped + ")");
    InsertAsSelectStatement insertAsSelectStatement = new InsertAsSelectStatement(catalogName, schemaName, tableName, tempTable)
            .addColumnNames("ID", "AUTHOR", "FILENAME", "DATEEXECUTED", "ORDEREXECUTED", "EXECTYPE", "MD5SUM", "DESCRIPTION", "COMMENTS", "'" + statement.getTag() + "'", "LIQUIBASE", "CONTEXTS", "LABELS", "DEPLOYMENT_ID")
            .setWhereCondition(tableNameEscaped + "." + dateColumnNameEscaped + " IN (SELECT MAX(" + tableNameEscaped + "." + dateColumnNameEscaped + ") FROM " + tableNameEscaped + ") AND ("
                    + tableNameEscaped + "." + tagColumnNameEscaped + " IS NULL OR " + tableNameEscaped + "." + tagColumnNameEscaped + " != ?)").addWhereParameters(statement.getTag());

    return CustomSqlGenerator.generateSql(database,
            createTableAsSelectStatement,
            insertAsSelectStatement,
            new DropTableStatement(catalogName, schemaName, tableName, false),
            new RenameTableStatement(catalogName, schemaName, tempTable, tableName));
}
 
Example #4
Source Project: liquibase-impala   Author: eselyavka   File: HiveDatabaseTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testEscapeObjectName() {
    HiveDatabase databaseWithDefaultQuoting = new HiveDatabase();
    databaseWithDefaultQuoting.setObjectQuotingStrategy(ObjectQuotingStrategy.LEGACY);
    assertEquals("Test", databaseWithDefaultQuoting.escapeObjectName("Test", Table.class));

    HiveDatabase databaseWithAllQuoting = new HiveDatabase();
    databaseWithAllQuoting.setObjectQuotingStrategy(ObjectQuotingStrategy.QUOTE_ALL_OBJECTS);
    assertEquals("`Test`", databaseWithAllQuoting.escapeObjectName("Test", Table.class));

    HiveDatabase databaseWithReservedWordsQuoting = new HiveDatabase();
    databaseWithReservedWordsQuoting.setReservedWords();
    databaseWithReservedWordsQuoting.setObjectQuotingStrategy(ObjectQuotingStrategy.QUOTE_ONLY_RESERVED_WORDS);
    assertEquals("`timestamp`", databaseWithReservedWordsQuoting.escapeObjectName("timestamp", Table.class));
    assertEquals("Test", databaseWithReservedWordsQuoting.escapeObjectName("Test", Table.class));
}
 
Example #5
Source Project: liquibase-impala   Author: eselyavka   File: ImpalaDatabaseTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testEscapeObjectName() {
    ImpalaDatabase databaseWithDefaultQuoting = new ImpalaDatabase();
    databaseWithDefaultQuoting.setObjectQuotingStrategy(ObjectQuotingStrategy.LEGACY);
    assertEquals("Test", databaseWithDefaultQuoting.escapeObjectName("Test", Table.class));

    ImpalaDatabase databaseWithAllQuoting = new ImpalaDatabase();
    databaseWithAllQuoting.setObjectQuotingStrategy(ObjectQuotingStrategy.QUOTE_ALL_OBJECTS);
    assertEquals("`Test`", databaseWithAllQuoting.escapeObjectName("Test", Table.class));

    ImpalaDatabase databaseWithReservedWordsQuoting = new ImpalaDatabase();
    databaseWithReservedWordsQuoting.setReservedWords();
    databaseWithReservedWordsQuoting.setObjectQuotingStrategy(ObjectQuotingStrategy.QUOTE_ONLY_RESERVED_WORDS);
    assertEquals("`timestamp`", databaseWithReservedWordsQuoting.escapeObjectName("timestamp", Table.class));
    assertEquals("Test", databaseWithReservedWordsQuoting.escapeObjectName("Test", Table.class));
}
 
Example #6
Source Project: liquibase-spatial   Author: lonnyj   File: DropSpatialIndexGeneratorGeoDB.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public Sql[] generateSql(final DropSpatialIndexStatement statement,
      final Database database, final SqlGeneratorChain sqlGeneratorChain) {
   final String catalogName = statement.getTableCatalogName();
   String schemaName = statement.getTableSchemaName();
   if (schemaName == null) {
      schemaName = database.getDefaultSchemaName();
   }

   final StringBuilder sql = new StringBuilder("CALL ");
   sql.append(schemaName).append(".DropSpatialIndex(");

   // Add the schema name parameter.
   sql.append("'").append(database.escapeStringForDatabase(schemaName)).append("'");
   sql.append(", ");

   // Add the table name parameter.
   final String tableName = statement.getTableName();
   sql.append("'").append(database.escapeStringForDatabase(tableName)).append("'");
   sql.append(')');

   final Table hatboxTable = new Table().setName(tableName + "_HATBOX");
   hatboxTable.setSchema(catalogName, schemaName);
   final UnparsedSql spatialize = new UnparsedSql(sql.toString(), hatboxTable);
   return new Sql[] { spatialize };
}
 
Example #7
Source Project: liquibase-spatial   Author: lonnyj   File: CreateSpatialIndexGeneratorOracle.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Generates the SQL for inserting the necessary record into the
 * <code>USER_SDO_GEOM_METADATA</code> table. This record must be present prior to creating the
 * spatial index.
 * 
 * @param statement
 *           the create spatial index statement.
 * @param database
 *           the database instance.
 * @return the SQL to insert the metadata record.
 */
protected String generateInsertMetadataSql(final CreateSpatialIndexStatement statement,
      final Database database) {
   final StringBuilder sql = new StringBuilder();
   sql.append("INSERT INTO user_sdo_geom_metadata ");
   sql.append("(table_name, column_name, diminfo, srid) ");
   final String tableName = statement.getTableName().trim();
   sql.append("VALUES ('").append(database.correctObjectName(tableName, Table.class));
   final String columnName = statement.getColumns()[0].trim();
   sql.append("', '").append(database.correctObjectName(columnName, Column.class));
   sql.append("', SDO_DIM_ARRAY(");
   sql.append("SDO_DIM_ELEMENT('Longitude', -180, 180, 0.005), ");
   sql.append("SDO_DIM_ELEMENT('Latitude', -90, 90, 0.005))");
   final Integer srid = statement.getSrid();
   if (srid == null) {
      sql.append(", NULL");
   } else {
      sql.append(", ").append(OracleSpatialUtils.EPSG_TO_ORACLE_FUNCTION).append("(")
            .append(srid).append(")");
   }
   sql.append(")");
   return sql.toString();
}
 
Example #8
Source Project: liquibase-spatial   Author: lonnyj   File: DropSpatialTableGeneratorOracle.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public Sql[] generateSql(final DropTableStatement statement, final Database database,
      final SqlGeneratorChain sqlGeneratorChain) {
   final StringBuilder sql = new StringBuilder();
   sql.append("DELETE FROM user_sdo_geom_metadata ");
   sql.append("WHERE table_name = '").append(
         database.correctObjectName(statement.getTableName(), Table.class));
   sql.append("'");
   final UnparsedSql deleteMetadata = new UnparsedSql(sql.toString(),
         new View().setName("user_sdo_geom_metadata"));

   // First delete the record then perform the standard behavior.
   final List<Sql> list = new ArrayList<Sql>();
   list.add(deleteMetadata);
   list.addAll(Arrays.asList(sqlGeneratorChain.generateSql(statement, database)));
   return list.toArray(new Sql[list.size()]);
}
 
Example #9
Source Project: liquibase-spatial   Author: lonnyj   File: SpatialIndexExistsPrecondition.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Generates the {@link Index} example (taken from {@link IndexExistsPrecondition}).
 *
 * @param database
 *           the database instance.
 * @param schema
 *           the schema instance.
 * @param tableName
 *           the table name of the index.
 * @return the index example.
 */
protected Index getIndexExample(final Database database, final Schema schema,
      final String tableName) {
   final Index example = new Index();
   if (tableName != null) {
      example.setTable((Table) new Table().setName(
            database.correctObjectName(getTableName(), Table.class)).setSchema(schema));
   }
   example.setName(database.correctObjectName(getIndexName(), Index.class));
   if (StringUtils.trimToNull(getColumnNames()) != null) {
      for (final String columnName : getColumnNames().split("\\s*,\\s*")) {
         final Column column = new Column(database.correctObjectName(columnName, Column.class));
         example.getColumns().add(column);
      }
   }
   return example;
}
 
Example #10
Source Project: liquibase-spatial   Author: lonnyj   File: GeometryColumnsUtils.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Indicates if the <code>GEOMETRY_COLUMNS</code> table or view exists.
 * 
 * @param database
 *           the database to check.
 * @return <code>true</code> if the table or view exists.
 */
public static boolean geometryColumnsExists(final Database database) {
   String geometryColumnsName = database.correctObjectName(
         "geometry_columns", Table.class);
   DatabaseObject example = null;
   if (database instanceof DerbyDatabase || database instanceof H2Database) {
      final Table tableExample = new Table();
      tableExample.setName(geometryColumnsName);
      tableExample.setSchema(database.getDefaultCatalogName(),
            database.getDefaultSchemaName());
      example = tableExample;
   } else if (database instanceof PostgresDatabase) {
      final View viewExample = new View();
      viewExample.setName(geometryColumnsName);
      viewExample.setSchema(database.getDefaultCatalogName(), "public");
      example = viewExample;
   }
   try {
      return example != null
            && SnapshotGeneratorFactory.getInstance().has(example, database);
   } catch (final LiquibaseException e) {
      throw new UnexpectedLiquibaseException(
            "Failed to determine if the geometry_columns table or view exists",
            e);
   }
}
 
Example #11
Source Project: keycloak   Author: keycloak   File: JpaUpdate1_2_0_Beta1.java    License: Apache License 2.0 6 votes vote down vote up
protected void convertSocialToIdFedUsers() throws SQLException, DatabaseException {
    String federatedIdentityTableName = database.correctObjectName("FEDERATED_IDENTITY", Table.class);
    PreparedStatement statement = jdbcConnection.prepareStatement("select REALM_ID, USER_ID, SOCIAL_PROVIDER, SOCIAL_USER_ID, SOCIAL_USERNAME from " + getTableName("USER_SOCIAL_LINK"));
    try {
        ResultSet resultSet = statement.executeQuery();
        try {
            int count = 0;
            while (resultSet.next()) {
                InsertStatement insert = new InsertStatement(null, null, federatedIdentityTableName)
                        .addColumnValue("REALM_ID", resultSet.getString("REALM_ID"))
                        .addColumnValue("USER_ID", resultSet.getString("USER_ID"))
                        .addColumnValue("IDENTITY_PROVIDER", resultSet.getString("SOCIAL_PROVIDER"))
                        .addColumnValue("FEDERATED_USER_ID", resultSet.getString("SOCIAL_USER_ID"))
                        .addColumnValue("FEDERATED_USERNAME", resultSet.getString("SOCIAL_USERNAME"));
                count++;
                statements.add(insert);
            }

            confirmationMessage.append("Updating " + count + " social links to federated identities. ");
        } finally {
            resultSet.close();
        }
    } finally {
        statement.close();
    }
}
 
Example #12
Source Project: keycloak   Author: keycloak   File: JpaUpdate1_2_0_Beta1.java    License: Apache License 2.0 6 votes vote down vote up
private void addAdminRole(String roleName, String realmId, String applicationId, String realmAdminAppRoleId) {
    String roleTableName = database.correctObjectName("KEYCLOAK_ROLE", Table.class);
    String compositeRoleTableName = database.correctObjectName("COMPOSITE_ROLE", Table.class);
    String newRoleId = KeycloakModelUtils.generateId();

    InsertStatement insertRole = new InsertStatement(null, null, roleTableName)
            .addColumnValue("ID", newRoleId)
            .addColumnValue("APP_REALM_CONSTRAINT", applicationId)
            .addColumnValue("APPLICATION_ROLE", true)
            .addColumnValue("NAME", roleName)
            .addColumnValue("REALM_ID", realmId)
            .addColumnValue("APPLICATION", applicationId);

    // Add newly created role to the composite roles of 'realm-admin' role
    InsertStatement insertCompRole = new InsertStatement(null, null, compositeRoleTableName)
            .addColumnValue("COMPOSITE", realmAdminAppRoleId)
            .addColumnValue("CHILD_ROLE", newRoleId);

    statements.add(insertRole);
    statements.add(insertCompRole);
}
 
Example #13
Source Project: keycloak   Author: keycloak   File: JpaUpdate4_7_0_OfflineSessionsTimestamps.java    License: Apache License 2.0 6 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String offlineUserSessionsTableName = database.correctObjectName("OFFLINE_USER_SESSION", Table.class);

    try {
        int currentTime = Time.currentTime();

        UpdateStatement updateStatement = new UpdateStatement(null, null, offlineUserSessionsTableName)
                .addNewColumnValue("LAST_SESSION_REFRESH", currentTime);

        statements.add(updateStatement);

        confirmationMessage.append("Updated column LAST_SESSION_REFRESH in OFFLINE_USER_SESSION table with time " + currentTime);
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #14
Source Project: liquibase-impala   Author: eselyavka   File: HiveStandardChangeLogHistoryService.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void destroy() throws DatabaseException {
    Database database = getDatabase();
    try {
        if (SnapshotGeneratorFactory.getInstance().has(new Table().setName(database.getDatabaseChangeLogTableName()).setSchema(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName()), database)) {
            ExecutorService.getInstance().getExecutor(database).execute(new DropTableStatement(database.getLiquibaseCatalogName(), database.getLiquibaseSchemaName(), database.getDatabaseChangeLogTableName(), false));
        }
        reset();
    } catch (InvalidExampleException e) {
        throw new UnexpectedLiquibaseException(e);
    }
}
 
Example #15
Source Project: liquibase-impala   Author: eselyavka   File: RenameTableGenerator.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public Sql[] generateSql(RenameTableStatement statement, Database database, SqlGeneratorChain sqlGeneratorChain) {
    String sql = "ALTER TABLE " + database.escapeTableName(statement.getCatalogName(), statement.getSchemaName(),
            statement.getOldTableName()) + " RENAME TO " + database.escapeObjectName(statement.getNewTableName(), Table.class);
    return new Sql[]{
            new UnparsedSql(sql,
                    fetchAffectedOldTable(statement),
                    fetchAffectedNewTable(statement)
            )
    };
}
 
Example #16
Source Project: liquibase-spatial   Author: lonnyj   File: DropSpatialIndexGeneratorOracle.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public Sql[] generateSql(final DropSpatialIndexStatement statement, final Database database,
      final SqlGeneratorChain sqlGeneratorChain) {
   final String indexName = statement.getIndexName();
   final Index example = new Index().setName(indexName);
   if (statement.getTableName() != null) {
      example.setTable((Table) new Table().setName(statement.getTableName()).setSchema(
            statement.getTableCatalogName(), statement.getTableSchemaName()));
   }
   Index index;
   try {
      index = SnapshotGeneratorFactory.getInstance().createSnapshot(example, database);
   } catch (final Exception e) {
      throw new UnexpectedLiquibaseException("Failed to create a snapshot of '" + indexName
            + "'", e);
   }

   final String tableName = index.getTable().getName();
   final Column column = index.getColumns().get(0);

   final StringBuilder sql = new StringBuilder();
   sql.append("DELETE FROM user_sdo_geom_metadata ");
   sql.append("WHERE table_name = '").append(database.correctObjectName(tableName, Table.class));
   sql.append("' AND column_name = '").append(
         database.correctObjectName(column.getName(), Column.class));
   sql.append("'");
   final UnparsedSql deleteMetadata = new UnparsedSql(sql.toString(),
         new View().setName("user_sdo_geom_metadata"));
   return new Sql[] { deleteMetadata };
}
 
Example #17
Source Project: liquibase-spatial   Author: lonnyj   File: CreateSpatialIndexGeneratorOracle.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Generates the SQL for deleting any existing record from the
 * <code>USER_SDO_GEOM_METADATA</code> table. Typically this record shouldn't be present but we
 * must ensure that it does not already exist.
 * 
 * @param statement
 *           the create spatial index statement.
 * @param database
 *           the database instance.
 * @return the SQL to delete any existing metadata record.
 */
protected String generateDeleteMetadataSql(final CreateSpatialIndexStatement statement,
      final Database database) {
   final StringBuilder sql = new StringBuilder();
   sql.append("DELETE FROM user_sdo_geom_metadata ");
   final String tableName = statement.getTableName().trim();
   sql.append("WHERE table_name = '").append(database.correctObjectName(tableName, Table.class));
   final String columnName = statement.getColumns()[0].trim();
   sql.append("' AND column_name = '").append(
         database.correctObjectName(columnName, Column.class));
   sql.append("'");
   return sql.toString();
}
 
Example #18
Source Project: liquibase-spatial   Author: lonnyj   File: CreateSpatialIndexGeneratorGeoDB.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * @see liquibase.sqlgenerator.SqlGenerator#generateSql(liquibase.statement.SqlStatement,
 *      liquibase.database.Database, liquibase.sqlgenerator.SqlGeneratorChain)
 */
@Override
public Sql[] generateSql(final CreateSpatialIndexStatement statement, final Database database,
      final SqlGeneratorChain sqlGeneratorChain) {
   final String catalogName = statement.getTableCatalogName();
   String schemaName = statement.getTableSchemaName();
   if (schemaName == null) {
      schemaName = database.getDefaultSchemaName();
   }
   final StringBuilder sql = new StringBuilder("CALL ");
   sql.append(schemaName).append(".CreateSpatialIndex(");

   // Add the schema name parameter.
   sql.append("'").append(schemaName).append("'");
   sql.append(", ");

   // Add the table name parameter.
   final String tableName = statement.getTableName();
   sql.append("'").append(tableName).append("'");
   sql.append(", ");

   // Add the column name parameter.
   final String columnName = statement.getColumns()[0];
   sql.append("'").append(columnName).append("'");
   sql.append(", ");

   // Add the SRID parameter.
   final int srid = statement.getSrid();
   sql.append("'").append(srid).append("'");
   sql.append(')');
   final Table hatboxTable = new Table().setName(database.correctObjectName(tableName
         + "_HATBOX", Table.class));
   hatboxTable.setSchema(catalogName, schemaName);
   final UnparsedSql spatialize = new UnparsedSql(sql.toString(), hatboxTable);

   return new Sql[] { spatialize };
}
 
Example #19
Source Project: liquibase-spatial   Author: lonnyj   File: SpatialIndexExistsPrecondition.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Creates an example of the database object for which to check.
 *
 * @param database
 *           the database instance.
 * @param tableName
 *           the table name of the index.
 * @return the database object example.
 */
public DatabaseObject getExample(final Database database, final String tableName) {
   final Schema schema = new Schema(getCatalogName(), getSchemaName());
   final DatabaseObject example;

   // For GeoDB, the index is another table.
   if (database instanceof DerbyDatabase || database instanceof H2Database) {
      final String correctedTableName = database.correctObjectName(getHatboxTableName(),
            Table.class);
      example = new Table().setName(correctedTableName).setSchema(schema);
   } else {
      example = getIndexExample(database, schema, tableName);
   }
   return example;
}
 
Example #20
Source Project: keycloak   Author: keycloak   File: JpaUpdateAuthz_3_4_0_CR1.java    License: Apache License 2.0 5 votes vote down vote up
private SqlStatement generateUpdateStatement(String resourceServerDetailTable) {
        String resourceServerTableName = database.correctObjectName(getTableName("RESOURCE_SERVER"), Table.class);
        String resourceServerDetailTableName = database.correctObjectName(getTableName(resourceServerDetailTable), Table.class);

        if (database instanceof MSSQLDatabase) {
//            UPDATE RESOURCE_SERVER_POLICY   SET RESOURCE_SERVER_CLIENT_ID = s.CLIENT_ID FROM (SELECT ID, CLIENT_ID FROM RESOURCE_SERVER) s WHERE s.ID = RESOURCE_SERVER_POLICY.RESOURCE_SERVER_ID;
//            UPDATE RESOURCE_SERVER_RESOURCE SET RESOURCE_SERVER_CLIENT_ID = s.CLIENT_ID FROM (SELECT ID, CLIENT_ID FROM RESOURCE_SERVER) s WHERE s.ID = RESOURCE_SERVER_RESOURCE.RESOURCE_SERVER_ID;
//            UPDATE RESOURCE_SERVER_SCOPE    SET RESOURCE_SERVER_CLIENT_ID = s.CLIENT_ID FROM (SELECT ID, CLIENT_ID FROM RESOURCE_SERVER) s WHERE s.ID = RESOURCE_SERVER_SCOPE.RESOURCE_SERVER_ID;
            return new RawSqlStatement(
              "UPDATE "
              + resourceServerDetailTableName
                + " SET RESOURCE_SERVER_CLIENT_ID = s.CLIENT_ID FROM "
                  + " (SELECT ID, CLIENT_ID FROM "
                  + resourceServerTableName
                  + ") s "
                + " WHERE s.ID = "
                + resourceServerDetailTableName
                + ".RESOURCE_SERVER_ID"
            );
        } else {
//          UPDATE RESOURCE_SERVER_POLICY p   SET RESOURCE_SERVER_CLIENT_ID = (SELECT CLIENT_ID FROM RESOURCE_SERVER s WHERE s.ID = p.RESOURCE_SERVER_ID);
//          UPDATE RESOURCE_SERVER_RESOURCE p SET RESOURCE_SERVER_CLIENT_ID = (SELECT CLIENT_ID FROM RESOURCE_SERVER s WHERE s.ID = p.RESOURCE_SERVER_ID);
//          UPDATE RESOURCE_SERVER_SCOPE p    SET RESOURCE_SERVER_CLIENT_ID = (SELECT CLIENT_ID FROM RESOURCE_SERVER s WHERE s.ID = p.RESOURCE_SERVER_ID);
            return new RawSqlStatement(
              "UPDATE "
              + resourceServerDetailTableName
              + " p SET RESOURCE_SERVER_CLIENT_ID = "
                + "(SELECT CLIENT_ID FROM "
                + resourceServerTableName
                + " s WHERE s.ID = p.RESOURCE_SERVER_ID)"
            );
        }

    }
 
Example #21
Source Project: keycloak   Author: keycloak   File: JpaUpdate1_2_0_CR1.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String realmClientTableName = database.correctObjectName("REALM_CLIENT", Table.class);

    try {
        String trueValue = DataTypeFactory.getInstance().getTrueBooleanValue(database);
        PreparedStatement statement = jdbcConnection.prepareStatement("select CLIENT.REALM_ID, CLIENT.ID CLIENT_ID from " + getTableName("CLIENT") + " CLIENT where CLIENT.CONSENT_REQUIRED = " + trueValue);

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String realmId = resultSet.getString("REALM_ID");
                    String oauthClientId = resultSet.getString("CLIENT_ID");

                    InsertStatement realmClientInsert = new InsertStatement(null, null, realmClientTableName)
                            .addColumnValue("REALM_ID", realmId)
                            .addColumnValue("CLIENT_ID", oauthClientId);
                    statements.add(realmClientInsert);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Inserted " + statements.size() + " OAuth Clients to REALM_CLIENT table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #22
Source Project: keycloak   Author: keycloak   File: CustomKeycloakTask.java    License: Apache License 2.0 5 votes vote down vote up
protected boolean isApplicable() throws CustomChangeException {
    try {
        String correctedTableName = database.correctObjectName("REALM", Table.class);
        if (SnapshotGeneratorFactory.getInstance().has(new Table().setName(correctedTableName), database)) {
            try (Statement st = connection.createStatement(); ResultSet resultSet = st.executeQuery("SELECT ID FROM " + getTableName(correctedTableName))) {
                return (resultSet.next());
            }
        } else {
            return false;
        }
    } catch (Exception e) {
        throw new CustomChangeException("Failed to check database availability", e);
    }
}
 
Example #23
Source Project: keycloak   Author: keycloak   File: AuthzResourceUseMoreURIs.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select ID,URI from " + getTableName("RESOURCE_SERVER_RESOURCE") + " where URI is not null");

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String resourceId = resultSet.getString(1);
                    String resourceUri = resultSet.getString(2);

                    InsertStatement insertComponent = new InsertStatement(null, null, database.correctObjectName("RESOURCE_URIS", Table.class))
                            .addColumnValue("RESOURCE_ID", resourceId)
                            .addColumnValue("VALUE", resourceUri);

                    statements.add(insertComponent);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Moved " + statements.size() + " records from RESOURCE_SERVER_RESOURCE to RESOURCE_URIS table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #24
Source Project: keycloak   Author: keycloak   File: JpaUpdate1_4_0_Final.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String userAttributeTableName = database.correctObjectName("USER_ATTRIBUTE", Table.class);

    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select NAME, USER_ID from " + getTableName("USER_ATTRIBUTE"));

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String name = resultSet.getString(1);
                    String userId = resultSet.getString(2);

                    UpdateStatement updateStatement = new UpdateStatement(null, null, userAttributeTableName)
                            .addNewColumnValue("ID", KeycloakModelUtils.generateId())
                            .setWhereClause("NAME='" + name + "' AND USER_ID='" + userId + "'");
                    statements.add(updateStatement);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " attributes in USER_ATTRIBUTE table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #25
Source Project: keycloak   Author: keycloak   File: JpaUpdate4_0_0_DefaultClientScopes.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String clientTableName = database.correctObjectName("CLIENT", Table.class);
    String clientScopeClientTableName = database.correctObjectName("CLIENT_SCOPE_CLIENT", Table.class);

    try (PreparedStatement statement = jdbcConnection.prepareStatement("SELECT ID, CLIENT_TEMPLATE_ID FROM " + clientTableName);
      ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            String clientId = rs.getString(1);
            String clientTemplateId = rs.getString(2);

            if (clientId == null || clientId.trim().isEmpty()) {
                continue;
            }
            if (clientTemplateId == null || clientTemplateId.trim().isEmpty()) {
                continue;
            }

            statements.add(
              new InsertStatement(null, null, clientScopeClientTableName)
                .addColumnValue("CLIENT_ID", clientId.trim())
                .addColumnValue("SCOPE_ID", clientTemplateId.trim())
                .addColumnValue("DEFAULT_SCOPE", Boolean.TRUE)
            );
        }

        confirmationMessage.append("Updated " + statements.size() + " records in CLIENT_SCOPE_CLIENT table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #26
Source Project: keycloak   Author: keycloak   File: JpaUpdate1_9_0_Final.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String userFederationProviderTableName = database.correctObjectName("USER_FEDERATION_PROVIDER", Table.class);

    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select REALM_ID, USERFEDERATIONPROVIDERS_ID from " + getTableName("FED_PROVIDERS"));

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String realmId = resultSet.getString(1);
                    String userFederationProviderId = resultSet.getString(2);

                    UpdateStatement updateStatement = new UpdateStatement(null, null, userFederationProviderTableName)
                            .addNewColumnValue("REALM_ID", realmId)
                            .setWhereClause("ID='" + userFederationProviderId + "'");
                    statements.add(updateStatement);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " records in USER_FEDERATION_PROVIDER table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #27
Source Project: keycloak   Author: keycloak   File: JpaUpdate1_2_0_Beta1.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void generateStatementsImpl() throws CustomChangeException {
    realmTableName = database.correctObjectName("REALM", Table.class);

    try {
        convertSocialToIdFedRealms();
        convertSocialToIdFedUsers();
        addAccessCodeLoginTimeout();
        addNewAdminRoles();
        addDefaultProtocolMappers();
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 
Example #28
Source Project: keycloak   Author: keycloak   File: AddRealmCodeSecret.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public SqlStatement[] generateStatements(Database database) throws CustomChangeException {
    try {
        StringBuilder sb = new StringBuilder();
        sb.append("Generated codeSecret for realms: ");

        Connection connection = ((JdbcConnection) (database.getConnection())).getWrappedConnection();
        ArrayList<SqlStatement> statements = new ArrayList<SqlStatement>();

        String correctedTableName = database.correctObjectName("REALM", Table.class);
        String correctedSchemaName = database.escapeObjectName(database.getDefaultSchemaName(), Schema.class);

        if (SnapshotGeneratorFactory.getInstance().has(new Table().setName(correctedTableName), database)) {
            try (Statement st = connection.createStatement(); ResultSet resultSet = st.executeQuery("SELECT ID FROM " + LiquibaseJpaUpdaterProvider.getTable(correctedTableName, correctedSchemaName) + " WHERE CODE_SECRET IS NULL")) {
                while (resultSet.next()) {
                    String id = resultSet.getString(1);
                    
                    UpdateStatement statement = new UpdateStatement(null, null, correctedTableName)
                            .addNewColumnValue("CODE_SECRET", KeycloakModelUtils.generateCodeSecret())
                            .setWhereClause("ID=?").addWhereParameters(id);
                    statements.add(statement);
                    
                    if (!resultSet.isFirst()) {
                        sb.append(", ");
                    }
                    sb.append(id);
                }

                if (!statements.isEmpty()) {
                    confirmationMessage = sb.toString();
                }
            }
        }

        return statements.toArray(new SqlStatement[statements.size()]);
    } catch (Exception e) {
        throw new CustomChangeException("Failed to add realm code secret", e);
    }
}
 
Example #29
Source Project: keycloak   Author: keycloak   File: ExtractRealmKeysFromRealmTable.java    License: Apache License 2.0 5 votes vote down vote up
private InsertStatement componentConfigStatement(String componentId, String name, String value) {
    return new InsertStatement(null, null, database.correctObjectName("COMPONENT_CONFIG", Table.class))
            .addColumnValue("ID", KeycloakModelUtils.generateId())
            .addColumnValue("COMPONENT_ID", componentId)
            .addColumnValue("NAME", name)
            .addColumnValue("VALUE", value);
}
 
Example #30
Source Project: keycloak   Author: keycloak   File: AbstractUserFedToComponent.java    License: Apache License 2.0 5 votes vote down vote up
protected InsertStatement componentConfigStatement(String componentId, String name, String value) {
    return new InsertStatement(null, null, database.correctObjectName("COMPONENT_CONFIG", Table.class))
            .addColumnValue("ID", KeycloakModelUtils.generateId())
            .addColumnValue("COMPONENT_ID", componentId)
            .addColumnValue("NAME", name)
            .addColumnValue("VALUE", value);
}