Re: [sqlite] how to know the list of pragma that are database related or connection related

[Igor Tandetnik]

Vander Clock Stephane  wrote:
>>> Some pragma are set be connection,
>>> some by database (and all the connection to this database) and
>>> some by the engine (all database and all connections)
>> Could you give an example of this last category? I don't see by what 
>> possible mechanism could a PRAGMA issued in one process
>> connected to one database file, affect a separate process connected to a 
>> different database file. Einstein's spooky action at a
>> distance?  
> 
> for exemple page_size, encoding, auto_vaccum, legacy_file_format

Those are of the second category - they affect one database file that is 
created by the connection on which the pragma is issued. If you then create 
another connection and another file, it won't be affected by these pragmas 
(unless you issue them on the new connection, too).

> From the doc :
> *PRAGMA secure_delete;
> *When there are attached databases and no database is specified in the
> pragma, all databases have their secure-delete setting altered
> The secure-delete setting for newly attached databases is the setting of
> the main database at the time the ATTACH command is evaluated
> 
> so doing this pragma will change the behavior of all the database

All the databases ATTACHed to the current connection. Not all the databases in 
existence. This setting is per connection. Even if you open another connection 
to the same database, it won't have the same behavior.

> and by
> the way all the connections ...

Where do you get that from?

>>> so how to distinguish the pragma that must be call on every connection
>>> or just set one time after the DLL initialization ?
>>
>> I'm not aware of any pragmas that could be set once and somehow magically 
>> take effect on all subsequent connections. How would
>> you even set such a pragma? You need a connection to run PRAGMA statements. 
> 
> like page_size, encoding, auto_vaccum, legacy_file_format, secure_delete
> for exemple ?

Those don't behave the way you seem to think they do. In any case, the 
documentation describes their behavior very carefully and precisely.

> for exemple what about
> cache_size when we use the Share_Cache mode ?

This does seem to be an omission in the documentation. It's not entirely clear 
how cache_size interacts with shared cache.

> so can you confirm me that what i do is good :
> 
> at the initialization :
> sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
> sqlite3_initialize;
> sqlite3_enable_shared_cache(1);
> 
> after before creating any database i do :
> 
> PRAGMA page_size = 512
> PRAGMA encoding = "UTF-8
> PRAGMA legacy_file_format = 0
> PRAGMA auto_vacuum = NONE
> 
> after for "EVERY" new connection on the database(s) i do :
> 
> PRAGMA cache_size = 2000
> PRAGMA count_changes = 0
> PRAGMA journal_mode = MEMORY
> PRAGMA journal_size_limit = -1
> PRAGMA locking_mode = NORMAL
> PRAGMA read_uncommitted = 1
> PRAGMA secure_delete = 0
> PRAGMA synchronous = OFF
> PRAGMA temp_store = MEMORY

Looks right to me.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to know the list of pragma that are database related or connection related

[Vander Clock Stephane]

>> Some pragma are set be connection,
>> some by database (and all the connection to this database) and
>> some by the engine (all database and all connections)
> Could you give an example of this last category? I don't see by what possible 
> mechanism could a PRAGMA issued in one process connected to one database 
> file, affect a separate process connected to a different database file. 
> Einstein's spooky action at a distance?

for exemple page_size, encoding, auto_vaccum, legacy_file_format or also

 From the doc :
*PRAGMA secure_delete;
*When there are attached databases and no database is specified in the 
pragma, all databases have their secure-delete setting altered
The secure-delete setting for newly attached databases is the setting of 
the main database at the time the ATTACH command is evaluated

so doing this pragma will change the behavior of all the database and by 
the way all the connections ...


>> so how to distinguish the pragma that must be call on every connection
>> or just set one time after the DLL initialization ?
> I'm not aware of any pragmas that could be set once and somehow magically 
> take effect on all subsequent connections. How would you even set such a 
> pragma? You need a connection to run PRAGMA statements.

like page_size, encoding, auto_vaccum, legacy_file_format, secure_delete 
for exemple ? but i can say about this because the doc speak about it, 
but most of the time the doc say nothing :( for exemple what about 
cache_size when we use the Share_Cache mode ?

>> for exemple did i need to call the pragma read_uncommitted on every
>> connection
> Yes.

ok, that is clair :)

so can you confirm me that what i do is good :

at the initialization :
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
sqlite3_initialize;
sqlite3_enable_shared_cache(1);

after before creating any database i do :

PRAGMA page_size = 512
PRAGMA encoding = "UTF-8
PRAGMA legacy_file_format = 0
PRAGMA auto_vacuum = NONE

after for "EVERY" new connection on the database(s) i do :

PRAGMA cache_size = 2000
PRAGMA count_changes = 0
PRAGMA journal_mode = MEMORY
PRAGMA journal_size_limit = -1
PRAGMA locking_mode = NORMAL
PRAGMA read_uncommitted = 1
PRAGMA secure_delete = 0
PRAGMA synchronous = OFF
PRAGMA temp_store = MEMORY


is it an good way to do ?

thanks you by advance
stephane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to know the list of pragma that are database related or connection related

[Igor Tandetnik]

Vander Clock Stephane  wrote:
> Some pragma are set be connection,
> some by database (and all the connection to this database) and
> some by the engine (all database and all connections)

Could you give an example of this last category? I don't see by what possible 
mechanism could a PRAGMA issued in one process connected to one database file, 
affect a separate process connected to a different database file. Einstein's 
spooky action at a distance?

> the doc http://www.sqlite.org/pragma.html don't say anything about this

Most pragmas are per connection. Those that affect the format of the database 
file are explicitly called out. For example:

PRAGMA auto_vacuum = ...;

... auto-vacuuming must be turned on before any tables are created...

PRAGMA encoding = ...;

The second and subsequent forms of this pragma are only useful if the main 
database has not already been created.

PRAGMA page_size = bytes;

The page size may only be set if the database has not yet been created.

> so how to distinguish the pragma that must be call on every connection
> or just set one time after the DLL initialization ?

I'm not aware of any pragmas that could be set once and somehow magically take 
effect on all subsequent connections. How would you even set such a pragma? You 
need a connection to run PRAGMA statements.

> for exemple did i need to call the pragma read_uncommitted on every
> connection

Yes.

> i just
> open thought sqlite3_open_V2 or simply can i open a connection,
> execute the pragma read_uncommitted and close the connection and this
> will stay
> for all the future connection on all database ?

No.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users