Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1

to be missing. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request - Tcl variables as "value-list"s

the parser. Is that still true? For reference, here are extracts from my old emails: On 10/4/2007 9:35 PM, Andy Goth wrote: See the bottom of http://wiki.tcl.tk/2633 for more details. My current project would benefit from the ability to expand a Tcl variable into multiple SQL values. This

Re: [sqlite] if possible point another table

ELECT 'SELECT * FROM ' || tablename FROM aa}] # Join subqueries with UNION ALL, then execute and display result. puts [query db [join $queries " UNION ALL "]] -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/c

Re: [sqlite] if possible point another table

On 4/3/2014 10:10 PM, Keith Medcalf wrote: select * from k1 union select * from k2; My understanding of the question was, how to select from tables whose names are somehow computed or extracted from another table -- Andy Goth

[sqlite] sqlite3 shell man page not installed

"make install" in the SQLite source tree (obtained via Fossil) does not install the sqlite3 shell man page, sqlite3.1. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite has no sqlite_bind_auto or similary named function

and SQLite will do the conversions on demand. Read up on duck typing sometime. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] printf function is shown in docs but not found when I try it

example: $ tclsh % package require sqlite3 % sqlite3 db :memory: % db function printf format % db eval {select printf('%05.1f', 12.3)} 012.3 -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] INSERT OR REPLACE

://www.sqlite.org/lang_createtable.html -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CTEs and unions

cte ...> union all ...> select b from cpy; Error: no such table: cte All these queries work for me without error. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CTEs and unions

On 4/22/2014 5:55 PM, Andy Goth wrote: On 4/22/2014 5:16 PM, Dominique Devienne wrote: sqlite> with cte(a) as (select 1) ...> select * from cte ...> union all ...> select * from cte; Error: no such table: cte All these queries work for me without error. http://www.sq

Re: [sqlite] WITHOUT ROWID option

On 5/8/2014 10:11 AM, Jim Morris wrote: To improve efficiency you could add "where 1=2" to avoid returning any rows. Should just check validity. This being SQLite, as previously discussed, you could say "where 0" :^) -- Andy Goth | __

Re: [sqlite] sqlite-users list failed to block large attachment

ttachments. Quite likely it snuck through due to being plain text. I don't normally mind plain text attachments, for instance patches, if they are very small. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-b

Re: [sqlite] Simple Select from IN - from a newbie.

rformance and maintainability, compared to actually normalizing your database. But you say you're stuck, so do what you have to. And honestly, please don't give people with no knowledge of SQL theory the power to set your SQL schema in stone. - -- Andy Goth | -BEGIN PGP SIGNATURE- Version: GnuPG

Re: [sqlite] Simple Select from IN - from a newbie.

example: CREATE TABLE "table" (id INTEGER PRIMARY KEY, a, b, c); CREATE TABLE phrase (id INTEGER PRIMARY KEY, "table" REFERENCES "table", a, b, c); So that's what double quotes means. Single quotes, on the other hand, are used to enter string literals. - -- And

[sqlite] SQLAR pronunciation

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have to ask, and I apologize if it's been asked before, but... how is SQLAR pronounced? My best guess is "squalor". :^) http://www.sqlite.org/sqlar/doc/trunk/README.md - -- Andy Goth | -BEGIN PGP SIGNATURE- Version: Gnu

[sqlite] Feature request - Tcl variables as "value-list"s

n will happen within SQLite and not Tcl. That's the whole point; it eliminates injection attacks and allows the compiled SQL to be cached inside the Tcl_Obj. -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Feature request - Tcl variables as "value-list"s

