Private DB

- Regular expressions in SQL, such as:
SELECT * FROM Maintenance WHERE Description REGEXP '^Fixed'
- SQLite 3.36.0

- If you open a database that has a field value that cannot be displayed as a text string, then a special string is displayed instead. This is to prevent unexpected behavior when binary data is in the field but the Raw field format is not selected.
- Bug fixes
- SQLite 3.35.5

Includes SQLite 3.35.4:
- Built-in math functions, such as sin() and log()
- ALTER TABLE DROP COLUMN
- Generalized UPSERT
- RETURNING clause lets you see the values after an INSERT, UPDATE, or DELETE.

NOTE: This is a major release. If you have critical databases, you must back them up using iTunes or iCloud or Dropbox before updating this app!

Fix for selecting field values in Date and Time formats in iOS 13.4 and above.

- SQLite 3.34
- The generate_series() virtual table is now built in to this app. As an example, the SQL executed below would make a virtual table of 6 rows with even values starting from 0 up to 10:
SELECT value FROM generate_series(0, 10, 2)
- Bug fix for importing large files

Note: Backing up your databases before a major software update is good practice and recommended. To backup your databases, sync your phone with iCloud or your computer, or go to the Files tab and Share your databases with another computer.

- The Tables List now has a database file settings button. This lets you more easily make tables or views hidden in the currently selected database.
- See the file size of any file by tapping it in the Files List then tapping its details button.
- Picker Types are now called Pickers that are stored in the system tables.
- Bug fixes

SQLite 3.33:
- Decimal math functions
- Support for UPDATE FROM

IMPORTANT: This is a major release. If you have critical databases, you MUST back them up before updating this app. To back them up, share them with your computer or iCloud, or upload to Dropbox.

- More documentation for SQL functions
- New search bar for SQL functions
- SQLite 3.32.3
- Bug fixes

Although this is a minor release, it is good practice to back up any critical databases you have before updating the software.

Hide tables and views in the List of Tables. This helps when working with databases that have a lot of tables and views, some of which you rarely use. It can also be useful if you are distributing your databases to others, who do not need to see all the tables. To hide a table or view, go to the List of Files, tap your database file, then tap the table or view you wish to hide and tap the "Hide" button. To un-hide it, do the same thing but tap the "Show" button. To temporarily display the hidden tables and views in the List of Tables, long-press the info button in the toolbar below the List of Tables.

Make any field an expanded field, not just fields with Text format. The expanded fields will be concatenated together in the Expanded Fields Section of the Row Display. Go to the table settings of a table to select the expanded fields.

Hide the first section in the Row Display. The first section normally displays the field name and the first line of each field. If you hide it, and select some fields to be displayed in the Expanded Fields Section, all the lines of the selected fields can be displayed. This makes for easier browsing through the rows. Go to the table settings of a table to hide the first section in the Row Display.

The Expanded Fields Section now is sized better when multiple fields are expanded.

The SQL Console now has a "Functions" button that presents a window showing all the functions available in the app. Tap the function to see its description if it has one.

There is a Long-Presses section in the Help in the app info, describing the long-press shortcuts in the app.

The release date of the SQLite library used in the app is shown in the Diagnostics in the app info.

SQLite 3.32.2

Bug fixes.

NOTE: This is a major release. If you have critical databases, you MUST back them up before updating your software. To back them up, share them with your computer using AirDrop, or upload them to iCloud or Dropbox.

- Fields with the Text field format can now have the "Show Expanded" style. This lets you see multiple lines of a field in the Row screen in an expansion section underneath the fields. Useful when navigating up and down through rows.
- Bug fix: Fields with the Number field format are shown without formatting if the accessory button is tapped.

- Allow override of the number of "Fields to Show" in a table's settings.
- Allow override of the "Row Format" in table's settings.
- Number fields with the format style None can be edited in-line.
- Override format styles of Number fields with field format style of None.
- In the "Tables Display" section of the App Info, the "Show Detail Button" option was removed as it was replaced with long press.

Even though this is a minor release, it is prudent to back up your critical databases and files before updating the app. To back them up, either use Dropbox or iCloud, or sync them with your computer.

IMPORTANT: This is a major release. If you have critical databases, you MUST back them up before updating the app. To back them up, either use Dropbox or iCloud or sync them with your computer.

- Fields with formats of Date, Time, or Number can now be styled. Change the default style for all Date, Time or Number fields in the app info screen. Override the default styles for a table or view in the table settings.
- The files within the app container can now be retrieved using SQL. Examples:
SELECT * FROM general_db_files
SELECT name FROM general_db_files WHERE size > 1024
- A Location field can now have its latitude and longitude separated by a comma.
- Choose the field storage format separately for Date and Time fields.
- Bug fixes
- SQLite 3.31.1

- Fix for importing text files with Chinese Unicode characters.

- Improved built-in web browser for downloading files from the web
- Fixes for dark mode in iOS 13

- Corrected the colors for the Rows Shown label.
- Corrected the dark mode colors in the status view when importing.
- SQLite 3.30.1

- Support for dark mode in iOS 13
- Updated Help in the app
- Long-press a table to go straight to its settings
- SQLite 3.30

- Choose the file where SQL is saved to.
- Long press the lightning button to skip the SQL History and go straight to the SQL Console
- Bug fix for importing files

IMPORTANT: Even though this is a minor release, it is good practice to back up your database files before installing an update, especially if they are critical. To back them up, upload them to Dropbox or iCloud, or share them to your computer using iTunes or AirDrop.

