How can I expression below sentens to transactd code?

[rush2ko]

1. SELECT COUNT(1) FROM test_info WHERE UserSN = 100000000;
2. SELECT UserNick, Skin, Level, UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(LastUpdate) FROM friend_info WHERE UserSN = 100000000;
3. SELECT UserSN, MemberID FROM reg_id_info WHERE MemberIDHash = CRC32('abcd') AND MemberID = 'abcd';

[bizstation]

Important points

• The index is important for high-speed processing.
• Transactd does not perform calculations on the server. For reduce server workload, it should be done on the client.

Code samples (C++)

(1) Did you mean that you want to check the existance of UserSN = 100000000 record?
We assume that UserSN field is a unique key, and the key number 1.

// To access to single record, using table class is faster.
table* tb = db->opneTable("test_info", TD_OPEN_READONL);
tb->setkeyNum(1); 
tb->setFVN("UserSN", 100000000);
tb->seek();
if (tb->stat() == 0)
   // record UserSN = 100000000 exists.
else if(tb->stat() == STATUS_NOT_FOUND_TI)
   // Not found the record UserSN = 100000000.
else
   // Other error.

(2) Please calculate time stamp value ON THE CLIENT.
We assume that UserSN field is a unique key, and the key number 1.

// time stamp calculation function
double secondsFrom(const char* str)
{
   tm t;
   memset(&t, 0, sizeof(t));
   strptime(str, "%Y-%m-%d %H:%M:%S", &t);
   return difftime(mktime(&m), time(NULL));
}
// To access to single record, using table class is faster.
// Maybe, it is not necessary to select the fields.
table* tb = db->opneTable("test_info", TD_OPEN_READONL);
tb->setkeyNum(1); 
tb->clearBuffer();
tb->setFVN("UserSN", 100000000);
tb->seek();
if (tb->stat() == 0)
{
    const fields& fds = tb->fields();
    printf("UserNick = %s", fds["UserNick"].c_str());
    printf("Skin = %s", fds["Skin"].c_str());
    printf("Level = %s", fds["Level"].c_str());
    printf("LastUpdateFrom = %f second", secondsFrom(fds["LastUpdate"].c_str()));
}

(3) In the same way, calculate CRC32 value ON THE CLIENT, before searching.
We assume that MemberIDHash field is a dupulicatatble key, and the key number 1.

unsigned int CRC32(const char*)
{
// ...snip...
}
unsigned int hashKey = CRC32("abcd");
// You can receive a result set with activaTable.
activeTable at(db, "reg_id_info", TD_OPEN_READONL);
recordset rs;
// Set query
query q;
q.select("UserSN", "MemberID").where("MemberIDHash", "=", hashKey).and_("MemberID" , "=", "abcd");
// Read records
at.index(1).keyValue(hashKey).read(rs, q);
row& r = rs.first();
printf("MemberID = %s", r["MemberID"].c_str());

[rush2ko]

Thank you for your answer. ^^

[bizstation]

Related issue comment: #30 (comment)

[rush2ko]

1 more please... ^^
I want to know record count exactly.

SELECT COUNT(*) FROM result_stage WHERE UserSN = XXXXXXXX;

How can I express this?

[bizstation]

There are some ways for counting records.
Would you tell us the table schema to suggest the best way?

[rush2ko]

The table schema is below

