SQLITE

SQLite is a self-contained, public domain SQL database engine that is often embedded in applications.   Manifold and Manifold Viewer installation packages include built-in support for SQLite (and also for GPKG, which is built on SQLite) as most people use it in GIS, which means viewing and working with tables using Manifold SQL, but not needing to run native SQLite SQL (we can always run Manifold's full-featured SQL).   See the Third Party Release Levels  topic for the current level of Manifold's internal SQLite support.  Manifold can manipulate SQLite file databases like any other database, including deleting or renaming table fields.

 

If we would like to run native SQLite SQL in addition to Manifold SQL, we can do so.  See the Example: Switching between Manifold and Native Query Engines topic for an example of using a native SQL in addition to Manifold SQL.

 

We can either import data from SQLite into a Manifold .map project or we can leave the data within the SQLite file and link to it  to enable editing or other use of the data in place within the SQLite database.   Which we choose will depend on the number of records involved, the speed of SQLite we can tolerate or not tolerate, and the convenience of keeping data within the SQLite file for any interchange requirements.  We can also export tables to SQLite, automatically creating the SQLite database file that stores the table.

 

SQLite has no standard three letter extension for files.   Most SQLite users will utilize .sqlite as an extension, but others will use .db or whatever they want.   ESRI, for example, uses SQLite database files for ESRI's .stylx files that save styles.  ESRI simply uses the .stylx extension.   Change the .stylx extension to .sqlite and we can double-click those ESRI files to open them as an SQLite database.  Or, we can leave the extension as .stylx and open them a SQLite database files as shown below.

 

For automatic imports, the Manifold dataport treats files with a .sqlite extension as SQLite database files, but any file name and extension can be used, as shown below.   The examples below use the Chinook sample database that is often used with SQLite.   

Data Types

SQLite tables are weakly-typed, and allow storing an integer value in a text field, and so on.  The latest versions of SQLite have started straightening out that squishiness with STRICT tables, allowing users to declare they give a hoot about data types in a particular table, but that is optional.   The range of supported types is limited as well: INT, REAL, TEXT, BLOB, or ANY.  All other type names are aliases.

 

As with enterprise DBMS packages, the SQLite dataport exposes all TEXT fields as NVARCHAR (Unicode).  Attempting to create a VARCHAR field will create it as TEXT field in the SQLite database, but the field will look like an NVARCHAR data type in Manifold.   What VARCHAR means varies between data sources and converting between different meanings frequently loses data, so this convention by Manifold helps to preserve data.  

Import an SQLite File

When importing a SQLite file the tables that appear in the Manifold project are Manifold components with no further connection to the SQLite file from which they were imported.  

 

To import from SQLite format (file named with a .sqlite extension):

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing data of interest.

  3. Double-click the .sqlite file desired.

  4. Everything found in that .sqlite file will be imported into the project.

 

 

To import from SQLite format (file not named with a .sqlite extension):

 

  1. Choose File-Import from the main menu.

  2. In the Import dialog browse to the folder containing data of interest.

  3. Click the file desired, to load the file name into the File name box.

  4. Choose SQLITE Files (*.sqlite) in the file type box.

  5. Press the Import button.

  6. Everything found in the specified SQLite file will be imported into the project.

 

 

Example:  We import an SQLite database file named chinook.db:

 

 

Choose File - Import and browse to the desired folder.   Click the chinook.db file to load it into the File name box.

 

 

Choose SQLITE Files (*.sqlite) in the file type box and press Import.

 

That will import into our project everything found in the chinook.db SQLite sample database file.  

 

 

If our SQLite files are named with an .sqlite extension, we can just double-click on them as shown above.

 

 

 

The Chinook sample database contains a variety of tables.   We can double-click on one of the tables to open it.

 

 

Manifold will capture whatever there is in the SQLite database file, and will utilize key fields and indexes in the database.  For example, in the table above, Manifold automatically utilized the index on the CustomerID field so the table is fully editable and selectable.   If we had imported a table from a database with no index, we could add an index in moments, as illustrated in the Add an Index to a Table topic.

Link an SQLite File

When linking a SQLite file the tables, images and drawings that appear in that data source in the Manifold project stay resident in the SQLite file.   They are SQLite components even though they may appear in many respects, for the convenience of the user, to be Manifold components.

 

SQLite may be slower than a fast DBMS like PostgreSQL, but it is fast enough to enable native storage of data when there are not too many objects.  In Manifold we can take advantage of that by linking a SQLite file into our project.   The link creates a data source cylinder that indicates the data is stored outside of the project, in the original SQLite file.  When we expand the data source we can see the data within.  This works well for smaller data sets until SQLite gets too slow.

 

To link a SQLite format file (file named with a .sqlite extension):

 

  1. Choose File-Link from the main menu.

  2. In the Link dialog browse to the folder containing data of interest.

  3. Click the .sqlite file desired.

  4. Check or uncheck the Save cache box as desired.

  5. Press Link.  A linked data source will appear in the project.

  6. Press the + icon next to the data source to expand the data source to see the tables it contains.

 

 

To link a SQLite format file (file not named with a .sqlite extension):

 

  1. Choose File-Link from the main menu.

  2. In the Link dialog browse to the folder containing data of interest.

  3. Click the file desired, to load the file name into the File name box.

  4. Check or uncheck the Save cache box as desired.

  5. Choose SQLITE Files (*.sqlite) in the file type box.

  6. Press Link.  A linked data source will appear in the project.

  7. Press the + icon next to the data source to expand the data source to see the tables it contains.

 

 

Example:  We link an SQLite database file named chinook.db:

 

 

Choose File - Link and browse to the desired folder.   Click the chinook.db file to load it into the File name box.   

 

The Save cache  button allows setting cache options.

 

 

Most often when linking to a format like SQLite, we will ensure the Save cached box is not checked.  Working with the SQLite database will be faster if we check the box, but if we are going to cache data within the project we may as well simply import the SQLite and use full Manifold speed.   We uncheck the box.

 

 

Choose SQLITE Files (*.sqlite) in the file type box and press Link

 

That will create a new data source in our project, linking into the project everything found in the chinook.db SQLite sample database file.  We can click on the + symbol next to the database cylinder to expand the data source.

 

 

The Chinook sample database contains a variety of tables.   We can double-click on one of the tables to open it.

 

 

The caption in the title bar uses Manifold nomenclature to indicate the invoices table we have opened is within the chinook data source.

Export a Table to an SQLite File

We can export tables to SQLite, automatically creating the SQLite database file that stores the table.

 

To export a table to an SQLite format file (file named with a .sqlite extension):

 

  1. Right-click the table in the Project pane and choose export,  or...

  2. With the focus on the opened table window, choose File-Export from the main menu.

  3. In the Export dialog browse to the folder where the SQLite file is to be created.

  4. In the File name box enter the name for the file.

  5. In the Save as type box choose SQLITE Files (*.sqlite).  

  6. Press Export.  A new SQLite file containing the table will be created.

 

Working with ESRI ST_Geometry within SQLite and GPKG Files

ESRI provides an optional extension library, stgeometry_sqlite.dll, that allows loading ESRI geodatabase tables, that is, ST_Geometry tables, into SQLite and GPKG. That is somewhat of a bizarre idea instead of using ESRI's own file GDB geodatabase, but what the heck, ESRI does it and Manifold supports it as well.  

 

If ESRI's optional extension library is installed into an SQLite or GPKG file database, Manifold supports using ST_Geometry for all operations.   See ESRI's page on adding ST_Geometry to SQLite and GPKG for ESRI information on using ESRI's extension.

Notes

SRID of -1 -  When Manifold links or imports an SQLITE / GPKG database file, the Spatialite SRID of -1 used for unqualified coordinate systems is automatically recognized.

 

Running native SQLite SQL on a GPKG or an SQLite data source - Manifold and Manifold Viewer installation packages automatically include a built-in SQLite implementation that supports GPKG as well as straight SQLite database files.   Built-in support for SQLite allows running either native SQLite SQL or Manifold SQL against an SQLite data source.   See the Example: Switching between Manifold and Native Query Engines topic for an example of using a native SQL in addition to Manifold SQL.

 

How can tables be exported to SQLite files?   Choose File - Export and export as a GPKG file.   GPKG is just SQLite by another name.  If we export ordinary tables, that is, tables which do not contain geometry fields, the GPKG simply ends up being an ordinary SQLite database that does not require use of any SpatiaLite extensions.

 

UUID fields - MySQL, SQLite, Oracle, and DB/2 databases support UUID fields as a fixed-length string type.

 

See Also

GPKG

 

Example: Link GPKG and Save Style - A companion topic to the GPKG topic.   How to link a GPKG, open a drawing, Style it and then save so the styling is retained within the GPKG file.

 

Example: Import from GPKG and Modify Geometry - This topic provides a complete example that shows a simple, but typical, task involving spatial data.  We import a country-sized data set from GeoPackage (GPKG) format, change all areas in the data to the boundary lines for those areas, and then save those boundary lines as a new drawing and table.