SQLite Forum

Timeline
Login

28 forum posts by user doug9forester occurring on or before 2020-06-02 00:32:47.

More ↑
2020-06-02
00:32 Post: Question about aliases on SELECT (artifact: 906ca360d6 user: doug9forester)

Given this select:

SELECT t1.a AS a1 FROM t1 AS ta WHERE t1.a = ...

I sort of expected an error, because I should have coded the WHERE as:

SELECT t1.a AS a1 FROM t1 AS ta WHERE ta.a1 = ...

But no error. Can I assume that both forms of WHERE are valid and yield the same results?

2020-06-01
18:15 Reply: Are Views just stored SQL Strings or something more? (artifact: 30b768cba4 user: doug9forester)

MD is Markdown. Should it be MU (Markup)? What's the difference?

So I just created a view with a set of joins. Worked like magic!

I am now going to try to create some views for my Qt application. I thought Qt documentation said views are not supported. But it's sql, right? Ought to work.

Looking at the CREATE TABLE (syntax diagram) definition again after looking at CREATE VIEW. I see that you can create a table composed of JOINs. What does that even mean? It doesn't seem right that I can define a table that is composed of JOINs of other tables.

17:43 Reply: Are Views just stored SQL Strings or something more? (artifact: 7d4713ee35 user: doug9forester)

Sorry, I missed that it's a full select, joins and all.

17:32 Reply: Opening Huaiwei android notes (artifact: d0b3c306d5 user: doug9forester)

This link tells you how to reset the application password - provided you own the phone: https://consumer.huawei.com/en/support/content/en-us00685029/

17:27 Reply: Are Views just stored SQL Strings or something more? (artifact: af8eca736f user: doug9forester)

I have been using MS Access for eons and have wanted to convert my applications to Sqlite. Aside from the obvious missing forms and reports (which are starting to be addressed with MD and other changes), Sqlite doesn't have saved queries, except --- wait for it --- something called a view! A view is a very simple, one-table query.

Is is a big step to provide the ability to add WHERE and ORDER BY to the view definition? And then the real bonus: add JOIN capability? With those things, you suddenly get a viable saved-query system, I think, at very little additional cost. And what about parameterized queries? Is that too big a step?

2020-05-14
14:32 Reply: Can this simple query be optimized? (artifact: 957246ba74 user: doug9forester)

Ryan, just to be clear, SQLite's handling of min() and max() is not fully deterministic. In the case that Keith pointed out, where there is more than one row with a max or a min, you don't know which row will be picked to satisfy the x.

2020-05-13
15:40 Reply: Can this simple query be optimized? (artifact: 95660c1de4 user: doug9forester)

One problem with the query as

SELECT x, min(y) FROM foo GROUP BY (x/10) UNION SELECT x, max(y) FROM foo GROUP BY (x/10);

is that you can't tell whether the x you get is related to the max or the min of the group (without doing another query on the results). You really need 5 values all together in one row of output. This CTE query gives you all the information in a simple and easy to understand query:

sqlite> WITH bar1(x_miny, miny, xd10) AS ...> ( SELECT x, min(y), (x/10) FROM foo GROUP BY (x/10) ) ...> ,bar2(x_maxy, maxy, xd10) AS ...> ( SELECT x, max(y), (x/10) FROM foo GROUP BY (X/10) ) ...> SELECT bar1.x_miny, bar1.miny, bar2.x_maxy, bar2.maxy, bar1.xd10 ...> FROM bar1 JOIN bar2 ON bar1.xd10=bar2.xd10; x_miny|miny|x_maxy|maxy|xd10 1|23|2|45|0

I don't believe you can get all the required information without scanning twice because the max and mins will (almost always) be in different rows of foo. The use of a temporary table adds the equivalent of a scan to load the temp table, so there is no performance gain using it.

As an aside, if other SQL engines don't return correlated fields for aggregate queries (the x in the SELECT), how can you get an x?

