Best way to set default PRAGMA's in System.Data.SQLite

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Best way to set default PRAGMA's in System.Data.SQLite

Sam Carleton-2
My .net application which uses System.Data.SQLite relies on the
foreign_keys to be enabled and recursive_trigger to be disabled.  In my
searching, I found documentation on DefaultFlags_SQLiteConnection, setting
that in the appSettings.  Here is what it says:

If this environment variable is set [to anything], it will be used by the
System.Data.SQLite.SQLiteConnection class as the default flags for all
opened connections (i.e. when they are not present in the connection
string).

https://system.data.sqlite.org/index.html/artifact?ci=trunk&filename=System.Data.SQLite/Configurations/System.Data.SQLite.dll.config


What I cannot find is what to set to enable foreign keys and disable
recursive triggers.  I tried looking through the source code to get an idea
of what the input should be but the only fruit that is producing is I THINK
the key is simply the pragma name equals value, but I cannot tell how to
separate multiple values, is it a comma, or some other separator?  My
current guess is:

DefaultFlags_SQLiteConnection="recursive_trigger=0,foreign_keys=1"

Is there documentation on this somewhere and I am just missing it?

Pax vobiscum,
Sam Carleton
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Simon Slavin-3
On 1 Jun 2019, at 4:34pm, Sam Carleton <[hidden email]> wrote:

> What I cannot find is what to set to enable foreign keys and disable
> recursive triggers.

Only flags which can be supplied in connection strings can be set in DefaultFlags_SQLiteConnection .  And you cannot set those two settings in connection strings because they are not related to file handling.  Sorry.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Joe Mistachkin-3
In reply to this post by Sam Carleton-2

Sam Carleton wrote:
>
> My .net application which uses System.Data.SQLite relies on the
> foreign_keys to be enabled and recursive_trigger to be disabled.  In my
> searching, I found documentation on DefaultFlags_SQLiteConnection, setting
> that in the appSettings.  Here is what it says:
>

In order to do this, you'll want to execute the PRAGMA statements against
the connections that you open prior to running other SQL.  The flags for
the connection (primarily) control the behavior of System.Data.SQLite and
they (generally) have no relation to PRAGMA statements.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Sam Carleton-2
In reply to this post by Simon Slavin-3
Ok, that is good to know.  If I am not mistaken,  System.Data.SQLite takes
part in the .Net connection pooling,  What is the best way to ensure that
these two features are set correctly?  Does the program simply need to make
sure it issues the pragma after each connection, assuming it is always new?

I do use SQLite in C/C++ application where I have recompiled SQLite DLL to
force these settings.  In looking through the .net code, it looks like it
simply a wrapper around the SQLite dll, Is it possible to have the .Net
application simply use that version of the DLL rather than the one that
came with System.Data.SQLite?

Pax vobiscum,
Sam Carleton


On Sat, Jun 1, 2019 at 1:47 PM Simon Slavin <[hidden email]> wrote:

> On 1 Jun 2019, at 4:34pm, Sam Carleton <[hidden email]> wrote:
>
> > What I cannot find is what to set to enable foreign keys and disable
> > recursive triggers.
>
> Only flags which can be supplied in connection strings can be set in
> DefaultFlags_SQLiteConnection .  And you cannot set those two settings in
> connection strings because they are not related to file handling.  Sorry.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Joe Mistachkin-3

Sam Carleton wrote:
>
> Ok, that is good to know.  If I am not mistaken,  System.Data.SQLite takes
> part in the .Net connection pooling,  What is the best way to ensure that
> these two features are set correctly?  Does the program simply need to
make
> sure it issues the pragma after each connection, assuming it is always
new?
>

System.Data.SQLite actually handles its own connection pooling.  In order to
use it, you'll want to add "Pooling=True;" to your connection strings.  Yes,
the program will need to make sure it issues the PRAGMA statements for each
connection.

>
> I do use SQLite in C/C++ application where I have recompiled SQLite DLL to
> force these settings.  In looking through the .net code, it looks like it
> simply a wrapper around the SQLite dll, Is it possible to have the .Net
> application simply use that version of the DLL rather than the one that
> came with System.Data.SQLite?
>

