| 1 |
using Sqlite; |
| 2 |
|
| 3 |
namespace Qlite { |
| 4 |
|
| 5 |
public class Table { |
| 6 |
protected Database db; |
| 7 |
public string name { get; private set; } |
| 8 |
protected Column[]? columns; |
| 9 |
private string constraints = ""; |
| 10 |
private string[] post_statements = {}; |
| 11 |
private string[] create_statements = {}; |
| 12 |
internal Column[]? fts_columns; |
| 13 |
|
| 14 |
public Table(Database db, string name) { |
| 15 |
this.db = db; |
| 16 |
this.name = name; |
| 17 |
} |
| 18 |
|
| 19 |
public void init(Column[] columns, string constraints = "") { |
| 20 |
this.columns = columns; |
| 21 |
this.constraints = constraints; |
| 22 |
|
| 23 |
foreach(Column c in columns) { |
| 24 |
c.table = this; |
| 25 |
} |
| 26 |
} |
| 27 |
|
| 28 |
public void fts(Column[] columns) { |
| 29 |
if (fts_columns != null) error("Only one FTS index may be used per table."); |
| 30 |
fts_columns = columns; |
| 31 |
string cs = ""; |
| 32 |
string cnames = ""; |
| 33 |
string cnews = ""; |
| 34 |
foreach (Column c in columns) { |
| 35 |
cs += @", $(c.to_column_definition())"; |
| 36 |
cnames += @", $(c.name)"; |
| 37 |
cnews += @", new.$(c.name)"; |
| 38 |
} |
| 39 |
add_create_statement(@"CREATE VIRTUAL TABLE IF NOT EXISTS _fts_$name USING fts4(tokenize=unicode61, content=\"$name\"$cs)"); |
| 40 |
add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_bu_$(name) BEFORE UPDATE ON $name BEGIN DELETE FROM _fts_$name WHERE docid=old.rowid; END"); |
| 41 |
add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_bd_$(name) BEFORE DELETE ON $name BEGIN DELETE FROM _fts_$name WHERE docid=old.rowid; END"); |
| 42 |
add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_au_$(name) AFTER UPDATE ON $name BEGIN INSERT INTO _fts_$name(docid$cnames) VALUES(new.rowid$cnews); END"); |
| 43 |
add_post_statement(@"CREATE TRIGGER IF NOT EXISTS _fts_ai_$(name) AFTER INSERT ON $name BEGIN INSERT INTO _fts_$name(docid$cnames) VALUES(new.rowid$cnews); END"); |
| 44 |
} |
| 45 |
|
| 46 |
public void fts_rebuild() { |
| 47 |
if (fts_columns == null) error("FTS not available on this table."); |
| 48 |
try { |
| 49 |
db.exec(@"INSERT INTO _fts_$name(_fts_$name) VALUES('rebuild');"); |
| 50 |
} catch (Error e) { |
| 51 |
critical(@"Qlite Error: Rebuilding FTS index: $(e.message)"); |
| 52 |
} |
| 53 |
} |
| 54 |
|
| 55 |
public void unique(Column[] columns, string? on_conflict = null) { |
| 56 |
constraints += ", UNIQUE ("; |
| 57 |
bool first = true; |
| 58 |
foreach (Column c in columns) { |
| 59 |
if (!first) constraints += ", "; |
| 60 |
constraints += c.name; |
| 61 |
first = false; |
| 62 |
} |
| 63 |
constraints += ")"; |
| 64 |
if (on_conflict != null) { |
| 65 |
constraints += " ON CONFLICT " + (!)on_conflict; |
| 66 |
} |
| 67 |
} |
| 68 |
|
| 69 |
public void add_post_statement(string stmt) { |
| 70 |
post_statements += stmt; |
| 71 |
} |
| 72 |
|
| 73 |
public void add_create_statement(string stmt) { |
| 74 |
create_statements += stmt; |
| 75 |
} |
| 76 |
|
| 77 |
public void index(string index_name, Column[] columns, bool unique = false) { |
| 78 |
string stmt = @"CREATE $(unique ? "UNIQUE" : "") INDEX IF NOT EXISTS $index_name ON $name ("; |
| 79 |
bool first = true; |
| 80 |
foreach (Column c in columns) { |
| 81 |
if (!first) stmt += ", "; |
| 82 |
stmt += c.name; |
| 83 |
first = false; |
| 84 |
} |
| 85 |
stmt += ")"; |
| 86 |
add_post_statement(stmt); |
| 87 |
} |
| 88 |
|
| 89 |
private void ensure_init() { |
| 90 |
if (columns == null) error("Table %s was not initialized, call init()", name); |
| 91 |
} |
| 92 |
|
| 93 |
public QueryBuilder select(Column[]? columns = null) { |
| 94 |
ensure_init(); |
| 95 |
return db.select(columns).from(this); |
| 96 |
} |
| 97 |
|
| 98 |
private MatchQueryBuilder match_query() { |
| 99 |
ensure_init(); |
| 100 |
return db.match_query(this); |
| 101 |
} |
| 102 |
|
| 103 |
public MatchQueryBuilder match(Column<string> column, string query) { |
| 104 |
return match_query().match(column, query); |
| 105 |
} |
| 106 |
|
| 107 |
public InsertBuilder insert() { |
| 108 |
ensure_init(); |
| 109 |
return db.insert().into(this); |
| 110 |
} |
| 111 |
|
| 112 |
public UpdateBuilder update() { |
| 113 |
ensure_init(); |
| 114 |
return db.update(this); |
| 115 |
} |
| 116 |
|
| 117 |
public UpsertBuilder upsert() { |
| 118 |
ensure_init(); |
| 119 |
return db.upsert(this); |
| 120 |
} |
| 121 |
|
| 122 |
public DeleteBuilder delete() { |
| 123 |
ensure_init(); |
| 124 |
return db.delete().from(this); |
| 125 |
} |
| 126 |
|
| 127 |
public RowOption row_with<T>(Column<T> column, T value) { |
| 128 |
ensure_init(); |
| 129 |
if (!column.unique && !column.primary_key) error("%s is not suited to identify a row, but used with row_with()", column.name); |
| 130 |
return select().with(column, "=", value).row(); |
| 131 |
} |
| 132 |
|
| 133 |
public bool is_known_column(string column) { |
| 134 |
ensure_init(); |
| 135 |
foreach (Column c in columns) { |
| 136 |
if (c.name == column) return true; |
| 137 |
} |
| 138 |
return false; |
| 139 |
} |
| 140 |
|
| 141 |
public void create_table_at_version(long version) { |
| 142 |
ensure_init(); |
| 143 |
string sql = @"CREATE TABLE IF NOT EXISTS $name ("; |
| 144 |
bool first = true; |
| 145 |
for (int i = 0; i < columns.length; i++) { |
| 146 |
Column c = columns[i]; |
| 147 |
if (c.min_version <= version && c.max_version >= version) { |
| 148 |
sql += @"$(!first ? "," : "") $(c.to_column_definition())"; |
| 149 |
first = false; |
| 150 |
} |
| 151 |
} |
| 152 |
sql += @"$constraints)"; |
| 153 |
try { |
| 154 |
db.exec(sql); |
| 155 |
} catch (Error e) { |
| 156 |
error(@"Qlite Error: Create table at version: $(e.message)"); |
| 157 |
} |
| 158 |
foreach (string stmt in create_statements) { |
| 159 |
try { |
| 160 |
db.exec(stmt); |
| 161 |
} catch (Error e) { |
| 162 |
error(@"Qlite Error: Create table at version: $(e.message)"); |
| 163 |
} |
| 164 |
} |
| 165 |
} |
| 166 |
|
| 167 |
public void add_columns_for_version(long old_version, long new_version) { |
| 168 |
ensure_init(); |
| 169 |
foreach (Column c in columns) { |
| 170 |
if (c.min_version <= new_version && c.max_version >= new_version && c.min_version > old_version) { |
| 171 |
try { |
| 172 |
db.exec(@"ALTER TABLE $name ADD COLUMN $(c.to_column_definition())"); |
| 173 |
} catch (Error e) { |
| 174 |
critical(@"Qlite Error: Add columns for version: $(e.message)"); |
| 175 |
} |
| 176 |
} |
| 177 |
} |
| 178 |
} |
| 179 |
|
| 180 |
public void delete_columns_for_version(long old_version, long new_version) { |
| 181 |
bool column_deletion_required = false; |
| 182 |
string column_list = ""; |
| 183 |
foreach (Column c in columns) { |
| 184 |
if (c.min_version <= new_version && c.max_version >= new_version) { |
| 185 |
if (column_list == "") { |
| 186 |
column_list = c.name; |
| 187 |
} else { |
| 188 |
column_list += ", " + c.name; |
| 189 |
} |
| 190 |
} |
| 191 |
if (!(c.min_version <= new_version && c.max_version >= new_version) && c.min_version <= old_version && c.max_version >= old_version) { |
| 192 |
column_deletion_required = true; |
| 193 |
} |
| 194 |
} |
| 195 |
if (column_deletion_required) { |
| 196 |
try { |
| 197 |
db.exec(@"ALTER TABLE $name RENAME TO _$(name)_$old_version"); |
| 198 |
create_table_at_version(new_version); |
| 199 |
db.exec(@"INSERT INTO $name ($column_list) SELECT $column_list FROM _$(name)_$old_version"); |
| 200 |
db.exec(@"DROP TABLE _$(name)_$old_version"); |
| 201 |
} catch (Error e) { |
| 202 |
error(@"Qlite Error: Delete columns for version change: $(e.message)"); |
| 203 |
} |
| 204 |
} |
| 205 |
} |
| 206 |
|
| 207 |
internal void post() { |
| 208 |
foreach (string stmt in post_statements) { |
| 209 |
try { |
| 210 |
db.exec(stmt); |
| 211 |
} catch (Error e) { |
| 212 |
error(@"Qlite Error: Post: $(e.message)"); |
| 213 |
} |
| 214 |
} |
| 215 |
} |
| 216 |
} |
| 217 |
|
| 218 |
} |