You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Alexey Kuznetsov (JIRA)" <ji...@apache.org> on 2016/12/09 02:58:58 UTC
[jira] [Comment Edited] (IGNITE-4350) Cache JDBC POJO store:
improve default data transformation
[ https://issues.apache.org/jira/browse/IGNITE-4350?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15734107#comment-15734107 ]
Alexey Kuznetsov edited comment on IGNITE-4350 at 12/9/16 2:58 AM:
-------------------------------------------------------------------
Pavel please test cache load from all RDBMS.
You could use following scripts to create test tables and populating them with data:
{code}
// Create table in H2.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 BOOLEAN, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 UUID);
// Create table in ORACLE.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 NUMBER(1), c2 INTEGER, c3 NUMBER(3), c4 NUMBER(4), c5 NUMBER(20), c6 NUMBER(20, 2), c7 NUMBER(20, 2), c8 NUMBER(10, 2), c9 TIMESTAMP, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 VARCHAR(36));
// Create table in MS SQL.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 BIT, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 DATETIME, c12 VARCHAR(100), c13 VARCHAR(36));
// Create table in DB2.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 SMALLINT , c2 INTEGER, c3 SMALLINT , c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 VARCHAR(36));
// Create table in Postgre SQL.
CREATE TABLE TEST_TRANSFORMER(id INTEGER, primary key, c1 BOOLEAN, c2 INTEGER, c3 SMALLINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 UUID);
// Create table in MySQL.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 BOOLEAN, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP(3), c12 VARCHAR(100), c13 VARCHAR(36));
----------------
// Add data to H2, Postgre SQL and MySQL.
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (1, true, 1, 2, 3, 4, 5.35, 6.15, 7.32, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', '100', '736bc956-090c-40d2-94da-916f2161f8a2');
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (2, false, 10, 20, 30, 40, 50, 60, 70, current_time, current_date, current_timestamp, '100.55', '736bc956-090c-40d2-94da-916f2161cdea');
// Add data to Oracle.
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (1, 1, 1, 2, 3, 4, 5.35, 6.15, 7.32, TO_TIMESTAMP('2016-01-01 00:01:08', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-01-01', 'YYYY-MM-DD'), TO_TIMESTAMP('2016-01-01 00:01:08.296', 'YYYY-MM-DD HH24:MI:SS.FF3'), '100', '736bc956-090c-40d2-94da-916f2161f8a2');
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (2, 0, 10, 20, 30, 40, 50, 60, 70, TO_TIMESTAMP('2016-01-01 00:01:08', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-01-01', 'YYYY-MM-DD'), TO_TIMESTAMP('2016-01-01 00:01:08.296', 'YYYY-MM-DD HH24:MI:SS.FF3'), '100.55', '736bc956-090c-40d2-94da-916f2161cdea');
// Add data to MS SQL or IBM DB2.
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (1, 1, 1, 2, 3, 4, 5.35, 6.15, 7.32, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', '100', '736bc956-090c-40d2-94da-916f2161f8a2');
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (2, 0, 10, 20, 30, 40, 50, 60, 70, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', '100.55', '736bc956-090c-40d2-94da-916f2161cdea');
// Add nulls to All RDBMS
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (3, null, null, null, null, null, null, null, null, null, null, null, null, null);
{code}
was (Author: kuaw26):
Pavel please test cache load from all RDBMS.
You could use following scripts to create test tables and populating them with data:
{code}
// Create table in H2.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 BOOLEAN, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 UUID);
// Create table in ORACLE.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 NUMBER(1), c2 INTEGER, c3 NUMBER(3), c4 NUMBER(4), c5 NUMBER(20), c6 NUMBER(20, 2), c7 NUMBER(20, 2), c8 NUMBER(10, 2), c9 TIMESTAMP, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 VARCHAR(36));
// Create table in MS SQL.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 BIT, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 DATETIME, c12 VARCHAR(100), c13 VARCHAR(36));
// Create table in DB2.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 SMALLINT , c2 INTEGER, c3 SMALLINT , c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 VARCHAR(36));
// Create table in Postgre SQL.
CREATE TABLE TEST_TRANSFORMER(id INTEGER, primary key, c1 BOOLEAN, c2 INTEGER, c3 SMALLINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP, c12 VARCHAR(100), c13 UUID);
// Create table in MySQL.
CREATE TABLE TEST_TRANSFORMER(id INTEGER primary key, c1 BOOLEAN, c2 INTEGER, c3 TINYINT, c4 SMALLINT, c5 BIGINT, c6 DECIMAL(20, 2), c7 DOUBLE PRECISION, c8 REAL, c9 TIME, c10 DATE, c11 TIMESTAMP(3), c12 VARCHAR(100), c13 VARCHAR(36));
----------------
// Add data to H2, Postgre SQL and MySQL.
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (1, true, 1, 2, 3, 4, 5.35, 6.15, 7.32, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', '100', '736bc956-090c-40d2-94da-916f2161f8a2');
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (2, false, 10, 20, 30, 40, 50, 60, 70, current_time, current_date, current_timestamp, '100.55', '736bc956-090c-40d2-94da-916f2161cdea');
// Add data to Oracle.
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (1, 1, 1, 2, 3, 4, 5.35, 6.15, 7.32, TO_TIMESTAMP('2016-01-01 00:01:08', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-01-01', 'YYYY-MM-DD'), TO_TIMESTAMP('2016-01-01 00:01:08.296', 'YYYY-MM-DD HH24:MI:SS.FF3'), '100', '736bc956-090c-40d2-94da-916f2161f8a2');
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (2, 0, 10, 20, 30, 40, 50, 60, 70, TO_TIMESTAMP('2016-01-01 00:01:08', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2016-01-01', 'YYYY-MM-DD'), TO_TIMESTAMP('2016-01-01 00:01:08.296', 'YYYY-MM-DD HH24:MI:SS.FF3'), '100.55', '736bc956-090c-40d2-94da-916f2161cdea');
// Add data to MS SQL or IBM DB2.
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
// "VALUES (1, 1, 1, 2, 3, 4, 5.35, 6.15, 7.32, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', " +
// "'100', '736bc956-090c-40d2-94da-916f2161f8a2')");
// stmt.executeUpdate("INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) " +
// "VALUES (2, 0, 10, 20, 30, 40, 50, 60, 70, '00:01:08', '2016-01-01', '2016-01-01 00:01:08.296', " +
// "'100.55', '736bc956-090c-40d2-94da-916f2161cdea')");
// Add nulls to All RDBMS
INSERT INTO TEST_TRANSFORMER(id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13) VALUES (3, null, null, null, null, null, null, null, null, null, null, null, null, null);
{code}
> Cache JDBC POJO store: improve default data transformation
> ----------------------------------------------------------
>
> Key: IGNITE-4350
> URL: https://issues.apache.org/jira/browse/IGNITE-4350
> Project: Ignite
> Issue Type: Task
> Components: cache
> Affects Versions: 1.7
> Reporter: Alexey Kuznetsov
> Assignee: Pavel Konstantinov
> Fix For: 1.9
>
>
> Improve JdbcTypesDefaultTransformer logic in case when in database column declared as some TYPE1 and in POJO store same column declared as some TYPE2. We could try to handle such cases out of the box.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)