[SQL] 在 SQLite3 環境中，使用 Regular Expression @ Ubuntu 16.04

$ sudo apt-get install sqlite3-pcre
$ file /usr/lib/sqlite3/pcre.so
/usr/lib/sqlite3/pcre.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=0afc1236e89d4a8b99746f231049101172714c2c, stripped
$ sqlite3
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT date();
2018-10-19
sqlite> SELECT date() AS d WHERE d REGEXP '[0-9]';
Error: no such function: REGEXP
sqlite> .load /usr/lib/sqlite3/pcre.so
sqlite> SELECT date() AS d WHERE d REGEXP '[0-9]';
2018-10-19
sqlite> SELECT date() AS d WHERE d REGEXP '^[0-9]$';
sqlite> SELECT date() AS d WHERE d REGEXP '^[0-9]+$';
sqlite> SELECT date() AS d WHERE d REGEXP '^[0-9\-]+$';
2018-10-19

[C] 使用 SQLite - sqlite3_bind_text 之 value 一直配對(綁定)錯誤

最近整理兩年前的程式，一直有空時會重構，由於時間拉太長了，我已不記得改過什麼，又懶得看 git log，就這樣東摸摸西摸摸，最後發現 bug 了。

有一張表：

CREATE TABLE IF NOT EXISTS test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
data TEXT NOT NULL
);

接著，不斷新增資料，並且透過 sqlite3_bind_text 維護：

INSERT OR IGNORE INTO test (data) VALUES (?),(?),(?)

然而，透過 sqlite3_bind_text 綁定時，一直出錯，起初以為是 UTF-8 問題，但早期程式也沒問題，透過觀察，發現記憶體不如預期，追了好一陣子，發現是 sqlite3_bind_text 的參數問題 Orz

https://www.sqlite.org/c3ref/c_static.html
Constants Defining Special Destructor Behavior

typedef void (*sqlite3_destructor_type)(void*);
#define SQLITE_STATIC      ((sqlite3_destructor_type)0)
#define SQLITE_TRANSIENT   ((sqlite3_destructor_type)-1)

原因在於我一直用 SQLITE_STATIC 啦，但我的結構已經是 std::vector<std::unordered_map<std::string, std::string>> values 這種稍微複雜的，並且透過 iterator 不斷變換資料，因此，必須改用 SQLITE_TRANSIENT 才對。

std::vector<std::unordered_map<std::string, std::string>> values;
std::vector<std::string> fields ({ "data" });
for (auto item : values) {
for (auto field: fields) {
if (item.find(field) != item.end()) {
if (SQLITE_OK != sqlite3_bind_text(stmt, i, item[field].c_str(), -1, SQLITE_TRANSIENT)) {
}
} else {
if (SQLITE_OK != sqlite3_bind_null(stmt, i)) {
}
}
}
}

最後還是埋了個 gtest 定期測試了。

[SQLite] 跨 database (db file) 進行 Table JOIN

使用 SQLITE - Attach 這個指令即可：

$ ls
a.sqlite3 b.sqlite3
$ sqlite3
sqlite> attach 'a.sqlite3' as adb
sqlite> attach 'b.sqlite3' as bdb
sqlite> .tables
adb.user  bdb.data
sqlite> SELECT adb.user.id, adb.user.name, bdb.data.info FROM adb.user, bdb.data WHERE adb.user.id = bdb.data.id

iOS 開發筆記 - 透過 CocoaPods - FMDB / FMDatabase 管理 SQLite Databases

好久沒用 C/C++ 處理 SQLite 的操作，原本有意直接在 Objective-C 一樣寫 C 來處理，但想起來最近一直把玩 CocoaPods ，就搜尋一下，發現 FMDB 還滿多的推薦的，且 github.com/ccgus/fmdb 上頭也有很猛的人數 XD 就下海來使用 FMDB 啦！

主要看中 FMDB 的特色：提供 Data Sanitization 機制！就是寫 PHP 時，會透過 mysql_real_escape_string 來處理 raw data ，避免資料格式破壞 SQL 語法。

把玩筆記：

#import "FMDatabase.h"
// $ vim Podfile
// pod 'FMDB'

- (void)insert {
// Documents/test.db
NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0] stringByAppendingPathComponent:@"test.db"];
BOOL needInitTable = ![[NSFileManager defaultManager] fileExistsAtPath:dbPath];
FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
if ([db open]) {
// init table
if (needInitTable && ![db executeStatements:@"CREATE TABLE IF NOT EXISTS t (id VARCHAR(8), number INT)"]) {
NSLog(@"table init error");
return;
}

// insert data
if (![db executeUpdate:@"INSERT OR IGNORE INTO t (id, number) VALUES ( :id, :number )" withParameterDictionary:@{
@"id" : @"id_data",
@"number": @(12345)
}] ) {
NSLog(@"insert error");
}
[db close];
}
}

- (NSArray *)query {
// Documents/test.db
NSString *dbPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0] stringByAppendingPathComponent:@"test.db"];
BOOL needInitTable = ![[NSFileManager defaultManager] fileExistsAtPath:dbPath];
FMDatabase *db = [FMDatabase databaseWithPath:dbPath];
if (!needInitTable) {
if ([db open]) {
NSMutableArray *output = [[NSMutableArray alloc] init];
FMResultSet *rs = [db executeQuery:"SELECT id, number FROM test;"];
while ([rs next]) {
// records to NSArray
NSMutableDictionary *item = [[NSMutableDictionary alloc] init];

item[@"id"] = [rs stringForColumn:@"id"];
item[@"number"] = @([rs intForColumn:@"number");

[output addObject:item];
}
[db close];
return output;
}
}
return @[];
}