Bug 443433 - [refactor] move places table creation statements to one file
authorShawn Wilsher <sdwilsh@shawnwilsher.com>
Mon, 04 Aug 2008 13:14:17 -0400
changeset 16361 0d0a155411ddc4a4f9fdddeaf1151e373b59b2bd
parent 16360 cba48512577ad9273ba603f3fd5b9e85c94b826e
child 16362 2b66c4c351f78e5ab8cb11d7d1bd99fa9f55a25c
push idunknown
push userunknown
push dateunknown
bugs443433
milestone1.9.1a2pre
Bug 443433 - [refactor] move places table creation statements to one file This refactors how tables are created in places. There is no change in logic. r=dietrich
toolkit/components/places/src/nsAnnotationService.cpp
toolkit/components/places/src/nsFaviconService.cpp
toolkit/components/places/src/nsNavBookmarks.cpp
toolkit/components/places/src/nsNavHistory.cpp
toolkit/components/places/src/nsPlacesTables.h
--- a/toolkit/components/places/src/nsAnnotationService.cpp
+++ b/toolkit/components/places/src/nsAnnotationService.cpp
@@ -45,16 +45,17 @@
 #include "mozIStorageStatement.h"
 #include "mozIStorageFunction.h"
 #include "mozStorageHelper.h"
 #include "nsIServiceManager.h"
 #include "nsIVariant.h"
 #include "nsString.h"
 #include "nsVariant.h"
 #include "nsNavBookmarks.h"
+#include "nsPlacesTables.h"
 
 const PRInt32 nsAnnotationService::kAnnoIndex_ID = 0;
 const PRInt32 nsAnnotationService::kAnnoIndex_PageOrItem = 1;
 const PRInt32 nsAnnotationService::kAnnoIndex_Name = 2;
 const PRInt32 nsAnnotationService::kAnnoIndex_MimeType = 3;
 const PRInt32 nsAnnotationService::kAnnoIndex_Content = 4;
 const PRInt32 nsAnnotationService::kAnnoIndex_Flags = 5;
 const PRInt32 nsAnnotationService::kAnnoIndex_Expiration = 6;
