Search in sources :

Example 6 with RunScript

use of org.h2.tools.RunScript in project h2database by h2database.

the class TestMVTableEngine method testRecover.

private void testRecover() throws Exception {
    if (config.memory) {
        return;
    }
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE";
    url = getURL(url, true);
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar)");
    stat.execute("insert into test values(1, 'Hello')");
    stat.execute("create table test2(name varchar)");
    stat.execute("insert into test2 values('Hello World')");
    conn.close();
    Recover.execute(getBaseDir(), getTestName());
    deleteDb(getTestName());
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("runscript from '" + getBaseDir() + "/" + getTestName() + ".h2.sql'");
    ResultSet rs;
    rs = stat.executeQuery("select * from test");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertEquals("Hello", rs.getString(2));
    rs = stat.executeQuery("select * from test2");
    assertTrue(rs.next());
    assertEquals("Hello World", rs.getString(1));
    conn.close();
}
Also used : PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) Connection(java.sql.Connection) JdbcConnection(org.h2.jdbc.JdbcConnection) ResultSet(java.sql.ResultSet)

Example 7 with RunScript

use of org.h2.tools.RunScript in project h2database by h2database.

the class TestRunscript method testCancelScript.

private void testCancelScript() throws Exception {
    if (config.travis) {
        // fails regularly under Travis, not sure why
        return;
    }
    deleteDb("runscript");
    Connection conn;
    conn = getConnection("runscript");
    final Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key) as " + "select x from system_range(1, 20000)");
    stat.execute("script simple drop to '" + getBaseDir() + "/backup.sql'");
    stat.execute("set throttle 1000");
    // need to wait a bit (throttle is only used every 50 ms)
    Thread.sleep(200);
    final String dir = getBaseDir();
    Task task;
    task = new Task() {

        @Override
        public void call() throws SQLException {
            stat.execute("script simple drop to '" + dir + "/backup2.sql'");
        }
    };
    task.execute();
    Thread.sleep(200);
    stat.cancel();
    SQLException e = (SQLException) task.getException();
    assertNotNull(e);
    assertEquals(ErrorCode.STATEMENT_WAS_CANCELED, e.getErrorCode());
    stat.execute("set throttle 1000");
    // need to wait a bit (throttle is only used every 50 ms)
    Thread.sleep(100);
    task = new Task() {

        @Override
        public void call() throws SQLException {
            stat.execute("runscript from '" + dir + "/backup.sql'");
        }
    };
    task.execute();
    Thread.sleep(200);
    stat.cancel();
    e = (SQLException) task.getException();
    assertNotNull(e);
    assertEquals(ErrorCode.STATEMENT_WAS_CANCELED, e.getErrorCode());
    conn.close();
    FileUtils.delete(getBaseDir() + "/backup.sql");
    FileUtils.delete(getBaseDir() + "/backup2.sql");
}
Also used : Task(org.h2.util.Task) SQLException(java.sql.SQLException) Statement(java.sql.Statement) PreparedStatement(java.sql.PreparedStatement) Connection(java.sql.Connection)

Example 8 with RunScript

use of org.h2.tools.RunScript in project h2database by h2database.

the class WebApp method tools.

