SQLite - Basics

Contents

Introduction. 1

Enable SQLite support. 1

Management. 1

Diagrams. 1

 

Introduction

SQLite is a server-less database meaning there is no separate server process that the client connects to. The client process accesses the database files directly using calls in the SQLite DLL.

Enable SQLite support

 

 

Management

SQLite includes no GUI management software. If you want to be able to issue commands to the database you could just do it from your own code using the DAL, the only thing you need to install is the SQLite data provider DLLs.

You can also install the “SQLite Tools”, this includes the “Command Line Shell For SQLite” with this you can execute commands against the database. https://sqlite.org/cli.html

Surfing the web turns up quite a few options for GUI management software but, to me anyway, there is no obvious choice, looks like the open source projects have been abandoned (may still be useful) or you have to pay and I’m not sure whether they are worth it?

You can use the Framework’s DAL to report on the database schema if you want to quickly look at:

       Spludlow.Data.IDAL data = Spludlow.Data.DAL.Create2(@"[SQLite] C:\ProgramData\Team MediaPortal\MP2-Server\Database\MP2TVE_3.s3db");

DataSet schema = data.Schema();

       Spludlow.Log.Report("Schema", new object[] { schema });

Then go and look at it on the Intranet Log page

Diagrams

One way of getting a diagram is use the Framework’s DAL to clone the database to a server type you are familiar with then work with its diagramming tools. Obviously this won’t synchronise with the original SQLite database but at least you will have a diagram to look at, you can always make a list of manual changes to make, or clone it back with the DAL if the database has no records in it yet. This example makes a clone of the SQLite schema on the SQL Server database running on host “DATABOX” (short hand SQL Server connection string using Windows Authentication).

Spludlow.Data.Schemas.CloneDatabase(@"[SQLite] C:\ProgramData\Team MediaPortal\MP2-Server\Database\MP2TVE_3.s3db", "DATABASE-HOST", "MP2TVE", true);

Then go and create a diagram in SSMS.