RE: Binary type has different affinity key fields

[Thomas Isaksen]

Hi again, I got it sorted. Thanks a million mate!

--
Thomas Isaksen

From: Thomas Isaksen [mailto:thomas.isak...@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.kopti...@gmail.com]
Sent: tirsdag 30. januar 2018 13.41
To: user@ignite.apache.org<mailto: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 
<thomas.isak...@sysco.no<mailto:thomas.isak...@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.kopti...@gmail.com<mailto:slava.kopti...@gmail.com>]
Sent: torsdag 25. januar 2018 17.39
To: user@ignite.apache.org<mailto: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/