Re: [sqlite] database is locked

[Hick Gunter]

Use the following code snippet (add error checking, set v_file to the full path 
name of your SQLite db file) to check.

It attempts to take the same locks as SQLite would, but prints the pid of the 
blocking process. It also prints the journal mode.

#include 
#include 
#ifdef AIX64
#include 
#endif
#include 
#include 
#include 

#include "sqliteInt.h"

static const char  *g_mode[] = {
"[0/0]", "[del]", "[WAL]", "[?/?]"
};

charv_buff[256];
int v_mode;

struct flockv_pending;   // = { F_WRLCK, SEEK_SET, PENDING_BYTE  ,  
 1, 0};
struct flockv_reserved;  // = { F_WRLCK, SEEK_SET, RESERVED_BYTE ,  
 1, 0};
struct flockv_shared;// = { F_WRLCK, SEEK_SET, SHARED_FIRST  , 
SHARED_SIZE, 0};
int v_fd= -1;
int v_ret   = -1;

memset( _pending, 0, sizeof( v_pending));
v_pending.l_type= F_WRLCK;
v_pending.l_whence  = SEEK_SET;
v_pending.l_start   = PENDING_BYTE;
v_pending.l_len = 1;
v_pending.l_pid = 0;

memset( _reserved, 0, sizeof( v_reserved));
v_reserved.l_type   = F_WRLCK;
v_reserved.l_whence = SEEK_SET;
v_reserved.l_start  = RESERVED_BYTE;
v_reserved.l_len= 1;
v_reserved.l_pid= 0;

memset( _shared, 0, sizeof( v_shared));
v_shared.l_type = F_WRLCK;
v_shared.l_whence   = SEEK_SET;
v_shared.l_start= SHARED_FIRST;
v_shared.l_len  = SHARED_SIZE;
v_shared.l_pid  = 0;

/* open the file readonly */
v_fd = open(v_file,O_RDONLY);

// read the file header
read(v_fd, v_buff, 100);

// interpret the bytes
switch (v_buff[18]) {
case  0: v_mode = (v_buff[18] == v_buff[19]) ? 0 : 3; break; // empty
case  1: v_mode = (v_buff[18] == v_buff[19]) ? 1 : 3; break; // 
journal_mode=delete
case  2: v_mode = (v_buff[18] == v_buff[19]) ? 2 : 3; break; // 
journal_mode=wal
default: v_mode =  3; break; // invalid
};

/* check for a PENDING lock */
fcntl(v_fd,F_GETLK,_pending);

/* check for a RESERVED lock */
fcntl(v_fd,F_GETLK,_reserved);

/* check for a SHARED/EXCLUSIVE lock */
fcntl(v_fd,F_GETLK,_shared);