2020-04-24
06:46 Reply: How do create the neat flow diagrams for SQLite? (artifact: 49027f581d user: doug9forester)

Thank you, Richard, for how-to generate the bubble diagrams. I can generate the diagrams, but all the text overflows the boundaries of their containers. I changed the font value but it made no difference. Any ideas?

The next request is for the minimum set of files needed to actually generate a multi-page html document which looks like Sqlite's SQL pages. I don't want requirements, nor testing. Just want to document my language. I've spent a couple of days pouring over the files with few results. I know that the lang_xxx files contain the descriptive text and that you use an Sqlite database for the information, but I can't figure out how to populate the database, or even find out how it's used. Can you point me in the right direction? Thanks.

2020-04-22
04:50 Reply: How do create the neat flow diagrams for SQLite? (artifact: 477c474754 user: doug9forester)

It turns out that I had to build Sqlite in order to get tclsqlite.c. I was successful in building that and in building the doc files.

However, my goal is to build my own docs. I can't figure out what the minimum set of files and processes are to get something that works. Can you tell me what that minimum set is and what processes I have to run (by hand) to gen the docs, please?

2020-04-21
21:18 Reply: How do create the neat flow diagrams for SQLite? (artifact: 694f591385 user: doug9forester)

The next layer of the onion: missing tclsh.docsrc:

doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc$ ls -ag total 0 drwxrwxrwx 1 root 512 Apr 15 08:25 . drwxrwxrwx 1 root 512 Feb 7 2019 .. drwxrwxrwx 1 root 512 Apr 21 13:24 archive drwxrwxrwx 1 root 512 Apr 14 22:15 dst drwxrwxrwx 1 root 512 Apr 21 07:16 mydoc drwxrwxrwx 1 root 512 Apr 15 13:25 sqlite doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc$ cd dst doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc/dst$ ls -ag total 304 drwxrwxrwx 1 root 512 Apr 14 22:15 . drwxrwxrwx 1 root 512 Apr 15 08:25 .. -rwxrwxrwx 1 root 1888 Apr 21 13:59 Makefile -rwxrwxrwx 1 root 1509 Apr 14 22:15 README.md drwxrwxrwx 1 root 512 Apr 14 22:15 art -rwxrwxrwx 1 root 1180 Apr 14 22:15 common_links.tcl -rwxrwxrwx 1 root 16408 Apr 14 22:15 custom.txt drwxrwxrwx 1 root 512 Apr 14 22:15 docapp -rwxrwxrwx 1 root 4221 Apr 14 22:15 document_header.tcl -rwxrwxrwx 1 root 3956 Apr 14 22:15 format_evidence.tcl drwxrwxrwx 1 root 512 Apr 14 22:15 images -rwxrwxrwx 1 root 3379 Apr 14 22:15 logo.gif -rwxrwxrwx 1 root 6543 Apr 14 22:15 main.mk -rwxrwxrwx 1 root 20496 Apr 14 22:15 matrix.tcl drwxrwxrwx 1 root 512 Apr 14 22:15 misc -rwxrwxrwx 1 root 1526 Apr 14 22:15 numbering.txt drwxrwxrwx 1 root 512 Apr 14 22:15 pages -rwxrwxrwx 1 root 533 Apr 14 22:15 push.sh drwxrwxrwx 1 root 512 Apr 14 22:15 rawpages -rwxrwxrwx 1 root 424 Apr 14 22:15 remove_carets.sh drwxrwxrwx 1 root 512 Apr 14 22:15 req -rwxrwxrwx 1 root 5266 Apr 14 22:15 scan_test_cases.tcl -rwxrwxrwx 1 root 4720 Apr 14 22:15 schema.tcl drwxrwxrwx 1 root 512 Apr 14 22:15 search -rwxrwxrwx 1 root 2196 Apr 14 22:15 spell_chk.sh -rwxrwxrwx 1 root 25779 Apr 14 22:15 wrap.tcl doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc/dst$ make make base; # Build base documents make evidence; # Gather evidence marks make matrix; # Build the traceability matrix make searchdb; # Construct the FTS search database make all; # Do all of the above make spell; # Spell check generated docs make fast; # Build documentation only - no requirements make schema; # Run once to initialize the build process doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc/dst$ make schema make: *** No rule to make target '../sqlite/tclsqlite3.c', needed by 'tclsh.docsrc'. Stop.

doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc/sqlite$ ls -al total 1568 drwxrwxrwx 1 root root 512 Apr 15 13:25 . drwxrwxrwx 1 root root 512 Apr 15 08:25 .. drwxrwxrwx 1 root root 512 Apr 21 2020 .fossil-settings -rwxrwxrwx 1 root root 263 Apr 21 2020 LICENSE.md -rwxrwxrwx 1 root root 46673 Apr 21 2020 Makefile.in -rwxrwxrwx 1 root root 3407 Apr 21 2020 Makefile.linux-gcc -rwxrwxrwx 1 root root 79874 Apr 21 2020 Makefile.msc -rwxrwxrwx 1 root root 16060 Apr 21 2020 README.md -rwxrwxrwx 1 root root 7 Apr 21 2020 VERSION -rwxrwxrwx 1 root root 282502 Apr 21 2020 aclocal.m4 drwxrwxrwx 1 root root 512 Apr 21 2020 art drwxrwxrwx 1 root root 512 Apr 21 2020 autoconf -rwxrwxrwx 1 root root 48216 Apr 21 2020 config.guess -rwxrwxrwx 1 root root 3508 Apr 21 2020 config.h.in -rwxrwxrwx 1 root root 31617 Apr 21 2020 config.sub -rwxrwxrwx 1 root root 405966 Apr 21 2020 configure -rwxrwxrwx 1 root root 22515 Apr 21 2020 configure.ac drwxrwxrwx 1 root root 512 Apr 21 2020 contrib drwxrwxrwx 1 root root 512 Apr 21 2020 doc drwxrwxrwx 1 root root 512 Apr 21 2020 ext -rwxrwxrwx 1 root root 5598 Apr 21 2020 install-sh -rwxrwxrwx 1 root root 245160 Apr 21 2020 ltmain.sh -rwxrwxrwx 1 root root 1515 Apr 21 2020 magic.txt -rwxrwxrwx 1 root root 36183 Apr 21 2020 main.mk -rwxrwxrwx 1 root root 142032 Apr 21 2020 manifest -rwxrwxrwx 1 root root 65 Apr 21 2020 manifest.uuid -rwxrwxrwx 1 root root 937 Apr 21 2020 mkso.sh drwxrwxrwx 1 root root 512 Apr 21 2020 mptest -rwxrwxrwx 1 root root 1796 Apr 21 2020 spec.template -rwxrwxrwx 1 root root 258 Apr 21 2020 sqlite.pc.in -rwxrwxrwx 1 root root 8928 Apr 21 2020 sqlite3.1 -rwxrwxrwx 1 root root 267 Apr 21 2020 sqlite3.pc.in drwxrwxrwx 1 root root 512 Apr 21 2020 src drwxrwxrwx 1 root root 512 Apr 21 2020 test drwxrwxrwx 1 root root 512 Apr 21 2020 tool drwxrwxrwx 1 root root 512 Apr 21 2020 vsixtest

18:54 Reply: How do create the neat flow diagrams for SQLite? (artifact: 4c8819a3e8 user: doug9forester)

Sorry, I must be going blind again. I downloaded the amalgamation but no test_md5.c. In fact, no "src" folder either:

