0%

movian-sqldb

Movian 使用 Sqlite3 来管理 metadatakvstore

Sqlite

常用 SQLite 语句 及 SQLite3 的使用

kvstore

kvstore 初始化过程,路径为 res/kvstore,文件列表

kvstore
├── 001.sql
└── 002.sql

main_init ->
kvstore_init ->
int r = db_upgrade_schema(db, buf, "metadb", "kvstore", buf2);

核心代码,通过循环加载 sql 模式

while(1) {

  if(ver == tgtver) {
    TRACE(TRACE_DEBUG, "DB", "%s: At current version %d", dbname, ver);
    if(detach[0]) db_one_statement(db, detach, NULL);
    return 0;
  }

  ver++;
  snprintf(path, sizeof(path), "%s/%03d.sql", schemadir, ver);

  buf_t *sql = fa_load(path,
                        FA_LOAD_ERRBUF(buf, sizeof(buf)),
                        NULL);
  if(sql == NULL) {
    TRACE(TRACE_ERROR, "DB",
      "%s: Unable to upgrade db schema to version %d using %s -- %s",
      dbname, ver, path, buf);
    if(detach[0]) db_one_statement(db, detach, NULL);
    return -1;
  }


  if(strstr(buf_cstr(sql), "-- schema-upgrade:disable-fk")) {
    db_one_statement(db, "PRAGMA foreign_keys=OFF;", NULL);
    enable_fk = 1;
  }
  db_begin(db);
  snprintf(buf, sizeof(buf), "PRAGMA user_version=%d", ver);
  if(db_one_statement(db, buf, NULL)) {
    free(sql);
    break;
  }

  const char *s = buf_cstr(sql);

  while(strchr(s, ';') != NULL) {
    sqlite3_stmt *stmt;

    int rc = sqlite3_prepare_v2(db, s, -1, &stmt, &s);
    if(rc != SQLITE_OK) {
  TRACE(TRACE_ERROR, "DB",
        "%s: Unable to prepare statement in upgrade %d\n%s", dbname, ver, s);
  goto fail;
    }

    rc = sqlite3_step(stmt);
    if(rc != SQLITE_DONE) {
  TRACE(TRACE_ERROR, "DB",
        "%s: Unable to execute statement error %d\n%s", dbname, rc,
        sqlite3_sql(stmt));
  goto fail;
    }
    sqlite3_finalize(stmt);
  }

  db_commit(db);
  if(enable_fk) {
    db_one_statement(db, "PRAGMA foreign_keys=ON;", NULL);
    enable_fk = 0;
  }
  TRACE(TRACE_INFO, "DB", "%s: Upgraded to version %d", dbname, ver);
  buf_release(sql);
}

example

sqlite> .dump url
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE url (
       id INTEGER PRIMARY KEY,
       url TEXT NOT NULL UNIQUE
       );
INSERT INTO "url" VALUES(1,'plugin:start');
INSERT INTO "url" VALUES(2,'plugin:repo:categories');
INSERT INTO "url" VALUES(3,'plugin:repo:tv');
INSERT INTO "url" VALUES(4,'discovered:');
INSERT INTO "url" VALUES(5,'upnp:uuid:4e4e2d48-3130-3233-4e4e-2d4831303233:urn:upnp-org:serviceId:ContentDirectory:0');
INSERT INTO "url" VALUES(6,'settings:');
INSERT INTO "url" VALUES(7,'/home/cell/ 音乐');
INSERT INTO "url" VALUES(8,'/home/cell/ 视频');
INSERT INTO "url" VALUES(9,'/home/cell/ 图片');
INSERT INTO "url" VALUES(10,'file:///home/cell/ 图片 /Screenshots');
INSERT INTO "url" VALUES(11,'settings:general');
INSERT INTO "url" VALUES(12,'plugin:repo:subtitles');
INSERT INTO "url" VALUES(13,'plugin:repo:glwosk');
INSERT INTO "url" VALUES(14,'plugin:repo:music');
INSERT INTO "url" VALUES(15,'plugin:repo:audioengine');
INSERT INTO "url" VALUES(16,'example:music:');
INSERT INTO "url" VALUES(17,'http://www.lonelycoder.com/music/Hybris_Intro-remake.mp3');
INSERT INTO "url" VALUES(18,'settings:lookandfeel');
INSERT INTO "url" VALUES(19,'settings:network');

sqlite> .dump url_kv
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE url_kv (
       url_id INTEGER REFERENCES url(id) ON DELETE CASCADE,
       domain INTEGER NOT NULL,
       key TEXT NOT NULL,
       value,
       UNIQUE (url_id, domain, key));
