Re: [sqlite] Feature suggestion / requesst

[Thomas Kurz]

May I ask whether this suggestion has been considered being added to SQlite?


- Original Message - 
From: Clemens Ladisch 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, June 8, 2018, 08:25:25
Subject: [sqlite] Feature suggestion / requesst

Hick Gunter wrote:
>> I've encountered a feature that I think would be awesome:
>> https://www.postgresql.org/docs/9.3/static/dml-returning.html

>> Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING 
>> id;

> What does this do if the INSERT creates multiple rows?

It returns multiple rows.  (Also useful with UPDATE, DELETE.)

> What about inserts generated from trigger programs?

The same as a SELECT in a trigger program.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[sqlite] Missing docs

[Joshua Thomas Wise]

Nowhere in the current documentation does it mention the existence of the 
SQLITE_ENABLE_NORMALIZE compile-time option, and nowhere does it mention that 
sqldark_source_normalized() requires that option to be present.

The docs should probably mention this.

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

Re: [sqlite] missing SQLITE_DEFAULT_TEMP_CACHE_SIZE

[Joshua Thomas Wise]

On Mar 10, 2019, at 1:33 PM, Joshua Thomas Wise  
wrote:

Hello,

The documentation here 
 
(https://www.sqlite.org/tempfiles.html#other_temporary_file_optimizations 
) 
mentions that temporary tables and indexes each use their own page cache, which 
is sized by the SQLITE_DEFAULT_TEMP_CACHE_SIZE compile-time option. However, I 
can’t find any mention of that identifier anywhere in the sqlite3 source code. 
Additionally, that documentation conflicts with this documentation 
 
(https://www.sqlite.org/pragma.html#pragma_cache_size 
) which states that the 
TEMP database always has a cache size of 0. What’s the true story behind page 
caches of temporary files, and how can I control their sizes?

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

Re: [sqlite] Are the 'sqlite3_snprintf()' family protected against SQL injection?

[Richard Hipp]

On 3/14/19, John Smith  wrote:
> For example, if I write function like:
>
> void CreateSQL_SetName( char* buffer, int size, const char* szName,
> const char* szCondition)
> {
> sqlite3_snprintf( size, buffer, "UPDATE my_table SET name='%s' WHERE
> %s", szName, szCondition);
> }
>
> Does SQLite 'sqlite3_snprintf()' processes the strings 'szName' and
> 'szCondition' to verify they do not contain escape sequence that may inject
> other SQL statements into this statement?

It does if you use %q or %Q instead of %s.  See
https://www.sqlite.org/printf.html#percentq

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Are the 'sqlite3_snprintf()' family protected against SQL injection?

[John Smith]

For example, if I write function like:

void CreateSQL_SetName( char* buffer, int size, const char* szName, const 
char* szCondition)
{
sqlite3_snprintf( size, buffer, "UPDATE my_table SET name='%s' WHERE 
%s", szName, szCondition);
}

Does SQLite 'sqlite3_snprintf()' processes the strings 'szName' and 
'szCondition' to verify they do not contain escape sequence that may inject 
other SQL statements into this statement?

Thanks!
John
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Developer questions about the Online Backup API

[Olivier Mascia]

> Le 14 mars 2019 à 09:27, Dan Kennedy  a écrit :
> 
> On 13/3/62 22:51, Simon Slavin wrote:
>> If the source database is changed while the Online Backup API is running, it 
>> returns to the beginning of the database and starts again.  I have a couple 
>> of questions which might be useful, especially if the database is changed 
>> only by the same connection as it performing the backup.
> 
> In that case - when the connection doing the backup is the same as the one 
> that modifies the database - the backup does not restart. Instead, when the 
> connection writes to the original database, any pages that have already been 
> copied into the backup are updated there as well.
> 
> The backup only has to restart when the connection doing the backup and the 
> connection doing the db modification are different connections.
> 
> From the docs for sqlite3_backup_step():
> 
> "If the source database is modified by an external process or via a database 
> connection other than the one being used by the backup operation, then the 
> backup will be automatically restarted by the next call to 
> sqlite3_backup_step(). If the source database is modified by the using the 
> same database connection as is used by the backup operation, then the backup 
> database is automatically updated at the same time."
> 
> https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep

Dan,

It has already been confirmed I think, but just for 100% clarity: if using WAL 
and a default (deferred) transaction has been started, the backup can run, step 
by step in the context of that reader transaction, without blocking writers and 
other (existing or new) readers.  Is that right?  Or could the detection of 
writes by other connections kicks in anyway and force the backup to needlessly 
restart?

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] Developer questions about the Online Backup API

[Clemens Ladisch]

Simon Slavin wrote:
> If the source database is changed while the Online Backup API is
> running, it returns to the beginning of the database and starts again.

The backup API must create a consistent snapshot of the source database,
i.e., the result must be the exact state at some point in time when no
write transaction was active.

> 1) Suppose the first page of the source database which is modified is
>after the point that the backup has reached.  Is it necessary to
>restart ?
> 2) Suppose the first page of the source database which is modified is
>before the point that the backup has reached.  Could the backup not
>return just to that point rather than to the very beginning ?

In the general case, it is not possible to detect which pages have been
changed.

> ... if the database is changed only by the same connection as it
> performing the backup

This would require additional code to track changed pages, and a lock to
prevent other connections from making changes.


If all connections are on the same machine, it should be possible to use
WAL mode.  You can then do the entire backup in a single step without
blocking writers.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users