[sqlite] Command line sqlite3 program bug

[Domingo Alvarez Duarte]

Hello !  

Every day is a day to learn something new !  

It also have the command line -bail for my example "sqlite3 -bail a.db <
the.sql".  

Interesting that how easy I can miss the basics "when everything else fail,
read the manual !".  

Thank you !  

Cheers !  
>  Tue Sep 08 2015 9:40:37 pm CEST CEST from "Gerry Snyder"
>  Subject: Re: [sqlite] Command line sqlite3 
>program
>bug
>
>  The Command Line Interface has the command:
> 
> ..bail on
> 
> which will do what you want.
> 
> HTH,
> 
> Gerry Snyder
> ---
> On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
>  
>>Hello !
>> 
>> After seem several emails from a user asking about how to use sqlite3
>>through
>> shell scripts, I remember my experiences with sqlite3 but didn't mind to
>> report it, but now I think that it's worth mention it because it'll hurt
>> several users.
>> 
>> The bug/problem is that the sqlite3 command line when feed with a sql
>>script
>> with commands wrapped by a transaction if there is any error in the middle
>>of
>> it sqlite3 reports the error but do not stop/abort the transaction and the
>> database end up in a dirty state.
>> 
>> __example to show the bug/problem
>> 
>> BEGIN;
>> 
>> DROP TABLE IF EXISTS a; --to allow run more than once
>> 
>> CREATE TABLE a(b); --after the next line error this should be rolled back
>> 
>> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>> 
>> INSERT INTO a(b) values(4);
>> 
>> COMMIT;
>> 
>> __
>> 
>> __blank database after been feed by the above sql script
>> 
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE a(b);
>> INSERT INTO "a" VALUES(4);
>> COMMIT;
>> 
>> __
>> 
>> Cheers !
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 

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



?

[sqlite] Third test of json and index expressions, now it works

[James K. Lowden]

On Sat, 5 Sep 2015 09:07:11 -0700
Darko Volaric  wrote:

> I'm asking why the SQL standard restricts the use of aliases in this
> way and what the benefit of this restriction is.

Rationales in SQL are hard to come by.  The language was promulgated by
a private firm, and the standard evolved under the aegis of what was,
for all intents and purposes, a private club.  Rationales for most
warts in the language boil down to "because we say so".  

Nonetheless, there is a good reason!  

There are no aliases in SQL, Horatio.  

In every SQL database, column names are unique and unambiguous.  If you
know the name of a table and a column, you've identified it.  A query
can name *new* columns, but it can't create aliases for existing
ones.  Consider, 

> SELECT a+b AS x FROM t1 WHERE x=99;

Here, "x" is a new column, the product of the SELECT.  By the rules of
SQL, it's *not* a macro for a+b, and it's not an alias.  It's the name
of the column formed by computing a+b, a new column of a new table.  

Now consider, 

SELECT x from (
SELECT a+b AS x FROM t1 
) as TEETH_GNASHER
WHERE x=99;

The outer query does not refer to t1, and thus not to "a" nor "b".  It
sees only the new table, with its sole column, "x".  And, although it's
a bit verbose, it also satisfies the sacred DRY criterion.  

Is that good?  The "no aliases" rule has one thing going for it: it's
consistent.  It's easy to understand and remember, and it reduces
opportunities for ambiguity.  SQL is a logic-based language, and
ambiguity in logic is anathema because it's too easy to form
syntactically valid constructions that produce incorrect (and
unintended) results.  

Nearly every SQL programmer uses some other programming language as the
"real" language for his application.  There's a temptation to make
informal, sometimes unwitting assumptions about the rules of SQL drawn
from that other language.  The best way to understand any language
though, including SQL, is on its own terms.  So double-quotes denote
identifiers, single-quotes strings, "||" contatenation, and there are
no aliases.  It's not easy to slough off unwarranted associations with
other languages, but once that's done, SQL is impossible to
misconstrue.  

--jkl

[sqlite] Using collation instead a virtual table

[Eduardo Morras]

Hello, 

I have a virtual table that implements query perceptual hashing data[1]. Now 
I'm thinking about converting the virtual table implementation in a collation 
on a normal sqlite3 table, but collation requieres that '=','<' and '>' be well 
defined by obeying the rules cited on create_collation() page[2]. Sometimes, 
rule 2 may not be true, but I always query for '=', never need sort query 
output and result is unique.
If I calculate the perceptual hash of an input, I want to get the closer 
(minimal distance) hash in the table calculating equal . Can I use a collation 
in this case?

