Examples with Trigger org.h2.api.Trigger used on opensource projects

Example 31 with Trigger

use of org.h2.api.Trigger in project h2database by h2database.

the class TestTriggersConstraints method testCheckConstraintErrorMessage.

private void testCheckConstraintErrorMessage() throws SQLException {
    Connection conn = getConnection("trigger");
    Statement stat = conn.createStatement();
    stat.execute("create table companies(id identity)");
    stat.execute("create table departments(id identity, " + "company_id int not null, " + "foreign key(company_id) references companies(id))");
    stat.execute("create table connections (id identity, company_id int not null, " + "first int not null, second int not null, " + "foreign key (company_id) references companies(id), " + "foreign key (first) references departments(id), " + "foreign key (second) references departments(id), " + "check (select departments.company_id from departments, companies where " + "       departments.id in (first, second)) = company_id)");
    stat.execute("insert into companies(id) values(1)");
    stat.execute("insert into departments(id, company_id) " + "values(10, 1)");
    stat.execute("insert into departments(id, company_id) " + "values(20, 1)");
    assertThrows(ErrorCode.CHECK_CONSTRAINT_INVALID, stat).execute("insert into connections(id, company_id, first, second) " + "values(100, 1, 10, 20)");
    stat.execute("drop table connections");
    stat.execute("drop table departments");
    stat.execute("drop table companies");
    conn.close();
}

Example 32 with Trigger

use of org.h2.api.Trigger in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method init.

/**
 * Initialize the fulltext support for a new database
 *
 * This method should be called from NxtDbVersion when performing the database version update
 * that enables NRS fulltext search support
 */
public static void init() {
    String ourClassName = FullTextTrigger.class.getName();
    try (Connection conn = Db.db.getConnection();
        Statement stmt = conn.createStatement();
        Statement qstmt = conn.createStatement()) {
        // 
        // Check if we have already been initialized.
        // 
        boolean alreadyInitialized = true;
        boolean triggersExist = false;
        try (ResultSet rs = qstmt.executeQuery("SELECT JAVA_CLASS FROM INFORMATION_SCHEMA.TRIGGERS " + "WHERE SUBSTRING(TRIGGER_NAME, 0, 4) = 'FTL_'")) {
            while (rs.next()) {
                triggersExist = true;
                if (!rs.getString(1).startsWith(ourClassName)) {
                    alreadyInitialized = false;
                }
            }
        }
        if (triggersExist && alreadyInitialized) {
            Logger.logInfoMessage("NRS fulltext support is already initialized");
            return;
        }
        // 
        // We need to delete an existing Lucene index since the V3 file format is not compatible with V5
        // 
        getIndexPath(conn);
        removeIndexFiles(conn);
        // 
        // Drop the H2 Lucene V3 function aliases
        // 
        stmt.execute("DROP ALIAS IF EXISTS FTL_INIT");
        stmt.execute("DROP ALIAS IF EXISTS FTL_CREATE_INDEX");
        stmt.execute("DROP ALIAS IF EXISTS FTL_DROP_INDEX");
        stmt.execute("DROP ALIAS IF EXISTS FTL_DROP_ALL");
        stmt.execute("DROP ALIAS IF EXISTS FTL_REINDEX");
        stmt.execute("DROP ALIAS IF EXISTS FTL_SEARCH");
        stmt.execute("DROP ALIAS IF EXISTS FTL_SEARCH_DATA");
        Logger.logInfoMessage("H2 fulltext function aliases dropped");
        // 
        // Create our schema and table
        // 
        stmt.execute("CREATE SCHEMA IF NOT EXISTS FTL");
        stmt.execute("CREATE TABLE IF NOT EXISTS FTL.INDEXES " + "(SCHEMA VARCHAR, TABLE VARCHAR, COLUMNS VARCHAR, PRIMARY KEY(SCHEMA, TABLE))");
        Logger.logInfoMessage("NRS fulltext schema created");
        // 
        try (ResultSet rs = qstmt.executeQuery("SELECT * FROM FTL.INDEXES")) {
            while (rs.next()) {
                String schema = rs.getString("SCHEMA");
                String table = rs.getString("TABLE");
                stmt.execute("DROP TRIGGER IF EXISTS FTL_" + table);
                stmt.execute(String.format("CREATE TRIGGER FTL_%s AFTER INSERT,UPDATE,DELETE ON %s.%s " + "FOR EACH ROW CALL \"%s\"", table, schema, table, ourClassName));
            }
        }
        // 
        // Rebuild the Lucene index since the Lucene V3 index is not compatible with Lucene V5
        // 
        reindex(conn);
        // 
        // Create our function aliases
        // 
        stmt.execute("CREATE ALIAS FTL_CREATE_INDEX FOR \"" + ourClassName + ".createIndex\"");
        stmt.execute("CREATE ALIAS FTL_DROP_INDEX FOR \"" + ourClassName + ".dropIndex\"");
        stmt.execute("CREATE ALIAS FTL_SEARCH NOBUFFER FOR \"" + ourClassName + ".search\"");
        Logger.logInfoMessage("NRS fulltext aliases created");
    } catch (SQLException exc) {
        Logger.logErrorMessage("Unable to initialize NRS fulltext search support", exc);
        throw new RuntimeException(exc.toString(), exc);
    }
}

