3rd party persistence: Mapping with RDBMS tables that don't have primary keys

classic Classic list List threaded Threaded
4 messages Options
Alex Panchenko Alex Panchenko
Reply | Threaded
Open this post in threaded view
|

3rd party persistence: Mapping with RDBMS tables that don't have primary keys

Hi,

I have an Ignite cluster with 3rd party persistence enabled (Postgres db). I
have a few tables in Postgres which don't have primary keys  bur I need to
keep these tables in Ignite:
 They look like this table (as an example):

  KeyValuesTable {
    key: UUID
    value: String
}

NOTE: there is no Primary key or any unique index in this table. The same
key value may have multiple values attached, for ex:
 
#1 record: key=1, value="v1"
#2 record: key=1, value="v2"
#3 record: key=2, value="v1"
#4 record: key=3, value="v1"
#5 record: key=3, value="v2"

NOTE: I can not change the table in Postress and add a primary key field,
like id or something like that.
Can you suggest some solution, please?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dmagda dmagda
Reply | Threaded
Open this post in threaded view
|

Re: 3rd party persistence: Mapping with RDBMS tables that don't have primary keys

Hi Alex,

How will you access such records in Ignite? SQL lookups? If primary key-based searches are irrelevant in Ignite as well then think about the following:
  • Obviously, Ignite still requires a primary key and that can be an integer number incremented by your application: https://apacheignite.readme.io/docs/id-generator
  • The default CacheStore implementation that writes down changes to a relational database needs to be overridden. CacheStore.update() and CacheStore.insert() methods must not try to use the value of the primary key and use a custom technique to update those Postgres tables with Ignite data.


    -
    Denis


On Tue, Jul 21, 2020 at 12:27 PM Alex Panchenko <[hidden email]> wrote:
Hi,

I have an Ignite cluster with 3rd party persistence enabled (Postgres db). I
have a few tables in Postgres which don't have primary keys  bur I need to
keep these tables in Ignite:
 They look like this table (as an example):

  KeyValuesTable {
    key: UUID
    value: String
}

NOTE: there is no Primary key or any unique index in this table. The same
key value may have multiple values attached, for ex:

#1 record: key=1, value="v1"
#2 record: key=1, value="v2"
#3 record: key=2, value="v1"
#4 record: key=3, value="v1"
#5 record: key=3, value="v2"

NOTE: I can not change the table in Postress and add a primary key field,
like id or something like that.
Can you suggest some solution, please?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Alex Panchenko Alex Panchenko
Reply | Threaded
Open this post in threaded view
|

Re: 3rd party persistence: Mapping with RDBMS tables that don't have primary keys

Hi Denis,
thanks for your reply

>>How will you access such records in Ignite? SQL lookups?
Yes, using SQL lookups

>>The default CacheStore implementation that writes down changes to a
relational database needs to be overridden.
I'm not going to write any new records or update existing ones for such
tables through Ignite. So I can skip the overrides in CacheStore
implementation, right? (I'm using default implementation at the moment)

>>Obviously, Ignite still requires a primary key and that can be an integer
number incremented by your application:
https://apacheignite.readme.io/docs/id-generator
Thi may work. Can you point me to the example where Ignite config is in XML
file

Also at the moment I'm using the following workaround. It seems like
working, but I'm still testing it

"keyType"="com.some.package.KeyValuesTableKey"

KeyValuesTableKey {
    key: UUID
    value: String
}


Thank you



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dmagda dmagda
Reply | Threaded
Open this post in threaded view
|

Re: 3rd party persistence: Mapping with RDBMS tables that don't have primary keys

Hi Alex,


I'm not going to write any new records or update existing ones for such
tables through Ignite. So I can skip the overrides in CacheStore
implementation, right? (I'm using default implementation at the moment)

That's right. 

https://apacheignite.readme.io/docs/id-generator
Thi may work. Can you point me to the example where Ignite config is in XML
file
 
I don't have any XML example that shows how to create an AtomicSequence bean. I tend to initialize it programmatically in code. Anyway, just create it as a standard bean if it needs to be defined in the XML format.

-
Denis


On Wed, Jul 22, 2020 at 12:46 AM Alex Panchenko <[hidden email]> wrote:
Hi Denis,
thanks for your reply

>>How will you access such records in Ignite? SQL lookups?
Yes, using SQL lookups

>>The default CacheStore implementation that writes down changes to a
relational database needs to be overridden.
I'm not going to write any new records or update existing ones for such
tables through Ignite. So I can skip the overrides in CacheStore
implementation, right? (I'm using default implementation at the moment)

>>Obviously, Ignite still requires a primary key and that can be an integer
number incremented by your application:
https://apacheignite.readme.io/docs/id-generator
Thi may work. Can you point me to the example where Ignite config is in XML
file

Also at the moment I'm using the following workaround. It seems like
working, but I'm still testing it

"keyType"="com.some.package.KeyValuesTableKey"

KeyValuesTableKey {
    key: UUID
    value: String
}


Thank you



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