Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

2017-10-17 Thread dave 
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Hick Gunter
> 
> In our virtual table implementations, we are using the rowid 
> to return the location of the record in the backing store 
> (e.g. record offset in the file used as a backing store, 
> offset within a shared memory section or maybe even the 
> memory address of the record image) and also implement fast 
> lookup by rowid.
> 
> If you don't require such ability, you may as well return a 
> constant, a global counter value or a counter that is reset 
> in the xFilter function.
> 
> So, YES you always have to implement the xRowid method.
> 
> It will only get called if your SELECT statement explicitly 
> mentions it. No "INTEGER PRIMARY KEY" magic is performed for 
> virtual tables.
> 
Thanks for your input as well; I somehow missed it until just now.
Cheers!
-dave


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

Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

2017-10-17 Thread dave 
> Behalf Of Dan Kennedy
> Sent: Tuesday, October 17, 2017 11:58 AM
> 
> On 10/17/2017 01:22 PM, Hick Gunter wrote:
> > In our virtual table implementations, we are using the 
> rowid to return the location of the record in the backing 
> store (e.g. record offset in the file used as a backing 
> store, offset within a shared memory section or maybe even 
> the memory address of the record image) and also implement 
> fast lookup by rowid.
> >
> > If you don't require such ability, you may as well return a 
> constant, a global counter value or a counter that is reset 
> in the xFilter function.
> >
> > So, YES you always have to implement the xRowid method.
> >
> > It will only get called if your SELECT statement explicitly 
> mentions it. No "INTEGER PRIMARY KEY" magic is performed for 
> virtual tables.
> 
> I think the exception is queries with OR terms. With 
> FTS[345], if you do 
> something like:
> 
>CREATE VIRTUAL TABLE t1 USING fts5(x);
>EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def';
> 
> You can see the Rowid opcodes.
> 
> SQLite runs two separate queries on the virtual table - one 
> with "MATCH 
> 'abc'" and the other with "MATCH 'def'". It uses the rowids for each 
> matched row to avoid returning duplicates. If the xRowid 
> method always 
> returned 0, then only the first set of matches would be returned 
> (because SQLite would deem the second set to be duplicates of the 
> first). Or if xRowid returned arbitrary values your results might 
> include duplicates. etc.
> 
> Same applies to other virtual table types.
> 
> Dan.

Yikes, thanks for the insight on that OR use-case; I'll have to do some
analysis to see what is my exposure.

The counter trick is an interesting suggestion, but I guess I am still
at-risk because I have to make it deterministic/repeatable at least in the
context of a statement, which can still a challenge.  E.g. in your OR clause
example, if the query engine does indeed do two table scans (as opposed to
one table scan, and computing all the predicates) then I have some risk that
the two scans return different results (since my data is coming from APIs,
and dynamic, rather that persisted collections).

In other projects I've definitely used the counter trick before, caching the
underlying data (to support updates and transactions) but those were known
to be small datasets.  This stuff coming from APIs could be big, so I wanted
to avoid caching it all.  But one does what one must

Thanks for all the feedback!

-dave


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

[sqlite] System.Data.SQLite version 1.0.106.0 pre-release packages now available

2017-10-17 Thread Joe Mistachkin 

Pre-release packages of System.Data.SQLite version 1.0.106.0 are now
available on the System.Data.SQLite website:

 https://system.data.sqlite.org/

Please click on the "Pre-Release Download" link in order to view the
available pre-release packages.

Further information about this upcoming release can be seen at:

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with these pre-release packages.

--
Joe Mistachkin @ https://urn.to/r/mistachkin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

2017-10-17 Thread Dan Kennedy 

On 10/17/2017 01:22 PM, Hick Gunter wrote:

In our virtual table implementations, we are using the rowid to return the 
location of the record in the backing store (e.g. record offset in the file 
used as a backing store, offset within a shared memory section or maybe even 
the memory address of the record image) and also implement fast lookup by rowid.

