Re: [sqlite] Result set column names

[Adrian Ho]

On 9/12/19 3:45 PM, Graham Holden wrote:
> Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
> wrote:
>
>> The SQL-92 standard actually says:
>>
>> |Syntax Rules
>> |
>> |9) Case:
>> |
>> |   b) If the i-th  in the  does not
>> | specify an  and the  of that
>> |  is a single , then the
>> |  of the i-th column of the result is C.
>> |
> Presumably the third line of clause (b) of the standard SHOULD have
> read: "is a single  C,", otherwise "is C" has
> nothing to refer to.

C is actually defined in a preceding paragraph:

 5) Let C be some column. Let QS be the . Let
    DCi, for i ranging from 1 to the number of s
    inclusively, be the i-th  simply contained in
    the  of QS. For all i, C is an underlying column
    of DCi, and of any  that identifies DCi, if
    and only if C is an underlying column of the 
    of DCi, or C is an underlying column of the 
    immediately contained in QS.

-- 
Best Regards,
Adrian

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

Re: [sqlite] localtime on current_time differs from localtime on current_timestamp

[Adrian Ho]

On 26/9/19 11:51 PM, Keith Medcalf wrote:
> So, when you ask for time(current_time, 'localtime') you are saying to
> get the current utc datetime, discard the date part, then assume that
> the date part is 2000-01-01 with that time, then compute the
> "localtime" for that UTC time, and then discard the date part and
> return the result. So the answer is correct, but for 2000-01-01
> according to the whims of the politicians who set the localtime rules
> at that time (and whether or not your OS knows how to compute that,
> Windows does not, for example). 

I must remember that phrase, "whim of the politicians". Such a beautiful
hand-wavy description of perfectly mundane issues like daylight saving time.

Given the magnitude of the difference (1 hour) and the seasonal
differences between Jan 1 and the date of the original question (Sep
26), I'd surmise this was the most likely cause.

-- 
Best Regards,
Adrian

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

Re: [sqlite] specify CHECK clause to date text field

[Adrian Ho]

On 14/8/19 8:46 PM, Adrian Ho wrote:
> On 14/8/19 8:33 PM, Clemens Ladisch wrote:
>> CREATE TABLE t (
>>   date date  CHECK (date = date(date, '+0 days'))
>> );
> Sadly, this isn't sufficient for guarding against malformed dates like
> '2019-02-00' and '2019-02-1' that the OP listed, because the CHECK
> expression in those cases resolves to NULL, which does *not* signal a
> constraint violation.
>
> Instead, you have to "round-trip" the date conversion manually, and
> check that the intermediate Julian conversion IS NOT NULL, for this
> CHECK to be truly effective.
Or use the IS operator instead, which slipped my mind. 8-)

-- 
Best Regards,
Adrian

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

Re: [sqlite] specify CHECK clause to date text field

[Adrian Ho]

On 14/8/19 8:47 PM, no...@null.net wrote:
>
> CREATE TABLE table_a(
> dt TEXT -- NOT NULL if you like
> CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
> );