doug@HP610:/mnt/q/qt_projects/dancemusicmanager/doc/sqlite$ ls -al total 15744 drwxrwxrwx 1 root root 512 Apr 15 13:25 . drwxrwxrwx 1 root root 512 Apr 15 08:25 .. -rwxrwxrwx 1 root root 4393914 Apr 15 13:16 Documentation+Source+Text-15a047bc79.zip drwxrwxrwx 1 root root 512 Apr 14 22:15 dst -rwxrwxrwx 1 root root 591565 Jan 28 03:25 shell.c -rwxrwxrwx 1 root root 2389909 Apr 21 11:41 sqlite-amalgamation-3310100.zip -rwxrwxrwx 1 root root 8074669 Jan 28 03:25 sqlite3.c -rwxrwxrwx 1 root root 576161 Jan 28 03:25 sqlite3.h -rwxrwxrwx 1 root root 34802 Jan 28 03:25 sqlite3ext.h

16:58 Reply: How do create the neat flow diagrams for SQLite? (artifact: 8bbd4f5ec5 user: doug9forester)

I downloaded the tarball and got the source. I am on W10 so not unix. I installed Linux Ubuntu package, so I have bash. Now I'm trying to build your stuff so I can see how it works (it's not simple). I get an error on the build no matter what make target I use:

$ make schema make: *** No rule to make target '../sqlite/src/test_md5.c', needed by 'tclsh.docsrc'. Stop.

Where is test_md5.c?

2020-04-16
16:43 Reply: select in order (artifact: f7790fa8f8 user: doug9forester)

OMG! insert into p(other) is obviously the way to not have to specify the id.

OTOH I thought that autoincrement was the safe way to guarantee that the id's were always incrementing. If you delete some records, can't a rowid table reuse those numbers?

15:37 Reply: select in order (artifact: ec556cd44b user: doug9forester)

Yet another simple way:

sqlite> create table p (id integer primary key autoincrement, other);
sqlite> insert into p values (null,'one'), (null,'two'), (null,'three'), (null,'four');
sqlite> select id,other from p order by id;
id      other
1       one
2       two
3       three
4       four
(I'd like to be able to insert into the table without having to put in the placeholder for the id, but I can't figure out how.)

2020-04-15
20:21 Reply: How do create the neat flow diagrams for SQLite? (artifact: ca52ff18ea user: doug9forester)

Thanks, Richard :)

17:00 Reply: How do create the neat flow diagrams for SQLite? (artifact: 1416593c28 user: doug9forester)

I must be blind because I can find links to neither Tarball nor ZIP Archive on the page you referenced.

15:48 Reply: How do create the neat flow diagrams for SQLite? (artifact: a1bd2d58b9 user: doug9forester)

hmmmm... I got to the web page with the list of files: https://www.sqlite.org/docsrc/dir?ci=tip

Now, how do I actually download those file? I can't find an option anywhere on the page. There are more than 70 files.

04:58 Post: How do create the neat flow diagrams for SQLite? (artifact: d1a45a9a79 user: doug9forester)

The SQLite language documentation is great! I want to use the same thing to document my own stuff. How can I generate those diagrams and associated text?

Here's a link to one page, for example: https://www.sqlite.org/lang_createtable.html

2020-04-08
22:43 Reply: Please explain the error on insert (artifact: b667e0c686 user: doug9forester)

The design requires ordered lists of teachers and their students because teachers and student can be inserted in the list anywhere and the application needs to remember where they were inserted.

The implementation was working fine (students and teachers in the right order) until I turned on referential integrity checking.

I am generating C++ code for accessing the database, a class for each table. I can generate a function for an arbitrary table query with as few as 2 lines of input. I support field types of INTEGER and TEXT only. For INTEGER type, I use a 'int' data member in the class. So implementing teacher_id as a C++ int was a natural. However, 'int's don't do null values very well.

Rethinking my design, the value of 0 can be treated as a null. Since I'm generating the code, it's pretty simple to do this when getting the value for binding to a parameter:

  queryinsert.addBindValue(teacher_prev==0 ? QVariant(QString()) : QVariant(teacher_prev));

The purpose of the teacher/student list is to allow a (dance) teacher to record that a particular student (or the teacher) wants to remember a particular song used for a dance lesson for that student. The application provides quick access to different lists of songs quickly without having to look them up again and again.

As for 1-to-1 teachers and students, if two teachers want to add the same student to their lists, then they just add them. There is no master list.