If you don't require such ability, you may as well return a constant, a global 
counter value or a counter that is reset in the xFilter function.

So, YES you always have to implement the xRowid method.

It will only get called if your SELECT statement explicitly mentions it. No "INTEGER 
PRIMARY KEY" magic is performed for virtual tables.


I think the exception is queries with OR terms. With FTS[345], if you do 
something like:


  CREATE VIRTUAL TABLE t1 USING fts5(x);
  EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def';

You can see the Rowid opcodes.

SQLite runs two separate queries on the virtual table - one with "MATCH 
'abc'" and the other with "MATCH 'def'". It uses the rowids for each 
matched row to avoid returning duplicates. If the xRowid method always 
returned 0, then only the first set of matches would be returned 
(because SQLite would deem the second set to be duplicates of the 
first). Or if xRowid returned arbitrary values your results might 
include duplicates. etc.


Same applies to other virtual table types.

Dan.















-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von dave
Gesendet: Montag, 16. Oktober 2017 21:23
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables

Hi, I am building a system which involves a number of virtual table 
implementations.  They are all read-only, but will be involved in a bunch of 
joins amongst themselves.  My question is this:

the documentation
   http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to 
be always required to be implemented.  But does it really?  Is it ever used for 
read-only tables?  I have never seen it invoked, and I have been blithely 
ignoring implementing it, but I wonder if there is a case where it would be 
invoked for a read-only query and so I am tempting fate.

I ask in particular because implementing it will be quite awkward for the 
underlying implementation in my case, and I'd very much prefer to skip it.
Even a 'without rowid' table would imply specifying some primary key, which in 
a few cases would also be awkward.

Thanks in advance,

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


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] xRowid and read only virtual tables....

