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 <[email protected]>:
> 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:[email protected]]
> Sent: torsdag 25. januar 2018 17.39
> To: [email protected]
> 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/
>