Also, teachers come and go with regularity so removing a teacher means removing data that only that teacher was interested in. (I may want to rethink cascade...) If a teacher deletes a student, the songs remembered for that student will revert to the catch-all student (Unspecified).

06:07 Reply: Please explain the error on insert (artifact: b5460455d1 user: doug9forester)

The items that refer to teacher 0 and student 0 are already in their respective tables. I put the there with pragma foreign_keys=0.

The insert that is failing has an unused student_id (4) and refers to an existing student_id (3):

INSERT INTO studenttable (student_id,student_displayname,teacher_id,student_prev,student_select)
   VALUES(4,'Doug',3,3,0);

What you are suggesting is that some mechanism is looking at existing rows to see if they are violating referential integrity (which they obviously do). And the checking occurs when I try to insert a new row.

If so, what is that mechanism?

Is there a table design which gets around the mechanism and still provides referential integrity for new inserts?

04:27 Post: Please explain the error on insert (artifact: 735db586f4 user: doug9forester)

I am actually running SQLite under in a Qt application. I got a failure on an insert statement so I turned to Sqlite3 CLI to figure it out. But I'm stuck. The design is a linked list of students for each teacher. The link is from the second to the first with a student-prev pointed to a student-id. The first student-prev in each student list is 0. Here is the CLI console [editted]:

sqlite> .version
SQLite 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6
zlib version 1.2.11
gcc-5.2.0
sqlite> .dump [editted to show the studenttable only]
CREATE TABLE studenttable
 (student_id INTEGER NOT NULL
 ,student_displayname TEXT NOT NULL
 ,teacher_id INTEGER NOT NULL REFERENCES teachertable(teacher_id) ON DELETE CASCADE
 ,student_prev INTEGER NOT NULL REFERENCES studenttable(student_id)
 ,student_select INTEGER NOT NULL
 ,UNIQUE(student_displayname,teacher_id)
);
sqlite> select * from sqlite_master where type = 'trigger';
type    name    tbl_name        rootpage        sql
trigger insert_new_student      studenttable    0
  CREATE TRIGGER insert_new_student
    AFTER INSERT ON studenttable
      BEGIN
        UPDATE OR IGNORE studenttable SET student_prev=NEW.student_id
          WHERE teacher_id=NEW.teacher_id
          AND student_prev=NEW.student_prev
          AND student_id!=NEW.student_id;
      END
sqlite> pragma foreign_keys;
foreign_keys
1
sqlite> select * from teachertable;
teacher_id      teacher_displayname     teacher_prev
1		       Doug		    0
2		       Matt		    1
3		       Zhenia		    2
sqlite> select * from studenttable;
student_id      student_displayname     teacher_id      student_prev    student_select
1		Unspecified		     1		       0	       0
2		Unspecified		     2		       0	       0
3		Unspecified		     3		       0	       0
sqlite> INSERT INTO studenttable (student_id,student_displayname,teacher_id,student_prev,student_select)
   ...>   VALUES(4,'Doug',3,3,0);
Error: foreign key mismatch - "studenttable" referencing "studenttable"
2020-03-30
21:32 Reply: 3.31.1: create table with multiple primary key columns (artifact: 96513fb983 user: doug9forester)

Au contraire, there is a way to add a child without a current parent: add DEFERRABLE INITIALLY DEFERRED to the column definition:

create table list (row_id INTEGER PRIMARY KEY, item TEXT UNIQUE,
     prev INTEGER REFERENCES list(row_id) DEFERRABLE INITIALLY DEFERRED);
...
begin transaction;
insert into list _child_;
insert into list _parent_;
commit transaction;

19:36 Post: How can I maintain a list in an arbitrary order? (artifact: 65a52ad3de user: doug9forester)

Given an arbitrarily ordered list of items b,q,t,z,a (obviously not in sort order), how can I insert a new item "p" after item "t", for example, and have a query return the ordered list b,q,t,p,z,a?

