Overview

General DB lets you easily enter data into a full-fledged SQL database. It's great for data distribution. The tabular format makes your data searchable, sortable, and selectable.

General DB is built on the SQLite database, which is a full-featured SQL implementation that supports tables, indexes, triggers, views, ACID transactions, JOINs, subqueries, and much more.

General DB enhances SQLite so that it is easier to enter data. It does this by adding a few system tables that store the format of a column (referred to as a "field" in General DB). The field formats allowed are shown below.

• Text
• Number
• Date
• Time
• Duration
• Location
• Photo
• Barcode
• Picker (defined by the user)
• Picker Table (defined by the user)
• Lookup Table (defined by the user)

These field formats make adding and editing rows much easier, yet the database is still compatible with SQLite and does not require any extension library to be installed.

Some field formats, such as Number, Date, and Time, can be styled as well. For example, the Number field format can be styled as an accounting number, which gives it a prefix of $ in the United States. Other format styles are available when adding or changing a table or view.

After you create your databases, you can distribute them to iOS devices that have the General DB or Private DB apps via email, iTunes, Dropbox, or a website.

For more information about this app, or for questions or comments, please visit our website.

Opening a Database

When you first start General DB, the default database general.db is opened. This database is simply a file located in the app's container.

To open a different database in the current window, click on the Database menu, then Open Database. You can choose from other databases in this app's container. You can select a recently opened database as well using the Open Recent menu, but it will be opened in a new window.

To open a database located outside this app's container in the current window, click on the Database menu, then Open External Database.

NOTE: if you open a database that is outside this app's container, then memory-based journaling mode must be used. This is due to security restrictions in OS X for sandboxed apps. Memory-based journaling affects the way INSERT and UPDATE transactions are performed. If you lose computer power during a transaction to a database located outside this app's container, it can cause database corruption. To be safe, make a copy of the database beforehand, or drag and drop the database file from the Finder in to the app container. The app container can be opened in the Finder by clicking on Open Database, and then pressing the Show in Finder button.

To open a database in a new window, click on the Database menu, then New Window. This is useful for adding rows or executing SQL in a separate window. To refresh the tables and rows in a database window, select the window and press Shift-Command-R.

To create a new database in the current window, click on the Database menu, then Open Database, and press the + button below the list of databases. Enter a filename of your new database and press the return key on the keyboard. Then press the Open button and the new databases will be selected in the current window. To create a new database in a new window, click on the Database menu, then New Window, and do the same thing.

Databases in this app must have either a .db or .sqlite extension to be shown in the list. To delete a database, select one or more and press the - button. You can rename a database file by selecting it and pressing the Return key on your keyboard.

You can open up to 5 databases in different windows at once. You can also open the same database in different windows, which lets you use the SQL Console in one and the Rows Console in another, for example.

To copy the current database to another folder, click the Share menu, then Copy to Folder. Using the Share menu, you can also email the database as an attachment, or upload it to Dropbox. To upload or download from Dropbox, you must have a Dropbox account.

Adding and Changing Tables

When you first open General DB, you will see the Tables listed on the left side, and the Rows listed on the right.

To add a table, click on the + button below the Tables List. A menu will appear.

Click Create New Table to open a window that lets you create a table by adding fields.

Click Add Blank Table from the menu to add a small table with 10 fields and 100 rows which you can edit. Change the name of the table by selecting the table in the Tables List and pressing the Return key on your keyboard. You can also double-click the table to change its name or fields.

You can change a table definition later if you need to. Click the gear button and then Name and Fields from the menu. The field names can be changed by clicking on a field name and then pressing the Enter key. Field formats can be changed by clicking the button next to the field name. The order of the fields can be changed by dragging and dropping the field to the desired place.

NOTE: If you open your database in another app and create a table, or create a table manually in this app using SQL, do not change it in this window or else its definition will be changed.

The table schema shows you the SQL used to create the table and the indices it has. To show the schema window, click the gear button and then Schema. In the window it is read-only, but you can copy and paste it into the SQL Console.

After adding a table and selecting it, you can add rows to it.

