Good-to-Know about SQLite
OGR supports SQLite and some SQLite based Formats (eg. GeoPackage, SpatiaLite, MBTiles and RasterLite) if compiled with SQLite3. SQLite has many advantages and some quirkes. Some of which are listed here.
Please contribute to this list if you know of additional problems or can help with the language!
Do not write on Network File Systems / Samba Shares!
"SQLite databases often do not work well over NFS, or some other networked file system protocols due to the poor support for locking. It is safest to operate only on SQLite files on a physical disk of the local system." (OGR SQLite Documentation)
The "[...] locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS [...]."(SQLite FAQ)
Delete and Change Column Names
"SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table." (SQLite FAQ) See also SQLite Documentation.
Delete and Change Names of Tables
In GeoPackage table names have connections to various places, for example the name of the R-Tree spatial index table, gpkg_contents, gpkg_geometry_columns, and perhaps gpkg_metadata. The constrains and triggers should prevent changing the table name with generic SQL "ALTER TABLE RENAME TO..." but as a rule of thumb, don't even try if you are not sure about what you are doing. The GDAL GeoPackage driver has special SQL functions which are safe to use.
Starting with GDAL 2.2, the "DROP TABLE layer_name" and "ALTER TABLE layer_name RENAME TO new_layer" statements can be used. They will update GeoPackage system tables.
SpatiaLite is also using table names as foreign keys. Renaming tables is not recommended. It is easier and safer to create a new database with altered table names.
R-Tree Spatial Index
How does the R-Tree spatial index work and what makes them difficult to use with spatial views? http://osgeo-org.1560.x6.nabble.com/gdal-dev-GeoPackege-views-and-spatial-index-td5312752.html
Journal Modes
SQLite supports different journal modes. The default is the rollback journal. Beginning with version 3.7.0 (2010-07-21), a new "Write-Ahead Log" option (WAL) is available. On one hand WAL is significantly faster in most scenarios and provides more concurrency. But on the other hand WAL does not work over a network filesystem, it is not possible to open WAL databases from read-only media (SQLite Documentation on WAL).
Existence of a WAL file means that there are pending transactions. Do not ever take the data file (.gpkg, .sqlile) at this state as a backup or for delivering data for other users but wait until the WAL file has disappeared. That may require closing a connection from the software that has done updates.
Performance
See the section Performance hints at the bottom of OGR SQLite Documentation.