Yes, there are several ways to do this.  One way is to simply compile the
System.Data.SQLite assembly in such a way that it uses a "standard" SQLite
DLL, via setting the following MSBuild properties:

        /p:UseSqliteStandard=true /p:UseInteropDll=false

It should be noted that System.Data.SQLite requires several SQLite options
be enabled at compile-time to operate correctly, including but not limited
to:

        SQLITE_ENABLE_COLUMN_METADATA=1;SQLITE_THREADSAFE=1;

Alternatively, you can use the default set of MSBuild properties and then
customize the source code for "SQLite.Interop.dll", e.g. in the "interp.c"
source code file.

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Simon Slavin-3
In reply to this post by Sam Carleton-2
On 1 Jun 2019, at 9:54pm, Sam Carleton <[hidden email]> wrote:

> What is the best way to ensure that these two features are set correctly?  Does the program simply need to make sure it issues the pragma after each connection, assuming it is always new?

The simplest way is what you say: execute those two PRAGMA commands immediately after you open the file.  It's never a problem to set those settings even if that's the way things are already set.

You should also make sure you have set a timeout, whether in the connection string or as a third PRAGMA.  In fact setting a timeout is what we normally get this question about.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Sam Carleton-2
In reply to this post by Joe Mistachkin-3
On Sat, Jun 1, 2019 at 7:35 PM Joe Mistachkin <[hidden email]> wrote:

>
> compile the System.Data.SQLite assembly in such a way that it uses a
> "standard" SQLite
> DLL, via setting the following MSBuild properties:
>
>         /p:UseSqliteStandard=true /p:UseInteropDll=false
>

Joe,

I am trying out this approach right now and running into one minor issue,
the following cmd only builds the debug version:

msbuild SQLite.NET.2015.MSBuild.sln /p:UseSqliteStandard=true
/p:UseInteropDll=false

I am assuming I need to set a target to get the release version built, but
/t:Release gives me this error:

...\sqlite-netFx-source\SQLite.NET.2015.MSBuild.sln.metaproj : error
MSB4057: The target "release" does not exist in the project.
[...\sqlite-netFx-source\SQLite.NET.2015.MSBuild.sln]

What should the target be?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Best way to set default PRAGMA's in System.Data.SQLite

Joe Mistachkin-3

Sam Carleton wrote:
>
> I am trying out this approach right now and running into one minor issue,
> the following cmd only builds the debug version:
>
> msbuild SQLite.NET.2015.MSBuild.sln /p:UseSqliteStandard=true
> /p:UseInteropDll=false
>

Have you tried using the "build.bat" tool in the source tree?  It sets
up the right build environment for the solution.  The following commands
should do generally what you want:

        CD /D C:\full\path\to\sqlite\dotnet\Setup
        SET MSBUILD_ARGS=/p:UseSqliteStandard=true /p:UseInteropDll=false
        CALL build.bat ReleaseManagedOnly

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Fwd: Best way to set default PRAGMA's in System.Data.SQLite

Sam Carleton-2
Joe,

I just opened up the build.bat file and saw your name as the author.  I
just wanted to send you a quick little note of immense gratitude for your
personal help and for all the hard work you and the rest of the SQLite
development team do for SQLite.  Independent software vendors such as
myself won't exist if it wasn't for the folks like you.  Thank you, I truly
appreciate it!

Pax vobiscum,
Sam Carleton


---------- Forwarded message ---------
From: Joe Mistachkin <[hidden email]>
Date: Sat, Jun 8, 2019 at 11:48 PM
Subject: Re: [sqlite] Best way to set default PRAGMA's in System.Data.SQLite
To: SQLite mailing list <[hidden email]>



Sam Carleton wrote:
>
> I am trying out this approach right now and running into one minor issue,
> the following cmd only builds the debug version:
>
> msbuild SQLite.NET.2015.MSBuild.sln /p:UseSqliteStandard=true
> /p:UseInteropDll=false
>

Have you tried using the "build.bat" tool in the source tree?  It sets
up the right build environment for the solution.  The following commands
should do generally what you want:

        CD /D C:\full\path\to\sqlite\dotnet\Setup
        SET MSBUILD_ARGS=/p:UseSqliteStandard=true /p:UseInteropDll=false
        CALL build.bat ReleaseManagedOnly

--
Joe Mistachkin

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users