Adding, Editing and Deleting Rows

When you select a table from the list on the left, its rows will be shown in the Rows Console on the right.

Use the up and down arrow keys on the keyboard to move up and down through the rows. A vertical scroller will appear to the right of the rows if there are more rows than will fit in the window. By clicking above or below the scroller, you can navigate through the table rows.

Use the left and right arrow keys on the keyboard to move left and right through the fields. A horizontal scroller will appear to the bottom of the rows if there are more fields (columns) than will fit in the window. By clicking to the left or right of the scroller, you can navigate through the fields. You can also set the first displayed field in the window by clicking the > button to the right of the field names and clicking a field from the menu.

To move up or down a page at a time, hold down the option key while pressing the up or down arrow keys. To go to the first or last row, hold down the command key while pressing the up or down arrow keys.

To move left or right by a page of fields at a time, hold down the option key while pressing the left or right arrow keys. To go to the first or last field, hold down the command key while pressing the left or right arrow keys.

To edit a value in a row, navigate to the desired row in the Rows Console and press the Return key. Make your desired changes, and then press the Return key again to save them to the database. If you want to discard the changes, press the Escape key or simply move off the cell using either Tab, Shift-Tab, or the arrow keys. You can also double-click on the desired cell to start editing it.

To edit a value in a row in a separate window, Command-double-click the cell to show the Edit Field panel. This panel can remain up while navigating the Rows Console, and lets you see multiple lines of a value, and has controls specific to the field format for entering data.

To change a value in the selected cells to the NULL value, click the Edit menu and then Set to NULL. The NULL value is used by some databases to indicate a special value, or the lack of a value. It is an optional feature. The table definition must allow NULL values in the field for them to be added or saved.

To add rows, click on the Add Row button. The Add Row Console will then appear. Enter your data. Press the tab key or shift-tab keys to navigate between the fields using the keyboard. Then press the Add button to insert the row into the database. The new row may or may not be visible in the Rows Console, depending on which rows are shown and searching and sorting criteria. If you have a SQL query active you may not see the new row as well.

To edit a row in the Add Row Console, navigate the selection to the desired row in the Rows Console, and hold down the option key while pressing the Return key. You can also hold down the option key and double-click on a field in the row. The Add Row Console will then appear. Make your desired changes and press the Save button to update the row in the database. You can return to the Rows Console by either clicking the Rows tab, or by pressing the Escape key on your keyboard twice. If you want to undo the changes before saving, press the curled-arrow button to revert the changes.

To delete an individual row, press the Delete button in the Add Row Console.

Clicking the circled i button at the end of each field will bring up a window which lets you enter data more easily based on the field's format.

For pickers and lookup tables, the values you can select are conveniently presented in a drop down list. You can also enter in a value using the keyboard, if it is not in the list.

Photo fields let you select a photo from a folder outside the app that will be copied in, or a photo that is already in the app by clicking the button with the thumbnail on it. Date and Time fields have visual calendars that you can select values from. For any field, you can also simply enter the desired value in to it and press the Save button. The button to the left of the Save button reverts the field to the value currently in the database.

When editing a row, the dates and times are unformatted, which is how they are actually stored in the database.

The keyboard shortcuts are shown in the Keyboard Shortcuts section of this document.

To insert a NULL value in a field, use the SQL Console and issue an UPDATE statement on the field.

Table rows are only editable in the Rows Console and Add Row Console if the table has a built-in ROWID and does not have a user-defined column named ROWID. This covers any table created using General DB, as well as almost all other tables created manually using SQL or other means. Typically, only very advanced users create tables that do not have a built-in ROWID field.

Copying and Pasting Data

General DB lets you copy and paste data by groups of cells or entire rows. You can also copy data from another app and paste it into a table.

To copy and paste a group of cells in to another table or app, select the desired cells using the mouse, and press Command-C. You can select multiple cells by holding down the Shift key while clicking with the mouse. You can also use the Command key and click on cells with the mouse to select cells that are not adjacent. To paste the copied cells over other cells in a table, position the focus to the desired starting cell, and press Command-V. You can also open another database and paste the cells in to it.

