Re: [sqlite] Time taken to perform checkpoint operation and does it lock database during this operation?

2014-02-20 Thread Simon Slavin 

On 21 Feb 2014, at 12:45am, Richard Hipp  wrote:

> Long-running or overlapping readers can prevent the checkpoint from
> occurring.  The checkpoint will be retried again and again, but if there is
> always a read transaction open on a transaction other than the most recent
> transaction, the the checkpoint will never have an opportunity to run to
> completion and reset the WAL file.

Suppose this happens and another process tried to write.  Will the write be 
held up until the checkpoint can complete, or will the expected WAL size be 
exceeded ?

Reading blocks checkpointing blocks writing ?

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

Re: [sqlite] Time taken to perform checkpoint operation and does it lock database during this operation?

2014-02-20 Thread Richard Hipp 
On Thu, Feb 20, 2014 at 7:41 PM, veeresh kumar  wrote:

>  Haven't measured the time, but I have seen a pause before commit
> happens. As stated during my earlier discussion, my service cannot pause
> for more than 10 sec as it would be result in time out for the clients that
> are connected to this service.
>
> What my understanding towards the checkpoint is taking my below settings
> as example, WAL size would never grow beyond 1MB because check point occurs
> after WAL file size reaches 1 MB. Please correct me if I am wrong.
>
>

Long-running or overlapping readers can prevent the checkpoint from
occurring.  The checkpoint will be retried again and again, but if there is
always a read transaction open on a transaction other than the most recent
transaction, the the checkpoint will never have an opportunity to run to
completion and reset the WAL file.

-- 
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] Time taken to perform checkpoint operation and does it lock database during this operation?

2014-02-20 Thread veeresh kumar 
Haven't measured the time, but I have seen a pause before
commit happens. As stated during my earlier discussion, my service cannot pause
for more than 10 sec as it would be result in time out for the clients that are
connected to this service. 
 
What my understanding towards the checkpoint is taking my
below settings as example, WAL size would never grow beyond 1MB because check
point occurs after WAL file size reaches 1 MB. Please correct me if I am wrong. 
 
Settings:
PRAGMA synchronous=NORMAL;",with default auto check
point and page size = 1024 bytes.



On Thursday, 20 February 2014 4:21 PM, Richard Hipp  wrote:
 





On Thu, Feb 20, 2014 at 7:08 PM, veeresh kumar  wrote:

Hi,
> I am using "PRAGMA
>journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",with
>default auto check point and page size = 1024 bytes.  Since checkpoint
>occurs automatically after every 1 MB, how much time it checkpoint operation
>would take to complete as the database size grows large (range 1GB -  50
>GB). I understand that it depends on the hardrive, but on a very good 
>configuration,
>will this operation ever exceeds 10 sec?Also during this operation, does the
>database gets locked?
>

The time needed for a checkpoint depends much more on the size of the WAL file 
than on the size of the database.  For a 1MB WAL file on modern hardware, I 
would think a checkpoint would require perhaps 50 to 100 milliseconds.  Have 
you done measurements to see how long it takes on your system?


The database cannot be written while a checkpoint is underway.  But reads can 
run concurrently with a checkpoint.

-- 
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] Time taken to perform checkpoint operation and does it lock database during this operation?

2014-02-20 Thread Richard Hipp 
On Thu, Feb 20, 2014 at 7:08 PM, veeresh kumar  wrote:

> Hi,
>  I am using "PRAGMA
> journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",with
> default auto check point and page size = 1024 bytes.  Since checkpoint
> occurs automatically after every 1 MB, how much time it checkpoint
> operation
> would take to complete as the database size grows large (range 1GB -  50
> GB). I understand that it depends on the hardrive, but on a very good
> configuration,
> will this operation ever exceeds 10 sec?Also during this operation, does
> the
> database gets locked?
>

The time needed for a checkpoint depends much more on the size of the WAL
file than on the size of the database.  For a 1MB WAL file on modern
hardware, I would think a checkpoint would require perhaps 50 to 100
milliseconds.  Have you done measurements to see how long it takes on your
system?

The database cannot be written while a checkpoint is underway.  But reads
can run concurrently with a checkpoint.

-- 
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] Time taken to perform checkpoint operation and does it lock database during this operation?