/* print in ascending restrictivity */
if (v_pending.l_type == F_RDLCK)
printf("%s File:%s, Process %d PENDING (SHARED)\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_RDLCK)
printf("%s File:%s, Process %d SHARED\n" ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

switch (v_reserved.l_type) {
case F_WRLCK:
case F_RDLCK:
printf("%s File:%s, Process %d RESERVED\n"   ,g_mode[v_mode] 
,v_file, (v_ret = v_reserved.l_pid));
break;
default: break;
}

if (v_pending.l_type == F_WRLCK)
printf("%s File: %s,Process %d PENDING (EXCLUSIVE)\n",g_mode[v_mode] 
,v_file, (v_ret = v_pending .l_pid));

if (v_shared .l_type == F_WRLCK)
printf("%s File %s, Process %d EXCLUSIVE\n"  ,g_mode[v_mode] 
,v_file, (v_ret = v_shared  .l_pid));

if (v_ret == -1)
printf("%s File:%s, \n",g_mode[v_mode] 
,v_file);


-Ursprüngliche Nachricht-
Von: Roman Fleysher [mailto:roman.fleys...@einstein.yu.edu]
Gesendet: Mittwoch, 14. Jänner 2015 18:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] database is locked

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org 
[sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher 
> wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table
> yesterday and I am rather sure that no one on our multi-user system is 
> updating it now.
> The time stamp on the file is from yesterday, showing correct time. I
> need to update a table (I use shell) and I get "database is locked".
> Is there a way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you specifying an 
alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org

Re: [sqlite] Best Practice: Storing Dates

[Chris Keilitz]

Thank you for all the responses to my original post. They were all very
helpful in formulating my position/solution: Unless you deal with financial
services, Facebook, Twitter type systems processing multi-million /
billions of records or high volumes / TPS that would be hampered by
text/string conversion and extraordinary date calculations, I think storing
the timestamp in a string format in UTC time, using the ISO 8601 notation
is the right answer for a portable, flexible, usable timestamps e.g.,
-MM-DDThh:mm:ssZ.



I had some fun reading your responses and digging into some of the details.
Only if you're interested, here is my rationale for recommending
text/string format using ISO representation over using an integer in Unix
time.



SQLite and most RDBMS can handle efficiently storing and converting between
ISO 8601 string and integer Unix/POSIX/Epoch timestamp (date/time)
representations. Converting back and forth, for me, isn’t really a deciding
factor.



The biggest consideration factor for me ended up being storage. Unix time
wins over a string representation hands down from a storage perspective, 2
to 3 times less storage. In order to get around the "2038 bug" for Unix
time, you'll need to use an unsigned 4 byte integer (or larger). At
3:14:07UTC Tuesday 19 January, there will have been 2,147,483,647 or 2^32-1
seconds that past since 1 January 1970 – one second later and you would
roll over to a negative number (I think). In order to represent the
date/time in UTC in a text/string format, you'd need at a minimum of 15
bytes (MMDDhhmmssZ) and a full 20 bytes using the full notation
(-MM-DDThh:mm:ssZ). Since SQLite doesn't support unsigned integers and
based on the feedback from Baruch Burstein, I assume SQLite would store the
integer as a 4 byte integer until it rolled past 2^32-1 and then would
expand it to a 6 byte integer. Nonetheless, from a storage perspective it
is a 4-6 byte integer vs. a 15-20 byte string. That seems like a lot per
record, but it really isn't for most uses. You would need to store almost
75,000 timestamps to equate to 1MB, 1 million records for 13MB, and you'd
have store over 76 million timestamps to get to 1GB. That said, if you were
storing a billion records, the text/string format would cost you an extra
931GB. This leaves me in thinking for the general user, storage, even on
mobile devices, probably even in memory databases, storing the timestamp in
a text/string representation won't really be that big of a deal.



I am sure performance comes into play at high volumes/TPS, particularly the
cost to convert strings to integers for calculations and then back to
strings again. To do a thorough evaluation, I would want to dig into
specific implementations and do my own tests.  I just don’t have the time
and don’t think it will be a factor at my relatively low volumes. If any of
you have dug into the performance aspects, I’d be interested in hearing.



Thanks again,



Chris

On Wed, Jan 14, 2015 at 9:57 AM, Doug Nebeker  wrote:

> Whatever format you choose to store it in, I highly recommend storing the
> UTC time.  It might be a little more work, but:
>
> 1. your program can display the correct local time, even if the
> database/app/user is in/changes to another timezone
> 2. you won't have to deal with seeing two 1:30am on the day that day light
> savings kicks in
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Chris Keilitz
> Sent: Wednesday, January 14, 2015 7:09 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Best Practice: Storing Dates
>
> I'm a new sqlite user and new to this mailing list. I hope this question
> is appropriate.
>
> I am writing an application that needs to track a timestamp - date + time
> down to the seconds. I'd like to store the date/time in a standard,
> efficient, usable, portable format.  I have looked over the sqlite
> date/time functions / data types and the ISO 8601 standard and have landed
> on these two options:
>
> 1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or 2. Storing it as
> an INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
> time) - number of seconds since 01/01/1970
>
> Since sqlite and most RDMS implementations have functions to convert to
> and from both options and using a LONG should allow the date/time to
> function way past 2038, it seems it comes down to how many bytes it takes
> to store the timestamp and how fast are the conversion routines. The
> application I'm writing won't push any performance boundaries and likely
> won't need to overly worry about storage.  I just want to make the right
> call on data type and format and learn something in the process.
>
> Again, I hope this is an appropriate post for this mailing list. If not, I
> apologize.
>
> Thanks!
>
> Chris
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

Re: [sqlite] using a hex integer as input to DateTime

[Peter Aronson]

If you're accessing SQLite from your own program, adding a hextoint function 
would be pretty easy.  Even if you were using sqlite3, defining your own 
extension isn't that hard (and is documented on the SQLite website to some 
extent).  That would allow you to do most of the things you've been asking 
about.  Here's a rough cut at one (untested, and somewhat platform dependent):

/***
*
*  S_hextoint_function -- Convert a hex number string starting with 0x to int.
*
***/
static void S_hextoint_function (sqlite3_context  *ctx,
 int  num_values,
 sqlite3_value**values)
{
  const char *input_string;
  char   *error_string;
  sqlite3_int64  value;
  long long  llvalue;
  intconverted;
  size_t string_length;
  
  /* The integer value of a NULL is NULL. */

  if (sqlite3_value_type (values[0]) == SQLITE_NULL) {
sqlite3_result_null (ctx);
return;
  }

  /* Get the hex string and make sure it starts with 0x. */

  input_string = (const char *)sqlite3_value_text (values[0]);
  if ((const SE_WCHAR *)NULL == input_string) {
sqlite3_result_error_nomem (ctx);
return;
  }

  string_length = strlen (input_string);
  if (strlen < 3 || '0' != input_string[0] ||
  ('x' != input_string[1] && 'X' != input_string[1])) {
error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
input_string);
sqlite3_result_error (ctx,error_string,-1);
sqlite3_free (error_string);
return;
  }

  /* Convert the hex string. */

  converted = sscanf (input_string,"%llx",);
  if (1 != converted)
error_string = sqlite3_mprintf ("'%s' is not a legal hex constant.",
input_string);
sqlite3_result_error (ctx,error_string,-1);
sqlite3_free (error_string);
return;
  }
  value = (sqlite3_int64)llvalue;

  /* Return the integer value. */

  sqlite3_result_int64 (ctx,value);
}

