Re: [sqlite] Question about: Adding a record to a table with select failure

[Jake Thaw]

One approach might be something like this:

INSERT INTO t (a, b, c, d, e, idate)
SELECT 'p006',
   Coalesce(b, 1),
   Coalesce(c, 2),
   'y',
   Coalesce(e, 4),
   '2019-20-12'
  FROM (SELECT 1)
  LEFT JOIN
   (SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC LIMIT 1);

A slightly more succinct (but not universal) way:
Note: see point 1 of
https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause

INSERT INTO t (a, b, c, d, e, idate)
SELECT IfNull('p006', Max(idate)),
   IfNull(b, 1),
   IfNull(c, 2),
   'y',
   IfNull(e, 4),
   '2019-20-12'
  FROM t
 WHERE a = 'p006';

On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera  wrote:
>
>
> Doug, on Friday, November 15, 2019 11:42 AM, wrote...
> >
> > WRT Jose's original context, and just for my enlightment, what happens with 
> > the following:
> >
> > insert into t (a, b, c, d, e, idate)
> > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
> >
> > where p999 does not define a record? Is a new record inserted with values 
> > of a,b,c, and e null?
>
> Ok, I promise that this will be the last email on this for me:
>
> I just came to my senses, and sometimes, I need to insert when the 'a' value 
> does not exists, as Doug just brought to my attention.  So, I am trying to 
> insert a record with two new values using the last existing 'a'.  If a does 
> not exists, then I need to add that record with the two values.  I have been 
> trying a few INSERT with CASEs, but nothing is working.  I know one of you 
> will make it look easy, but this is what I have done as of now:
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, 
> '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, 
> '2019-02-11');
> select * from t;
> 1|p001|1|2|n|4|2019-02-11
> 2|p002|2|2|n|4|2019-02-11
> 3|p003|3|2|n|4|2019-02-11
> 4|p004|4|2|y|4|2019-02-11
> 5|p005|5|2|y|4|2019-02-11
>
> I have tried various combination of the following,
>
> insert into t (a, b, c, d, e, idate) VALUES
> (
> CASE
>SELECT a from t WHERE a = 'p006' idate desc limit 1
> WHEN a = NULL
> THEN 'p006',1,2,'y',4,'2019-02-12'
> ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate 
> desc limit 1
> END
> );
> Error: near "SELECT": syntax error
> sqlite>
>
> But, different syntax error have popped.  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.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding a record to a table with one value change

[Jake Thaw]

Why not like this?

insert into t (a, b, c, d, e, idate)
SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' ORDER BY
idate desc limit 1;

On Fri, Nov 15, 2019 at 9:19 AM Simon Slavin  wrote:
>
> On 14 Nov 2019, at 10:06pm, Jose Isaias Cabrera  wrote:
>
> > insert into t (a, b, c, d, e, idate) values
> >  (
> >(SELECT a FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
> >(SELECT b FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
> >(SELECT c FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
> >'y',
> >(SELECT e FROM t WHERE a = 'p001' ORDER BY idate desc limit 1),
> >'2019-02-12'
> >  );
> >
> > Is there a simpler way?  Thanks.
>
> No simpler way.  I suggest you duplicate the exiting row first, then UPDATE 
> the duplicate.
> ___
> 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] Import XLS file?

[Jake Thaw]

You might like to consider the xlsx virtual table I wrote last year:

https://github.com/jakethaw/xlsx_vtab

Note that this only works for xlsx files, and may have unexpected
results in some circumstances (e.g. merged cells).

On Wed, Oct 2, 2019 at 4:39 AM Winfried  wrote:
>
> Hello,
>
> I need to import an XLS file into SQLite.
>
> The  web site    doesn't seem to show how, and
> searching the archives  here    didn't
> help.
>
> FWIW, the data are in the file's first sheet, while other sheets only
> contain text infos about the data.
>
> Thank you.
>
>
>
> --
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug: WINDOW clause within a CTE

[Jake Thaw]

The following examples demonstrate a possible bug when using a WINDOW
clause within a CTE:

SQLite version 3.30.0 2019-09-14 16:44:51
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE x AS SELECT 1 a UNION SELECT 2;
sqlite>
sqlite> -- Unexpected result - expect 1,1
sqlite> WITH y AS (
   ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a))
   ...> SELECT * FROM y;
1
2
sqlite> -- Unexpected result - expected "Error: no such column: fake_column"
sqlite> WITH y AS (
   ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION
BY fake_column))
   ...> SELECT * FROM y;