private String tools() {
    try {
        String toolName = (String) attributes.get("tool");
        session.put("tool", toolName);
        String args = (String) attributes.get("args");
        String[] argList = StringUtils.arraySplit(args, ',', false);
        Tool tool = null;
        if ("Backup".equals(toolName)) {
            tool = new Backup();
        } else if ("Restore".equals(toolName)) {
            tool = new Restore();
        } else if ("Recover".equals(toolName)) {
            tool = new Recover();
        } else if ("DeleteDbFiles".equals(toolName)) {
            tool = new DeleteDbFiles();
        } else if ("ChangeFileEncryption".equals(toolName)) {
            tool = new ChangeFileEncryption();
        } else if ("Script".equals(toolName)) {
            tool = new Script();
        } else if ("RunScript".equals(toolName)) {
            tool = new RunScript();
        } else if ("ConvertTraceFile".equals(toolName)) {
            tool = new ConvertTraceFile();
        } else if ("CreateCluster".equals(toolName)) {
            tool = new CreateCluster();
        } else {
            throw DbException.throwInternalError(toolName);
        }
        ByteArrayOutputStream outBuff = new ByteArrayOutputStream();
        PrintStream out = new PrintStream(outBuff, false, "UTF-8");
        tool.setOut(out);
        try {
            tool.runTool(argList);
            out.flush();
            String o = new String(outBuff.toByteArray(), StandardCharsets.UTF_8);
            String result = PageParser.escapeHtml(o);
            session.put("toolResult", result);
        } catch (Exception e) {
            session.put("toolResult", getStackTrace(0, e, true));
        }
    } catch (Exception e) {
        server.traceError(e);
    }
    return "tools.jsp";
}
Also used : ConvertTraceFile(org.h2.tools.ConvertTraceFile) RunScript(org.h2.tools.RunScript) Script(org.h2.tools.Script) PrintStream(java.io.PrintStream) Backup(org.h2.tools.Backup) ByteArrayOutputStream(java.io.ByteArrayOutputStream) Restore(org.h2.tools.Restore) DeleteDbFiles(org.h2.tools.DeleteDbFiles) DbException(org.h2.message.DbException) InvocationTargetException(java.lang.reflect.InvocationTargetException) SQLException(java.sql.SQLException) JdbcSQLException(org.h2.jdbc.JdbcSQLException) Recover(org.h2.tools.Recover) RunScript(org.h2.tools.RunScript) Tool(org.h2.util.Tool) ChangeFileEncryption(org.h2.tools.ChangeFileEncryption) CreateCluster(org.h2.tools.CreateCluster)

Example 9 with RunScript

use of org.h2.tools.RunScript in project h2database by h2database.

the class WebApp method getResult.

