Re: [PATCH][merge-tracking]Create mergeinfo.db with proper schema in case it does not exist.

On Tue, 02 Jan 2007, Malcolm Rowe wrote:

> On Thu, Dec 21, 2006 at 06:27:36PM +0530, Kamesh Jayachandran wrote:
> > Create mergeinfo.db with the necessary schema if the db does not exist with
> > one.
>
> It's annoying that SQLite doesn't give us any way to tell whether it's
> created a blank database for us, but I'd prefer that we use SQLite's
> built-in support for user schema version numbers rather than assume that
> a SQLITE_ERROR return means that we should create the schema afresh.
>
> That is, after connecting to the database, issue a
>
> PRAGMA user_version;
>
> command. If it returns zero, create the database schema and then issue
>
> PRAGME user_version = 1;
>
> to record that we've created the schema.
>
> I'm assuming that we'd tie the mergeinfo schema version to the
> repository format number, so we wouldn't actually use the user_version
> value as a proper format number, just a created/not-created indicator.

Good suggestion, Malcolm. How about this implementation?

I do think using some expected format number would be a good way to
go, so we could more gracefully handle schema changes in the future.

[[[
On the merge-tracking branch: When opening a connection to the merge
info database, use of sqlite's "user_version" PRAGMA to validate our
schema's version:

  http://www.sqlite.org/pragma.html#version

(Note that we do *not* use the "schema_version" PRAGMA, which is for
internal use by sqlite itself.)

* subversion/libsvn_fs_util/merge-info-sqlite-index.c
  (check_format): Validate schema version.
  (open_db): Create the schema if it didn't already exist.
  (svn_fs_merge_info__create_index): Remove explicit invocation of
   SVN_MTD_CREATE_SQL, which is now handled by open_db().
]]]

Index: subversion/libsvn_fs_util/merge-info-sqlite-index.c
===================================================================
--- subversion/libsvn_fs_util/merge-info-sqlite-index.c (revision 22878)
+++ subversion/libsvn_fs_util/merge-info-sqlite-index.c (working copy)
@@ -71,18 +71,41 @@
   return SVN_NO_ERROR;
 }
 
-/* Open a connection in *DB to the merge info database under
- REPOS_PATH. */
+/* Return SVN_ERR_FS_UNSUPPORTED_FORMAT if the schema version is
+ invalid, or SVN_ERR_FS_SQLITE_ERROR if an sqlite error occurs
+ during validation. */
 static svn_error_t *
-open_db(sqlite3 **db, const char *repos_path, apr_pool_t *pool)
+check_format(sqlite3 *db)
 {
- const char *db_path = svn_path_join(repos_path, SVN_FS_MERGE_INFO__DB_NAME,
- pool);
- SQLITE_ERR(sqlite3_open(db_path, db), *db);
-#ifdef SQLITE3_DEBUG
- sqlite3_trace(*db, sqlite_tracer, *db);
-#endif
- return SVN_NO_ERROR;
+ svn_error_t *err;
+ sqlite3_stmt *stmt;
+
+ SQLITE_ERR(sqlite3_prepare(db, "pragma user_version;", -1, &stmt, NULL), db);
+ if (sqlite3_step(stmt) == SQLITE_ROW)
+ {
+ /* Validate that the schema exists as expected... */
+ int schema_version = sqlite3_column_int(stmt, 0);
+ if (schema_version > 0)
+ {
+ /* ### TODO: ...and that the schema and repository versions match. */
+ err = SVN_NO_ERROR;
+ }
+ else
+ {
+ /* This is likely a freshly-created database in which the
+ merge tracking schema doesn't yet exist. */
+ err = svn_error_create(SVN_ERR_FS_UNSUPPORTED_FORMAT, NULL,
+ _("Merge Tracking schema version not set"));
+ }
+
+ SQLITE_ERR(sqlite3_finalize(stmt), db);
+ }
+ else
+ {
+ err = svn_error_create(SVN_ERR_FS_SQLITE_ERROR, NULL,
+ sqlite3_errmsg(db));
+ }
+ return err;
 }
 
 const char SVN_MTD_CREATE_SQL[] = "pragma auto_vacuum = 1;"
@@ -102,8 +125,38 @@
   "create index mi_c_path_idx on mergeinfo_changed (path);"
   APR_EOL_STR
   "create index mi_c_revision_idx on mergeinfo_changed (revision);"
+ APR_EOL_STR
+ "pragma user_version = 1;"
   APR_EOL_STR;
 
+/* Open a connection in *DB to the merge info database under
+ REPOS_PATH. Validate the merge tracking schema, creating it if it
+ doesn't yet exist. This provides both a migration path for pre-1.5
+ repositories, and handles merge info index initialization for 1.5+
+ repositories. */
+static svn_error_t *
+open_db(sqlite3 **db, const char *repos_path, apr_pool_t *pool)
+{
+ svn_error_t *err;
+ const char *db_path = svn_path_join(repos_path, SVN_FS_MERGE_INFO__DB_NAME,
+ pool);
+ SQLITE_ERR(sqlite3_open(db_path, db), *db);
+#ifdef SQLITE3_DEBUG
+ sqlite3_trace(*db, sqlite_tracer, *db);
+#endif
+
+ /* Validate the schema. */
+ err = check_format(*db);
+ if (err && err->apr_err == SVN_ERR_FS_UNSUPPORTED_FORMAT)
+ {
+ /* Assume that we've just created an empty merge info index by
+ way of sqlite3_open() (likely from accessing a pre-1.5
+ repository), and need to create the merge tracking schema. */
+ err = util_sqlite_exec(*db, SVN_MTD_CREATE_SQL, NULL, NULL);
+ }
+ return err;
+}
+
 /* Create an sqlite DB for our merge info index under PATH. Use POOL
    for temporary allocations. */
 svn_error_t *
@@ -111,7 +164,6 @@
 {
   sqlite3 *db;
   SVN_ERR(open_db(&db, path, pool));
- SVN_ERR(util_sqlite_exec(db, SVN_MTD_CREATE_SQL, NULL, NULL));
   SQLITE_ERR(sqlite3_close(db), db);
   return SVN_NO_ERROR;
 }