Primary key bug

[breathem <va...@gmail.com>]

Hi,
Suppose we have following DDL:
CREATE TABLE TEST
(
  org_id INT NOT NULL,
  drug_id INT NOT NULL,
  price DOUBLE NULL,
  qtty INT NULL,
  inter_id INT NULL,
  trade_id INT NULL,
  med_id INT NULL,
  medp_id INT NULL,
  avgprc DOUBLE NULL,
  avgprc_region DOUBLE NULL,
  region_id INTEGER NULL,
  prctype INTEGER NOT NULL,
  prccomment VARCHAR(250) NULL,
  PRIMARY KEY (org_id, drug_id, prctype)
) WITH
"CACHE_NAME=TEST,TEMPLATE=REPLICATED,ATOMICITY=ATOMIC,KEY_TYPE=KTest,VALUE_TYPE=TTest";

Execute this script via thin JDBC driver (Ignite 2.3.0).
TEST table is created but primary key doesn't exist (this can be seen in DB
via H2 web console).
If we remove PRIMARY KEY (org_id, drug_id, prctype) section from CREATE
TABLE Ignite throws exception:
No PRIMARY KEY defined for CREATE TABLE
Of course we can CREATE INDEX PK_TEST ON TEST (org_id, drug_id, prctype)
instead of PK, but it seems that Ignite 2.3.0 has bug in PK creation.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Primary key bug

[Alexey Kukushkin <ku...@gmail.com>]

Hi, there might be a misunderstanding here of how Ignite SQL grid is
implemented. Please note that Ignite does not use H2 as a storage. Ignite
uses H2 only as a parser and planner. H2 hands a parsed and planned
statement over to Ignite and then Ignite maps the statement to
get/put/create cache/etc... operations depending on what the statement was.

What you see in H2 console is actually the data stored in Ignite and not in
H2.

So I am not sure you really need "Ignite tables created directly in H2".
But if you do need it, the closest feature would be Ignite 3rd-party
persistence, where you can attach an external persistent store (H2 database
in this case) to Ignite and use Ignite to cache the data.

Re: Primary key bug

[breathem <va...@gmail.com>]

Hello, Alexey.
Thank you for clarification.
Is there any way to use in Ignite tables created directly in H2?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Primary key bug

[Alexey Kukushkin <ku...@gmail.com>]

Ignite's PRIMARY KEY does not map to H2 PRIMARY KEY constraint so you not
seeing a primary key in H2 is OK.

Ignite's PRIMARY KEY designates a binary object that Ignite SQL Grid will
create for the underlying data grid cache key. Thus, in your example you
will end up with two binary objects stored in Ignite - a key with fields
org_id, drug_id and prctype and a value including all the remaining fields.

Now if you want to map it to Java classes KTest and TTest you have to
create corresponding Java classes and give the same field names as those in
the TEST table. After that you can use both Java API and SQL API to
manipulate the data.