You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by "slava.koptilin" <sl...@gmail.com> on 2018/01/25 13:07:35 UTC
Re: Binary type has different affinity key fields
Hi Thomas,
Could you please share a small code snippet of cache configuration/cache
creation?
Do you use DDL for that?
I guess that you need to define affinity keys using upper-case
public class CredentialsKey {
@QuerySqlField(index = true)
@AffinityKeyMapped
private String USERNAME;
@QuerySqlField(index = true)
private String PASSWORD;
...
}
Thanks,
Slava.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Binary type has different affinity key fields
Posted by Вячеслав Коптилин <sl...@gmail.com>.
Hi Thomas,
Yes, SQL and JCache can be used interchangeably, but sometimes it can be
tricky.
Best regards,
Slava.
2018-01-31 12:10 GMT+03:00 Thomas Isaksen <th...@sysco.no>:
> Hi Slava,
>
>
>
> Thanks a lot for your help this is working great J
>
>
>
> I do wonder If I can use SQL and JCache API interchangeably?
>
> For example, I do cache.put(key, value) for one row and I insert another
> one using SQL INSERT. Can I still retrieve both rows using both SQL or
> JCache API?
>
> The reason I ask is that it seems to me that only the row I stored using
> SQL INSERT shows up when I do a SELECT on the table, but both rows shows up
> in ignite visor when I do a cache -scan
>
>
>
> Thanks!
>
>
>
> --
>
> Thomas Isaksen
>
>
>
> *From:* Вячеслав Коптилин [mailto:slava.koptilin@gmail.com]
> *Sent:* tirsdag 30. januar 2018 13.41
> *To:* user@ignite.apache.org
> *Subject:* Re: Binary type has different affinity key fields
>
>
>
> Hi Thomas,
>
>
>
> Let's start with the table (I will use java api for that)
>
>
>
>
>
> *// Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this// will appear in future versions, JDBC and ODBC drivers do not require it already).*CacheConfiguration<?, ?> cacheCfg = *new *CacheConfiguration<>(*DUMMY_CACHE_NAME*).setSqlSchema(*"PUBLIC"*);
> IgniteCache<?, ?> dummyCache = ignite.getOrCreateCache(cacheCfg);
>
>
> *// Create UserCache table based on the partitioned template.*dummyCache.query(*new *SqlFieldsQuery(
> *"CREATE TABLE UserCache (id BIGINT, username VARCHAR, password varchar, PRIMARY KEY (username, password)) " *+
> *"WITH \"template=partitioned," *+
> *"affinitykey=username," *+
> *"cache_name=UserCache," *+
> *"key_type=org.apache.ignite.examples.CredentialsKey," *+
> *"value_type=org.apache.ignite.examples.Credentials\""*)).getAll();
>
>
>
> one important thing that should be mentioned here is that SQL is
> case-insensitive and therefore table name and column names will be
> automatically converted to *upper case*.
>
> if you want to preserve the case, you need to put double quotes around the
> table name and columns.
>
> for instance:
>
>
>
> *// Create UserCache table based on the partitioned template.*
>
> dummyCache.query(*new *SqlFieldsQuery(
>
> *"CREATE TABLE **\"**UserCache**\"** (\"id\" BIGINT, **\"**username*
> *\"** VARCHAR, **\"**password**\"** varchar, PRIMARY KEY (**\"**username*
> *\"**, **\"**password**\"**)) " *+
>
> * "WITH **\"**template=partitioned," *+
>
> *"affinitykey=username," *+
>
> *"cache_name=UserCache," *+
>
> * "key_type=org.apache.ignite.examples.CredentialsKey," *+
>
> *"value_type=org.apache.ignite.examples.Credentials**\"**"*
> )).getAll();
>
>
>
> optionally, you can create indices on UserCache
>
>
> *// Create indices.*dummyCache.query(*new *SqlFieldsQuery(*"CREATE INDEX on UserCache (username)"*)).getAll();
> dummyCache.query(*new *SqlFieldsQuery(*"CREATE INDEX on UserCache (password)"*)).getAll();
>
>
>
> next step is defining CredentialsKey and Credential classes.
>
> in accordance with the documentation https://apacheignite-sql.readme.io/
> docs/create-table#section-examples
>
> the PRIMARY KEY columns will be used as the object's key, the rest of the
> columns will belong to the value.
>
>
>
> *public class *CredentialsKey {
>
> // Please take into account my note about case-insensitive SQL
> @AffinityKeyMapped
> *private *String *USERNAME*;
>
> *private *String *PASSWORD*;
>
> *public *CredentialsKey(String username, String password) {
> *this*.*USERNAME *= username;
> *this*.*PASSWORD *= password;
> }
>
> *public *String getUsername() {*return USERNAME*;}
>
> *public void *setUsername(String username) {*this*.*USERNAME *= username;}
>
> *public *String getPassword() {*return PASSWORD*;}
>
> *public void *setPassword(String password) {*this*.*PASSWORD *= password;}
> }
>
>
>
> *public class *Credentials {
> *private long ID*;
>
> *public *Credentials(*long *id) {
> *this*.*ID *= id;
> }
>
> *public long *getId() {*return ID*;}
>
> *public void *setId(*long *id) {*this*.*ID *= id;}
>
> @Override *public *String toString() {*return "Credentials=[id=" *+ *ID *+ *"]"*;}
> }
>
>
>
> Now, you can populate the cache/table via JCache API
>
> IgniteCache testCache = ignite.cache(*"UserCache"*);
> testCache.put(*new *CredentialsKey(*"username-1"*, *"password-1"*), *new *Credentials(1L));
> testCache.put(*new *CredentialsKey(*"username-2"*, *"password-2"*), *new *Credentials(2L));
> testCache.put(*new *CredentialsKey(*"username-3"*, *"password-3"*), *new *Credentials(3L));
>
> or SQL API
>
> SqlFieldsQuery qry = *new *SqlFieldsQuery(*"INSERT INTO UserCache (id, username, password) VALUES (?, ?, ?)"*);
> dummyCache.query(qry.setArgs(1L, *"username-sql-1"*, *"password-5"*)).getAll();
> dummyCache.query(qry.setArgs(2L, *"username-sql-2"*, *"password-6"*)).getAll();
> dummyCache.query(qry.setArgs(3L, *"username-sql-3"*, *"password-7"*)).getAll();
>
> Best regards,
>
> Slava.
>
>
>
>
>
> 2018-01-26 12:27 GMT+03:00 Thomas Isaksen <th...@sysco.no>:
>
> Hi Slava
>
> Thanks for pointing out my mistakes with the template.
> I have attached the java classes in question and the ignite config file
> that I am using .
>
> I create the table using DDL as follows:
>
> CREATE TABLE UserCache (
> id bigint,
> username varchar,
> password varchar,
> PRIMARY KEY (username, password)
> )
> WITH "template=userCache, affinitykey=username, cache_name=UserCache,
> key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey,
> value_type=no.toyota.gatekeeper.authenticate.Credentials";
>
> Next I try to put one entry into my cache:
>
> @Test
> Public void testIgnite()
> {
> Ignition.setClientMode(true);
> Ignite ignite = Ignition.start("/config/test-config.xml");
> IgniteCache<CredentialsKey, Credentials> cache =
> ignite.cache("UserCache");
> // this blows up
> cache.put(new CredentialsKey("foo","bar"), new
> Credentials("foo","bar","resourceId"));
> }
>
> I am not sure my code is correct but I get the same error when I try to
> insert a row using SQL.
>
> INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar');
>
> --
> Thomas Isaksen
>
> -----Original Message-----
> From: slava.koptilin [mailto:slava.koptilin@gmail.com]
>
> Sent: torsdag 25. januar 2018 17.39
> To: user@ignite.apache.org
> Subject: RE: Binary type has different affinity key fields
>
> Hi Thomas,
>
> CREATE TABLE statement doesn't use your template because you specified the
> predefined one - 'partitioned' *template=partitioned*
>
> In case of using replicated mode, the following property <property
> name="backups" value="1"/> does not make sense.
>
> Could you please share full reproducer? I will try it on my side and come
> back to you with findings.
> I mean definition CredentialsKey and Credentials and code that can be used
> in order to reproduce the exception you mentioned.
>
> Best regards,
> Slava.
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
>
>
Re: Binary type has different affinity key fields
Posted by Вячеслав Коптилин <sl...@gmail.com>.
> Hi again, I got it sorted.
sounds good :)
Thanks!
2018-01-31 18:01 GMT+03:00 Thomas Isaksen <th...@sysco.no>:
> Hi again, I got it sorted. Thanks a million mate!
>
>
>
> --
>
> Thomas Isaksen
>
>
>
> *From:* Thomas Isaksen [mailto:thomas.isaksen@sysco.no]
> *Sent:* onsdag 31. januar 2018 10.10
>
> *To:* user@ignite.apache.org
> *Subject:* RE: Binary type has different affinity key fields
>
>
>
> Hi Slava,
>
>
>
> Thanks a lot for your help this is working great J
>
>
>
> I do wonder If I can use SQL and JCache API interchangeably?
>
> For example, I do cache.put(key, value) for one row and I insert another
> one using SQL INSERT. Can I still retrieve both rows using both SQL or
> JCache API?
>
> The reason I ask is that it seems to me that only the row I stored using
> SQL INSERT shows up when I do a SELECT on the table, but both rows shows up
> in ignite visor when I do a cache -scan
>
>
>
> Thanks!
>
>
>
> --
>
> Thomas Isaksen
>
>
>
> *From:* Вячеслав Коптилин [mailto:slava.koptilin@gmail.com
> <sl...@gmail.com>]
> *Sent:* tirsdag 30. januar 2018 13.41
> *To:* user@ignite.apache.org
> *Subject:* Re: Binary type has different affinity key fields
>
>
>
> Hi Thomas,
>
>
>
> Let's start with the table (I will use java api for that)
>
>
>
>
>
> *// Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this// will appear in future versions, JDBC and ODBC drivers do not require it already).*CacheConfiguration<?, ?> cacheCfg = *new *CacheConfiguration<>(*DUMMY_CACHE_NAME*).setSqlSchema(*"PUBLIC"*);
> IgniteCache<?, ?> dummyCache = ignite.getOrCreateCache(cacheCfg);
>
>
> *// Create UserCache table based on the partitioned template.*dummyCache.query(*new *SqlFieldsQuery(
> *"CREATE TABLE UserCache (id BIGINT, username VARCHAR, password varchar, PRIMARY KEY (username, password)) " *+
> *"WITH \"template=partitioned," *+
> *"affinitykey=username," *+
> *"cache_name=UserCache," *+
> *"key_type=org.apache.ignite.examples.CredentialsKey," *+
> *"value_type=org.apache.ignite.examples.Credentials\""*)).getAll();
>
>
>
> one important thing that should be mentioned here is that SQL is
> case-insensitive and therefore table name and column names will be
> automatically converted to *upper case*.
>
> if you want to preserve the case, you need to put double quotes around the
> table name and columns.
>
> for instance:
>
>
>
> *// Create UserCache table based on the partitioned template.*
>
> dummyCache.query(*new *SqlFieldsQuery(
>
> *"CREATE TABLE **\"**UserCache**\"** (\"id\" BIGINT, **\"**username*
> *\"** VARCHAR, **\"**password**\"** varchar, PRIMARY KEY (**\"**username*
> *\"**, **\"**password**\"**)) " *+
>
> * "WITH **\"**template=partitioned," *+
>
> *"affinitykey=username," *+
>
> *"cache_name=UserCache," *+
>
> * "key_type=org.apache.ignite.examples.CredentialsKey," *+
>
> *"value_type=org.apache.ignite.examples.Credentials**\"**"*
> )).getAll();
>
>
>
> optionally, you can create indices on UserCache
>
>
> *// Create indices.*dummyCache.query(*new *SqlFieldsQuery(*"CREATE INDEX on UserCache (username)"*)).getAll();
> dummyCache.query(*new *SqlFieldsQuery(*"CREATE INDEX on UserCache (password)"*)).getAll();
>
>
>
> next step is defining CredentialsKey and Credential classes.
>
> in accordance with the documentation https://apacheignite-sql.readme.io/
> docs/create-table#section-examples
>
> the PRIMARY KEY columns will be used as the object's key, the rest of the
> columns will belong to the value.
>
>
>
> *public class *CredentialsKey {
>
> // Please take into account my note about case-insensitive SQL
> @AffinityKeyMapped
> *private *String *USERNAME*;
>
> *private *String *PASSWORD*;
>
> *public *CredentialsKey(String username, String password) {
> *this*.*USERNAME *= username;
> *this*.*PASSWORD *= password;
> }
>
> *public *String getUsername() {*return USERNAME*;}
>
> *public void *setUsername(String username) {*this*.*USERNAME *= username;}
>
> *public *String getPassword() {*return PASSWORD*;}
>
> *public void *setPassword(String password) {*this*.*PASSWORD *= password;}
> }
>
>
>
> *public class *Credentials {
> *private long ID*;
>
> *public *Credentials(*long *id) {
> *this*.*ID *= id;
> }
>
> *public long *getId() {*return ID*;}
>
> *public void *setId(*long *id) {*this*.*ID *= id;}
>
> @Override *public *String toString() {*return "Credentials=[id=" *+ *ID *+ *"]"*;}
> }
>
>
>
> Now, you can populate the cache/table via JCache API
>
> IgniteCache testCache = ignite.cache(*"UserCache"*);
> testCache.put(*new *CredentialsKey(*"username-1"*, *"password-1"*), *new *Credentials(1L));
> testCache.put(*new *CredentialsKey(*"username-2"*, *"password-2"*), *new *Credentials(2L));
> testCache.put(*new *CredentialsKey(*"username-3"*, *"password-3"*), *new *Credentials(3L));
>
> or SQL API
>
> SqlFieldsQuery qry = *new *SqlFieldsQuery(*"INSERT INTO UserCache (id, username, password) VALUES (?, ?, ?)"*);
> dummyCache.query(qry.setArgs(1L, *"username-sql-1"*, *"password-5"*)).getAll();
> dummyCache.query(qry.setArgs(2L, *"username-sql-2"*, *"password-6"*)).getAll();
> dummyCache.query(qry.setArgs(3L, *"username-sql-3"*, *"password-7"*)).getAll();
>
> Best regards,
>
> Slava.
>
>
>
>
>
> 2018-01-26 12:27 GMT+03:00 Thomas Isaksen <th...@sysco.no>:
>
> Hi Slava
>
> Thanks for pointing out my mistakes with the template.
> I have attached the java classes in question and the ignite config file
> that I am using .
>
> I create the table using DDL as follows:
>
> CREATE TABLE UserCache (
> id bigint,
> username varchar,
> password varchar,
> PRIMARY KEY (username, password)
> )
> WITH "template=userCache, affinitykey=username, cache_name=UserCache,
> key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey,
> value_type=no.toyota.gatekeeper.authenticate.Credentials";
>
> Next I try to put one entry into my cache:
>
> @Test
> Public void testIgnite()
> {
> Ignition.setClientMode(true);
> Ignite ignite = Ignition.start("/config/test-config.xml");
> IgniteCache<CredentialsKey, Credentials> cache =
> ignite.cache("UserCache");
> // this blows up
> cache.put(new CredentialsKey("foo","bar"), new
> Credentials("foo","bar","resourceId"));
> }
>
> I am not sure my code is correct but I get the same error when I try to
> insert a row using SQL.
>
> INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar');
>
> --
> Thomas Isaksen
>
> -----Original Message-----
> From: slava.koptilin [mailto:slava.koptilin@gmail.com]
>
> Sent: torsdag 25. januar 2018 17.39
> To: user@ignite.apache.org
> Subject: RE: Binary type has different affinity key fields
>
> Hi Thomas,
>
> CREATE TABLE statement doesn't use your template because you specified the
> predefined one - 'partitioned' *template=partitioned*
>
> In case of using replicated mode, the following property <property
> name="backups" value="1"/> does not make sense.
>
> Could you please share full reproducer? I will try it on my side and come
> back to you with findings.
> I mean definition CredentialsKey and Credentials and code that can be used
> in order to reproduce the exception you mentioned.
>
> Best regards,
> Slava.
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
>
>
RE: Binary type has different affinity key fields
Posted by Thomas Isaksen <th...@sysco.no>.
Hi again, I got it sorted. Thanks a million mate!
--
Thomas Isaksen
From: Thomas Isaksen [mailto:thomas.isaksen@sysco.no]
Sent: onsdag 31. januar 2018 10.10
To: user@ignite.apache.org
Subject: RE: Binary type has different affinity key fields
Hi Slava,
Thanks a lot for your help this is working great ☺
I do wonder If I can use SQL and JCache API interchangeably?
For example, I do cache.put(key, value) for one row and I insert another one using SQL INSERT. Can I still retrieve both rows using both SQL or JCache API?
The reason I ask is that it seems to me that only the row I stored using SQL INSERT shows up when I do a SELECT on the table, but both rows shows up in ignite visor when I do a cache -scan
Thanks!
--
Thomas Isaksen
From: Вячеслав Коптилин [mailto:slava.koptilin@gmail.com]
Sent: tirsdag 30. januar 2018 13.41
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: Re: Binary type has different affinity key fields
Hi Thomas,
Let's start with the table (I will use java api for that)
// Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this
// will appear in future versions, JDBC and ODBC drivers do not require it already).
CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
IgniteCache<?, ?> dummyCache = ignite.getOrCreateCache(cacheCfg);
// Create UserCache table based on the partitioned template.
dummyCache.query(new SqlFieldsQuery(
"CREATE TABLE UserCache (id BIGINT, username VARCHAR, password varchar, PRIMARY KEY (username, password)) " +
"WITH \"template=partitioned," +
"affinitykey=username," +
"cache_name=UserCache," +
"key_type=org.apache.ignite.examples.CredentialsKey," +
"value_type=org.apache.ignite.examples.Credentials\"")).getAll();
one important thing that should be mentioned here is that SQL is case-insensitive and therefore table name and column names will be automatically converted to upper case.
if you want to preserve the case, you need to put double quotes around the table name and columns.
for instance:
// Create UserCache table based on the partitioned template.
dummyCache.query(new SqlFieldsQuery(
"CREATE TABLE \"UserCache\" (\"id\" BIGINT, \"username\" VARCHAR, \"password\" varchar, PRIMARY KEY (\"username\", \"password\")) " +
"WITH \"template=partitioned," +
"affinitykey=username," +
"cache_name=UserCache," +
"key_type=org.apache.ignite.examples.CredentialsKey," +
"value_type=org.apache.ignite.examples.Credentials\"")).getAll();
optionally, you can create indices on UserCache
// Create indices.
dummyCache.query(new SqlFieldsQuery("CREATE INDEX on UserCache (username)")).getAll();
dummyCache.query(new SqlFieldsQuery("CREATE INDEX on UserCache (password)")).getAll();
next step is defining CredentialsKey and Credential classes.
in accordance with the documentation https://apacheignite-sql.readme.io/docs/create-table#section-examples
the PRIMARY KEY columns will be used as the object's key, the rest of the columns will belong to the value.
public class CredentialsKey {
// Please take into account my note about case-insensitive SQL
@AffinityKeyMapped
private String USERNAME;
private String PASSWORD;
public CredentialsKey(String username, String password) {
this.USERNAME = username;
this.PASSWORD = password;
}
public String getUsername() {return USERNAME;}
public void setUsername(String username) {this.USERNAME = username;}
public String getPassword() {return PASSWORD;}
public void setPassword(String password) {this.PASSWORD = password;}
}
public class Credentials {
private long ID;
public Credentials(long id) {
this.ID = id;
}
public long getId() {return ID;}
public void setId(long id) {this.ID = id;}
@Override public String toString() {return "Credentials=[id=" + ID + "]";}
}
Now, you can populate the cache/table via JCache API
IgniteCache testCache = ignite.cache("UserCache");
testCache.put(new CredentialsKey("username-1", "password-1"), new Credentials(1L));
testCache.put(new CredentialsKey("username-2", "password-2"), new Credentials(2L));
testCache.put(new CredentialsKey("username-3", "password-3"), new Credentials(3L));
or SQL API
SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO UserCache (id, username, password) VALUES (?, ?, ?)");
dummyCache.query(qry.setArgs(1L, "username-sql-1", "password-5")).getAll();
dummyCache.query(qry.setArgs(2L, "username-sql-2", "password-6")).getAll();
dummyCache.query(qry.setArgs(3L, "username-sql-3", "password-7")).getAll();
Best regards,
Slava.
2018-01-26 12:27 GMT+03:00 Thomas Isaksen <th...@sysco.no>>:
Hi Slava
Thanks for pointing out my mistakes with the template.
I have attached the java classes in question and the ignite config file that I am using .
I create the table using DDL as follows:
CREATE TABLE UserCache (
id bigint,
username varchar,
password varchar,
PRIMARY KEY (username, password)
)
WITH "template=userCache, affinitykey=username, cache_name=UserCache, key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey, value_type=no.toyota.gatekeeper.authenticate.Credentials";
Next I try to put one entry into my cache:
@Test
Public void testIgnite()
{
Ignition.setClientMode(true);
Ignite ignite = Ignition.start("/config/test-config.xml");
IgniteCache<CredentialsKey, Credentials> cache = ignite.cache("UserCache");
// this blows up
cache.put(new CredentialsKey("foo","bar"), new Credentials("foo","bar","resourceId"));
}
I am not sure my code is correct but I get the same error when I try to insert a row using SQL.
INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar');
--
Thomas Isaksen
-----Original Message-----
From: slava.koptilin [mailto:slava.koptilin@gmail.com<ma...@gmail.com>]
Sent: torsdag 25. januar 2018 17.39
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: RE: Binary type has different affinity key fields
Hi Thomas,
CREATE TABLE statement doesn't use your template because you specified the predefined one - 'partitioned' *template=partitioned*
In case of using replicated mode, the following property <property name="backups" value="1"/> does not make sense.
Could you please share full reproducer? I will try it on my side and come back to you with findings.
I mean definition CredentialsKey and Credentials and code that can be used in order to reproduce the exception you mentioned.
Best regards,
Slava.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Binary type has different affinity key fields
Posted by Thomas Isaksen <th...@sysco.no>.
Hi Slava,
Thanks a lot for your help this is working great ☺
I do wonder If I can use SQL and JCache API interchangeably?
For example, I do cache.put(key, value) for one row and I insert another one using SQL INSERT. Can I still retrieve both rows using both SQL or JCache API?
The reason I ask is that it seems to me that only the row I stored using SQL INSERT shows up when I do a SELECT on the table, but both rows shows up in ignite visor when I do a cache -scan
Thanks!
--
Thomas Isaksen
From: Вячеслав Коптилин [mailto:slava.koptilin@gmail.com]
Sent: tirsdag 30. januar 2018 13.41
To: user@ignite.apache.org
Subject: Re: Binary type has different affinity key fields
Hi Thomas,
Let's start with the table (I will use java api for that)
// Create dummy cache to act as an entry point for SQL queries (new SQL API which do not require this
// will appear in future versions, JDBC and ODBC drivers do not require it already).
CacheConfiguration<?, ?> cacheCfg = new CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
IgniteCache<?, ?> dummyCache = ignite.getOrCreateCache(cacheCfg);
// Create UserCache table based on the partitioned template.
dummyCache.query(new SqlFieldsQuery(
"CREATE TABLE UserCache (id BIGINT, username VARCHAR, password varchar, PRIMARY KEY (username, password)) " +
"WITH \"template=partitioned," +
"affinitykey=username," +
"cache_name=UserCache," +
"key_type=org.apache.ignite.examples.CredentialsKey," +
"value_type=org.apache.ignite.examples.Credentials\"")).getAll();
one important thing that should be mentioned here is that SQL is case-insensitive and therefore table name and column names will be automatically converted to upper case.
if you want to preserve the case, you need to put double quotes around the table name and columns.
for instance:
// Create UserCache table based on the partitioned template.
dummyCache.query(new SqlFieldsQuery(
"CREATE TABLE \"UserCache\" (\"id\" BIGINT, \"username\" VARCHAR, \"password\" varchar, PRIMARY KEY (\"username\", \"password\")) " +
"WITH \"template=partitioned," +
"affinitykey=username," +
"cache_name=UserCache," +
"key_type=org.apache.ignite.examples.CredentialsKey," +
"value_type=org.apache.ignite.examples.Credentials\"")).getAll();
optionally, you can create indices on UserCache
// Create indices.
dummyCache.query(new SqlFieldsQuery("CREATE INDEX on UserCache (username)")).getAll();
dummyCache.query(new SqlFieldsQuery("CREATE INDEX on UserCache (password)")).getAll();
next step is defining CredentialsKey and Credential classes.
in accordance with the documentation https://apacheignite-sql.readme.io/docs/create-table#section-examples
the PRIMARY KEY columns will be used as the object's key, the rest of the columns will belong to the value.
public class CredentialsKey {
// Please take into account my note about case-insensitive SQL
@AffinityKeyMapped
private String USERNAME;
private String PASSWORD;
public CredentialsKey(String username, String password) {
this.USERNAME = username;
this.PASSWORD = password;
}
public String getUsername() {return USERNAME;}
public void setUsername(String username) {this.USERNAME = username;}
public String getPassword() {return PASSWORD;}
public void setPassword(String password) {this.PASSWORD = password;}
}
public class Credentials {
private long ID;
public Credentials(long id) {
this.ID = id;
}
public long getId() {return ID;}
public void setId(long id) {this.ID = id;}
@Override public String toString() {return "Credentials=[id=" + ID + "]";}
}
Now, you can populate the cache/table via JCache API
IgniteCache testCache = ignite.cache("UserCache");
testCache.put(new CredentialsKey("username-1", "password-1"), new Credentials(1L));
testCache.put(new CredentialsKey("username-2", "password-2"), new Credentials(2L));
testCache.put(new CredentialsKey("username-3", "password-3"), new Credentials(3L));
or SQL API
SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO UserCache (id, username, password) VALUES (?, ?, ?)");
dummyCache.query(qry.setArgs(1L, "username-sql-1", "password-5")).getAll();
dummyCache.query(qry.setArgs(2L, "username-sql-2", "password-6")).getAll();
dummyCache.query(qry.setArgs(3L, "username-sql-3", "password-7")).getAll();
Best regards,
Slava.
2018-01-26 12:27 GMT+03:00 Thomas Isaksen <th...@sysco.no>>:
Hi Slava
Thanks for pointing out my mistakes with the template.
I have attached the java classes in question and the ignite config file that I am using .
I create the table using DDL as follows:
CREATE TABLE UserCache (
id bigint,
username varchar,
password varchar,
PRIMARY KEY (username, password)
)
WITH "template=userCache, affinitykey=username, cache_name=UserCache, key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey, value_type=no.toyota.gatekeeper.authenticate.Credentials";
Next I try to put one entry into my cache:
@Test
Public void testIgnite()
{
Ignition.setClientMode(true);
Ignite ignite = Ignition.start("/config/test-config.xml");
IgniteCache<CredentialsKey, Credentials> cache = ignite.cache("UserCache");
// this blows up
cache.put(new CredentialsKey("foo","bar"), new Credentials("foo","bar","resourceId"));
}
I am not sure my code is correct but I get the same error when I try to insert a row using SQL.
INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar');
--
Thomas Isaksen
-----Original Message-----
From: slava.koptilin [mailto:slava.koptilin@gmail.com<ma...@gmail.com>]
Sent: torsdag 25. januar 2018 17.39
To: user@ignite.apache.org<ma...@ignite.apache.org>
Subject: RE: Binary type has different affinity key fields
Hi Thomas,
CREATE TABLE statement doesn't use your template because you specified the predefined one - 'partitioned' *template=partitioned*
In case of using replicated mode, the following property <property name="backups" value="1"/> does not make sense.
Could you please share full reproducer? I will try it on my side and come back to you with findings.
I mean definition CredentialsKey and Credentials and code that can be used in order to reproduce the exception you mentioned.
Best regards,
Slava.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Binary type has different affinity key fields
Posted by Вячеслав Коптилин <sl...@gmail.com>.
Hi Thomas,
Let's start with the table (I will use java api for that)
// Create dummy cache to act as an entry point for SQL queries (new
SQL API which do not require this
// will appear in future versions, JDBC and ODBC drivers do not
require it already).
CacheConfiguration<?, ?> cacheCfg = new
CacheConfiguration<>(DUMMY_CACHE_NAME).setSqlSchema("PUBLIC");
IgniteCache<?, ?> dummyCache = ignite.getOrCreateCache(cacheCfg);
// Create UserCache table based on the partitioned template.
dummyCache.query(new SqlFieldsQuery(
"CREATE TABLE UserCache (id BIGINT, username VARCHAR, password
varchar, PRIMARY KEY (username, password)) " +
"WITH \"template=partitioned," +
"affinitykey=username," +
"cache_name=UserCache," +
"key_type=org.apache.ignite.examples.CredentialsKey," +
"value_type=org.apache.ignite.examples.Credentials\"")).getAll();
one important thing that should be mentioned here is that SQL is
case-insensitive and therefore table name and column names will be
automatically converted to *upper case*.
if you want to preserve the case, you need to put double quotes around the
table name and columns.
for instance:
// Create UserCache table based on the partitioned template.
dummyCache.query(new SqlFieldsQuery(
"CREATE TABLE \"UserCache\" (\"id\" BIGINT, \"username\" VARCHAR, \"
password\" varchar, PRIMARY KEY (\"username\", \"password\")) " +
"WITH \"template=partitioned," +
"affinitykey=username," +
"cache_name=UserCache," +
"key_type=org.apache.ignite.examples.CredentialsKey," +
"value_type=org.apache.ignite.examples.Credentials\"")).getAll();
optionally, you can create indices on UserCache
// Create indices.
dummyCache.query(new SqlFieldsQuery("CREATE INDEX on UserCache
(username)")).getAll();
dummyCache.query(new SqlFieldsQuery("CREATE INDEX on UserCache
(password)")).getAll();
next step is defining CredentialsKey and Credential classes.
in accordance with the documentation
https://apacheignite-sql.readme.io/docs/create-table#section-examples
the PRIMARY KEY columns will be used as the object's key, the rest of the
columns will belong to the value.
public class CredentialsKey {
// Please take into account my note about case-insensitive SQL
@AffinityKeyMapped
private String USERNAME;
private String PASSWORD;
public CredentialsKey(String username, String password) {
this.USERNAME = username;
this.PASSWORD = password;
}
public String getUsername() {return USERNAME;}
public void setUsername(String username) {this.USERNAME = username;}
public String getPassword() {return PASSWORD;}
public void setPassword(String password) {this.PASSWORD = password;}
}
public class Credentials {
private long ID;
public Credentials(long id) {
this.ID = id;
}
public long getId() {return ID;}
public void setId(long id) {this.ID = id;}
@Override public String toString() {return "Credentials=[id=" + ID + "]";}
}
Now, you can populate the cache/table via JCache API
IgniteCache testCache = ignite.cache("UserCache");
testCache.put(new CredentialsKey("username-1", "password-1"), new
Credentials(1L));
testCache.put(new CredentialsKey("username-2", "password-2"), new
Credentials(2L));
testCache.put(new CredentialsKey("username-3", "password-3"), new
Credentials(3L));
or SQL API
SqlFieldsQuery qry = new SqlFieldsQuery("INSERT INTO UserCache (id,
username, password) VALUES (?, ?, ?)");
dummyCache.query(qry.setArgs(1L, "username-sql-1", "password-5")).getAll();
dummyCache.query(qry.setArgs(2L, "username-sql-2", "password-6")).getAll();
dummyCache.query(qry.setArgs(3L, "username-sql-3", "password-7")).getAll();
Best regards,
Slava.
2018-01-26 12:27 GMT+03:00 Thomas Isaksen <th...@sysco.no>:
> Hi Slava
>
> Thanks for pointing out my mistakes with the template.
> I have attached the java classes in question and the ignite config file
> that I am using .
>
> I create the table using DDL as follows:
>
> CREATE TABLE UserCache (
> id bigint,
> username varchar,
> password varchar,
> PRIMARY KEY (username, password)
> )
> WITH "template=userCache, affinitykey=username, cache_name=UserCache,
> key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey,
> value_type=no.toyota.gatekeeper.authenticate.Credentials";
>
> Next I try to put one entry into my cache:
>
> @Test
> Public void testIgnite()
> {
> Ignition.setClientMode(true);
> Ignite ignite = Ignition.start("/config/test-config.xml");
> IgniteCache<CredentialsKey, Credentials> cache =
> ignite.cache("UserCache");
> // this blows up
> cache.put(new CredentialsKey("foo","bar"), new
> Credentials("foo","bar","resourceId"));
> }
>
> I am not sure my code is correct but I get the same error when I try to
> insert a row using SQL.
>
> INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar');
>
> --
> Thomas Isaksen
>
> -----Original Message-----
> From: slava.koptilin [mailto:slava.koptilin@gmail.com]
> Sent: torsdag 25. januar 2018 17.39
> To: user@ignite.apache.org
> Subject: RE: Binary type has different affinity key fields
>
> Hi Thomas,
>
> CREATE TABLE statement doesn't use your template because you specified the
> predefined one - 'partitioned' *template=partitioned*
>
> In case of using replicated mode, the following property <property
> name="backups" value="1"/> does not make sense.
>
> Could you please share full reproducer? I will try it on my side and come
> back to you with findings.
> I mean definition CredentialsKey and Credentials and code that can be used
> in order to reproduce the exception you mentioned.
>
> Best regards,
> Slava.
>
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
RE: Binary type has different affinity key fields
Posted by Thomas Isaksen <th...@sysco.no>.
Hi Slava
Thanks for pointing out my mistakes with the template.
I have attached the java classes in question and the ignite config file that I am using .
I create the table using DDL as follows:
CREATE TABLE UserCache (
id bigint,
username varchar,
password varchar,
PRIMARY KEY (username, password)
)
WITH "template=userCache, affinitykey=username, cache_name=UserCache, key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey, value_type=no.toyota.gatekeeper.authenticate.Credentials";
Next I try to put one entry into my cache:
@Test
Public void testIgnite()
{
Ignition.setClientMode(true);
Ignite ignite = Ignition.start("/config/test-config.xml");
IgniteCache<CredentialsKey, Credentials> cache = ignite.cache("UserCache");
// this blows up
cache.put(new CredentialsKey("foo","bar"), new Credentials("foo","bar","resourceId"));
}
I am not sure my code is correct but I get the same error when I try to insert a row using SQL.
INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar');
--
Thomas Isaksen
-----Original Message-----
From: slava.koptilin [mailto:slava.koptilin@gmail.com]
Sent: torsdag 25. januar 2018 17.39
To: user@ignite.apache.org
Subject: RE: Binary type has different affinity key fields
Hi Thomas,
CREATE TABLE statement doesn't use your template because you specified the predefined one - 'partitioned' *template=partitioned*
In case of using replicated mode, the following property <property name="backups" value="1"/> does not make sense.
Could you please share full reproducer? I will try it on my side and come back to you with findings.
I mean definition CredentialsKey and Credentials and code that can be used in order to reproduce the exception you mentioned.
Best regards,
Slava.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Binary type has different affinity key fields
Posted by "slava.koptilin" <sl...@gmail.com>.
Hi Thomas,
CREATE TABLE statement doesn't use your template because you specified the
predefined one - 'partitioned' *template=partitioned*
In case of using replicated mode, the following property <property
name="backups" value="1"/> does not make sense.
Could you please share full reproducer? I will try it on my side and come
back to you with findings.
I mean definition CredentialsKey and Credentials and code that can be used
in order to reproduce the exception you mentioned.
Best regards,
Slava.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
RE: Binary type has different affinity key fields
Posted by Thomas Isaksen <th...@sysco.no>.
Hi Slava
I did create the cache using DDL.
CREATE TABLE UserCache (
id long,
username varchar,
password varchar,
PRIMARY KEY (username, password)
)
WITH "template=partitioned, affinitykey=username, cache_name=UserCache, key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey, value_type=no.toyota.gatekeeper.authenticate.Credentials";
The config looks like this, very simple:
<bean id="userCacheTemplate" class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="userCache*"/>
<property name="cacheMode" value="REPLICATED"/>
<property name="backups" value="1"/>
</bean>
Why would I have to use uppercase? I did change now to test it but no still getting the same exception.
./t
-----Original Message-----
From: slava.koptilin [mailto:slava.koptilin@gmail.com]
Sent: torsdag 25. januar 2018 14.08
To: user@ignite.apache.org
Subject: Re: Binary type has different affinity key fields
Hi Thomas,
Could you please share a small code snippet of cache configuration/cache creation?
Do you use DDL for that?
I guess that you need to define affinity keys using upper-case
public class CredentialsKey {
@QuerySqlField(index = true)
@AffinityKeyMapped
private String USERNAME;
@QuerySqlField(index = true)
private String PASSWORD;
...
}
Thanks,
Slava.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/