[1] Perceptual Hashing: Hash function that similar input data has equal or 
similar hash. 
[2] http://www.sqlite.org/c3ref/create_collation.html
1.If A==B then B==A.
2.If A==B and B==C then A==C.
3.If AA.
4.If A

[sqlite] Fwd: OT: Oracle functions for SQlite

[Domingo Alvarez Duarte]

Hello !  

It's nice that you shared this, probably if you also host/mirror the on
github you'll get an even broader audience and probably feedback and
enhancements.  

I suggest to to fork this project https://github.com/mackyle/sqlite it's
almost daily updated with the https://www.sqlite.org and I hope contributions
from third party like your will be around it (because right now is the
best/updated mirror).  

I personally have a fork of it at https://github.com/mingodad/sqlite .   

Cheers !  

?  
>  Tue Sep 08 2015 7:46:22 pm CEST CEST from "Petite Abeille"
>  Subject: [sqlite] Fwd: OT: Oracle functions for
>SQlite
>
>  Perhaps of interest:
> 
> http://sqlite-libs.cis.ksu.edu
> 
> 
> 
>  
>>Begin forwarded message:
>> 
>> From: St?phane Faroult 
>> Subject: OT: Oracle functions for SQlite
>> Date: September 8, 2015 at 2:30:24 AM GMT+2
>> To: "Oracle-L (E-mail)" 
>> Reply-To: sfaroult at roughsea.com
>> 
>> I don't know if there are many people on the list using SQLite, but I use
>>it more and more often; teaching SQL is one reason (give a master file to
>>students, and let them create, drop tables, run DML at will without any
>>worry, and no need to bother about having a conveniently set server), another
>>one is consulting, whenever I'd *like* to store some data but I am either
>>unauthorized or unwilling to create my stuff on the database I'm working on.
>>Great also for implementing the poor man's performance pack - dump your v$
>>every so often to a SQLite file, and you have something far more flexible
>>than statspack. 
>> The only snag is that SQLite is a bit weak function-wise. I have last
>>spring given as assignment to the students in one of my classes the writing
>>for SQLite of functions available in other products. Making everything
>>homogeneous, writing a few functions I couldn't decently ask of
>>undergraduates (even if I usually set the bar rather high), substituting my
>>own date functions to the standard Unix ones so as to have the same behavior
>>as Oracle in October 1582 and so forth has been a huge endeavor (not
>>finished), it may still be a bit rough here and there but I have started
>>publishing this collective effort as an open source library. 
>> 
>> It's at http://sqlite-libs.cis.ksu.edu/  
>> 
>> There isn't EVERYTHING, but all the classic functions are there. 
>> 
>> Enjoy. 
>> 
>> St?phane Faroult 
>> 

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



?

[sqlite] Variables in statements

[James K. Lowden]

On Thu, 03 Sep 2015 16:44:50 +
Peter Haworth  wrote:

> The statement I'm using is:
> 
> SELECT * FROM myTable WHERE myKey IN (:1)
> 
> If the value I supply to be used as :1 is a single integer, the SELECT
> finds the correct rows.  If the value is a comma separated list of
> integers, e.g 1,2 the SELECT statement does not return any rows and no
> error is returned.  

If you think about the SQLite C interface that Livecode must be
using, you'll see why lists don't work in this context.  The data in
your list are conveyed to SQLite as a pointer to the list data.  The
caller indicates how that pointer is to interpreted through the bind
function used.  (http://www.sqlite.org/c3ref/bind_blob.html)  Livecode
must bind the parameter as one of those scalar types, probably
integer.  SQLite then interprets the data as (say) an integer whose
bytes are the ASCII encoding of the characters 

'1'  ','  '2'  ',' [...]

which is probably a very big number, and one that does not match any
myKey values.  Hence no row returns and no error produced.  

> I suspect this is a Livecode problem

If Livecode has a way to prevent passing a "list" type as an integer
parameter then, yes, I'd say so.  If not, they'd have to call it a
pibcak problem and say, "well, don't do that".  :-)  

--jkl

[sqlite] Fwd: OT: Oracle functions for SQlite

[Petite Abeille]

Perhaps of interest:

http://sqlite-libs.cis.ksu.edu