1
2
sqlite> -- Possible unexpected result - expected "Error: no such
column: fake_column"
sqlite> SELECT 1 WINDOW win AS (PARTITION BY fake_column);
1
sqlite> -- Expected result
sqlite> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a);
1
1
sqlite> -- Expected result
sqlite> WITH y AS (SELECT Row_Number() OVER (PARTITION BY a) FROM x)
   ...> SELECT * FROM y;
1
1
sqlite> -- Expected result
sqlite> SELECT * FROM (
   ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a));
1
1
sqlite>


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

Re: [sqlite] "unable to use function highlight in the requested context" in group by

[Jake Thaw]

Dan Kennedy explained why this limitation exists:

>On 27/2/62 05:47, Jake Thaw wrote:
>>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.
>
>I think it has always been this way.
>
>The tricky case is when there is a GROUP BY with an expression list that the 
>virtual table cannot optimize as an ORDER BY. In that case the virtual table 
>cursor will be closed before the overloaded function is invoked, which breaks 
>the implementation of most overloaded functions (including the built-in ones).
>
>Dan.

-Jake

On Sat, Jul 13, 2019 at 7:36 PM Damian Adrian  wrote:
>
> Thank you, that works!
>
> I still think highlight() not working in this case may be a bug, though,
> since it only operates on a single row and in theory shouldn't be affected
> by the group by (but there may be limitations not obvious to me).
>
> Thanks again,
> Adrian
> ___
> 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] Is it possible to transpose a table using SQL?

[Jake Thaw]

Hi Shane,

You might be interested in trying out my pivot virtual table implementation.

https://github.com/jakethaw/pivot_vtab

This will cater for changing values, but like a VIEW implementation,
it does not satisfy your criteria of dynamically changing
rows/columns. Changes to rows/columns can only be propagated by
dropping and re-creating the virtual table.

I have provided an example below using your data structure, however it
works nicer with normalized data.

e.g.

.load ./pivot_vtab
.headers on
.mode column

CREATE TABLE t(
  "Product/Region",
  Belgium,
  France,
  USA
);
INSERT INTO t VALUES
('Oil_filter', 1, 2, 3),
('Spark_plug', 4, 5, 6),
('Coolent', 7, 8, 9);

CREATE VIRTUAL TABLE pivot USING pivot_vtab(
 (SELECT name "Product/Region" -- pivot table key
FROM pragma_table_info('t')
   WHERE name <> 'Product/Region'),

 (SELECT "Product/Region", -- pivot column key - can be referenced in
pivot query as ?2,
 "Product/Region"  -- pivot column name
FROM t),

 (SELECT CASE ?1
   WHEN 'Belgium' THEN Belgium
   WHEN 'France' THEN France
   WHEN 'USA' THEN USA
 END
FROM t
   WHERE "Product/Region" = ?2)
);

SELECT *
  FROM pivot;

Product/Region  Oil_filter  Spark_plug  Coolent
--  --  --  --
Belgium 1   4   7
France  2   5   8
USA 3   6   9

-Jake

On Mon, Apr 1, 2019 at 7:07 AM Shane Dev  wrote:
>
> Hello,
>
> Is it possible to create a view which switches rows and columns of a
> dynamically changing table?
>
> For example, imagine we have table t1 where both columns and rows could
> change after the view has been created
>
> sqlite> select * from t1;
> Product/Region|Belgium|France|USA
> Oil_filter|1|2|3
> Spark_plug|4|5|6
> Coolent|7|8|9
>
> Could view v1 be created such that
>
> sqlite> select * from v1;
> Product/Region|Oil_filter|Spark_plug|Coolent
> Belgium|1|4|7
> France|2|5|8
> USA|3|6|9
> ___
> 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

[Jake Thaw]

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