And, if in your own program, you'd just execute 

  sqlite3_create_function (hdbc,
   "hextoint",
   -1,
   SQLITE_ANY,
   globalfunc_info,
   S_hextoint_function,
   NULL,
   NULL);

After connecting, but before executing your SQL.

Peter


On Wednesday, January 14, 2015 4:32 PM, Paul Sanderson 
 wrote:
 

>
>
>Thanks all
>
>I am running the latest version :)
>
>I am just getting back to this and have a related problem
>
>I have a table
>
>create table (base int, hex text)
>
>and I want to create a trigger such that if hex is updated (or a new
>row inserted) with a text string in the form 0x12345abcd this value is
>converted into an integer and copied to base.
>
>I have tried various methods such as
>
>CREATE TRIGGER hex_trig after insert on dates
>when (select hex from dates where hex is not null)
>begin
>update dates set base = cast(new.hex as int);
>end
>
>but so far have drawn a blank
>
>Can this be done?
>
>
>Paul
>www.sandersonforensics.com
>skype: r3scue193
>twitter: @sandersonforens
>Tel +44 (0)1326 572786
>http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>-Forensic Toolkit for SQLite
>http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>processing made easy
>
>
>
>On 8 January 2015 at 00:33, Richard Hipp  wrote:
>> On 1/7/15, Paul Sanderson  wrote:
>>> Evening all
>>>
>>> I dont think this can be done, but would love to be corrected. I have
>>> a column with integer dates stored in hex format so 1234567890 is
>>> stored as 49962d2
>>>
>>> Obviously
>>> DateTime(1234567890, 'unixepoch')
>>>
>>> work OK, but what I would like to do is something like
>>>
>>> DateTime(0x49962d2, 'unixepoch')
>>
>> This should work fine, provided you are using SQLite 3.8.6 or later.
>> Support for hexadecimal literals was added in version 3.8.6.  Release
>> date for 3.8.6 was 2014-08-15, so this is not the version running on
>> your phone.  :-\
>>
>>
>>>
>>> or
>>>
>>> DateTime(HexToInt(0x49962d2), 'unixepoch')
>>>
>>> Is this possible? Not a problem if not, but would be nice.
>>>
>>> Thanks
>>>
>>>
>>> Paul
>>> www.sandersonforensics.com
>>> skype: r3scue193
>>> twitter: @sandersonforens
>>> Tel +44 (0)1326 572786
>>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>>> -Forensic Toolkit for SQLite
>>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>>> processing made easy
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp

Re: [sqlite] using a hex integer as input to DateTime

[Richard Hipp]

On 1/14/15, Paul Sanderson  wrote:
> Thanks all
>
> I am running the latest version :)
>
> I am just getting back to this and have a related problem
>
> I have a table
>
> create table (base int, hex text)
>
> and I want to create a trigger such that if hex is updated (or a new
> row inserted) with a text string in the form 0x12345abcd this value is
> converted into an integer and copied to base.
>
> I have tried various methods such as
>
> CREATE TRIGGER hex_trig after insert on dates
> when (select hex from dates where hex is not null)
> begin
> update dates set base = cast(new.hex as int);
> end
>
> but so far have drawn a blank
>
> Can this be done?

No.  Not like that, anyhow.

>From the documentation (https://www.sqlite.org/lang_expr.html#hexint):
"For backwards compatibility, the "0x" hexadecimal integer notation is
only understood by the SQL language parser, not by the type
conversions routines."


>
>
> Paul
> www.sandersonforensics.com
> skype: r3scue193
> twitter: @sandersonforens
> Tel +44 (0)1326 572786
> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
> -Forensic Toolkit for SQLite
> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
> processing made easy
>
>
>
> On 8 January 2015 at 00:33, Richard Hipp  wrote:
>> On 1/7/15, Paul Sanderson  wrote:
>>> Evening all
>>>
>>> I dont think this can be done, but would love to be corrected. I have
>>> a column with integer dates stored in hex format so 1234567890 is
>>> stored as 49962d2
>>>
>>> Obviously
>>> DateTime(1234567890, 'unixepoch')
>>>
>>> work OK, but what I would like to do is something like
>>>
>>> DateTime(0x49962d2, 'unixepoch')
>>
>> This should work fine, provided you are using SQLite 3.8.6 or later.
>> Support for hexadecimal literals was added in version 3.8.6.  Release
>> date for 3.8.6 was 2014-08-15, so this is not the version running on
>> your phone.  :-\
>>
>>
>>>
>>> or
>>>
>>> DateTime(HexToInt(0x49962d2), 'unixepoch')
>>>
>>> Is this possible? Not a problem if not, but would be nice.
>>>
>>> Thanks
>>>
>>>
>>> Paul
>>> www.sandersonforensics.com
>>> skype: r3scue193
>>> twitter: @sandersonforens
>>> Tel +44 (0)1326 572786
>>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>>> -Forensic Toolkit for SQLite
>>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>>> processing made easy
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] using a hex integer as input to DateTime

[Paul Sanderson]

Thanks all

I am running the latest version :)

I am just getting back to this and have a related problem

I have a table

create table (base int, hex text)

and I want to create a trigger such that if hex is updated (or a new
row inserted) with a text string in the form 0x12345abcd this value is
converted into an integer and copied to base.