2017-10-17 Thread dave 
> On 10/16/17, dave  wrote:
> > Hi, I am building a system which involves a number of virtual table
> > implementations.  They are all read-only, but will be 
> involved in a bunch of
> > joins amongst themselves.  My question is this:
> >
> > the documentation
> >   http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid
> > seems (to my reading) to be always required to be 
> implemented.  But does it
> > really?  Is it ever used for read-only tables?  I have never seen it
> > invoked, and I have been blithely ignoring implementing it, 
> but I wonder if
> > there is a case where it would be invoked for a read-only 
> query and so I am
> > tempting fate.
> 
> I don't think xRowid is ever called if you create a WITHOUT ROWID
> virtual table (https://sqlite.org//vtab.html#worid).  But, just to be
> safe, I think I would include a stub function that always returned 0.
> -- 
> D. Richard Hipp

Thanks. OK, I am interpreting that to mean:
*  you could use WITHOUT ROWID, which will surely obviate the need for a
valid xRowid implementation. However that does incur the need for defining
PRIMARY KEY, etc.  (I have verified this. It is problemattic with at least a
few of my vtables)
*  for a read-only vtable, you do not need a valid imlementation of xRowid,
you can just stub it.  However it is required to be present (i.e. the xRowid
member must not be NULL).

-dave


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

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

2017-10-17 Thread David Raymond 
If you're doing 1 gigantic insert to populate the table then it's more 
efficient to delay creating an index until the end. If you've already got a 
bunch of data in there and are inserting more, then you get diminishing returns 
from that. In this case though you're not creating a separate good index, 
you're creating a duplicate one that doesn't add value even when it's there and 
complete, so it's doing nothing but slowing things down. When you declare a 
primary key SQLite implements that by creating a unique index on those fields 
to keep track of it. (named sqlite_autoindex_map_1 below) When you explicitly 
declare another index on the same data SQLite shrugs and does what you ask, 
making a second index of the same things.

Depending on your confidence level you could get rid of the "primary key" line 
and only add the explicit unique index at the end of the initial population. 
Though I'd advise for any primary key to be in there from the start and only 
delay the creation of non-unique/helper indexes. Just my own personal 
preference on that though.


CREATE TABLE map(
  zoom_level INTEGER,
  tile_column INTEGER,
  tile_row INTEGER,
  tile_id VARCHAR(256) NOT NULL,
  PRIMARY KEY(zoom_level, tile_column, tile_row)
);
CREATE UNIQUE INDEX map_index ON map(
  zoom_level ASC,
  tile_column ASC,
  tile_row ASC
);

sqlite> select * from sqlite_master where type = 'index' and tbl_name = 'map';
type|name|tbl_name|rootpage|sql
index|sqlite_autoindex_map_1|map|5|
index|map_index|map|7|CREATE UNIQUE INDEX map_index ON map (zoom_level ASC, 
tile_column ASC, tile_row ASC)

sqlite> pragma index_list(map);
seq|name|unique|origin|partial
0|map_index|1|c|0
1|sqlite_autoindex_map_1|1|pk|0

sqlite> pragma index_info(sqlite_autoindex_map_1);
seqno|cid|name
0|0|zoom_level
1|1|tile_column
2|2|tile_row

sqlite> pragma index_info(map_index);
seqno|cid|name
0|0|zoom_level
1|1|tile_column
2|2|tile_row


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fiona
Sent: Monday, October 16, 2017 10:36 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with 
large db file(>280GB)

>>The setting for synchronous is basically what level of safety net do you
want if it dies in the middle of something. Setting it to off shouldn't
cause any corruption if things go well, it should only come into play if you
saw errors or didn't close things down correctly etc. 

You're right, my Python code was ended manually sometime for it's taking too
much time to finish the INSERT/UPDATE operation, or the image data I get is
wrong.

>>The unique index you declared is redundant by the way, declaring those
three fields as the primary key makes a unique index already to keep track
of that. Did you intend to make that on the retry table? 

The redundant map_index is an attempt to improve insert speed, as I learned
drop index before insert operation is a better way to go. But now with my
data growing so huge, drop/rebuild index also takes quite a long time, and I
never choice to drop then create this index anymore, just leave the index
there. Does it still effect my operation and I should just drop it? 

And if I want to speed up insert operation further more, what measures
should I consider?  I'v already set synchronous and journal_mode off, use
transaction and prepared statement, but when insert data there's still 3
times' speed difference between my code and  the *attach-insert* method. 



--
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

Re: [sqlite] Using .testcase and .check in continuous integration test

2017-10-17 Thread Dominique Devienne 
On Tue, Oct 17, 2017 at 3:16 PM, Lodewijk Duymaer van Twist <
lodew...@adesys.nl> wrote:

> Thank you for investigating. You're fix works. Should I repost this as a
> bug with your fix, or will this be picked up as is right now?
>

Glad it did. Just sit tight and again wait and see if Dr Hipp agrees the
behavior should change or not.
I'm hopeful he might, but if he doesn't, there's little you can do about
it. I don't think reposting is necessary for now. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using .testcase and .check in continuous integration test

2017-10-17 Thread Lodewijk Duymaer van Twist 
Hi Dominique, 

Thank you for investigating. You're fix works. Should I repost this as a bug 
with your fix, or will this be picked up as is right now? 

Kind regard, 

Lodewijk 


Van: "Dominique Devienne"  
Aan: "sqlite-users"  
Verzonden: Dinsdag 17 oktober 2017 13:38:13 
Onderwerp: Re: [sqlite] Using .testcase and .check in continuous integration 
test 

On Tue, Oct 17, 2017 at 12:30 PM, Lodewijk Duymaer van Twist < 
lodew...@adesys.nl> wrote: 

> That would be an other way, but what I'm looking for is using the Command 
> Line Shell ".testcase" and ".check" method. 
> 

OK. That's new information :) 


> Create an example test file: 
> echo ".testcase 100 
> SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); 
> .check 1||1 
> 
> .testcase 110 
> SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); 
> .check 1|5|1" > test-script.sql 
> 
> Execute the test: 
> sqlite3 < test-script.sql 
> 
> Bail will not work for that. 
> 

