[sqlite] Best way to create multiple tables?
I'm doing an app. that has multiple tables in sqlite. When the app. is run for the first time, or the tables are lost/damaged, it will create the database and all tables. My question is if there is a best way to create 5 tables? I can put al the Python code into one function, pass the path to the database file to multiple functions, or pass the connection to multiple functions. One function is the most contained, but very large! Any suggestions from those that have been there done that (BTDT)? Thanks, Dave Sent with [ProtonMail](https://protonmail.com) Secure Email. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] specify CHECK clause to date text field
Hi,
Using the precompiled CLI for MacOS (10.13.6) I stumbled upon the following to
do
with the date() function:
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'.
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.
Thanks, Martin.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Create multipe tables, many-to-many design
I'm doing an app. that uses sqlite, and has a many-to-many relationship. The areas I need some guidance are: * Best way to create multiple tables the first time the app. is started. * How to create a MTM relationship and add/modify data. I can create tables (Python) by putting the code in discrete functions for each table and passing just the path to the database. I can also create a calling function that gets a connection and passes that. What is the pro-con of each since each seems to work? Better ideas? Some of the tables are related. For example: Hiking_Table Trails_Table Joining_Table - - --- hike_id PK trail_id PK hike_id FK hike_date TEXT trail_name TEXT trail_id FK hike_destination TEXTtrail_rating REAL hike_rating REAL trail_comments TEXT hike_comments TEXT So far, so good. I know how to create the tables. What I am struggling with is how do I insert data into the joining table or don"t I. Somethings I have read suggest that the joining table just contains references, so there is no actual insert. A pointer to information how to do this would be appreciated. As for queries, I think I use joins, but a pointer on how to do this would also be appreciated. Perhaps these topics could be added to the excellent http://www.sqlitetutorial.net/ . Thanks, Dave Sent with [ProtonMail](https://protonmail.com) Secure Email. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Programming methodology (was DEF CON (wasL A license plate of NULL))
On 8/13/19 4:47 PM, Richard Hipp wrote: > On 8/13/19, Jose Isaias Cabrera wrote: >> I see all of you smart programmers using this >> non-column matching behavior, and I ask myself why? > Because that's the way Dennis Richie did it. :-) There are many ways to format code, and many programmers have a strong preference to the way THEY want it. In many ways this choice is a bit like religion, sometimes hard to really explain why, but often the believer has some ideas about it, and the choice is often firmly held and hard to change. I personally like the K&R style, as it is compact and dense, so you can see more code. Some people dislike it for much the same reason. While the braces don't align, the closing brace does align with the beginning of the statement it is closing, and there is nothing else in that column between so scanning up to find it isn't that hard (and then to the end of the line to see the opening brace). If the distance is long, I will add a comment after the closing brace describing the start statement to make it easier to match. Yes, this format makes it harder to see mismatched braces, but by compiling often you get a syntax error with miss-matched braces, and letting your editor find matching braces it tends to be fairly quick to locate it. The key is to compile (or have the editor syntax check) often enough that you can't make two opposing errors like this that hide each other. -- Richard Damon ___ 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
Martin wrote:
> sqlite> select date('2019-02-29'); -- not a leap year
> 2019-02-29
> 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.
sqlite> select date('2019-02-29', '+0 days');
2019-03-01
CREATE TABLE t (
date date CHECK (date = date(date, '+0 days'))
);
(This also ensures that the date is not in Julian day format.)
Regards,
Clemens
___
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
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] specify CHECK clause to date text field
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
> It seems the date function does not check that the date is valid,
> only the format.
I've run into the same issue. Don't remember if it has been raised on
the list, but I have a vague memory that it fell into the WONTFIX
category :-(
> 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.
The following might be instructive:
.version
.width 10 10 2 15 2 21 2
WITH
x(dt)
AS (
VALUES
(NULL),
('2019-02-00'),
('2019-02-01'),
('2019-02-1'),
('2019-02-29'),
('2019-02-30'),
('2019-02-31'),
('2019-02-32')
)
SELECT
x.dt AS "str",
date(x.dt) AS "date(str)",
x.dt IS date(x.dt) AS "IS",
date(x.dt,'utc') AS "date(str,'utc')",
x.dt IS date(x.dt,'utc') AS "IS",
date(x.dt,'localtime') AS "date(str,'localtime')",
x.dt IS date(x.dt,'localtime') AS "IS"
FROM
x
;
-- Output
SQLite 3.22.0 2017-11-27 17:56:14
465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6
str date(str) IS date(str,'utc') IS date(str,'localtime') IS
-- -- -- --- -- - --
NULLNULL1 NULL 1 NULL 1
2019-02-00 NULL0 NULL 0 NULL 0
2019-02-01 2019-02-01 1 2019-01-31 0 2019-02-01 1
2019-02-1 NULL0 NULL 0 NULL 0
2019-02-29 2019-02-29 1 2019-02-28 0 2019-03-01 0
2019-02-30 2019-02-30 1 2019-03-01 0 2019-03-02 0
2019-02-31 2019-02-31 1 2019-03-02 0 2019-03-03 0
2019-02-32 NULL0 NULL 0 NULL 0
I think the above means you can compare the original value with the
localtime equivalent:
CREATE TABLE table_a(
dt TEXT -- NOT NULL if you like
CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
);
Regards,
Mark
--
Mark Lawrence
___
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
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. You can verify the above claim by running "nm" (on many unix systems, or the equivalent on Mac or Windows) on a compiled sqlite3.o file, and seeing that "strftime" appears nowhere in the output, while library routines that SQLite does use like "open", "read", and "malloc" do appear in the output. -- 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] multi-multi join/bridge table, table creation questions
I'm doing a Python app (not that the language really matters - just for context) that uses SQLite to store data. I have a many-to-many (MTM) relationship. After a lot of Googling and reading I have concluded that: >> I need to create the join/bridge table just like all the other tables. In >> other words, it will not be created automatically using some SQL that I have >> yet to understand. True or false? >> In all the examples I have seen, the join/bridge table is populated (INSERT >> INTO) manually - as in not done by SQL. Here is my problem. I will need to >> insert the primary keys as foreign keys into the table. In the examples I >> have seen, they knew what those number were (all 12 of them). I won't have >> that situation as the primary keys from the other tables are AUTO_NUMBER. >> That means I have to query those tables before I can INSERT INTO the >> join/bridge tables. Too much work. Is there a better easier way? How >> about some complete examples? Finally, is there a best way to create a number of tables when an app. is first run? For example, I can have one large function that contains all the SQL to create 6 tables and all the Python to create a connection, cursor, and execute the SQL. Alternatively, I can have functions that pass either the db path, or the connection, to specialized functions that create just one table. What is the pro/con of each? Thanks! Dave Sent with [ProtonMail](https://protonmail.com) Secure Email. ___ 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
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
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
[sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD
sqlite3_interrupt is documented as: “It is safe to call this routine from a thread different from the thread that is currently running the database operation” SQLITE_CONFIG_SINGLETHREAD is documented as: “puts SQLite into a mode where it can only be used by a single thread” Which one wins 😉? i.e. Can we call sqlite3_interrupt from a secondary thread in a SQLITE_CONFIG_SINGLETHREAD environment? (And can we have a doc clarification on this). Secondly, regardless of the above answer - from a technical perspective, sqlite3_interrupt is implemented as: volatile int isInterrupted; /* True if sqlite3_interrupt has been called */ … db->u1.isInterrupted = 1; However, even though it’s a volatile int, it doesn’t have any kind of memory fence around it. So reads and writes to it can be re-ordered out of existence or into undefined behavior. This is probably undesired. - Deon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD
On 8/14/19, Deon Brewis wrote: > sqlite3_interrupt is documented as: > “It is safe to call this routine from a thread different from the thread > that is currently running the database operation” > > SQLITE_CONFIG_SINGLETHREAD is documented as: > “puts SQLite into a mode where it can only be used by a single thread” > > Which one wins The sqlite3_interrupt() interface is intending to stop a long-running query, usually by a single handler in response to the user pressing Ctrl-C or similar. This works regardless of compile-time options. What is your intended use of sqlite3_interrupt() that compile-time options matter? -- 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
Re: [sqlite] specify CHECK clause to date text field
On Wed Aug 14, 2019 at 09:08:26PM +0800, Adrian Ho wrote: > 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): Right you are. I should have checked that properly. Looks like '+0 days' is the right way to go then, still using the IS operator to handle NULLs. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Incorrect query result
Hi, I am experiencing incorrect query result with SQLite 3.25.2 and 3.28. Attached are the database and a Tcl script running 3 queries. The database and the queries have been reduced to the minimum, so they are not really relevant, but demonstrates better where is the problem. Also note that the result is correct using SQLite 3.15.2. The first query fills an array with number of rows returned per "sens" attribute. The second query is the same, but using "WHERE ITI1.sens = 1". The third query is the same as the second, but without the GROUP BY in the WITH clause. The second query returns nothing, which is not expected, as shown in the result of the first query. Thank you and kind regards, Eric ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorrect query result
Alas, the mailing list does not allow attachments. -j > On Aug 14, 2019, at 8:24 AM, Eric Boudaillier > wrote: > > Hi, > > I am experiencing incorrect query result with SQLite 3.25.2 and 3.28. > Attached are the database and a Tcl script running 3 queries. > The database and the queries have been reduced to the minimum, so they are > not really relevant, but demonstrates better where is the problem. > Also note that the result is correct using SQLite 3.15.2. > > The first query fills an array with number of rows returned per "sens" > attribute. > The second query is the same, but using "WHERE ITI1.sens = 1". > The third query is the same as the second, but without the GROUP BY in the > WITH clause. > > The second query returns nothing, which is not expected, as shown in the > result of the first query. > > Thank you and kind regards, > > Eric > ___ > 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] specify CHECK clause to date text field
Converting the date from naive (unknown timezone) format to naive utc or naive
localtime will result in a different date and it will not match for valid dates
depending on the timezone of your computer and the vagaries of the OS localtime
conversions, and the particular time-of-day at which the computation is run,
which will internally vary depending on whether Standard, Daylight, Double
Daylight, or other times happen to be in effect at that locale. Attempting to
add 0 days will simply get back what you put it, round tripped through the
conversion to and from julian (with the current timezone base).
CHECK (dt IS date(dt, '+0 days')) is where I would go.
create table dt
(
dt text check (dt IS date(dt, '+0 days'))
);
insert into dt values (null);
insert into dt values ('2019-02-1');
insert into dt values ('2019-02-28');
insert into dt values ('2019-02-29');
insert into dt values ('2019-02-30');
insert into dt values ('2019-02-31');
insert into dt values ('2019-03-01');
Mutatis mutandis for datetime and time. Note however that things such as the
localization (timezone offset) and fractional seconds will not be permitted or
preserved in those cases.
--
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 no...@null.net
>Sent: Wednesday, 14 August, 2019 06:47
>To: SQLite mailing list
>Subject: Re: [sqlite] specify CHECK clause to date text field
>
>> It seems the date function does not check that the date is valid,
>> only the format.
>
>I've run into the same issue. Don't remember if it has been raised on
>the list, but I have a vague memory that it fell into the WONTFIX
>category :-(
>
>> 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.
>
>The following might be instructive:
>
>.version
>.width 10 10 2 15 2 21 2
>WITH
>x(dt)
>AS (
>VALUES
>(NULL),
>('2019-02-00'),
>('2019-02-01'),
>('2019-02-1'),
>('2019-02-29'),
>('2019-02-30'),
>('2019-02-31'),
>('2019-02-32')
>)
>SELECT
>x.dt AS "str",
>date(x.dt) AS "date(str)",
>x.dt IS date(x.dt) AS "IS",
>date(x.dt,'utc') AS "date(str,'utc')",
>x.dt IS date(x.dt,'utc') AS "IS",
>date(x.dt,'localtime') AS "date(str,'localtime')",
>x.dt IS date(x.dt,'localtime') AS "IS"
>FROM
>x
>;
>
>-- Output
>
>SQLite 3.22.0 2017-11-27 17:56:14
>
>465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6
>str date(str) IS date(str,'utc') IS
>date(str,'localtime') IS
>-- -- -- --- --
>- --
>NULLNULL1 NULL 1 NULL
>1
>2019-02-00 NULL0 NULL 0 NULL
>0
>2019-02-01 2019-02-01 1 2019-01-31 0 2019-02-01
>1
>2019-02-1 NULL0 NULL 0 NULL
>0
>2019-02-29 2019-02-29 1 2019-02-28 0 2019-03-01
>0
>2019-02-30 2019-02-30 1 2019-03-01 0 2019-03-02
>0
>2019-02-31 2019-02-31 1 2019-03-02 0 2019-03-03
>0
>2019-02-32 NULL0 NULL 0 NULL
>0
>
>I think the above means you can compare the original value with the
>localtime equivalent:
>
>CREATE TABLE table_a(
>dt TEXT -- NOT NULL if you like
>CONSTRAINT valid_date CHECK(dt IS date(dt,'localtime'))
>);
>
>Regards,
>Mark
>--
>Mark Lawrence
>___
>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] Fw: multi-multi join/bridge table, table creation questions
Sent with [ProtonMail](https://protonmail.com) Secure Email. ‐‐‐ Original Message ‐‐‐ On Wednesday, August 14, 2019 8:54 AM, dboland9 wrote: > I'm doing a Python app (not that the language really matters - just for > context) that uses SQLite to store data. I have a many-to-many (MTM) > relationship. After a lot of Googling and reading I have concluded that: > >>> I need to create the join/bridge table just like all the other tables. In >>> other words, it will not be created automatically using some SQL that I >>> have yet to understand. True or false? > >>> In all the examples I have seen, the join/bridge table is populated (INSERT >>> INTO) manually - as in not done by SQL. Here is my problem. I will need >>> to insert the primary keys as foreign keys into the table. In the examples >>> I have seen, they knew what those number were (all 12 of them). I won't >>> have that situation as the primary keys from the other tables are >>> AUTO_NUMBER. That means I have to query those tables before I can INSERT >>> INTO the join/bridge tables. Too much work. Is there a better easier way? >>> How about some complete examples? > > Finally, is there a best way to create a number of tables when an app. is > first run? For example, I can have one large function that contains all the > SQL to create 6 tables and all the Python to create a connection, cursor, and > execute the SQL. Alternatively, I can have functions that pass either the db > path, or the connection, to specialized functions that create just one table. > What is the pro/con of each? > > Thanks! > Dave > > Sent with [ProtonMail](https://protonmail.com) Secure Email. ___ 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
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] multi-multi join/bridge table, table creation questions
On 8/14/2019 8:54 AM, dboland9 wrote: I'm doing a Python app (not that the language really matters - just for context) that uses SQLite to store data. I have a many-to-many (MTM) relationship. After a lot of Googling and reading I have concluded that: I need to create the join/bridge table just like all the other tables. In other words, it will not be created automatically using some SQL that I have yet to understand. True or false? True. There is no special built-in syntax for many-to-many relationships (nor for one-to-many relationships, for that matter). You just use tables that store each other's keys. In all the examples I have seen, the join/bridge table is populated (INSERT INTO) manually - as in not done by SQL. Here is my problem. I will need to insert the primary keys as foreign keys into the table. In the examples I have seen, they knew what those number were (all 12 of them). I won't have that situation as the primary keys from the other tables are AUTO_NUMBER. That means I have to query those tables before I can INSERT INTO the join/bridge tables. Too much work. Is there a better easier way? How about some complete examples? You may be looking for sqlite3_last_insert_rowid API function ( https://www.sqlite.org/c3ref/last_insert_rowid.html ) and/or last_insert_rowid() SQL function ( https://www.sqlite.org/lang_corefunc.html#last_insert_rowid ). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to create multiple tables?
On 12 Aug 2019, at 13:51, dboland9 wrote: > I'm doing an app. that has multiple tables in sqlite. When the app. is run > for the first time, or the tables are lost/damaged, it will create the > database and all tables. My question is if there is a best way to create 5 > tables? > > I can put al the Python code into one function, pass the path to the database > file to multiple functions, or pass the connection to multiple functions. One > function is the most contained, but very large! Any suggestions from those > that have been there done that (BTDT)? I check for the existence of each database, and if not found then create it from scratch. I also have a globals table in each database, of which one of the columns is a version number. If a new software version expects a later version number, it can apply incremental schema changes. An older software version finding a too-new version number can quit with a message asking the user to upgrade. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD
Intended use is to cancel long running SQLITE background operations on other threads if the user needs UI responsiveness on the main thread. Even though the operations are background, we need the CPU & disk back for the user. Once the user becomes idle again, the background operations restart. My concern is that SQLITE_CONFIG_SINGLETHREAD implies no mutexes. I don't know if it is possible to correctly implement sqlite3_interrupt() without a mutex on all platforms that SQLITE runs on. - Deon -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2019 6:19 AM To: SQLite mailing list Subject: Re: [sqlite] sqlite3_interrupt vs. SQLITE_CONFIG_SINGLETHREAD On 8/14/19, Deon Brewis wrote: > sqlite3_interrupt is documented as: > “It is safe to call this routine from a thread different from the > thread that is currently running the database operation” > > SQLITE_CONFIG_SINGLETHREAD is documented as: > “puts SQLite into a mode where it can only be used by a single thread” > > Which one wins The sqlite3_interrupt() interface is intending to stop a long-running query, usually by a single handler in response to the user pressing Ctrl-C or similar. This works regardless of compile-time options. What is your intended use of sqlite3_interrupt() that compile-time options matter? -- 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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > > > Why are you storing the timezone? You display the TZ of the user who is, > > later, viewing the data. And that user could be anywhere. > > Because the actual time on the clock on the wall matters. > I want to know cashiers that are making transactions outside of 9am-5pm > (for instance). But depending on where they are that time is different. > I also want to know the time continuously according to the linear time that > they happened in. > > The time on the clock is irrelevant, completely and totally, due to daylight saving. Moving the clock back and forth twice a year causes a chunk of time to go missing or a chunk of time to be duplicated. The clock is a User Interface problem, not a data storage problem. When you want to know that locations actual time, you store that stores location with their TZ information, and your UI presents that information to you. Because UTC does not move forward or backwards (Occasionally we add a second due to the rotation of the earth slowing down*, but we NEVER go backwards), anything time related in a database NEEDS to be stored in UTC/Zulu/GMT-0 time (Or whatever you want to call it). This is THE base time. Sure, you COULD store the location, but, if you're in British Columbia, looking at a stores transactions in New Found Land, (Or say California versus New York), what time do you believe? I work for a company that literally has servers all around the world, which means Time Zones ARE a thing for us. We set every single machine we deploy to UTC. If there were any times to be displayed in "local" time, it's handled at the application/presentation layer, not the database layer. *https://www.quora.com/Why-is-Earths-rotation-slowing-down -- First hit on Google. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski wrote: > > On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > >>> Why are you storing the timezone? You display the TZ of the user who is, >>> later, viewing the data. And that user could be anywhere. >> >> Because the actual time on the clock on the wall matters. >> > The time on the clock is irrelevant, completely and totally, due to > daylight saving. I agree with your reply as far as it goes, and I agree that times stored in the DB should be in UTC or some similar format. (By that I mean we don’t need to get into UTC vs UT1 vs whatever other “universal” time standard you prefer. Just pick one and stick to it for all users of that DB.) However, it *can* be helpful to store the time zone or the local UTC offset alongside the universal timestamp in the DB so you can translate the timestamp to local time in calculations. Without it, you can’t ask the DB questions like, “Give me all records that occurred between 9am and 10am local time,” or “Across all stores, what are our busiest hours?” The timestamp in UTC lets you ask different questions, such as “Give me the 10 most recent matching records,” which breaks if you store timestamps as local time, and your DB holds values from multiple time zones. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: DEF CON (wasL A license plate of NULL)
On Aug 14, 2019, at 12:27 AM, Hick Gunter wrote: > > But surely any compiler worth ist salt would optimize away all of that code > and just use the result of the expression given as argument in the call ;) You joke, but the answer is “Maybe.” See https://godbolt.org/z/K9g-ai In English, that assembly says “return the passed value.” Take out the -O3 on that page and observe the change. But now change the compiler to see whether your favorite compiler can do this optimization. The latest version of Visual C++ won’t optimize this away, as the latest GCC will: https://godbolt.org/z/tnr9cT There’s probably an optimization setting that will do this, but the point is, you can’t always count on the optimizer to do what you think it should. Thus Compiler Explorer. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)
Yep, I agree, to which is where I pointed out that you'd need the additional information for that store location. But that's all you'd need, and only in that one location. Your UI (Or whatever specialized report generation) would have to do the math from the UTC time, and convert it accordingly to whatever the stores location TZ info is, then present the appropriate information. So even if you are in BC, you can look at NFLD info and get the NFLD local times. On Wed, Aug 14, 2019 at 12:24 PM Warren Young wrote: > On Aug 14, 2019, at 9:55 AM, Stephen Chrzanowski > wrote: > > > > On Tue, Aug 13, 2019 at 7:30 PM J Decker wrote: > > > >>> Why are you storing the timezone? You display the TZ of the user who > is, > >>> later, viewing the data. And that user could be anywhere. > >> > >> Because the actual time on the clock on the wall matters. > >> > > The time on the clock is irrelevant, completely and totally, due to > > daylight saving. > > I agree with your reply as far as it goes, and I agree that times stored > in the DB should be in UTC or some similar format. > > (By that I mean we don’t need to get into UTC vs UT1 vs whatever other > “universal” time standard you prefer. Just pick one and stick to it for > all users of that DB.) > > However, it *can* be helpful to store the time zone or the local UTC > offset alongside the universal timestamp in the DB so you can translate the > timestamp to local time in calculations. Without it, you can’t ask the DB > questions like, “Give me all records that occurred between 9am and 10am > local time,” or “Across all stores, what are our busiest hours?” > > The timestamp in UTC lets you ask different questions, such as “Give me > the 10 most recent matching records,” which breaks if you store timestamps > as local time, and your DB holds values from multiple time zones. > ___ > 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] Create multipe tables, many-to-many design
On Monday, 12 August, 2019 14:05, dboland9 wrote: >Hiking_Table Trails_Table >Joining_Table >- - -- >- >hike_id PK trail_id PK >hike_id FK >hike_date TEXT trail_name TEXT trail_id FK >hike_destination TEXTtrail_rating REAL >hike_rating REAL trail_comments TEXT >hike_comments TEXT >So far, so good. I know how to create the tables. What I am >struggling with is how do I insert data into the joining table or >don"t I. Somethings I have read suggest that the joining table just >contains references, so there is no actual insert. A pointer to >information how to do this would be appreciated. As for queries, I >think I use joins, but a pointer on how to do this would also be >appreciated. The problem is that you have not identified the keys properly. What you call the PK is certainly a candidate, but it is not the actual key that identifies the unique data, but rather is rather a pseudokey identifying a row, not a set of unique data identifying a row. Once you identify the actual keys, you are in business. I would guess that the actual tables probably might look as follows (I have removed the extraneous embedding since it adds nothing except more typing, and changed your "date" into a starting datetime): create table Hikes ( id INTEGER PRIMARY KEY, start TEXT NOT NULL CHECK (start IS datetime(start, '+0 days'), destination TEXT NOT NULL COLLATE NOCASE, rating REAL, comments TEXT, UNIQUE (date, destination) ); create table Trails ( id INTEGER PRIMARY KEY, name TEXT NOT NULL COLLATE NOCASE UNIQUE, rating REAL, comments TEXT ); create table HikeTrail ( hike_id INTEGER NOT NULL REFERENCES Hikes, trail_id INTEGER NOT NULL REFERENCES Trails ); create index HikedTrails on HikeTrail (hike_id, trail_id); create index TrailsHiked on HikeTrail (trail_id, hike_id); Assuming that you want to ADD a new hike on (start, destination, name), then you do the following sql: BEGIN IMMEDIATE; INSERT OR IGNORE INTO Hikes (start, destination) values (:start, :destination); INSERT OR IGNORE INTO Trails (name) values (:name); INSERT INTO HikeTrail (hike_id, trail_id) VALUES ((select id from Hikes where start=:start and destination=:destination),(select id from Trails where name=:name)); COMMIT; To DELETE a hike on (start, destination, name) you do the following: BEGIN IMMEDIATE; DELETE FROM HikeTrail WHERE hike_id = (select id from Hikes where start=:start and destination=:destination) AND trail_id = (select id from trails where name=:name); DELETE FROM Hikes WHERE start=:start AND destination=:destination AND NOT EXISTS (select hike_id from HikeTrail where hike_id = id); DELETE FROM Trails WHERE name=:name AND NOT EXISTS (select trail_id from HikeTrail where trail_id = id); COMMIT; >Perhaps these topics could be added to the excellent >http://www.sqlitetutorial.net/ . -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create multipe tables, many-to-many design
> UNIQUE (date, destination) should of course be UNIQUE (start, destination) >create index HikedTrails on HikeTrail (hike_id, trail_id); >create index TrailsHiked on HikeTrail (trail_id, hike_id); Both of these should be UNIQUE indexes and could be declared in the create table ... and of course foreign Key enforcement should be enabled -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Create multipe tables, many-to-many design
For maximum space efficiency and using a supporting version of SQLite3 you could declare the N:M table as: create table HikeTrail ( hike_id INTEGER NOT NULL REFERENCES Hikes, trail_id INTEGER NOT NULL REFERENCES Trails, PRIMARY KEY (hike_id, trail_id), UNIQUE (trail_id, hike_id) ) WITHOUT ROWID; because really you only need the N:M and M:N index trees. -- 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 Keith Medcalf >Sent: Wednesday, 14 August, 2019 11:33 >To: SQLite mailing list >Subject: Re: [sqlite] Create multipe tables, many-to-many design > > >On Monday, 12 August, 2019 14:05, dboland9 >wrote: > >>Hiking_Table Trails_Table >>Joining_Table >>- - - >- >>- >>hike_id PK trail_id PK >>hike_id FK >>hike_date TEXT trail_name TEXT trail_id FK >>hike_destination TEXTtrail_rating REAL >>hike_rating REAL trail_comments TEXT >>hike_comments TEXT > >>So far, so good. I know how to create the tables. What I am >>struggling with is how do I insert data into the joining table or >>don"t I. Somethings I have read suggest that the joining table just >>contains references, so there is no actual insert. A pointer to >>information how to do this would be appreciated. As for queries, I >>think I use joins, but a pointer on how to do this would also be >>appreciated. > >The problem is that you have not identified the keys properly. What >you call the PK is certainly a candidate, but it is not the actual >key that identifies the unique data, but rather is rather a pseudokey >identifying a row, not a set of unique data identifying a row. Once >you identify the actual keys, you are in business. I would guess >that the actual tables probably might look as follows (I have removed >the extraneous embedding since it adds nothing except more typing, >and changed your "date" into a starting datetime): > >create table Hikes >( > id INTEGER PRIMARY KEY, > start TEXT NOT NULL CHECK (start IS datetime(start, '+0 days'), > destination TEXT NOT NULL COLLATE NOCASE, > rating REAL, > comments TEXT, > UNIQUE (date, destination) >); >create table Trails >( > id INTEGER PRIMARY KEY, > name TEXT NOT NULL COLLATE NOCASE UNIQUE, > rating REAL, > comments TEXT >); >create table HikeTrail >( > hike_id INTEGER NOT NULL REFERENCES Hikes, > trail_id INTEGER NOT NULL REFERENCES Trails >); >create index HikedTrails on HikeTrail (hike_id, trail_id); >create index TrailsHiked on HikeTrail (trail_id, hike_id); > >Assuming that you want to ADD a new hike on (start, destination, >name), then you do the following sql: > >BEGIN IMMEDIATE; >INSERT OR IGNORE INTO Hikes (start, destination) > values (:start, :destination); >INSERT OR IGNORE INTO Trails (name) > values (:name); >INSERT INTO HikeTrail (hike_id, trail_id) > VALUES ((select id from Hikes where start=:start and >destination=:destination),(select id from Trails where name=:name)); >COMMIT; > >To DELETE a hike on (start, destination, name) you do the following: > >BEGIN IMMEDIATE; >DELETE FROM HikeTrail > WHERE hike_id = (select id > from Hikes >where start=:start > and destination=:destination) >AND trail_id = (select id > from trails > where name=:name); >DELETE FROM Hikes > WHERE start=:start >AND destination=:destination >AND NOT EXISTS (select hike_id from HikeTrail where hike_id = >id); >DELETE FROM Trails > WHERE name=:name >AND NOT EXISTS (select trail_id from HikeTrail where trail_id >= id); >COMMIT; > >>Perhaps these topics could be added to the excellent >http://www.sqlitetutorial.net/ . > >-- >The fact that there's a Highway to Hell but only a Stairway to Heaven >says a lot about anticipated traffic volume. > > > > >___ >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