I have tried various methods such as

CREATE TRIGGER hex_trig after insert on dates
when (select hex from dates where hex is not null)
begin
update dates set base = cast(new.hex as int);
end

but so far have drawn a blank

Can this be done?


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
processing made easy



On 8 January 2015 at 00:33, Richard Hipp  wrote:
> On 1/7/15, Paul Sanderson  wrote:
>> Evening all
>>
>> I dont think this can be done, but would love to be corrected. I have
>> a column with integer dates stored in hex format so 1234567890 is
>> stored as 49962d2
>>
>> Obviously
>> DateTime(1234567890, 'unixepoch')
>>
>> work OK, but what I would like to do is something like
>>
>> DateTime(0x49962d2, 'unixepoch')
>
> This should work fine, provided you are using SQLite 3.8.6 or later.
> Support for hexadecimal literals was added in version 3.8.6.  Release
> date for 3.8.6 was 2014-08-15, so this is not the version running on
> your phone.  :-\
>
>
>>
>> or
>>
>> DateTime(HexToInt(0x49962d2), 'unixepoch')
>>
>> Is this possible? Not a problem if not, but would be nice.
>>
>> Thanks
>>
>>
>> Paul
>> www.sandersonforensics.com
>> skype: r3scue193
>> twitter: @sandersonforens
>> Tel +44 (0)1326 572786
>> http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
>> -Forensic Toolkit for SQLite
>> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC
>> processing made easy
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Simon Slavin]

On 14 Jan 2015, at 10:40pm, Baruch Burstein  wrote:

> Of course, this is just at the theoretical level. As yo said, your app
> probably wouldn't need to worry about this.

I think a previous poster had it right.  If you need to do lots of maths with 
the timestamps store unix epochs as INTEGERs (or Julian dates if you care only 
about date and not time).  If, on the other hand, your database is read 
directly by a human a lot, store the dates as ISO format in TEXT.

None of these formats have any problem-dates coming up, so we don't expect a 
y2k panic for any of them.

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

Re: [sqlite] Best Practice: Storing Dates

[Baruch Burstein]

On Wed, Jan 14, 2015 at 3:09 PM, Chris Keilitz  wrote:

> Since sqlite and most RDMS implementations have functions to convert to and
> from both options and using a LONG should allow the date/time to function
> way past 2038, it seems it comes down to how many bytes it takes to store
> the timestamp and how fast are the conversion routines. The application I'm
> writing won't push any performance boundaries and likely won't need to
> overly worry about storage.
>

Just for the reference, to answer your size/performance question: The
timestamp will take 4 bytes of data (excluding headers etc.) per entry
(until 2038, after which it will be 6). The text version will be the length
of the string (20 bytes for ISO8601 with second precision).
Naturally, things like comparing and sorting will be faster with the
timestamp then the text version (there is just less data to compare).
Of course, this is just at the theoretical level. As yo said, your app
probably wouldn't need to worry about this.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Adam Devita]

For the data collection systems we use we store only UTC in the database.
The application can translate times to the appropriate time zone and format
for the user as required.  This variable complexity needs to be controlled
into one layer of your program. Since governments, even some city ones,
have the authority to change the time zone or implementation date for their
population, there is a high potential for change. 3rd party time zone rule
libraries can externalize most of the maintenance work without affecting
the core app or the database.

regards,
Adam DeVita

On Wed, Jan 14, 2015 at 12:57 PM, Simon Slavin  wrote:

>
> On 14 Jan 2015, at 5:53pm, Nigel Verity  wrote:
>
> > I generally just use a fixed-length 14-character string to store the
> date and time in MMDDHHMMSS format. It accommodates any time stamp
> across a 10,000 year timespan and also supports simple date/time
> comparisons and sorting.
>
> There is no problem with using that format.  However I would advise you to
> make a note in your documentation, and/or to add comments to your code,
> saying what TimeZone these stamps are in.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Doug Nebeker]

Whatever format you choose to store it in, I highly recommend storing the UTC 
time.  It might be a little more work, but:

1. your program can display the correct local time, even if the 
database/app/user is in/changes to another timezone
2. you won't have to deal with seeing two 1:30am on the day that day light 
savings kicks in


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Chris Keilitz
Sent: Wednesday, January 14, 2015 7:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Best Practice: Storing Dates

I'm a new sqlite user and new to this mailing list. I hope this question is 
appropriate.

I am writing an application that needs to track a timestamp - date + time down 
to the seconds. I'd like to store the date/time in a standard, efficient, 
usable, portable format.  I have looked over the sqlite date/time functions / 
data types and the ISO 8601 standard and have landed on these two options:

1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or 2. Storing it as an 
INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
time) - number of seconds since 01/01/1970

Since sqlite and most RDMS implementations have functions to convert to and 
from both options and using a LONG should allow the date/time to function way 
past 2038, it seems it comes down to how many bytes it takes to store the 
timestamp and how fast are the conversion routines. The application I'm writing 
won't push any performance boundaries and likely won't need to overly worry 
about storage.  I just want to make the right call on data type and format and 
learn something in the process.

Again, I hope this is an appropriate post for this mailing list. If not, I 
apologize.

Thanks!

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

