General DB lets you store and update your databases on your mobile device. You can import your CSV or Tab-delimited text files from the web or Dropbox, or share them from iTunes. You can also download entire SQLite databases, and execute SQL files.
Private DB is a similar app but designed for those who don't need to add, edit, or delete rows, or don't want their data consumers to. It is ideal for distributing large datasets in tabular form.
This guide covers General DB and Private DB and has screenshots for the iPhone version. The iPad version functions similarly but has been customized for its larger screen.
Thanks to David Edson and his gracious explanations and encouragement, General DB and Private DB now support the x-callback-url interface. To learn more about it and how to extract rows into another app, click here.
We also offer General DB for the Mac.
You can add a table by downloading or sharing a CSV or tab-delimited text file and then importing it in to a new table.
In General DB, you can also add a table by tapping on the Tables button and then tapping the + button. Enter a name for your table and define it by adding fields.
You can also create views (a type of table) by downloading and executing SQL files. Views are typically a subset of the fields of another table, or a join of multiple tables. When creating a view, be sure that the first field is either the special field named ROWID, or another field with unique values. Alternatively, you can include the ROWID for a table anywhere in the view.
Shown is the Table list with a toolbar with buttons that let you (from left to right): view information about the app, switch databases, download files, import or execute files, or delete the tables.
After adding your table and tapping on it, tap the Settings (gear) button to change its settings. You can change its name and the first 3 fields it is sorted on. When you select a field to sort on, an index is made on that field, which may take a little time after tapping the Done button depending on the size of your table. In General DB, you can also change the definition of the table after it is added. You cannot change the definition of a view after it is created. To change a view, change the SQL file that created it and execute the SQL again.
Tapping on a table will show you a list of its rows. You can scroll up or down by swiping up and down, or if your table exceeds 100 rows, you can tap the index column on the right.
To search for rows, tap the search bar and enter the string you want to search on for the currently selected field. To change the search field, tap the button in the search bar that looks like a filter. Searches are case-insensitive.
Some field types must be searched according to how they are actually stored in the database. To see how they are actually stored, tap the info button next to the row when viewing it. For example, dates have the internal storage form "2012-08-13 -0400".
To view the details of a particular row, tap on it from the table view. In General DB, you can edit the row or add a new row.
To edit a row, tap on it in the list of rows. Then you can swipe up and down to edit any particular field.
Some field types will have special buttons to make entering data easier. For example, the Date field type has a Today button that enters the current date.
Tapping the field row itself also can bring a special input method for some field types. Tapping on a Barcode field will bring up the camera from which you can focus on a barcode which will then be entered as the data.
The navigation buttons in the toolbar will let you go up, down, to the first row, or to the last row. You can also delete the row by tapping the trashcan button.
To email rows, first select some by tapping the checkbox button on the left side of the toolbar in the rows list. Checkboxes will then appear next to each row. Check the rows you want to act on, and then tap the action button. You can select all the rows, by tapping the ALL button.
You can also export the rows to a file, which will then appear in the Files as a text file, or you can delete the selected rows.
Selecting rows in a table or view requires the first field to have unique values. For tables (but not views), the first field is the ROWID and is not shown.
To import a CSV (comma-separated-value) or tab-delimited text file, tap on Tables and then tap the button in the toolbar that looks like a file folder. In Private DB, the file button is not shown unless you turn the Files Button switch on in the app settings. After tapping the Files button, you will see a list of files that you either previously downloaded from the internet, or shared to the app via iTunes. Any file with an extension of .txt, .csv, or .sql is shown. Tap on one of the .txt or .csv files and you will see the screen below.
Tap on the File line to preview the first few lines of the file. Tap on the Table line to select a different table to import the file in to. Tap on the Type line to change the type of the file from Tab-delimited text to CSV. If the first line of your file is the field names, tap on 1st line to change it so. You can also set it to be the field types, or data, or you can discard that line. Set the 2nd line accordingly.
You can download .sql files in to General DB and execute them. To do so, tap the Files button, then tap the .sql file you want to execute, and then tap the Execute button. If it is a simple SELECT statement, you will see a popup with its results. You can create a view on another table, or a join of two tables. Be sure to make the first field of the view be one with unique values, such as the ROWID, so that you can select rows in the table view. Some examples:
CREATE VIEW customer_names AS
SELECT ROWID, last_name, first_name
ORDER BY last_name, first_name
CREATE VIEW customer_orders AS
SELECT o.ROWID, o.date_ordered, c.last_name, c.first_name
FROM orders o, customers c
WHERE o.customer_id = c.ROWID
ORDER BY date_ordered desc
For more on views, click here see the SQLite page on it.
SQL is a powerful, widely-used query language that lets you search, sort, update, join, and do just about anything on tables and their rows. Click here for the SQL as understood by SQLite, which is the database used in this app. For more details on SQL, please pick up a book!
You can either download and execute a SQL file (described above), or select a table and then tap the lightning bolt button, then tap the + button in the lower left corner. Enter the query you want to execute. You can tap the templates button to create a sample query, and then change the field and the value. If your table name or field contains spaces, you must use double quotes around them. Tap the Go button to execute the SQL.
After executing a SQL query, it will be remembered in the History list, where it can be executed again by tapping on it:
Frequently executed queries can be added to your Favorites. Only the title of the favorite appears in the Favorites list, and the Favorites are shown first when the lightning bolt is tapped. So a user doesn't have to know any SQL to execute a favorite. This is handy if you want to preload your database with common queries, then tell your database user to tap the lightning bolt button and then tap the selected favorite.
After executing a SQL SELECT query, the lightning bolt button will change color to orange, indicating the query is active. To inactivate the query, tap the lightning bolt button again.