shell.c needs to change for that, by 1 character I believe, in 
do_meta_command. 
In case of a failure, set rc to 1 instead of 2. That way the shell no 
longer exits 
with 0, but 1, i.e. an error. 

I'm actually surprised the result of do_meta_command() is not subject to 
.bail, 
and always exits if non-zero. 

Either do those changes yourself in your locally compiled shell, 
or hope Richard agrees the behavior should change. --DD 

rc = do_meta_command(azCmd[i], ); 
if( rc ) return rc==2 ? 0 : rc; 

static int do_meta_command(char *zLine, ShellState *p){ 
... 
/* Cancel output redirection, if it is currently set (by .testcase) 
** Then read the content of the testcase-out.txt file and compare against 
** azArg[1]. If there are differences, report an error and exit. 
*/ 
if( c=='c' && n>=3 && strncmp(azArg[0], "check", n)==0 ){ 
char *zRes = 0; 
output_reset(p); 
if( nArg!=2 ){ 
raw_printf(stderr, "Usage: .check GLOB-PATTERN\n"); 
rc = 2; 
}else if( (zRes = readFile("testcase-out.txt", 0))==0 ){ 
raw_printf(stderr, "Error: cannot read 'testcase-out.txt'\n"); 
rc = 2; 
}else if( testcase_glob(azArg[1],zRes)==0 ){ 
utf8_printf(stderr, 
"testcase-%s FAILED\n Expected: [%s]\n Got: [%s]\n", 
p->zTestcase, azArg[1], zRes); 
rc = 2; < change to 1 
}else{ 
utf8_printf(stdout, "testcase-%s ok\n", p->zTestcase); 
p->nCheck++; 
} 
sqlite3_free(zRes); 
}else 
... 

meta_command_exit: 
if( p->outCount ){ 
p->outCount--; 
if( p->outCount==0 ) output_reset(p); 
} 
return rc; 
} 
___ 
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] Using .testcase and .check in continuous integration test

2017-10-17 Thread Dominique Devienne 
On Tue, Oct 17, 2017 at 12:30 PM, Lodewijk Duymaer van Twist <
lodew...@adesys.nl> wrote:

> That would be an other way, but what I'm looking for is using the Command
> Line Shell ".testcase" and ".check" method.
>

OK. That's new information :)


> Create an example test file:
> echo ".testcase 100
> SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1);
> .check 1||1
>
> .testcase 110
> SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1);
> .check 1|5|1" > test-script.sql
>
> Execute the test:
> sqlite3 < test-script.sql
>
> Bail will not work for that.
>

shell.c needs to change for that, by 1 character I believe, in
do_meta_command.
In case of a failure, set rc to 1 instead of 2. That way the shell no
longer exits
with 0, but 1, i.e. an error.

I'm actually surprised the result of do_meta_command() is not subject to
.bail,
and always exits if non-zero.

Either do those changes yourself in your locally compiled shell,
or hope Richard agrees the behavior should change. --DD

rc = do_meta_command(azCmd[i], );
if( rc ) return rc==2 ? 0 : rc;

static int do_meta_command(char *zLine, ShellState *p){
...
  /* Cancel output redirection, if it is currently set (by .testcase)
  ** Then read the content of the testcase-out.txt file and compare against
  ** azArg[1].  If there are differences, report an error and exit.
  */
  if( c=='c' && n>=3 && strncmp(azArg[0], "check", n)==0 ){
char *zRes = 0;
output_reset(p);
if( nArg!=2 ){
  raw_printf(stderr, "Usage: .check GLOB-PATTERN\n");
  rc = 2;
}else if( (zRes = readFile("testcase-out.txt", 0))==0 ){
  raw_printf(stderr, "Error: cannot read 'testcase-out.txt'\n");
  rc = 2;
}else if( testcase_glob(azArg[1],zRes)==0 ){
  utf8_printf(stderr,
 "testcase-%s FAILED\n Expected: [%s]\n  Got: [%s]\n",
 p->zTestcase, azArg[1], zRes);
  rc = 2; < change to 1
}else{
  utf8_printf(stdout, "testcase-%s ok\n", p->zTestcase);
  p->nCheck++;
}
sqlite3_free(zRes);
  }else
...

meta_command_exit:
  if( p->outCount ){
p->outCount--;
if( p->outCount==0 ) output_reset(p);
  }
  return rc;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] xRowid and read only virtual tables....