Re: [sqlite] Variable Declaration

[Jake Thaw]

There does exist an experimental branch which introduces the concept
of shell variables.

https://www.sqlite.org/src/timeline?r=shell-bindings

The check-in comment describes the usage:

"Add the ability to use bind parameters in the CLI. The new ".set
KEY=VALUE" dot-command works to set bindings. Or use the "-Dkey=value"
command-line option. Or use the built-in shell_bindings(k,v) virtual
table to set, delete, or changing bindings."

Note: A bug exists when setting text/blob values via the
shell_bindings virtual table. The bindvtabUpdate method fails to set
p->len. Adding p->len = len resolves the issue.

On Sun, Jan 20, 2019 at 1:15 PM  wrote:
>
> I'm jumping into the middle of the conversation, possibly without seeing the 
> beginning, but from what I've seen so far, it seems to me that the key is 
> recognizing there are many tools in a programmer's toolbox.  Each tool covers 
> a certain area of functionality really well, then there are areas where it 
> can be applied but with difficulty, and then the rest is off-limits for any 
> serious applications.  There is considerable overlap between the 
> applicability of different tools, so you get to pick your favorites.  But you 
> have to be willing to incorporate additional tools when you get to the limits 
> of comfort for your favorite tool.
>
> However, instead what I often see is people trying to expand the scope of 
> their favorite.  Sometimes this is the right choice, but usually it is not.  
> It takes considerable experience and good taste to be able to wisely judge 
> the difference and to chart a roadmap that legitimately enhances tools in a 
> way that justifies the cost to maintenance, documentation, ease of learning, 
> and so forth.  I know I've been on the wrong side of this many times, but I 
> try to learn from my mistakes.
>
> SQLite is a special sort of tool in that it expressly acknowledges its 
> limitations, and it lets those limitations be its strength.  I mean, "Lite" 
> is right there in the name.  The idea of having a larger number of more 
> limited tools, with a stronger focus on interoperability, is a very powerful 
> and transformative engineering mindset and is central to the Unix design 
> philosophy (which, by the way, is often at odds with the Unix design 
> practice).  The point of SQLite, in contrast to SQL servers, is that it be 
> embeddable in your application.  Thus, SQLite has no need to replicate the 
> capabilities already present in every reasonable programming language.  
> Namely, variables.
>
> I do see a valid objection earlier in the thread: the SQLite shell.  It 
> indeed lacks variables, though they can be emulated with temporary tables.  
> This is what I was talking about in the first paragraph.  It's possible to 
> stretch tools beyond their core competency, but it can be difficult, so 
> you're better off letting other tools fill the gaps.  But when you're using 
> the SQLite shell, that's not really a good option.  You can let the SQLite 
> shell be the backend to a shell script, but you're not going to want to call 
> it more than once or twice per complete run of a program.
>
> So rather than discuss adding variable declarations to SQLite, maybe we 
> should be talking about adding variable declarations to the SQLite shell.  
> Please don't think I'm making a serious proposal.  Instead it's just for the 
> sake of argument.  Consider what would come of doing that.  We'd be taking 
> the SQLite shell in the direction of becoming a programming language unto 
> itself, complementary to yet distinct from SQLite.  Once the SQLite shell 
> gets variables, there would also be clamor for loops and conditionals and 
> function declarations and fine-grained I/O and so forth.  That would be cool, 
> I suppose.  And then, once all that is implemented, why not migrate the new 
> functionality from the shell into SQLite proper?  But, umm, don't we already 
> have dozens or hundreds of programming languages to choose from?  Does SQLite 
> really need to compete in that arena?
>
> Instead, here's what I think.  SQLite is extensible by virtue of being 
> embeddable; it can be embedded in a wrapper that expands its capabilities.  
> Furthermore, the sources are maximally open and free, including the Lemon 
> parser.  If you want to add new syntax to SQLite to ease its integration with 
> another programming language, start by duplicating and extending its parser 
> to recognize variable declaration and usage.  The output of this new parser 
> would be plain-Jane SQLite SQL, augmented with all the necessary variable 
> declarations, statement compiles, parameter bindings, statement steps, column 
> extractions, error checks, and so on needed to make it happen.
>
> So, figure out exactly what you want the new syntax to look like, and write a 
> lot of sample queries in the updated language.  For each example, write the 
> equivalent C code.  Then, it will be your task to make a parser that knows 
> how to 

