Re: [sqlite] tcl binding: pkgIndex.tcl not portable
Didn't get any response on this... Overseen or not worth to mention? Should I open a ticket? >-Ursprüngliche Nachricht- >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag >von Schmitz, Uwe >Gesendet: Donnerstag, 24. Januar 2019 17:22 >An: sqlite-users@mailinglists.sqlite.org >Betreff: [sqlite] tcl binding: pkgIndex.tcl not portable > >The pkgIndex.tcl which is generated when using >--enable-tcl contains an absolute path to the shared library. >That is not portable. > >It's better to use the $dir variable, which is automatically >created by the package loading process, to locate the >library file, e.g. change lines 1419-1420 in Makefile.in to >- >pkgIndex.tcl: > echo 'package ifneeded sqlite3 $(RELEASE) [list load [file join $$dir >libtclsqlite3[info sharedlibextension]] sqlite3]' > $@ >- > >Although the selection of the right lib extension is delegated >to Tcl. > >Regards, >Uwe > >___ >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] Vtab scalar function overloading in aggregate queries
This may not strictly be a bug, but currently (3.27.2) a vtab cannot overload scalar functions in aggregate queries. Adding a check for TK_AGG_COLUMN in sqlite3VtabOverloadFunction makes my use case function as expected. -Jake ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation is lying to me
In this SQLite3 documentation (https://www.sqlite.org/pragma.html#pragfunc), it says that information_schema could be implemented by doing something like this: ATTACH ':memory:' AS 'information_schema'; CREATE VIEW information_schema.schemata(schema_name) AS SELECT name FROM pragma_database_list(); However, when attempting to do this, we get an error: "no such table: information_schema.pragma_database_list”. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Documentation is lying to me
Unable to reproduce: >sqlite SQLite version 3.28.0 2019-02-25 18:43:54 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> ATTACH ':memory:' AS 'information_schema'; sqlite> CREATE VIEW information_schema.schemata(schema_name) AS ...> SELECT name FROM pragma_database_list(); sqlite> sqlite> select * from schemata; main information_schema sqlite> .exit What version of SQLite are you using? What do the following commands do? pragma database_list; select * from pragma_database_list(); --- 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 Joshua Thomas Wise >Sent: Tuesday, 26 February, 2019 19:06 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Documentation is lying to me > >In this SQLite3 documentation >(https://www.sqlite.org/pragma.html#pragfunc), it says that >information_schema could be implemented by doing something like this: > >ATTACH ':memory:' AS 'information_schema'; >CREATE VIEW information_schema.schemata(schema_name) AS >SELECT name FROM pragma_database_list(); > >However, when attempting to do this, we get an error: "no such table: >information_schema.pragma_database_list”. >___ >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] Getting data from two JOIN tables
Sorry to bother you with this simple request, but I can't seem to come up with
a solution. Imagine these tables:
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
'2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
'2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
'2019-02-13'); <-
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
'2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
'2019-02-13');
create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
'2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
'2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
'2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
'2019-02-16');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
'2019-02-16');
insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
'2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
'2019-02-18'); <-
insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
'2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
'2019-02-18');
What I am trying to do is to SELECT the max(idate) items a, b and c from t and
g, h, and i from z where t.a and z.f = 'p001' in both tables. So, what I want
is,
'p001', 'a', 3, 'a', 3, 'a'
from the combination of both <-. So, here is what I have tried,
select a.a, a.b, a.c, b.g, b.h, b.i from t as a join z as b on a.a = b.f
where a.a = 'p001'
group by a.a
having
( a.idate = (select max(a.idate) from t as c where a.n = c.n)
and
b.idate = (select max(b.idate) from z as d where b.n = d.n)
);
But, I get nothing.
sqlite> select a.a, a.b, a.c, b.g, b.h, b.i from t as a join z as b on a.a = b.f
...> where a.a = 'p001'
...> group by a.a
...> having
...> ( a.idate = (select max(a.idate) from t as c where a.n = c.n)
...> and
...> b.idate = (select max(b.idate) from z as d where b.n = d.n)
...> );
sqlite>
Any help would be greatly appreciated. Thanks.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting data from two JOIN tables
select a, b, c, g, h, i
from t outer_t, z outer_z
where a == f
and a == 'p001'
and outer_t.idate == (select max(idate) from t where a == outer_t.a)
and outer_z.idate == (select max(idate) from z where f == outer_z.f)
;
This requires that t(a, idate) is unique and that z(f, idate) is unique (in
which case you should declare it so in order to prevent violation of your
requirements). If that assumption is incorrect then there is no solution (or
more correctly that your schema is improperly normalized).
---
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 Jose Isaias Cabrera
>Sent: Tuesday, 26 February, 2019 20:09
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Getting data from two JOIN tables
>
>
>Sorry to bother you with this simple request, but I can't seem to
>come up with a solution. Imagine these tables:
>create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
>insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
>'2019-02-11');
>insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
>'2019-02-12');
>insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
>'2019-02-13'); <-
>insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
>'2019-02-13');
>insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
>'2019-02-13');
>
>create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
>insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
>'2019-02-15');
>insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
>'2019-02-15');
>insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
>'2019-02-15');
>insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
>'2019-02-16');
>insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
>'2019-02-16');
>insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
>'2019-02-17');
>insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
>'2019-02-17');
>insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
>'2019-02-17');
>insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
>'2019-02-17');
>insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
>'2019-02-18');
>insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
>'2019-02-18'); <-
>insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
>'2019-02-18');
>insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
>'2019-02-18');
>insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
>'2019-02-18');
>insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
>'2019-02-18');
>
>What I am trying to do is to SELECT the max(idate) items a, b and c
>from t and g, h, and i from z where t.a and z.f = 'p001' in both
>tables. So, what I want is,
>
>'p001', 'a', 3, 'a', 3, 'a'
>
>from the combination of both <-. So, here is what I have tried,
>
>select a.a, a.b, a.c, b.g, b.h, b.i from t as a join z as b on a.a =
>b.f
>where a.a = 'p001'
>group by a.a
>having
>( a.idate = (select max(a.idate) from t as c where a.n = c.n)
>and
>b.idate = (select max(b.idate) from z as d where b.n = d.n)
>);
>
>But, I get nothing.
>
>sqlite> select a.a, a.b, a.c, b.g, b.h, b.i from t as a join z as b
>on a.a = b.f
> ...> where a.a = 'p001'
> ...> group by a.a
> ...> having
> ...> ( a.idate = (select max(a.idate) from t as c where a.n = c.n)
> ...> and
> ...> b.idate = (select max(b.idate) from z as d where b.n
>= d.n)
> ...> );
>sqlite>
>
>Any help would be greatly appreciated. Thanks.
>
>josé
>___
>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
Re: [sqlite] Documentation is lying to me
On 27/2/19 10:06 AM, Joshua Thomas Wise wrote: > In this SQLite3 documentation (https://www.sqlite.org/pragma.html#pragfunc), > it says that information_schema could be implemented by doing something like > this: > > ATTACH ':memory:' AS 'information_schema'; > CREATE VIEW information_schema.schemata(schema_name) AS > SELECT name FROM pragma_database_list(); > > However, when attempting to do this, we get an error: "no such table: > information_schema.pragma_database_list”. What does: SELECT sqlite_version(); return? From the link you posted: > The table-valued functions for PRAGMA feature was added in SQLite version > 3.16.0 (2017-01-02). Prior versions of SQLite cannot use this feature. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