2017-10-17 Thread Richard Hipp 
On 10/16/17, dave  wrote:
> Hi, I am building a system which involves a number of virtual table
> implementations.  They are all read-only, but will be involved in a bunch of
> joins amongst themselves.  My question is this:
>
> the documentation
>   http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid
> seems (to my reading) to be always required to be implemented.  But does it
> really?  Is it ever used for read-only tables?  I have never seen it
> invoked, and I have been blithely ignoring implementing it, but I wonder if
> there is a case where it would be invoked for a read-only query and so I am
> tempting fate.

I don't think xRowid is ever called if you create a WITHOUT ROWID
virtual table (https://sqlite.org//vtab.html#worid).  But, just to be
safe, I think I would include a stub function that always returned 0.

-- 
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

Re: [sqlite] Using .testcase and .check in continuous integration test

2017-10-17 Thread Lodewijk Duymaer van Twist 
Hi Dominique, 

That would be an other way, but what I'm looking for is using the Command Line 
Shell ".testcase" and ".check" method. 

Create an example test file: 
echo ".testcase 100 
SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); 
.check 1||1 

.testcase 110 
SELECT (1,2,3)<(1,2,4), (1,2,3)<(1,NULL,4), (1,2,3)<(2,NULL,1); 
.check 1|5|1" > test-script.sql 

Execute the test: 
sqlite3 < test-script.sql 

Bail will not work for that. 

Kind regards, 

Lodewijk 


Van: "Dominique Devienne"  
Aan: "sqlite-users"  
Verzonden: Maandag 16 oktober 2017 13:49:18 
Onderwerp: Re: [sqlite] Using .testcase and .check in continuous integration 
test 

On Mon, Oct 16, 2017 at 12:32 PM, Lodewijk Duymaer van Twist < 
lodew...@adesys.nl> wrote: 

> I would like use .testcase and .check in our GitLab Continuous Integration 
> test. 
> 
> GitLab pipelines will check process return code for success or fail. 
> 
> Consider a simple test: 
> lodewijk@DebianDev:~$ sqlite3 database.db3 < test.sql 
> testcase-100 ok 
> testcase-110 ok 
> lodewijk@DebianDev:~$ echo $? 
> 0 
> 
> Now if I would have a failure the return value of the sqlite3 process will 
> also be 0: 
> lodewijk@DebianDev:~$ sqlite3 octalarm.db3 < test/test-languages.sql 
> testcase-100 FAILED 
> Expected: [66] 
> Got: [67 
> ] 
> lodewijk@DebianDev:~$ echo $? 
> 0 
> 
> Is there a nice elegant way of making my CI stop on a failure? 
> 

C:\Users\ddevienne>sqlite3 -bail bad.db "create table foo(id)" && echo OK 
OK 

C:\Users\ddevienne>sqlite3 -bail bad.db "create table bar" && echo OK 
Error: near "bar": syntax error 

C:\Users\ddevienne> 
___ 
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] Performance issue for version 3.9.2 compared with 3.16.2

2017-10-17 Thread advancenOO 
In order to profile sqlite 3.9.2 and sqlite 3.16.2, I run speedtest1.c on my
mobile phone.
It is weird to find that 3.9.2 is better than 3.16.2, especially in the
following case:
“270, 1 DELETEs, numeric BETWEEN, indexed"
3.9.2 uses 0.7s while 3.16.2 uses 1.8s;

My relevant compilation options are shown below:
-DSQLITE_THREADSAFE=1
-DSQLITE_DEFAULT_AUTOVACUUM=1
-DSQLITE_DEFAULT_PAGE_SIZE=4096
-DSQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=1048576

