Examples with Constraint org.h2.constraint.Constraint used on opensource projects

Example 16 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class Column method addCheckConstraint.

/**
 * Add a check constraint expression to this column. An existing check
 * constraint constraint is added using AND.
 *
 * @param session the session
 * @param expr the (additional) constraint
 */
public void addCheckConstraint(Session session, Expression expr) {
    if (expr == null) {
        return;
    }
    resolver = new SingleColumnResolver(this);
    synchronized (this) {
        String oldName = name;
        if (name == null) {
            name = "VALUE";
        }
        expr.mapColumns(resolver, 0);
        name = oldName;
    }
    expr = expr.optimize(session);
    resolver.setValue(ValueNull.INSTANCE);
    // check if the column is mapped
    synchronized (this) {
        expr.getValue(session);
    }
    if (checkConstraint == null) {
        checkConstraint = expr;
    } else {
        checkConstraint = new ConditionAndOr(ConditionAndOr.AND, checkConstraint, expr);
    }
    checkConstraintSQL = getCheckConstraintSQL(session, name);
}

Example 17 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class TestView method testViewConstraintFromColumnExpression.

/**
 * Make sure that the table constraint is still available when create a view
 * of other table.
 */
private void testViewConstraintFromColumnExpression() throws SQLException {
    deleteDb("view");
    Connection conn = getConnection("view");
    Statement stat = conn.createStatement();
    stat.execute("create table t0(id1 int primary key CHECK ((ID1 % 2) = 0))");
    stat.execute("create table t1(id2 int primary key CHECK ((ID2 % 1) = 0))");
    stat.execute("insert into t0 values(0)");
    stat.execute("insert into t1 values(1)");
    stat.execute("create view v1 as select * from t0,t1");
    // Check with ColumnExpression
    ResultSet rs = stat.executeQuery("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'V1'");
    assertTrue(rs.next());
    assertEquals("ID1", rs.getString("COLUMN_NAME"));
    assertEquals("((ID1 % 2) = 0)", rs.getString("CHECK_CONSTRAINT"));
    assertTrue(rs.next());
    assertEquals("ID2", rs.getString("COLUMN_NAME"));
    assertEquals("((ID2 % 1) = 0)", rs.getString("CHECK_CONSTRAINT"));
    // Check with AliasExpression
    stat.execute("create view v2 as select ID1 key1,ID2 key2 from t0,t1");
    rs = stat.executeQuery("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'V2'");
    assertTrue(rs.next());
    assertEquals("KEY1", rs.getString("COLUMN_NAME"));
    assertEquals("((KEY1 % 2) = 0)", rs.getString("CHECK_CONSTRAINT"));
    assertTrue(rs.next());
    assertEquals("KEY2", rs.getString("COLUMN_NAME"));
    assertEquals("((KEY2 % 1) = 0)", rs.getString("CHECK_CONSTRAINT"));
    // Check hide of constraint if column is an Operation
    stat.execute("create view v3 as select ID1 + 1 ID1, ID2 + 1 ID2 from t0,t1");
    rs = stat.executeQuery("select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'V3'");
    assertTrue(rs.next());
    assertEquals("ID1", rs.getString("COLUMN_NAME"));
    assertEquals("", rs.getString("CHECK_CONSTRAINT"));
    assertTrue(rs.next());
    assertEquals("ID2", rs.getString("COLUMN_NAME"));
    assertEquals("", rs.getString("CHECK_CONSTRAINT"));
    conn.close();
    deleteDb("view");
}

Example 18 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class TestSpatial method testInsertNull.

private void testInsertNull() throws SQLException {
    deleteDb("spatial");
    try (Connection conn = getConnection(URL)) {
        Statement stat = conn.createStatement();
        stat.execute("\n" + "drop table if exists PUBLIC.DUMMY_12;\n" + "CREATE TABLE PUBLIC.DUMMY_12 (\n" + "    \"fid\" serial,\n" + "    Z_ID INTEGER,\n" + "    GEOM GEOMETRY,\n" + "    CONSTRAINT CONSTRAINT_DUMMY_12 PRIMARY KEY (\"fid\")\n" + ");\n" + "CREATE INDEX PRIMARY_KEY_DUMMY_12 ON PUBLIC.DUMMY_12 (\"fid\");\n" + "CREATE spatial INDEX PUBLIC_DUMMY_12_SPATIAL_INDEX_ ON PUBLIC.DUMMY_12 (GEOM);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (123,3125163,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (124,3125164,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (125,3125173,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (126,3125174,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (127,3125175,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (128,3125176,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (129,3125177,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (130,3125178,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (131,3125179,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (132,3125180,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (133,3125335,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (134,3125336,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (135,3125165,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (136,3125337,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (137,3125338,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (138,3125339,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (139,3125340,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (140,3125341,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (141,3125342,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (142,3125343,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (143,3125344,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (144,3125345,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (145,3125346,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (146,3125166,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (147,3125347,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (148,3125348,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (149,3125349,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (150,3125350,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (151,3125351,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (152,3125352,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (153,3125353,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (154,3125354,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (155,3125355,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (156,3125356,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (157,3125167,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (158,3125357,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (159,3125358,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (160,3125359,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (161,3125360,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (162,3125361,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (163,3125362,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (164,3125363,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (165,3125364,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (166,3125365,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (167,3125366,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (168,3125168,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (169,3125367,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (170,3125368,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (171,3125369,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (172,3125370,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (173,3125169,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (174,3125170,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (175,3125171,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (176,3125172,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (177,-2,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (178,-1,NULL);\n" + "INSERT INTO PUBLIC.DUMMY_12 (\"fid\",Z_ID,GEOM) VALUES (179," + "-1,NULL);");
        try (ResultSet rs = stat.executeQuery("select * from DUMMY_12")) {
            assertTrue(rs.next());
        }
    }
    deleteDb("spatial");
}