If you paste cells from another app, be sure to match the field separator and character encoding with what is shown in the Import Console.

If you are copying cells to another app, the separator for the fields in the row can be changed in the Export Console.

To add rows to your selected table from the data on the Clipboard, click the Edit menu, then Paste by Adding Rows. The rows will be added using SQL INSERT statements. Where the rows appear in the Rows Console depends on whether you have a search or SELECT query active and if the fields are sorted.

If the table you are pasting in to has fewer columns than what is on the Clipboard, then the excess columns will be removed from the end of each row on the Clipboard.

If the table you are pasting in to has more columns that what is on the Clipboard, then blank columns will be added to the end of each row.

To copy and paste entire rows in to another table or app, select the desired rows by clicking on the # column on the far left, then press Command-C on the keyboard. You can select multiple rows by holding down the Shift key while clicking on the # columns. You can also hold down the Command key and click on rows with the mouse to select rows that are not adjacent.

Up to 1,000 rows can be copied and pasted to another app at once.

To deselect the rows, click on one of them, then click again so that it is not selected.

You can also clear or replace the data in groups of cells by first selecting the cells and then clicking the Edit menu, and then Clear or Replace.

To deselect cells, click on one.

You can also drag and drop entire tables to another database, or even the same database, by opening another database window, selecting the desired tables, and dragging and dropping them in to the destination window. You will be prompted with a confirmation window. Check the Include Indexes box if you want the same indexes created on the new table. Check the Copy Rows box if you want to copy all the rows from the original table.

Searching and Sorting

To search for rows in the selected table, click the gear button under the Tables List and then click Search from the menu. The Search window will appear.

Select the field you would like to search on, enter the string to search for, and click the Search button. For dates and times, the search string must be unformatted. To see unformatted date or time fields, edit the row. For example, an unformatted Date would be stored in the form 2012-08-13 -0400.

Searches are case-insensitive and match any occurrence of the string in the field.

You can sort the rows in the main window by selecting from the Sort on and Then on lists. When you change the Sort on or Then on selections, an index will be created on the table to improve performance. This index can be seen in the table settings.

If you know your VIEW is sorted on a unique key, you can scroll through all the rows in it by checking the "Sorted on Unique Key" box in the Sort panel. If you designate the VIEW as being sorted on a unique key when in fact it is not sorted on a unique key, then the order in which the rows are navigated can be unpredictable. In SQL databases, rows are not stored in any guaranteed order, so if you retrieve them without specifying an order, they may be returned in any order that is most appropriate at that moment.

Executing SQL

SQL is a powerful 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.

To use SQL in General DB, click on the SQL tab.

The top part of the window is where the results of a SELECT query are shown, and below that is where you add and edit SQL queries. If you execute a query that returns rows, such as a SELECT query, you can create a view from it so that you can select it in the Tables List and see its rows in the Rows Console.

Click the Templates button to create a template for a SELECT, INSERT, UPDATE, DELETE, CREATE VIEW, or CREATE TABLE query. If a table is selected, it will be inserted in to the template.

After a query is executed, it will be added to your query history. To mark a query as a favorite, press the star button next to the query in the SQL History window. To select a previously executed query or a favorite query click on it in the SQL History window.

If you drag and drop .sql files from the Finder into the Tables List, the SQL Console will appear with the contents of those files pre-loaded.

You can create views from the SQL Console as well. A view is typically either a re-ordered subset of fields in another table, or a JOIN of two or more tables. Views are read-only, meaning you cannot add, edit, or delete rows in them.

The files within the app container are available as a virtual table. To list them, execute the SQL below:

select * from general_db_files

SQL is widely used and has been around for more than 40 years. It is too large a subject to cover in this guide. For more details on SQL, please pick up a book.

Importing Files

Another way you can add a table is by importing a CSV file or a tab-separated or pipe-separated text file. To do this, click the Import tab. Click the + button under the Files to Import list and choose one or more files to import. You can change the Import Into table and other options such as the field separtor or text encoding by control-clicking on one or more files. You can change the options for the entire import job by clicking the gear button under the Files to Import list. If you enter a table name that does not exist, it will be created.