2014-02-20 Thread veeresh kumar 
Hi,
 I am using "PRAGMA
journal_mode=WAL;" with "PRAGMA synchronous=NORMAL;",with
default auto check point and page size = 1024 bytes.  Since checkpoint
occurs automatically after every 1 MB, how much time it checkpoint operation
would take to complete as the database size grows large (range 1GB -  50
GB). I understand that it depends on the hardrive, but on a very good 
configuration,
will this operation ever exceeds 10 sec?Also during this operation, does the
database gets locked?
Thank you-Veeresh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Still trying to track down loadable extensions

2014-02-20 Thread Nico Williams 
On Thu, Feb 20, 2014 at 5:19 PM, Peter Haworth  wrote:
> I know nothing about git so can someone please point me in the right
> direction to find these loadable extensions.

SQLite3 is version controlled with Fossil anyways, not git...

Besides the ones that SQLite3 comes with (see D.R. Hipp's responses)

The great search engines find lots of third-party SQLite3 extensions.
Here's a few results, barely skimmed by myself, in no particular
order, nor any endorsement from me:

https://github.com/slightfoot/sqlite3-extensions
https://github.com/salviati/sqlite3-lz4
https://github.com/ralight/sqlite3-pcre
http://sqlite.mobigroup.ru/wiki?name=extensions
http://sqlite.mobigroup.ru/wiki?name=utils
https://github.com/djodjo/sqlite3ext_parse_json
https://github.com/fnoyanisi/sqlite3_capi_extensions
https://github.com/mrwilson/squib
https://github.com/evsukov89/SQLiteFuzzySearch
https://sites.google.com/site/lserinol/sqlitecompress
ftp://ftp.freebsd.org/pub/FreeBSD/ports/local-distfiles/glarkin/extension-functions-1.0.c
http://sqlcipher.net/design/
http://sourceforge.net/projects/sqlite-undo/
http://schplurtz.free.fr/wiki/schplurtziel/sqlite3-ipv4-ext
https://bitbucket.org/luciad/libgpkg
https://www.linux.com/news/software/developer/8010-libferris-and-sqlite-a-powerful-combination-part-2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Still trying to track down loadable extensions

2014-02-20 Thread Richard Hipp 
On Thu, Feb 20, 2014 at 6:19 PM, Peter Haworth  wrote:

> I'm still hunting for loadable extensions.
>

In the ext/ folder, and especially in ext/misc/

http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk

-- 
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] Still trying to track down loadable extensions

2014-02-20 Thread Peter Haworth 
I'm still hunting for loadable extensions.  The SQLite web site makes
reference to extensions being part of the source code in the contrib folder
but when I browser around there, I can only see 2 files dlmalloc and sqlcon.

I know nothing about git so can someone please point me in the right
direction to find these loadable extensions.

Thanks,

Pete
lcSQL Software 
Home of lcStackBrowser  and
SQLiteAdmin 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculation of a fraction stored in a text column

2014-02-20 Thread RSmith 

Ensure you store the string representation of the reals (floats w/e) of precise 
numerical format and length, such that:
0.3, 12 and 1.456 all look alike and sorts correct ex:

"000.30"   and
"001.456000"   and
"012.00"   etc.

or whatever similar format you may choose as Simon (I think) suggested so that you will have those values correctly 
sorted/grouped/distinct-ed in any string-based SQL function. A further enhancement I like to do in such cases is add some alpha char 
in front, like:

"F01.456000"   and
"F12.00"   etc.
so that any output I do create, which invariably ends up being copied to excel or calc or such, does not get confused with actual 
numerals and stripped of leading zeroes etc. It's much easier to apply a formula to make that into numerals should the need arise, 
than to avoid it being done automatically sans the leading alpha char.




On 2014/02/20 20:50, Patrick Proniewski wrote:

Donald,

On 20 févr. 2014, at 15:16, Donald Griggs wrote:


Am I right that fractional exposures will always have one as the numerator?   I.e., you might 
record an exposure as "1.5" seconds, but never as "3/2" seconds?   If so, then 
that might simplify things.

Yes, no 3/2, only 1/x and regular REALs.