Re: [sqlite] SQLite 3.26.0 recursive CTE performance regression

[Jake Thaw]

Hi Sebastian,

You can achieve better performance by constructing the path as you
walk the tree.

e.g.

WITH
tree(
  id,
  depth,
  path
) AS (
SELECT id,
   1,
   id
  FROM languoid
 WHERE parent_id IS NULL
UNION ALL
SELECT l.id,
   t.depth+1,
   t.path || '/' || l.id
  FROM tree t
  JOIN languoid l ON t.id = l.parent_id
)
SELECT id,
   depth,
   path
  FROM tree
 ORDER BY 1;

-Jake

On Sat, Dec 22, 2018 at 11:53 PM Sebastian Bank
 wrote:
>
> Hi,
>
> given a table that represents an adjacency tree, I use a recursive CTE
> together with group_concat() to generate the path for each tree item.
>
> With SQLite up to version 3.25.3 the query below (with the 500 example
> items inserted below) takes about 0.2 seconds on my system. With version
> 3.26.0 it takes over 6 seconds (with the full data set of around 24000
> items, it seems to become infeasible).
>
> Thanks and best,
>
> Sebastian Bank
>
>
> CREATE TABLE languoid (
>id VARCHAR(8) NOT NULL,
>parent_id VARCHAR(8),
>PRIMARY KEY (id),
>FOREIGN KEY(parent_id) REFERENCES languoid (id)
> );
>
> INSERT INTO languoid (id, parent_id) VALUES
> ("abin1243", NULL), ("abis1238", NULL), ("abkh1242", NULL), ("abkh1243",
> "abkh1242"),
> ("abaz1241", "abkh1243"), ("ashk1247", "abaz1241"), ("bezs1238",
> "abaz1241"), ("tapa1256", "abaz1241"),
> ("abkh1244", "abkh1243"), ("abzh1238", "abkh1244"), ("bzyb1238",
> "abkh1244"), ("samu1242", "abkh1244"),
> ("circ1239", "abkh1242"), ("adyg1241", "circ1239"), ("abad1240",
> "adyg1241"), ("bezh1247", "adyg1241"),
> ("natu1243", "adyg1241"), ("shap1240", "adyg1241"), ("xaku1238",
> "adyg1241"), ("kaba1278", "circ1239"),
> ("grea1271", "kaba1278"), ("ubyk1235", "abkh1242"), ("abun1252", NULL),
> ("abun1253", "abun1252"),
> ("abun1254", "abun1252"), ("abun1255", "abun1252"), ("adai1235", NULL),
> ("afro1255", NULL),
> ("berb1260", "afro1255"), ("awji1241", "berb1260"), ("ghad1239",
> "berb1260"), ("aytw1238", "ghad1239"),
> ("eltu1238", "ghad1239"), ("kaby1244", "berb1260"), ("atla1275",
> "kaby1244"), ("cent2194", "atla1275"),
> ("cent2195", "cent2194"), ("stan1324", "cent2194"), ("nort3248",
> "atla1275"), ("ghom1257", "nort3248"),
> ("senh1238", "nort3248"), ("tach1250", "atla1275"), ("susi1238",
> "tach1250"), ("kaby1243", "kaby1244"),
> ("grea1281", "kaby1243"), ("less1241", "kaby1243"), ("nafu1238",
> "berb1260"), ("jbal1238", "nafu1238"),
> ("jerb1241", "nafu1238"), ("jerb1242", "nafu1238"), ("tame1243",
> "nafu1238"), ("zuar1238", "nafu1238"),
> ("siwi1238", "berb1260"), ("sawk1238", "siwi1238"), ("siwi1239",
> "siwi1238"), ("tuar1240", "berb1260"),
> ("sout3263", "tuar1240"), ("tama1365", "sout3263"), ("tadg1238",
> "tama1365"), ("tadh1242", "tama1365"),
> ("timb1263", "tama1365"), ("tawa1286", "sout3263"), ("ioul1238",
> "tawa1286"), ("tawa1287", "tawa1286"),
> ("tawa1288", "tawa1286"), ("taya1257", "sout3263"), ("airr1242",
> "taya1257"), ("tana1297", "taya1257"),
> ("taha1241", "tuar1240"), ("ghat1242", "taha1241"), ("hogg1238",
> "taha1241"), ("unun9880", "berb1260"),
> ("guan1277", "unun9880"), ("west2724", "berb1260"), ("tets1235",
> "west2724"), ("zena1248", "west2724"),
> ("zena1250", "berb1260"), ("chen1266", "zena1250"), ("moza1250",
> "zena1250"), ("ouar1239", "moza1250"),
> ("taga1278", "ouar1239"), ("oued1238", "taga1278"), ("tari1264",
> "taga1278"), ("tema1244", "taga1278"),
> ("tema1243", "ouar1239"), ("tazn1238", "moza1250"), ("gour1247",
> "tazn1238"), ("sout3056", "tazn1238"),
> ("toua1238", "tazn1238"), ("tidi1241", "moza1250"), ("tidi1242",
> "tidi1241"), ("titt1238", "tidi1241"),
> ("tumz1238", "moza1250"), ("sene1271", "zena1250"), ("nucl1705",
> "sene1271"), ("tmag1238", "sene1271"),
> ("tach1249", "zena1250"), ("tari1263", "zena1250"), ("east2803",
> "tari1263"), ("riff1234", "tari1263"),
> ("east2804", "riff1234"), ("beni1249", "east2804"), ("cent2333",
> "east2804"), ("arze1238", "cent2333"),
> ("beni1250", "cent2333"), ("beni1251", "cent2333"), ("guel1234",
> "cent2333"), ("tems1234", "cent2333"),
> ("kebd1234", "east2804"), ("sout3264", "east2804"), ("beni1252",
> "sout3264"), ("igze1238", "sout3264"),
> ("meta1239", "sout3264"), ("west2882", "riff1234"), ("boqq1234",
> "west2882"), ("urri1238", "west2882"),
> ("tuni1262", "zena1250"), ("chad1250", "afro1255"), ("bium1280",
> "chad1250"), ("hurz1242", "bium1280"),
> ("mbuk1243", "hurz1242"), ("vame1236", "hurz1242"), ("dume1239",
> "vame1236"), ("gwen1242", "vame1236"),
> ("mayo1276", "vame1236"), ("mber1261", "vame1236"), ("nort3156",
> "bium1280"), ("gida1247", "nort3156"),
> ("lamm1244", "gida1247"), ("higi1241", "nort3156"), ("hyaa1239",
> "higi1241"), ("nkaf1238", "higi1241"),
> ("bana1305", "nkaf1238"), ("gamb1258", "bana1305"), ("gili1246",
> "bana1305"), ("higi1242", "nkaf1238"),
> ("kamw1239", "higi1242"), ("kiry1234", "higi1242"), ("psik1239",
> "higi1241"), ("nucl1685", "psik1239"),
> ("wula1250", "psik1239"), ("zlen1238", "psik1239"), ("jina1243",
> "nort3156"), ("jina1244", "jina1243"),

