1/* This Source Code Form is subject to the terms of the Mozilla Public
2 * License, v. 2.0. If a copy of the MPL was not distributed with this
3 * file, you can obtain one at http://mozilla.org/MPL/2.0/. */
4
5const EXPORTED_SYMBOLS = ["SQLiteDirectory"];
6
7const { AddrBookDirectory } = ChromeUtils.import(
8  "resource:///modules/AddrBookDirectory.jsm"
9);
10const { XPCOMUtils } = ChromeUtils.import(
11  "resource://gre/modules/XPCOMUtils.jsm"
12);
13
14XPCOMUtils.defineLazyModuleGetters(this, {
15  AsyncShutdown: "resource://gre/modules/AsyncShutdown.jsm",
16  FileUtils: "resource://gre/modules/FileUtils.jsm",
17  newUID: "resource:///modules/AddrBookUtils.jsm",
18  Services: "resource://gre/modules/Services.jsm",
19});
20
21var log = console.createInstance({
22  prefix: "mail.addr_book",
23  maxLogLevel: "Warn",
24  maxLogLevelPref: "mail.addr_book.loglevel",
25});
26
27// Track all directories by filename, for SQLiteDirectory.forFile.
28var directories = new Map();
29
30// Keep track of all database connections, and close them at shutdown, since
31// nothing else ever tells us to close them.
32var connections = new Map();
33
34/**
35 * Opens an SQLite connection to `file`, caches the connection, and upgrades
36 * the database schema if necessary.
37 */
38function openConnectionTo(file) {
39  const CURRENT_VERSION = 3;
40
41  let connection = connections.get(file.path);
42  if (!connection) {
43    connection = Services.storage.openDatabase(file);
44    let fileVersion = connection.schemaVersion;
45
46    // If we're upgrading the version, first create a backup.
47    if (fileVersion > 0 && fileVersion < CURRENT_VERSION) {
48      let backupFile = file.clone();
49      backupFile.leafName = backupFile.leafName.replace(
50        /\.sqlite$/,
51        `.v${fileVersion}.sqlite`
52      );
53      backupFile.createUnique(Ci.nsIFile.NORMAL_FILE_TYPE, 0o644);
54
55      log.warn(`Backing up ${file.leafName} to ${backupFile.leafName}`);
56      file.copyTo(null, backupFile.leafName);
57    }
58
59    switch (fileVersion) {
60      case 0:
61        connection.executeSimpleSQL("PRAGMA journal_mode=WAL");
62        connection.executeSimpleSQL(
63          "CREATE TABLE properties (card TEXT, name TEXT, value TEXT)"
64        );
65        connection.executeSimpleSQL(
66          "CREATE TABLE lists (uid TEXT PRIMARY KEY, name TEXT, nickName TEXT, description TEXT)"
67        );
68        connection.executeSimpleSQL(
69          "CREATE TABLE list_cards (list TEXT, card TEXT, PRIMARY KEY(list, card))"
70        );
71      // Falls through.
72      case 1:
73        connection.executeSimpleSQL(
74          "CREATE INDEX properties_card ON properties(card)"
75        );
76        connection.executeSimpleSQL(
77          "CREATE INDEX properties_name ON properties(name)"
78        );
79      // Falls through.
80      case 2:
81        connection.executeSimpleSQL("DROP TABLE IF EXISTS cards");
82        // The lists table may have a localId column we no longer use, but
83        // since SQLite can't drop columns it's not worth effort to remove it.
84        connection.schemaVersion = CURRENT_VERSION;
85        break;
86    }
87    connections.set(file.path, connection);
88  }
89  return connection;
90}
91
92/**
93 * Closes the SQLite connection to `file` and removes it from the cache.
94 */
95function closeConnectionTo(file) {
96  let connection = connections.get(file.path);
97  if (connection) {
98    return new Promise(resolve => {
99      connection.asyncClose({
100        complete() {
101          resolve();
102        },
103      });
104      connections.delete(file.path);
105    });
106  }
107  return Promise.resolve();
108}
109
110// Close all open connections at shut down time.
111AsyncShutdown.profileBeforeChange.addBlocker(
112  "Address Book: closing databases",
113  async () => {
114    let promises = [];
115    for (let directory of directories.values()) {
116      promises.push(directory.cleanUp());
117    }
118    await Promise.allSettled(promises);
119  }
120);
121
122// Close a connection on demand. This serves as an escape hatch from C++ code.
123Services.obs.addObserver(async file => {
124  file.QueryInterface(Ci.nsIFile);
125  await closeConnectionTo(file);
126  Services.obs.notifyObservers(file, "addrbook-close-ab-complete");
127}, "addrbook-close-ab");
128
129/**
130 * Adds SQLite storage to AddrBookDirectory.
131 */
132class SQLiteDirectory extends AddrBookDirectory {
133  init(uri) {
134    let uriParts = /^[\w-]+:\/\/([\w\.-]+\.\w+)$/.exec(uri);
135    if (!uriParts) {
136      throw new Components.Exception(
137        `Unexpected uri: ${uri}`,
138        Cr.NS_ERROR_UNEXPECTED
139      );
140    }
141
142    this._uri = uri;
143    let fileName = uriParts[1];
144    if (fileName.includes("/")) {
145      fileName = fileName.substring(0, fileName.indexOf("/"));
146    }
147
148    for (let child of Services.prefs.getChildList("ldap_2.servers.")) {
149      if (
150        child.endsWith(".filename") &&
151        Services.prefs.getStringPref(child) == fileName
152      ) {
153        this._dirPrefId = child.substring(0, child.length - ".filename".length);
154        break;
155      }
156    }
157    if (!this._dirPrefId) {
158      throw Components.Exception(
159        `Couldn't grab dirPrefId for uri=${uri}, fileName=${fileName}`,
160        Cr.NS_ERROR_UNEXPECTED
161      );
162    }
163
164    // Make sure we always have a file. If a file is not created, the
165    // filename may be accidentally reused.
166    let file = FileUtils.getFile("ProfD", [fileName]);
167    if (!file.exists()) {
168      file.create(Ci.nsIFile.NORMAL_FILE_TYPE, 0o644);
169    }
170
171    this._fileName = fileName;
172
173    super.init(uri);
174
175    directories.set(fileName, this);
176    // Create the DB connection here already, to let init() throw on corrupt SQLite files.
177    this._dbConnection;
178  }
179  async cleanUp() {
180    await super.cleanUp();
181
182    if (this.hasOwnProperty("_file")) {
183      await closeConnectionTo(this._file);
184      delete this._file;
185    }
186
187    directories.delete(this._fileName);
188  }
189
190  get _dbConnection() {
191    this._file = FileUtils.getFile("ProfD", [this.fileName]);
192    let connection = openConnectionTo(this._file);
193
194    // SQLite cache size can be set by the cacheSize preference, in KiB.
195    // The default is 5 MiB but this can be lowered to 1 MiB if wanted.
196    // There is no maximum size.
197    let cacheSize = this.getIntValue("cacheSize", 5120); // 5 MiB
198    cacheSize = Math.max(cacheSize, 1024); // 1 MiB
199    connection.executeSimpleSQL(`PRAGMA cache_size=-${cacheSize}`);
200
201    Object.defineProperty(this, "_dbConnection", {
202      enumerable: true,
203      value: connection,
204      writable: false,
205    });
206    return connection;
207  }
208  get lists() {
209    let listCache = new Map();
210    let selectStatement = this._dbConnection.createStatement(
211      "SELECT uid, name, nickName, description FROM lists"
212    );
213    while (selectStatement.executeStep()) {
214      listCache.set(selectStatement.row.uid, {
215        uid: selectStatement.row.uid,
216        name: selectStatement.row.name,
217        nickName: selectStatement.row.nickName,
218        description: selectStatement.row.description,
219      });
220    }
221    selectStatement.finalize();
222
223    Object.defineProperty(this, "lists", {
224      enumerable: true,
225      value: listCache,
226      writable: false,
227    });
228    return listCache;
229  }
230  get cards() {
231    let cardCache = new Map();
232    let propertiesStatement = this._dbConnection.createStatement(
233      "SELECT card, name, value FROM properties"
234    );
235    while (propertiesStatement.executeStep()) {
236      let uid = propertiesStatement.row.card;
237      if (!cardCache.has(uid)) {
238        cardCache.set(uid, new Map());
239      }
240      let card = cardCache.get(uid);
241      if (card) {
242        card.set(propertiesStatement.row.name, propertiesStatement.row.value);
243      }
244    }
245    propertiesStatement.finalize();
246
247    Object.defineProperty(this, "cards", {
248      enumerable: true,
249      value: cardCache,
250      writable: false,
251    });
252    return cardCache;
253  }
254
255  loadCardProperties(uid) {
256    if (this.hasOwnProperty("cards")) {
257      let cachedCard = this.cards.get(uid);
258      if (cachedCard) {
259        return new Map(cachedCard);
260      }
261    }
262    let properties = new Map();
263    let propertyStatement = this._dbConnection.createStatement(
264      "SELECT name, value FROM properties WHERE card = :card"
265    );
266    propertyStatement.params.card = uid;
267    while (propertyStatement.executeStep()) {
268      properties.set(propertyStatement.row.name, propertyStatement.row.value);
269    }
270    propertyStatement.finalize();
271    return properties;
272  }
273  saveCardProperties(card) {
274    let cachedCard;
275    if (this.hasOwnProperty("cards")) {
276      cachedCard = this.cards.get(card.UID);
277      cachedCard.clear();
278    }
279
280    this._dbConnection.beginTransaction();
281    let deleteStatement = this._dbConnection.createStatement(
282      "DELETE FROM properties WHERE card = :card"
283    );
284    deleteStatement.params.card = card.UID;
285    deleteStatement.execute();
286    let insertStatement = this._dbConnection.createStatement(
287      "INSERT INTO properties VALUES (:card, :name, :value)"
288    );
289    let saveProp = function(name, value) {
290      insertStatement.params.card = card.UID;
291      insertStatement.params.name = name;
292      insertStatement.params.value = value;
293      insertStatement.execute();
294      insertStatement.reset();
295
296      if (cachedCard) {
297        cachedCard.set(name, value);
298      }
299    };
300
301    for (let { name, value } of card.properties) {
302      if (
303        name != "LastModifiedDate" &&
304        value !== null &&
305        value !== undefined &&
306        value !== ""
307      ) {
308        saveProp(name, value);
309      }
310    }
311    // Always set the last modified date.
312    let now = "" + Math.floor(Date.now() / 1000);
313    card.setProperty("LastModifiedDate", now);
314    saveProp("LastModifiedDate", now);
315
316    this._dbConnection.commitTransaction();
317    deleteStatement.finalize();
318    insertStatement.finalize();
319  }
320  deleteCard(uid) {
321    let deleteStatement = this._dbConnection.createStatement(
322      "DELETE FROM properties WHERE card = :cardUID"
323    );
324    deleteStatement.params.cardUID = uid;
325    deleteStatement.execute();
326    deleteStatement.finalize();
327  }
328  saveList(list) {
329    // Ensure list cache exists.
330    this.lists;
331
332    let replaceStatement = this._dbConnection.createStatement(
333      "REPLACE INTO lists (uid, name, nickName, description) " +
334        "VALUES (:uid, :name, :nickName, :description)"
335    );
336    replaceStatement.params.uid = list._uid;
337    replaceStatement.params.name = list._name;
338    replaceStatement.params.nickName = list._nickName;
339    replaceStatement.params.description = list._description;
340    replaceStatement.execute();
341    replaceStatement.finalize();
342
343    this.lists.set(list._uid, {
344      uid: list._uid,
345      name: list._name,
346      nickName: list._nickName,
347      description: list._description,
348    });
349  }
350  deleteList(uid) {
351    let deleteListStatement = this._dbConnection.createStatement(
352      "DELETE FROM lists WHERE uid = :uid"
353    );
354    deleteListStatement.params.uid = uid;
355    deleteListStatement.execute();
356    deleteListStatement.finalize();
357
358    if (this.hasOwnProperty("lists")) {
359      this.lists.delete(uid);
360    }
361
362    this._dbConnection.executeSimpleSQL(
363      "DELETE FROM list_cards WHERE list NOT IN (SELECT DISTINCT uid FROM lists)"
364    );
365  }
366  async _bulkAddCards(cards) {
367    if (cards.length == 0) {
368      return;
369    }
370
371    let usedUIDs = new Set();
372    let propertiesStatement = this._dbConnection.createStatement(
373      "INSERT INTO properties VALUES (:card, :name, :value)"
374    );
375    let propertiesArray = propertiesStatement.newBindingParamsArray();
376    for (let card of cards) {
377      let uid = card.UID;
378      if (!uid || usedUIDs.has(uid)) {
379        // A card cannot have the same UID as one that already exists.
380        // Assign a new UID to avoid losing data.
381        uid = newUID();
382      }
383      usedUIDs.add(uid);
384
385      let cachedCard;
386      if (this.hasOwnProperty("lists")) {
387        cachedCard = new Map();
388        this.cards.set(uid, cachedCard);
389      }
390
391      for (let { name, value } of card.properties) {
392        if (
393          [
394            "DbRowID",
395            "LowercasePrimaryEmail",
396            "LowercaseSecondEmail",
397            "RecordKey",
398            "UID",
399          ].includes(name)
400        ) {
401          continue;
402        }
403        let propertiesParams = propertiesArray.newBindingParams();
404        propertiesParams.bindByName("card", uid);
405        propertiesParams.bindByName("name", name);
406        propertiesParams.bindByName("value", value);
407        propertiesArray.addParams(propertiesParams);
408
409        if (cachedCard) {
410          cachedCard.set(name, value);
411        }
412      }
413    }
414    try {
415      this._dbConnection.beginTransaction();
416      if (propertiesArray.length > 0) {
417        propertiesStatement.bindParameters(propertiesArray);
418        await new Promise((resolve, reject) => {
419          propertiesStatement.executeAsync({
420            handleError(error) {
421              this._error = error;
422            },
423            handleCompletion(status) {
424              if (status == Ci.mozIStorageStatementCallback.REASON_ERROR) {
425                reject(
426                  Components.Exception(this._error.message, Cr.NS_ERROR_FAILURE)
427                );
428              } else {
429                resolve();
430              }
431            },
432          });
433        });
434        propertiesStatement.finalize();
435      }
436      this._dbConnection.commitTransaction();
437
438      Services.obs.notifyObservers(this, "addrbook-directory-invalidated");
439    } catch (ex) {
440      this._dbConnection.rollbackTransaction();
441      throw ex;
442    }
443  }
444
445  /* nsIAbDirectory */
446
447  getCardFromProperty(property, value, caseSensitive) {
448    let sql = caseSensitive
449      ? "SELECT card FROM properties WHERE name = :name AND value = :value LIMIT 1"
450      : "SELECT card FROM properties WHERE name = :name AND LOWER(value) = LOWER(:value) LIMIT 1";
451    let selectStatement = this._dbConnection.createStatement(sql);
452    selectStatement.params.name = property;
453    selectStatement.params.value = value;
454    let result = null;
455    if (selectStatement.executeStep()) {
456      result = this.getCard(selectStatement.row.card);
457    }
458    selectStatement.finalize();
459    return result;
460  }
461  getCardsFromProperty(property, value, caseSensitive) {
462    let sql = caseSensitive
463      ? "SELECT card FROM properties WHERE name = :name AND value = :value"
464      : "SELECT card FROM properties WHERE name = :name AND LOWER(value) = LOWER(:value)";
465    let selectStatement = this._dbConnection.createStatement(sql);
466    selectStatement.params.name = property;
467    selectStatement.params.value = value;
468    let results = [];
469    while (selectStatement.executeStep()) {
470      results.push(this.getCard(selectStatement.row.card));
471    }
472    selectStatement.finalize();
473    return results;
474  }
475
476  static forFile(fileName) {
477    return directories.get(fileName);
478  }
479}
480SQLiteDirectory.prototype.classID = Components.ID(
481  "{e96ee804-0bd3-472f-81a6-8a9d65277ad3}"
482);
483