SQL Entity Store progress...

[Niclas Hedhman <ni...@hedhman.org>]

Hi,

I just want to give an update on this critical extension.

The good news is that both test suites passes for Derby, H2, Postgres and
SQLite.

The bad news is that MySQL is not working, because JOOQ seems to generate
the wrong SQL for table creation, or possibly is expecting some "mode" in
MySQL that I don't know about.

For all java.lang.String, it uses CHAR as the type;









*create table if not exists `ENTITIES`(    `_identity` char null,
`_app_version` char null,     `_value_id` char null,     `_version` char
null,     `_type` char null,     `_modified_at` timestamp null,
`_created_at` timestamp null)*

And those are simply 1 character in size.


All my recent problems was related to adding debugging to figure out why
this was happening, and digging a much deeper hole, as one is not allowed
to wrap some types in JOOQ as types are (incorrectly) used to look for
capabilities. I have raised that with the JOOQ people, and that will be
addressed eventually.

THIS issue is bizarre though, and I will try to work with JOOQ people to
figure out if I am doing something wrong (hard to argue since 4 other
dialects works and no code changes).


There is one more bug, possibly in JOOQ; Derby supports SCHEMAs but if I
use schema for Derby, then the tests fail with an SQL problem, saying






*SQL [    select * from "POLYGENE"."ENTITIES"         left outer join
"POLYGENE"."TestEntity" on "_value_id" = "TestEntity"."_identity"     where
"ENTITIES"."_identity" = cast(? as varchar(32672))]; Column
'ENTITIES._identity' is either not in any table in the FROM list or appears
within a join specification and is outside the scope of the join
specification or appears in a HAVING clause and is not in the GROUP BY
list. If this is a CREATE or ALTER TABLE  statement then
'ENTITIES._identity' is not a column in the target table.*

Instead of fighting that, I am simply disabling Schema for Derby and let it
use the default one. I think this is acceptable.

MySQL and SQLite are also without Schema support.


I will disable the MySQL test suite and push this to develop branch shortly.
I hope others can help me put this through some heavy testing of other
domain models and such, as we probably have holes in the test suites.


Cheers
-- 
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java

Re: SQL Entity Store progress...

[Kent Sølvsten <ke...@gmail.com>]

Nice to hear of the progress.

I don't think that schema support is needed. Most applications will
probably use a schema, but most (all?) databases allow to select that in
the connection url.
And if an application is large enough to warrant splitting the data in
multiple schemas, it would probably make sense to create the same
separation in the application and have an EntityStore for each schema
(otherwise it would be a smell that the data separation is probably wrong).

On Thu, Oct 26, 2017 at 8:51 AM, Niclas Hedhman <ni...@hedhman.org> wrote:

> Hi,
>
> I just want to give an update on this critical extension.
>
> The good news is that both test suites passes for Derby, H2, Postgres and
> SQLite.
>
> The bad news is that MySQL is not working, because JOOQ seems to generate
> the wrong SQL for table creation, or possibly is expecting some "mode" in
> MySQL that I don't know about.
>
> For all java.lang.String, it uses CHAR as the type;
>
>
>
>
>
>
>
>
>
> *create table if not exists `ENTITIES`(    `_identity` char null,
> `_app_version` char null,     `_value_id` char null,     `_version` char
> null,     `_type` char null,     `_modified_at` timestamp null,
> `_created_at` timestamp null)*
>
> And those are simply 1 character in size.
>
>
> All my recent problems was related to adding debugging to figure out why
> this was happening, and digging a much deeper hole, as one is not allowed
> to wrap some types in JOOQ as types are (incorrectly) used to look for
> capabilities. I have raised that with the JOOQ people, and that will be
> addressed eventually.
>
> THIS issue is bizarre though, and I will try to work with JOOQ people to
> figure out if I am doing something wrong (hard to argue since 4 other
> dialects works and no code changes).
>
>
> There is one more bug, possibly in JOOQ; Derby supports SCHEMAs but if I
> use schema for Derby, then the tests fail with an SQL problem, saying
>
>
>
>
>
>
> *SQL [    select * from "POLYGENE"."ENTITIES"         left outer join
> "POLYGENE"."TestEntity" on "_value_id" = "TestEntity"."_identity"     where
> "ENTITIES"."_identity" = cast(? as varchar(32672))]; Column
> 'ENTITIES._identity' is either not in any table in the FROM list or appears
> within a join specification and is outside the scope of the join
> specification or appears in a HAVING clause and is not in the GROUP BY
> list. If this is a CREATE or ALTER TABLE  statement then
> 'ENTITIES._identity' is not a column in the target table.*
>
> Instead of fighting that, I am simply disabling Schema for Derby and let it
> use the default one. I think this is acceptable.
>
> MySQL and SQLite are also without Schema support.
>
>
> I will disable the MySQL test suite and push this to develop branch
> shortly.
> I hope others can help me put this through some heavy testing of other
> domain models and such, as we probably have holes in the test suites.
>
>
> Cheers
> --
> Niclas Hedhman, Software Developer
> http://polygene.apache.org - New Energy for Java
>