C SQLite usage

Transcript

1. SQLite usage
2. SQLite • Good ◦ File based ◦ Development ◦ Small • Bad ◦ No ACL ◦ Performance ◦ Alow only one write at a time
3. SQLite Flow • Create DB/table/index • Prepare SQL • SQL bind • SQL Query
4. 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);"
5. 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);
6. 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;
7. 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
8. 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|
9. Create DB/table/index • When to create? ◦ System startup ◦ Program startup ◦ In every query
10. 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; }
11. 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
12. 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;
13. SQL Query • sqlite3_exec ◦ sqlite3_prepare_v2 + sqlite3_step + sqlite3_finalize ◦ Good for insertion/create table/transaction ◦ Bad for query
14. SQL Query sqlite3_exec( sqlite3*, const char *sql, int (*callback)(void*, int column_num, char**, char**), void *, char **errmsg } // void * is for user data
15. 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')
16. 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
17. 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
18. Relational Database • MariaDB/MySQL/Percona ◦ Multiple engines ▪ MyISAM • Table lock ▪ InnoDB • Row lock • Transaction • PostgreSQL • SQLite
19. Relational Database • Replication ◦ For read ◦ Master/Slave • Sharding ◦ For read/write ◦ Do not rely on auto sharding
20. Reference • http://stackoverflow.com/questions/1454188/how-can-i-analyse-a-sqlite-query-execution • https://www.sqlite.org/docs.html • http://www.cnblogs.com/likebeta/archive/2012/06/17/2552757.html