/*
 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
 * Version 1.0, and under the Eclipse Public License, Version 1.0
 * (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.samples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

/**
 * Demonstrates the benefit of using the CREATE TABLE ... AS SELECT
 * optimization.
 */
public class DirectInsert {

    /**
     * This method is called when executing this sample application from the
     * command line.
     *
     * @param args the command line parameters
     */
    public static void main(String... args) throws Exception {
        Class.forName("org.h2.Driver");
        DeleteDbFiles.execute("~", "test", true);
        String url = "jdbc:h2:~/test";
        initialInsert(url, 200000);
        for (int i = 0; i < 3; i++) {
            createAsSelect(url, true);
            createAsSelect(url, false);
        }
    }

    private static void initialInsert(String url, int len) throws SQLException {
        Connection conn = DriverManager.getConnection(url + ";LOG=0");
        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEST");
        stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
        PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, 'Test' || SPACE(100))");
        long time = System.currentTimeMillis();
        for (int i = 0; i < len; i++) {
            long now = System.currentTimeMillis();
            if (now > time + 1000) {
                time = now;
                System.out.println("Inserting " + (100L * i / len) + "%");
            }
            prep.setInt(1, i);
            prep.execute();
        }
        conn.commit();
        prep.close();
        stat.close();
        conn.close();
    }

    private static void createAsSelect(String url, boolean optimize) throws SQLException {
        Connection conn = DriverManager.getConnection(url + ";OPTIMIZE_INSERT_FROM_SELECT=" + optimize);
        Statement stat = conn.createStatement();
        stat.execute("DROP TABLE IF EXISTS TEST2");
        System.out.println("CREATE TABLE ... AS SELECT " + (optimize ? "(optimized)" : ""));
        long time = System.currentTimeMillis();
        stat.execute("CREATE TABLE TEST2 AS SELECT * FROM TEST");
        System.out.printf("%.3f sec.\n", (System.currentTimeMillis() - time) / 1000.0);
        stat.execute("INSERT INTO TEST2 SELECT * FROM TEST2");
        stat.close();
        conn.close();
    }

}