private String getResult(Connection conn, int id, String sql, boolean allowEdit, boolean forceEdit) {
    try {
        sql = sql.trim();
        StringBuilder buff = new StringBuilder();
        String sqlUpper = StringUtils.toUpperEnglish(sql);
        if (sqlUpper.contains("CREATE") || sqlUpper.contains("DROP") || sqlUpper.contains("ALTER") || sqlUpper.contains("RUNSCRIPT")) {
            String sessionId = attributes.getProperty("jsessionid");
            buff.append("<script type=\"text/javascript\">parent['h2menu'].location='tables.do?jsessionid=").append(sessionId).append("';</script>");
        }
        Statement stat;
        DbContents contents = session.getContents();
        if (forceEdit || (allowEdit && contents.isH2())) {
            stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        } else {
            stat = conn.createStatement();
        }
        ResultSet rs;
        long time = System.currentTimeMillis();
        boolean metadata = false;
        int generatedKeys = Statement.NO_GENERATED_KEYS;
        boolean edit = false;
        boolean list = false;
        if (isBuiltIn(sql, "@autocommit_true")) {
            conn.setAutoCommit(true);
            return "${text.result.autoCommitOn}";
        } else if (isBuiltIn(sql, "@autocommit_false")) {
            conn.setAutoCommit(false);
            return "${text.result.autoCommitOff}";
        } else if (isBuiltIn(sql, "@cancel")) {
            stat = session.executingStatement;
            if (stat != null) {
                stat.cancel();
                buff.append("${text.result.statementWasCanceled}");
            } else {
                buff.append("${text.result.noRunningStatement}");
            }
            return buff.toString();
        } else if (isBuiltIn(sql, "@edit")) {
            edit = true;
            sql = sql.substring("@edit".length()).trim();
            session.put("resultSetSQL", sql);
        }
        if (isBuiltIn(sql, "@list")) {
            list = true;
            sql = sql.substring("@list".length()).trim();
        }
        if (isBuiltIn(sql, "@meta")) {
            metadata = true;
            sql = sql.substring("@meta".length()).trim();
        }
        if (isBuiltIn(sql, "@generated")) {
            generatedKeys = Statement.RETURN_GENERATED_KEYS;
            sql = sql.substring("@generated".length()).trim();
        } else if (isBuiltIn(sql, "@history")) {
            buff.append(getCommandHistoryString());
            return buff.toString();
        } else if (isBuiltIn(sql, "@loop")) {
            sql = sql.substring("@loop".length()).trim();
            int idx = sql.indexOf(' ');
            int count = Integer.decode(sql.substring(0, idx));
            sql = sql.substring(idx).trim();
            return executeLoop(conn, count, sql);
        } else if (isBuiltIn(sql, "@maxrows")) {
            int maxrows = (int) Double.parseDouble(sql.substring("@maxrows".length()).trim());
            session.put("maxrows", "" + maxrows);
            return "${text.result.maxrowsSet}";
        } else if (isBuiltIn(sql, "@parameter_meta")) {
            sql = sql.substring("@parameter_meta".length()).trim();
            PreparedStatement prep = conn.prepareStatement(sql);
            buff.append(getParameterResultSet(prep.getParameterMetaData()));
            return buff.toString();
        } else if (isBuiltIn(sql, "@password_hash")) {
            sql = sql.substring("@password_hash".length()).trim();
            String[] p = split(sql);
            return StringUtils.convertBytesToHex(SHA256.getKeyPasswordHash(p[0], p[1].toCharArray()));
        } else if (isBuiltIn(sql, "@prof_start")) {
            if (profiler != null) {
                profiler.stopCollecting();
            }
            profiler = new Profiler();
            profiler.startCollecting();
            return "Ok";
        } else if (isBuiltIn(sql, "@sleep")) {
            String s = sql.substring("@sleep".length()).trim();
            int sleep = 1;
            if (s.length() > 0) {
                sleep = Integer.parseInt(s);
            }
            Thread.sleep(sleep * 1000);
            return "Ok";
        } else if (isBuiltIn(sql, "@transaction_isolation")) {
            String s = sql.substring("@transaction_isolation".length()).trim();
            if (s.length() > 0) {
                int level = Integer.parseInt(s);
                conn.setTransactionIsolation(level);
            }
            buff.append("Transaction Isolation: ").append(conn.getTransactionIsolation()).append("<br />");
            buff.append(Connection.TRANSACTION_READ_UNCOMMITTED).append(": read_uncommitted<br />");
            buff.append(Connection.TRANSACTION_READ_COMMITTED).append(": read_committed<br />");
            buff.append(Connection.TRANSACTION_REPEATABLE_READ).append(": repeatable_read<br />");
            buff.append(Connection.TRANSACTION_SERIALIZABLE).append(": serializable");
        }
        if (sql.startsWith("@")) {
            rs = getMetaResultSet(conn, sql);
            if (rs == null) {
                buff.append("?: ").append(sql);
                return buff.toString();
            }
        } else {
            int maxrows = getMaxrows();
            stat.setMaxRows(maxrows);
            session.executingStatement = stat;
            boolean isResultSet = stat.execute(sql, generatedKeys);
            session.addCommand(sql);
            if (generatedKeys == Statement.RETURN_GENERATED_KEYS) {
                rs = null;
                rs = stat.getGeneratedKeys();
            } else {
                if (!isResultSet) {
                    buff.append("${text.result.updateCount}: ").append(stat.getUpdateCount());
                    time = System.currentTimeMillis() - time;
                    buff.append("<br />(").append(time).append(" ms)");
                    stat.close();
                    return buff.toString();
                }
                rs = stat.getResultSet();
            }
        }
        time = System.currentTimeMillis() - time;
        buff.append(getResultSet(sql, rs, metadata, list, edit, time, allowEdit));
        // }
        if (!edit) {
            stat.close();
        }
        return buff.toString();
    } catch (Throwable e) {
        // throwable: including OutOfMemoryError and so on
        return getStackTrace(id, e, session.getContents().isH2());
    } finally {
        session.executingStatement = null;
    }
}
Also used : Profiler(org.h2.util.Profiler) PreparedStatement(java.sql.PreparedStatement) Statement(java.sql.Statement) DbContents(org.h2.bnf.context.DbContents) ResultSet(java.sql.ResultSet) SimpleResultSet(org.h2.tools.SimpleResultSet) PreparedStatement(java.sql.PreparedStatement)

Example 10 with RunScript

use of org.h2.tools.RunScript in project h2database by h2database.

the class DbUpgrade method upgrade.