Re: [sqlite] database is locked

[Roman Fleysher]

Thank you!!

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 1:31 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher  wrote:
> Thank you, Richard.
>
> You are correct, I made a typo: we have NFS not NTFS and I know they are
> buggy. I always use the same node on our compute cluster to minimize
> buffering issue. So, are you saying I can not clear the database lock and
> must rebuild the database?

Probably you can just restart the NFS lock manager.  Rebuilding the
database seems a bit extreme.


>
> Roman
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 1:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> SQLite shell version 3.7.2
>> on Linux 2.6.18
>> NTFS
>
> On Linux, SQLite uses posix advisory locks.  All locks are
> automatically released when the process dies (if they haven't been
> already).  If you have stuck locks, that indicates that you either
> have a stuck process or a busted filesystem.  I'm not sure how well
> NTFS plays with Linux.  I thought linux could only read NTFS.
>
> If you are using a network filesystem, all bets are off.  Those are
> usually pretty buggy with respect to locking.
>
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 14, 2015 12:50 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] database is locked
>>
>> On 1/14/15, Roman Fleysher  wrote:
>>> Dear SQLiters,
>>>
>>> There has been a lot of discussion, I remember, on this subject by
>>> others.
>>> Please forgive me for asking this for a millionth time.
>>>
>>> I somehow got my database in a locked state. I updated a table yesterday
>>> and
>>> I am rather sure that no one on our multi-user system is updating it
>>> now.
>>> The time stamp on the file is from yesterday, showing correct time. I
>>> need
>>> to update a table (I use shell) and I get "database is locked". Is there
>>> a
>>> way to figure out what is happening? Clear the lock?
>>
>> What operating system and filesystem are you using?  And are you
>> specifying an alternative VFS for SQLite or using the default?
>>
>>>
>>> Thank you for your help,
>>>
>>> Roman
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> Thank you, Richard.
>
> You are correct, I made a typo: we have NFS not NTFS and I know they are
> buggy. I always use the same node on our compute cluster to minimize
> buffering issue. So, are you saying I can not clear the database lock and
> must rebuild the database?

Probably you can just restart the NFS lock manager.  Rebuilding the
database seems a bit extreme.


>
> Roman
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 1:26 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> SQLite shell version 3.7.2
>> on Linux 2.6.18
>> NTFS
>
> On Linux, SQLite uses posix advisory locks.  All locks are
> automatically released when the process dies (if they haven't been
> already).  If you have stuck locks, that indicates that you either
> have a stuck process or a busted filesystem.  I'm not sure how well
> NTFS plays with Linux.  I thought linux could only read NTFS.
>
> If you are using a network filesystem, all bets are off.  Those are
> usually pretty buggy with respect to locking.
>
>>
>>
>>
>> 
>> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
>> on
>> behalf of Richard Hipp [d...@sqlite.org]
>> Sent: Wednesday, January 14, 2015 12:50 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] database is locked
>>
>> On 1/14/15, Roman Fleysher  wrote:
>>> Dear SQLiters,
>>>
>>> There has been a lot of discussion, I remember, on this subject by
>>> others.
>>> Please forgive me for asking this for a millionth time.
>>>
>>> I somehow got my database in a locked state. I updated a table yesterday
>>> and
>>> I am rather sure that no one on our multi-user system is updating it
>>> now.
>>> The time stamp on the file is from yesterday, showing correct time. I
>>> need
>>> to update a table (I use shell) and I get "database is locked". Is there
>>> a
>>> way to figure out what is happening? Clear the lock?
>>
>> What operating system and filesystem are you using?  And are you
>> specifying an alternative VFS for SQLite or using the default?
>>
>>>
>>> Thank you for your help,
>>>
>>> Roman
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Roman Fleysher]

Thank you, Richard.

You are correct, I made a typo: we have NFS not NTFS and I know they are buggy. 
I always use the same node on our compute cluster to minimize buffering issue. 
So, are you saying I can not clear the database lock and must rebuild the 
database? 

Roman

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 1:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher  wrote:
> SQLite shell version 3.7.2
> on Linux 2.6.18
> NTFS

On Linux, SQLite uses posix advisory locks.  All locks are
automatically released when the process dies (if they haven't been
already).  If you have stuck locks, that indicates that you either
have a stuck process or a busted filesystem.  I'm not sure how well
NTFS plays with Linux.  I thought linux could only read NTFS.

If you are using a network filesystem, all bets are off.  Those are
usually pretty buggy with respect to locking.

>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 12:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> Dear SQLiters,
>>
>> There has been a lot of discussion, I remember, on this subject by
>> others.
>> Please forgive me for asking this for a millionth time.
>>
>> I somehow got my database in a locked state. I updated a table yesterday
>> and
>> I am rather sure that no one on our multi-user system is updating it now.
>> The time stamp on the file is from yesterday, showing correct time. I
>> need
>> to update a table (I use shell) and I get "database is locked". Is there
>> a
>> way to figure out what is happening? Clear the lock?
>
> What operating system and filesystem are you using?  And are you
> specifying an alternative VFS for SQLite or using the default?
>
>>
>> Thank you for your help,
>>
>> Roman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> SQLite shell version 3.7.2
> on Linux 2.6.18
> NTFS