The example below creates a column named "canon" to hold the canonical exposure 
string value for sorting and grouping.
It assumes any non-decimal fractions will begin with the string "1/"

Thank you for this example, the idea of storing the computed number into the database is 
very good and made me rethink the initial database feeding. I'm using exiftool to script 
EXIF reading from my files. exiftool has a very nice option that allows the reading of 
raw data. Exposure Time, displayed in "raw" is the REAL equivalent to my 
strings: 1/200 is read as 0.005. Very handy.
I'm going to redesign my database in order to include raw data aside 
human-readable data when I need it.

thanks,
Patrick
___
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] calculation of a fraction stored in a text column

2014-02-20 Thread Patrick Proniewski 
Simon,

> Date: Thu, 20 Feb 2014 14:04:59 +
> From: Simon Slavin 
> 
> On 20 Feb 2014, at 1:56pm, Patrick Proniewski  wrote:
> 
>> Thanks for you reply. In fact I would like the script to remain portable. 
>> I'm running all this on Mac OS X 10.6.8 (sqlite3 3.6.12) but I plan to share 
>> it and use it on FreeBSD too. And developing a extension is probably out of 
>> my reach :)
> 
> I don't think the thing you want to do can be done easily within SQLite.  I 
> would expect to see it done in whatever programming language you're using.
> 
> One approach would be to save two columns when you extract from EXIF.  One 
> has exactly the text from the EXIF column.  Another would be to save the 
> factor as a REAL number.  Do some string processing to get the bit before the 
> '/' and the bit after it, and divide one by the other.
> 
> You might be able to do the same calculation inside SQLite but it'll be 
> horrid to look at.  Something like
> 
> SELECT 
> expTime,substr(expTime,1,instr(expTime,'/'))/substr(expTime,instr(expTime,'/')+1)
>  AS etAsReal FROM photos


I'm going to rework my database to include proper "already calculated" columns, 
but using bash to do the math would have been a little bit unpleasant (not 
enough streamlined for my liking). Fortunately exiftool has an option to 
extract raw value for EXIF tags, and I've found a way to read them exactly as I 
need, "one-pot".

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

Re: [sqlite] calculation of a fraction stored in a text column

2014-02-20 Thread Patrick Proniewski 
Donald,

On 20 févr. 2014, at 15:16, Donald Griggs wrote:

> Am I right that fractional exposures will always have one as the numerator?   
> I.e., you might record an exposure as "1.5" seconds, but never as "3/2" 
> seconds?   If so, then that might simplify things.

Yes, no 3/2, only 1/x and regular REALs.


> The example below creates a column named "canon" to hold the canonical 
> exposure string value for sorting and grouping.
> It assumes any non-decimal fractions will begin with the string "1/"

Thank you for this example, the idea of storing the computed number into the 
database is very good and made me rethink the initial database feeding. I'm 
using exiftool to script EXIF reading from my files. exiftool has a very nice 
option that allows the reading of raw data. Exposure Time, displayed in "raw" 
is the REAL equivalent to my strings: 1/200 is read as 0.005. Very handy.
I'm going to redesign my database in order to include raw data aside 
human-readable data when I need it.

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

[sqlite] Syntax diagram missing in sqlite3 docs

2014-02-20 Thread Zsbán Ambrus 
Hi,

The page "http://sqlite.org/lang_transaction.html"; should show the
syntax diagrams for commit-stmt and rollback-stmt.  Similarly, the
page "http://sqlite.org/lang_savepoint.html"; should show the diagram
for rollback-stmt.  These missing diagrams show up correctly in
"http://sqlite.org/draft/syntaxdiagrams.html";.

Could you please include the missing diagrams?  Thanks,

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

Re: [sqlite] Virtual Memory Size used looks too large

2014-02-20 Thread Clemens Ladisch 
Woody Wu wrote:
> What I used is sqlite 3.7.5, but the doc says that memory-mapped io
> only started from 3.7.17.
>
> So, what are used for the huge VSZ in my sqlite application?

Some other data.  The page cache doesn't get that big by default,
so probably some temporary data.

> I am afraid, when the VSZ of a process goes too large, it could
> trigger some kind of system bug.

The OOM killer?

> I've been always bothered with a sqlite "disk image malformed"
> issue after my application run a long time.




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

