CreateViewTest.java

/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package my.test.command.ddl;

import my.test.TestBase;

public class CreateViewTest extends TestBase {
    public static void main(String[] args) throws Exception {

        new CreateViewTest().start();
        System.out.println(Integer.bitCount(3));
        System.out.println(Integer.bitCount(1));
        System.out.println(Integer.bitCount(2));
        System.out.println(Integer.bitCount(7));
    }

    // 测试org.h2.command.Parser.parseCreateView(boolean, boolean)
    // org.h2.command.ddl.CreateView
    @Override
    public void startInternal() throws Exception {
        executeUpdate("drop table IF EXISTS CreateViewTest CASCADE");
        executeUpdate("create table IF NOT EXISTS CreateViewTest(id int, name varchar(500), b boolean)");
        executeUpdate("CREATE INDEX IF NOT EXISTS CreateViewTestIndex ON CreateViewTest(name)");

        executeUpdate("insert into CreateViewTest(id, name, b) values(1, 'a1', true)");
        executeUpdate("insert into CreateViewTest(id, name, b) values(1, 'b1', true)");
        executeUpdate("insert into CreateViewTest(id, name, b) values(2, 'a2', false)");
        executeUpdate("insert into CreateViewTest(id, name, b) values(2, 'b2', true)");
        executeUpdate("insert into CreateViewTest(id, name, b) values(3, 'a3', false)");
        executeUpdate("insert into CreateViewTest(id, name, b) values(3, 'b3', true)");

        // executeUpdate("DROP VIEW IF EXISTS my_view");
        sql = "CREATE OR REPLACE FORCE VIEW IF NOT EXISTS my_view COMMENT IS 'my view'(f1,f2) " //
                + "AS SELECT id,name FROM CreateViewTest";

        executeUpdate();

        sql = "CREATE OR REPLACE FORCE VIEW my_view COMMENT IS 'my view'(f1,f2) " //
                + "AS SELECT id,name FROM CreateViewTest";

        // select字段个数比view字段多的情况，多出来的按select字段原来的算
        // 这里实际是f1、name
        sql = "CREATE OR REPLACE FORCE VIEW my_view COMMENT IS 'my view'(f1) " //
                + "AS SELECT id,name FROM CreateViewTest";

        // select字段个数比view字段少的情况，view中少的字段被忽略
        // 这里实际是f1，而f2被忽略了，也不提示错误
        sql = "CREATE OR REPLACE FORCE VIEW my_view COMMENT IS 'my view'(f1, f2) " //
                + "AS SELECT id FROM CreateViewTest";

        // 不管加不加FORCE，跟上面也一样
        sql = "CREATE OR REPLACE VIEW my_view COMMENT IS 'my view'(f1, f2) " //
                + "AS SELECT id FROM CreateViewTest";

        sql = "CREATE OR REPLACE FORCE VIEW my_view COMMENT IS 'my view'(f1,f2) " //
                + "AS SELECT id,name FROM CreateViewTest";

        executeUpdate("CREATE OR REPLACE FORCE VIEW view1 AS SELECT f1 FROM my_view");
        executeUpdate("CREATE OR REPLACE FORCE VIEW view2 AS SELECT f2 FROM my_view");

        // sql = "CREATE OR REPLACE FORCE VIEW my_view COMMENT IS 'my view'(f1,f2) " //
        // + "AS SELECT top 2 id,name FROM CreateViewTest order by id";
        //
        // 如果是这种情况，接下来要查视图时也要按CreateViewTest的字段名来查
        // sql = "CREATE OR REPLACE FORCE VIEW IF NOT EXISTS my_view " //
        // + "AS SELECT id,name FROM CreateViewTest";

        // 目前不支持参数:
        // org.h2.jdbc.JdbcSQLException: Feature not supported: "parameters in views"; SQL statement:
        // sql =
        // "CREATE OR REPLACE FORCE VIEW IF NOT EXISTS my_view (f1,f2) AS SELECT id,name FROM CreateViewTest where id=?";
        // ps = conn.prepareStatement(sql);
        // ps.setInt(1, 2);
        // ps.executeUpdate();
        executeUpdate(sql);

        sql = "select * from my_view where f1 > 2";
        sql = "select * from my_view where f2 > 'b1'";
        sql = "select * from my_view where f2 between 'b1' and 'b2'";

        // executeUpdate("SET BATCH_JOINS 1");

        sql = "select * from my_view where f1=2 and f2 between 'b1' and 'b2'";
        executeQuery();

        // sql = "select name from (select id,name from CreateViewTest where id=? and name=?) where name='b2'";
        // ps = conn.prepareStatement(sql);
        // ps.setInt(1, 2);
        // ps.setString(2, "b2");
        // ps.executeQuery();

        // sql = "select * from CreateViewTest";

        // 测试org.h2.command.Parser.parserWith()
        // executeUpdate("CREATE LOCAL TEMPORARY TABLE IF NOT EXISTS my_tmp_table(f1 int)");
        // executeUpdate("DROP VIEW IF EXISTS my_tmp_table");
        // executeUpdate("CREATE OR REPLACE FORCE VIEW my_tmp_table AS SELECT f2 FROM my_view");
        // sql =
        // "WITH RECURSIVE my_tmp_table(f1,f2) AS(select id,name from CreateViewTest) select f1, f2 from my_tmp_table";
        // sql = "WITH my_tmp_table(f1,f2) AS(select id,name from CreateViewTest) select f1, f2 from my_tmp_table";

        // AS里面必须是UNION ALL
        sql = "WITH RECURSIVE my_tmp_table(f1,f2) " //
                + "AS(select id,name from CreateViewTest UNION ALL select 1, 2)" //
                + "select f1, f2 from my_tmp_table";

        executeQuery();

        sql = "WITH RECURSIVE my_tmp_table(f1,f2) " //
                + "AS(select id,name from CreateViewTest UNION ALL select id,name from CreateViewTest)" //
                + "select f1, f2 from my_tmp_table";

        executeQuery();

        executeUpdate("drop table IF EXISTS tb");
        executeUpdate("create table IF NOT EXISTS tb(id varchar(3) , pid varchar(3) , name varchar(10))");
        executeUpdate("insert into tb values('002' , 0 , '浙江省')");
        executeUpdate("insert into tb values('001' , 0 , '广东省')");
        executeUpdate("insert into tb values('006' , '002' , '嘉兴市')");
        executeUpdate("insert into tb values('004' , '002' , '杭州市')");
        executeUpdate("insert into tb values('014' , '004' , '上城区')");
        executeUpdate("insert into tb values('015' , '004' , '下城区')");
        executeUpdate("insert into tb values('019' , '001' , '广州市')");
        executeUpdate("insert into tb values('020' , '001' , '深圳市')");

        sql = "WITH RECURSIVE cte (id,name)" //
                + " AS(select a.id,a.name,a.pid from tb a where id='002'" //
                + "    union all select k.id,k.name,k.pid from tb k inner join cte c on c.id = k.pid)" //
                + " select id,name from cte";

        executeQuery();

        sql = "WITH RECURSIVE cte (id,name) " //
                + " AS(select a.id,cast(a.name as varchar(100)) from tb a where id='002'" //
                + "    union all "//
                + "      select k.id,cast(c.name||'>'||k.name as varchar(100)) as name "//
                + "        from tb k inner join cte c on c.id = k.pid)" //
                + " select id,name from cte";

        executeQuery();

        // 必须在from后面加括号，此时from后面的被认为是一个临时视图
        sql = "select f1, f2 from (select id,name from CreateViewTest)"; // f1,f2找不到
        sql = "select id,name from (select id,name from CreateViewTest)";
        executeQuery();

        // 这条不会使得parameters.size>0
        sql = "CREATE OR REPLACE FORCE VIEW IF NOT EXISTS my_view2(f1,f2) " //
                + "AS select id,name from (select id,name from CreateViewTest) where id=? and name=?";

        ps = conn.prepareStatement(sql);
        ps.setInt(1, 2);
        ps.setString(2, "b2");
        try {
            ps.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        // 这条可以
        sql = "CREATE OR REPLACE FORCE VIEW IF NOT EXISTS my_view2(f1,f2) " //
                + "AS select id,name from (select id,name from CreateViewTest where id=? and name=?)";

        ps = conn.prepareStatement(sql);
        ps.setInt(1, 2);
        ps.setString(2, "b2");
        try {
            ps.executeUpdate();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        sql = "select id,name from (select id,name from CreateViewTest where id=? and name=?)";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, 2);
        ps.setString(2, "b2");
        rs = ps.executeQuery();
        printResultSet(rs);

        sql = "select * from my_view";
        sql = "select * from my_view where f1=2 and f2 between 'b1' and 'b2'";

        stmt.setFetchSize(2);
        executeQuery();

        executeUpdate("alter view my_view RECOMPILE");

        executeUpdate("CREATE OR REPLACE FORCE VIEW view5 AS SELECT f1 FROM my_view");
        tryExecuteUpdate("drop view IF EXISTS my_view RESTRICT");
        executeUpdate("drop view IF EXISTS my_view CASCADE");
    }
}