Re: [sqlite] Date time input

[Keith Medcalf]

>Because that's locale-dependent. Some countries, like the US, use
>month/day/year; most other countries use day/month/year. 

Maybe.  Canada supposedly uses the day/month/year format, or so I suddenly 
became aware of in 1998 when I was in my mid 30's.  Prior to that day it was 
m/d/y.  Then again, ever since I was 12 it was always y-m-d.  And the hours 
always went from 00 to 23.

>To interpret such a date string, SQLite has to know what country's 
>customs to use. And that is a pretty significant problem, since:
>
>- Different operating systems communicate locale info in completely
>  different ways
>- The locale settings may not be applied at the layer of the OS where
>  SQLite is running (example: Android only very recently started setting
>  the C-level locale to match the GUI locale.)
>- The current locale may not match the locale from which the date string
>   originates

Not to mention that whoever you have appointed as the "authority" for "locale" 
probably has no bloody clue.  Plus of course if I get on an airplane and fly to 
Japan the locale is completely different from if I hop on a plane and fly to 
Amsterdam, and still different again if I fly to Miami.  No date entered in any 
one of those locale's will be parseable in any other.

In other words a date format of -MM-DD HH:MM:SS.S TZ can always be 
unambiguosly parsed.  The others only perhaps maybe sometimes occassionally.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Re: [sqlite] Date time input

[Jens Alfke]

> On Oct 7, 2019, at 6:17 AM, Jose Isaias Cabrera  wrote:
> 
> I have to ask this question:  Why is it that the date function does not take 
> a '4/5/2019' and returns '2019-04-05'? 

Because that's locale-dependent. Some countries, like the US, use 
month/day/year; most other countries use day/month/year. To interpret such a 
date string, SQLite has to know what country's customs to use. And that is a 
pretty significant problem, since:

- Different operating systems communicate locale info in completely different 
ways
- The locale settings may not be applied at the layer of the OS where SQLite is 
running (example: Android only very recently started setting the C-level locale 
to match the GUI locale.)
- The current locale may not match the locale from which the date string 
originates

A database should not have to care about locales. It's supposed to process data 
in globally-consistent ways that don't depend on settings like that.

I swear, half the questions on this list build down to "Why doesn't 
SQLite act like MS Access?" If you need all the bells and whistles of 
formatting input and output, then use a fancy DBMS application. SQLite is for 
embedded use _inside_ applications.

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

Re: [sqlite] [patch] avoid dynamic alloc in vdbeSorterSort(...)

[Keith Medcalf]

On Monday, 7 October, 2019 14:58, Dominique Pellé  
wrote:

>Here the allocated size is fixed (always 64 pointers), so alloca does
>not seem needed.

>I wonder how many other functions could avoid dynamic allocation
>like this one (either with a stack array or alloca).

Probably a lot.  I would note that it probably does not allocate on the stack 
by default because SQLite3 is intended to be cross-platform.  While many 
Operating Systems and Platforms may allow for lots of stack (virtually 
unlimited for modern flat address space Operating Systems running on x86_64 
hardware), this is not necessarily the case for all Operating Systems or 
platforms in which the per-thread or per-process stack may be quite limited.

For example, in a single-thread process on a "flat virtual address space" 
machine, the stack theoretically extends from the end of the last code segment 
all the way up to the bottom of the heap (the heap grows down and the stack 
grows up).  However, as soon as you allow multiple threads in that process, you 
now have an arbitrary constrained stack, since the threads must each now be 
allocated a specific address space range for that threads stack.

If the memory model is not "flat", then the problem won't exist because the 
additional thread will merely be allocated a different "selector" for its 
stack.  However, not all machines treat segments as selectors, so there may 
very well still be constraints (ie, consider the old 16-bit addressing mode the 
segments/selectors were really just paragraph addresses in a flat address 
space, not true selectors).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] [patch] avoid dynamic alloc in vdbeSorterSort(...)

[Dominique Pellé]

Here the allocated size is fixed (always 64 pointers), so alloca does
not seem needed.

I wonder how many other functions could avoid dynamic allocation
like this one (either with a stack array or alloca).

