Using DBlookup to modify a SQLite table column

[MickB]

Good day all,

By way of disclaimer I have to say that I have not done any serious programming for 20 years (using Delphi/Paradox), I have not used Lazarus much and I have never used SQLite. I want to be able to use Lazarus Pascal and SQLite for a project I am working on. Before starting on the project I am playing around with some very simple things to get familiar with the software. That being said, the question is: how can I use a lookup to alter a value in another table's column. Let me explain.

I have two tables: (1) Countries and (2) Cities defined as:

Code: MySQL  [Select][+][-]

1. CREATE TABLE "Countries"
2. ("CountryID"INTEGER NOT NULL UNIQUE,"
3. Country"NVARCHAR(30),"
4. CapitalID"INTEGER,
5. PRIMARY KEY("COuntryID" AUTOINCREMENT),
6. FOREIGN KEY("CapitalID") REFERENCES "Cities"("CityID")
7. )
8.  
9. CREATE TABLE "Cities" (
10. "CityID"INTEGER NOT NULL UNIQUE,
11. "City"NVARCHAR(30),
12. PRIMARY KEY("CityID" AUTOINCREMENT))
13.  

The idea is that for each Country in the Countries table there will be one entry (in the CapitalID column) for the corresponding city in the Cities table.

I am using Zeos DBO but that is not locked in. I'm happy to use standard DB components.

In the main form I have a DBgrid. The DBgrid displays the columns for the Countries table plus an extra column to display the name of the capital city. The SQL is:

Code: MySQL  [Select][+][-]

1. select CountryId, country, CapitalID, city
2. from countries
3. left  join cities
4. on countries.CapitalID = cities.cityid;    
5.  

This displays as expected (see attached screenshot).

If I don't have the join in the SQL, I can update the CapitalID column with a dblookup. However, with the join, the DBlookup will not update the column. I get a message:

Cannot update a complex query with more than one table.

Obviously I am doing something wrong - but what?.  I would like to have the actual city displayed in the DBgrid but still be able to update it.

Any suggestions would be greatly appreciated.

[rvk]

How does your update SQL look like?
(You need to write those manual for join selects)

And you can only update one table. You can't update the CapitalID in Countries at the same time as the capitolname in Cities. For that you need to think about redesigning your screens and use separate entry dialogs.

BTW. Why is the table called Cities?
Shouldn't it be called Capitols?

If Cities can have non-capitol cities, then that table also needs to have another field CountryID as foreign key pointing back to a country. Make sure to think about your DB design beforehand. Or don't you need to know what country a city is in???

[Zvoni]