You can also drag and drop text files from the Finder in to the Tables List. If you drop the files on a table name, then that table will be used by default. Otherwise, the table name will be the name of the first file dropped without the filename extension.

UTF-8 is the most common character set encoding. If you know your file's character set encoding is not UTF-8, you can select the Windows 1252 Code Page or the MacRoman character set.

Options:

Convert Data: Convert dates and times to database format before inserting, and the NULL text string to the NULL value. You can change the text string that is converted to the NULL value by clicking the NULL Text String menu item.

Bail: Checking one of these options will abort the rest of the import job if those conditions are met.

Batch Lines: Batch row inserts in to groups of 100 for faster importing

Delete ALL Rows First: Delete all rows in the table before importing the rows from the file.

Click the Import button to start the import process. A progress bar will appear showing how many lines are remaining. You can cancel the import while it is running if necessary. After it completes, if any rows could not be imported, or if there were any other issues with the import, details of the import will be shown in the log. You can export this log to file by control-clicking in the Log view.

Exporting Rows

To export entire tables, click the Export tab. Then check boxes next to fields for each table you want to export. To the right of the field names are the field values from the first row in the table, if it exists.

By default, the rows are exported with fields separated by a tab character and lines ending with a linefeed character (Unix-style). Windows-style text files have carriage return/linefeed character combinations at the end of lines. You can change the export options by clicking the gear button and choosing from the pop-up menu. You can change the text string that is exported for the NULL value by clicking the NULL value menu item.

To export individual rows, click in the # column for the desired rows, and then press command-C on your keyboard to copy them. Then paste them to the desired app using command-V.

Printing Rows

You can print your rows in a grid of boxes in a page by clicking on the Print tab. The boxes can include these types of objects: fields, text, or newlines.

To design your box layout, press the Layout button. The Layout window will appear that includes the list of objects in the order they will appear in the box, settings for the boxes and the page, and a list of layouts. You can add fields from the currently selected table by pressing the + button below the Objects in Box list and then Add Field. You can similarly add text, or newlines. You can change the field or text object by clicking on it. To delete an object, select the object by clicking on it then press the - below the Objects in Box list.

The Layout window lets you change the page margins, the page grid, the box spacing, the box margins, or the font. You can create a new layout by pressing the + button below the Layouts list, or delete layouts using the - button. The (Default Layout) cannot be deleted and refers to the layout if there is no other layout selected.

You can browse through the selected rows by closing the Layout window, then pressing the up and down keys on your keyboard. Hold down the Option key to page up and down. Hold down the Command key to go to the first or last row.

Click the Page button to see how the layout will look on a page. When ready to print, press the Print button.

Creating Views

Views are a very powerful feature of SQL that let you present subsets of fields in a table or combinations of other tables as if it were a distinct table. You can create a view without manually typing SQL by first selecting the tables you want to include in the Tables List, then clicking the gear button and then clicking Create View.

Click the checkboxes for the fields that you want to include in the view for each table, then press the Next button. You will then see the SQL that is created.

You can add a WHERE clause to the SQL to join tables or limit it to a certain condition. You cannot change the view name from this SQL. Press the Create button, and the new view will appear in the Tables List. If you specified multiple tables, they will be joined together. If performance suffers as a result of a join, try adding indexes to your tables appropriately.

Pickers and Lookup Tables

Pickers and Lookup Tables provide a convenient way to select a value from a list for a field. To do this, you create a Picker or Lookup Table, and then select the Picker or Lookup Table as the field format of a field in another table.

In this app, you can create a Picker that is either stored in the system tables, or is a separate Picker Table itself. Pickers that are stored in the system tables are typically ones that have a small number of values, such as a "Yes/No" or "Small/Medium/Large" picker. A Picker Table is a table or view you created and designated as a Picker. A Picker Table is good for selecting from larger lists, such as a group of managers at a company, or a list of item types in an inventory.

