[sqlite] Any way to disable transactional behavior?

[Hayden Livingston]

I have a program that writes hundreds of thousands of statements that
are logically unrelated, i.e. each is "transactional".

The problem is if I don't do a BEGIN TRANSACTION and do my inserts, it
takes absolutely forever for my program to finish (we're talking
hours).

If instead I do it in a single transaction (i.e. BEGIN TRANSACTION, 1
million inserts, END) it dramatically improves the time.

Is there a way to get this behavior without transactions? The reason
is most of the times the program is terminated by a user action and I
don't get a chance to END the transaction.

I'm considering doing "periodic transactions", i.e. buffer in my
application X statements and club them together.

Is there a better more SQLite idiomatic solution?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table Count Grow

[Keith Medcalf]

How many system objects do you have that this is a problem?

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of sanhua.zh
> Sent: Sunday, 9 October, 2016 21:53
> To: sqlite-users
> Subject: [sqlite] CREATE TABLE/ALTER TABLE Slow Down When Table Count Grow
> 
> I foundCREATE TABLE/ALTER TABLE Slow Down When Table Count Grow.
> Since those SQLs modify the schema, SQLite use theOP_ParseSchema to update
> them, which cause the search of sqlite_master. (SELECT name, rootpage, sql
> FROM '%q'.%s WHERE %s ORDER BY rowid)
> As we all know, sqlite_master has no index except rowid. So this search
> may slow down when the table count grow, since it cause a full-table-
> search.
> 
> 
> Is there anyway to solve this problem?
> ___
> 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] CREATE TABLE/ALTER TABLE Slow Down When Table Count Grow

[sanhua.zh]

I foundCREATE TABLE/ALTER TABLE Slow Down When Table Count Grow.
Since those SQLs modify the schema, SQLite use theOP_ParseSchema to update 
them, which cause the search of sqlite_master. (SELECT name, rootpage, sql FROM 
'%q'.%s WHERE %s ORDER BY rowid)
As we all know, sqlite_master has no index except rowid. So this search may 
slow down when the table count grow, since it cause a full-table-search.


Is there anyway to solve this problem?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parallel access to read only in memory database

[Howard Chu]

Jens Alfke wrote:



On Oct 9, 2016, at 8:15 AM, Howard Chu  wrote:

Use SQLightning, it's designed specifically for write once read many workloads.


"SQLite3 ported to use LMDB instead of its original Btree code” — sounds great, 
and the performance figures quoted in the readme are impressive. But the source code 
appears to be a modified version of SQLite’s source, which would make it very 
difficult to keep in sync with SQLite, and (as Domingo pointed out) the last commit 
is three years old and seems to be based on SQLite 3.7.17. So far this looks like an 
exciting proof-of-concept, but not something I’d use in a real project.

(By comparison, SQLCipher is also released as a modified copy of SQLite, but 
they sync with SQLite regularly; the latest version from this April is based on 
3.11.0.)

It would be best if this were implemented as a separate plugin, but as I’m not 
familiar with the innards of SQLite, I’ll assume that simply wasn't feasible. 
(I know SQLite supports VFS plugins, but I think those just operate at the 
paging layer, below the b-tree.)


SQLite3's code structure is monolithic, not modular, so there is no clean way 
to replace its underlying Btree layer. It's necessarily a hack-and-slash 
proposition.


As for code freshness, I've seen no compelling new features from 3.8.x onward 
that would improve performance so there's been no reason to update further. 
Many of the SQLite performance enhancements from 3.8 are in its own Btree 
code, which is entirely ripped out in SQLightning and thus irrelevant. On the 
other hand, there have been multiple regressions (performance and otherwise) 
in subsequent releases, most often in the query planner, which I've chosen to 
avoid.


E.g.
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg85558.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg86191.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg86901.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg89666.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg91201.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg92189.html
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg99646.html

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

[Jens Alfke]

> On Oct 8, 2016, at 1:39 PM, Richard Hipp <d...@sqlite.org> wrote:
> 
> See http://sqlite.org/graphs/size-20161009.jpg 
> <http://sqlite.org/graphs/size-20161009.jpg> for a graph of
> compiled-binary size using gcc 4.8.4 and -Os on x64 Linux.  Less than
> 0.5 MB, though to be fair this is without extensions such as FTS5 or
> RTREE or JSON.