- In the SQL Console, favorites are now merged with the history, similar to the SQL History in the macOS version. To make a SQL command a favorite, tap the star adjacent to it. To only show SQL favorites, tap the star button to the left of the search bar.
- Execute multiple SQL statements in the SQL console. Separate them with semicolons.
- For Location field formats, the previous span of the map view is remembered.
- Bug fixes

Easier to Create SQL Views:
After executing a SQL query that returns results in the SQL screen, you can now create a SQL view from those results. You can also change the field formats of the fields when creating a view. This is a very powerful feature yet very lightweight, as it does not add any rows to the database, but lets you rearrange fields, select only a few rows, or reorder the rows.

Raw Field Format:
The new "Raw" field format lets you see the actual bytes in hexadecimal that are stored in a field in a row in the database. This is useful if you are having any issues with your data, or are storing binary large objects (BLOBs). They are only displayed and cannot be edited. To change their value use the UPDATE statement in SQL.

Dates and Times:
When a field with the Date or Time format is stored in General DB, by default it includes the time zone in +/-HHMM format at the end. Now you can omit that if you know you will always be in the same time zone (without Daylight Savings Time), or if you want to use UTC time. The original format is recommended however, as omitting the time zone causes ambiguity in dates and times.

There are some new built-in functions for converting fields in the Date and Time format of General DB to that which can be used by SQLite date and time functions. The following SQL statements can be executed in the SQL screen in General DB to see how they work:
SELECT utc2date('2020-01-01');
SELECT utc2time('2020-01-01 13:05:30');
SELECT date2utc('2020-01-01 -0400');
SELECT time2utc('2020-01-01 13:05:30 -0400');

Other Changes:
- Number of rows loaded for a Lookup Table can be increased in the app options. Only do this if you need to and have a fast device with a lot of memory.
- Specialized field views are available even if the table or view is read-only.
- SQLite 3.28
- Bug fixes

Include the + button so fields can be added in a new table.

Fix for error when altering a table that already has a view referring to it.

IMPORTANT: As this is a major release, it is imperative to back up your critical databases before updating! To back up your databases, either use iTunes or AirDrop to share your databases to your computer, or upload your databases to Dropbox.

Major Changes for the iPad User Interface:
The app's user interface on the iPad has been changed to more closely resemble that of the iPhone. This is not just to make them more consistent, but also to allow the larger iPhones to format fields horizontally in a row (set the "Row Format" option to "Horizontal"). When this app was first developed almost 10 years ago, the iPhone was 3.5" in size and the iPad was 9.7". Now some of the newer iPhones are much closer in size to th e iPad, so the change was necessary. Also, some of the views were changed to better use the larger screen-size of the iPad.

Lookup Tables:
A table or view can be designated as "Lookup" in its settings. Then it can be used as a data type of a field in other tables. 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 List of Rows screen. If a lookup table has only 1 field, then the ROWID will be used as the key.

Other Changes:
- The SQL Console is now accessible by tapping the lightning bolt button in the toolbar of the Tables and Files screen. It was previously in the app info or in the search bar area after selecting a table.
- A view no longer has to include the ROWID field. However, a view must be sorted on a unique key for all of its rows to be scrolled through, and it must be designated as such in its settings. If it is not designated as "Sorted on Unique Key", then scrolling through it is limited to the first 1,000 rows.
- The lightning bolt button in the SQL Results screen now prompts to create a view with the executed query. After you create a view, you can select it from the Tables list to see its rows. You can also change the format data type of the view's fields to display the data in a more readable format.
- The SQL Results screen now shows up to 1,000 rows
- Bug fixes
- SQLite 3.27.2

- Allows database file extensions of .db3 and .sqlite3
- The "View Row" screen is now titled "Row" on the iPhone.
- Bug fixes

- Fix for files not being able to be copied into the app from the Files app if Dropbox was not enabled.
- Copy, import, and extract from the Files app now requires iOS 11 or above.

IMPORTANT: As this is a major release, it is imperative to back up your critical databases before updating! To back up your databases, either use iTunes or AirDrop to share your databases to your computer, or upload your databases to Dropbox.

- Search more than one field. Note that if you search on fields that do not have indexes, and there are many, many rows, it can take a long time to return results.
- Import a text file from the Files app without copying the file.
- Execute SQL in a text file in the Files app without copying the file.
- Extract a compressed file in the Files app without copying the file.
- Copy a database file or image file from the Files app.
- Allows reserved words to be used for table names and fields. Note, it is usually a bad idea to use reserved words, such as "SELECT" or "FROM" as your table or field names. Here is a difficult to comprehend SELECT SQL query example that searches for the value 'Here" from a table named "From" with a field named "Where":
SELECT * FROM "From" WHERE "Where" = 'Here'
- Queries in the SQL Console no longer have to start with "SELECT" to show row results. You can prepend a WITH clause before a SELECT statement and view row results.
- SQLite 3.27.1
- Bug fixes

IMPORTANT: As this is a major release, it is imperative to back up your critical databases before updating! To back up your databases, either use iTunes or AirDrop to share your databases to your computer, or upload your databases to Dropbox.

- Importing allows line separators within the fields for CSV files, if they are enclosed in double-quote characters.
- You can convert NULL text strings to the NULL value when importing. This can be useful when importing extracts from another database system, such as MySQL.
- The View Row view indicates if a field is NULL.
- The options for importing files have been moved out of the App Info options to the view where the import is performed.
- Better font scaling
- SQLite 3.26
- Bug fixes

Notes on changes to importing text files:
- If you import a text file as UTF-8 that is not actually encoded as UTF-8, some characters may appear as question marks.
- All carriage return characters are removed from a line before inserting the corresponding row into the database. This is to prevent the perception of a problem when importing a text file as Unix-style when it is actually Windows-style (came from a Windows computer).