If I use a position integer, then all the positions after the inserted row need to be increased by one. Ugh.

The table below seems to capture the information. However, I can't figure out how to query to get it in the right order. My guess is I need a recursive CTE.

Can someone help me out here, please?

--drop table list;
create table list (row_id INTEGER PRIMARY KEY, item TEXT UNIQUE,
     prev INTEGER REFERENCES list(row_id) DEFERRABLE INITIALLY DEFERRED);
insert into list values(null,"b",0);
insert into list values(null,"q",(select last_insert_rowid()));
insert into list values(null,"t",(select last_insert_rowid()));
insert into list values(null,"z",(select last_insert_rowid()));
insert into list values(null,"a",(select last_insert_rowid()));
select * from list order by row_id;
row_id|item|prev
1|b|0
2|q|1
3|t|2
4|z|3
5|a|4
-- want order to be b,q,t,z,a

-- normal insert "p" before "t"
begin transaction;
insert into list values(null,"p",(select prev from list where item="t"));
update list set prev=(select last_insert_rowid()) where item="t";
commit transaction;
select * from list order by row_id;
row_id|item|prev
1|b|0
2|q|1
3|t|6
4|z|3
5|a|4
6|p|2
-- want order to be b,q,p,t,z,a

-- boundary insert row before first row ("b")
begin transaction;
insert into list values(null,"w",(select prev from list where item="b"));
update list set prev=(select last_insert_rowid()) where item="b";
commit transaction;
select * from list order by row_id;
row_id|item|prev
1|b|7
2|q|1
3|t|6
4|z|3
5|a|4
6|p|2
7|w|0
-- want order to be w,b,q,p,t,z,a

-- boundary insert new row at end "h"
insert into list values(null,"h",(select max(row_id) from list));
select * from list order by row_id;
row_id|item|prev
1|b|7
2|q|1
3|t|6
4|z|3
5|a|4
6|p|2
7|w|0
8|h|7-- want order to be w,b,q,p,t,z,a,h
2020-03-20
05:27 Reply: ESCAPE '%' (artifact: 527fb867e7 user: doug9forester)

Thanks. That clears it up nicely.

02:03 Reply: ESCAPE '%' (artifact: 7723bbd5d1 user: doug9forester)

What is the difference between these:

SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%';
and
SELECT id FROM t1 WHERE x LIKE 'abc%%';
and
SELECT id FROM t1 WHERE x LIKE 'abc%';
I'm pretty sure the last one is a wildcard search, that (I assume) matches anything like the regular expression "abc.*".

What match string expressed as a regular expression do the first two selects find?

2020-03-19
21:13 Reply: ESCAPE '%' (artifact: 33376df4c9 user: doug9forester)

I assumed a wildcard was zero or more like the "*" wildcard in regular expressions.

19:27 Reply: ESCAPE '%' (artifact: 663e52ce87 user: doug9forester)

I don't know how do_execsql_test works. Is the {4} at the end the number of row returned? From Richard's update to the test suite: ```

2020-03-19

The ESCAPE clause on LIKE takes precedence over wildcards

# do_execsql_test idu-6.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT); INSERT INTO t1 VALUES (1,'abcde'), (2,'abc_'), (3,'abc_'), (4,'abc%'), (5,'abc%%'); SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%'; } {4} do_execsql_test icu-6.1 { SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE ''; } {2} ``` If {4} and {2} are the number of rows returned, shouldn't they both be {2}?

2020-03-18
15:39 Reply: columnName function (artifact: ee92c859fb user: doug9forester)
ALERT: Forum question: why do I see back slashes before every underscore in the email notification for this post?

The SQLITE\_ENABLE\_COLUMN\_METADATA symbol needs to be defined in order to collect this information when the statement is parsed.

It also enables the sqlite3\_column\_database_name, sqlite3\_column\_table\_name, and sqlite3\_column\_origin\_name APIs.

I presume that you did not have this symbol defined at compile time?  And if you do have it defined, why not just use the intended API functions?