[sqlite] Bug: Incorrect fsdir result

[Jake Thaw]

The following returns an erroneous fsdir error. Tested on macOS 10.13.6.

SQLite version 3.25.3 2018-11-05 20:37:38
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE d AS SELECT '.' d;
sqlite> CREATE TABLE x AS SELECT 1 x;
sqlite> SELECT name
   ...>   FROM d
   ...>   JOIN x
   ...>   JOIN fsdir(d)
   ...>  ORDER BY 1;
Error: table function fsdir requires an argument
sqlite>


These variants all run as expected:

SELECT name
  FROM d
  JOIN x
  JOIN fsdir('.')
 ORDER BY 1;

SELECT name
  FROM (SELECT '.' d)
  JOIN (SELECT 1 x)
  JOIN fsdir(d)
 ORDER BY 1;

SELECT name
  FROM d
  JOIN x
  JOIN fsdir(d);

SELECT name
  FROM d
  JOIN fsdir(d)
 ORDER BY 1;

Regards

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

[sqlite] Draft Geopoly docs

[Jake Thaw]

The following typos exist in the draft Geopoly docs:

3.9
"new polygon that is a affine transformation"
-> "new polygon that is an affine transformation"

4.1
"each dimension of each coordinate is of 32-byte floating point number"
-> "each dimension of each coordinate is a 32-bit floating point number"

