Examples with ConstraintRef org.voltdb.catalog.ConstraintRef used on opensource projects

Example 1 with ConstraintRef

use of org.voltdb.catalog.ConstraintRef in project voltdb by VoltDB.

the class CatalogSchemaTools method toSchema.

/**
     * Convert a Table catalog object into the proper SQL DDL, including all indexes,
     * constraints, and foreign key references.
     * Also returns just the CREATE TABLE statement, since, like all good methods,
     * it should have two purposes....
     * It would be nice to have a separate method to just generate the CREATE TABLE,
     * but we use that pass to also figure out what separate constraint and index
     * SQL DDL needs to be generated, so instead, we opt to build the CREATE TABLE DDL
     * separately as we go here, and then fill it in to the StringBuilder being used
     * to construct the full canonical DDL at the appropriate time.
     * @param sb - the schema being built
     * @param catalog_tbl - object to be analyzed
     * @param viewQuery - the Query if this Table is a View
     * @param isExportOnly Is this a export table.
     * @param streamPartitionColumn stream partition column
     * @param streamTarget - true if this Table is an Export Table
     * @return SQL Schema text representing the CREATE TABLE statement to generate the table
     */
public static String toSchema(StringBuilder sb, Table catalog_tbl, String viewQuery, boolean isExportOnly, String streamPartitionColumn, String streamTarget) {
    assert (!catalog_tbl.getColumns().isEmpty());
    boolean tableIsView = (viewQuery != null);
    // We need the intermediate results of building the table schema string so that
    // we can return the full CREATE TABLE statement, so accumulate it separately
    final StringBuilder table_sb = new StringBuilder();
    final Set<Index> skip_indexes = new HashSet<>();
    final Set<Constraint> skip_constraints = new HashSet<>();
    if (tableIsView) {
        table_sb.append("CREATE VIEW ").append(catalog_tbl.getTypeName()).append(" (");
    } else {
        if (isExportOnly) {
            table_sb.append("CREATE STREAM ").append(catalog_tbl.getTypeName());
            if (streamPartitionColumn != null && viewQuery == null) {
                table_sb.append(" PARTITION ON COLUMN ").append(streamPartitionColumn);
            }
            //Default target means no target.
            if (streamTarget != null && !streamTarget.equalsIgnoreCase(Constants.DEFAULT_EXPORT_CONNECTOR_NAME)) {
                table_sb.append(" EXPORT TO TARGET ").append(streamTarget);
            }
        } else {
            table_sb.append("CREATE TABLE ").append(catalog_tbl.getTypeName());
        }
        table_sb.append(" (");
    }
    // Columns
    String add = "\n";
    for (Column catalog_col : CatalogUtil.getSortedCatalogItems(catalog_tbl.getColumns(), "index")) {
        VoltType col_type = VoltType.get((byte) catalog_col.getType());
        if (tableIsView) {
            table_sb.append(add).append(spacer).append(catalog_col.getTypeName());
            add = ",\n";
            continue;
        }
        table_sb.append(add).append(spacer).append(catalog_col.getTypeName()).append(" ").append(col_type.toSQLString()).append(col_type.isVariableLength() && catalog_col.getSize() > 0 ? "(" + catalog_col.getSize() + (catalog_col.getInbytes() ? " BYTES" : "") + ")" : "");
        // Default value
        String defaultvalue = catalog_col.getDefaultvalue();
        //VoltType defaulttype = VoltType.get((byte)catalog_col.getDefaulttype());
        boolean nullable = catalog_col.getNullable();
        // TODO: Shouldn't have to check whether the string contains "null"
        if (defaultvalue == null) {
        } else if (defaultvalue.toLowerCase().equals("null") && nullable) {
            defaultvalue = null;
        } else {
            if (col_type == VoltType.TIMESTAMP) {
                if (defaultvalue.startsWith("CURRENT_TIMESTAMP")) {
                    defaultvalue = "CURRENT_TIMESTAMP";
                } else {
                    assert (defaultvalue.matches("[0-9]+"));
                    long epoch = Long.parseLong(defaultvalue);
                    Date d = new Date(epoch / 1000);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    defaultvalue = "\'" + sdf.format(d) + "." + StringUtils.leftPad(String.valueOf(epoch % 1000000), 6, "0") + "\'";
                }
            } else {
                // XXX: if (defaulttype != VoltType.VOLTFUNCTION) {
                // TODO: Escape strings properly
                defaultvalue = defaultvalue.replace("\'", "\'\'");
                defaultvalue = "'" + defaultvalue + "'";
            }
        }
        if (defaultvalue == null) {
            table_sb.append((!nullable ? " NOT NULL" : ""));
        } else {
            table_sb.append(" DEFAULT ").append(defaultvalue != null ? defaultvalue : "NULL").append(!nullable ? " NOT NULL" : "");
        }
        // Single-column constraints
        for (ConstraintRef catalog_const_ref : catalog_col.getConstraints()) {
            Constraint catalog_const = catalog_const_ref.getConstraint();
            ConstraintType const_type = ConstraintType.get(catalog_const.getType());
            // Check if there is another column in our table with the same constraint
            // If there is, then we need to add it to the end of the table definition
            boolean found = false;
            for (Column catalog_other_col : catalog_tbl.getColumns()) {
                if (catalog_other_col.equals(catalog_col))
                    continue;
                if (catalog_other_col.getConstraints().getIgnoreCase(catalog_const.getTypeName()) != null) {
                    found = true;
                    break;
                }
            }
            if (!found) {
                switch(const_type) {
                    case FOREIGN_KEY:
                        {
                            Table catalog_fkey_tbl = catalog_const.getForeignkeytable();
                            Column catalog_fkey_col = null;
                            for (ColumnRef ref : catalog_const.getForeignkeycols()) {
                                catalog_fkey_col = ref.getColumn();
                                // Nasty hack to get first item
                                break;
                            }
                            assert (catalog_fkey_col != null);
                            table_sb.append(" REFERENCES ").append(catalog_fkey_tbl.getTypeName()).append(" (").append(catalog_fkey_col.getTypeName()).append(")");
                            skip_constraints.add(catalog_const);
                            break;
                        }
                    default:
                }
            }
        }
        add = ",\n";
    }
    // Constraints
    for (Constraint catalog_const : catalog_tbl.getConstraints()) {
        if (skip_constraints.contains(catalog_const))
            continue;
        ConstraintType const_type = ConstraintType.get(catalog_const.getType());
        // Primary Keys / Unique Constraints
        if (const_type == ConstraintType.PRIMARY_KEY || const_type == ConstraintType.UNIQUE) {
            Index catalog_idx = catalog_const.getIndex();
            if (!tableIsView) {
                // Get the ConstraintType.
                table_sb.append(add).append(spacer);
                if (!catalog_const.getTypeName().startsWith(HSQLInterface.AUTO_GEN_PREFIX)) {
                    table_sb.append("CONSTRAINT ").append(catalog_const.getTypeName()).append(" ");
                }
                if (const_type == ConstraintType.PRIMARY_KEY || const_type == ConstraintType.UNIQUE) {
                    if (const_type == ConstraintType.PRIMARY_KEY) {
                        table_sb.append("PRIMARY KEY (");
                    } else {
                        if (catalog_idx.getAssumeunique()) {
                            table_sb.append("ASSUMEUNIQUE (");
                        } else {
                            table_sb.append("UNIQUE (");
                        }
                    }
                    String col_add = "";
                    if (catalog_idx.getExpressionsjson() != null && !catalog_idx.getExpressionsjson().equals("")) {
                        String exprStrings = new String();
                        StmtTargetTableScan tableScan = new StmtTargetTableScan(catalog_tbl);
                        try {
                            List<AbstractExpression> expressions = AbstractExpression.fromJSONArrayString(catalog_idx.getExpressionsjson(), tableScan);
                            String sep = "";
                            for (AbstractExpression expr : expressions) {
                                exprStrings += sep + expr.explain(catalog_tbl.getTypeName());
                                sep = ",";
                            }
                        } catch (JSONException e) {
                        }
                        table_sb.append(col_add).append(exprStrings);
                    } else {
                        for (ColumnRef catalog_colref : CatalogUtil.getSortedCatalogItems(catalog_idx.getColumns(), "index")) {
                            table_sb.append(col_add).append(catalog_colref.getColumn().getTypeName());
                            col_add = ", ";
                        }
                    // FOR
                    }
                    table_sb.append(")");
                }
            }
            if (catalog_idx.getTypeName().startsWith(HSQLInterface.AUTO_GEN_PREFIX) || catalog_idx.getTypeName().startsWith(HSQLInterface.AUTO_GEN_MATVIEW)) {
                skip_indexes.add(catalog_idx);
            }
        // Foreign Key
        } else if (const_type == ConstraintType.FOREIGN_KEY) {
            Table catalog_fkey_tbl = catalog_const.getForeignkeytable();
            String col_add = "";
            String our_columns = "";
            String fkey_columns = "";
            for (ColumnRef catalog_colref : catalog_const.getForeignkeycols()) {
                // The name of the ColumnRef is the column in our base table
                Column our_column = catalog_tbl.getColumns().getIgnoreCase(catalog_colref.getTypeName());
                assert (our_column != null);
                our_columns += col_add + our_column.getTypeName();
                Column fkey_column = catalog_colref.getColumn();
                assert (fkey_column != null);
                fkey_columns += col_add + fkey_column.getTypeName();
                col_add = ", ";
            }
            table_sb.append(add).append(spacer + "CONSTRAINT ").append(catalog_const.getTypeName()).append(" FOREIGN KEY (").append(our_columns).append(") REFERENCES ").append(catalog_fkey_tbl.getTypeName()).append(" (").append(fkey_columns).append(")");
        }
        skip_constraints.add(catalog_const);
    }
    if (catalog_tbl.getTuplelimit() != Integer.MAX_VALUE) {
        table_sb.append(add).append(spacer + "LIMIT PARTITION ROWS ").append(String.valueOf(catalog_tbl.getTuplelimit()));
        String deleteStmt = CatalogUtil.getLimitPartitionRowsDeleteStmt(catalog_tbl);
        if (deleteStmt != null) {
            if (deleteStmt.endsWith(";")) {
                // StatementCompiler appends the semicolon, we don't want it here.
                deleteStmt = deleteStmt.substring(0, deleteStmt.length() - 1);
            }
            table_sb.append("\n" + spacer + spacer + "EXECUTE (").append(deleteStmt).append(")");
        }
    }
    if (viewQuery != null) {
        table_sb.append("\n) AS \n");
        table_sb.append(spacer).append(viewQuery).append(";\n");
    } else {
        table_sb.append("\n);\n");
    }
    // We've built the full CREATE TABLE statement for this table,
    // Append the generated table schema to the canonical DDL StringBuilder
    sb.append(table_sb.toString());
    // Partition Table for regular tables (non-streams)
    if (catalog_tbl.getPartitioncolumn() != null && viewQuery == null && !isExportOnly) {
        sb.append("PARTITION TABLE ").append(catalog_tbl.getTypeName()).append(" ON COLUMN ").append(catalog_tbl.getPartitioncolumn().getTypeName()).append(";\n");
    }
    // All other Indexes
    for (Index catalog_idx : catalog_tbl.getIndexes()) {
        if (skip_indexes.contains(catalog_idx))
            continue;
        if (catalog_idx.getUnique()) {
            if (catalog_idx.getAssumeunique()) {
                sb.append("CREATE ASSUMEUNIQUE INDEX ");
            } else {
                sb.append("CREATE UNIQUE INDEX ");
            }
        } else {
            sb.append("CREATE INDEX ");
        }
        sb.append(catalog_idx.getTypeName()).append(" ON ").append(catalog_tbl.getTypeName()).append(" (");
        add = "";
        String jsonstring = catalog_idx.getExpressionsjson();
        if (jsonstring.isEmpty()) {
            for (ColumnRef catalog_colref : CatalogUtil.getSortedCatalogItems(catalog_idx.getColumns(), "index")) {
                sb.append(add).append(catalog_colref.getColumn().getTypeName());
                add = ", ";
            }
        } else {
            List<AbstractExpression> indexedExprs = null;
            try {
                indexedExprs = AbstractExpression.fromJSONArrayString(jsonstring, new StmtTargetTableScan(catalog_tbl));
            } catch (JSONException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            if (indexedExprs != null) {
                for (AbstractExpression expr : indexedExprs) {
                    sb.append(add).append(expr.explain(catalog_tbl.getTypeName()));
                    add = ", ";
                }
            }
        }
        sb.append(")");
        String jsonPredicate = catalog_idx.getPredicatejson();
        if (!jsonPredicate.isEmpty()) {
            try {
                AbstractExpression predicate = AbstractExpression.fromJSONString(jsonPredicate, new StmtTargetTableScan(catalog_tbl));
                sb.append(" WHERE ").append(predicate.explain(catalog_tbl.getTypeName()));
            } catch (JSONException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        sb.append(";\n");
    }
    if (catalog_tbl.getIsdred()) {
        sb.append("DR TABLE ").append(catalog_tbl.getTypeName()).append(";\n");
    }
    sb.append("\n");
    // statement to whoever might be interested (DDLCompiler, I'm looking in your direction)
    return table_sb.toString();
}