Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Richard Damon]

On 6/17/18 12:55 PM, Markos wrote:
> Good Morning,
>
> I am a beginner in database and I'm developing a Database (with
> Tcl/Tk) to control the loans of books in a reading room.
>
> Some users will have administrator privileges (to make loans, register
> other users etc).
>
> I am creating a table to store login and password to be used in the
> authentication of the admin users (admin_user).
>
> Id_user is the primary key of the user table (all users).
>
> I want to avoid two administrators (admin_user) with the same login
> but for this I am in doubt if I put the two fields as primary key or
> as unique:
>
>
> CREATE TABLE admin_user (
> id_user integer,
> login text NOT NULL,
> password text NOT NULL,
> admin_registration_date INTEGER NOT NULL,
> id_super_admin INTEGER NOT NULL,
> *PRIMARY KEY (id_user, login),*
> *FOREIGN KEY(id_**user**) REFERENCES us**er**(id_us**er**)*);
>
>
> or
>
>
> CREATE TABLE admin_user (
> id_user integer,
> login text NOT NULL,
> password text NOT NULL,
> admin_registration_date INTEGER NOT NULL,
> id_super_admin INTEGER NOT NULL,
> *UNIQUE (id_user, login),*
> *FOREIGN KEY(id_**user**) REFERENCES us**er**(id_us**er**)*);
>
> What are the possible consequences of the two strategies and which
> would be the most appropriate?
>
> Thank you,
>
> Markos 
One first comment, unless you INTEND that one id_user might be accessed
via multiple login names, the id_user should have at least a UNIQUE
constraint, and as others have commented, you really want this to be the
primary key.

You likely also want that a given login name be unique (and as some have
shown, perhaps unique even under case folding), so that would be a
second independent constraint.

UNIQUE(id_user, login) doesn't make each item listed unique, but says
that there that there can be only one record for each unique combination.

-- 
Richard Damon

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

Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Keith Medcalf]

Also note that you probably want your application to store the password as a 
salted-hash, and not as a plain-text password.  Otherwise someone could look up 
the passwords with a text editor ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Sunday, 17 June, 2018 11:30
>To: SQLite mailing list
>Subject: Re: [sqlite] Criteria to define two fields as Primary Key or
>Unique
>
>On 17 Jun 2018, at 5:55pm, Markos  wrote:
>
>> I want to avoid two administrators (admin_user) with the same login
>but for this I am in doubt if I put the two fields as primary key or
>as unique:
>
>Your ideas both have different advantages, but are not the normal way
>to do things.  Try this instead:
>
>CREATE TABLE user (
>   id INTEGER PRIMARY KEY,
>   login text COLLATE NOCASE NOT NULL UNIQUE,
>   password text NOT NULL,
>   admin INTEGER DEFAULT 0,
>   admin_registration_date INTEGER NOT NULL);
>
>The idea is that you have just one account table.  In that you have
>everyone, whether they're superadmin, admin or mundane users.  You
>just have a field saying what kind of account the row represents.
>The 'UNIQUE' keyword for the 'login' field has SQLite create its own
>private index so it can check for uniqueness, and if in other places
>it needs to look up a login name it will use that index.
>
>In the above I have just two values for the admin field.  users are
>'0' admins are '1' (which SQLite interprets as meaning TRUE).
>
>But you seem to have a superadmin status (presumably you).  So you
>might prefer '0' for superadmin, '1' for admin, '2' for normal users.
>Or some other system that suits you.  Maybe even store the words
>'user', 'admin', 'superadmin'.
>
>The use of the 'id' field as INTEGER PRIMARY KEY is a fundamental
>part of the way SQL tables are often used.  Every row has an INTEGER
>key, assigned by the SQL engine (you don't set it yourself,
>automatically incrementing values are set for you).  You never change
>those values.  And when you need to refer to that entry in other
>tables (e.g. a foreign key) you use the correct 'id' value, not
>someone's login name.
>
>See FAQ number 1 in
>
>
>
>Simon.
>___
>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

Re: [sqlite] Criteria to define two fields as Primary Key or Unique

[Simon Slavin]

On 17 Jun 2018, at 5:55pm, Markos  wrote:

> I want to avoid two administrators (admin_user) with the same login but for 
> this I am in doubt if I put the two fields as primary key or as unique:

Your ideas both have different advantages, but are not the normal way to do 
things.  Try this instead:

CREATE TABLE user (
id INTEGER PRIMARY KEY,
login text COLLATE NOCASE NOT NULL UNIQUE,
password text NOT NULL,
admin INTEGER DEFAULT 0,
admin_registration_date INTEGER NOT NULL);

