[sqlite] datetime in CHECK

On 4 May 2016, at 9:43pm, Roman Fleysher wrote: > when I insert '2015-08-10T17:19:37.670' or '2015-08-10 17:19:37.670' fails. > Why? Take a look at the result of "datetime(AcquisitionDateTime)" . You can use the SQLite shell tool. > How to do it properly? I suspect you want something

[sqlite] SQLite's use of memory only temporary data

On 4 May 2016, at 9:32pm, Nikolaos Tsikoudis wrote: > I execute a query and I see that SQLite creates temporary data that are > never written to the disk and I am trying to understand what happens. Use the EXPLAIN QUERY PLAN command: It will show you how

[sqlite] datetime in CHECK

Thank you. I did not notice loss of fractional seconds. I now see in the manual that datetime() is equivalent to strftime('%Y-%m-%d %H:%M:%S', ...), with capital "S" rather than lower "f" at the end that I expected. Roman From: sqlite-users-bounces at

[sqlite] Fastest way to backup/copy database?

On 2016/05/04 8:38 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 5:51 PM, Scott Robison > wrote: > This is going to become a bigger problem for us as the database will only get bigger so any advice welcomed. >> Perhaps, rather than backing up the live data, you create an

[sqlite] datetime in CHECK

Dear SQLiters, I am trying to use CHECK constraint is column of a table to enforce datetime format and this works: AcquisitionDateTEXT CHECK (AcquisitionDate IS date(AcquisitionDate)) when I insert '2015-08-10'. But this AcquisitionDateTime TEXT CHECK (AcquisitionDateTime IS

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 5:51 PM, Scott Robison wrote: > > > This is going to become a bigger problem for us as the database will > > > only get bigger so any advice welcomed. > > Perhaps, rather than backing up the live data, you create an append only > log of each and every query you send to the

[sqlite] Fastest way to backup/copy database?

On 2016/05/04 2:35 PM, Rob Willett wrote: > Dominque, > > We put together a quick C program to try out the C API a few weeks > ago, it worked but it was very slow, from memory not much different to > the sqlite command line backup system. We put it on the back burner as > it wasn?t anywhere

[sqlite] Fastest way to backup/copy database?

Dan, Thats NOT the case for us so that explains why things are slow. Mmm? as I recall we never did get a backup to finish?. Now we know why :) Rob On 4 May 2016, at 18:53, Dan Kennedy wrote: > On 05/05/2016 12:45 AM, Rob Willett wrote: >> Ryan, >> >> Ah! The penny drops, we didn?t realise

[sqlite] Fastest way to backup/copy database?

Scott, OK, We can see how to do this (I think). Our app is written in Perl and we?d just need to capture the command we write down. The only issue I can think of is the prepare statement and making sure we capture the right SQL command. W We?ll dig into it and have a look, Thanks for taking

[sqlite] datetime in CHECK

datetime('2015-08-10T17:19:37.670') is '2015-08-10 17:19:37' datetime('2015-08-10 17:19:37.670') is '2015-08-10 17:19:37' as you see, the output is not equal to the input. Therefore the check constraint fails. However, datetime(timestring) where timestring is an invalid ISO timestring

[sqlite] Fastest way to backup/copy database?

Scott, Thats an interesting idea. Is there an option in SQLite to do this for us, or do we have to write a small shim in our app? I like the idea of this as its simple and elegant. Rob On 4 May 2016, at 16:51, Scott Robison wrote: >>> This is going to become a bigger problem for us as the

[sqlite] Fastest way to backup/copy database?

Ryan, Ah! The penny drops, we didn?t realise that with the backup API. That explains a great deal. We must have missed that in the docs. Blast. We?ve looked around for other providers in Europe and the cost differences are very high. We need to be in the EU for various data protection

[sqlite] SQLite custom function for regular expression using c/c++

I want to write the custom function for regular expression using C/C++. How to write it. Is there the document/sample code available on this. -Bhagwat

[sqlite] SQLite's use of memory only temporary data

You are right, I don't know why I didn't think to use explain query plan command earlier. Its output shows exactly what is going on. An automatic covering index is created. Thank you very much. On Wed, May 4, 2016 at 5:11 PM, Simon Slavin wrote: > > On 4 May 2016, at 9:32pm, Nikolaos Tsikoudis

[sqlite] Fastest way to backup/copy database?

Rob, I have a continuously running application that simply writes the same data to two different databases; one in the primary location and the other in the backup location. This is obviously not a perfect solution (a man with two watches never really knows what time it is) but it's good

[sqlite] SQLite's use of memory only temporary data

Hi, I execute a query and I see that SQLite creates temporary data that are never written to the disk and I am trying to understand what happens. My query's structure looks like the following: select sum() from table1 t1 table2 t2 where ( t1.a = t2.a and ... ) or ( t1.a =

[sqlite] Fastest way to backup/copy database?

On Wed, 04 May 2016 11:44:17 +0100 "Rob Willett" wrote: > Hi, > > We think we know the answer to this, but we?ll ask the question > anyway. > > We?re trying to backup a 10GB live running database > ?as-fast-as-we-possibly-can? without stopping updates coming in. The > updates come every 2-3

[sqlite] Fastest way to backup/copy database?

Simon, Thanks for the reply, we were a little surprised at the UNIX cp speed as well. We investigated it with the provider in the past over other file system speed issues and it turned out that they rate limit the IO ops, so you can?t consume them all. Our provider runs many servers out of

[sqlite] SQLite custom function for regular expression using c/c++

