1using Gee; 2using Qlite; 3 4using Dino.Entities; 5 6namespace Dino.Plugins.Omemo { 7 8public class Database : Qlite.Database { 9 private const int VERSION = 5; 10 11 public class IdentityMetaTable : Table { 12 //Default to provide backwards compatability 13 public Column<int> identity_id = new Column.Integer("identity_id") { not_null = true, min_version = 2, default = "-1" }; 14 public Column<string> address_name = new Column.Text("address_name") { not_null = true }; 15 public Column<int> device_id = new Column.Integer("device_id") { not_null = true }; 16 public Column<string?> identity_key_public_base64 = new Column.Text("identity_key_public_base64"); 17 public Column<bool> trusted_identity = new Column.BoolInt("trusted_identity") { default = "0", max_version = 1 }; 18 public Column<int> trust_level = new Column.Integer("trust_level") { default = TrustLevel.UNKNOWN.to_string(), min_version = 2 }; 19 public Column<bool> now_active = new Column.BoolInt("now_active") { default = "1" }; 20 public Column<long> last_active = new Column.Long("last_active"); 21 public Column<long> last_message_untrusted = new Column.Long("last_message_untrusted") { min_version = 5 }; 22 public Column<long> last_message_undecryptable = new Column.Long("last_message_undecryptable") { min_version = 5 }; 23 24 internal IdentityMetaTable(Database db) { 25 base(db, "identity_meta"); 26 init({identity_id, address_name, device_id, identity_key_public_base64, trusted_identity, trust_level, now_active, last_active, last_message_untrusted, last_message_undecryptable}); 27 index("identity_meta_idx", {identity_id, address_name, device_id}, true); 28 index("identity_meta_list_idx", {identity_id, address_name}); 29 } 30 31 public QueryBuilder with_address(int identity_id, string address_name) { 32 return select().with(this.identity_id, "=", identity_id).with(this.address_name, "=", address_name); 33 } 34 35 public QueryBuilder get_with_device_id(int identity_id, int device_id) { 36 return select().with(this.identity_id, "=", identity_id).with(this.device_id, "=", device_id); 37 } 38 39 public void insert_device_list(int32 identity_id, string address_name, ArrayList<int32> devices) { 40 update().with(this.identity_id, "=", identity_id).with(this.address_name, "=", address_name).set(now_active, false).perform(); 41 foreach (int32 device_id in devices) { 42 upsert() 43 .value(this.identity_id, identity_id, true) 44 .value(this.address_name, address_name, true) 45 .value(this.device_id, device_id, true) 46 .value(this.now_active, true) 47 .value(this.last_active, (long) new DateTime.now_utc().to_unix()) 48 .perform(); 49 } 50 } 51 52 public int64 insert_device_bundle(int32 identity_id, string address_name, int device_id, Bundle bundle, TrustLevel trust) { 53 if (bundle == null || bundle.identity_key == null) return -1; 54 // Do not replace identity_key if it was known before, it should never change! 55 string identity_key = Base64.encode(bundle.identity_key.serialize()); 56 RowOption row = with_address(identity_id, address_name).with(this.device_id, "=", device_id).single().row(); 57 if (row.is_present() && row[identity_key_public_base64] != null && row[identity_key_public_base64] != identity_key) { 58 critical("Tried to change the identity key for a known device id. Likely an attack."); 59 return -1; 60 } 61 return upsert() 62 .value(this.identity_id, identity_id, true) 63 .value(this.address_name, address_name, true) 64 .value(this.device_id, device_id, true) 65 .value(this.identity_key_public_base64, identity_key) 66 .value(this.trust_level, trust).perform(); 67 } 68 69 public int64 insert_device_session(int32 identity_id, string address_name, int device_id, string identity_key, TrustLevel trust) { 70 RowOption row = with_address(identity_id, address_name).with(this.device_id, "=", device_id).single().row(); 71 if (row.is_present() && row[identity_key_public_base64] != null && row[identity_key_public_base64] != identity_key) { 72 critical("Tried to change the identity key for a known device id. Likely an attack."); 73 return -1; 74 } 75 return upsert() 76 .value(this.identity_id, identity_id, true) 77 .value(this.address_name, address_name, true) 78 .value(this.device_id, device_id, true) 79 .value(this.identity_key_public_base64, identity_key) 80 .value(this.trust_level, trust).perform(); 81 } 82 83 public void update_last_message_untrusted(int identity_id, int device_id, DateTime? time) { 84 var stmt = update() 85 .with(this.identity_id, "=", identity_id) 86 .with(this.device_id, "=", device_id); 87 if (time != null) { 88 stmt.set(last_message_untrusted, (long)time.to_unix()); 89 } else { 90 stmt.set_null(last_message_untrusted); 91 } 92 stmt.perform(); 93 } 94 95 public void update_last_message_undecryptable(int identity_id, int device_id, DateTime? time) { 96 var stmt = update() 97 .with(this.identity_id, "=", identity_id) 98 .with(this.device_id, "=", device_id); 99 if (time != null) { 100 stmt.set(last_message_undecryptable, (long)time.to_unix()); 101 } else { 102 stmt.set_null(last_message_undecryptable); 103 } 104 stmt.perform(); 105 } 106 107 public QueryBuilder get_trusted_devices(int identity_id, string address_name) { 108 return this.with_address(identity_id, address_name) 109 .with(this.trust_level, "!=", TrustLevel.UNTRUSTED) 110 .with(this.now_active, "=", true); 111 } 112 113 public QueryBuilder get_known_devices(int identity_id, string address_name) { 114 return this.with_address(identity_id, address_name) 115 .with(this.trust_level, "!=", TrustLevel.UNKNOWN) 116 .without_null(this.identity_key_public_base64); 117 } 118 119 public QueryBuilder get_unknown_devices(int identity_id, string address_name) { 120 return this.with_address(identity_id, address_name) 121 .with_null(this.identity_key_public_base64); 122 } 123 124 public QueryBuilder get_new_devices(int identity_id, string address_name) { 125 return this.with_address(identity_id, address_name) 126 .with(this.trust_level, "=", TrustLevel.UNKNOWN) 127 .without_null(this.identity_key_public_base64); 128 } 129 130 public Row? get_device(int identity_id, string address_name, int device_id) { 131 return this.with_address(identity_id, address_name) 132 .with(this.device_id, "=", device_id).single().row().inner; 133 } 134 } 135 136 137 public class TrustTable : Table { 138 public Column<int> identity_id = new Column.Integer("identity_id") { not_null = true }; 139 public Column<string> address_name = new Column.Text("address_name"); 140 public Column<bool> blind_trust = new Column.BoolInt("blind_trust") { default = "1" } ; 141 142 internal TrustTable(Database db) { 143 base(db, "trust"); 144 init({identity_id, address_name, blind_trust}); 145 index("trust_idx", {identity_id, address_name}, true); 146 } 147 148 public bool get_blind_trust(int32 identity_id, string address_name, bool def = false) { 149 RowOption row = this.select().with(this.identity_id, "=", identity_id) 150 .with(this.address_name, "=", address_name).single().row(); 151 if (row.is_present()) return row[blind_trust]; 152 return def; 153 } 154 } 155 156 public class IdentityTable : Table { 157 public Column<int> id = new Column.Integer("id") { primary_key = true, auto_increment = true }; 158 public Column<int> account_id = new Column.Integer("account_id") { unique = true, not_null = true }; 159 public Column<int> device_id = new Column.Integer("device_id") { not_null = true }; 160 public Column<string> identity_key_private_base64 = new Column.NonNullText("identity_key_private_base64"); 161 public Column<string> identity_key_public_base64 = new Column.NonNullText("identity_key_public_base64"); 162 163 internal IdentityTable(Database db) { 164 base(db, "identity"); 165 init({id, account_id, device_id, identity_key_private_base64, identity_key_public_base64}); 166 } 167 168 public int get_id(int account_id) { 169 int id = -1; 170 Row? row = this.row_with(this.account_id, account_id).inner; 171 if (row != null) id = ((!)row)[this.id]; 172 return id; 173 } 174 } 175 176 public class SignedPreKeyTable : Table { 177 public Column<int> identity_id = new Column.Integer("identity_id") { not_null = true }; 178 public Column<int> signed_pre_key_id = new Column.Integer("signed_pre_key_id") { not_null = true }; 179 public Column<string> record_base64 = new Column.NonNullText("record_base64"); 180 181 internal SignedPreKeyTable(Database db) { 182 base(db, "signed_pre_key"); 183 init({identity_id, signed_pre_key_id, record_base64}); 184 unique({identity_id, signed_pre_key_id}); 185 index("signed_pre_key_idx", {identity_id, signed_pre_key_id}, true); 186 } 187 } 188 189 public class PreKeyTable : Table { 190 public Column<int> identity_id = new Column.Integer("identity_id") { not_null = true }; 191 public Column<int> pre_key_id = new Column.Integer("pre_key_id") { not_null = true }; 192 public Column<string> record_base64 = new Column.NonNullText("record_base64"); 193 194 internal PreKeyTable(Database db) { 195 base(db, "pre_key"); 196 init({identity_id, pre_key_id, record_base64}); 197 unique({identity_id, pre_key_id}); 198 index("pre_key_idx", {identity_id, pre_key_id}, true); 199 } 200 } 201 202 public class SessionTable : Table { 203 public Column<int> identity_id = new Column.Integer("identity_id") { not_null = true }; 204 public Column<string> address_name = new Column.NonNullText("name"); 205 public Column<int> device_id = new Column.Integer("device_id") { not_null = true }; 206 public Column<string> record_base64 = new Column.NonNullText("record_base64"); 207 208 internal SessionTable(Database db) { 209 base(db, "session"); 210 init({identity_id, address_name, device_id, record_base64}); 211 unique({identity_id, address_name, device_id}); 212 index("session_idx", {identity_id, address_name, device_id}, true); 213 } 214 } 215 216 public class ContentItemMetaTable : Table { 217 public Column<int> content_item_id = new Column.Integer("message_id") { primary_key = true }; 218 public Column<int> identity_id = new Column.Integer("identity_id") { not_null = true }; 219 public Column<string> address_name = new Column.Text("address_name") { not_null = true }; 220 public Column<int> device_id = new Column.Integer("device_id") { not_null = true }; 221 public Column<bool> trusted_when_received = new Column.BoolInt("trusted_when_received") { not_null = true, default = "1" }; 222 223 internal ContentItemMetaTable(Database db) { 224 base(db, "content_item_meta"); 225 init({content_item_id, identity_id, address_name, device_id, trusted_when_received}); 226 index("content_item_meta_device_idx", {identity_id, device_id, address_name}); 227 } 228 229 public RowOption with_content_item(ContentItem item) { 230 return row_with(content_item_id, item.id); 231 } 232 233 public QueryBuilder with_device(int identity_id, string address_name, int device_id) { 234 return select() 235 .with(this.identity_id, "=", identity_id) 236 .with(this.address_name, "=", address_name) 237 .with(this.device_id, "=", device_id); 238 } 239 } 240 241 public IdentityMetaTable identity_meta { get; private set; } 242 public TrustTable trust { get; private set; } 243 public IdentityTable identity { get; private set; } 244 public SignedPreKeyTable signed_pre_key { get; private set; } 245 public PreKeyTable pre_key { get; private set; } 246 public SessionTable session { get; private set; } 247 public ContentItemMetaTable content_item_meta { get; private set; } 248 249 public Database(string fileName) { 250 base(fileName, VERSION); 251 identity_meta = new IdentityMetaTable(this); 252 trust = new TrustTable(this); 253 identity = new IdentityTable(this); 254 signed_pre_key = new SignedPreKeyTable(this); 255 pre_key = new PreKeyTable(this); 256 session = new SessionTable(this); 257 content_item_meta = new ContentItemMetaTable(this); 258 init({identity_meta, trust, identity, signed_pre_key, pre_key, session, content_item_meta}); 259 260 try { 261 exec("PRAGMA journal_mode = WAL"); 262 exec("PRAGMA synchronous = NORMAL"); 263 exec("PRAGMA secure_delete = ON"); 264 } catch (Error e) { 265 error("Failed to set OMEMO database properties: %s", e.message); 266 } 267 } 268 269 public override void migrate(long oldVersion) { 270 if(oldVersion == 1) { 271 try { 272 exec("DROP INDEX identity_meta_idx"); 273 exec("DROP INDEX identity_meta_list_idx"); 274 exec("CREATE UNIQUE INDEX identity_meta_idx ON identity_meta (identity_id, address_name, device_id)"); 275 exec("CREATE INDEX identity_meta_list_idx ON identity_meta (identity_id, address_name)"); 276 } catch (Error e) { 277 stderr.printf("Failed to migrate OMEMO database\n"); 278 Process.exit(-1); 279 } 280 } 281 } 282} 283 284} 285