Re: [sqlite] How to Troubleshoot Disk I/O Error

2014-02-20 Thread Dan Kennedy 

On 02/20/2014 09:29 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote:

Our client  got the following print-out. First hint of failure is the line "TRUNCATE 
48 32768 failed". Any insights into what the root cause might be ?


The operation that is failing is (probaby) an attempt to
use SetEndOfFile() to extend the size of the *-shm file to
32KB (from 0KB). I have no idea why it is failing.

What version of SQLite is this? Newer versions provide more
detail for debugging, particularly if you also turn on
sqlite3_log() logging:

  http://sqlite.org/c3ref/log.html

Dan.




OPEN 40 D:\blp\wintrv\smartclient\applications\appinfo.db 0xc000 ok
READ 40 lock=0
LOCK 40 1 was 0(0)
READ 40 lock=1
OPEN 44 D:\blp\wintrv\smartclient\applications\appinfo.db-wal 0xc000 ok
OPEN 48 D:\blp\wintrv\smartclient\applications\appinfo.db-shm 0xc000 ok
SHM-LOCK 48 ok LockFileEx 0x
TRUNCATE 48 0
TRUNCATE 48 0 ok
SHM-LOCK 48 ok UnlockFileEx 0x
SHM-LOCK 48 ok LockFileEx 0x
SHM-LOCK 48 ok LockFileEx 0x
SHM-LOCK shmid-0, pid-7308 got 000,001 ok
TRUNCATE 48 32768
TRUNCATE 48 32768 failed
SHM-LOCK 48 ok UnlockFileEx 0x
SHM-LOCK shmid-0, pid-7308 got 000,000 ok
Error occurred-> SQLITE_IOERR[10]: disk I/O error
LOCK 40 4 was 1(0)
unreadlock = 1
TRUNCATE 48 32768
TRUNCATE 48 32768 failed
CLOSE 48
CLOSE 48 ok
CLOSE 44
CLOSE 44 ok
UNLOCK 40 to 0 was 4(0)
CLOSE 40
CLOSE 40 ok
OPEN 44 D:\blp\wintrv\smartclient\applications\appinfo.backup.db 0xc000 ok
READ 44 lock=0
LOCK 44 1 was 0(0)
DELETE "D:\blp\wintrv\smartclient\applications\appinfo.backup.db-wal" failed
UNLOCK 44 to 0 was 1(0)
LOCK 44 1 was 0(0)
DELETE "D:\blp\wintrv\smartclient\applications\appinfo.backup.db-wal" failed
UNLOCK 44 to 0 was 1(0)
UNLOCK 44 to 0 was 0(0)
CLOSE 44
CLOSE 44 ok

Thanks,

Akintoye


- Original Message -
From: Akintoye Olorode (BLOOMBERG/ 731 LEXIN)
To: sqlite-users@sqlite.org
At: Jan 30 2014 10:52:17


Thanks, will try this suggestions and post results.

- Akintoye

- Original Message -
From: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
At: Jan 30 2014 10:44:45


On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote:

Hi,

We have one client that recently upgraded from WinXP SP3 to Win7 sp1 (Japanese 
Language). With new OS, attempts to access read from our sqlite database fails 
with disk I/O error.

Can anyone help with  suggestions on how to troubleshoot disk I/O error ? 
Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional 
information.

We have not been able to reproduce the error in-house.The user has HP ENVY 
laptop with hybrid drive. User has tried :

1. Install latest file system drivers
2. reformat the hard-drive & reinstall Win7 OS


all to no avail. Client does not report problems with any other applications.

Immediately after the error occurs, what value is returned by
the sqlite3_extended_errcode() function? Often the extended error
code identifies the specific IO operation that failed, which can
shed light on the problem.

Another thing to do is to try compiling with the following defines:

-DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1

This causes the Windows VFS module to print various messages to
standard output that should help to figure out what is happening.

Dan.


___
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


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

Re: [sqlite] Problem with .mode line

2014-02-20 Thread Clemens Ladisch 
pelek wrote:
> CREATE TABLE asd (
> [blah] INTEGER NULL,
> [blah2] INTEGER NULL
> );
>
> .mode line
> .out file.txt
> .schema asd
>
> Result is:
> CREATE TABLE asd ([blah] INTEGER NULL,[blah2] INTEGER NULL);