Not mentioned in the docs are:
  - SQLITE_ENABLE_GEOPOLY compile option
  - Aggregate function geopoly_group_bbox

Regards

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

[sqlite] Bug in shell_bindings bindvtabUpdate

[Jake Thaw]

Hi SQLite dev team,

I have been testing the shell-bindings branch, and encountered an issue
when setting text/blob values via the shell_bindings virtual table.

bindvtabUpdate fails to set p->len. Adding p->len = len resolves the issue.

Regards

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

[sqlite] Bug in fsdir

[Jake Thaw]

The following query causes a crash in fsdirNext on Windows 10.

SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT name FROM fsdir('.') JOIN (VALUES(1),(2));
.
./sqlite3.exe
./sqlite3_analyzer.exe
./sqldiff.exe
.

Adding an ORDER BY clause seems to provide workaround for this particular
case.

Regards

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

Re: [sqlite] New pre-release snapshot with performance enhancements

[Jake Thaw]

Hi Dan,

I can confirm that the current snapshot works for me as expected. I was
linking against sqlite-snapshot-201701170010, which predates the session
enhancement.

Thank you for your time.

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

Re: [sqlite] New pre-release snapshot with performance enhancements

[Jake Thaw]

Hello Richard,

Is the following enhancement included as part of this pre-release snapshot?

- Enhance the session extension to support WITHOUT ROWID tables.

The draft documentation still says that this support does not yet exist,
and my test below also demonstrates this. Please let me know if I am
missing something.

/*
** The following function will print an error as expected: "UNIQUE
constraint failed: a.x".
**
** If table a is WITHOUT ROWID, then there will be no error.
*/
void fn(void){
  sqlite3 *db;
  sqlite3_session *pSession = 0;
  int nChangeset;
  void *pChangeset;
  char *sql;
  char *zMsg;

  sqlite3_open(":memory:", );

  sql = "CREATE TABLE a(x PRIMARY KEY) --WITHOUT ROWID";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3session_create(db, "main", );
  sqlite3session_attach(pSession, "a");

  sql = "INSERT INTO a VALUES (1)";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3session_changeset(pSession, , );

  sql = "DELETE FROM a WHERE x=1";
  sqlite3_exec(db, sql, NULL, NULL, NULL);

  sqlite3changeset_apply(db, nChangeset, pChangeset, 0, 0, 0);

  sql = "INSERT INTO a VALUES (1)";
  sqlite3_exec(db, sql, NULL, NULL, );
  if( zMsg ) printf("%s\n", zMsg);
}

/*/

Kind Regards

Jake

On Sun, Feb 5, 2017 at 2:23 AM, Richard Hipp  wrote:

> There is a new pre-release snapshot of SQLite up at
> https://www.sqlite.org/download.html
>
> Change notes can be seen at https://www.sqlite.org/draft/
> releaselog/3_17_0.html
>
> The performance enhancements in the R-Tree extension are of particular
> significance.  If you are able to test out this pre-release snapshot
> in your application, please do so and report results to this mailing
> list, or directly to me.  Thanks.
>
> --
> 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

[sqlite] Efficient relational SELECT

[Jake Thaw]

Hi Simon,

I do this type of query all the time to avoid sub queries and aggregation.
This might be what you are looking for to satisfy the elegance criteria:

SELECT r.room_id,
   b.date
  FROM roomr
  LEFT JOIN
   booking b  ON r.room_id = b.room_id
  LEFT JOIN
   booking b2 ON r.room_id = b2.room_id
 AND b2.date > b.date
 WHERE b2.booking_id IS NULL

I leave it to you to understand why this works.

Regards

Jake