Retrieve Your cordova app’s localStorage Data Directly From iOS Device

Use Case

If WiFi fails, your ajax call fails and your backup re-submit code fails, you may need a last ditch solution of physically grabbing your form data from inside your PhoneGap app, off of an individual iOS device.

Prerequisites

Make sure the app is coded to save data locally using HTML5 localStorage API.

Once the localStorage functionality is tested and working, and you have your PhoneGamp / cordova app running on an iPad, you can use PhoneView to access the file system on your the iPad.

Install DB Browser for SQLite on your Mac. Under the hood, cordova saves your localStorage data as a simple SQLite database.

Retrieve the Data

  1. Connect your device to your Mac and open PhoneView Demo
  2. Go to Apps in the main nav on the left.
  3. Click on Settings in the top nav icons bar and you’ll see this dialog window:
    sc1
  4. Check the “Show Entire Disk in Disk Mode” and “Show All Apps in Apps Mode (Developers Only)” boxes. You’ll get a warning confirmation popup, which you should accept by clicking OK.
  5. You’ll now see your custom cordova app listed under Apps:
    screen-shot-2016-10-25-at-4-10-00-pm

  6. The folders you want is yourApp/Library/Caches/ and yourApp/Documents/Backups/. The file should be called something like “file__0.localstorage” in /Library/Caches or “localstorage.appdata.db” in /Documents/Backups. It’s essentially a SQLite file, and can be opened using an app that lets you view SQLite files.
  7. To open it, you need to first copy the Library folder to your Mac.
  8. Click the Library folder to to select it
  9. Click on Copy from iPhone button in the top icons navbar
  10. Choose a location to save it on your machine.
  11. Launch DB Browser for SQLite
  12. Open your recently copied “file__0.localstorage” or “localstorage.appdata.db” files from your Mac in DB Browser for SQLite
  13. Click on the Browse Data tab towards the top of the interface
  14. You should see your localStorage Key / Value pairs listed as a SQL table:
  15. If you stored your Values as Arrays, your actual data may be stored as a BLOB data type and not plain text
  16. Select your BLOB value in the table and look at the right side of the interface, under Edit Database Cell
  17. Make sure the Mode is set to “Binary”
  18. You should be able to see your data with very bad kerning
  19. Click the “Export” button above the cell content area
  20. Save your file as .txt and open the .txt in any text editor, like SublimeText, to access your data as plain text.

Titanium SDK: Selecting Data from Multiple Tables in SQLite with JOIN

Assumptions

For this example, I’m assuming we get our user_ID populated from a web service when the app starts.

Create your tables

There’re 3 tables, one for game text info, one for game icon graphics and one that stores a list of my favorite games.

 
CREATE TABLE MyGames (
      game_ID INTEGER PRIMARY KEY,
      game_name TEXT,
      num_players INTEGER,
      game_rules TEXT
);   

CREATE TABLE MyGameIcons (
      icon_ID INTEGER PRIMARY KEY,
      game_ID INTEGER,
      icon_ImgPth TEXT,
      detail_ImgPth TEXT
); 

CREATE TABLE MyFaveGames (
	   fave_ID INTEGER PRIMARY KEY,
	   game_ID INTEGER, 
	   user_ID INTEGER 
	)

Add some dummy data to them using SQLite Manager plugin for Firefox or a command line tool. Once you have some data, you can move on to trying out the JOIN clause.

Use a basic INSERT to save a game to the MyFaveGames table:

 
INSERT INTO MyFaveGames(user_ID, game_ID) VALUES(1823, 9)

To use this statement in Titanium SDK 2.x app, you can do this:

 
db.execute( 'INSERT INTO MyFaveGames(user_ID, game_ID) VALUES(?, ?)', varUserID, varGameID );

Assuming varUserID and varGameID are pre-populated in your app and db is defined earlier as your database connection object ( db = Ti.Database.open(myConn); )

Use SQL’s implicit JOIN clause to pull records from more than one table using one query

Pull the game info & icon graphics for games saved to My Favorite Games. Order them by the game names.

 
SELECT MyGames.game_ID, game_name, num_players, game_rules, icon_ImgPth, detail_ImgPth FROM MyGames, MyGameIcons, MyFaveGames WHERE MyFaveGames.game_ID=MyGames.game_ID AND MyFaveGames.game_ID=MyGameIcons.game_ID ORDER BY game_name

In Titanium SDK 2.x app, it can look like this:

 
db.execute('SELECT MyGames.game_ID, game_name, num_players, game_rules, icon_ImgPth, detail_ImgPth FROM MyGames, MyGameIcons, MyFaveGames WHERE MyFaveGames.game_ID=MyGames.game_ID AND MyFaveGames.game_ID=MyGameIcons.game_ID ORDER BY game_name');

The above would return a recordset, which you can loop through.

Creating a Temporary Table for Immediate Use

 
		var myDBconn = getDB();

		var temp = myDBconn.execute('CREATE TEMP VIEW MyTempTabl AS SELECT fave_ID, game_ID, user_ID FROM MyFaveGames WHERE user_ID=' + varUserID);
		var rows = myDBconn.execute('SELECT MyGames.game_ID, game_name, num_players, equip_items, fld_type, classics, fancies, game_instructns, fld_Icon, equip_Icon, composit_Icon, fave_ID, user_ID FROM MyGames LEFT OUTER JOIN MyGameIcons on MyGames.game_ID = MyGameIcons.game_ID LEFT OUTER JOIN MyTempTabl ON MyGames.game_ID = MyTempTabl.game_ID WHERE classics = "TRUE" ORDER BY game_name'); 

SQLite3: CREATE Table w/ DATE data type, INSERT Rows Using date() Function

Let’s say our UI gives the user a choice between 4 options. They can select option 1, 2, 3 or 4.

You can grab the SQLite Manager Add On for Firefox, use sqlite3 via command line or use commercial software.

The userID can be obtained locally, or in a real life situation, most likely populated from a web service after you log in. The details of how to populate userID are beyond the scope of this note, but are easily searchable online.

Create your TABLE

It’ll consist of these 4 fields:

  • Options ID INTEGER primary key
  • User ID INTEGER
  • Entry Date INTEGER – stored in YYYY-MM-DD format
  • Choice Type: 1, 2, 3, 4 (1 = option 1, etc) INTEGER
 
CREATE TABLE Choices (
      options_ID INTEGER PRIMARY KEY,
      choice_type INTEGER,
      user_ID INTEGER,
      entry_Date DATE DEFAULT CURRENT_DATE
   );   

The entry_Date field gets auto populated with today’s date if you fail to provide a value in the INSERT statement.

Save today’s choice

Sample data includes ‘4’ for the chosen option and ‘1823’ as a mock userID. Today’s date is inserted using SQLite’s built-in date() function.

    
INSERT INTO Choices(choice_type, user_ID, entry_Date) VALUES( 4, 1823, date('now','localtime'))

Save yesterday’s choice

Yesterday’s date is grabbed using SQLite’s built-in date() function plus the NNN days modifier.

    
INSERT INTO Choices(choice_type, user_ID, entry_Date) VALUES( 1, 1823, date('now','localtime', '-1 days')) 

Get yesterday’s choice

Get the option chosen for YESTERDAY’s date for user ID ‘1823’

    
SELECT choice_type FROM Choices WHERE Choices.user_ID=1823 AND Choices.entry_Date=date('now','localtime', '-1 days')