Ah, thanks for the info. I was simply extrapolating from the size of 
/usr/lib/libsqlite3.dylib (4.1MB on macOS 10.12) and dividing by 3 (the number 
of architectures). But that binary does come with all the bells and whistles, 
and was doubtless compiled with -Ofast.

Some of our users will need FTS5 and/or RTREE, but I assume those can be built 
as separate dylibs that can be dropped in as needed?

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

Re: [sqlite] Parallel access to read only in memory database

[Jens Alfke]

> On Oct 9, 2016, at 8:15 AM, Howard Chu  wrote:
> 
> Use SQLightning, it's designed specifically for write once read many 
> workloads.

"SQLite3 ported to use LMDB instead of its original Btree code” — sounds great, 
and the performance figures quoted in the readme are impressive. But the source 
code appears to be a modified version of SQLite’s source, which would make it 
very difficult to keep in sync with SQLite, and (as Domingo pointed out) the 
last commit is three years old and seems to be based on SQLite 3.7.17. So far 
this looks like an exciting proof-of-concept, but not something I’d use in a 
real project.

(By comparison, SQLCipher is also released as a modified copy of SQLite, but 
they sync with SQLite regularly; the latest version from this April is based on 
3.11.0.)

It would be best if this were implemented as a separate plugin, but as I’m not 
familiar with the innards of SQLite, I’ll assume that simply wasn't feasible. 
(I know SQLite supports VFS plugins, but I think those just operate at the 
paging layer, below the b-tree.)

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

Re: [sqlite] Parallel access to read only in memory database

[Domingo Alvarez Duarte]

Hello !

But SQLightning is a dead project, isn't it ?

version 3.7.17 
 



hyc  
committed on 12 Sep 2013


Cheers !

On 09/10/16 12:15, Howard Chu wrote:

Daniel Meyer wrote:

We are interested in using sqlite as a read only, in memory, parallel
access database.  We have database files that are on the order of 100GB
that we are loading into memory.  We have found great performance when
reading from a single thread.  We need to scale up to have many parallel
reader threads.  Once the DB is created it never needs to be 
modified.  How

can we allow many reader threads on an in memory, write once read many
times database and achieve multi-core performance?  Is this possible 
with

sqlite?


Use SQLightning, it's designed specifically for write once read many 
workloads.




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

Re: [sqlite] Parallel access to read only in memory database

[Howard Chu]

Daniel Meyer wrote:

We are interested in using sqlite as a read only, in memory, parallel
access database.  We have database files that are on the order of 100GB
that we are loading into memory.  We have found great performance when
reading from a single thread.  We need to scale up to have many parallel
reader threads.  Once the DB is created it never needs to be modified.  How
can we allow many reader threads on an in memory, write once read many
times database and achieve multi-core performance?  Is this possible with
sqlite?


Use SQLightning, it's designed specifically for write once read many workloads.

--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL parsing logic for triggers

[gwenn]

On Sun, Oct 9, 2016 at 12:14 PM, Richard Hipp  wrote:
> On 10/9/16, gwenn  wrote:
>> Hello,
>> I am just looking for information (I am not asking for any change):
>> It is for (auto) completion hints.
>>
>> 1) it seems not possible to insert default values in trigger command:
>>
>> // INSERT
>> trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).
>>
>> versus
>>
>> // The INSERT command
>> /
>> //
>> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
>> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
>>
>> Is it a choice or an oversight ?
>
> Deliberate.  Adding DEFAULT VALUES to triggers is just more code to be
> tested and maintained for something that adds no new capability and
> that nobody ever uses.
>
>>
>> Is it to make possible to create temporary trigger by using the
>> following syntax:
>> CREATE TABLE test (data);
>> CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
>> instead of:
>> CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;
>>
>
> Did you try it?  What happened?

Yes I tried and it works as expected (the two statements seem equivalent).
But the reverse does not:
CREATE TEMP TABLE test (data);
CREATE TRIGGER main.trig UPDATE ON temp.test BEGIN ...; END;
Is (temp.[trigger name], [^temp].[table name])  the only working case
when the provided database names are different ?

And I have another question related to hexadecimals.
It seems that they are not properly tokenized in select statements (at
least in the result columns part).
Invalid hexadecimals are not rejected but splitted into the valid part
and an elided 'as' part.
For example:
SELECT 0x1g;
is interpreted as
SELECT 0x1 g;