Sorry, that 'localtime' qualifier is a non-starter; that will throw a
"non-deterministic function in index expression or CHECK constraint"
error
(https://www.sqlite.org/deterministic.html#special_case_processing_for_date_time_functions):

The built-in date and time functions of SQLite are a special case.
These functions are usually considered deterministic. However, if
these functions use the string "now" as the date, or if they use the
localtime modifier or the utc modifier, then they are considered
non-deterministic. Because the function inputs are not necessarily
known until run-time, the date/time functions will throw an
exception if they encounter any of the non-deterministic features in
a context where only deterministic functions are allows.


-- 
Best Regards,
Adrian

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

Re: [sqlite] specify CHECK clause to date text field

[Adrian Ho]

On 14/8/19 8:47 PM, Richard Hipp wrote:
> On 8/14/19, Adrian Ho  wrote:
>> Here's a Dirty Little Secret: All the SQLite date functions are centered
>> around strftime(), which is not implemented in a strictly correct sense
>> in *every* Unix-like platform I've seen.
> Not true.
>
> SQLite implements its own date and time computations, based on
> algorithms taken from Astronomical Algorithms, 2nd Edition, 1998,
> ISBN 0-943396-61-1. See https://sqlite.org/src/file/src/date.c for the
> latest source code.
>
> An obscure exception is if you compile with
> -DSQLITE_OMIT_DATETIME_FUNCS.  In that case SQLite does invoke the
> system strftime() routine as a fallback implementation for the
> keywords CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP.  But this
> only happens when you use the obscure -DSQLITE_OMIT_DATETIME_FUNCS
> compile-time option. I am aware of nobody who actually does that.
Apologies, I did indeed missing the #ifdef SQLITE_OMIT_DATETIME_FUNCS
around the currentTimeFunc() definition.

-- 
Best Regards,
Adrian

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

Re: [sqlite] specify CHECK clause to date text field

[Adrian Ho]

On 14/8/19 8:33 PM, Clemens Ladisch wrote:
> CREATE TABLE t (
>   date date  CHECK (date = date(date, '+0 days'))
> );

Sadly, this isn't sufficient for guarding against malformed dates like
'2019-02-00' and '2019-02-1' that the OP listed, because the CHECK
expression in those cases resolves to NULL, which does *not* signal a
constraint violation.

Instead, you have to "round-trip" the date conversion manually, and
check that the intermediate Julian conversion IS NOT NULL, for this
CHECK to be truly effective.

-- 
Best Regards,
Adrian

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

Re: [sqlite] specify CHECK clause to date text field

[Adrian Ho]

On 12/8/19 10:59 PM, Martin wrote:
> sqlite> .version
> SQLite 3.29.0 2019-07-10 17:32:03 
> fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
> zlib version 1.2.11
> clang-10.0.1
> sqlite> select date('2019-02-00');  -- null
>
> sqlite> select date('2019-02-01');  -- ok
> 2019-02-01
> sqlite> select date('2019-02-1' );  -- null
>
> sqlite> select date('2019-02-29');  -- not a leap year
> 2019-02-29
> sqlite> select date('2019-02-31');  -- ?
> 2019-02-31
> sqlite> select date('2019-02-32');  -- null
>
> sqlite> .quit
>
> It seems the date function does not check that the date is valid, only the 
> format.
> Regardless of month it accepts day numbers from '01' to '31'.
Here's a Dirty Little Secret: All the SQLite date functions are centered
around strftime(), which is not implemented in a strictly correct sense
in *every* Unix-like platform I've seen. SQLite at least does a simple
range check on the day-of-month; using your platform's strftime() C
function, the equivalent of 2019-02-1234 is perfectly legal, and gives
you the date that's 1233 days after Feb 1 2019.
> Consequently, I would appreciate any advice on the preferred way to specify a
> CREATE TABLE .. CHECK clause
> to guard inserting a -mm-dd date into a text field.

Off the top of my head, simply "round-trip" the date string to Julian
days and back again, then compare the two dates. Of course, you should
first check that the Julian conversion succeeded.

=

$ cat test.sql

CREATE TABLE t(a TEXT CONSTRAINT valid_date CHECK (

  strftime('%J', a) IS NOT NULL AND a = date(strftime('%J', a)))

);

INSERT INTO t VALUES('2019-02-00');
INSERT INTO t VALUES('2019-02-01');
INSERT INTO t VALUES('2019-02-1');
INSERT INTO t VALUES('2019-02-28');
INSERT INTO t VALUES('2019-02-29');
INSERT INTO t VALUES('2019-02-30');
INSERT INTO t VALUES('2019-02-31');
INSERT INTO t VALUES('2019-02-32');
SELECT * FROM t;


$ sqlite3 < test.sql

Error: near line 2: CHECK constraint failed: valid_date
Error: near line 4: CHECK constraint failed: valid_date
Error: near line 6: CHECK constraint failed: valid_date
Error: near line 7: CHECK constraint failed: valid_date
Error: near line 8: CHECK constraint failed: valid_date
Error: near line 9: CHECK constraint failed: valid_date
2019-02-01
2019-02-28

=

-- 
Best Regards,
Adrian


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

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

[Adrian Ho]

On 6/8/19 8:55 AM, Keith Medcalf wrote:
> The THREADSAFE parameter merely sets the level of suspenders that you
> wish to have to protect against shoddy programming or
> non-deterministic languages (ie, those that do things such as
> asynchronous garbage collection or destructors).

Or one of the fundamental constants of the development universe:
*changing requirements* that violate long-forgotten assumptions like
"there will be only one".

Been there, stepped on the Lego, got the bloodied T-shirt.

-- 
Best Regards,
Adrian

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

Re: [sqlite] Hidden data

[Adrian Ho]

On 5/8/19 12:33 AM, bitwyse wrote:
> There is a field "bookmarkProperties/description" in the
> "moz_anno_attributes" table but I can't find the corresponding data.

I suspect that's the key right there. I don't have any descriptions
stored in my places.sqlite, but this may surface something for you:

$ sqlite3 places.sqlite
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.

sqlite> .schema moz_annos
CREATE TABLE moz_annos (id INTEGER PRIMARY KEY,place_id INTEGER NOT
NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT
NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER
DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT
0,lastModified INTEGER DEFAULT 0);
CREATE UNIQUE INDEX moz_annos_placeattributeindex ON moz_annos
(place_id, anno_attribute_id);

sqlite> .schema moz_anno_attributes
CREATE TABLE moz_anno_attributes (id INTEGER PRIMARY KEY,name
VARCHAR(32) UNIQUE NOT NULL);

The field names suggest a "foreign key" relationship of sorts between
moz_annos:anno_attribute_id and moz_anno_attribute:id, so...

sqlite> select * from moz_anno_attributes;
3|bookmarkProperties/description
4|URIProperties/characterSet
17|downloads/destinationFileURI
19|downloads/metaData
20|mobile/bookmarksRoot
22|Places/SmartBookmark

sqlite> select content from moz_annos where anno_attribute_id=3;

-- 
Best Regards,
Adrian

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

Re: [sqlite] Safe saving of in-memory db to disk file

[Adrian Ho]

On 15/7/19 11:25 PM, Nelson, Erik - 2 wrote:
> Dominique Devienne wrote on Monday, July 15, 2019 2:41 AM
>
>> That's when you reach for virtual tables (and their "virtual indices").
>> I.e. you keep your data in native data-structures (Boost.MultiIndex in my 
>> case),
>> and just provide a SQLite view of it. Much faster than "pure-in-Memory" with
>> SQLite-managed pager-backed B-tree tables. Steep learning curve, especially
>> for the indexing part, but the results are well worth it IMHO.
>> Which can be freely mixed with "real" tables having "real" indexes (in the 
>> in-memory DB).
> That sounds really intriguing- does it significantly speed up queries coming 
> in through the SQLite engine?  Or the speed bump is only if accessing from 
> the C++-native side?

The whole point of virtual tables is to make something outside an actual
SQLite DB accessible via normal SQL queries within SQLite; you'd still
use the normal access methods (pointer deferencing,  lookups, etc.)
from the native side.

Speedup depends entirely on the efficiency of your implementation, of
course.

> Is there any literature out there or tips you can share that can flatten the 
> learning curve?

The definitive documentation on SQLite virtual tables is here:
https://sqlite.org/vtab.html

A list of implementation examples is here: https://sqlite.org/vtablist.html

Of which, a (relatively) simple example of exposing a C array is
described here: https://sqlite.org/carray.html

And whose source code is in your SQLite source distribution under
ext/misc/carray.c, or online here:
https://www.sqlite.org/src/artifact?ci=trunk=ext/misc/carray.c


Best Regards,
Adrian

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

Re: [sqlite] How lock is implemented upon write?

[Adrian Ho]

On 5/7/19 3:14 AM, Larry Brasfield wrote:
> I notice that you have ignored repeated requests for insight into why
> you have made your inquiry. People who may be able to help you with
> your objective ask for such information because, often, that leads to
> or permits a more direct solution to your actual problem.
In fact, this phenomenon happens so often, it even has a Wikipedia
entry: https://en.wikipedia.org/wiki/XY_problem

It's surprisingly easy to build a consulting career around asking just
one question, "What problem *X* are you trying to solve, for which you
think this method *Y* is the (probably incorrect) solution?". The real
trick is to summon the patience to guide your clients through the
process of discovering *X*, because they're almost always fixated with *Y*.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Upsert inside trigger?

[Adrian Ho]

On 16/6/19 8:37 PM, ingo wrote:
> Upon creation, the trigger below gives a syntax error near INSERT.
> Without the trigger surrounding it, the query works well.
> The docs give me no clue to what goes wrong.

From https://sqlite.org/lang_createtrigger.html :

*Syntax Restrictions On UPDATE, DELETE, and INSERT Statements Within
Triggers*

The UPDATE , DELETE
, and INSERT
 statements within triggers do not
support the full syntax for UPDATE
, DELETE
, and INSERT
 statements. The following
restrictions apply:

[...]

  * Common table expression are not supported for statements inside of
triggers.

> CREATE TRIGGER IF NOT EXISTS update_balances
> AFTER INSERT ON journal
> BEGIN
> WITH inup(account_id, value_balance, amount_balance) AS (
> --ledgers is a view
>  SELECT ledgers.account_id,
> SUM(ledgers.asset_value),
> SUM(ledgers.asset_amount)
>FROM ledgers
>   WHERE ledgers.account_id = 11
> )
> INSERT INTO balances(account_id, value_balance, amount_balance)
> VALUES (
> (SELECT account_id FROM inup),
> (SELECT value_balance FROM inup),
> (SELECT amount_balance FROM inup)
> )
> ON CONFLICT (balances.account_id)
>   DO UPDATE
> SET value_balance = (SELECT value_balance  FROM inup),
> amount_balance= (SELECT amount_balance FROM inup)
>   WHERE account_id = 11
> ;
> END;
At a minimum, you'll have to factor out that WITH clause.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select within transaction

[Adrian Ho]

On 15/6/19 2:22 AM, Roman Fleysher wrote:
> I have a transaction consisting of two commands: update and select. The idea 
> is to get new state after update:
>
> PRAGMA busy_timeout = 50;
> BEGIN EXCLUSIVE;
> UPDATE OR ROLLBACK t SET c = 5 WHERE ...;
> SELECT  d FROM t WHERE c = 5 AND ...;
> COMMIT;
>
> Is this what will happen:
>
> 1. Wait for the EXCLUSIVE lock. If not enough time, exit with error.
> 2. If lock obtained, attempt to update table t to set c=5.
> 3. Regardless (!?) if step 2 was successful or not, execute SELECT to obtain 
> d. If update failed, then c will not be 5 (it will be old value, different 
> from 5) and output of SEELCT will be empty.
>
> Since ROLLBACK is not an error, I want SELECT to be executed only will update 
> actually happened (not rollback). Because of EXCLUSIVE, I want it to be in 
> one transaction and thus I need some indicator if SELECT was after successful 
> update, not rollback.
>
> Is this what changes() is for?

"Successful update" is rather vague. In some contexts, "no rows changed,
but no error thrown either" might be considered successful.

So there are actually *three* scenarios for your existing code here:

1. UPDATE touches zero rows (WHERE clause matches nothing) -- SELECT
happens.

2. UPDATE touches one or more rows (WHERE clause matches something) --
SELECT happens.

3. UPDATE touches one or more rows, but triggers a constraint violation
in the process -- ROLLBACK kicks in, SELECT doesn't happen.

If you actually want the SELECT to *not* happen in scenario 1, and you
*must* use the SQLite shell instead of a proper language binding like
the Tcl API (https://sqlite.org/tclsqlite.html), then I think you're
stuck. You can sorta get what you want by changing your SELECT statement
as follows:

SELECT  d FROM t WHERE changes() > 0 AND c = 5;

which still runs the SELECT, but returns nothing in scenario 1. It's
just not very efficient, especially for large tables.


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

Re: [sqlite] select within transaction

[Adrian Ho]

On 15/6/19 3:06 AM, Jose Isaias Cabrera wrote:
> Jose Isaias Cabrera, on Friday, June 14, 2019 02:50 PM, wrote...
>
>> Yes, and no.  From what I understand, and have been using it, if
>> something was written to the DB, it will give you a 1.  Otherwise
>> a 0.  But, it is not the amount of fields, just a write. ie.
> This is wrong information.  It does give you the amount of fields updated. Ie.

changes() returns the number of *rows* modified, not fields. See
https://sqlite.org/c3ref/changes.html for the base API function
documentation, which also reveals important details on how it counts
changes in various environments (e.g. triggers, multithreaded updates).


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

Re: [sqlite] Failing SQLite .import returns exit status 0

[Adrian Ho]

On 8/6/19 9:26 PM, Simon Slavin wrote:
> Use
>
> .bail ON
>
> If the shell bails out because of an error, it should be setting the exit 
> code to indicate an error.

Except...it's not bailing on the failed import. Does the SQLite shell
consider failed dot-commands as errors?

$ cat empty.sh

#!/bin/sh

# Check SQLite version
sqlite3 -version

# Run supposedly-failing test
rm -f empty.db empty.csv
echo "" > empty.csv
echo "a, b" >> empty.csv
sqlite3 empty.db \
'.bail on' \
'DROP TABLE IF EXISTS empty;' \
'.mode csv' \
'CREATE TABLE IF NOT EXISTS empty(A, B NOT NULL);' \
'.import empty.csv empty' '.exit'
echo "error code is" $?

# Check contents of the generated files
echo "-"
head empty.csv
echo "-"
sqlite3 empty.db .dump

echo "-"


$ ./empty.sh

3.28.0 2019-04-16 19:49:53
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
empty.csv:1: expected 2 columns but found 1 - filling the rest with NULL
empty.csv:1: INSERT failed: NOT NULL constraint failed: empty.B
error code is 0
-

a, b
-
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE empty(A, B NOT NULL);
INSERT INTO empty VALUES('a',' b');
COMMIT;
-

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

Re: [sqlite] Failing SQLite .import returns exit status 0

[Adrian Ho]

On 8/6/19 10:49 AM, Faheem Mitha wrote:
> rm -f empty.db empty.csv
> echo "" > empty.csv
> echo "a, b" >> empty.csv
> sqlite3 empty.db \
> 'DROP TABLE IF EXISTS empty;' \
> '.mode csv' \
> 'CREATE TABLE IF NOT EXISTS empty(A, B NOT NULL);' \
> '.import empty.csv empty' '.exit'
> echo "error code is" $?
>
> This returns:
>
> sh sqlite_err.sh
>
> empty.csv:1: expected 2 columns but found 1 - filling the rest with NULL
> empty.csv:1: INSERT failed: NOT NULL constraint failed: empty.B
> error code is 0
>
Which version of the SQLite shell are you running? (Run "sqlite3 -version"
to see.) I'm guessing it's a pretty old one.


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

Re: [sqlite] How to set access permissions to protect a database file?

[Adrian Ho]

On 27/5/19 4:51 AM, James K. Lowden wrote:
> On Sun, 26 May 2019 19:52:29 +0800
> Adrian Ho  wrote:
>
>> Finally, create a "reading_room" script that your users will run:
>>
>> #!/usr/bin/env bash
>>
>> sudo -u reading_room /path/to/reading_room.tcl
> This script is more efficient and portable:
>
> #! /bin/sh
> sudo -u reading_room /path/to/reading_room.tcl
True, though the space after your shebang reminded me of this:
https://www.in-ulm.de/~mascheck/various/shebang/#blankrequired
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to set access permissions to protect a database file?

[Adrian Ho]

On 26/5/19 9:10 PM, Graham Holden wrote:
> You should probably also make sure that users cannot alter the tcl
> file through which they access the database file; probably something
> like: 
>
> chown reading_room /path/to/reading_room.tcl
> chmod 644 /path/to/reading_room.tcl

Good point. In fact, since most Linux distros (including Debian, I
think) create a dedicated eponymous group by default for each new user,
this can be tightened further:

chown reading_room /path/to/reading_room.tcl
chgrp aho /path/to/reading_room.tcl
chmod 560 /path/to/reading_room.tcl

That leaves me able to edit the script without fancy sudo footwork,
while ensuring that everyone (including me) needs to sudo as user
"reading_room" to actually run it.


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

Re: [sqlite] How to set access permissions to protect a database file?

[Adrian Ho]

On 27/5/19 12:43 AM, Luuk wrote:
>
> On 26-5-2019 13:52, Adrian Ho wrote:
>> On 26/5/19 7:49 AM, Markos wrote:
>>> I made a program (reading_room.tcl), with Sqlite running on Debian 9,
>>> to control the books of a reading room.
>>>
>>> I implemented an authentication system for common users and
>>> administrator users in the reading_room.tcl program.
>>>
>>> Now I want that any user logged in the Linux be able to run the
>>> program reading_room.tcl, which will access the database (books.db)
>>>
>>> But I want to protect the file books.db so that only the the program
>>> reading_room.tcl can access the books.db file (to read or write). But
>>> that no user could delete or write to the file books.db (only the
>>> program reading_room.tcl)
>> The standard Unix permissions/ACLs architecture doesn't support this use
>> case directly.
>
> Can you give some more information on this, because it seems to work
> as i excpect it to:
>
> Database is 'owned' by user 'luuk', trying to access via 'luuk2', both
> users are in the group 'users':
>
> luuk2@opensuse1:/home/luuk/temp> whoami
> luuk2
> luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
> -r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
> luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> select * from test;
> 1
> 2
> sqlite> insert into test values(3);
> Error: attempt to write a readonly database
> sqlite> .q
> luuk2@opensuse1:/home/luuk/temp>
>
The OP wants *all users* to be able to update (write) the DB via the Tcl
script reading_room.tcl, but *not* by (say) running the SQLite shell or
something else. In your setup, as long as a specific user has write
permissions, *every program* the user runs can write to the DB.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to set access permissions to protect a database file?

[Adrian Ho]

On 26/5/19 5:26 PM, Luuk wrote:
>
> On 26-5-2019 01:49, Markos wrote:
>> Now I want that any user logged in the Linux be able to run the
>> program reading_room.tcl, which will access the database (books.db)
>>
>> But I want to protect the file books.db so that only the the program
>> reading_room.tcl can access the books.db file (to read or write). But
>> that no user could delete or write to the file books.db (only the
>> program reading_room.tcl)
> If you have read, and used, this:
> https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt than
> you should know the answer ;)

WARNING: The documentation seems to suggest that an SQLite library/shell
compiled without SQLITE_USER_AUTHENTICATION still has full access to the
DB. A quick build and test seems to confirm this:

$ ./sqlite3_with_user_auth ~/tmp/test_userauth.db3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> .user help
Usage: .user login|add|edit|delete ...
sqlite> .user add aho testing yes
sqlite> create table test(x int);
sqlite> insert into test values (1);
sqlite> select * from test;
1
sqlite>

Now let's see if an SQLite shell that doesn't do user auth can muck with
this DB:

$ sqlite3 ~/tmp/test_userauth.db3

sqlite> .user help
Error: unknown command or invalid arguments:  "user". Enter ".help" for help
sqlite> .schema
CREATE TABLE sqlite_user(
  uname TEXT PRIMARY KEY,
  isAdmin BOOLEAN,
  pw BLOB
) WITHOUT ROWID;
CREATE TABLE test(x int);
sqlite> select * from sqlite_user;
aho|1|$▒iP}▒�m��
sqlite> select * from test;
1
sqlite> insert into test values (2);
sqlite> select * from test;
1
2