Works for me.  (In an editor that understands Unix line endings).

I don't know why the shell opens the output file in binary mode.


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

Re: [sqlite] How to Troubleshoot Disk I/O Error

2014-02-20 Thread Simon Slavin 

On 20 Feb 2014, at 2:29pm, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) 
 wrote:

> Our client  got the following print-out. First hint of failure is the line 
> "TRUNCATE 48 32768 failed". Any insights into what the root cause might be ?

Have you checked the media for faults ?  In other words, I'm suggesting you 
check for a hardware failure.  Try copying the whole database file from it 
using conventional copy commands.

Also check for corruption of the database using "PRAGMA integrity_check"



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

Re: [sqlite] Lack of "decimal" support

2014-02-20 Thread indraneel_in 
I too faced a similar situation..

the following thing worked

set the Column type to Double
and insert rows using Double data type for java..





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Lack-of-decimal-support-tp57530p74046.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

[sqlite] Problem with .mode line

2014-02-20 Thread pelek 
Hello, 
I am using sqlite3's shell command program to update my DB. Now something
really funny is happening. I am using code to create table:

CREATE TABLE asd (
[blah] INTEGER NULL,
[blah2] INTEGER NULL
);

Then I am doing:
.mode line
.out stdout
.schema asd

Result is:
CREATE TABLE asd (
[blah] INTEGER NULL,
[blah2] INTEGER NULL
);

but when I do: 
.mode line
.out file.txt
.schema asd

Result is:
CREATE TABLE asd ([blah] INTEGER NULL,[blah2] INTEGER NULL);

I am really depressed because I cannot pass such stupid issue for almost 2
weeks !!
Looking forward for any help! thank you !




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Problem-with-mode-line-tp74045.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] How to Troubleshoot Disk I/O Error

2014-02-20 Thread Akintoye Olorode (BLOOMBERG/ 731 LEXIN) 
Our client  got the following print-out. First hint of failure is the line 
"TRUNCATE 48 32768 failed". Any insights into what the root cause might be ?

OPEN 40 D:\blp\wintrv\smartclient\applications\appinfo.db 0xc000 ok
READ 40 lock=0
LOCK 40 1 was 0(0)
READ 40 lock=1
OPEN 44 D:\blp\wintrv\smartclient\applications\appinfo.db-wal 0xc000 ok
OPEN 48 D:\blp\wintrv\smartclient\applications\appinfo.db-shm 0xc000 ok
SHM-LOCK 48 ok LockFileEx 0x
TRUNCATE 48 0
TRUNCATE 48 0 ok
SHM-LOCK 48 ok UnlockFileEx 0x
SHM-LOCK 48 ok LockFileEx 0x
SHM-LOCK 48 ok LockFileEx 0x
SHM-LOCK shmid-0, pid-7308 got 000,001 ok
TRUNCATE 48 32768
TRUNCATE 48 32768 failed
SHM-LOCK 48 ok UnlockFileEx 0x
SHM-LOCK shmid-0, pid-7308 got 000,000 ok
Error occurred-> SQLITE_IOERR[10]: disk I/O error
LOCK 40 4 was 1(0)
unreadlock = 1
TRUNCATE 48 32768
TRUNCATE 48 32768 failed
CLOSE 48
CLOSE 48 ok
CLOSE 44
CLOSE 44 ok
UNLOCK 40 to 0 was 4(0)
CLOSE 40
CLOSE 40 ok
OPEN 44 D:\blp\wintrv\smartclient\applications\appinfo.backup.db 0xc000 ok
READ 44 lock=0
LOCK 44 1 was 0(0)
DELETE "D:\blp\wintrv\smartclient\applications\appinfo.backup.db-wal" failed
UNLOCK 44 to 0 was 1(0)
LOCK 44 1 was 0(0)
DELETE "D:\blp\wintrv\smartclient\applications\appinfo.backup.db-wal" failed
UNLOCK 44 to 0 was 1(0)
UNLOCK 44 to 0 was 0(0)
CLOSE 44
CLOSE 44 ok

Thanks,

Akintoye


