c 3}]
b {a 1 b -2 c 3}
[inverse {a 1 b -2 c 3} a 1]
[step {b -2 c 3} d -4]
[value {b -2 c 3 d -4}]
c {b -2 c 3 d -4}
[inverse {b -2 c 3 d -4} b -2]
[step {c 3 d -4} 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
as 2 arguments 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
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 c
n alternative to
storing state data in the return 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 [
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
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
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 fun
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&ci=trunk
On Sat, Jan 19, 2019, 06:10 Scott
> I apologize, I sent this from a different email than I registered
> accidentally.
>
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 tha
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
ke SQLite build for
Solaris 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
right now:
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('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.ag
directly see sudsol's parameter, I declare both
sudsol and x to be table-valued functions, then I pass the argument
(terminology: parameter value) to each invocation of x. This is more
explicit and requires less magic on the part of SQLite to figure out
where everything is coming from when faced with nested functions.
Also, witness my lame attempt to format the input. :^)
By the way, I'm unclear why 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
eplace food, filename, age,
and hour with complex expressions such as nested queries.
The next evolution in bloat is to also support AND, OR, NOT, and
parentheses, allowing the LHS operand of any operator in a complex
expression to be omitted, defaulting to CASE's first argument. In the
last example above this would allow 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
en does
that mean having LIMIT 1 would also make it an 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
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
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. That is
umns 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 maili
/c_deterministic.html
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
d functions should 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 |
_
really fine point. Parameters are names whereas
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
import and loading it using the Tcl extension. Far from
clean, but it would work 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
-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
as a keyword. For 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
inability, 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 v2.0
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/
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 |
__
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
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
http://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
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/mailma
ng along the way. That end user will *expect* an integer here
and a datetime there, 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
work because it could be at
the nether end of the Internet. You want to do this analysis using your
existing codebase which was designed to operate using SQL. You see
SQLite as a good fit because it's compatible (useful subset of SQL) and
is trivial to set up (link it into y
"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
mbedded in your application, which means it's on equal footing with all
your other application code.
Also yes, do normalize your database design.
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
ata sources. For example, the
SQLite shell has the .import command for loading a file into a table.
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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
7;aaa'),
(32, 1, 'fs', 'bbc');
}
# Get list of subqueries which need to be UNION'ed.
set queries [db eval {SELECT 'SELECT * FROM ' || tablename FROM aa}]
# Join subqueries with UNION ALL, then execute and display result.
puts [query db [join
licts
irreconcilably with the fundamental structure of 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 t
be missing.
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
pport
asynchronous [chan copy].
--
Andy Goth |
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
to the index
that lists all rows? The indexed "word" could even 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
convenient, 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]
-
e> select * from x;
3|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.
#x27;s design, or is it an oversight? Am I missing something?
For now, I 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]
-
ul thing to search for 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 situ
nsaction] doesn'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]
-
lso, BEGIN 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
http://www.sqlite.org/tclsqlite.html#function . (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]
-
ich 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]
-
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
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
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
ansion 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]
-
COPY reads the
rest, and there's no seeking and no reopening).
--
Andy Goth
<[EMAIL PROTECTED]>
<[EMAIL PROTECTED]>
signature.asc
Description: OpenPGP digital signature
55 matches
Mail list logo