Uh oh...

> Otherwise set access permissions on the database  (use: 'man chmod'
> and/or 'man chown', to find out how to do that under Debian 9)

To give *any* user access to the DB *only* via reading_room.tcl, as the
OP requested, access permissions aren't sufficient by themselves. You'd
also need to force all users to run reading_room.tcl as the DB's owner,
i.e. something like "sudo".

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

Re: [sqlite] How to set access permissions to protect a database file?

[Adrian Ho]

On 26/5/19 7:49 AM, Markos wrote:
> I made a program (reading_room.tcl), with Sqlite running on Debian 9,
> to control the books of a reading room.
>
> I implemented an authentication system for common users and
> administrator users in the reading_room.tcl program.
>
> Now I want that any user logged in the Linux be able to run the
> program reading_room.tcl, which will access the database (books.db)
>
> But I want to protect the file books.db so that only the the program
> reading_room.tcl can access the books.db file (to read or write). But
> that no user could delete or write to the file books.db (only the
> program reading_room.tcl)

The standard Unix permissions/ACLs architecture doesn't support this use
case directly. A relatively simple and bulletproof way to achieve what
you want is to use sudo to get everyone running reading_room.tcl as a
separate (non-login) user.

As root, run "useradd reading_room", then "visudo" to add the following
line to /etc/sudoers:

ALL    ALL = (reading_room) /path/to/reading_room.tcl

Then, "chown reading_room /path/to/books.db" and "chmod 600
/path/to/books.db" to ensure that only user "reading_room" can access
the DB.

Finally, create a "reading_room" script that your users will run:

#!/usr/bin/env bash

sudo -u reading_room /path/to/reading_room.tcl


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

Re: [sqlite] Documentation is lying to me

[Adrian Ho]

On 27/2/19 10:06 AM, Joshua Thomas Wise wrote:
> In this SQLite3 documentation (https://www.sqlite.org/pragma.html#pragfunc), 
> it says that information_schema could be implemented by doing something like 
> this:
>
> ATTACH ':memory:' AS 'information_schema';
> CREATE VIEW information_schema.schemata(schema_name) AS
> SELECT name FROM pragma_database_list();
>
> However, when attempting to do this, we get an error: "no such table: 
> information_schema.pragma_database_list”.

What does:

SELECT sqlite_version();

return? From the link you posted:

> The table-valued functions for PRAGMA feature was added in SQLite version 
> 3.16.0 (2017-01-02). Prior
versions of SQLite cannot use this feature.

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

Re: [sqlite] can you speed this query up?

[Adrian Ho]

On Tue, May 23, 2006 at 08:50:56AM +0200, A. Pagaltzis wrote:
> * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]:
> > What you have to do is:
> > 
> >SELECT qi, ri, drl, score
> >  FROM ...
> > WHERE score=(SELECT max(score) FROM ...)
> 
> Actually, in cases such as this, the easiest approach is to use
> `LIMIT`:
> 
> SELECT qi, ri, drl, score
> FROM ...
> WHERE ...
> ORDER BY score DESC
> LIMIT 1