On Linux, SQLite uses posix advisory locks.  All locks are
automatically released when the process dies (if they haven't been
already).  If you have stuck locks, that indicates that you either
have a stuck process or a busted filesystem.  I'm not sure how well
NTFS plays with Linux.  I thought linux could only read NTFS.

If you are using a network filesystem, all bets are off.  Those are
usually pretty buggy with respect to locking.

>
>
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Richard Hipp [d...@sqlite.org]
> Sent: Wednesday, January 14, 2015 12:50 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] database is locked
>
> On 1/14/15, Roman Fleysher  wrote:
>> Dear SQLiters,
>>
>> There has been a lot of discussion, I remember, on this subject by
>> others.
>> Please forgive me for asking this for a millionth time.
>>
>> I somehow got my database in a locked state. I updated a table yesterday
>> and
>> I am rather sure that no one on our multi-user system is updating it now.
>> The time stamp on the file is from yesterday, showing correct time. I
>> need
>> to update a table (I use shell) and I get "database is locked". Is there
>> a
>> way to figure out what is happening? Clear the lock?
>
> What operating system and filesystem are you using?  And are you
> specifying an alternative VFS for SQLite or using the default?
>
>>
>> Thank you for your help,
>>
>> Roman
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Simon Slavin]

On 14 Jan 2015, at 5:53pm, Nigel Verity  wrote:

> I generally just use a fixed-length 14-character string to store the date and 
> time in MMDDHHMMSS format. It accommodates any time stamp across a 10,000 
> year timespan and also supports simple date/time comparisons and sorting.

There is no problem with using that format.  However I would advise you to make 
a note in your documentation, and/or to add comments to your code, saying what 
TimeZone these stamps are in.

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

Re: [sqlite] database is locked

[Simon Slavin]

On 14 Jan 2015, at 5:30pm, Roman Fleysher  
wrote:

> Is there a way to figure out what is happening? Clear the lock?

We would need specifics of your system to answer this absolutely correctly.  
But you can try these things in turn until one of them works:

Unmount the volume the database is on, then mount it again.
Log out, then log in again.
Reboot the computer.

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

Re: [sqlite] database is locked

[Roman Fleysher]

SQLite shell version 3.7.2
on Linux 2.6.18
NTFS




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Richard Hipp [d...@sqlite.org]
Sent: Wednesday, January 14, 2015 12:50 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table yesterday and
> I am rather sure that no one on our multi-user system is updating it now.
> The time stamp on the file is from yesterday, showing correct time. I need
> to update a table (I use shell) and I get "database is locked". Is there a
> way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you
specifying an alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Nigel Verity]

Hi

Interesting discussion on the best way to store date/time fields.

Unix time certainly has advantages for portability but, I agree, it can be a 
pain to convert, depending on which applications and tools you are interfacing 
with.

ISO8601 is fine if you just need a local time stamp, but even that introduces 
unnecessary complexity with the field separators.

I generally just use a fixed-length 14-character string to store the date and 
time in MMDDHHMMSS format. It accommodates any time stamp across a 10,000 
year timespan and also supports simple date/time comparisons and sorting.

Regards

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

Re: [sqlite] database is locked

[Richard Hipp]

On 1/14/15, Roman Fleysher  wrote:
> Dear SQLiters,
>
> There has been a lot of discussion, I remember, on this subject by others.
> Please forgive me for asking this for a millionth time.
>
> I somehow got my database in a locked state. I updated a table yesterday and
> I am rather sure that no one on our multi-user system is updating it now.
> The time stamp on the file is from yesterday, showing correct time. I need
> to update a table (I use shell) and I get "database is locked". Is there a
> way to figure out what is happening? Clear the lock?

What operating system and filesystem are you using?  And are you
specifying an alternative VFS for SQLite or using the default?

>
> Thank you for your help,
>
> Roman
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge WAL log

[Simon Slavin]

On 14 Jan 2015, at 3:36pm, Jan Slodicka  wrote:

> - WAL log size 7.490 GB

Please repeat your tests but as the first command after opening your database 
file issue

PRAGMA journal_size_limit = 100

With this change the WAL file may still grow to 7 GB while that particular 
transaction is being executed but should be reduced in size after the 
transaction is completed.

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

Re: [sqlite] Huge WAL log

[Richard Hipp]

On 1/14/15, Jan Slodicka  wrote:
> Richard Hipp-3 wrote
>>> No other active readers or writers.
>>
>> Are you sure?
>
> Writers for sure.
>
> As far readers are concerned, the things are too complex to make an
> absolute
> statement. (I shall check once more.) However, I can add a few observations
> I made:
>
> WAL file size was about 70 MB (as reported by the OS) until the critical
> table started.

What is your page size?

>
> The OS started to report 7GB after the commit of the critical table
> finished.

Your original post said you inserted two rows for each transaction.
How big are those two rows?