> Begin forwarded message:
> 
> From: St?phane Faroult 
> Subject: OT: Oracle functions for SQlite
> Date: September 8, 2015 at 2:30:24 AM GMT+2
> To: "Oracle-L (E-mail)" 
> Reply-To: sfaroult at roughsea.com
> 
> I don't know if there are many people on the list using SQLite, but I use it 
> more and more often; teaching SQL is one reason (give a master file to 
> students, and let them create, drop tables, run DML at will without any 
> worry, and no need to bother about having a conveniently set server), another 
> one is consulting, whenever I'd *like* to store some data but I am either 
> unauthorized or unwilling to create my stuff on the database I'm working on. 
> Great also for implementing the poor man's performance pack - dump your v$ 
> every so often to a SQLite file, and you have something far more flexible 
> than statspack. 
> The only snag is that SQLite is a bit weak function-wise. I have last spring 
> given as assignment to the students in one of my classes the writing for 
> SQLite of functions available in other products. Making everything 
> homogeneous, writing a few functions I couldn't decently ask of 
> undergraduates (even if I usually set the bar rather high), substituting my 
> own date functions to the standard Unix ones so as to have the same 
> behavior as Oracle in October 1582 and so forth has been a huge endeavor (not 
> finished), it may still be a bit rough here and there but I have started 
> publishing this collective effort as an open source library. 
> 
> It's at http://sqlite-libs.cis.ksu.edu/  
> 
> There isn't EVERYTHING, but all the classic functions are there. 
> 
> Enjoy. 
> 
> St?phane Faroult 

[sqlite] Command line sqlite3 program bug

[Domingo Alvarez Duarte]

Hello !  

After seem several emails from a user asking about how to use sqlite3 through
shell scripts, I remember my experiences with sqlite3 but didn't mind to
report it, but now I think that it's worth mention it because it'll hurt
several users.  

The bug/problem is that the sqlite3 command line when feed with a sql script
with commands wrapped by a transaction if there is any error in the middle of
it sqlite3 reports the error but do not stop/abort the transaction and the
database end up in a dirty state.  

__example to show the bug/problem  

BEGIN;  

DROP TABLE IF EXISTS a; --to allow run more than once  

CREATE TABLE a(b); --after the next line error this should be rolled back  

INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback  

INSERT INTO a(b) values(4);  

COMMIT;  

__  

__blank database after been feed by the above sql script  

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE a(b);
INSERT INTO "a" VALUES(4);
COMMIT;  

__  

Cheers !

[sqlite] Help with ext/misc/regex.c extension

[Domingo Alvarez Duarte]

Hello !  

Now I realize that in the function re_balanced I also need restore the
ReInput back in case of not matching:  

___  