Only if "cases such as this" is defined as "datasets where only one record
has the maximum score" (which may be the case that Brannon presented -- I
don't recall offhand).  Otherwise, the two queries above are semantically
different and should reasonably be expected to return different results.

- Adrian

Re: [sqlite] Low Level API for SQLite3

[Adrian Ho]

On Mon, May 08, 2006 at 04:06:33PM +1000, Bill KING wrote:
> Anish Enos Mathew wrote:
> > Well, currently I am  doing a mobile project and we are testing the
> > performance of four databases depending on the time it takes to insert
> > or delete or search a particular data from the database. I am using
> > sqlite3 database. I am using sqlite_exec command for doing a particular
> > process in the data base. But the problem with sqlite_exec is that the
> > sqlite engine should parse the select or insert statement and then
> > produces the result. If we are having a low level API for doing the same
> > which doesn't takes parsing time, we could directly do the process
> > rather than giving the select or insert statements. So I would like to
> > know is there any other method which can be used to retrieve or insert
> > data's from the database other than using the select or insert commands.
> >   
> Much better, as far as I know (which isn't much). The sql parser
> generates vdbe bytecodes (see the explain command), which it then
> executes to execute the query as such. You could take a squiz in that
> direction.

Fair warning though: I'm pretty sure it's not a blessed interface, and
can therefore subject change without advance notice (it did, after all,
change significantly in the jump to 3.0).

Anish, if your app is just executing one of a number of predetermined
SQL queries, you may want to use sqlite3_prepare() instead, so that you
pay the parsing cost just a few times rather than on every query, while
retaining the flexibility and general clarity of SQL in your program.

- Adrian

Re: [sqlite] Strange execution times

[Adrian Ho]

On Wed, Feb 22, 2006 at 11:11:45AM +0200, Ulrich Sch?bel wrote:
> I tried your script and got, after a slight modification, quite
> consistent results. When I tried it as is, I got slightly varying
> time results with a peak in the 50 to 100 region. Then I
> commented out all lines concerning the deletion, creation
> and filling to get the pure retrieval times. Drom then on
> I got the following almost invariable results,
> 
> t(1)=538 microseconds per iteration
> t(5)=69.2 microseconds per iteration
> t(10)=39.9 microseconds per iteration
> t(50)=391.48 microseconds per iteration
> t(100)=215.61 microseconds per iteration
> t(500)=73.154 microseconds per iteration
> t(1000)=54.753 microseconds per iteration
> t(5000)=40.9094 microseconds per iteration
> t(1)=39.4558 microseconds per iteration
> 
> The t(1) time is probably due to Tcls bytecode engine, but
> the t(50) and t(100) times are inexplicable, at least for me.
> 
> The 'mini database' you use is, apart from a few additional
> fields, almost identical to the one I used in my previous tests.
> 
> Do you come to similar results?

Nope, mine were a lot more consistent (Centrino 1.6GHz laptop, 512MB RAM):

t(1)=254 microseconds per iteration
t(5)=186.6 microseconds per iteration
t(10)=156.1 microseconds per iteration
t(50)=147.24 microseconds per iteration
t(100)=144.88 microseconds per iteration
t(500)=153.658 microseconds per iteration
t(1000)=142.218 microseconds per iteration
t(5000)=142.7774 microseconds per iteration
t(1)=143.1704 microseconds per iteration

> I have to oppose your statement, Tcl has garbage collection.
> It doesn't, at least in the sense, that it calls a routine to
> collect unused space and free it at arbitrary times, i.e. during
> idle times.

Ah, that's where our perspectives differ.  The definition of "garbage
collection" which I'm operating from (which I believe is the classical
CS one) makes no reference whatsoever to time, other than the implicit
"at some point after it's been identified as garbage".

