{"id":157,"date":"2014-09-15T10:47:00","date_gmt":"2014-09-15T17:47:00","guid":{"rendered":"https:\/\/simpkins.social\/cliff\/?p=157"},"modified":"2025-03-04T10:50:09","modified_gmt":"2025-03-04T18:50:09","slug":"sqlite-on-wp-db-schema-management","status":"publish","type":"post","link":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/","title":{"rendered":"SQLite on WP \u2013 DB schema management"},"content":{"rendered":"\n

As I started working with SQLite as a place to store my data, I began thinking about my app\u2019s changing data scheme and I wondered: \u2018how do I handle change management in my local database?\u2019 This blog entry outlines my approach to solving this issue, which basically boils down to tracking the data scheme version in the database and doing a wholesale drop and recreate when the schema changes.<\/p>\n\n\n\n

Why drop and recreate?<\/h3>\n\n\n\n

For my app, I use SQLite for data caching of server-side information \u2013 providing performance and offline capabilities for the user. Prior to using SQLite, I had been using in-memory serialization to store the object collections in isolated storage, which had a number of performance issues (memory and CPU).<\/p>\n\n\n\n

For non-cached user information (account information, preferences, etc.), I don\u2019t use SQLite \u2013 I store those items in the isolated storage property bag. As I take my app to the Windows Runtime platform, I\u2019ll bring this information into the roaming data area (for preference information) and credentials locker (for account information).<\/p>\n\n\n\n

For the cached app data, I\u2019m now storing this as a SQLite database. My issue is that each version sees continued improvement around the richness of the data (adding review information from a variety of sites, news entries, etc.) \u2013 which means scheme modifications. Many of the websites describing using SQLite talk about how to create the database, but leave it to the user to figure out database updates. I played around DDL tracking with complicated UPDATE scripts and decided this was taking way too long for basically volatile cache data\u2026and I went with a simpler approach \u2013 ship the app with a current snapshot of the full cache (~7mb) and simply do a drop and recreate each time the schema changes.<\/p>\n\n\n\n

Tracking Database schema state<\/h3>\n\n\n\n

To track the state of the database, I created a class called DBMetadata that I can use for a variety of things in my app, including the schema version (what we\u2019re explaining here), and things like the last time I retrieved a particular dataset (to help balance data pulls that don\u2019t need to be run every time the app runs). The class looks like the following:<\/p>\n\n\n\n

[Table(\"Metadata\")]\npublic class DbMetadata {\n \n  [PrimaryKey, AutoIncrement]\n  public int Id { get; set; }\n \n  public string SettingName { get; set; }\n \n  public int SettingValueMajor { get; set; }\n \n  public int SettingValueMinor { get; set; }\n \n  public string SettingInt { get; set; }\n \n  public string SettingString { get; set; }\n \n  public DateTime SettingDateTime { get; set; }\n \n}<\/code><\/pre>\n\n\n\n
To track the database schema version, I use constants in my LocalData class like the following:<\/p>\n\n\n\n
const string DBMETA_SCHEMA_VERSION_NAME = \"DbSchemaVersion\";\nconst int DBMETA_SCHEMA_VERSION_MAJOR = 1;\nconst int DBMETA_SCHEMA_VERSION_MINOR = 2;<\/code><\/pre>\n\n\n\n
With this code in place, I have the following two methods in my LocalData class to interact and check if the database is running the most current:<\/p>\n\n\n\n
private async static Task<bool> ValidateIsLatestDbVersion() {\n \n  try {\n \n    int _table = await LocalData.DbConnection.ExecuteScalarAsync<Int32>(\"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='Metadata';\");\n \n    if (_table == 0) return false;\n \n    if ((await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).CountAsync()) > 0) {\n      var _ret = await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).FirstAsync();\n \n      if (_ret != null && _ret.SettingValueMajor > DBMETA_SCHEMA_VERSION_MAJOR) {\n        return true;\n      } else if (_ret.SettingValueMajor == DBMETA_SCHEMA_VERSION_MAJOR && _ret.SettingValueMinor >= DBMETA_SCHEMA_VERSION_MINOR) {\n        return true;\n      }\n    }\n  } catch (Exception _e) {\n  }\n \n  return false;\n}\n \n \nprivate async static Task<bool> MarkDbVersion() {\n \n  try {\n    if ((await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).CountAsync()) > 0) {\n      var _ret = await LocalData.DbConnection.Table<DbMetadata>().Where(t => t.SettingName == DBMETA_SCHEMA_VERSION_NAME).FirstAsync();\n      _ret.SettingValueMajor = DBMETA_SCHEMA_VERSION_MAJOR;\n      _ret.SettingValueMinor = DBMETA_SCHEMA_VERSION_MINOR;\n      await LocalData.DbConnection.UpdateAsync(_ret);\n    } else {\n      await LocalData.DbConnection.InsertAsync(new DbMetadata() { SettingName = DBMETA_SCHEMA_VERSION_NAME, SettingValueMajor = DBMETA_SCHEMA_VERSION_MAJOR, SettingValueMinor = DBMETA_SCHEMA_VERSION_MINOR });\n    }\n    return true;\n  } catch (Exception _e) {\n    return false;\n  }\n}<\/code><\/pre>\n\n\n\n
With the above in place, it\u2019s pretty easy to now handle the check and drop, as needed.<\/p>\n\n\n\n
Open\/Create\/Delete<\/h3>\n\n\n\n
So now with the plumbing in place, it\u2019s time to put in place the business logic that does the database creation. I do this when I open the database for the first time in my Windows Phone app.<\/p>\n\n\n\n
private async static Task OpenLegoDB() {\n \n  string DBPATH = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, DBNAME_LEGO_SETS);\n  try {\n    await ApplicationData.Current.LocalFolder.GetFileAsync(DBNAME_LEGO_SETS);\n    _legoDbConnection = new SQLiteAsyncConnection(DBPATH);\n \n    \/\/Check the schema version, and replace the database if it's an old one\n    if (!await ValidateIsLatestDbVersion()) {\n      _legoDbConnection = null;\n      await LocalData.DeleteLegoDB();\n    }\n \n  } catch (FileNotFoundException) {\n  } catch (SQLiteException) {\n    _legoDbConnection = null;\n  }\n  if (_legoDbConnection == null) {\n    await LocalData.CreateLegoDB();\n    _legoDbConnection = new SQLiteAsyncConnection(DBPATH);\n  }\n}\n \n \nprivate async static Task DeleteLegoDB() {\n  string DBPATH = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, DBNAME_LEGO_SETS);\n  try {\n    var _file = await ApplicationData.Current.LocalFolder.GetFileAsync(DBNAME_LEGO_SETS);\n \n    SQLite.SQLiteConnectionPool.Shared.Reset();\n    await _file.DeleteAsync();\n  } catch (FileNotFoundException) {\n  }\n \n}\n \n \nprivate async static Task CreateLegoDB() {\n  try {\n \n    string DBPATH = System.IO.Path.Combine(ApplicationData.Current.LocalFolder.Path, DBNAME_LEGO_SETS);\n \n#if DEBUG\n    _legoDbConnection = new SQLiteAsyncConnection(DBPATH);\n    await _legoDbConnection.CreateTableAsync<LegoTheme>();\n    await _legoDbConnection.CreateTableAsync<LegoSubtheme>();\n    await _legoDbConnection.CreateTableAsync<LegoSet>();\n \n    await _legoDbConnection.CreateTableAsync<DbMetadata>();\n    await LocalData.MarkDbVersion();\n    return;\n#endif\n \n \n    StorageFile dbFile = null;\n    try {\n      dbFile = await StorageFile.GetFileFromPathAsync(DBPATH);\n    } catch (FileNotFoundException) {\n      if (dbFile == null) {\n        \/\/ Copy file from installation folder to local folder.\n        IsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();\n \n        \/\/ Create a stream for the file in the installation folder.\n        using (Stream input = System.Windows.Application.GetResourceStream(new Uri(\"Assets\\\\LegoSets.Snapshot.sqlite\", UriKind.Relative)).Stream) {\n          \/\/ Create a stream for the new file in the local folder.\n          using (IsolatedStorageFileStream output = iso.CreateFile(DBPATH)) {\n            \/\/ Initialize the buffer.\n            byte[] readBuffer = new byte[4096];\n            int bytesRead = -1;\n \n            \/\/ Copy the file from the installation folder to the local folder.\n            while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0) {\n              output.Write(readBuffer, 0, bytesRead);\n            }\n          }\n        }\n      }\n    }\n \n  } catch (Exception _e) {\n    return;\n  }\n}<\/code><\/pre>\n\n\n\n
There\u2019s a bunch of cool stuff happening in the code above (the delete step alone took me a few hours on StackOverflow to get right). But the basic logic here is as follows:<\/p>\n\n\n\n
    \n
  1. Open the database file<\/li>\n\n\n\n
  2. If the file is there, check the version number\n
      \n
    1. Validate that the settings table exists (that we have a version number to check\u00a0\"Smile\")<\/li>\n\n\n\n
    2. If the database is the same or newer version, then all is good!<\/li>\n\n\n\n
    3. If the database has an old version, delete the file and clear the variable<\/li>\n<\/ol>\n<\/li>\n\n\n\n
    4. If we have an open database file, proceed; if we don\u2019t, then create a new one\n
        \n
      1. If we\u2019re running in DEBUG, then create the file and the tables from scratch<\/li>\n\n\n\n
      2. If we\u2019re not running in DEBUG, then use the snapshot that shipped with the app<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n
        Note that the above applies to an app written for Windows Phone Silverlight. When I do bring this over to the Windows Runtime platform (targeting Windows client, as well as phone), I will likely add some #IF checks to handle where the files are located and the like. But that\u2019s for a future consideration (I have a tablet version of my code, but put it on hold while I get the SQLite and Amazon integration done).<\/p>\n\n\n\n
        Hope that helps<\/h3>\n\n\n\n
        That about does it for how I manage my SQLite database in my app; I hope this helps!<\/p>\n\n\n\n
        There are a variety of other things that I\u2019ve adopted over time based on this strategy that may be worth noting (e.g., breaking my SQLite DB into a few different databases to enable part of the cache to be cleared without clearing all of a user\u2019s past traffic). Also, I\u2019m in the process of wiring this up with the Bing Translator, which also warrants a blog post in the coming months.<\/p>\n","protected":false},"excerpt":{"rendered":"
        As I started working with SQLite as a place to store my data, I began thinking about my app\u2019s changing data scheme and I wondered: \u2018how do I handle change…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[8],"tags":[],"class_list":["post-157","post","type-post","status-publish","format-standard","hentry","category-windows-phone"],"yoast_head":"\nSQLite on WP \u2013 DB schema management - Cliff Simpkins<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLite on WP \u2013 DB schema management - Cliff Simpkins\" \/>\n<meta property=\"og:description\" content=\"As I started working with SQLite as a place to store my data, I began thinking about my app\u2019s changing data scheme and I wondered: \u2018how do I handle change…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/\" \/>\n<meta property=\"og:site_name\" content=\"Cliff Simpkins\" \/>\n<meta property=\"article:published_time\" content=\"2014-09-15T17:47:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-04T18:50:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/web.archive.org\/web\/20170531043239im_\/http:\/\/www.monkeyslaps.com\/wp-content\/uploads\/2014\/09\/wlEmoticon-smile1.png\" \/>\n<meta name=\"author\" content=\"cliff.simpkins\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"cliff.simpkins\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/\"},\"author\":{\"name\":\"cliff.simpkins\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/#\\\/schema\\\/person\\\/bba546ac4d77da1fb51bc4030238e864\"},\"headline\":\"SQLite on WP \u2013 DB schema management\",\"datePublished\":\"2014-09-15T17:47:00+00:00\",\"dateModified\":\"2025-03-04T18:50:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/\"},\"wordCount\":786,\"image\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/web.archive.org\\\/web\\\/20170531043239im_\\\/http:\\\/\\\/www.monkeyslaps.com\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/wlEmoticon-smile1.png\",\"articleSection\":[\"Windows Phone\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/\",\"url\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/\",\"name\":\"SQLite on WP \u2013 DB schema management - Cliff Simpkins\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/web.archive.org\\\/web\\\/20170531043239im_\\\/http:\\\/\\\/www.monkeyslaps.com\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/wlEmoticon-smile1.png\",\"datePublished\":\"2014-09-15T17:47:00+00:00\",\"dateModified\":\"2025-03-04T18:50:09+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/#\\\/schema\\\/person\\\/bba546ac4d77da1fb51bc4030238e864\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#primaryimage\",\"url\":\"https:\\\/\\\/web.archive.org\\\/web\\\/20170531043239im_\\\/http:\\\/\\\/www.monkeyslaps.com\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/wlEmoticon-smile1.png\",\"contentUrl\":\"https:\\\/\\\/web.archive.org\\\/web\\\/20170531043239im_\\\/http:\\\/\\\/www.monkeyslaps.com\\\/wp-content\\\/uploads\\\/2014\\\/09\\\/wlEmoticon-smile1.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/2014\\\/09\\\/15\\\/sqlite-on-wp-db-schema-management\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLite on WP \u2013 DB schema management\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/#website\",\"url\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/\",\"name\":\"Cliff Simpkins\",\"description\":\"Personal site + blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/#\\\/schema\\\/person\\\/bba546ac4d77da1fb51bc4030238e864\",\"name\":\"cliff.simpkins\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/07b113647e819a448cfc8545b476db50a641b9d43808a2c399af84e724d078d2?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/07b113647e819a448cfc8545b476db50a641b9d43808a2c399af84e724d078d2?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/07b113647e819a448cfc8545b476db50a641b9d43808a2c399af84e724d078d2?s=96&d=mm&r=g\",\"caption\":\"cliff.simpkins\"},\"url\":\"https:\\\/\\\/simpkins.social\\\/cliff\\\/blog\\\/author\\\/cliff-simpkins\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLite on WP \u2013 DB schema management - Cliff Simpkins","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/","og_locale":"en_US","og_type":"article","og_title":"SQLite on WP \u2013 DB schema management - Cliff Simpkins","og_description":"As I started working with SQLite as a place to store my data, I began thinking about my app\u2019s changing data scheme and I wondered: \u2018how do I handle change…","og_url":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/","og_site_name":"Cliff Simpkins","article_published_time":"2014-09-15T17:47:00+00:00","article_modified_time":"2025-03-04T18:50:09+00:00","og_image":[{"url":"https:\/\/web.archive.org\/web\/20170531043239im_\/http:\/\/www.monkeyslaps.com\/wp-content\/uploads\/2014\/09\/wlEmoticon-smile1.png","type":"","width":"","height":""}],"author":"cliff.simpkins","twitter_card":"summary_large_image","twitter_misc":{"Written by":"cliff.simpkins","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#article","isPartOf":{"@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/"},"author":{"name":"cliff.simpkins","@id":"https:\/\/simpkins.social\/cliff\/#\/schema\/person\/bba546ac4d77da1fb51bc4030238e864"},"headline":"SQLite on WP \u2013 DB schema management","datePublished":"2014-09-15T17:47:00+00:00","dateModified":"2025-03-04T18:50:09+00:00","mainEntityOfPage":{"@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/"},"wordCount":786,"image":{"@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#primaryimage"},"thumbnailUrl":"https:\/\/web.archive.org\/web\/20170531043239im_\/http:\/\/www.monkeyslaps.com\/wp-content\/uploads\/2014\/09\/wlEmoticon-smile1.png","articleSection":["Windows Phone"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/","url":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/","name":"SQLite on WP \u2013 DB schema management - Cliff Simpkins","isPartOf":{"@id":"https:\/\/simpkins.social\/cliff\/#website"},"primaryImageOfPage":{"@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#primaryimage"},"image":{"@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#primaryimage"},"thumbnailUrl":"https:\/\/web.archive.org\/web\/20170531043239im_\/http:\/\/www.monkeyslaps.com\/wp-content\/uploads\/2014\/09\/wlEmoticon-smile1.png","datePublished":"2014-09-15T17:47:00+00:00","dateModified":"2025-03-04T18:50:09+00:00","author":{"@id":"https:\/\/simpkins.social\/cliff\/#\/schema\/person\/bba546ac4d77da1fb51bc4030238e864"},"breadcrumb":{"@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#primaryimage","url":"https:\/\/web.archive.org\/web\/20170531043239im_\/http:\/\/www.monkeyslaps.com\/wp-content\/uploads\/2014\/09\/wlEmoticon-smile1.png","contentUrl":"https:\/\/web.archive.org\/web\/20170531043239im_\/http:\/\/www.monkeyslaps.com\/wp-content\/uploads\/2014\/09\/wlEmoticon-smile1.png"},{"@type":"BreadcrumbList","@id":"https:\/\/simpkins.social\/cliff\/blog\/2014\/09\/15\/sqlite-on-wp-db-schema-management\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/simpkins.social\/cliff\/"},{"@type":"ListItem","position":2,"name":"SQLite on WP \u2013 DB schema management"}]},{"@type":"WebSite","@id":"https:\/\/simpkins.social\/cliff\/#website","url":"https:\/\/simpkins.social\/cliff\/","name":"Cliff Simpkins","description":"Personal site + blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/simpkins.social\/cliff\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/simpkins.social\/cliff\/#\/schema\/person\/bba546ac4d77da1fb51bc4030238e864","name":"cliff.simpkins","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/07b113647e819a448cfc8545b476db50a641b9d43808a2c399af84e724d078d2?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/07b113647e819a448cfc8545b476db50a641b9d43808a2c399af84e724d078d2?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/07b113647e819a448cfc8545b476db50a641b9d43808a2c399af84e724d078d2?s=96&d=mm&r=g","caption":"cliff.simpkins"},"url":"https:\/\/simpkins.social\/cliff\/blog\/author\/cliff-simpkins\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/posts\/157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/comments?post=157"}],"version-history":[{"count":1,"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/posts\/157\/revisions"}],"predecessor-version":[{"id":158,"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/posts\/157\/revisions\/158"}],"wp:attachment":[{"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/media?parent=157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/categories?post=157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/simpkins.social\/cliff\/wp-json\/wp\/v2\/tags?post=157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}