First off:
Since you want to use SQLite, you should get familiar with it.
1) Don't use AutoIncrement for Integer Primary Keys in SQLite. You don't need it. SQLite will still count up automatically.
2) Don't use Datatypes like VARCHAR, DateTime etc. Use the real and correct DataTypes.
3) Your Table-Setup is Nonsense. It should be the other way around: Countries is the Master-Table to Cities. the Cities-Table needs a Foreign Key to Countries' Primary Key.
If you're interested in the Capitol of a Country, just add a Column "Capitol" to "Cities with DataType Integer defaulting to 0. If a City is the Capitol, set it to 1
(I'd probably implement an AFTER INSERT and AFTER UPDATE Trigger setting all other "Capitol"-Values to 0, if a City gets the "1" for Capitol).

Then the Query to get the Countries with its Capitol is straightforward.
Untested

Code: SQL  [Select][+][-]

1. CREATE TABLE "Countries" (
2.   "ID" INTEGER PRIMARY KEY,
3.   "CountryName" TEXT UNIQUE NOT NULL
4. );
5.  
6. CREATE TABLE "Cities" (
7.   "ID" INTEGER PRIMARY KEY,
8.   "CityName" TEXT NOT NULL,
9.   "Capitol" INTEGER DEFAULT 0,
10.   "Country_ID" INTEGER,
11.   FOREIGN KEY ("Country_ID") REFERENCES "Countries"("ID") ON UPDATE CASCADE ON DELETE CASCADE
12. );
13.  

Code: SQL  [Select][+][-]

1. SELECT
2. CO.ID AS CountryID, CHAR(CO.CountryName) AS CountryName,
3. CI.ID AS CityID, CHAR(CI.CityName) AS CityName
4. FROM Countries AS CO
5. LEFT JOIN Cities AS CI ON CI.Country_ID=CO.ID AND CI.Capitol=1

[rvk]

If you're interested in the Capitol of a Country, just add a Column "Capitol" to "Cities with DataType Integer defaulting to 0. If a City is the Capitol, set it to 1
(I'd probably implement an AFTER INSERT and AFTER UPDATE Trigger setting all other "Capitol"-Values to 0, if a City gets the "1" for Capitol).

In that case you have a field which is seldomly used (only once per country).

So you could also have a capitalID field in Country pointing back at the cities (like it is now).

But you do need to make constraints in that case not to select a city which isn't in it's country.
So there are two ways to look at that design.

The problem will still be the input-design.
I think this calls for separate dialogs or inputfields instead of just being able to edit the TDBGrid.

[MickB]

Thanks for your replies rvk and zvoni.
rvk: The names of the tables don't matter. They are some simple tables I am using to get familiar with SQLite. They could be anything. For example the Cities table could be a Mark-Ups table where each line is a mark-up percent, (10%, 20%) and the Countries table could be a Products table so each product would have only one mark-up applied to it. I used Countries and Cities just to play around with. The cities table does not have countries associated with it so I could select London as the capital of the USA. While this does not reflect the real world, I am trying to get the updates working as, e.g., if I was doing markups and products, the markup would not belong to only one product. 

zvoni: Please see my first comment to rvk. I am trying to get familiar with it and with Lazarus. I can only go on examples I see and many of them used autoincrement. However, if SQLite does not need them I can remove them and I can change the datatypes but those don't get to the issue. That is, I want to be able to update (in this example) the capitalId column in the countries table using the id of the value I select in a dblookup. I'm not sure why I need a foreign key in the Cities table as there will only ever be one capital city. Surely it should be possible to use the tables as they are (ignoring autoincrement columns) Again, this is just a simple example for me to play with SQLite. Each item in the countries table will only ever have one item from the cities table. The tables could be anything as I mentioned to rvk above.

Cheers

[rvk]

That is, I want to be able to update (in this example) the capitalId column in the countries table using the id of the value I select in a dblookup.

As I already stated... because you are using a JOIN in your SELECT, FPC can't generate the INSERT and UPDATE queries automatically.
So you need to set your TSQLQuery.UpdateSQL manually if you want to be able to edit that field.

Something like:

Code: SQL  [Select][+][-]

1. UPDATE Countries
2. SET CapitalID = :CapitalID
3. WHERE ID = :OLD_ID;

You will only be able to update one table at a time. Either Cities or Countries.

I'm not sure why I need a foreign key in the Cities table as there will only ever be one capital city.

You need to think about your data before defining your final tables.
A city will always need a country. That's why you would normally also add a foreign key to your city, pointing to the country.

For example if you assign a city to a customer... you want to know in which country that city lies.
But ok... for just test purposes it isn't important.
But make sure you think in a better way about your final data.

[Zvoni]

If you're interested in the Capitol of a Country, just add a Column "Capitol" to "Cities with DataType Integer defaulting to 0. If a City is the Capitol, set it to 1
(I'd probably implement an AFTER INSERT and AFTER UPDATE Trigger setting all other "Capitol"-Values to 0, if a City gets the "1" for Capitol).

In that case you have a field which is seldomly used (only once per country).

So you could also have a capitalID field in Country pointing back at the cities (like it is now).

I disagree.
Think about Normalization-Rules.
Is "Capitol of Country" an attribute/property of a City or of a Country?
e.g. Netherlands (Shout out to Thaddy)
Is Amsterdam the Capitol or Den Haag?
Officially, Amsterdam is the Capitol, but Den Haag is the seat of the Goverment and the royal residence.
Could argue both ways, or even, that both are the Capitol

As I already stated... because you are using a JOIN in your SELECT, FPC can't generate the INSERT and UPDATE queries automatically.
So you need to set your TSQLQuery.UpdateSQL manually if you want to be able to edit that field.

Just thought about an INSERT/UPDATE on a VIEW, because then he'd have only one underlying "table" (Object) in his Query, but SQLite doesn't support that

[rvk]

Is Amsterdam the Capitol or Den Haag?
Officially, Amsterdam is the Capitol, but Den Haag is the seat of the Goverment and the royal residence.
Could argue both ways, or even, that both are the Capitol

No you can't. Amsterdam is the capital... I'm also from the Netherlands

But if you set a capital flag, and enforce that only one capital can be set to 1 per country, you have the same problem. But in that case you need to create a trigger to set all others to 0. Having a capitalid foreign key in country then you can only choose one city.

There is only one city capital in each country.
Den Haag is not a capital of any country.

If you want to add a field seat_of_government, then of course, you can add that. But capital is just one per country.

[Zvoni]

Is Amsterdam the Capitol or Den Haag?
Officially, Amsterdam is the Capitol, but Den Haag is the seat of the Goverment and the royal residence.
Could argue both ways, or even, that both are the Capitol

No you can't. Amsterdam is the capital... I'm also from the Netherlands

But if you set a capital flag, and enforce that only one capital can be set to 1 per country, you have the same problem. But in that case you need to create a trigger to set all others to 0. Having a capitalid foreign key in country then you can only choose one city.

There is only one city capital in each country.
Den Haag is not a capital of any country.

If you want to add a field seat_of_government, then of course, you can add that. But capital is just one per country.

OK, wasn't sure about that.
But i still don't like that "CapitalID" in "Countries"-Table.
Kind of like a Foreign Key pointing back to Cities...
Just trying to think about DRI gives me a headache in that case
Nevermind, how the Queries behave, if CapitalID is NULL.....
Remember: TS has the Countries/Cities only as a Scenario

Yeah, Trigger are not everybodies cup of tea, since pretty much everyone would do such a "Check" from the Frontend.
Ah well....

EDIT: Let's take TS' original Setup: Cities being Master to Countries, with a CapitalID as ForeignKey in Countries.
City "Amsterdam" is the Capital of Country "Netherlands".
Let's say, he has DRI with ON DELETE CASCADE on that FK.

Now let's nuke Amsterdam.... it get's deleted.... and deletes the whole Country....
q.e.d.

[rvk]

But i still don't like that "CapitalID" in "Countries"-Table.
Kind of like a Foreign Key pointing back to Cities...

Haha, I kind of like it like that.
Because with a Capital flag (boolean) you can accidentally get multiple capitals per country (or you need to set a constraint on that).
Plus it takes more space (a INTEGER per City) while having a foreign key pointing back is just an INTEGER per country.
Imagine the real tables have millions of entries (because this was just an example).

In my eyes there is nothing wrong with pointing back to cities inside country, especially because it's just ONE field/city specific per country.

Of course... you could also normalize this further and make a third table CAPITALS.

Code: SQL  [Select][+][-]

1. CREATE TABLE CAPITALS
2.   ID INTEGER PRIMARY KEY,
3.   Country_ID INTEGER NOT NULL,
4.   City_ID INTEGER NOT NULL,
5.   FOREIGN KEY (Country_ID) REFERENCES Countries(ID) ON DELETE CASCADE,
6.   FOREIGN KEY (City_ID) REFERENCES Cities(ID) ON DELETE CASCADE
7. );

BTW ON UPDATE CASCADE  shouldn't be needed for ID foreign keys because ID can't (or shouldn't) change, you only use those for CHAR fields

But to do this further normalization, for just one field/entity, I find that a bit of overkill

Nevermind, how the Queries behave, if CapitalID is NULL.....
Remember: TS has the Countries/Cities only as a Scenario

I have the same similar constructions in my own CRM and because that field is just one field, being NULL doesn't matter in the country table when joining (same as some other field being NULL).

But yes, you always need to mind NULL values, especially in JOINs... but that kind of becomes second nature

[rvk]

AI.... !!! I stand corrected.
A country CAN have multiple capitals.

> Can a country have two capitals
> Yes, some countries have more than one capital. These are often referred to as dual or multiple capitals. For example, South Africa has three capitals: Pretoria (executive), Bloemfontein (judicial), and Cape Town (legislative). Another example is Bolivia, which has two capitals: Sucre (constitutional) and La Paz (seat of government). The choice of having multiple capitals is usually based on historical, political, or administrative reasons.

Apparently you can have a "administrative capital", a "legislative capital" and a "judicial capital".

So it's kind of how you define the word capital, which can also differ per language... (I've always only considered judicial capitals).

[Zvoni]

AI.... !!! I stand corrected.
A country CAN have multiple capitals.

> Can a country have two capitals
> Yes, some countries have more than one capital. These are often referred to as dual or multiple capitals. For example, South Africa has three capitals: Pretoria (executive), Bloemfontein (judicial), and Cape Town (legislative). Another example is Bolivia, which has two capitals: Sucre (constitutional) and La Paz (seat of government). The choice of having multiple capitals is usually based on historical, political, or administrative reasons.

Apparently you can have a "administrative capital", a "legislative capital" and a "judicial capital".

So it's kind of how you define the word capital, which can also differ per language... (I've always only considered judicial capitals).

In that case we're back to my setup.

Yes, agreed on the Constraint, but that's not an easy one in SQLite (as compared to the "big" DBMS), since SQLite doesn't support UDF's:
Such a "Constraint" can be implemented with a BEFORE INSERT/UPDATE Trigger.
Untested
CASE WHEN (SELECT SUM(IsCapital) /*As SumCap*/ FROM Cities GROUP BY Country_ID)>2 THEN SELECT RAISE(ABORT, 'Uh. No more than two Capitals per Country') END

[rvk]

In that case we're back to my setup.

Yes, but in that case you also might want to define if its a administrative, legislative or judicial capital 
(maybe with 1, 2 and 3 in the Capital field )

Ah well... it's all theory because these tables aren't real anyway 

[Zvoni]

In that case we're back to my setup.

Yes, but in that case you also might want to define if its a administrative, legislative or judicial capital 
(maybe with 1, 2 and 3 in the Capital field )

Ah well... it's all theory because these tables aren't real anyway 

Agreed.
and a "beautiful" example why you should think on your data-structures BEFORE starting to create tables and what not

To get back to TS initial "Problem"

Each item in the countries table will only ever have one item from the cities table.

That's a "1:1/0"-relation, which only makes sense to separate into 2 tables, if you have, say, BLOBS, or any other data you have to separate because of "forcing" factors (Disk-Space --> Keyword "Sharding" resp. "partitioning")

If his quote were the other way around ("Cities can have multiple Countries"), then it would be a "1:m"-relation, which is daily business

[rvk]

If his quote were the other way around ("Cities can have multiple Countries"), then it would be a "1:m"-relation, which is daily business

Actually... even that is possible 

Baarle in the Netherlands lies in both the Netherlands and Belgium

You could argue that it is Baarle-Nassau and Baarle-Hertog but Baarle itself is a village in two countries. But there are probably more examples. Question becomes if you would need to take that into account for your own program. (People could get around that problem with just adding both as local authority names.

https://nl.m.wikipedia.org/wiki/Baarle