> Tcl collects its garbage when there is some. Tcls objects are reference
> counted and as soon as this count reaches zero the object is cleaned
> up. This costs time, of course, but it happens each time the garbage
> is due. That has the effect, that garbage collection times are simply
> included in execution times, regularly.

While that's true, I'd point out that unset'ing a large list/array,
or otherwise destroying a heavily-referenced Tcl_Obj, can cause a huge
cascade of derefs/deletes, so it's not necessarily the case that Tcl's
GC times are always predictably similar even across iterations of the
same script.

In any case, this is the SQlite list rather than the tcl-core list, so
returning to the subject at hand...

> It should not produce the peak times I see at t(50) and t(100).

I'm keeping an open mind on this one, since I have no hard data to back
up any conclusion whatsoever.  And since you've subsequently indicated
that it's now a non-issue (esp. in comparison to MySQL), I guess I'll
shut up now.  8-)

- Adrian

Re: [sqlite] Strange execution times

[Adrian Ho]

On Wed, Feb 22, 2006 at 01:13:19AM +0200, Ulrich Sch??bel wrote:
> I don't think it's an interface problem. I'm using Tcl, more or less
> the 'natural' language for sqlite. Tcl doesn't have a garbage
> collection.

Tcl certainly *does* have garbage collection:

<http://wiki.tcl.tk/3096>
<http://wiki.tcl.tk/12144>