>
> The commit itself took more than 30 min. After the commit the DB size grew
> by several 100MB. What else could explain this except moving data from WAL
> to DB? (I.e. WAL reset.)
>
> Afterwards several other tables were written (total number of records close
> to 1 mil), but the WAL file did not grow anymore.
>
> After the last table finished, the application was responsive nearly
> instantly. (No big data movement between WAL and DB.)
>
> Finally, the application closed without any delay while deleting the WAL
> file. (Apparently the WAL file did not contain large amount of unsaved
> data.)
>
>
>
>> Because the WAL file should reset automatically after a commit...
>
> To make sure that I understand: You mean moving a file pointer, not
> shrinking of the file itself? (AFAIK, WAL file never shrinks.)
>
>
>
> P.S. We still use v3.7.15.2. (Would like to upgrade shortly.)
>
> P.P.S. I have a copy of all SQLite files. If it helped I could try to read
> WAL file manually.
>
>
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p79993.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database is locked

[Roman Fleysher]

Dear SQLiters,

There has been a lot of discussion, I remember, on this subject by others. 
Please forgive me for asking this for a millionth time.

I somehow got my database in a locked state. I updated a table yesterday and I 
am rather sure that no one on our multi-user system is updating it now. The 
time stamp on the file is from yesterday, showing correct time. I need to 
update a table (I use shell) and I get "database is locked". Is there a way to 
figure out what is happening? Clear the lock?

Thank you for your help,

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

Re: [sqlite] Huge WAL log

[Jan Slodicka]

Richard Hipp-3 wrote
>> No other active readers or writers.
> 
> Are you sure?

Writers for sure.

As far readers are concerned, the things are too complex to make an absolute
statement. (I shall check once more.) However, I can add a few observations
I made:

WAL file size was about 70 MB (as reported by the OS) until the critical
table started.

The OS started to report 7GB after the commit of the critical table
finished.

The commit itself took more than 30 min. After the commit the DB size grew
by several 100MB. What else could explain this except moving data from WAL
to DB? (I.e. WAL reset.)

Afterwards several other tables were written (total number of records close
to 1 mil), but the WAL file did not grow anymore.

After the last table finished, the application was responsive nearly
instantly. (No big data movement between WAL and DB.)

Finally, the application closed without any delay while deleting the WAL
file. (Apparently the WAL file did not contain large amount of unsaved
data.)



> Because the WAL file should reset automatically after a commit...

To make sure that I understand: You mean moving a file pointer, not
shrinking of the file itself? (AFAIK, WAL file never shrinks.)



P.S. We still use v3.7.15.2. (Would like to upgrade shortly.)

P.P.S. I have a copy of all SQLite files. If it helped I could try to read
WAL file manually.






--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991p79993.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Huge WAL log

[Richard Hipp]

On 1/14/15, Jan Slodicka  wrote:
> I understand that the WAL log uses less efficient storage rules than the
> real
> database, but this case was a real surprise for me. Here is the brief
> description.
>
> We start from an empty database, create a few tables (each having a few
> indexes), then begin a transaction, do a couple of inserts into the first
> table, commit the transaction and repeat the same with the remaining
> tables.
>
> No other active readers or writers.

Are you sure?  Because the WAL file should reset automatically after a
commit when the size goes above a threshold (1000 pages, by default).
This can fail if you (1) do something to turn off the automatic
checkpoint mechanism or (2) you are holding a read transaction open
that prevents the WAL file from resetting.  Usually the cause of your
problem is (2).  So, please double-check to make sure you are not
accidently starting and leaving a transaction open.  note that a
prepared statement (an sqlite3_stmt object) that has been stepped a
few times but never reset (sqlite3_reset()) or finalized
(sqlite3_finalize()) will hold a read transaction open, because it has
no way of knowing whether or not you might call sqlite3_step() again.
Are you sure you don't have a stray unfinished prepared statement in
your program?

>
> Summary of this process:
> - DB size 1.022 GB
> - WAL log size 7.490 GB
> - SHM file size 57 MB (About 7mil pages, which - page size is 1024 -
> corresponds to the WAL size.)
> - Total no. of records inserted is 5.207 mil.
> - Among the tables there is one that dominates - it takes about 80% of all
> records. The commit of this table took over 30 min.
> - The test was done on a rather old W7 notebook. Memory consumption
> approached 1 GB (as opposed to the normal state ~100 MB).
>
> I understand that we have to modify above algorithm by cutting the
> transaction into smaller pieces. The official SQLite documentation only
> says
> "WAL does not work well for very large transactions. For transactions
> larger
> than about 100 megabytes...".
>
> Could somebody quantify this statement? While the official documentation
> mentions the size (megabytes), my gut feeling is that it has more to do
> with
> the record count. (In our case large records are rather exceptional.)
>
> Do table indexes play an important role? Wouldn't be better to create them
> after all inserts are done?
>
>
>
>
>
>
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Huge WAL log

[Jan Slodicka]

I understand that the WAL log uses less efficient storage rules than the real
database, but this case was a real surprise for me. Here is the brief
description.

We start from an empty database, create a few tables (each having a few
indexes), then begin a transaction, do a couple of inserts into the first
table, commit the transaction and repeat the same with the remaining tables.

No other active readers or writers.

Summary of this process:
- DB size 1.022 GB
- WAL log size 7.490 GB
- SHM file size 57 MB (About 7mil pages, which - page size is 1024 -
corresponds to the WAL size.)
- Total no. of records inserted is 5.207 mil.
- Among the tables there is one that dominates - it takes about 80% of all
records. The commit of this table took over 30 min.
- The test was done on a rather old W7 notebook. Memory consumption
approached 1 GB (as opposed to the normal state ~100 MB).