Invalid hexadecimals are properly rejected where there is no elided
'as' possible.
For example,
INSERT INTO test VALUES (0x1g);

Is it intentional ?

Many 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] 回复： The Performance Between [Drop Table] And [Rename Table] ?

[sanhua.zh]

Now I found the possible reason why [Rename Table] become slow.
While the one table renamed, the schema need to be updated. But SQLite use the 
['SELECT name, rootpage, sql FROM 'main'.sqlite_master WHERE 
tbl_name=’tablename' ORDER BY rowid”] to update the schema.


Isn’t it too ugly ? Why we should select the whole [sqlite_master] to update 
ONLY ONE altered table ?




原始邮件
发件人:sanhua.zhsanhua...@foxmail.com
收件人:sqlite-userssqlite-us...@mailinglists.sqlite.org
发送时间:2016年10月9日(周日) 17:59
主题:[sqlite] The Performance Between [Drop Table] And [Rename Table] ?


I found that [Rename Table](using ‘ALTER TABLE RENAME TO') is much slower than 
[Drop Table]. The cost of [Rename Table] may be twice, even if the table is 
empty(which means it has no index, no trigger, no view and no column). As I 
known, both [Drop Table] and [Rename Table] just modify the `sqlite_master` 
when the table is empty. But in my testcase, [Rename Table] is much more 
slower. Does anyone know the reason ? Here is my test code. Result: drop table 
total cost 4705633 alter total cost 13172092 Code: #import sqlite3.h #import 
sys/time.h #define EXIT_IF_FAILED(rc) if (rc!=SQLITE_OK) {printf("%d failed at 
%d\n", rc, __LINE__); exit(0);} #define TABLE_COUNT 1 static uint64_t now() 
{ #define MICROSECOND_PER_SECOND 100 struct timeval cur; gettimeofday(cur, 
NULL); uint64_t time = cur.tv_sec*MICROSECOND_PER_SECOND+cur.tv_usec; return 
time; } void preCreateTable(sqlite3* db) { int rc = SQLITE_OK; rc = 
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 
0; i TABLE_COUNT; i++) { NSString* sql = [NSString stringWithFormat:@"CREATE 
TABLE test%d (name TEXT)", i]; rc = sqlite3_exec(db, sql.UTF8String, NULL, 
NULL, NULL); EXIT_IF_FAILED(rc); } rc = sqlite3_exec(db, "COMMIT", NULL, NULL, 
NULL); EXIT_IF_FAILED(rc); } void config(sqlite3* db) { sqlite3_exec(db, 
"PRAGMA journal_mode=WAL;", NULL, NULL, NULL); sqlite3_exec(db, "PRAGMA 
synchronous=FULL;", NULL, NULL, NULL); } int main(int argc, const char * 
argv[]) { const char* testDropTablePath = 
"/Users/sanhuazhang/Desktop/testDropTablePath"; const char* testAlterTablePath 
= "/Users/sanhuazhang/Desktop/testAlterTablePath"; //test 'drop table' { 
sqlite3* db; int rc = sqlite3_open(testDropTablePath, db); EXIT_IF_FAILED(rc); 
config(db); preCreateTable(db); uint64_t before = now(); rc = sqlite3_exec(db, 
"BEGIN", NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 0; i TABLE_COUNT; 
i++) { NSString* sql = [NSString stringWithFormat:@"DROP TABLE test%d", i]; rc 
= sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL); EXIT_IF_FAILED(rc); } rc 
= sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); EXIT_IF_FAILED(rc); uint64_t 
after = now(); printf("drop table total cost %llu\n", after-before); 
sqlite3_close(db); } //test 'alter table' { sqlite3* db; int rc = 
sqlite3_open(testAlterTablePath, db); EXIT_IF_FAILED(rc); config(db); 
preCreateTable(db); uint64_t before = now(); rc = sqlite3_exec(db, "BEGIN", 
NULL, NULL, NULL); EXIT_IF_FAILED(rc); for (int i = 0; i TABLE_COUNT; i++) { 
NSString* sql = [NSString stringWithFormat:@"ALTER TABLE test%d RENAME TO 
re%d", i, i]; rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL); 
EXIT_IF_FAILED(rc); } rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); 
EXIT_IF_FAILED(rc); uint64_t after = now(); printf("alter total cost %llu\n", 
after-before); sqlite3_close(db); } return 0; } 
___ 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] SQL parsing logic for triggers