Regards
Dominique

On Mon, Oct 7, 2019 at 10:26 PM Mateusz Wajchęprzełóż
 wrote:
>
> What about sqlite3StackAllocZero and SQLITE_USE_ALLOCA?
>
> pon., 7 paź 2019 o 20:51 Dominique Pellé 
> napisał(a):
>
> > Hi
> >
> > Below is a patch which avoids a dynamic
> > allocation in vdbeSorterSort(...), using a local
> > stack array instead (faster and smaller code).
> > I assume that a local array of 64 pointers is small
> > enough to be in the stack.
> >
> > Is this worth merging?
> >
> > $ fossil diff  src/vdbesort.c
> > Index: src/vdbesort.c
> > ==
> > --- src/vdbesort.c
> > +++ src/vdbesort.c
> > @@ -1394,25 +1394,20 @@
> >  ** SQLITE_OK if successful, or an SQLite error code (i.e. SQLITE_NOMEM) if
> >  ** an error occurs.
> >  */
> >  static int vdbeSorterSort(SortSubtask *pTask, SorterList *pList){
> >int i;
> > -  SorterRecord **aSlot;
> > +  SorterRecord *aSlot[64] = { 0 };
> >SorterRecord *p;
> >int rc;
> >
> >rc = vdbeSortAllocUnpacked(pTask);
> >if( rc!=SQLITE_OK ) return rc;
> >
> >p = pList->pList;
> >pTask->xCompare = vdbeSorterGetCompare(pTask->pSorter);
> >
> > -  aSlot = (SorterRecord **)sqlite3MallocZero(64 * sizeof(SorterRecord *));
> > -  if( !aSlot ){
> > -return SQLITE_NOMEM_BKPT;
> > -  }
> > -
> >while( p ){
> >  SorterRecord *pNext;
> >  if( pList->aMemory ){
> >if( (u8*)p==pList->aMemory ){
> >  pNext = 0;
> > @@ -1438,11 +1433,10 @@
> >  if( aSlot[i]==0 ) continue;
> >  p = p ? vdbeSorterMerge(pTask, p, aSlot[i]) : aSlot[i];
> >}
> >pList->pList = p;
> >
> > -  sqlite3_free(aSlot);
> >assert( pTask->pUnpacked->errCode==SQLITE_OK
> > || pTask->pUnpacked->errCode==SQLITE_NOMEM
> >);
> >return pTask->pUnpacked->errCode;
> >  }
> >
> > Regards
> > Dominique
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [patch] avoid dynamic alloc in vdbeSorterSort(...)

[Mateusz Wajchęprzełóż]

What about sqlite3StackAllocZero and SQLITE_USE_ALLOCA?

pon., 7 paź 2019 o 20:51 Dominique Pellé 
napisał(a):

> Hi
>
> Below is a patch which avoids a dynamic
> allocation in vdbeSorterSort(...), using a local
> stack array instead (faster and smaller code).
> I assume that a local array of 64 pointers is small
> enough to be in the stack.
>
> Is this worth merging?
>
> $ fossil diff  src/vdbesort.c
> Index: src/vdbesort.c
> ==
> --- src/vdbesort.c
> +++ src/vdbesort.c
> @@ -1394,25 +1394,20 @@
>  ** SQLITE_OK if successful, or an SQLite error code (i.e. SQLITE_NOMEM) if
>  ** an error occurs.
>  */
>  static int vdbeSorterSort(SortSubtask *pTask, SorterList *pList){
>int i;
> -  SorterRecord **aSlot;
> +  SorterRecord *aSlot[64] = { 0 };
>SorterRecord *p;
>int rc;
>
>rc = vdbeSortAllocUnpacked(pTask);
>if( rc!=SQLITE_OK ) return rc;
>
>p = pList->pList;
>pTask->xCompare = vdbeSorterGetCompare(pTask->pSorter);
>
> -  aSlot = (SorterRecord **)sqlite3MallocZero(64 * sizeof(SorterRecord *));
> -  if( !aSlot ){
> -return SQLITE_NOMEM_BKPT;
> -  }
> -
>while( p ){
>  SorterRecord *pNext;
>  if( pList->aMemory ){
>if( (u8*)p==pList->aMemory ){
>  pNext = 0;
> @@ -1438,11 +1433,10 @@
>  if( aSlot[i]==0 ) continue;
>  p = p ? vdbeSorterMerge(pTask, p, aSlot[i]) : aSlot[i];
>}
>pList->pList = p;
>
> -  sqlite3_free(aSlot);
>assert( pTask->pUnpacked->errCode==SQLITE_OK
> || pTask->pUnpacked->errCode==SQLITE_NOMEM
>);
>return pTask->pUnpacked->errCode;
>  }
>
> Regards
> Dominique
> ___
> 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] [patch] avoid dynamic alloc in vdbeSorterSort(...)

[Dominique Pellé]

Hi

Below is a patch which avoids a dynamic
allocation in vdbeSorterSort(...), using a local
stack array instead (faster and smaller code).
I assume that a local array of 64 pointers is small
enough to be in the stack.

Is this worth merging?

$ fossil diff  src/vdbesort.c
Index: src/vdbesort.c
==
--- src/vdbesort.c
+++ src/vdbesort.c
@@ -1394,25 +1394,20 @@
 ** SQLITE_OK if successful, or an SQLite error code (i.e. SQLITE_NOMEM) if
 ** an error occurs.
 */
 static int vdbeSorterSort(SortSubtask *pTask, SorterList *pList){
   int i;
-  SorterRecord **aSlot;
+  SorterRecord *aSlot[64] = { 0 };
   SorterRecord *p;
   int rc;

   rc = vdbeSortAllocUnpacked(pTask);
   if( rc!=SQLITE_OK ) return rc;

   p = pList->pList;
   pTask->xCompare = vdbeSorterGetCompare(pTask->pSorter);

-  aSlot = (SorterRecord **)sqlite3MallocZero(64 * sizeof(SorterRecord *));
-  if( !aSlot ){
-return SQLITE_NOMEM_BKPT;
-  }
-
   while( p ){
 SorterRecord *pNext;
 if( pList->aMemory ){
   if( (u8*)p==pList->aMemory ){
 pNext = 0;
@@ -1438,11 +1433,10 @@
 if( aSlot[i]==0 ) continue;
 p = p ? vdbeSorterMerge(pTask, p, aSlot[i]) : aSlot[i];
   }
   pList->pList = p;

-  sqlite3_free(aSlot);
   assert( pTask->pUnpacked->errCode==SQLITE_OK
|| pTask->pUnpacked->errCode==SQLITE_NOMEM
   );
   return pTask->pUnpacked->errCode;
 }

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

Re: [sqlite] Date time input

[Tim Streater]

On 07 Oct 2019, at 17:19, Keith Medcalf  wrote:

> On Monday, 7 October, 2019 07:17, Jose Isaias Cabrera 
> wrote:
>
>>I have to ask this question:  Why is it that the date function does not
>>take a '4/5/2019' and returns '2019-04-05'?  
>
> Because it does not.  Humans read things in big endian order.

No, we read them in order of importance. If someone asks what the date is, the 
most useful answer is the day number of the month (not, for example, the year). 
Equally, if asomeone asks what time of day it is, the most useful answer is the 
hour (not, for example, the second). If someone asked me the date and time, I'd 
hardly respond "It's 2019 and its 27 seconds past." Wouldn't be that useful, 
now would it. So the most important item if first, then the others in 
decreasing order of importance.


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

Re: [sqlite] Date time input

[Donald Griggs]

Hi, Jose,

Re: Wishing out loud.
Maybe an implementation of sscanf() would be more useful generally.

As to your date parsing problem, if you really insist on doing it in sql,
you may already know how to accomplish it with something like the UPDATE
below:


.mode column
.headers on

create table t1 (dt, dtIso); -- I'm assuming you meant input (dt) to be in
m-d- format (and not d-m-)

INSERT INTO t1 VALUES
   ('2/1/2017',   NULL),
   ('2/19/2019',  NULL),
   ('12/5/1955',  NULL),
   ('12/13/2018', NULL)
  ;

UPDATE t1 SET dtISO = printf('%04d-%02d-%02d',
   substr(dt, -4, 4),
   CAST(dt AS INTEGER),
   CAST ( replace(substr(dt, 3), '/', ' ')  AS INTEGER)
   );

SELECT * FROM t1;
-- output, using sqlite3.exe version 3.29.0, was:

dt  dtIso
--  --
4/5/20192019-04-05
2/19/2019   2019-02-19
12/5/1955   1955-12-05
12/13/2018  2018-12-13

You can then check for a valid date with something like:
SELECT  date('2019-12-32') isnull;
but if it's user input, you'd be sanitizing your inputs before they reach
sqlite, I should hope.

Donald



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

Re: [sqlite] Date time input

[Simon Slavin]

On 7 Oct 2019, at 2:17pm, Jose Isaias Cabrera  wrote:

> I have to ask this question:  Why is it that the date function does not take 
> a '4/5/2019' and returns '2019-04-05'?

Those are human formats.  SQLite is a database management system.  Its job is 
to store data and allow it to be recalled in convenient ways.

Converting data to and from a convenient storage format is not the job of a 
DBMS.  If you want a software library which does date format conversion, you 
could go look for a software library which does date format conversion.  One 
which can be called from your preferred programming language.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

[Keith Medcalf]

On Monday, 7 October, 2019 07:17, Jose Isaias Cabrera  
wrote:

>I have to ask this question:  Why is it that the date function does not
>take a '4/5/2019' and returns '2019-04-05'?  

Because it does not.  Humans read things in big endian order.  For example, in 
the common base 10 system used everywhere in this solar system, the left most 
position contains the biggest thing, with the bigness represented by each digit 
decreasing by a factor of 10 as you travel from left to right.  When you are 
talking about datetime, the biggest thing is the year, then the month, then the 
day, then the hour, then the minute, then the seconds and fractions thereof.  
Therefore, having datetimes in "parts are in descending order of bigness" is 
rational, consistent, cannot be misinterpreted, and requires no external 
rosetta stone for interpretation of the various parts.

How confusing would it be if one million four hundred thirty seven thousand six 
hundred and forty two were written as 4371426?

>This may have been asked
>before, and the answer may be some where in the internet, but, I could
>not find it.  The other thing is that it would be nice to have date take
>something like this,

>date('2/15/2019','m/d/')

This is exactly why.  Because the format is ambiguous and requires the input of 
an external rosetta stone key for decoding, such formats are not used.

>and return

>2019-02-15
>
>so I don't have to deal with the changes. :-)  Just wishing out-loud...

You can always write an extension function that translates between arbitrary 
datetime or number formats into standardized format, or use the built-in 
capabilities of your programming language standard library or Operating System 
to do that before you pass the data to SQLite3 in the format that it requires.  
Just as SQLite3 does not recognize the string 4371426 as a valid representing 
of the number 1437642, it does not recognize that 4/5/6789 is 6789-04-05 (or is 
that 6789-05-04, or is it 04-6789-05 in the Alpha Centauri calendar?).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Re: [sqlite] Date time input

[Roman Fleysher]

I had to deal with a similar conversion.

I think the answer to why date() does not take other formats is simple: SQLite 
is minimalistic. This string processing can be done outside SQLite library.

Minimalism of SQLite is one of the criteria for what gets implemented. It has 
few mathematical operations, few string manipulations, subset of SQL, etc. 
Sqlite developers are very good at choosing which few to implement to give 
maximum of versatility. Every time I notice such decision I get more impressed. 
I hope you will experience such joy as well. 

 


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Jose Isaias Cabrera [jic...@outlook.com]
Sent: Monday, October 07, 2019 9:17 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Date time input

Greetings.

I have to ask this question:  Why is it that the date function does not take a 
'4/5/2019' and returns '2019-04-05'?  This may have been asked before, and the 
answer may be some where in the internet, but, I could not find it.  The other 
thing is that it would be nice to have date take something like this,

date('2/15/2019','m/d/')

and return

2019-02-15

so I don't have to deal with the changes. :-)  Just wishing out-loud...  Thanks.

josé


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7Cfa23548f95114997012508d74b28b25c%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637060510468196612sdata=kxiXOugWi%2FGEmGs%2FKi7FTyK4xpipJeCmK0EBv9qkbFc%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copy-on-write VFS

[Keith Medcalf]

On Monday, 7 October, 2019 03:16, Simon Slavin  wrote:

>> Given that we disable ceckpointing, can we assume that the main
>> database-file will never be modified, and therefor could potentially be
>> mounted read-only?

No, and No.

>You are correct that the WAL file contains database pages, rather than
>SQL statements.  A recent change to the way WAL files work means that if
>an already-modified page is modified again, the page appears in the WAL
>file only once.  So the size of the WAL file depends more on how much new
>data gets written to the database than the total number of changes.

You are misstating the change.  The correct statement is:

"A recent change to the way WAL files work means that if a page ALREADY 
MODIFIED IN A TRANSACTION IS MODIFIED AGAIN IN THE SAME TRANSACTION, then that 
page appears in the WAL file only once FOR THAT TRANSACTION."  That is to say 
that for each transaction appearing in the WAL file, each page modified by that 
transaction appears only once.  If a given page is modified by multiple 
transactions then that page appears in the WAL file once for each transaction.  
It must be so or you could not have a "snapshot" position between transactions. 
 However, if you are coalescing (checkpointing) three transactions from the WAL 
file to the main database, then indeed only the latest change to a page made by 
any of those transactions needs to be copied to the main file, even though each 
of the three transactions may have modified the same page.

>Presumably you mean you disable /automatic/ checkpointing.  If you
>disable all checkpointing your writes never get completed.  Sooner or
>later you are going to have to let SQLite modify your database file, so
>no, you can't mount it read-only if you want to make changes.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

[sqlite] Date time input

[Jose Isaias Cabrera]

Greetings.

I have to ask this question:  Why is it that the date function does not take a 
'4/5/2019' and returns '2019-04-05'?  This may have been asked before, and the 
answer may be some where in the internet, but, I could not find it.  The other 
thing is that it would be nice to have date take something like this,

date('2/15/2019','m/d/')

and return

2019-02-15

so I don't have to deal with the changes. :-)  Just wishing out-loud...  Thanks.

josé


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

Re: [sqlite] Copy-on-write VFS

[Fredrik Larsen]

I'm aware that what I'm asking for is not be very portable between
sql-engines, and that is fine. It does not affect core-product/value, just
infrastructure. Also, this is about optimizing infrastructure work, so nice
if it works, no biggi if it does not.

But the thing we want to optimize is the size we need to copy to move/copy
db to different nodes. Today we must copy the full 20Gb+ to accomplish
this, and this will also be required in your text-file solution. One
possible solution is to store a read-only "base-image" of the db in a
network-mounted folder, and only copy over the changes since base-image was
created. This will reduce copy-time to almost zero in our case. Data is
accumulated over a long time so day-to-day changes are small (<1Mb?). Also,
I know that moving database around is not a very common requirement, but
this is something we often do.

Anyway, I will look more into the WAL-system when I get some time. If
someone know for sure what I'm planning to do will crash and burn, I will
appriciate a heads-up on why.

Fredrik

On Mon, Oct 7, 2019 at 11:42 AM Simon Slavin  wrote:

> On 7 Oct 2019, at 10:34am, Fredrik Larsen  wrote:
>
> > In my head, checkpointing implies copying back all dirty-pages from the
> WAL/COW-log to the main db-file. If we never checkpoint, the writes are
> still completed, but lives in the WAL-file. We will offcourse merge back
> pages to the main db-file, but this would be an offline process we start
> when we want to create a new "base-image" of the db. For this to work, it
> is very important that the main db-file is untouched until we actively want
> to update this file.
>
> Your use of SQLite depends on several obscure facts about how SQLite
> works.  If you ever want to change implementation details you're going to
> need someone who understands SQLite very thoroughly.
>
> Had you considered just making a text file of all the SQL commands
> executed, and running that against your main database file ?  It would take
> less CPU time, less filespace, be easier to debug, and be simpler for
> another programmer to understand.
> ___
> 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] Copy-on-write VFS

[Simon Slavin]

On 7 Oct 2019, at 10:34am, Fredrik Larsen  wrote:

> In my head, checkpointing implies copying back all dirty-pages from the 
> WAL/COW-log to the main db-file. If we never checkpoint, the writes are still 
> completed, but lives in the WAL-file. We will offcourse merge back pages to 
> the main db-file, but this would be an offline process we start when we want 
> to create a new "base-image" of the db. For this to work, it is very 
> important that the main db-file is untouched until we actively want to update 
> this file.

Your use of SQLite depends on several obscure facts about how SQLite works.  If 
you ever want to change implementation details you're going to need someone who 
understands SQLite very thoroughly.

Had you considered just making a text file of all the SQL commands executed, 
and running that against your main database file ?  It would take less CPU 
time, less filespace, be easier to debug, and be simpler for another programmer 
to understand.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copy-on-write VFS

[Fredrik Larsen]

Hi Simon

In my head, checkpointing implies copying back all dirty-pages from the
WAL/COW-log to the main db-file. If we never checkpoint, the writes are
still completed, but lives in the WAL-file. We will offcourse merge back
pages to the main db-file, but this would be an offline process we start
when we want to create a new "base-image" of the db. For this to work, it
is very important that the main db-file is untouched until we actively want
to update this file.

Yes, I'm aware that we are trying to "missuse" sqlite a bit here, but it
may just work :)

Fredrik


On Mon, Oct 7, 2019 at 11:16 AM Simon Slavin  wrote:

> On 7 Oct 2019, at 9:56am, Fredrik Larsen  wrote:
>
> > nowhere does it warn about severe regression with
> > unbounded WAL-size [snip]
>
> There are tons of bad stuff the documentation doesn't warn you about.  You
> might want to read
>
> 
>
> again and get back to us if you have further questions.  There's a reason
> that there's a whole section on avoiding excessively large WAL files.
>
> At your level of sophistication you can probably guess the fallout from
> the things you're doing.  The sort of stuff you're doing is rare and
> obscure.  Almost all users of SQLite pick a journalling mode and let SQLite
> handle everything itself.
>
> > Given that we disable ceckpointing, can we assume that the main
> > database-file will never be modified, and therefor could potentially be
> mounted read-only?
>
> You are correct that the WAL file contains database pages, rather than SQL
> statements.  A recent change to the way WAL files work means that if an
> already-modified page is modified again, the page appears in the WAL file
> only once.  So the size of the WAL file depends more on how much new data
> gets written to the database than the total number of changes.
>
> Presumably you mean you disable /automatic/ checkpointing.  If you disable
> all checkpointing your writes never get completed.  Sooner or later you are
> going to have to let SQLite modify your database file, so no, you can't
> mount it read-only if you want to make changes.
> ___
> 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] Copy-on-write VFS

[Simon Slavin]

On 7 Oct 2019, at 9:56am, Fredrik Larsen  wrote:

> nowhere does it warn about severe regression with
> unbounded WAL-size [snip]

There are tons of bad stuff the documentation doesn't warn you about.  You 
might want to read



again and get back to us if you have further questions.  There's a reason that 
there's a whole section on avoiding excessively large WAL files.

At your level of sophistication you can probably guess the fallout from the 
things you're doing.  The sort of stuff you're doing is rare and obscure.  
Almost all users of SQLite pick a journalling mode and let SQLite handle 
everything itself.

> Given that we disable ceckpointing, can we assume that the main
> database-file will never be modified, and therefor could potentially be 
> mounted read-only?

You are correct that the WAL file contains database pages, rather than SQL 
statements.  A recent change to the way WAL files work means that if an 
already-modified page is modified again, the page appears in the WAL file only 
once.  So the size of the WAL file depends more on how much new data gets 
written to the database than the total number of changes.

Presumably you mean you disable /automatic/ checkpointing.  If you disable all 
checkpointing your writes never get completed.  Sooner or later you are going 
to have to let SQLite modify your database file, so no, you can't mount it 
read-only if you want to make changes.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Copy-on-write VFS

[Fredrik Larsen]

Interesting, I previously just assumed that WAL file stored a log of
sql-like-update-statements since last checkpoint, making a large WAL-file
effectivly unusable if you have any requirements on query-performance.

But re-reading the WAL-documentation, there are references to "pages" being
stored at the end of the WAL-log, and a index containing some mapping. This
smells alot like a COW-implementation, where modified pages/blocks of the
database are copied then modfied and written to a change-file, and using an
index to map old-pages to new-pages. Also, re-reading documentation about
query-performance, nowhere does it warn about severe regression with
unbounded WAL-size, as I assumed, further supporting that WAL is in fact
implemented using COW.

Given that we disable ceckpointing, can we assume that the main
database-file will never be modified, and therefor could potentially be
mounted read-only?

Fredrik

On Fri, Oct 4, 2019 at 11:18 PM Keith Medcalf  wrote:

>
> You still have not explained what you think COW is and of what possible
> use it could be.
>
> If you want a "snapshot + changes" then why not just enable WAL mode and
> disable checkpointing?
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users  On
> >Behalf Of Fredrik Larsen
> >Sent: Friday, 4 October, 2019 14:14
> >To: SQLite mailing list 
> >Subject: Re: [sqlite] Copy-on-write VFS
> >
> >A file-system with COW support would work, but that is a big
> >dependency/constraint to bring into a project, and not always
> >possible/practical. A file based version (snapshot + changes) will be
> >more
> >practical and easier to manage, and also very doable IMHO.
> >
> >Anyway, I was just wondering if anyone else had explored this path. From
> >the feedback so far it seems not.
> >
> >Fredrik
> >
> >On Fri, Oct 4, 2019 at 3:23 PM test user 
> >wrote:
> >
> >> Hello Fredrik,
> >>
> >> Why does it need to be part of a VFS instead of using a file system
> >with
> >> COW like ZFS?
> >>
> >> On Fri, 4 Oct 2019 at 12:18, Fredrik Larsen  wrote:
> >>
> >> > Hi
> >> >
> >> > A copy-on-write IO-path where data is split into static and dynamic
> >parts
> >> > (think snapshots for storage) would be very helpful for our project,
> >.
> >> This
> >> > would simplify backups, testing, moving data around in a multinode
> >> > environment, etc.
> >> >
> >> > Does something like this exist for sqlite? In my head this sounds
> >like an
> >> > relative easy feature to add as IO-stuff is already centralized in
> >the
> >> VFS
> >> > layer. Maybe a new COW-VFS?
> >> >
> >> > Fredrik
> >> > ___
> >> > 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-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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Online backup of in memory database

[Rowan Worth]

On Sun, 6 Oct 2019 at 23:27, Kadirk  wrote:

> How to do online backup of an in memory database (to disk)?
>
> Planning to use in memory database with 10 gb+ data, there are queries
> continuously so stopping application is not an option. Looks like for on
> disk databases it is possible with a non-blocking fashion but I couldn't
> find a way to do it for in memory database. Whenever an update comes in,
> backup process starts over so it won't finish. Any idea how to solve this?
>

Huh, the documentation explicitly points this out:

If another thread or process writes to the source database while this
> function is sleeping, then SQLite detects this and usually restarts the
> backup process when sqlite3_backup_step() is next called. There is one
> exception to this rule: If the source database is not an in-memory
> database, and the write is performed from within the same process as the
> backup operation and uses the same database handle (pDb), then the
> destination database (the one opened using connection pFile) is
> automatically updated along with the source.
>

https://www.sqlite.org/backup.html

Seems like a strange exception, I wonder why it's there?

You could still complete the backup by specifying nPage=-1 to
sqlite3_backup_step -- this requires a read lock for the duration, but the
lock methods appear to be a no-op for in-memory DBs. Presumably holding the
DB's mutex will still prevent other threads from accessing it though.

Another option if you're on linux is to put the DB file in /dev/shm, at
which point it is physically in memory but from sqlite's perspective is a
regular disk file. You'd have to check the performance characteristics
again of course.

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