SQLite foreign_keys issue

In this forum we will discuss things relating the ZEOSLib 6.6.x stable versions

Moderators: gto, EgonHugeist

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

SQLite foreign_keys issue

Postby jjeffman » 04.07.2013, 21:34

Hello,

I am using Zeos to connect to a SQLite database ( protocol=sqlite-3 ).

According to SQLite website http://www.sqlite.org/pragma.html#pragma_foreign_keys SQLite do not enable foreign keys by default, we need to enable it on every connection running a SQL command

Code: Select all

"PRAGMA foreign_keys = ON"
I am not having success. The code bellow always pops up a message showing "0" (OFF):

Code: Select all

void __fastcall TLDB::CadConnAfterConnect(TObject *Sender)
{
  QService->SQL->BeginUpdate();
  QService->SQL->Clear();
  QService->SQL->Add("PRAGMA foreign_keys = 1");
  QService->SQL->EndUpdate();
  QService->ExecSQL();
  QService->SQL->BeginUpdate();
  QService->SQL->Clear();
  QService->SQL->Add("PRAGMA foreign_keys");
  QService->SQL->EndUpdate();
  QService->Open();
  if( !QService->Eof && !QService->FieldByName("foreign_keys")->IsNull )
    ShowMessage( QService->FieldByName("foreign_keys")->AsString );
<cut>...</cut>

Is there another way of setting up SQLite to enable foreign keys ? I am deleting parent rows and no error message are being raised.

Thanks a lot.

Kind regards.
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Postby marsupilami » 05.07.2013, 08:00

Hello jjeffman,

trying to set the pragma from a query will not work because it cannot be set when there is a pending transaction in SQLite:
SQLite PRAGMA foreign_keys documentation wrote:This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

TZQueries always operate within a transaction.

I wrote a small patch to enable setting the pragma from the TZConnection properties. So doing ZConnection->Properties->Add("foreign_keys=1") will enable the foreign_keys pragma, whereas ZConnection->Properties->Add("foreign_keys=0") will disable the pragma. Having no foreign_keys line will leave SQLite to its default behaviour where the pragma can be set at compile time.

Could you please apply the patch and see if it solves your problems?
Best regards,

Jan
You do not have the required permissions to view the files attached to this post.

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

I am not whitin a transaction

Postby jjeffman » 05.07.2013, 15:31

Hello marsupilami,

As a matter of fact I am not whitin a transaction, I have just connected to the SQLite database.

The TZConnection::AutoCommit is set to false and I have not started an explicit transaction, so I can not undrstand why the SQL commando does not work.

Kind regards.
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Postby marsupilami » 06.07.2013, 08:37

Hello jjeffman,

it seems to be a Zeos decision - at least for SQLite - but when you open a connection, transaction support is started immediately. Have a look at the file ZDbcSqLite.pas. One of the last things, the procedure TZSQLiteConnection.Open does, is a call to the procedure StartTransactionSupport. Similar things happen for PostgeSQL and Firebird. I would not be surprised if it were true for all DBC drivers. So I think this has been a design decision by the Zeos developers - as soon as you open a connection, you are in a transaction.
This is why my patch modifies TZSQLiteConnection.Open to check for a parameter foreign_keys and executes the corresponding pragma before transaction support has ben started.
So - to get back to my original question - does the patch solve your problem?
Best regards,

Jan

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

I am not sure

Postby jjeffman » 06.07.2013, 17:46

Hello marsupilami,

I am not sure because no exception was raised when I have pressed the delete button of my application, although the record was not deleted.

Kind regards.
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

jjeffman
Senior Boarder
Senior Boarder
Posts: 56
Joined: 25.08.2005, 12:40
Location: Porto Alegre
Contact:

Your patch is working fine

Postby jjeffman » 08.07.2013, 19:50

marsupilami,

Your patch is working fine, and the exceptions are being raised properly.

I have forgotten of compiling the Zeos components, that's why the application was not raising any exception.

Kind regards
Jayme Jeffman Filho

DBA, Software Engineer
Sul Engenharia e Sistema Ltda

http://www.sulenge.com.br

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Postby marsupilami » 10.07.2013, 07:58

Hello jjeffman,

thank you for the reply. :)
Best regards,

Jan

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 13.07.2013, 00:19

marsupilami,

Jan can you apply the patch to 6.6 and 7.1? It's testing save.

Hey enbetween we need a list for such possible parameters... (:
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Postby marsupilami » 13.07.2013, 11:06

Hello EgonHugeist,

the patch cannot be directly applied to 7.1. But I will supply a similar patch for 7.1 in the upcoming week :-)
I have an Idea on how to support this kind of parameters in a more generic way but it will take some more time to work this out in a way that I can present here ;o)
Best regards,

Jan

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Postby marsupilami » 13.07.2013, 18:38

Hello EgonHugeist,

I unexpectedly had some time to do the patch for Zeos 7.0. I think 7.0 should be modified too because otherwise it will not easily be possible to migrate from 6.6 to 7.0 if anybody chooses to do so.
Best regards,

Jan
You do not have the required permissions to view the files attached to this post.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Re: SQLite foreign_keys issue

Postby EgonHugeist » 12.09.2013, 10:04

Hello Jan,

patch applied. R2730 \testing-7.1 SVN

Thank you...
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

mac_srv
Fresh Boarder
Fresh Boarder
Posts: 21
Joined: 23.01.2015, 00:04

Re: SQLite foreign_keys issue

Postby mac_srv » 11.12.2015, 18:13

Hi,

Just for confirmation using C++Builder:

Code: Select all

ZConnection->Properties->Add("foreign_keys=1");


will activate foreign keys pragma with SQLite. But:

Code: Select all

ZConnection->Properties->Add("foreign_keys=ON");


will not.

Tested with ZeosLib 7.2.0 Beta 3609, C++Builder XE2 Pro, SQLite 3.8.11.1.

Best,

Marcelo.
Last edited by mac_srv on 04.01.2016, 21:22, edited 1 time in total.

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Re: SQLite foreign_keys issue

Postby marsupilami » 04.01.2016, 19:20

Hello Marcelo,

this is strange because as far as I understand the source code, Zeos should behave exactly the opposite way. ON is not a defined value for a boolean parameter.
Hmmm - I will have to check this...
I added the expected behaviour to the driver parameters Wiki page nonetheless and added a reference to your post. See https://sourceforge.net/p/zeoslib/wiki/ ... arameters/

Thank you and best regards,

Jan

mac_srv
Fresh Boarder
Fresh Boarder
Posts: 21
Joined: 23.01.2015, 00:04

Re: SQLite foreign_keys issue

Postby mac_srv » 04.01.2016, 21:20

Hi Jan,

Sorry, you are right.

I was testing a "ON DELETE CASCADE" and with "foreign_keys=ON" seemed to be working, but I think I misleading something when checking it with sqlite3.exe command line tool.

I've just retested again and "foreign_keys=1" works.

On the other hand, maybe these parameters ask some standardization, e.g., the "AutoEncodeStrings=ON " when setting AutoEncodeStrings to True into Object Inspector.

Tested with ZeosLib 7.2.0 Beta 3609, C++Builder XE2 Pro, SQLite 3.8.11.1.

I'll try do delete or correct the previous comment.

Sorry.

Marcelo.

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Re: SQLite foreign_keys issue

Postby marsupilami » 06.01.2016, 11:02

Hello Marcelo,

thank you for your feedback. I adjusted the sourceforge wiki :-)
Best regards,

Jan


Return to “6.6 - stable”

Who is online

Users browsing this forum: No registered users and 1 guest