- Original Message -
From: Akintoye Olorode (BLOOMBERG/ 731 LEXIN)
To: sqlite-users@sqlite.org
At: Jan 30 2014 10:52:17


Thanks, will try this suggestions and post results.

- Akintoye

- Original Message -
From: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org
At: Jan 30 2014 10:44:45


On 01/30/2014 10:19 PM, Akintoye Olorode (BLOOMBERG/ 731 LEXIN) wrote:
> Hi,
>
> We have one client that recently upgraded from WinXP SP3 to Win7 sp1 
> (Japanese Language). With new OS, attempts to access read from our sqlite 
> database fails with disk I/O error.
>
> Can anyone help with  suggestions on how to troubleshoot disk I/O error ? 
> Adding call to sqlite3_config(SQLITE_CONFIG_LOG, ...) produces no additional 
> information.
>
> We have not been able to reproduce the error in-house.The user has HP ENVY 
> laptop with hybrid drive. User has tried :
>
> 1. Install latest file system drivers
> 2. reformat the hard-drive & reinstall Win7 OS
>
>
> all to no avail. Client does not report problems with any other applications.

Immediately after the error occurs, what value is returned by
the sqlite3_extended_errcode() function? Often the extended error
code identifies the specific IO operation that failed, which can
shed light on the problem.

Another thing to do is to try compiling with the following defines:

   -DSQLITE_DEBUG=1 -DSQLITE_DEBUG_OS_TRACE=1

This causes the Windows VFS module to print various messages to
standard output that should help to figure out what is happening.

Dan.


___
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] calculation of a fraction stored in a text column

2014-02-20 Thread Donald Griggs 
Hi Patrick,

Am I right that fractional exposures will always have one as the numerator?
  I.e., you might record an exposure as "1.5" seconds, but never as "3/2"
seconds?   If so, then that might simplify things.

The example below creates a column named "canon" to hold the canonical
exposure string value for sorting and grouping.
It assumes any non-decimal fractions will begin with the string "1/"


CREATE TABLE expo (str TEXT, canon TEXT);
INSERT INTO expo(str) VALUES ('1/30'), ('1/500'), ('1/6000'), ('.5'), ('6');


UPDATE expo
 SET canon =
 CASE WHEN substr(str,1,2) == '1/'
   THEN printf( '%014.8f',  (1.0 / substr(str,3)) )
 ELSE
   printf( '%014.8f', str)
 END;

.mode tabs
SELECT * FROM expo;

1/300.0333
1/500   0.0020
1/6000  0.00016667
.5  0.5000
6   6.

Note that I used a newer sqlite version which includes the handy printf()
function.

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

Re: [sqlite] calculation of a fraction stored in a text column

2014-02-20 Thread Simon Slavin 

On 20 Feb 2014, at 1:56pm, Patrick Proniewski  wrote:

> Thanks for you reply. In fact I would like the script to remain portable. I'm 
> running all this on Mac OS X 10.6.8 (sqlite3 3.6.12) but I plan to share it 
> and use it on FreeBSD too. And developing a extension is probably out of my 
> reach :)

I don't think the thing you want to do can be done easily within SQLite.  I 
would expect to see it done in whatever programming language you're using.

One approach would be to save two columns when you extract from EXIF.  One has 
exactly the text from the EXIF column.  Another would be to save the factor as 
a REAL number.  Do some string processing to get the bit before the '/' and the 
bit after it, and divide one by the other.

You might be able to do the same calculation inside SQLite but it'll be horrid 
to look at.  Something like

SELECT 
expTime,substr(expTime,1,instr(expTime,'/'))/substr(expTime,instr(expTime,'/')+1)
 AS etAsReal FROM photos

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

Re: [sqlite] calculation of a fraction stored in a text column

2014-02-20 Thread Patrick Proniewski 
hi Kevin,

On 20 févr. 2014, at 14:11, Kevin Martin wrote:

> 
> On 20 Feb 2014, at 12:54, Patrick Proniewski  wrote:
> 
>> My problem is that my database holds too many different values for 
>> ExposureTime, so the resulting plot is unreadable. I want to be able to 
>> "bin" those values to create a proper histogram. It's not possible to "bin" 
>> string values, because SQLite has no idea that "1/60" has nothing to do near 
>> "1/6000".
> 
> Are you able to use an extension? A custom collation on the ExposureTime 
> column seems pretty simple (although not thought about it in detail). You 
> should then be able to bin the values as they are.