static int re_balanced(
??? ReInput *p,
??? int cb, /* char that opens a balanced expression */
??? int ce? /* char that closes a balanced expression */
){
? int c = re_next_char(p); 
? if(c != cb) return 0; // string doesnt start with open char /need
restore? ReInput to it's original state before return
? int cont = 1;
? while ((c = re_next_char(p)) {
??? if (c == ce) {
? if (--cont == 0) {
? return ce;
? }
??? }
??? else if (c == cb) cont++;
? }  

///need restore? ReInput to it's original state before return
? return 0; // string ends out of balance
}  

___  

?

[sqlite] Help with ext/misc/regex.c extension

[Domingo Alvarez Duarte]

Hello !  

I'm trying to add a new option to ext/misc/regex.c it's "\p()" that's like
the LUA operator "%b"  

%b/xy/, where /x/ and /y/ are two distinct characters; such item matches
strings that start with?/x/, end with?/y/, and where the /x/ and /y/ are
/balanced/. This means that, if one reads the string from left to right,
counting /+1/ for an /x/ and /-1/ for a /y/, the ending /y/ is the first /y/
where the count reaches 0. For instance, the item %b() matches expressions
with balanced parentheses.  

But I'm having trouble to understand how to register/run this new re_opcode
bellow is what I got so far, the code to check for the balanced chars is
already used elsewhere it works:  

_regex.c diff  

@@ -45,10 +45,11 @@
?** \W? Non-word character
?** \d? Digit
?** \D? Non-digit
?** \s? Whitespace character
?** \S? Non-whitespace character
+** \pxy? Match balanced where xy are the open/close
characters characters, like LUA %b 
?**
?** A nondeterministic finite automaton (NFA) is used for matching, so the
?** performance is bounded by O(N*M) where N is the size of the regular
?** expression and M is the size of the input string.? The matcher never
?** exhibits exponential behavior.? Note that the X{p,q} operator expands
@@ -91,10 +92,11 @@ SQLITE_EXTENSION_INIT1
?#define RE_OP_DIGIT? 13??? /* digit:? [0-9] */
?#define RE_OP_NOTDIGIT?? 14??? /* Not a digit */
?#define RE_OP_SPACE? 15??? /* space:? [ \t\n\r\v\f] */
?#define RE_OP_NOTSPACE?? 16??? /* Not a digit */
?#define RE_OP_BOUNDARY?? 17??? /* Boundary between word and non-word
*/
+#define RE_OP_BALANCED?? 18??? /* Balanced between open/close character
*/
?
?/* Each opcode is a "state" in the NFA */
?typedef unsigned short ReStateNumber;
?
?/* Because this is an NFA and not a DFA, multiple states can be active at
@@ -189,10 +191,29 @@ static int re_digit_char(int c){
?/* Return true if c is a perl "space" character:? [ \t\r\n\v\f] */
?static int re_space_char(int c){
?? return c==' ' || c=='\t' || c=='\n' || c=='\r' || c=='\v' || c=='\f';
?}
?
+static int re_balanced(
+??? ReInput *p,
+??? int cb, /* char that opens a balanced expression */
+??? int ce? /* char that closes a balanced expression */
+){
+? int c = re_next_char(p);
+? if(c != cb) return 0; // string doesnt start with open char
+? int cont = 1;
+? while ((c = re_next_char(p)) {
+??? if (c == ce) {
+? if (--cont == 0) {
+? return ce;
+? }
+??? }
+??? else if (c == cb) cont++;
+? }
+? return 0; // string ends out of balance
+}
+
?/* Run a compiled regular expression on the zero-terminated input
?** string zIn[].? Return true on a match and false if there is no match.
?*/
?static int re_match(ReCompiled *pRe, const unsigned char *zIn, int nIn){
?? ReStateSet aStateSet[2], *pThis, *pNext;
@@ -277,10 +298,14 @@ static int re_match(ReCompiled *pRe, const unsigned
char *zIn, int nIn){
 }
 case RE_OP_BOUNDARY: {
?? if( re_word_char(c)!=re_word_char(cPrev) )
re_add_state(pThis, x+1);
?? break;
 }
+??? case RE_OP_BALANCED: { //here the re_balanced should be
called with ReInput *p with it's two parameters \bxy
+? if( re_balanced(c)!=re_word_char(cPrev) )
re_add_state(pThis, x+1);
+? break;
+??? }
 case RE_OP_ANYSTAR: {
?? re_add_state(pNext, x);
?? re_add_state(pThis, x+1);
?? break;
 }
@@ -580,18 +605,29 @@ static const char *re_subcompile_string(ReCompiled *p){
 int specialOp = 0;
 switch( rePeek(p) ){
?? case 'b': specialOp = RE_OP_BOUNDARY;?? break;
?? case 'd': specialOp = RE_OP_DIGIT;? break;
?? case 'D': specialOp = RE_OP_NOTDIGIT;?? break;
+? case 'p': specialOp = RE_OP_BALANCED;?? break;
?? case 's': specialOp = RE_OP_SPACE;? break;
?? case 'S': specialOp = RE_OP_NOTSPACE;?? break;
?? case 'w': specialOp = RE_OP_WORD;?? break;
?? case 'W': specialOp = RE_OP_NOTWORD;??? break;
 }
 if( specialOp ){
?? p->sIn.i++;
-? re_append(p, specialOp, 0);
+? if(specialOp == RE_OP_BALANCED){? ///here I need to
register the op_code and it's two parameters \bxy
+??? c = p->xNextChar(&p->sIn);
+??? if( c==0 ) return "balanced open character expected";
+??? re_append(p, specialOp, c);
+??? p->sIn.i++;
+??? c = p->xNextChar(&p->sIn);
+??? if( c==0 ) return "balanced close character
expected";
+??? re_append(p, specialOp, c);
+? }else{
+??? re_append(p, specialOp, 0);
+? }
 }else{
?? c = re_esc_char(p);
?? re_append(p, RE_OP_MATCH, c);
 }
 break;  

_  

?  

Any help are welcome !  

The code if other people have interested is released with the same license as
sqlite.  

Cheers !

[sqlite] Using collation instead a virtual table

[Richard Hipp]

On 9/8/15, Eduardo Morras  wrote:
>
>
> Hello,
>
> I have a virtual table that implements query perceptual hashing data[1]. Now
> I'm thinking about converting the virtual table implementation in a
> collation on a normal sqlite3 table, but collation requieres that '=','<'
> and '>' be well defined by obeying the rules cited on create_collation()
> page[2]. Sometimes, rule 2 may not be true, but I always query for '=',

Yes, but under the hood, SQLlite never does an == query on the b-trees
even if you ask for a == query in the SQL.  Instead, the b-trees are
queried using one of >, >=, <, or <=.  A query of the form:

 x=$value

Gets translated (at the b-tree layer) into

 x>=$value AND x<=$value

So it is *very* important that the comparison operators all work
correctly on your collating sequence function.  If they don't, then
SQLite will give incorrect answers.



> never need sort query output and result is unique.
> If I calculate the perceptual hash of an input, I want to get the closer
> (minimal distance) hash in the table calculating equal . Can I use a
> collation in this case?
>
> [1] Perceptual Hashing: Hash function that similar input data has equal or
> similar hash.
> [2] http://www.sqlite.org/c3ref/create_collation.html
> 1.If A==B then B==A.
> 2.If A==B and B==C then A==C.
> 3.If AA.
> 4.If A
> ---   ---
> Eduardo Morras 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] To the sqlite wish list, usefull internal/extensions as library

[Domingo Alvarez Duarte]

Hello !  

Sqlite like any other project has developed a lot of cross platform/utilities
code but most of it remain locked down to sqlite3 alone.  

Example the latest json1 functions, vfs, sqlite3_printf, thread/lock
management functions, ..., would be nice if they could also be used by the
sqlite3 users and even to other projects, more users more test, more
feedback.  

Cheers !

[sqlite] To the sqlite wish list, usefull internal/extensions as library

[Simon Slavin]

On 8 Sep 2015, at 1:43pm, Domingo Alvarez Duarte  
wrote:

> Sqlite like any other project has developed a lot of cross platform/utilities
> code but most of it remain locked down to sqlite3 alone.  
> 
> Example the latest json1 functions, vfs, sqlite3_printf, thread/lock
> management functions, ..., would be nice if they could also be used by the
> sqlite3 users and even to other projects, more users more test, more
> feedback. 

Everything in SQLite is public domain.  If you like a piece of code, for 
example thread/lock management, you are free to take it and use it in any 
project you like, whether public domain or private.

License here:



Source code here:



Simon.

[sqlite] Command line sqlite3 program bug

[Gerry Snyder]

The Command Line Interface has the command:

.bail on

which will do what you want.

HTH,

Gerry Snyder
---
On 9/8/2015 9:54 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> After seem several emails from a user asking about how to use sqlite3 through
> shell scripts, I remember my experiences with sqlite3 but didn't mind to
> report it, but now I think that it's worth mention it because it'll hurt
> several users.
>
> The bug/problem is that the sqlite3 command line when feed with a sql script
> with commands wrapped by a transaction if there is any error in the middle of
> it sqlite3 reports the error but do not stop/abort the transaction and the
> database end up in a dirty state.
>
> __example to show the bug/problem
>
> BEGIN;
>
> DROP TABLE IF EXISTS a; --to allow run more than once
>
> CREATE TABLE a(b); --after the next line error this should be rolled back
>
> INSERT INTO a(c) VALUES(3); -- intentional error and no stop/rollback
>
> INSERT INTO a(b) values(4);
>
> COMMIT;
>
> __
>
> __blank database after been feed by the above sql script
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE a(b);
> INSERT INTO "a" VALUES(4);
> COMMIT;
>
> __
>
> Cheers !
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] To the sqlite wish list, sqldiff, data sync

[Domingo Alvarez Duarte]

Hello !  

I forgot to include this topics on my wish list:  

Sqldiff is an amazing tool added to sqlite, but it can be even more useful if
it's integrated on sqlite itself, right know sqldiff needs two databases to
operate, but if included in sqlite3 somehow it could generate the diffs on
the go and then those diffs could be used to sync a remote/other database  

Using the knowledge already developed with fossil, sqldiff inside sqlite
could also optionally generate a hash of the original record (I mean pre
update) and that hash could be used to help with conflicts and or allow a
creation of a historic database a la VCS/fossil-scm/git.  

Cheers !

[sqlite] To the sqlite wish list

[Domingo Alvarez Duarte]

Hello !  

Sqlite is really a very nice piece of software and it could even be better,
here is things to add to the wish list:  

- Implement sql "CREATE FUNCTION" even if it is as simple of only allowing a
limited set of operations, refactoring the actual trigger implementation to
allow it be called with parameters, with this we could also have generic
trigger functions that could be applied to more than one table/view in a
"DRY" way, see postgresql.  

- Implement "PREPARE/EXECUTE/DEALLOC" like the previous wish several times a
view with parameters can make life a lot "DRY" easier.  

 example from a postgresql function  

CREATE FUNCTION lastcost(int) RETURNS FLOAT AS '

DECLARE

v_cost float;
v_parts_id alias for $1;

BEGIN

? SELECT INTO v_cost sellprice FROM invoice i
? JOIN ap a ON (a.id = i.trans_id)
? WHERE i.parts_id = v_parts_id
? ORDER BY a.transdate desc, a.id desc
? LIMIT 1;

? IF v_cost IS NULL THEN
??? v_cost := 0;
? END IF;

RETURN v_cost;
END;
' language plpgsql;  

  

The above function could be done with an even simple implementation of
"PREPARE" like this:  

___ as PREPARE (view with parameters, without parameters I don't know how
to parametrize this because of the inner variable)  

PREPARE lastcost(int) AS  

SELECT IFNULL(sellprice, 0) AS v_cost FROM invoice i
? JOIN ap a ON (a.id = i.trans_id)
? WHERE i.parts_id = $1
? ORDER BY a.transdate desc, a.id desc
? LIMIT 1;  

___  

?  

- Sqlite "C" interface function to return rows, something like:  void
sqlite3_result_sql(sqlite3_context*, const char* sql, int sql_size,
void(*)(void*));

To be used like this (pseudo code):
void my_sqlite_eval_function(sqlite3_context *context, int argc,
sqlite3_value **argv)
{
const char *ztable_name = (const char*)sqlite3_value_text(argv[0]);
char *sql = sqlite3_mprintf("select name, value from %s",ztable_name);
?   sqlite3_result_sql(context, sql, -1, sqlite3_free);
}  

Behind scenes it basically could be implemented as sqlite3_prepare that
extends/add new opcode and execute it after the function call.  

- Also like before about generic trigger functions a "C" interface to
register "C" functions as triggers.  

- Meta programming ansi catalog, it's strange that sqlite stores it's catalog
information in a normal hidden table, but do not do the same with other
parsed elements like "PRAGMA table_info", "PRAGMA foreign_keys", "PRAGMA
collation_list", "PRAGMA index_info", would be nice if at least sqlite
provides virtual views for this information that could be used through sql
queries (I mean joining with other tables to achieve several results).
Storing the parsed elements in a normalized catalog would permit things like
"ALTER TABLE RENAME COLUMN", "ALTER TABLE ADD CONSTRAINT", ..., and also when
renaming a table the views that reference it could be updated properly (or
not need update at all if it's recovered from join querying the catalog).  

?  

Cheers !

[sqlite] Function lower on index expressions not allowed ?

[Domingo Alvarez Duarte]

Hello !  

The create_collation functions have a signature similar to create_function
but no mention abount add SQLITE_DETERMINISTIC to then, they do not need it ?


Cheers !  
>  Tue Sep 08 2015 12:04:44 am CEST CEST from "Richard Hipp"
>  Subject: Re: [sqlite] Function lower on index expressions
>not allowed ?
>
>  On 9/7/15, Domingo Alvarez Duarte  wrote:
>  
>>Hello !
>> 
>> I'm converting a database from postgresql and when I tried to create this
>> index it fails:
>> 
>> CREATE UNIQUE INDEX country_name_idx on country(lower(name));
>> 
>> Error: "non-deterministic functions prohibited in index expressions"
>> 
>> Can the lower function be non deterministic ?
>> 
>> 

>  My guess: You are using ICU or some other extension that is replacing
> the built-in lower() function with another. And that extension is not
> marking its new lower() function as SQLITE_DETERMINISTIC.
> (https://www.sqlite.org/c3ref/c_deterministic.html)
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?