[sqlite] current_timestamp locale

2017-10-16 Thread Stephen Chrzanowski 
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

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

2017-10-16 Thread Fiona 
>>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] xRowid and read only virtual tables....

2017-10-16 Thread dave 
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

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

2017-10-16 Thread Kees Nuyt 
On Sun, 15 Oct 2017 18:36:56 -0700 (MST), Fiona
 wrote:

> Thanks for noticing that problem! Follow your instructions, now I'm sure
> it's all because my db file is corrupted.  Is there anything I can do to fix
> it?
>
> Integrity check result:
>  

I can think of three options:

1- Rebuild the database from the original input, 
   with the schema improvements suggested in
   this thread

2- Restore a recent backup, then import the data into
   a new database with the correct schema [*].

3- the recipe that Simon gave to retrieve as much of 
   the contents as possible using the .dump command
   and build a new database from the dump file,
   then import the data into a new database with
   the correct schema [*].


[*] The script for importing data from a database with the old
schema into a database with a better schema loks like this:

sqlite3 newdb.sqite http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 on Windows

2017-10-16 Thread Dominique Devienne 
On Sat, Oct 14, 2017 at 10:47 PM, Phoenix 
wrote:

> Dominique wrote:
> Not to sound too snarky, but both questions can easily be answered
> through experimentation.
> You can also use http://www.dependencywalker.com/ to inspect DLL and
> EXE dependencies. --DD
>
> It's a bit difficult to do that if you don't have access to a Windows
> box. I have been using Linux since Jan 2009 and I haven't touched
> Windows since then.
>

OK. You didn't mention that before. I'm sure Linux has a way to look inside
Windows PE binaries, e.g. [1] below perhaps, but whether one can get the
dependencies easily, I don't know.

Had you explained your lack of Windows access, i.e. context, you'd probably
have gotten your answer quicker, FWIW.

He had downloaded sqlite-dll-win32-x86-3200100.zip and after unzipping
> it was trying to link it to his GnuCOBOL program and having problems.
> One of the project developers was able to put him right.


Glad it worked out in the end, via a different channel. --DD

[1] https://ubuntuforums.org/showthread.php?t=1442258
___
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-16 Thread Richard Hipp 
On 10/16/17, David Raymond  wrote:
> 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.

Actually, synchronous=off is safe as long as you don't take a
hard-reboot or power loss in the middle of a transaction.  An
application crash with synchronous=off should be harmless.

-- 
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] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

2017-10-16 Thread David Raymond 
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.

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?


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

Thanks a lot! That may be the problem: my db file is corrupted. Below is the
*PRAGMA integrity_check* result. It didn't return OK. 
 

I think it's because I set PRAGMA synchronous=off in Python code to enhance
insert speed. 
Does that mean this db file can not be used anymore? Or is there any way I
can fix it?



--
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-16 Thread Dominique Devienne 
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] Using .testcase and .check in continuous integration test

2017-10-16 Thread Lodewijk Duymaer van Twist 
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? 

Kind regards, 

Lodewijk 

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

Re: [sqlite] Odd query plan for without rowid table

2017-10-16 Thread Dominique Devienne 
On Mon, Oct 16, 2017 at 12:28 AM, Richard Hipp  wrote:
>
> Fixed on trunk.  https://sqlite.org/src/info/ee31c043

FYI, small typo in that commit. --DD

line 1885 of where.c
** Return TRUE if all of the following are true:
**
**   (1)  X has the same or lower cost that Y
**   (2)  X users fewer WHERE clause terms than Y
**   (3)  Every WHERE clause term used by X is also used by Y
**   (4)  X skips at least as many columns as Y
**   (5)  If X is a covering index, than Y is too

-- **   (2)  X users fewer WHERE clause terms than Y
++ **   (2)  X uses fewer WHERE clause terms than Y
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users