Thanks for you reply. In fact I would like the script to remain portable. I'm 
running all this on Mac OS X 10.6.8 (sqlite3 3.6.12) but I plan to share it and 
use it on FreeBSD too. And developing a extension is probably out of my reach :)

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

[sqlite] Wrong filename handling in sqlite3_load_extension() for Cygwin

2014-02-20 Thread Jan Nijtmans 
The function sqlite3_load_extension() on Cygwin expects a
win32 path, even though it is compiled for Cygwin.
How to reproduce? First create some extension, e.g.:
gcc -shared -o wholenumber.dll ext/misc/wholenumber.c

Then compile the following little program:
== main.c 
#include 
#include 
void main(int argc, char **argv){
  sqlite3 *db;
  int rc;
  char *zErrMsg = 0;
  printf("loading extension: %s\n", argv[1]);
  sqlite3_initialize();
  sqlite3_open("foo.db", &db);
  sqlite3_enable_load_extension(db, 1);
  rc = sqlite3_load_extension(db, argv[1], "sqlite3_wholenumber_init",
&zErrMsg);
  if( rc!=SQLITE_OK ){
fprintf(stderr, "Error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
rc = 1;
  }
}


gcc main.c sqlite3.c
(This produces an executable "a.exe")

Then try:
$ ./a.exe wholenumber.dll
loading extension: wholenumber.dll
This works, but:
$ ./a.exe $PWD/wholenumber.dll
loading extension: /home/nijtmaj/wholenumber.dll
Error: The specified module could not be found.
Hey, this is exactly where the dll is.
$ ./a.exe `cygpath -w /home/nijtmaj/`wholenumber.dll
loading extension: C:\Localdata\cygwin\home\nijtmaj\wholenumber.dll
When using win32 path, it works! That's not correct!

Suggested patch which fixes this is below. Note
that adding 17 lines to the function
winConvertFromUtf8Filename() means that 38 lines
can be removed from the function winGetTempname():
If we teach winConvertFromUtf8Filename() how to
handle cygwin paths, winGetTempname() doesn't
have to do this conversion any more, just deligate it
to winConvertFromUtf8Filename(). The total
amalgamation becomes 21 lines shorter.

If the function cygwin_conv_path() fails for whatever
reason (which should never happen!), the original path
translation is used as fall-back. This simplifies greatly
the error-handling: if there is something wrong with
the path, everything is cleaned up correctly and
handled exactly as before.

I hope this contribution is still in time for SQLite 3.8.4,
and given some time for proper evaluation.

Regards,
Jan Nijtmans
==
Index: src/os_win.c
==
--- src/os_win.c
+++ src/os_win.c
@@ -4131,10 +4131,27 @@
 ** function.
 */
 static void *winConvertFromUtf8Filename(const char *zFilename){
   void *zConverted = 0;
   if( osIsNT() ){
+#ifdef __CYGWIN__
+if( !(winIsDriveLetterAndColon(zFilename)
+&& winIsDirSep(zFilename[2])) ){
+  int nByte = cygwin_conv_path(CCP_POSIX_TO_WIN_W, zFilename, 0, 0);
+  if( nByte>0 ){
+zConverted = sqlite3MallocZero(nByte);
+if ( zConverted==0 ){
+  return zConverted;
+}
+if( cygwin_conv_path(CCP_POSIX_TO_WIN_W, zFilename,
+ zConverted, nByte)==0 ){
+  return zConverted;
+}
+sqlite3_free(zConverted);
+  }
+}
+#endif
 zConverted = winUtf8ToUnicode(zFilename);
   }
 #ifdef SQLITE_WIN32_HAS_ANSI
   else{
 zConverted = sqlite3_win32_utf8_to_mbcs(zFilename);
@@ -4243,11 +4260,11 @@
   /* If the path starts with a drive letter followed by the colon
   ** character, assume it is already a native Win32 path; otherwise,
   ** it must be converted to a native Win32 path via the Cygwin API
   ** prior to using it.
   */
-  if( winIsDriveLetterAndColon(zDir) ){
+  if( 1 ){
 zConverted = winConvertFromUtf8Filename(zDir);
 if( !zConverted ){
   sqlite3_free(zBuf);
   OSTRACE(("TEMP-FILENAME rc=SQLITE_IOERR_NOMEM\n"));
   return SQLITE_IOERR_NOMEM;
@@ -4256,10 +4273,11 @@
   sqlite3_snprintf(nMax, zBuf, "%s", zDir);
   sqlite3_free(zConverted);
   break;
 }
 sqlite3_free(zConverted);
+#if 0 /* not necessary any more */
   }else{
 zConverted = sqlite3MallocZero( nMax+1 );
 if( !zConverted ){
   sqlite3_free(zBuf);
   OSTRACE(("TEMP-FILENAME rc=SQLITE_IOERR_NOMEM\n"));
@@ -4290,10 +4308,11 @@
   sqlite3_free(zUtf8);
   sqlite3_free(zConverted);
   break;
 }
 sqlite3_free(zConverted);
+#endif /* not necessary any more */
   }
 }
   }
 #elif !SQLITE_OS_WINRT && !defined(__CYGWIN__)
   else if( osIsNT() ){
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] calculation of a fraction stored in a text column

2014-02-20 Thread Kevin Martin 

On 20 Feb 2014, at 12:54, Patrick Proniewski  wrote:

> My problem is that my database holds too many different values for 
> ExposureTime, so the resulting plot is unreadable. I want to be able to "bin" 
> those values to create a proper histogram. It's not possible to "bin" string 
> values, because SQLite has no idea that "1/60" has nothing to do near 
> "1/6000".

Are you able to use an extension? A custom collation on the ExposureTime column 
seems pretty simple (although not thought about it in detail). You should then 
be able to bin the values as they are.

Thanks,
Kevin  


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

[sqlite] calculation of a fraction stored in a text column

2014-02-20 Thread Patrick Proniewski 
Hello,

I'm coming back with my EXIF database. I have a TEXT column, ExposureTime, 
where I store the EXIF representation of photograph's exposure time. Sample 
values: "1/6000", "1/250", "0.5", "1", "6"...
I need to retain this representation, because it's how photographers deal with 
exposure time. 0.004 would be great for math, sorting, etc. but the real life 
thing is "1/250".

My problem is that my database holds too many different values for 
ExposureTime, so the resulting plot is unreadable. I want to be able to "bin" 
those values to create a proper histogram. It's not possible to "bin" string 
values, because SQLite has no idea that "1/60" has nothing to do near "1/6000".

I need to convert strings like "1/6000" and "1/250" into their REAL 
counterparts "0.000166", "0.004" during my SELECT request for "binning"/sorting 
and counting purposes. I've started to work on an over-complex substr()+instr() 
combo that is not finished yet, but will probably be a dead-end.

Is there a straightforward way to do this, instead of conditionally decomposing 
the TEXT and recomposing a mathematical expression that SELECT can calculate?

regards,
Patrick

(you can Cc me, I'm subscribed to digest)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trying to use in-memory database

2014-02-20 Thread Yuriy Kaminskiy 
Richard Hipp wrote:
> On Wed, Feb 19, 2014 at 5:25 PM, Jeff Archer <
> jsarc...@nanotronicsimaging.com> wrote:
> 
>> Long time SQLite user but I don't think I have ever tried to do an
>> in-memory database before.
>> Just upgraded to 3.8.3.1 but I am not having any other failures with
>> existing code so I don't think that is any part of the problem.
>> I am trying to open the database with sqlite3_open16() using a filename of
>> L":MEMORY:".  I am getting a result code of 14 which is SQLITE_CANTOPEN.
>> It does open if I use a real filename.
> 
> The magic name is case-sensitive and lower-case.  Use L":memory:" and it
> should work for you.

... and also note that passing L"..." string constants (and everything else of
`wchar_t *` type) to sqlite3_*16() *very* bad idea (on nearly all non-windows
platforms wchar_t is 32-bit int ucs-32, sqlite3_*16() expects 16-bit/utf-16,
depending on platform endianness it will try to open ":" [which will silently
succeed, but open *on-disk* database in current directory, extremely nasty!] or
"" [which will likely fail]).

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