search

projectbarbel / barbelhisto-core

Lightweight ultra-fast library to store data in bi-temporal format
MIT License
42 stars 0 forks source link

Interesting but... #2

Open hrstoyanov opened 5 years ago

hrstoyanov commented 5 years ago

.. after reading the docs I realized that BarbelHisto does not work with JDBC (Oracle, MySql, PostreSQL).

Really?

I see there is Mongo engine, but Mongo is a very limited db engine.

The real pain with bi-temporal design is when you have to query a set of related navigable entities, not just a single entity taken (document?) in isolation (which is what i see in your tests cases/docs?). An i don't see this addressed....

For now Hibernate/Enver would still be my preferred technology for dealing with bitemporal data in RDBMS, but I will keep an eye on BabekHisto in case its starts moving in the right direction!

Thanks!

nschlimm commented 5 years ago

Hi Hristo! Thanks for your attention :-) BarbelHisto is meant as a lightweight, easy to understand and use, scalable solution for NoSql Databases. It‘s not meant to compete with Hinernate in the area of OR-Mapping, although you could use the event mechanism to persist the Bitemporal obejcts to any kind of SQL/JDBC datasource. I will think about how it would make sense to support it build-in.

hrstoyanov commented 5 years ago

@nschlimm Thanks for the quick response.

Thanks again!

nschlimm commented 5 years ago

Hi Hristo ! I will put that NoSql disclaimer upfront in the docs ... I agree ... the complexity that comes with OR-mapping is one reason for me to use NoSQL if possible, especially when bi-temporal is required

nschlimm commented 5 years ago

BTW: BarbelHisto provides disk persistence and SQL-like indexed quries on the collection ... this pair is blazing fast compared to the assembling required in OR solutions ... on the other hand there are many situation and requirements why people choose a relational database. I will investigate a Hibernate intergration in near future to see if that makes sense ... I have to dive into that

nschlimm commented 5 years ago

Are you sure Envers provides bi-temporal? My level of knowledge is that envers provides audit trail, i.e. record time, not effective time ...

hrstoyanov commented 5 years ago

Thanks @nschlimm

nschlimm commented 5 years ago

Thanks for your valuable input ! I will definitely look into that soon and comment here when there are any news ...

nschlimm commented 5 years ago

@hrstoyanov Hi Hristo, I have made it more obvious now right on the landing page, that BarbelHisto (for now) adresses NoSQL data sources. Appreciate your feedback if that is more intuitive now.

nschlimm commented 5 years ago

@hrstoyanov I noticed that Oracle Database supports JavaScript Object Notation (JSON) data natively with relational database features, including transactions, indexing, declarative querying, and views. I could integrate Oracle easily using that feature. Would that be sifficient or is there any need to dissassemble the object into its relational equivalents?

hrstoyanov commented 5 years ago

Hi! Yes, Oracle has some JSON extension, but only in the latest versions(12c+). PostgreSQL also implements binary JSON, arguably better than MongoDB. So PostgreSQL could be a better target ..

nschlimm commented 5 years ago

@hrstoyanov I could develop an SQL-persistence integration that uses one relation (table). that relation contains the bitemporal stamp fields (version fields) as native SQL fields and the managed pojo (say the a Policy or an Employee or wthatever) as JSON, which then is accessible in the "better" SQL databases like Oracle and PostgreSQL. This kind of solution would work with any SQL database.
Appreciate any of your thoughts ...

hrstoyanov commented 5 years ago

@nschlimm Can you pls put something like a design draft, e.g. sq--design.md int he repo with more details?

The implementations I have seen so far are like:

Table A:
 - pk
 - valid_from: timestamp -- when the record is valid from
 - valid_to: timestamp -- when the record is valid to. NULL indicates the latest record
 - <other fields > -- other fields that change

The latest record always has valid_to as NULL, any change to the biz fields in the table appends a new record with new <valid_from, NULL> and closes the old most-current with setting timestamp to its "valid_to".

Back-in-time query - need "to" and "from". "to" can be null:

select * from (select * from A where valid_from >= :from and valid_to<= nvl(:to,valid_to) ) -- NVL is a Oracle function

Latest record query:

select * from (select * from A where valid_to is null )

This can be extended to multiple tables:

-- Latest 
select * from 
(select * from A where valid_to is null )  A join
(select * from B where valid_to is null )  B join
on B.foregn_key_ref=A.pk

-- Back-in-time

select A.*, B.* 
from (select * from A where valid_from >= :from and valid_to<= nvl(:to,valid_to) ) A
join (select * from B where valid_from >= :from and valid_to<= nvl(:to,valid_to) ) B
on B.foregn_key_ref=A.pk.

I think Hibernate/Enver takes the design one step further - it places the ... into a global Revision table . Go check their detailed docs to see.

hrstoyanov commented 5 years ago

@nschlimm Can you pls put something like a design draft, e.g. sq--design.md int he repo with more details?

The implementations I have seen so far are like:

Table A:
 - pk
 - valid_from: timestamp -- when the record is valid from
 - valid_to: timestamp -- when the record is valid to. NULL indicates the latest record
 - <other fields > -- other fields that change

The latest record always has valid_to as NULL, any change to the biz fields in the table appends a new record with new <valid_from, NULL> and closes the old most-current with setting timestamp to its "valid_to".

Back-in-time query - need "to" and "from". "to" can be null:

select * from (select * from A where valid_from >= :from and valid_to<= nvl(:to,valid_to) ) -- NVL is a Oracle function

Latest record query:

select * from (select * from A where valid_to is null )

This can be extended to multiple tables:

-- Latest 
select * from 
(select * from A where valid_to is null )  A join
(select * from B where valid_to is null )  B join
on B.foregn_key_ref=A.pk

-- Back-in-time

select A.*, B.* 
from (select * from A where valid_from >= :from and valid_to<= nvl(:to,valid_to) ) A
join (select * from B where valid_from >= :from and valid_to<= nvl(:to,valid_to) ) B
on B.foregn_key_ref=A.pk.

I think Hibernate/Enver takes the design one step further - it places the ... into a global Revision table . Go check their detailed docs to see.

nschlimm commented 5 years ago

hi @hrstoyanov sorry, I was sick the last week. I have looked into the JPA subject more in detail and I do not see a real benefit to use JPA for bi-temporal data. Building a general solution for JPA (i.e. SQL-based DB) would require loads of assumptions being made, not speaking about the mega assembly and disassembly headache that ORM causes in general, and with bi-temporal in particular. I will concentrate on the following options of which I hope that at least one is interesting for you :-)

hrstoyanov commented 5 years ago

I don't see much interest in jpa either