SQLite snippets

1_sample.sql

/*
CREATE文における各種データ型の設定
擬似的にBOOLEAN型を設定
*/
CREATE TABLE IF NOT EXISTS tests (id INTEGER PRIMARY KEY AUTOINCREMENT,
                                  memo TEXT,
                                  image BLOB,
                                  type INTEGER,
                                  favorite BOOLEAN NOT NULL DEFAULT 0 CONSTRAINT testsFavorite CHECK (favorite IN (0, 1) OR favorite IS NULL),
                                  createdAt DATETIME
                                  );

2_sample.sql

/**
 日付表示
 */
sqlite> select primaryKey, datetime(createdAt, 'unixepoch', 'localtime') from h_r_c_addresses;