INSERT INTO "url_kv" VALUES(17,1,'lastplayed',1507880885);
INSERT INTO "url_kv" VALUES(17,1,'playcount',1);
INSERT INTO "url_kv" VALUES(49,2,'glwUserView','list.view');
INSERT INTO "url_kv" VALUES(49,3,'order','relevance');
INSERT INTO "url_kv" VALUES(69,1,'lastplayed',1516080607);
INSERT INTO "url_kv" VALUES(69,1,'playcount',1);
INSERT INTO "url_kv" VALUES(70,1,'lastplayed',1516080693);
INSERT INTO "url_kv" VALUES(70,1,'playcount',1);
INSERT INTO "url_kv" VALUES(72,1,'lastplayed',1516081838);
INSERT INTO "url_kv" VALUES(72,1,'playcount',1);
CREATE INDEX url_kv_url_id_idx ON url_kv(url_id);
COMMIT;

url

CREATE TABLE url (
       id INTEGER PRIMARY KEY,
       url TEXT NOT NULL UNIQUE
       );

CREATE INDEX url_url_idx ON url(url);

set & get

static int get_url(void *db, const char *url, uint64_t *id)

url_kv

CREATE TABLE url_kv (
       url_id INTEGER REFERENCES url(id) ON DELETE CASCADE,
       domain INTEGER NOT NULL,
       key TEXT NOT NULL,
       value,
       UNIQUE (url_id, domain, key));

set

kv_url_opt_set ->
deferred_callout_fire ->
kvstore_deferred_flush ->
kv_write_db ->

if(kw->kw_type == KVSTORE_SET_VOID) {
  rc = db_prepare(db, &stmt,
                  "DELETE FROM url_kv "
                  "WHERE url_id = ?1 "
                  "AND key = ?2 "
                  "AND domain = ?3");

  if(rc != SQLITE_OK)
    return rc;

  value = "[DELETED]";

} else {

  rc = db_prepare(db, &stmt,
                  "INSERT OR REPLACE INTO url_kv "
                  "(url_id, key, domain, value) "
                  "VALUES "
                  "(?1, ?2, ?3, ?4)"
                  );

  if(rc != SQLITE_OK)
    return rc;

  switch(kw->kw_type) {
  case KVSTORE_SET_INT:
    sqlite3_bind_int(stmt, 4, kw->kw_int);
    snprintf(vtmp, sizeof(vtmp), "%d", kw->kw_int);
    break;

  case KVSTORE_SET_INT64:
    sqlite3_bind_int(stmt, 4, kw->kw_int64);
    snprintf(vtmp, sizeof(vtmp), "%"PRId64, kw->kw_int64);
    break;

  case KVSTORE_SET_STRING:
    sqlite3_bind_text(stmt, 4, kw->kw_string, -1, SQLITE_STATIC);
    value = kw->kw_string;
    break;

  default:
    break;
  }
}
sqlite3_bind_int64(stmt, 1, id);
sqlite3_bind_text(stmt, 2, kw->kw_key, -1, SQLITE_STATIC);
sqlite3_bind_int(stmt, 3, kw->kw_domain);

rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);

db_prepare 中的 ?1 ?2,为 sqlite3_bind_ 传入的参数

DOMAIN 有四种

#define KVSTORE_DOMAIN_SYS     1
#define KVSTORE_DOMAIN_PROP    2
#define KVSTORE_DOMAIN_PLUGIN  3
#define KVSTORE_DOMAIN_SETTING 4

idurl 中获取

get

playinfo_register_play ->
kv_url_opt_get_int/int64/rstr ->
kv_url_opt_get ->
SELECT value

int cur = kv_url_opt_get_int(url, KVSTORE_DOMAIN_SYS, "playcount", 0);

metadata

metadata 初始化过程,路径为 res/metadata,文件列表

metadb
├── 001.sql
├── 002.sql
├── 003.sql
├── 004.sql
├── 005.sql
├── 006.sql
├── 007.sql
├── 008.sql
├── 009.sql
├── 010.sql
├── 011.sql
├── 012.sql
├── 013.sql
├── 014.sql
├── 015.sql
├── 016.sql
├── 017.sql
└── 018.sql

main_init ->
metadb_init ->
int r = db_upgrade_schema(db, buf, "metadb", "kvstore", buf2); ->
snprintf(path, sizeof(path), "%s/%03d.sql", schemadir, ver);