On Thu, 4 Oct 2007 21:35:30 -0500, Andy Goth wrote > (See my original proposal writeup at the bottom of > http://wiki.tcl.tk/2633 for more details.) I made a significant update to the bottom of said page. I'll briefly cover it here as well. Basically I revise my proposal to be less g

Re: [sqlite] Feature request - Tcl variables as "value-list"s

On Fri, 05 Oct 2007 15:20:41 +, drh wrote > "Andy Goth" <[EMAIL PROTECTED]> wrote: > > http://wiki.tcl.tk/2633 > > I suggest you go head and write a short TCL procedure to > accomplish the same thing. Like this? proc sql_expand {varname} { upvar

Re: [sqlite] Feature request - Tcl variables as "value-list"s

On Fri, 5 Oct 2007 09:41:27 -0700, Scott Hess wrote > On 10/5/07, Andy Goth <[EMAIL PROTECTED]> wrote: > > proc sql_expand {varname} { > >upvar 1 $varname var > >set result [list] > >foreach elem $var { > > lappend result '[string map {' '

Re: [sqlite] Problems with SQLite and PHP

which is taking place. Me, I'd find out what's really happening by running SQLite in strace and looking for open() calls. -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] built in function hex

nction . (By the way, Author, you might want to rename the example function to not collide with the built-in hex() function.) -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Making BEGIN IMMEDIATE the default.

GIN does not nest, so you have to make sure no other transactions are active before starting a new one. The 'transaction' method takes care of all of these details automatically." You might want to be a little bit more clear about the fact that [transaction] nests even though

Re: [sqlite] Making BEGIN IMMEDIATE the default.

't nest either. Also, I didn't explain my typographical convention: [bracketed] words are Tcl commands, CAPITALIZED words are SQL keywords. -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -

[sqlite] fts3 - "SQL logic error or missing database" on all match words negated

r nothing but negated words, but I think it should result in an empty list, not an SQL error. I mean, the match words often come from a Web , and I don't think I should have to write code to check for this situation. -- Andy Goth <[EMA

[sqlite] fts3 - primary key doesn't seem to do anything

will avoid this problem by deleting rows matching the primary key before inserting/replacing them. -- Andy Goth <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] sql optimization question

1 sqlite> In response to the deletion of (0,null), the trigger fired, deleting (1,0), and (2,0). But the trigger didn't fire again in response to either of these subsequent deletions, so (3,1) was not automatically deleted. If anyone knows how to get ar

Re: [sqlite] fts3 - "SQL logic error or missing database" on all match words negated

nient, so since this couldn't be supported, it's an error. Then how does "select * from my_fts3_table" work? -- Andy Goth | <[EMAIL PROTECTED]> | http://andy.junkdrome.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] fts3 - "SQL logic error or missing database" on all match words negated

be empty string, as in all rows contain empty string. :^) Then in any match query lacking nonnegated words (i.e. empty match query or entirely negative match query), the match words' index sets are intersected with or subtracted from this

[sqlite] tclsqlite.c DB_COPY: binary mode?

nd there's no seeking and no reopening). -- Andy Goth <[EMAIL PROTECTED]> <[EMAIL PROTECTED]> signature.asc Description: OpenPGP digital signature

[sqlite] [$db incrblob] and asynchronous [chan copy]

]. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Tcl copy command

right now. And last, a question. Are there any other functionalities common to the SQLite shell and Tcl extension which could become common code? -- Andy Goth | signature.asc Description: OpenPGP digital signature ___ sqlite-users maili

Re: [sqlite] Idea: defining table-valued functions directly in SQL

On 6/9/2018 8:31 PM, Simon Slavin wrote: > On 10 Jun 2018, at 2:18am, Andy Goth wrote: >> Skip computed columns in the value list? If two tables have the same >> schema, this should duplicate one into the other, but apparently not: >> >> INSERT INTO table2 SELECT * fr

Re: [sqlite] Idea: defining table-valued functions directly in SQL

that exist in the underlying tables. I wish SELECT statement expressions could refer not only to input columns but also output columns that have been named using AS, but we don't have this feature. -- Andy Goth | ___ sqlite-users mailing list sq

Re: [sqlite] Idea: defining table-valued functions directly in SQL

On 06/09/18 20:10, Simon Slavin wrote: On 10 Jun 2018, at 2:00am, Andy Goth wrote: CREATE TABLE tempLog ( datestamp TEXT COLLATE NOCASE PRIMARY KEY , centTemp REAL); CREATE VIEW tempLogView AS SELECT * , centTemp * 9 / 5 + 32 AS fahrTemp FROM tempLog; Yes

Re: [sqlite] Idea: defining table-valued functions directly in SQL

/c_deterministic.html -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

uld be no different, provided they return exactly one column. CREATE VIEW multiples AS VALUES (x) UNION ALL VALUES (x * 2) UNION ALL VALUES (x * 3) PARAMETERS (x); SELECT * FROM foo WHERE (a, b, c) IN multiples(a); -- Andy Goth |

[sqlite] Idea: defining table-valued functions directly in SQL