The idea is that you have just one account table.  In that you have everyone, 
whether they're superadmin, admin or mundane users.  You just have a field 
saying what kind of account the row represents.  The 'UNIQUE' keyword for the 
'login' field has SQLite create its own private index so it can check for 
uniqueness, and if in other places it needs to look up a login name it will use 
that index.

In the above I have just two values for the admin field.  users are '0' admins 
are '1' (which SQLite interprets as meaning TRUE).

But you seem to have a superadmin status (presumably you).  So you might prefer 
'0' for superadmin, '1' for admin, '2' for normal users.  Or some other system 
that suits you.  Maybe even store the words 'user', 'admin', 'superadmin'.

The use of the 'id' field as INTEGER PRIMARY KEY is a fundamental part of the 
way SQL tables are often used.  Every row has an INTEGER key, assigned by the 
SQL engine (you don't set it yourself, automatically incrementing values are 
set for you).  You never change those values.  And when you need to refer to 
that entry in other tables (e.g. a foreign key) you use the correct 'id' value, 
not someone's login name.

See FAQ number 1 in



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

[sqlite] Criteria to define two fields as Primary Key or Unique

[Markos]

Good Morning,

I am a beginner in database and I'm developing a Database (with Tcl/Tk) 
to control the loans of books in a reading room.


Some users will have administrator privileges (to make loans, register 
other users etc).


I am creating a table to store login and password to be used in the 
authentication of the admin users (admin_user).


Id_user is the primary key of the user table (all users).

I want to avoid two administrators (admin_user) with the same login but 
for this I am in doubt if I put the two fields as primary key or as unique:



CREATE TABLE admin_user (
id_user integer,
login text NOT NULL,
password text NOT NULL,
admin_registration_date INTEGER NOT NULL,
id_super_admin INTEGER NOT NULL,
*PRIMARY KEY (id_user, login),*
*FOREIGN KEY(id_**user**) REFERENCES us**er**(id_us**er**)*);


or


CREATE TABLE admin_user (
id_user integer,
login text NOT NULL,
password text NOT NULL,
admin_registration_date INTEGER NOT NULL,
id_super_admin INTEGER NOT NULL,
*UNIQUE (id_user, login),*
*FOREIGN KEY(id_**user**) REFERENCES us**er**(id_us**er**)*);

What are the possible consequences of the two strategies and which would 
be the most appropriate?


Thank you,

Markos

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

Re: [sqlite] .timer

[x]

Keith, I posted I was wrong about the limit offset comparison between 23.1 and 
24.0 but I’m unsure where I  gave you the impression the remainder of your post 
on the subject would be news to me. I’m well aware sqlite has to read all the 
rows to reach that offset. My point was that if we were able to specify it 
shouldn’t be buffered (I.e. it did not have to be saved in memory) then we’d 
maybe be spared the missing 86 secs (in the worst case test). I certainly 
wasn’t advocating that having offset  in a 1 table was a good 
strategy.

Thanks for reporting the bug. Hopefully something will be done but I suspect 
it’s been around a long time. I read three threads today complaining about 
sqlite’s cold cache performance. They were many years old going back as far as 
2006.

I disagree about the severity though. It’s devastated my rowid collection 
methods. I’m averse to pagination as I want to see the record number / count at 
the bottom of my grid and the option to move the scrollbar thumb track to any 
point and have that page come up instantly with the record number being known. 
It all worked fine until I tried making the collection from both ends (thus 
making pages near the top / bottom of the table accessible immediately) and 
having them meet in the middle. It was at that point I discovered the 
descending queries were crippling the performance.

Thanks again for all your work on this.

Tom

From: Keith Medcalf
Sent: 17 June 2018 16:02
To: SQLite mailing list
Subject: Re: [sqlite] .timer


No, when you use OFFSET you are reading and discarding rows.  There is no 
difference between:

select * from t;

and discarding all the results except the last row, and,

select * from t limit ,1;

for a table containing 1 rows.  In both cases you have to read the 
entire table in order to find the last row.

However,

select * from t order by _rowid_ desc limit 1;

returns the last row directly rather than requiring the entire table to be read 
and will be fast no matter whether the file is cached or not.

The optimization was for LIMIT without the OFFSET, meaning that you know you 
only want so many rows of the results from the beginning, not at some offset 
(which increases the number of rows included, they are just not returned to you 
-- being discarded -- they still have to be read and processed) by not even 
calculating the rows beyond the LIMIT, if those can be determined and primarily 
affects complicated queries.

I have fed the bug report for the Windows cache handling back to the OEM where 
it will get to Microsoft.  While not severe, it does affect the usage of the 
FileSystem cache and when triggered under memory pressure can force page 
eviction (or swapping) that is unnecessary or even detrimental.  I suspect it 
was not found previously because the specific access pattern for a file is 
rather uncommon -- however that should make it easy to find and fix I should 
think.