> Am 04.05.2016 um 14:25 schrieb Bhagwat Balshetwar gmail.com>: > > I want to write the custom function for regular expression using C/C++. > How to write it. Is there the document/sample code available on this. > Of course. Google is your friend. Maybe start reading here:

[sqlite] Fastest way to backup/copy database?

On 4 May 2016, at 1:35pm, Rob Willett wrote: > I think that unless we can get the C API to back up in a time close to that > of a cp, the easiest solution is to suspend updates for 10-15 mins > out-of-hours and do a simple cp from there. Sometimes a change in workflow > might be the easiest

[sqlite] Fastest way to backup/copy database?

Rob Willett wrote: > We?re trying to backup a 10GB live running database ?as-fast-as-we- > possibly-can? without stopping updates coming in. How much memory do you have? I guess you can't simply read the entire database file to force it into the file cache? In WAL mode, a writer does not block

[sqlite] Fastest way to backup/copy database?

Clemens, We have 8GB of memory which is the most our VPS provider allows. We?d like 32GB but its not an option for us. Our desktops have more than that, but the VPS provider is reasonably priced :) We hadn?t considered the WAL mode, my conclusion is that a simple change to our workflow is

[sqlite] Fastest way to backup/copy database?

Dominque, We put together a quick C program to try out the C API a few weeks ago, it worked but it was very slow, from memory not much different to the sqlite command line backup system. We put it on the back burner as it wasn?t anywhere near quick enough. We hadn?t seen or found or even

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 1:26 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 1:22 PM, Dominique Devienne > wrote: >> On Wed, May 4, 2016 at 1:13 PM, Rob Willett >> wrote: >>> Thanks for the reply, >>> >>> Yes Example 2 in https://www.sqlite.org/backup.html is what we are talking >>>

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 1:22 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 1:13 PM, Rob Willett > wrote: >> Thanks for the reply, >> >> Yes Example 2 in https://www.sqlite.org/backup.html is what we are talking >> about. It was very slow to run for us. > > Then maybe

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 12:38 PM, Dominique Devienne wrote: > On Wed, May 4, 2016 at 5:51 PM, Scott Robison > wrote: > > > > > This is going to become a bigger problem for us as the database will > > > > only get bigger so any advice welcomed. > > > > Perhaps, rather than backing up the live

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 1:13 PM, Rob Willett wrote: > Thanks for the reply, > > Yes Example 2 in https://www.sqlite.org/backup.html is what we are talking > about. It was very slow to run for us. Then maybe https://www.sqlite.org/rbu.html is your last change. Although I don't see how it could be

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 12:44 PM, Rob Willett wrote: > We?re trying to backup a 10GB live running database [...] > 2. If we use the command line sqlite .dump > it > works, but its very slow. That's going to SQL text. While .backup is page-based, and binary. But not incremental in the Shell I

[sqlite] Fastest way to backup/copy database?

Thanks for the reply, Yes Example 2 in https://www.sqlite.org/backup.html is what we are talking about. It was very slow to run for us. Rob On 4 May 2016, at 12:08, Dominique Devienne wrote: > On Wed, May 4, 2016 at 12:44 PM, Rob Willett > wrote: >> We?re trying to backup a 10GB live running

[sqlite] Fastest way to backup/copy database?

On Wed, May 4, 2016 at 11:47 AM, Rob Willett wrote: > Scott, > > Thats an interesting idea. Is there an option in SQLite to do this for us, > or do we have to write a small shim in our app? > > I like the idea of this as its simple and elegant. It would require a little extra work on your

[sqlite] Fastest way to backup/copy database?

Hi, We think we know the answer to this, but we?ll ask the question anyway. We?re trying to backup a 10GB live running database ?as-fast-as-we-possibly-can? without stopping updates coming in. The updates come every 2-3 mins, and write a chunk of data in. We can?t really stop the database

[sqlite] Version 3.13.0 coming soon

Am Tue, 3 May 2016 13:21:04 -0400 schrieb Richard Hipp: > On 5/3/16, nomad at null.net wrote: >> On Tue May 03, 2016 at 08:33:30AM -0400, Richard Hipp wrote: >>> >>> Yes. Apparently that is the new standard for security on unix >> >> The way I understood Rolf's comment was that he was pointing

[sqlite] Fastest way to backup/copy database?

> > This is going to become a bigger problem for us as the database will > > only get bigger so any advice welcomed. Perhaps, rather than backing up the live data, you create an append only log of each and every query you send to the database. Should you need to restore, you replay the log of

[sqlite] SQLite custom function for regular expression using c/c++

On 5/4/16, Bhagwat Balshetwar wrote: > I want to write the custom function for regular expression using C/C++. You mean like this one: https://www.sqlite.org/src/artifact/a68d25c659bd2d89 -- D. Richard Hipp drh at sqlite.org

[sqlite] Version 3.13.0 coming soon

See in-line; On Wed, May 4, 2016 at 4:39 AM, Wolfgang Enzinger wrote: > > Two more typos (IMHO, I'm not a native English speaker): > > "where not being recognized" -> "were not being recognized" > > "Where" could be right or wrong, but I suspect this is the correct word. WHERE would mean

[sqlite] Make mmap_size dynamic?

On Tuesday, 3 May 2016, Richard Hipp > wrote: > On 5/2/16, Mikael wrote: > > > > If I just force it on (by hacking the build script), as long as mmap_size > > always is 2^63, will Sqlite access the file via memory accesses only, and > > never using fread/fwrite which would lead to undefined