Example 19 with Constraint

use of org.h2.constraint.Constraint in project ignite by apache.

the class GridSqlQueryParser method parseCreateTable.

/**
 * Parse {@code CREATE TABLE} statement.
 *
 * @param createTbl {@code CREATE TABLE} statement.
 * @see <a href="http://h2database.com/html/grammar.html#create_table">H2 {@code CREATE TABLE} spec.</a>
 */
private GridSqlCreateTable parseCreateTable(CreateTable createTbl) {
    GridSqlCreateTable res = new GridSqlCreateTable();
    res.templateName(QueryUtils.TEMPLATE_PARTITIONED);
    Query qry = CREATE_TABLE_QUERY.get(createTbl);
    if (qry != null) {
        throw new IgniteSQLException("CREATE TABLE ... AS ... syntax is not supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    List<DefineCommand> constraints = CREATE_TABLE_CONSTRAINTS.get(createTbl);
    if (F.isEmpty(constraints)) {
        throw new IgniteSQLException("No PRIMARY KEY defined for CREATE TABLE", IgniteQueryErrorCode.PARSING);
    }
    if (constraints.size() > 1) {
        throw new IgniteSQLException("Too many constraints - only PRIMARY KEY is supported for CREATE TABLE", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    DefineCommand constraint = constraints.get(0);
    if (!(constraint instanceof AlterTableAddConstraint)) {
        throw new IgniteSQLException("Unsupported type of constraint for CREATE TABLE - only PRIMARY KEY " + "is supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    AlterTableAddConstraint alterTbl = (AlterTableAddConstraint) constraint;
    if (alterTbl.getType() != Command.ALTER_TABLE_ADD_CONSTRAINT_PRIMARY_KEY) {
        throw new IgniteSQLException("Unsupported type of constraint for CREATE TABLE - only PRIMARY KEY " + "is supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    Schema schema = SCHEMA_COMMAND_SCHEMA.get(createTbl);
    res.schemaName(schema.getName());
    CreateTableData data = CREATE_TABLE_DATA.get(createTbl);
    if (data.globalTemporary) {
        throw new IgniteSQLException("GLOBAL TEMPORARY keyword is not supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    if (data.temporary) {
        throw new IgniteSQLException("TEMPORARY keyword is not supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    if (data.isHidden) {
        throw new IgniteSQLException("HIDDEN keyword is not supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    if (!data.persistIndexes) {
        throw new IgniteSQLException("MEMORY and NOT PERSISTENT keywords are not supported", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    LinkedHashMap<String, GridSqlColumn> cols = new LinkedHashMap<>(data.columns.size());
    for (Column col : data.columns) {
        if (cols.put(col.getName(), parseColumn(col)) != null)
            throw new IgniteSQLException("Duplicate column name: " + col.getName(), IgniteQueryErrorCode.PARSING);
    }
    if (cols.containsKey(QueryUtils.KEY_FIELD_NAME.toUpperCase()) || cols.containsKey(QueryUtils.VAL_FIELD_NAME.toUpperCase())) {
        throw new IgniteSQLException("Direct specification of _KEY and _VAL columns is forbidden", IgniteQueryErrorCode.PARSING);
    }
    IndexColumn[] pkIdxCols = CREATE_TABLE_PK.get(createTbl);
    if (F.isEmpty(pkIdxCols))
        throw new AssertionError("No PRIMARY KEY columns specified");
    LinkedHashSet<String> pkCols = new LinkedHashSet<>();
    for (IndexColumn pkIdxCol : pkIdxCols) {
        GridSqlColumn gridCol = cols.get(pkIdxCol.columnName);
        if (gridCol == null) {
            throw new IgniteSQLException("PRIMARY KEY column is not defined: " + pkIdxCol.columnName, IgniteQueryErrorCode.PARSING);
        }
        pkCols.add(gridCol.columnName());
    }
    int keyColsNum = pkCols.size();
    int valColsNum = cols.size() - keyColsNum;
    if (valColsNum == 0) {
        throw new IgniteSQLException("Table must have at least one non PRIMARY KEY column.", IgniteQueryErrorCode.UNSUPPORTED_OPERATION);
    }
    res.columns(cols);
    res.primaryKeyColumns(pkCols);
    res.tableName(data.tableName);
    res.ifNotExists(CREATE_TABLE_IF_NOT_EXISTS.get(createTbl));
    List<String> extraParams = data.tableEngineParams != null ? new ArrayList<String>() : null;
    if (data.tableEngineParams != null)
        for (String s : data.tableEngineParams) extraParams.addAll(F.asList(s.split(",")));
    res.params(extraParams);
    if (!F.isEmpty(extraParams)) {
        Map<String, String> params = new HashMap<>();
        for (String p : extraParams) {
            String[] parts = p.split(PARAM_NAME_VALUE_SEPARATOR);
            if (parts.length > 2) {
                throw new IgniteSQLException("Invalid parameter (key[=value] expected): " + p, IgniteQueryErrorCode.PARSING);
            }
            String name = parts[0].trim().toUpperCase();
            String val = parts.length > 1 ? parts[1].trim() : null;
            if (F.isEmpty(name)) {
                throw new IgniteSQLException("Invalid parameter (key[=value] expected): " + p, IgniteQueryErrorCode.PARSING);
            }
            if (params.put(name, val) != null)
                throw new IgniteSQLException("Duplicate parameter: " + p, IgniteQueryErrorCode.PARSING);
        }
        for (Map.Entry<String, String> e : params.entrySet()) processExtraParam(e.getKey(), e.getValue(), res);
    }
    // Process key wrapping.
    Boolean wrapKey = res.wrapKey();
    if (wrapKey != null && !wrapKey) {
        if (keyColsNum > 1) {
            throw new IgniteSQLException(PARAM_WRAP_KEY + " cannot be false when composite primary key exists.", IgniteQueryErrorCode.PARSING);
        }
        if (!F.isEmpty(res.keyTypeName())) {
            throw new IgniteSQLException(PARAM_WRAP_KEY + " cannot be false when " + PARAM_KEY_TYPE + " is set.", IgniteQueryErrorCode.PARSING);
        }
    }
    boolean wrapKey0 = (res.wrapKey() != null && res.wrapKey()) || !F.isEmpty(res.keyTypeName()) || keyColsNum > 1;
    res.wrapKey(wrapKey0);
    // Process value wrapping.
    Boolean wrapVal = res.wrapValue();
    if (wrapVal != null && !wrapVal) {
        if (valColsNum > 1) {
            throw new IgniteSQLException(PARAM_WRAP_VALUE + " cannot be false when multiple non-primary key " + "columns exist.", IgniteQueryErrorCode.PARSING);
        }
        if (!F.isEmpty(res.valueTypeName())) {
            throw new IgniteSQLException(PARAM_WRAP_VALUE + " cannot be false when " + PARAM_VAL_TYPE + " is set.", IgniteQueryErrorCode.PARSING);
        }
        res.wrapValue(false);
    } else
        // By default value is always wrapped to allow for ALTER TABLE ADD COLUMN commands.
        res.wrapValue(true);
    if (!F.isEmpty(res.valueTypeName()) && F.eq(res.keyTypeName(), res.valueTypeName())) {
        throw new IgniteSQLException("Key and value type names " + "should be different for CREATE TABLE: " + res.valueTypeName(), IgniteQueryErrorCode.PARSING);
    }
    if (res.affinityKey() == null) {
        LinkedHashSet<String> pkCols0 = res.primaryKeyColumns();
        if (!F.isEmpty(pkCols0) && pkCols0.size() == 1 && wrapKey0)
            res.affinityKey(pkCols0.iterator().next());
    }
    return res;
}

Example 20 with Constraint

use of org.h2.constraint.Constraint in project h2database by h2database.

the class ConstraintReferential method getShortDescription.

/**
 * Get a short description of the constraint. This includes the constraint
 * name (if set), and the constraint expression.
 *
 * @param searchIndex the index, or null
 * @param check the row, or null
 * @return the description
 */
private String getShortDescription(Index searchIndex, SearchRow check) {
    StatementBuilder buff = new StatementBuilder(getName());
    buff.append(": ").append(table.getSQL()).append(" FOREIGN KEY(");
    for (IndexColumn c : columns) {
        buff.appendExceptFirst(", ");
        buff.append(c.getSQL());
    }
    buff.append(") REFERENCES ").append(refTable.getSQL()).append('(');
    buff.resetCount();
    for (IndexColumn r : refColumns) {
        buff.appendExceptFirst(", ");
        buff.append(r.getSQL());
    }
    buff.append(')');
    if (searchIndex != null && check != null) {
        buff.append(" (");
        buff.resetCount();
        Column[] cols = searchIndex.getColumns();
        int len = Math.min(columns.length, cols.length);
        for (int i = 0; i < len; i++) {
            int idx = cols[i].getColumnId();
            Value c = check.getValue(idx);
            buff.appendExceptFirst(", ");
            buff.append(c == null ? "" : c.toString());
        }
        buff.append(')');
    }
    return buff.toString();
}