SQLite - one file?

1

Hey Everyone, (C11 here)
I inherited a legacy app and besides refreshing its visual, i’d like to move it to SQLite … now… i have problems extending schema with new fields and tables. When i did it DCT did expand but sqlitebrowser shows that schema is still the same.
two questions:

  • Is it normal to have one file for each table? (i would prefer them in one sqlite file, what should i put as owner and physical path?
  • How to expand schema and effectuate change on the sqlite file(s)? (Synchronization option seemed impossible/complicated because of multi-file approach)
2

No, one sqlite database per table is just plain wrong. The ownername should be the name of your (one) SQLite file. Full path name is the table name as it exists in SQLite.

SQLite is rather limited when it comes to ALTER table options (ALTER TABLE)

You can rename a table or column and you can add and drop columns.

If you need to change a column type, you have to go the long way round…

create table newtab
(oldcolumn defs,
changedcolumn defs);

insert into newtab(columns) select oldcolumns from existing_table;

drop table existing_table;

alter table newtab rename to existing_table;

In my opinion, even when you have everything in the one SQLite database I would only cascade changes from SQLite to Clarion. That is, change your table defn in SQLite, use the synchronizer to cascade the changes to your clarion dictionary.

Other things to bear in mind: Clarion will not import views from SQLite. What I do is create a table in SQLite by selecting from a view, import the table into clarion and point it to the view, then delete the temporary table in SQLite.

When you import tables into Clarion and the names in SQLite have underscores, Clarion will generate an external name like this: “with_underscore”. Those quotation marks will cause an error as soon as you try to open the table. Either get rid of the external name entirely or get rid of the quotes. Or don’t use underscores.

In my experience, Clarion dealing with SQLite locking is flaky. Sometimes things work as advertized, at other times Clarion has the database locked so that even operations on the same thread cannot make changes to data. However I haven’t yet found either the smoking gun or the silver bullet.

1 Like
3

Thank you so much - ton of useful information here - i will come back when i test.
I chose sqlite for ability to interact with external apps and use case is small that does not require big planning. I will mostly add columns to accomodate new things i will add.
I assume even with limitation - sqlite is best non tps file based “database”? Any other free alternative - i guess that mysql goes with odbc? How does it stack against mssql? Something i am overlooking?

1 Like
4

I used SQLite because I cannot really conceive of not being able to run ad hoc queries against the data that is being collected, and it is “embedded”, you don’t actually have a “server” that takes requests to add/change/serve up data that you have to install. But the whole-database locking thing with SQLite and Clarion making that worse meant that in one application I use TPS for actual data entry, and then periodically dump the data to SQLite for reporting and analysis.

In that embedded section you have firebird embedded and SQLite, where SQlite is less limited than firebird.

Then you have free client/server solutions including Postgres and MariaDB (a fork of MySQL). Personally, I would just go for Postgres in that realm. Client/server has a lot to recommend it, but in my environment (government) there is a whole bunch of hassle in making a use case because you require administrative privileges to install, and I have shied away from that.

And then there are commercial databases that are often free up to a certain size (MSSQL, Oracle, Mimer, MySQL) which you might choose based on footprint or particular capabilities. And yes, you’ll be using ODBC to access them; MSSQL also has the optimized ODBC driver, and Oracle has an OCI-based driver. For the larger things I have built here at work I have an Oracle server I control, and then all I have to do is get people access through the firewall. That’s easier than getting Postgres installed someplace, which I guess is why I don’t go that middle route.

5

Even if ALTER TABLE isn’t full featured, it’s pretty neat to be able to add a column on the fly without all of the stuff you go through with TPS.

6

I’ve been working with a real database so long that I sometimes forget what it’s like back in the old world, where you made a change and then nothing would work until you restructured all your data files.

7

Hello!

This is a fragment of a C# routine where I had to add the “Descuento” field to the “Pedidos” table in runtime.

Unfortunately I don’t have the routine at Clarion. I may also guide you how to do it.

// ---  Agrego el campo Descuento si la tabla no lo tiene
string tableName = "Pedidos";
string columnName = "Descuento";

string query = $"PRAGMA table_info({tableName})";
using (SQLiteCommand comm = new SQLiteCommand(query, SQLConn))
{
	using (SQLiteDataReader reader = comm.ExecuteReader())
	{
		bool fieldExists = false;
		while (reader.Read())
		{
			string existingColumnName = reader["name"].ToString();
			if (existingColumnName == columnName)
			{
				fieldExists = true;
				break;
			}
		}

		if (!fieldExists)
		{
			// El campo no existe, agrégalo
			string addColumnQuery = $"ALTER TABLE {tableName} ADD COLUMN {columnName} REAL";
			using (SQLiteCommand addColumnCommand = new SQLiteCommand(addColumnQuery, SQLConn))
			{
				addColumnCommand.ExecuteNonQuery();
			}
		}
	}
}
8

SQLite is Not Multi-User. IIRC you can have multiple EXEs on the same PC with the SQLite database open, but you run into issues like you cannot Insert in EXE 1 if the file is open in EXE 2 because it needs to lock the file.

ClarionLive did 9+ episodes refactoring the Invoice example that included converting it to SQLite. It was December 2022 to April 2023 Episodes 685 to 709 with some gaps. The titles were mostly “Boldly Going Where No One Has Gone Before - Refactoring A Clarion Example” so you could put “BOLDLY” in the search box.

I did Episode 692 titled “Testing The Invoice Example; SQLite issues and oddities!”. I wrote a separate EXE to load the SQL database with test data. This revealed that running both the Example and Test programs had issues.

After working on that test data generator IMO the Clarion SQLite driver is not polished for use in production. Often it returns the same error message that is not too useful: “Unknown Error Posted: 19”. I found the DUPLICATE(File) function always returned True and DUPLICATE(Key) always returned False.

1 Like
9

5 posts that drifted to Firebird were split to a new topic: Firebird Embeded versus SQLite considerations?

10

Not true. The clarion accelerator driver for sqlite3 is a bit lacking (it’s unusable IMHO), but sqlite3 is more than capable of working in a multi tenant environment:

  • if you have memory locality you can run the db in WAL mode meaning that the only limitation is that no two writers may update the DB at the same time.
  • if you need to work without memory locality (LAN or similar infra) then you need to be mindful not to leave read statements in a “working” state as they will block commits from other connections.

You are pretty much gonna need a library to interact with sqlite3 though. We are planing to release our sqlite3 clarion lib, but it needs a bit of cleanup before I’d feel comfortable sharing it with the wider community.

11

I should have said “not great” at multiuser network to the point I think of it as unusable. As you note the Clarion driver does not seem finished. It’s too bad because SQLite is small, robust, easy and has many cool features like the command line SQLite3.exe utility.

The Invoice example I posted allows trying it out. The included Tester allows loading a lot of data. You can have it adding records while you can try using the example at the same time to see what errors or locking problem happen.

This page details “Appropriate Uses For SQLite” where it says access by many users over a network is not recommended.

https://www.sqlite.org/whentouse.html