CREATE TABLE user_info (
UserSN INT(10) UNSIGNED NOT NULL DEFAULT '0',
UserNick VARCHAR(50) NOT NULL DEFAULT '0',
LanguageCode VARCHAR(8) NOT NULL DEFAULT 'ko' COMMENT 'en, ja, ko, zh, tw',
Platform TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 : Android, 1 : IOS',
Market TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 : Google, 1 : Apple',
UserNickHash INT(10) UNSIGNED NOT NULL DEFAULT '0',
CreateTime DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (UserSN),
INDEX EmailHash (UserNickHash)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

1. I want to know, how record counting on activeTable? use recordCount() or use recordset rs, rs.size()
2. On table object... there are two field key( ReqSN, RecvSN )
   CREATE TABLE friend_request_info (
   InfoSN INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   ReqSN INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Request UserSN',
   RecvSN INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Received UserSN',
   UpdateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (InfoSN),
   INDEX ReqSN (ReqSN),
   INDEX RecvSN (RecvSN)
   )
   COLLATE='utf8_general_ci'
   ENGINE=InnoDB;

SELECT COUNT(1) FROM friend_request_info WHERE ReqSN = XXXXXXX AND RecvSN = XXXXXX;

[bizstation]

(1) You showed the following code on #29 (comment) :

SELECT COUNT(*) FROM result_stage WHERE UserSN = XXXXXXXX;

It counts records on result_stage table, but you showed the table schema of user_info. Which do you want to count?

If you want to SELECT COUNT(*) FROM user_info WHERE UserSN = XXXXXXXX, it returns 0 or 1 because UserSN is a primary key.

If you want to SELECT COUNT(*) FROM result_stage WHERE UserSN = XXXXXXXX, the table schema of result_stage is important.
The answer of "Which do you think is better table::recordCount() or recordset::size()?" DEPENDS ON TABLE SCHEMA.

I will explain it with the answer of question (2).

(2) It is important that "WHICH INDEX CAN YOU USE?".

If you want to count record with a UNIQUE key (It means that return value is 0 or 1), table::seek and check table::stat is the fastest way.

tb->setKeyNum(0);
tb->setFV("InfoSN", "xxxx"); 
tb->seek(); // The only one access to the server. And read only one record.
count = tb->stat() == 0 ? 1 : 0;

If you want to count records with duplicatable key, read all "ReqSN = xxxx" records on the server.
It is slow if many duplicate values.
In many cases recordset::size is better because the number of network access is smaller.

With activeTable:

recordset rs;
query q;
q.select("ReqSN").where("ReqSN" , "=", "xxxx").and_("RecvSN" , "=", "yyyy").reject(0xffff);
at.index(1).keyValue("xxxx").read(q, rs);  // The only one access to the server. And read all "ReqSN = xxxx" record.
count = rs.size();

With table:

query q;
q.select("ReqSN").where("ReqSN" , "=", "xxxx").and_("RecvSN" , "=", "yyyy").reject(0xffff);
tb->setQuery(q);
tb->setKeyNum(1);
tb->setFV("ReqSN", "xxxx");
tb->seekGreater(true);                  // The first access to the server.
if (tb->stat() == 0)
  count = tb->recordCount(false, true); // The second access to the server. And read all "ReqSN = xxxx" record.

If you can add the multiple-column-index (ReqSN, RecvSN), you can reduce record reading.

With activeTable:

query q;
recordset rs;
q.select("ReqSN").where("ReqSN" , "=", "xxxx").and_("RecvSN" , "=", "yyyy").reject(1);
at.index(3).keyValue("xxxx", "yyyy").read(q, rs);  // The only one access to the server. And read fewest records.
count = rs.size();

With table:

query q;
q.select("ReqSN").where("ReqSN" , "=", "xxxx").and_("RecvSN" , "=", "yyyy").reject(1);
tb->setQuery(q);
tb->setKeyNum(3);
tb->setFV("ReqSN", "xxxx");
tb->setFV("RecvSN", "yyyy");
tb->seekGreater(true);                  // The first access to the server.
if (tb->stat() == 0)
  count = tb->recordCount(false, true); // The second access to the server. And read the fewest records.

Above examples count the number of records.
If you want to know the existence of the record, you can finish searching when a record has been found.

Modify query:

q.select("ReqSN").where("ReqSN" , "=", "xxxx").and_("RecvSN" , "=", "yyyy").reject(0xffff).lmit(1).stopAtlimit(true);

or

q.select("ReqSN").where("ReqSN" , "=", "xxxx").and_("RecvSN" , "=", "yyyy").reject(1).lmit(1).stopAtlimit(true);

[rush2ko]

Thank you so much.

[rush2ko]

What different reject(0xffff) and reject(1) ?

[bizstation]

Please see #33 .