However, what you are talking about here is now the difference between doing 
physical I/O and avoiding I/O by reading from a cache in RAM.

I/O is slow and the best way to optimize it (speed it up) is not to do it.  
(Gee that is old, from the 1960's I believe -- cannot remember who to attribute 
it to though).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Sunday, 17 June, 2018 06:35
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>It’s like having a world class soccer team and having to play in a
>league where the players are forced to wear stiletto heels. And it
>gets worse.
>
>Richard was saying he had sped up LIMIT queries in 3.24. I checked
>this out and was running LIMIT queries in sqlite expert (3.23.1) and
>my app (3.24). The former was taking 3 times as long to run the
>queries (not sure how much of that was down to improvements or sqlite
>expert). Anyway, I was getting quite excited until I realised I was
>conducting the test with a warm cache. When I tried the same with a
>cold cache I felt depressed even though the factor of 3 was still
>evident.
>
>The code below will illustrate. In one case it takes 109 secs to
>retrieve one record from a table that only took 77 secs to create.
>Keith mentioned earlier he was unable to test FILE_FLAG_NO_BUFFERING
>because of some setting in sqlite3. While I’ve no experience of the
>ramifications of setting this flag the LIMIT OFFSET looks like it’s
>tailor made for such a setting. Short of getting Microsoft to fix
>this, maybe what we need is a ‘pragma buffering = on/off’ although
>I’ve no idea if that’s possible or the difficulty involved.
>
>SQLite version 3.24.0 2018-06-04 19:24:41
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".ope

Re: [sqlite] .timer

[Keith Medcalf]

No, when you use OFFSET you are reading and discarding rows.  There is no 
difference between:

select * from t;

and discarding all the results except the last row, and,

select * from t limit ,1;

for a table containing 1 rows.  In both cases you have to read the 
entire table in order to find the last row.  

However,

select * from t order by _rowid_ desc limit 1;

returns the last row directly rather than requiring the entire table to be read 
and will be fast no matter whether the file is cached or not.

The optimization was for LIMIT without the OFFSET, meaning that you know you 
only want so many rows of the results from the beginning, not at some offset 
(which increases the number of rows included, they are just not returned to you 
-- being discarded -- they still have to be read and processed) by not even 
calculating the rows beyond the LIMIT, if those can be determined and primarily 
affects complicated queries.

I have fed the bug report for the Windows cache handling back to the OEM where 
it will get to Microsoft.  While not severe, it does affect the usage of the 
FileSystem cache and when triggered under memory pressure can force page 
eviction (or swapping) that is unnecessary or even detrimental.  I suspect it 
was not found previously because the specific access pattern for a file is 
rather uncommon -- however that should make it easy to find and fix I should 
think.

However, what you are talking about here is now the difference between doing 
physical I/O and avoiding I/O by reading from a cache in RAM.  

I/O is slow and the best way to optimize it (speed it up) is not to do it.  
(Gee that is old, from the 1960's I believe -- cannot remember who to attribute 
it to though).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Sunday, 17 June, 2018 06:35
>To: SQLite mailing list
>Subject: Re: [sqlite] .timer
>
>It’s like having a world class soccer team and having to play in a
>league where the players are forced to wear stiletto heels. And it
>gets worse.
>
>Richard was saying he had sped up LIMIT queries in 3.24. I checked
>this out and was running LIMIT queries in sqlite expert (3.23.1) and
>my app (3.24). The former was taking 3 times as long to run the
>queries (not sure how much of that was down to improvements or sqlite
>expert). Anyway, I was getting quite excited until I realised I was
>conducting the test with a warm cache. When I tried the same with a
>cold cache I felt depressed even though the factor of 3 was still
>evident.
>
>The code below will illustrate. In one case it takes 109 secs to
>retrieve one record from a table that only took 77 secs to create.
>Keith mentioned earlier he was unable to test FILE_FLAG_NO_BUFFERING
>because of some setting in sqlite3. While I’ve no experience of the
>ramifications of setting this flag the LIMIT OFFSET looks like it’s
>tailor made for such a setting. Short of getting Microsoft to fix
>this, maybe what we need is a ‘pragma buffering = on/off’ although
>I’ve no idea if that’s possible or the difficulty involved.
>
>SQLite version 3.24.0 2018-06-04 19:24:41
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> .timer on
>sqlite> .open mytemp.db
>sqlite> create table test as -- creates test table with 100 million
>rows
>   ...> with recursive cte(x,y) as
>   ...> (values(1,'012345678901234567890123456789')
>   ...> union all select x+1,y from cte where x<1)
>   ...> select * from cte;
>Run Time: real 77.348 user 68.156250 sys 8.234375
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 13.6147 GBs - post flush = 14.1428 GBs
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 30.722 user 3.515625 sys 17.609375
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 14.2898 GBs - post flush = 14.4573 GBs
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 33.748 user 5.00 sys 18.078125
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 14.4758 GBs - post flush = 14.4825 GBs
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 44.493 user 5.281250 sys 25.625000
>
>Above results unimpressive and inconsistent.
>
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 7.269 user 2.609375 sys 4.656250
>
>sqlite> select rowid from test order by rowid limit ,1;
>1
>Run Time: real 7.230 user 2.859375 sys 4.375000
>
>Above 2 with warm cache.
>
>sqlite> .shell flushmem
>Available RAM - pre flush = 14.497 GBs - post flush = 14.4306 GBs
>sqlite> select rowid from test order by rowid desc limit ,1;
>1
>Run Time: real 103.339 user 4.062500 sys 20.671

