Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...
The syntax diagrams are created by a tcl script, not drawn by hand: https://wiki.tcl-lang.org/page/Generating+Syntax+Diagrams+Using+Tk I think a lot of the documentation assumes the reader already knows the basics of sql... What kind of examples are you thinking of? On Tue, Jun 25, 2019, 8:16 PM Ben Earhart wrote: > ...that the person(s) that has no problem writing small, but solid, walls > of technical detail and drawing intricate circularly recursive syntax > diagrams which require multiple levels of detail to coherently represent, > can't be bothered to write example sql code for the top dozen things that > most surely cover better than the majority of real-world uses cases. > > Does anybody here know where such a thing might exist? I think sqlite3 is a > gem but, for whatever reason, I have had poor luck getting sqlite3 sql code > examples - just scraps here and there. I don't use it near as much as I > could simply because of difficulty getting examples for a few basic schema > patterns. > > Thanks, > Ben > ___ > 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] Incorrect results after upgrading to 3.28.0
Hello,
We recently updated from version 3.26.0 to version 3.28.0. Now we're seeing
different (incorrect) results for the following query. Our database is
available here:
https://github.com/aspnet/EntityFrameworkCore/raw/f386095005e46ea3aa4d677e4439cdac113dbfb1/test/EFCore.Sqlite.FunctionalTests/northwind.db
SELECT (
SELECT "t"."CustomerID"
FROM (
SELECT "o"."CustomerID", "o"."OrderDate"
FROM "Orders" AS "o"
WHERE ("c"."CustomerID" = "o"."CustomerID") AND "o"."CustomerID" IS NOT
NULL
ORDER BY "o"."CustomerID", "o"."OrderDate" DESC
LIMIT 2
) AS "t"
ORDER BY "t"."CustomerID", "t"."OrderDate" DESC
LIMIT 1)
FROM "Customers" AS "c";
Results in 3.26.0:
ALFKI
ANATR
ANTON
AROUT
...
Results in 3.28.0:
ALFKI
(NULL)
(NULL)
(NULL)
...
Thanks!
--
Brice Lambson
Senior Software Engineer
Microsoft
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...
...that the person(s) that has no problem writing small, but solid, walls of technical detail and drawing intricate circularly recursive syntax diagrams which require multiple levels of detail to coherently represent, can't be bothered to write example sql code for the top dozen things that most surely cover better than the majority of real-world uses cases. Does anybody here know where such a thing might exist? I think sqlite3 is a gem but, for whatever reason, I have had poor luck getting sqlite3 sql code examples - just scraps here and there. I don't use it near as much as I could simply because of difficulty getting examples for a few basic schema patterns. Thanks, Ben ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] nested set tree: how to change order of one node?
On 18 Jun 2019, at 14:19, Sam Carleton wrote:
>
> The tree in question contains categories, subcategories and finally image
> galleries. It is common for the user to want to sort all the subordinates
> of one level a different way, at times alphanumeric, other times simply to
> their liking. I have been reading through Joe Celko's Trees and
> Hierarchies In Sql for Smarties book to refresh the old brain. He never
> talks about how to do any sorting, which tells me it is none trivial, but I
> am sure it has been done.
For the usual (lexicographic, numeric, temporal, …) orderings, SQL’s “order by”
is your friend. If you want to maintain a user-defined ordering, you must
store it explicitly in the database.
> My thought process is to do this:
>
> 1. create a temp table to hold all the descendants of the parent
> 2. copy the subordinates (and descendants) into the temp table one at a
> time in the new order to get the lft/rgt values correct
> 3. Once all the children and descendants are copied into the temp table,
> update the lft/rgt values of the source table to get the new order
>
> Is this a valid approach?
It sounds like a terribly inefficient and complicated approach to me.
> Is there a better one?
I would step back to the design phase of your database if possible.
From your (not too detailed) description, a draft logical model might be like
the one in attachment (hoping that attachments reach the mailing list). It uses
IDEF1X notation, which you may read about online if you are not familiar with
it.
Anyway, the model captures the following requirements:
- Each Node is either a Category or a Gallery.
- Each Category is a Node.
- Each Gallery is a Node (a leaf, in fact).
- A Node is a child of zero or one Category.
- A Category is a parent of zero or more Nodes (*).
- A Category may follow one other Category (at the same level).
- A Category may precede one other Category (at the same level).
(*) This model allows a Category to have both Categories and Galleries as
children. This may be constrained if necessary.
The code at the end of this message, tested in SQLite, creates the
corresponding tables and shows a few transactions to populate and query the
database (in the real world, such transactions would likely be implemented as
user-defined functions, with suitable parameters). In particular, the
recursive query returns the categories at a given level according to the
persisted ordering. Another possibility would be to store the rank directly,
but that would make other operations more involved.
The closure extension suggested in another post may be used with the
proposed model, if desired, but not with your model (AFAICS).
Hope this helps!
Life.
create table Node (
Name text not null,
Type text not null,
primary key (Name),
constraint ValidNodeType check (Type in ('C','G')) -- Category/Gallery
);
create table Category (
Name text not null,
primary key (Name),
foreign key (Name) references Node(Name)
on update cascade on delete cascade
);
create table Gallery (
Name text not null,
primary key (Name),
foreign key (Name) references Node(Name)
on update cascade on delete cascade
);
create table NodeCategory (
Name text not null,
ParentName text not null,
primary key (Name),
unique (Name, ParentName), -- Required for foreign keys in CustomOrdering
-- unique (ParentName, Name), -- Might be useful for performance
foreign key (Name) references Node(Name)
on update cascade on delete cascade,
foreign key (ParentName) references Category(Name)
on update cascade on delete cascade
);
create table CustomOrdering (
NextName text not null,
PrevName text not null,
ParentName text not null,
constraint ValidPair check (NextName <> PrevName),
primary key (NextName),
unique (PrevName),
foreign key (NextName, ParentName)
references NodeCategory(Name, ParentName)
on update cascade on delete cascade,
foreign key (PrevName, ParentName)
references NodeCategory(Name, ParentName)
on update cascade on delete cascade,
foreign key (ParentName) references Category(Name)
on update cascade on delete cascade
);
-- Sample transactions
-- Insert a top category
begin transaction;
insert into Node(Name, Type) values ('Top','C');
insert into Category(Name) values ('Top');
commit;
-- Insert child categories
begin transaction;
insert into Node(Name, Type) values ('C1','C');
insert into Category(Name) values ('C1');
insert into NodeCategory(Name, ParentName) values ('C1', 'Top');
commit;
begin transaction;
insert into Node(Name, Type) values ('C2','C');
insert into Category(Name) values ('C2');
insert into NodeCategory(Name, ParentName) values ('C2', 'Top');
commit;
begin transaction;
insert into Node(Name, Type) values ('C3','C');
insert into Category(Name) values ('C3');
insert into NodeCategory(Name, ParentName) values ('C3', 'Top');
commit;
begin transaction;
insert
[sqlite] behavior with inconsistent pragma journal_mode
process:
- create a new db and set pragma journal_mode=PERSIST
- create a table
- open the same db again, without setting journal mode
- create another table
Result:
second create table statement returns SQLITE_IOERR (code 10) after notable
delay
Honestly, I'm not very proud of this use case (one client tries to optimize
access times, the other doesn't care), but a more graceful handling or a
warning in the documentation would be nice.
Example code:
#include "sqlite3.h"
#include
#include
void Check(sqlite3 * db, int err)
{
char const * msg = sqlite3_errmsg(db);
assert(err == SQLITE_OK);
}
void Exec(sqlite3 * db, char const * sql)
{
Check(db, sqlite3_exec(db, sql, nullptr, nullptr, nullptr));
}
int main()
{
char const * path = "c:\\temp\\db1.sqlite";
std::remove(path);
sqlite3 * db = 0;
Check(db, sqlite3_open(path, ));
Exec(db, "pragma journal_mode=PERSIST");
Exec(db, "CREATE TABLE t1 (id INTEGER PRIMARY KEY AutoIncrement, value
STRING)");
sqlite3 * db2 = 0;
Check(db2, sqlite3_open(path, ));
Exec(db2, "CREATE TABLE t2 (id INTEGER PRIMARY KEY AutoIncrement, value
STRING)");
Check(db2, sqlite3_close(db2));
Check(db, sqlite3_close(db));
}
sqlite amalgamation 3.28.0.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