> The strangest thing is, I can reproduce this behaviour.
> I'm absolutely clueless. I stumbled over it by coincidence.
> Tried 1000 repetitions, was quite fast, so I tried 1,
> which was even faster. This led me to the (obviously wrong)
> conclusion, that sqlite spends some time parsing the sql.
> Next I tried 100 repetitions, expecting a bit more than
> 76 microseconds. 310 microsecs didn't bother me really,
> I tried the 10 reps expecting even more. Then came the surprise:
> only 67 microsecs.
> 
> My first feeling was, something like a busy disk or so came
> in just when I tried the 100 reps. But the results were reproducible,
> deviating only by a few microseconds.

Try running the following script and see if there's an odd pattern to
the timing variations:

#!/usr/bin/env tclsh
package require sqlite3
if {[file exists aho.db]} {
  file delete aho.db
}
sqlite3 db aho.db
db eval {create table cust_persons ( first_name string, last_name string
)}
db eval {insert into cust_persons values ('Adrian','Ho')}
db eval {insert into cust_persons values ('Thunder','Lightning')}
foreach rounds {1 5 10 50 100 500 1000 5000 1} {
  puts "t($rounds)=[time {db eval {select * from cust_persons where first_name 
= 'Adrian'}} $rounds]"
}
db close

- Adrian