Re: [sqlite] .timer

[x]

>Richard was saying he had sped up LIMIT queries in 3.24. I checked this out 
>and was running LIMIT queries >in sqlite expert (3.23.1) and my app (3.24). 
>The former was taking 3 times as long to run the queries (not >sure how much 
>of that was down to improvements or sqlite expert). Anyway, I was getting 
>quite excited >until I realised I was conducting the test with a warm cache. 
>When I tried the same with a cold cache I felt >depressed even though the 
>factor of 3 was still evident.

I’m talking drivel there. Having checked I must’ve been doing the sqlite expert 
queries on a cold cache and the ones in my app on a warm cache.

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

Re: [sqlite] .timer

[x]

It’s like having a world class soccer team and having to play in a league where 
the players are forced to wear stiletto heels. And it gets worse.

Richard was saying he had sped up LIMIT queries in 3.24. I checked this out and 
was running LIMIT queries in sqlite expert (3.23.1) and my app (3.24). The 
former was taking 3 times as long to run the queries (not sure how much of that 
was down to improvements or sqlite expert). Anyway, I was getting quite excited 
until I realised I was conducting the test with a warm cache. When I tried the 
same with a cold cache I felt depressed even though the factor of 3 was still 
evident.

The code below will illustrate. In one case it takes 109 secs to retrieve one 
record from a table that only took 77 secs to create. Keith mentioned earlier 
he was unable to test FILE_FLAG_NO_BUFFERING because of some setting in 
sqlite3. While I’ve no experience of the ramifications of setting this flag the 
LIMIT OFFSET looks like it’s tailor made for such a setting. Short of getting 
Microsoft to fix this, maybe what we need is a ‘pragma buffering = on/off’ 
although I’ve no idea if that’s possible or the difficulty involved.

SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> .open mytemp.db
sqlite> create table test as -- creates test table with 100 million rows
   ...> with recursive cte(x,y) as
   ...> (values(1,'012345678901234567890123456789')
   ...> union all select x+1,y from cte where x<1)
   ...> select * from cte;
Run Time: real 77.348 user 68.156250 sys 8.234375

sqlite> .shell flushmem
Available RAM - pre flush = 13.6147 GBs - post flush = 14.1428 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 30.722 user 3.515625 sys 17.609375

sqlite> .shell flushmem
Available RAM - pre flush = 14.2898 GBs - post flush = 14.4573 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 33.748 user 5.00 sys 18.078125

sqlite> .shell flushmem
Available RAM - pre flush = 14.4758 GBs - post flush = 14.4825 GBs
sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 44.493 user 5.281250 sys 25.625000

Above results unimpressive and inconsistent.

sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 7.269 user 2.609375 sys 4.656250

sqlite> select rowid from test order by rowid limit ,1;
1
Run Time: real 7.230 user 2.859375 sys 4.375000

Above 2 with warm cache.

sqlite> .shell flushmem
Available RAM - pre flush = 14.497 GBs - post flush = 14.4306 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 103.339 user 4.062500 sys 20.671875

sqlite> .shell flushmem
Available RAM - pre flush = 10.1498 GBs - post flush = 14.4109 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 92.210 user 3.812500 sys 15.50

sqlite> .shell flushmem
Available RAM - pre flush = 10.3382 GBs - post flush = 14.5637 GBs
sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 109.676 user 3.796875 sys 21.562500

Woeful results and again inconsistent.

sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 7.405 user 2.062500 sys 5.343750

sqlite> select rowid from test order by rowid desc limit ,1;
1
Run Time: real 7.440 user 2.546875 sys 4.890625

Above 2 with warm cache.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users