eas arguments are values. I'm not sure this distinction is important, but I went with it anyway. I used views for my syntax examples, but I'd also like to see this work for common table expressions. WITH double AS (SELECT arg * 2 PARAMETERS (arg)) SELECT

[sqlite] SQLite 3.24.0 Solaris 9 build failure

ris 9 without breaking other Solaris/SunOS platforms. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

ALUES ('Bob', 33), ('Jen', 19), ('Liz', 30); SELECT people.name AS name , count(other.name) AS nolder FROM people LEFT JOIN people AS other ON (other.age > people.age) GROUP BY people.name; namenolder -- -- Bob

[sqlite] CASE and NULL

the two "sleep" cases (or the "commute" cases) to be combined with OR: "WHEN BETWEEN 22 AND 24 OR BETWEEN 0 and 6 THEN 'sleep'". But I imagine this would complicate the parser far beyond any practical benefit. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

n aggregate function? But before we get bogged down in semantics, I ask whether or not this distinction even matters. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

it's okay to simply say "lp" in the above indexing expressions, rather than "lp.lp" which is what I believe is meant. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lazy JSON data to sqlite database

If you have an SQL schema that works for you and also sample data, I might be able to assist writing the conversion program. Dunno if you're interested since it sounds like you won't need to do this again for another year. On Sat, Jan 19, 2019, 23:17 David Bicking >> Is there a tool out there

Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

You can try reading the Fossil source code to see how it handles full-text searching across multiple tables. https://fossil-scm.org/index.html/artifact?fn=src/search.c=trunk On Sat, Jan 19, 2019, 06:10 Scott > I apologize, I sent this from a different email than I registered > accidentally. >

Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure

Dennis Clarke wrote: > On 2018-07-28 08:33, Andy Goth wrote: >> SQLite 3.24.0 fails to build on Solaris 9 (a.k.a. Solaris 2.9) > It may be [worth] while to spin up a Solaris 9 zone on a Solaris 10 or > Solaris 11 server for this purpose. I don't have access to any Solaris serv

Re: [sqlite] Custom aggregate functions in Tcl

Oh yeah, I meant to say that I was going to leave window functions for future expansion. First I need to get more familiar with their use. Yesterday was my first time implementing an aggregate function, and I need to work my way up. On Tue, Jan 29, 2019, 07:46 Richard Hipp On 1/29/19, Andy Goth

[sqlite] Custom aggregate functions in Tcl

I wish to define custom aggregate functions in Tcl, but this capability is currently not exposed through the Tcl interface. Thus I am thinking about how best to add it. Here's a first crack at a design proposal: Extend the [db function] command to accept an -aggregate switch that makes the new

Re: [sqlite] Custom aggregate functions in Tcl

On 1/29/19 1:15 AM, Andy Goth wrote: I wish to define custom aggregate functions in Tcl Initial implementation: https://chiselapp.com/user/andy/repository/sqlite-andy/info/e0689f05d1f8792d Sample program, intended to be run from the root of a built SQLite tree: #!/usr/bin/env tclsh load

Re: [sqlite] Custom aggregate functions in Tcl

turn value, making it possible to modify it in-place without incurring copy-on-write, as documented in the tclSqlFuncStep() comments. Overall, my preference is to avoid creating global named objects when anonymous values will do the job, hence my use of [apply]. -- Andy Goth |

Re: [sqlite] Custom aggregate functions in Tcl

ents where the first argument > is "final". Then when you go to add the xValue and xInverse routines > for window functions, you will have a convenient way to distinguish > those calls from xStep and xFinal. > > On 1/30/19, Andy Goth wrote: > > On 1/29/19 1:15 AM,

Re: [sqlite] Custom aggregate functions in Tcl

} e 5] [value {c 3 d -4 e 5}] d {c 3 d -4 e 5} [inverse {c 3 d -4 e 5} c 3] [step {d -4 e 5} f -6] [value {d -4 e 5 f -6}] e {d -4 e 5 f -6} [inverse {d -4 e 5 f -6} d -4] [value {e 5 f -6}] f {e 5 f -6} [destroy {e 5 f -6}] -- Andy Goth | ___ sqlite-

Re: [sqlite] Custom aggregate functions in Tcl

On 1/30/19 3:27 PM, Andy Goth wrote: The next chance I get (probably tomorrow morning), I'll go ahead and add "step" or "final" as the initial argument to aggregate functions. I'll also lift the prohibition on aggregate functions with no arguments. This change is n