[Richard Hipp]

On 10/9/16, gwenn  wrote:
> Hello,
> I am just looking for information (I am not asking for any change):
> It is for (auto) completion hints.
>
> 1) it seems not possible to insert default values in trigger command:
>
> // INSERT
> trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).
>
> versus
>
> // The INSERT command
> /
> //
> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
>
> Is it a choice or an oversight ?

Deliberate.  Adding DEFAULT VALUES to triggers is just more code to be
tested and maintained for something that adds no new capability and
that nobody ever uses.

>
> Is it to make possible to create temporary trigger by using the
> following syntax:
> CREATE TABLE test (data);
> CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
> instead of:
> CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;
>

Did you try it?  What happened?
-- 
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] The Performance Between [Drop Table] And [Rename Table] ?

[sanhua.zh]

I found that [Rename Table](using ‘ALTER TABLE RENAME TO') is much slower than 
[Drop Table]. The cost of [Rename Table] may be twice, even if the table is 
empty(which means it has no index, no trigger, no view and no column).


As I known, both [Drop Table] and [Rename Table] just modify the 
`sqlite_master` when the table is empty. But in my testcase, [Rename Table] is 
much more slower. Does anyone know the reason ?
Here is my test code.


Result:
drop table total cost 4705633
alter total cost 13172092


Code:
#import sqlite3.h
#import sys/time.h


#define EXIT_IF_FAILED(rc) if (rc!=SQLITE_OK) {printf("%d failed at %d\n", rc, 
__LINE__); exit(0);}
#define TABLE_COUNT 1


static uint64_t now()
{
#define MICROSECOND_PER_SECOND 100
  struct timeval cur;
  gettimeofday(cur, NULL);
  uint64_t time = cur.tv_sec*MICROSECOND_PER_SECOND+cur.tv_usec;
  return time;
}


void preCreateTable(sqlite3* db)
{
  int rc = SQLITE_OK;
  rc = sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
  for (int i = 0; i  TABLE_COUNT; i++) {
NSString* sql = [NSString stringWithFormat:@"CREATE TABLE test%d (name 
TEXT)", i];
rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
  }
  rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
}


void config(sqlite3* db)
{
  sqlite3_exec(db, "PRAGMA journal_mode=WAL;", NULL, NULL, NULL);
  sqlite3_exec(db, "PRAGMA synchronous=FULL;", NULL, NULL, NULL);
}


int main(int argc, const char * argv[]) {
 
  const char* testDropTablePath = 
"/Users/sanhuazhang/Desktop/testDropTablePath";
  const char* testAlterTablePath = 
"/Users/sanhuazhang/Desktop/testAlterTablePath";
 
  //test 'drop table'
  {
sqlite3* db;
int rc = sqlite3_open(testDropTablePath, db);
EXIT_IF_FAILED(rc);
config(db);
preCreateTable(db);
uint64_t before = now();
rc = sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
for (int i = 0; i  TABLE_COUNT; i++) {
  NSString* sql = [NSString stringWithFormat:@"DROP TABLE test%d", i];
  rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
}
rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
uint64_t after = now(); 
printf("drop table total cost %llu\n", after-before);
sqlite3_close(db);
  }
 
  //test 'alter table'
  {
sqlite3* db;
int rc = sqlite3_open(testAlterTablePath, db);
EXIT_IF_FAILED(rc);
config(db);
preCreateTable(db);
uint64_t before = now();
rc = sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
for (int i = 0; i  TABLE_COUNT; i++) {
  NSString* sql = [NSString stringWithFormat:@"ALTER TABLE test%d RENAME TO 
re%d", i, i];
  rc = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
  EXIT_IF_FAILED(rc);
}
rc = sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
EXIT_IF_FAILED(rc);
uint64_t after = now(); 
printf("alter total cost %llu\n", after-before);
sqlite3_close(db);
  }


 
  return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL parsing logic for triggers

[gwenn]

Sorry,
I've just found the answer to the first question here:
http://www.sqlite.org/lang_insert.html
Regards.

On Sun, Oct 9, 2016 at 10:34 AM, gwenn  wrote:
> Hello,
> I am just looking for information (I am not asking for any change):
> It is for (auto) completion hints.
>
> 1) it seems not possible to insert default values in trigger command:
>
> // INSERT
> trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).
>
> versus
>
> // The INSERT command 
> /
> //
> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
> cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.
>
> Is it a choice or an oversight ?
>
> 2) It seems possible to use different database names when creating a trigger:
>
> trigger_decl(A) ::= temp(T) TRIGGER ifnotexists(NOERR) nm(B) dbnm(Z)
> trigger_time(C) trigger_event(D)
> ON fullname(E) foreach_clause when_clause(G). {
>
> Is it to make possible to create temporary trigger by using the
> following syntax:
> CREATE TABLE test (data);
> CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
> instead of:
> CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;
>
> Or there are other use cases supported ?
>
> Thanks.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

[Simon Slavin]

On 9 Oct 2016, at 9:32am, R Smith  wrote:

> most everybody else in the World (including North America) seems to more 
> easily understand "Indexes" as the plural

That's why I used it here.  Formal English requires that I write 'indices' and 
that's what I used when I started posting here.  But this list has many readers 
for whom English is not the first language, and I think they understand 
'indexes' more easily.

I sometimes also capitalise SQL terms when I use them inside text to give the 
reader a clue that I'm using SQL commands they can use.  I can write "DROP 
INDEXes", which looks okay.  But writing "DROP INDICes" is strange and annoying.

Technically wrong.  Practically right.

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

[sqlite] SQL parsing logic for triggers

[gwenn]

Hello,
I am just looking for information (I am not asking for any change):
It is for (auto) completion hints.

1) it seems not possible to insert default values in trigger command:

// INSERT
trigger_cmd(A) ::= insert_cmd(R) INTO trnm(X) idlist_opt(F) select(S).

versus

// The INSERT command /
//
cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) select(S).
cmd ::= with(W) insert_cmd(R) INTO fullname(X) idlist_opt(F) DEFAULT VALUES.

Is it a choice or an oversight ?

2) It seems possible to use different database names when creating a trigger:

trigger_decl(A) ::= temp(T) TRIGGER ifnotexists(NOERR) nm(B) dbnm(Z)
trigger_time(C) trigger_event(D)
ON fullname(E) foreach_clause when_clause(G). {

Is it to make possible to create temporary trigger by using the
following syntax:
CREATE TABLE test (data);
CREATE TRIGGER temp.trig UPDATE ON main.test BEGIN ...; END;
instead of:
CREATE TEMP TRIGGER trig UPDATE ON main.test BEGIN ...; END;

Or there are other use cases supported ?

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

Re: [sqlite] Backward compatibility of indexes with "WHERE function()"

[R Smith]

On 2016/10/08 9:41 PM, Jens Alfke wrote:

—Jens

* is it ‘indexes’ or ‘indices’, in computer science?


It is both, and I pedantically prefer "Indices", but there's been some 
debate and you can find on-line literature in support of all views.


The correct original "English" is of course "Indices" in the same way 
that other Latin-origin words ending in "x" such as "Vertex" and 
"Matrix" plurals to "Vertices" and "Matrices". So it is correct to say 
"Indices", also noting that in normal English the word "Indexes" is a 
present tense verb - as in: "Mary indexes her cook books" (alternate to 
the pres-cont. "is indexing"), or: "the DB engine Indexes the table". 
Funnily, the one usually preferred in technical literature is "Indices",


but...

In computer terms it has become acceptable and even sometimes preferable 
to use the word "Indexes" to describe the plural of a database Index. 
The reason seems two-fold in that computer programming languages (while 
based in English words mostly) are really World-wide used, not just by 
English speaking patrons for whom "indices" would make sense - most 
everybody else in the World (including North America) seems to more 
easily understand "Indexes" as the plural - and it further serves to 
distinguish between the plethora of indices out there, and specifically 
Database Indexes, which are much more than just a list of page numbers.


This has permeated the language world to the point you almost always 
find both "Indices" and "Indexes" in dictionaries now given as plural 
for Index.


Language rules are never quite strict rules... it's just a description 
and rule-based understanding of how people speak, and if that changes 
over time, so must the rules that describe it.
I cringe to think it, but probably some day these sentence parts would 
be found in an actual dictionary:

"And I was like, r u serius? And she wuz all like - whaeva!, l8er m8."

Fun. :)
Ryan

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