| Tea Yu 2005-12-07, 3:40 am |
| Hi Dave,
yeah sorry.... the backend actually returns 0, so there's nothing to do with
the Driver but Postgres...
Tea
> Hi Dave,
>
> Yes it does actually go into the partitioned table, pls find the test case
> below.
>
> Thanks,
> Tea
>
> =========== PostgresqlTest.java ==============
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> import junit.framework.TestCase;
>
> /*
> *
> * $Log$
> */
>
> /**
> * Test Postgresql JDBC driver features
> *
> * @author $Author$
> * @version $Revision$, $Date$
> */
>
> public class PostgresqlTest extends TestCase {
> /**
> * change to your test JDBC URL
> */
> private static final String JDBC_URL = "jdbc:postgresql://"
> + "localhost/test" + "?user=test&password=test";
>
> private static final String DRIVER_CLASS = "org.postgresql.Driver";
>
> private static final String SQL_CREATE_TABLE = "CREATE TABLE tbl_test
"
> + "(msg VARCHAR(255), create_date DATE)";
>
> private static final String SQL_CREATE_PARTITION
= "CREATE TABLE "
> + "tbl_test_2005_12 (CHECK (create_date >= DATE '2005-12-01' "
> + "AND create_date < DATE '2006-01-01')) INHERITS (tbl_test)";
>
> /**
> * when date of insert row falls within 2005-12, insert into
> * tbl_test_2005_12 instead
> */
> private static final String SQL_CREATE_INSERT_RU
LE = "CREATE RULE "
> + "rul_test_2005_12 AS ON INSERT TO tbl_test WHERE
(create_date
> "
> + ">= DATE '2005-12-01' AND create_date < DATE '2006-01-01') "
> + "DO INSTEAD INSERT INTO tbl_test_2005_12 (msg, create_date)
"
> + "VALUES (NEW.msg, NEW.create_date)";
>
> private static final String SQL_DELETE_TABLE = "DROP TABLE tbl_test "
> + "CASCADE";
>
> private Connection conn;
>
> /**
> * the default TestCase constructor
> *
> * @param name
> * @throws ClassNotFoundExcepti
on
> * @throws SQLException
> */
> public PostgresqlTest(Strin
g name) throws ClassNotFoundExcepti
on,
> SQLException {
> super(name);
> Class. forName(DRIVER_CLASS
);
> }
>
> /**
> * remove the test table and its dependents, if any
> *
> * @throws SQLException
> * @throws SQLException
> */
> public void setUp() throws SQLException {
> try {
> conn = DriverManager. getConnection(JDBC_U
RL);
> } catch (SQLException e) {
> if (conn != null) {
> tearDown();
> }
> throw e;
> }
>
> try {
> removeTables();
> } catch (SQLException e) {
> // the test tables may not exist initially
> }
> }
>
> /**
> * clean up the connection
> */
> public void tearDown() throws SQLException {
> conn.close();
> }
>
> /**
> * create a test table without partition, insert a row and expects
> affected
> * rows = 1
> * <p>
> * this test always passes
> *
> * @throws SQLException
> */
> public void testNoPartition() throws SQLException {
> boolean cleanUp = false;
> try {
> createWithoutPartiti
on();
> cleanUp = true;
> int rows = new SQLTemplate(conn).execute("INSERT INTO tbl_test
"
> + "(msg, create_date) VALUES " + "("
> + "'noPartition', date '2005-12-01')");
> assertEquals(1, rows);
> } finally {
> if (cleanUp) {
> removeTables();
> }
> }
> }
>
> /**
> * create a test table with partition, insert a row and expects
affected
> * rows = 1
> * <p>
> * this test fails on JDBC drivers <= 8.2dev-500 JDBC x
> *
> * @throws SQLException
> */
> public void testAgainstPartition
() throws SQLException {
> boolean cleanUp = false;
> try {
> createWithPartition(
);
> cleanUp = true;
> int rows = new SQLTemplate(conn).execute("INSERT INTO tbl_test
"
> + "(msg, create_date) VALUES " + "("
> + "'yesPartition', date '2005-12-02')");
> assertEquals(1, rows);
> } finally {
> if (cleanUp) {
> removeTables();
> }
> }
> }
>
> /**
> * creates only the test table
> *
> * @throws SQLException
> */
> private void createWithoutPartiti
on() throws SQLException {
> new SQLTemplate(conn). execute(SQL_CREATE_T
ABLE);
> }
>
> /**
> * creates test table with partitioning
> *
> * @throws SQLException
> */
> private void createWithPartition(
) throws SQLException {
> new SQLTemplate(conn).execute(new String[] { SQL_CREATE_TABLE,
> SQL_CREATE_PARTITION
, SQL_CREATE_INSERT_RU
LE });
> }
>
> /**
> * remove test and its dependents
> *
> * @throws SQLException
> */
> private void removeTables() throws SQLException {
> new SQLTemplate(conn). execute(SQL_DELETE_T
ABLE);
> }
>
> /**
> * convenient inner class to aid SQL execution
> */
> private final class SQLTemplate {
> private final Connection conn;
>
> private SQLTemplate(Connecti
on conn) {
> super();
> this.conn = conn;
> }
>
> private int execute(String sql) throws SQLException {
> return execute(new String[] { sql })[0];
> }
>
> private int[] execute(String[] sqls) throws SQLException {
> Statement stmt = null;
> try {
> stmt = conn.createStatement();
> // to store the affected rows for each sql
> int[] rowsArr = new int[sqls.length];
> for (int i = 0; i < sqls.length; i++) {
> rowsArr[i] = stmt.executeUpdate(sqls[i]);
> }
> return rowsArr;
> } catch (SQLException e) {
> conn.rollback();
> throw e;
> } finally {
> stmt.close();
> }
> }
> }
> }
>
> ====================
====================
=======
>
>
>
> ----- Original Message -----
> From: "Dave Cramer" <pg@fastcrypt.com>
> To: "Tea Yu" <teayu1@gmail.com>
> Cc: <pgsql-jdbc@postgresql.org>
> Sent: Tuesday, December 06, 2005 8:54 PM
> Subject: Re: [JDBC] JDBC executeUpdate returns 0 for table partitioning
rule
> insertion
>
>
>
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|