@@ -243,56 +244,35 @@ nsAnnotationService::Init()
 nsresult // static
 nsAnnotationService::InitTables(mozIStorageConnection* aDBConn)
 {
   nsresult rv;
   PRBool exists;
   rv = aDBConn->TableExists(NS_LITERAL_CSTRING("moz_annos"), &exists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (! exists) {
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_annos ("
-        "id INTEGER PRIMARY KEY,"
-        "place_id INTEGER NOT NULL,"
-        "anno_attribute_id INTEGER,"
-        "mime_type VARCHAR(32) DEFAULT NULL,"
-        "content LONGVARCHAR, flags INTEGER DEFAULT 0,"
-        "expiration INTEGER DEFAULT 0,"
-        "type INTEGER DEFAULT 0,"
-        "dateAdded INTEGER DEFAULT 0," 
-        "lastModified INTEGER DEFAULT 0)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_ANNOS);
     NS_ENSURE_SUCCESS(rv, rv);
 
     rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
         "CREATE UNIQUE INDEX moz_annos_placeattributeindex ON moz_annos (place_id, anno_attribute_id)"));
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   rv = aDBConn->TableExists(NS_LITERAL_CSTRING("moz_anno_attributes"), &exists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (! exists) {
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
-        "CREATE TABLE moz_anno_attributes ("
-        "id INTEGER PRIMARY KEY,"
-        "name VARCHAR(32) UNIQUE NOT NULL)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_ANNO_ATTRIBUTES);
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   rv = aDBConn->TableExists(NS_LITERAL_CSTRING("moz_items_annos"), &exists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (! exists) {
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_items_annos ("
-        "id INTEGER PRIMARY KEY,"
-        "item_id INTEGER NOT NULL,"
-        "anno_attribute_id INTEGER,"
-        "mime_type VARCHAR(32) DEFAULT NULL,"
-        "content LONGVARCHAR, flags INTEGER DEFAULT 0,"
-        "expiration INTEGER DEFAULT 0,"
-        "type INTEGER DEFAULT 0,"
-        "dateAdded INTEGER DEFAULT 0," 
-        "lastModified INTEGER DEFAULT 0)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_ITEMS_ANNOS);
     NS_ENSURE_SUCCESS(rv, rv);
     rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
         "CREATE UNIQUE INDEX moz_items_annos_itemattributeindex ON moz_items_annos (item_id, anno_attribute_id)"));
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   return NS_OK;
 }
--- a/toolkit/components/places/src/nsFaviconService.cpp
+++ b/toolkit/components/places/src/nsFaviconService.cpp
@@ -58,16 +58,17 @@
 #include "nsNavBookmarks.h"
 #include "nsNavHistory.h"
 #include "nsNetUtil.h"
 #include "nsReadableUtils.h"
 #include "nsStreamUtils.h"
 #include "nsStringStream.h"
 #include "mozStorageHelper.h"
 #include "plbase64.h"
+#include "nsPlacesTables.h"
 
 // For favicon optimization
 #include "imgITools.h"
 #include "imgIContainer.h"
 
 #define FAVICON_BUFFER_INCREMENT 8192
 
 #define MAX_FAVICON_CACHE_SIZE 512
@@ -189,22 +190,17 @@ nsFaviconService::Init()
 
 nsresult // static
 nsFaviconService::InitTables(mozIStorageConnection* aDBConn)
 {
   nsresult rv;
   PRBool exists = PR_FALSE;
   aDBConn->TableExists(NS_LITERAL_CSTRING("moz_favicons"), &exists);
   if (! exists) {
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
-      "CREATE TABLE moz_favicons (id INTEGER PRIMARY KEY, "
-                                  "url LONGVARCHAR UNIQUE, "
-                                  "data BLOB, "
-                                  "mime_type VARCHAR(32), "
-                                  "expiration LONG)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_FAVICONS);
     NS_ENSURE_SUCCESS(rv, rv);
   }
   return NS_OK;
 }
 
 
 // nsFaviconService::SetFaviconUrlForPage
 
--- a/toolkit/components/places/src/nsNavBookmarks.cpp
+++ b/toolkit/components/places/src/nsNavBookmarks.cpp
@@ -46,16 +46,17 @@
 #include "nsUnicharUtils.h"
 #include "nsFaviconService.h"
 #include "nsAnnotationService.h"
 #include "nsPrintfCString.h"
 #include "nsIUUIDGenerator.h"
 #include "prprf.h"
 #include "nsILivemarkService.h"
 #include "nsPlacesTriggers.h"
+#include "nsPlacesTables.h"
 
 const PRInt32 nsNavBookmarks::kFindBookmarksIndex_ID = 0;
 const PRInt32 nsNavBookmarks::kFindBookmarksIndex_Type = 1;
 const PRInt32 nsNavBookmarks::kFindBookmarksIndex_ForeignKey = 2;
 const PRInt32 nsNavBookmarks::kFindBookmarksIndex_Parent = 3;
 const PRInt32 nsNavBookmarks::kFindBookmarksIndex_Position = 4;
 const PRInt32 nsNavBookmarks::kFindBookmarksIndex_Title = 5;
 
@@ -322,29 +323,17 @@ nsNavBookmarks::Init()
 
 nsresult // static
 nsNavBookmarks::InitTables(mozIStorageConnection* aDBConn)
 {
   PRBool exists;
   nsresult rv = aDBConn->TableExists(NS_LITERAL_CSTRING("moz_bookmarks"), &exists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (! exists) {
-    // The fk column is for "foreign key". It contains ids from moz_places
-    // if the row is a bookmark.
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_bookmarks ("
-        "id INTEGER PRIMARY KEY,"
-        "type INTEGER, "
-        "fk INTEGER DEFAULT NULL, "
-        "parent INTEGER, "
-        "position INTEGER, "
-        "title LONGVARCHAR, "
-        "keyword_id INTEGER, "
-        "folder_type TEXT, "
-        "dateAdded INTEGER, " 
-        "lastModified INTEGER)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_BOOKMARKS);
     NS_ENSURE_SUCCESS(rv, rv);
 
     // This index will make it faster to determine if a given item is
     // bookmarked (used by history queries and vacuuming, for example).
     // Making it compound with "type" speeds up type-differentiation
     // queries, such as expiration and search.
     rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
         "CREATE INDEX moz_bookmarks_itemindex ON moz_bookmarks (fk, type)"));
@@ -363,30 +352,25 @@ nsNavBookmarks::InitTables(mozIStorageCo
         "ON moz_bookmarks (fk, lastModified)"));
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   // moz_bookmarks_roots
   rv = aDBConn->TableExists(NS_LITERAL_CSTRING("moz_bookmarks_roots"), &exists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (!exists) {
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_bookmarks_roots ("
-        "root_name VARCHAR(16) UNIQUE, "
-        "folder_id INTEGER)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_BOOKMARKS_ROOTS);
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   // moz_keywords
   rv = aDBConn->TableExists(NS_LITERAL_CSTRING("moz_keywords"), &exists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (! exists) {
-    rv = aDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
-        "CREATE TABLE moz_keywords ("
-        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
-        "keyword TEXT UNIQUE)"));
+    rv = aDBConn->ExecuteSimpleSQL(CREATE_MOZ_KEYWORDS);
     NS_ENSURE_SUCCESS(rv, rv);
 
     // Create trigger to update as well
     rv = aDBConn->ExecuteSimpleSQL(CREATE_KEYWORD_VALIDITY_TRIGGER);
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   return NS_OK;
--- a/toolkit/components/places/src/nsNavHistory.cpp
+++ b/toolkit/components/places/src/nsNavHistory.cpp
@@ -39,16 +39,17 @@
  * the terms of any one of the MPL, the GPL or the LGPL.
  *
  * ***** END LICENSE BLOCK ***** */
 
 #include <stdio.h>
 #include "nsNavHistory.h"
 #include "nsNavBookmarks.h"
 #include "nsAnnotationService.h"
+#include "nsPlacesTables.h"
 
 #include "nsIArray.h"
 #include "nsArrayEnumerator.h"
 #include "nsCollationCID.h"
 #include "nsCOMPtr.h"
 #include "nsCRT.h"
 #include "nsDateTimeFormatCID.h"
 #include "nsDebug.h"
@@ -787,26 +788,17 @@ nsNavHistory::InitDB(PRInt16 *aMadeChang
   // http://www.sqlite.org/pragma.html#pragma_locking_mode
   rv = mDBConn->ExecuteSimpleSQL(
     NS_LITERAL_CSTRING("PRAGMA locking_mode = EXCLUSIVE"));
   NS_ENSURE_SUCCESS(rv, rv);
 
   // moz_places
   if (!tableExists) {
     *aMadeChanges = DB_MIGRATION_CREATED;
-    rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_places ("
-        "id INTEGER PRIMARY KEY, "
-        "url LONGVARCHAR, "
-        "title LONGVARCHAR, "
-        "rev_host LONGVARCHAR, "
-        "visit_count INTEGER DEFAULT 0, "
-        "hidden INTEGER DEFAULT 0 NOT NULL, "
-        "typed INTEGER DEFAULT 0 NOT NULL, "
-        "favicon_id INTEGER, "
-        "frecency INTEGER DEFAULT -1 NOT NULL)"));
+    rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_PLACES);
     NS_ENSURE_SUCCESS(rv, rv);
 
     rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
         "CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url)"));
     NS_ENSURE_SUCCESS(rv, rv);
 
     // This index is used for favicon expiration, see nsNavHistoryExpire::ExpireItems.
     rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
@@ -825,23 +817,17 @@ nsNavHistory::InitDB(PRInt16 *aMadeChang
         "CREATE INDEX moz_places_frecencyindex ON moz_places (frecency)"));
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   // moz_historyvisits
   rv = mDBConn->TableExists(NS_LITERAL_CSTRING("moz_historyvisits"), &tableExists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (! tableExists) {
-    rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_historyvisits ("
-        "id INTEGER PRIMARY KEY, "
-        "from_visit INTEGER, "
-        "place_id INTEGER, "
-        "visit_date INTEGER, "
-        "visit_type INTEGER, "
-        "session INTEGER)"));
+    rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_HISTORYVISITS);
     NS_ENSURE_SUCCESS(rv, rv);
 
     rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
         "CREATE INDEX moz_historyvisits_placedateindex "
         "ON moz_historyvisits (place_id, visit_date)"));
     NS_ENSURE_SUCCESS(rv, rv);
 
     // This makes a big difference in startup time for large profiles because of
@@ -860,21 +846,17 @@ nsNavHistory::InitDB(PRInt16 *aMadeChang
     rv = mDBConn->ExecuteSimpleSQL(CREATE_VISIT_COUNT_DELETE_TRIGGER);
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   // moz_inputhistory
   rv = mDBConn->TableExists(NS_LITERAL_CSTRING("moz_inputhistory"), &tableExists);
   NS_ENSURE_SUCCESS(rv, rv);
   if (!tableExists) {
-    rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE moz_inputhistory ("
-        "place_id INTEGER NOT NULL, "
-        "input LONGVARCHAR NOT NULL, "
-        "use_count INTEGER, "
-        "PRIMARY KEY (place_id, input))"));
+    rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_INPUTHISTORY);
     NS_ENSURE_SUCCESS(rv, rv);
   }
 
   PRBool migrated = PR_FALSE;
   rv = EnsureCurrentSchema(mDBConn, &migrated);
   NS_ENSURE_SUCCESS(rv, rv);
   if (migrated && *aMadeChanges != DB_MIGRATION_CREATED)
     *aMadeChanges = DB_MIGRATION_UPDATED;
new file mode 100644
--- /dev/null
+++ b/toolkit/components/places/src/nsPlacesTables.h
@@ -0,0 +1,152 @@
+/* vim: sw=2 ts=2 sts=2 expandtab
+ * ***** BEGIN LICENSE BLOCK *****
+ * Version: MPL 1.1/GPL 2.0/LGPL 2.1
+ *
+ * The contents of this file are subject to the Mozilla Public License Version
+ * 1.1 (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ * http://www.mozilla.org/MPL/
+ *
+ * Software distributed under the License is distributed on an "AS IS" basis,
+ * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
+ * for the specific language governing rights and limitations under the
+ * License.
+ *
+ * The Original Code is Places code.
+ *
+ * The Initial Developer of the Original Code is
+ * Mozilla Corporation.
+ * Portions created by the Initial Developer are Copyright (C) 2008
+ * the Initial Developer. All Rights Reserved.
+ *
+ * Contributor(s):
+ *   Shawn Wilsher <me@shawnwilsher.com> (Original Author)
+ *
+ * Alternatively, the contents of this file may be used under the terms of
+ * either the GNU General Public License Version 2 or later (the "GPL"), or
+ * the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
+ * in which case the provisions of the GPL or the LGPL are applicable instead
+ * of those above. If you wish to allow use of your version of this file only
+ * under the terms of either the GPL or the LGPL, and not to allow others to
+ * use your version of this file under the terms of the MPL, indicate your
+ * decision by deleting the provisions above and replace them with the notice
+ * and other provisions required by the GPL or the LGPL. If you do not delete
+ * the provisions above, a recipient may use your version of this file under
+ * the terms of any one of the MPL, the GPL or the LGPL.
+ *
+ * ***** END LICENSE BLOCK ***** */
+
+#ifndef __nsPlacesTables_h__
+#define __nsPlacesTables_h__
+
+#define CREATE_MOZ_PLACES NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_places ( " \
+    "  id INTEGER PRIMARY KEY" \
+    ", url LONGVARCHAR" \
+    ", title LONGVARCHAR" \
+    ", rev_host LONGVARCHAR" \
+    ", visit_count INTEGER DEFAULT 0" \
+    ", hidden INTEGER DEFAULT 0 NOT NULL" \
+    ", typed INTEGER DEFAULT 0 NOT NULL" \
+    ", favicon_id INTEGER" \
+    ", frecency INTEGER DEFAULT -1 NOT NULL" \
+  ")" \
+)
+
+#define CREATE_MOZ_HISTORYVISITS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_historyvisits (" \
+    "  id INTEGER PRIMARY KEY" \
+    ", from_visit INTEGER" \
+    ", place_id INTEGER" \
+    ", visit_date INTEGER" \
+    ", visit_type INTEGER" \
+    ", session INTEGER" \
+  ")" \
+)
+
+#define CREATE_MOZ_INPUTHISTORY NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_inputhistory (" \
+    "  place_id INTEGER NOT NULL" \
+    ", input LONGVARCHAR NOT NULL" \
+    ", use_count INTEGER" \
+    ", PRIMARY KEY (place_id, input)" \
+  ")" \
+)
+
+#define CREATE_MOZ_ANNOS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_annos (" \
+    "  id INTEGER PRIMARY KEY" \
+    ", place_id INTEGER NOT NULL" \
+    ", anno_attribute_id INTEGER" \
+    ", mime_type VARCHAR(32) DEFAULT NULL" \
+    ", content LONGVARCHAR" \
+    ", flags INTEGER DEFAULT 0" \
+    ", expiration INTEGER DEFAULT 0" \
+    ", type INTEGER DEFAULT 0" \
+    ", dateAdded INTEGER DEFAULT 0" \
+    ", lastModified INTEGER DEFAULT 0" \
+  ")" \
+)
+
+#define CREATE_MOZ_ANNO_ATTRIBUTES NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_anno_attributes (" \
+    "  id INTEGER PRIMARY KEY" \
+    ", name VARCHAR(32) UNIQUE NOT NULL" \
+  ")" \
+)
+
+#define CREATE_MOZ_ITEMS_ANNOS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_items_annos (" \
+    "  id INTEGER PRIMARY KEY" \
+    ", item_id INTEGER NOT NULL" \
+    ", anno_attribute_id INTEGER" \
+    ", mime_type VARCHAR(32) DEFAULT NULL" \
+    ", content LONGVARCHAR" \
+    ", flags INTEGER DEFAULT 0" \
+    ", expiration INTEGER DEFAULT 0" \
+    ", type INTEGER DEFAULT 0" \
+    ", dateAdded INTEGER DEFAULT 0" \
+    ", lastModified INTEGER DEFAULT 0" \
+  ")" \
+)
+
+#define CREATE_MOZ_FAVICONS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_favicons (" \
+    "  id INTEGER PRIMARY KEY" \
+    ", url LONGVARCHAR UNIQUE" \
+    ", data BLOB" \
+    ", mime_type VARCHAR(32)" \
+    ", expiration LONG" \
+  ")" \
+)
+
+#define CREATE_MOZ_BOOKMARKS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_bookmarks (" \
+    "  id INTEGER PRIMARY KEY" \
+    ", type INTEGER" \
+    ", fk INTEGER DEFAULT NULL" /* place_id */ \
+    ", parent INTEGER" \
+    ", position INTEGER" \
+    ", title LONGVARCHAR" \
+    ", keyword_id INTEGER" \
+    ", folder_type TEXT" \
+    ", dateAdded INTEGER" \
+    ", lastModified INTEGER" \
+  ")" \
+)
+
+#define CREATE_MOZ_BOOKMARKS_ROOTS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_bookmarks_roots (" \
+    "  root_name VARCHAR(16) UNIQUE" \
+    ", folder_id INTEGER" \
+  ")" \
+)
+
+#define CREATE_MOZ_KEYWORDS NS_LITERAL_CSTRING( \
+  "CREATE TABLE moz_keywords (" \
+    "  id INTEGER PRIMARY KEY AUTOINCREMENT" \
+    ", keyword TEXT UNIQUE" \
+  ")" \
+)
+
+#endif // __nsPlacesTables_h__