I understand that we have to modify above algorithm by cutting the
transaction into smaller pieces. The official SQLite documentation only says
"WAL does not work well for very large transactions. For transactions larger
than about 100 megabytes...".

Could somebody quantify this statement? While the official documentation
mentions the size (megabytes), my gut feeling is that it has more to do with
the record count. (In our case large records are rather exceptional.)

Do table indexes play an important role? Wouldn't be better to create them
after all inserts are done?










--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Huge-WAL-log-tp79991.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Richard Hipp]

On 1/14/15, Stephan Beal  wrote:
> On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz  wrote:
>
>> Since sqlite and most RDMS implementations have functions to convert to
>> and
>> from both options and using a LONG should allow the date/time to function
>> way past 2038,
>
>
> In my experience, having the timestamp in Unix Epoch gives you something
> which can be easily converted by a wide variety of tools, and simplifies
> calculation of time deltas (provided you don't need to account for
> timezones, locale-specific summer/winter time changes, and similar
> absurdities). That said, for humans Unix timestamps are basically just a
> pain in the butt. If your data are there for the software, as opposed to
> the humans, i personally find Unix Epoch simpler to work with. If the data
> are strictly for display/reading by humans, without much app logic tied to
> them, ISO8601 is my preferred form (-MM-DD HH:ii:ss...).
>

SQLite support "unix time" (seconds since 1970) and ISO8601 and also
Julian Day number - the number of days (including fractional days)
since noon in Greenwich on November 24, 4714 B.C.  Use whichever
format seems appropriate.  Note that all three formats sort in time
order.  The date and time functions in SQLite will easily convert
between all three formats.

Please familiarize yourself with ISO8601.  Consider using it yourself
when communicating dates, instead of YY-MM-DD or MM/DD/ or various
other formats which are potentially ambiguous and which often do not
sort in time order.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best Practice: Storing Dates

[Stephan Beal]

On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz  wrote:

> Since sqlite and most RDMS implementations have functions to convert to and
> from both options and using a LONG should allow the date/time to function
> way past 2038,


In my experience, having the timestamp in Unix Epoch gives you something
which can be easily converted by a wide variety of tools, and simplifies
calculation of time deltas (provided you don't need to account for
timezones, locale-specific summer/winter time changes, and similar
absurdities). That said, for humans Unix timestamps are basically just a
pain in the butt. If your data are there for the software, as opposed to
the humans, i personally find Unix Epoch simpler to work with. If the data
are strictly for display/reading by humans, without much app logic tied to
them, ISO8601 is my preferred form (-MM-DD HH:ii:ss...).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best Practice: Storing Dates

[Chris Keilitz]

I'm a new sqlite user and new to this mailing list. I hope this question is
appropriate.

I am writing an application that needs to track a timestamp - date + time
down to the seconds. I'd like to store the date/time in a standard,
efficient, usable, portable format.  I have looked over the sqlite
date/time functions / data types and the ISO 8601 standard and have landed
on these two options:

1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or
2. Storing it as an INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch
time) - number of seconds since 01/01/1970

Since sqlite and most RDMS implementations have functions to convert to and
from both options and using a LONG should allow the date/time to function
way past 2038, it seems it comes down to how many bytes it takes to store
the timestamp and how fast are the conversion routines. The application I'm
writing won't push any performance boundaries and likely won't need to
overly worry about storage.  I just want to make the right call on data
type and format and learn something in the process.

Again, I hope this is an appropriate post for this mailing list. If not, I
apologize.

Thanks!

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

Re: [sqlite] .NET System.Data.SQLite, how trap SQLITE_BUSY_SNAPSHOT (WAL) and retry

[Simon Slavin]

On 13 Jan 2015, at 10:53pm, Andreas Hofmann  wrote:

> Ideally, SQLite should retry itself, but I am not sure if this is supported.





SQLite supports it just fine.  The question is whether System.Data.SQLite 
supports it.

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

[sqlite] .NET System.Data.SQLite, how trap SQLITE_BUSY_SNAPSHOT (WAL) and retry

[Andreas Hofmann]

Hi,

 

I am using WAL. If I use a new db connection for every write (as usually
suggested practice) and connection pooling is enabled, my writes are quite a
bit faster. Good so far.  However, I also see some db locks occurring,
specifically:

 

(517) SQLITE_BUSY_SNAPSHOT

 

For some reason, the .net wrapper only traces that to the console, but it
does not throw. 

 

1)   How can I make the sqlite .NET wrapper throw an exception so I can deal
with it.

2)   Retry the same write again.

 

Ideally, SQLite should retry itself, but I am not sure if this is supported.

 

Thanks

Andy

 

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

Re: [sqlite] help with query

[snowbiwan]

Maybe something like this would work for you:

SELECT *
  FROM table
 WHERE data1 IN (SELECT data1
   FROM table
   GROUP BY data1
 HAVING count(*)>=3);

~snowbiwan



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/help-with-query-tp79978p79979.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users