To create a Picker that is stored in the system tables, press the settings button below the Tables List. Then click on Pickers from the menu. A window will appear with the pickers listed on the left and the picker values on the right. First add a picker by clicking on the + button underneath that list. You can select a new picker, or add one of the predefined ones and modify it. Add values to a picker by clicking the + button under the list on the right. If you would like to be able to select multiple values for a Picker, check the Multiple Choice box next to it.

To make one of your tables or views a Picker Table, select it in the Tables List, press the settings button, and click on Name and Fields. Then at the bottom left of the Name and Fields panel next to where it says Special, select Picker and press the Change button. Only the first field in a Picker is used. To allow multiple choice, select Multi-Picker.

After adding a picker, you can select another table, open the Name and Fields panel and change the format of a field to the new Picker. Then, when you add or edit the field with that picker format, you will be able to select it from the list.

To make one of your tables or views a Lookup Table, select it in the Tables List, press the settings button, and click on Name and Fields. Then at the bottom left of the Name and Fields panel next to Special, select Lookup and press the Change button. The Lookup Table should normally have 2 fields. The first field is used as the key, and the second as the value. Selecting a Lookup Table field stores the key in the database, but displays the value in the Row screen and Rows Console. If a Lookup Table has only 1 field, the the ROWID will be used as the key.

After adding a Lookup Table, you can select another table, open the Name and Fields panel and change the format of a field to the new Lookup Table. Then, when you add or edit the field with that picker format, you will be able to select it from the list.

Web Browser Bookmarks

Web browser bookmarks are only used in the iOS version of General DB and Private DB, where they can be selected in the web browser to download files from. You can still add or edit them using this macOS app though.

To add a bookmark, press the settings button above the Tables List. Then click on Web Browser Bookmarks from the menu. A window will appear with a table showing the Title and URL of the bookmarks. To add and delete from the bookmarks, click the + or - buttons. You can edit a bookmark by clicking in the text field of its Title or URL.

Preferences

Options

• Warn on row delete: Require a confirmation before deleting a row in the Add Row Console.
• Show system tables: The system tables are those that begin with sqlite_ or general_db_. These are mainly displayed for diagnostic purposes only.
• Format Data in Rows Console: If unformatted, the data is displayed how it is stored in the database. When editing a field, the unformatted value is shown.
• Show NULL in Rows Console: Displays NULL in gray if a field value is the NULL value.
• Auto-Refresh All Windows: Update other database windows whenever a field is saved.
• Foreign Key Constraints: Enable enforcement of foreign key constraints when adding or updating rows.
• Special Table Rows: Maximum number of rows that are loaded into memory for picker and lookup tables.

Tables Display

Four types of database objects can be displayed in the Tables List -- table, view, index, and trigger. Only tables and views have rows appear in the Rows Console or the other consoles. Views cannot have rows added, updated, or deleted.

Field Display

• If No Time Zone: If a Date or Time field has no time zone at the end, it is ambiguous and must be assumed as either local time or UTC.
• Date: Default format style for Date fields.
• Time: If the format style for Time fields is Date and Time of Day then the format styles of the Date and Time of Day components can be specified separately.
• Number: If the format style for Number fields is fractional, then the minimum and maximum fraction digits can be specified. The maximum fraction digits must be greater than 0 or else it will appear as an integer.

Field Storage

Including the time zone in a Date or Time field is recommended and the default.

If you store a Date or Time field with no time zone, it is not clear what point in time it refers to.

Keyboard Shortcuts

For keyboard shortcuts to the menu commands, click on the menu and they will be displayed. The shortcuts for buttons can also be found by hovering the mouse over the button.

Keyboard Shortcuts for Rows Console, Add Row Console, or Print Console

Keyboard Shortcuts for Rows Console

Keyboard Shortcuts for Add Row Console

Limitations

Table names that start with general_db_ are reserved for internal use.

Views are read-only per SQLite and thus rows cannot be added or editing in them.

Tables with more than 1 million rows are less performant when used on the apps for iOS devices.

Up to 1,000 rows can be copied and pasted to another app at once.