private static void upgrade(ConnectionInfo ci, Properties info) throws SQLException {
    String name = ci.getName();
    String data = name + Constants.SUFFIX_OLD_DATABASE_FILE;
    String index = name + ".index.db";
    String lobs = name + ".lobs.db";
    String backupData = data + ".backup";
    String backupIndex = index + ".backup";
    String backupLobs = lobs + ".backup";
    String script = null;
    try {
        if (scriptInTempDir) {
            new File(Utils.getProperty("java.io.tmpdir", ".")).mkdirs();
            script = File.createTempFile("h2dbmigration", "backup.sql").getAbsolutePath();
        } else {
            script = name + ".script.sql";
        }
        String oldUrl = "jdbc:h2v1_1:" + name + ";UNDO_LOG=0;LOG=0;LOCK_MODE=0";
        String cipher = ci.getProperty("CIPHER", null);
        if (cipher != null) {
            oldUrl += ";CIPHER=" + cipher;
        }
        Connection conn = DriverManager.getConnection(oldUrl, info);
        Statement stat = conn.createStatement();
        String uuid = UUID.randomUUID().toString();
        if (cipher != null) {
            stat.execute("script to '" + script + "' cipher aes password '" + uuid + "' --hide--");
        } else {
            stat.execute("script to '" + script + "'");
        }
        conn.close();
        FileUtils.move(data, backupData);
        FileUtils.move(index, backupIndex);
        if (FileUtils.exists(lobs)) {
            FileUtils.move(lobs, backupLobs);
        }
        ci.removeProperty("IFEXISTS", false);
        conn = new JdbcConnection(ci, true);
        stat = conn.createStatement();
        if (cipher != null) {
            stat.execute("runscript from '" + script + "' cipher aes password '" + uuid + "' --hide--");
        } else {
            stat.execute("runscript from '" + script + "'");
        }
        stat.execute("analyze");
        stat.execute("shutdown compact");
        stat.close();
        conn.close();
        if (deleteOldDb) {
            FileUtils.delete(backupData);
            FileUtils.delete(backupIndex);
            FileUtils.deleteRecursive(backupLobs, false);
        }
    } catch (Exception e) {
        if (FileUtils.exists(backupData)) {
            FileUtils.move(backupData, data);
        }
        if (FileUtils.exists(backupIndex)) {
            FileUtils.move(backupIndex, index);
        }
        if (FileUtils.exists(backupLobs)) {
            FileUtils.move(backupLobs, lobs);
        }
        FileUtils.delete(name + ".h2.db");
        throw DbException.toSQLException(e);
    } finally {
        if (script != null) {
            FileUtils.delete(script);
        }
    }
}
Also used : Statement(java.sql.Statement) Connection(java.sql.Connection) JdbcConnection(org.h2.jdbc.JdbcConnection) JdbcConnection(org.h2.jdbc.JdbcConnection) File(java.io.File) DbException(org.h2.message.DbException) SQLException(java.sql.SQLException)

Aggregations

Connection (java.sql.Connection)8 Statement (java.sql.Statement)7 ByteArrayOutputStream (java.io.ByteArrayOutputStream)4 PrintStream (java.io.PrintStream)4 PreparedStatement (java.sql.PreparedStatement)4 ResultSet (java.sql.ResultSet)4 SQLException (java.sql.SQLException)4 Recover (org.h2.tools.Recover)3 SimpleResultSet (org.h2.tools.SimpleResultSet)3 JdbcConnection (org.h2.jdbc.JdbcConnection)2 DbException (org.h2.message.DbException)2 RunScript (org.h2.tools.RunScript)2 ExtendedJdbcTemplate (com.serotonin.db.spring.ExtendedJdbcTemplate)1 DatabaseSchemaDefinition (com.serotonin.m2m2.module.DatabaseSchemaDefinition)1 User (com.serotonin.m2m2.vo.User)1 DefaultDataPointPropertiesTemplateFactory (com.serotonin.m2m2.vo.template.DefaultDataPointPropertiesTemplateFactory)1 File (java.io.File)1 InvocationTargetException (java.lang.reflect.InvocationTargetException)1 DbContents (org.h2.bnf.context.DbContents)1 AlterTableAddConstraint (org.h2.command.ddl.AlterTableAddConstraint)1