Further more, 3.16.2 performs better when I decrease the delete times from
1 to 1000.
So is it expected? If not, then what might be the possible reason? 

Thanks for your help.



--
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

Re: [sqlite] [EXTERNAL] current_timestamp locale

2017-10-17 Thread Stephen Chrzanowski 
Yes, I understand that beyond integer, float, and chars, there are no
further "types", but I was just questioning whether current_timestamp
formats what it stores in a specific manor, or, if it has some dealings
with the locale of the system its executed on.  You've answered the
question.  Thanks. :]

On Tue, Oct 17, 2017 at 2:34 AM, Hick Gunter  wrote:

> There is no "DATE" type in SQLite. The current_timestamp is a TEXT value
> equivalent to datetime('now') which returns an ISO formatted datetime
> string in UTC (-MM-DD HH:MM:SS).
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Stephen Chrzanowski
> Gesendet: Dienstag, 17. Oktober 2017 07:59
> An: General Discussion of SQLite Database  sqlite.org>
> Betreff: [EXTERNAL] [sqlite] current_timestamp locale
>
> Does SQLite go by users locale to insert date/time information into a row,
> or is it a very specific format when using current_timestamp as a default
> value?
>
> I don't want to go start monkeying with my system settings to find out,
> so, relying on those with experience.
>
> Thanks
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
> 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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] [EXTERNAL] one to one relationships

2017-10-17 Thread Hick Gunter 
What is the question?

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Paul Alagna
Gesendet: Dienstag, 17. Oktober 2017 08:35
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] one to one relationships

2 tables keyed alike are in a one to one relationship. IE every record of T1 
will yield one and only one record in T2

PAUL ALAGNA
pjala...@gmail.com 




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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] current_timestamp locale

2017-10-17 Thread Hick Gunter 
There is no "DATE" type in SQLite. The current_timestamp is a TEXT value 
equivalent to datetime('now') which returns an ISO formatted datetime string in 
UTC (-MM-DD HH:MM:SS).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Stephen Chrzanowski
Gesendet: Dienstag, 17. Oktober 2017 07:59
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] current_timestamp locale

Does SQLite go by users locale to insert date/time information into a row, or 
is it a very specific format when using current_timestamp as a default value?

I don't want to go start monkeying with my system settings to find out, so, 
relying on those with experience.

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] one to one relationships

2017-10-17 Thread Paul Alagna 
2 tables keyed alike are in a one to one relationship. IE every record of T1 
will yield one and only one record in T2

PAUL ALAGNA
pjala...@gmail.com 




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

Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

2017-10-17 Thread Hick Gunter 
In our virtual table implementations, we are using the rowid to return the 
location of the record in the backing store (e.g. record offset in the file 
used as a backing store, offset within a shared memory section or maybe even 
the memory address of the record image) and also implement fast lookup by rowid.

If you don't require such ability, you may as well return a constant, a global 
counter value or a counter that is reset in the xFilter function.

So, YES you always have to implement the xRowid method.

It will only get called if your SELECT statement explicitly mentions it. No 
"INTEGER PRIMARY KEY" magic is performed for virtual tables.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von dave
Gesendet: Montag, 16. Oktober 2017 21:23
An: 'SQLite mailing list' 
Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables

Hi, I am building a system which involves a number of virtual table 
implementations.  They are all read-only, but will be involved in a bunch of 
joins amongst themselves.  My question is this:

the documentation
  http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to 
be always required to be implemented.  But does it really?  Is it ever used for 
read-only tables?  I have never seen it invoked, and I have been blithely 
ignoring implementing it, but I wonder if there is a case where it would be 
invoked for a read-only query and so I am tempting fate.

I ask in particular because implementing it will be quite awkward for the 
underlying implementation in my case, and I'd very much prefer to skip it.
Even a 'without rowid' table would imply specifying some primary key, which in 
a few cases would also be awkward.

Thanks in advance,

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users