Example 33 with Trigger

use of org.h2.api.Trigger in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method init.

/**
 * Initialize the trigger (Trigger interface)
 *
 * @param   conn                Database connection
 * @param   schema              Database schema name
 * @param   trigger             Database trigger name
 * @param   table               Database table name
 * @param   before              TRUE if trigger is called before database operation
 * @param   type                Trigger type
 * @throws  SQLException        A SQL error occurred
 */
@Override
public void init(Connection conn, String schema, String trigger, String table, boolean before, int type) throws SQLException {
    // 
    if (!isActive || table.contains("_COPY_")) {
        return;
    }
    // 
    // Access the Lucene index
    // 
    // We need to get the access just once, either in a trigger or in a function alias
    // 
    getIndexAccess(conn);
    // 
    // Get table and index information
    // 
    tableName = schema + "." + table;
    try (Statement stmt = conn.createStatement()) {
        // 
        try (ResultSet rs = stmt.executeQuery("SHOW COLUMNS FROM " + table + " FROM " + schema)) {
            int index = 0;
            while (rs.next()) {
                String columnName = rs.getString("FIELD");
                String columnType = rs.getString("TYPE");
                columnType = columnType.substring(0, columnType.indexOf('('));
                columnNames.add(columnName);
                columnTypes.add(columnType);
                if (columnName.equals("DB_ID")) {
                    dbColumn = index;
                }
                index++;
            }
        }
        if (dbColumn < 0) {
            Logger.logErrorMessage("DB_ID column not found for table " + tableName);
            return;
        }
        // 
        try (ResultSet rs = stmt.executeQuery(String.format("SELECT COLUMNS FROM FTL.INDEXES WHERE SCHEMA = '%s' AND TABLE = '%s'", schema, table))) {
            if (rs.next()) {
                String[] columns = rs.getString(1).split(",");
                for (String column : columns) {
                    int pos = columnNames.indexOf(column);
                    if (pos >= 0) {
                        if (columnTypes.get(pos).equals("VARCHAR")) {
                            indexColumns.add(pos);
                        } else {
                            Logger.logErrorMessage("Indexed column " + column + " in table " + tableName + " is not a string");
                        }
                    } else {
                        Logger.logErrorMessage("Indexed column " + column + " not found in table " + tableName);
                    }
                }
            }
        }
        if (indexColumns.isEmpty()) {
            Logger.logErrorMessage("No indexed columns found for table " + tableName);
            return;
        }
        // 
        // Trigger is enabled
        // 
        isEnabled = true;
        indexTriggers.put(tableName, this);
    } catch (SQLException exc) {
        Logger.logErrorMessage("Unable to get table information", exc);
    }
}

Example 34 with Trigger

use of org.h2.api.Trigger in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method dropAll.

/**
 * Drop all fulltext indexes
 *
 * @param   conn                SQL connection
 * @throws  SQLException        Unable to drop fulltext indexes
 */
public static void dropAll(Connection conn) throws SQLException {
    // 
    try (Statement qstmt = conn.createStatement();
        Statement stmt = conn.createStatement();
        ResultSet rs = qstmt.executeQuery("SELECT TABLE FROM FTL.INDEXES")) {
        while (rs.next()) {
            String table = rs.getString(1);
            stmt.execute("DROP TRIGGER IF EXISTS FTL_" + table);
        }
        stmt.execute("TRUNCATE TABLE FTL.INDEXES");
        indexTriggers.clear();
    }
    // 
    // Delete the Lucene index
    // 
    removeIndexFiles(conn);
}

Example 35 with Trigger

use of org.h2.api.Trigger in project elastic-core-maven by OrdinaryDude.

the class FullTextTrigger method dropIndex.

/**
 * Drop the fulltext index for a table
 *
 * @param   conn                SQL connection
 * @param   schema              Schema name
 * @param   table               Table name
 * @throws  SQLException        Unable to drop fulltext index
 */
public static void dropIndex(Connection conn, String schema, String table) throws SQLException {
    String upperSchema = schema.toUpperCase();
    String upperTable = table.toUpperCase();
    boolean reindex = false;
    // 
    try (Statement qstmt = conn.createStatement();
        Statement stmt = conn.createStatement()) {
        try (ResultSet rs = qstmt.executeQuery(String.format("SELECT COLUMNS FROM FTL.INDEXES WHERE SCHEMA = '%s' AND TABLE = '%s'", upperSchema, upperTable))) {
            if (rs.next()) {
                stmt.execute("DROP TRIGGER IF EXISTS FTL_" + upperTable);
                stmt.execute(String.format("DELETE FROM FTL.INDEXES WHERE SCHEMA = '%s' AND TABLE = '%s'", upperSchema, upperTable));
                reindex = true;
            }
        }
    }
    // 
    if (reindex) {
        reindex(conn);
    }
}