Share
Embed
C SQLite usage
Chein-Wei Huang
September 17, 2015
Transcript
20 slides
-
SQLite • Good ◦ File based ◦ Development ◦ Small
• Bad ◦ No ACL ◦ Performance ◦ Alow only one write at a time -
SQLite Flow • Create DB/table/index • Prepare SQL • SQL
bind • SQL Query -
Create DB/table/index #define CMD_CREATE_DP_TABLE "CREATE TABLE IF NOT EXISTS `data_planner`
(" \ "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," \ "`tx_bytes` bigint(20) NOT NULL," \ "`rx_bytes` bigint(20) NOT NULL," \ "`timestamp` datetime);" #define CMD_CREATE_DP_INDEX "CREATE INDEX IF NOT EXISTS `timestamp_idx` ON \ `data_planner` (`timestamp` DESC);" -
Create DB/table/index sqlite3 *db = NULL; char *err_msg = NULL;
int ret = -1; if (sqlite3_open(DATA_PLANNER_DB_PATH, &db) != SQLITE_OK) { syslog(LOG_ERR, "Open sqlite fail: %s", DATA_PLANNER_DB_PATH); goto out; } if (sqlite3_exec(db, CMD_CREATE_DP_TABLE, NULL, NULL, &err_msg) != SQLITE_OK) { syslog(LOG_ERR, "Create data_planner table fail: %s", err_msg); goto out; } if (err_msg) sqlite3_free(err_msg); -
Create DB/table/index if (sqlite3_exec(db, CMD_CREATE_DP_INDEX, NULL, NULL, &err_msg) != SQLITE_OK)
{ syslog(LOG_ERR, "Create data_planner index fail: %s", err_msg); goto out; } ret = 0; out: if (err_msg) sqlite3_free(err_msg); if (db) sqlite3_close(db); return ret; -
Create DB/table/index • Check if using index • “EXPLAIN <your
query>” ◦ ex: EXPLAIN SELECT * FROM a WERHE name = “1”; ◦ Will show IdxRowid/IdxGE if apply index -
Create DB/table/index sqlite> explain select * from users where name='foo';
0|Trace|0|0|0||00| 1|String8|0|1|0|foo|00| 2|Goto|0|18|0||00| 3|OpenRead|0|2|0|2|00| 4|OpenRead|1|3|0|keyinfo(1,BINARY)|00| 5|IsNull|1|15|0||00| 6|Affinity|1|1|0|bb|00| 7|SeekGe|1|15|1|1|00| 8|IdxGE|1|15|1|1|01| 9|IdxRowid|1|2|0||00| ...blahblah 21|Goto|0|3|0||00| -
Create DB/table/index • When to create? ◦ System startup ◦
Program startup ◦ In every query -
Prepare SQL sqlite3 *db = NULL; sqlite3_stmt *stmt = NULL;
int ret = DP_ERROR; if (sqlite3_open(DATA_PLANNER_DB_PATH, &db) != SQLITE_OK) { syslog(LOG_ERR, "Open sqlite fail: %s", DATA_PLANNER_DB_PATH); goto out; } char *sql = "SELECT sum(tx_bytes) as total_tx_bytes,sum(rx_bytes) as total_rx_bytes \ FROM data_planner WHERE datetime(timestamp, 'localtime') >= ? AND datetime(timestamp, 'localtime') < ?;"; if (sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, NULL) != SQLITE_OK) { syslog(LOG_ERR, "SQL prepare fail: %s", sql); goto out; } -
SQL Bind sqlite3_bind_text(stmt, 1, begin_date, -1, NULL); sqlite3_bind_text(stmt, 2, end_date,
-1, NULL); // Also, there is no uint64, only int64 -
SQL Query if (sqlite3_step(stmt) == SQLITE_ROW) { *total_tx_bytes = sqlite3_column_int64(stmt,
0); *total_rx_bytes = sqlite3_column_int64(stmt, 1); } else { syslog(LOG_ERR, "SQL fail: Error when get previous total data: %s", sqlite3_errmsg(db)); goto out; } ret = 0; out: if (stmt) sqlite3_finalize(stmt); if (db) sqlite3_close(db); return ret; -
SQL Query • sqlite3_exec ◦ sqlite3_prepare_v2 + sqlite3_step + sqlite3_finalize
◦ Good for insertion/create table/transaction ◦ Bad for query -
SQL Query sqlite3_exec( sqlite3*, const char *sql, int (*callback)(void*, int
column_num, char**, char**), void *, char **errmsg } // void * is for user data -
Timezone issue • Use UTC • In C ◦ Use
gmtime_r instead of localtime_r • In SQLite3 ◦ Insert using datetime(?, 'unixepoch') ▪ ? is time_t ◦ Query using datetime(timestamp, 'localtime') -
SQL Debug void* sqlite3_trace( sqlite3* db, trace_callback, void* udp); //
Register sql callback and call it every time before executing void trace_callback( void* udp, const char* sql ); // udp means user data pointer -
SQLite Busy Handle • SQlite may be locked sometimes, solution:
◦ sqlite3_busy_handle ▪ More flexible, can add sleep() and retry times ◦ sqlite3_busy_timeout ◦ http://www.bubuko.com/infodetail-240892.html ◦ http://www.360doc. com/content/10/1214/12/87000_77984300.shtml -
Relational Database • MariaDB/MySQL/Percona ◦ Multiple engines ▪ MyISAM •
Table lock ▪ InnoDB • Row lock • Transaction • PostgreSQL • SQLite -
Relational Database • Replication ◦ For read ◦ Master/Slave •
Sharding ◦ For read/write ◦ Do not rely on auto sharding