Re: [sqlite] SQLite Shell with Custom FTS Tokenizer

On Wed, 24 Apr 2013 19:50:14 -0400 Richard Hipp wrote: > On Wed, Apr 24, 2013 at 7:39 PM, Paul Vercellotti > wrote: > > > Does someone have a clever way of getting a custom tokenizer into a > > build of the sqlite shell, without modifying the shell sources?

Re: [sqlite] Programming API vs console

On 25 Apr 2013, at 3:28am, Igor Korot wrote: > Changed. No difference at all. Record is still does not show up. My guess is that you are opening different files in the shell and your app. This is usually caused by a default file path not being what you think it is. Use

Re: [sqlite] Programming API vs console

Simon, On Wed, Apr 24, 2013 at 5:56 PM, Simon Slavin wrote: > > On 25 Apr 2013, at 1:54am, Igor Korot wrote: > > > Then why this query works in the command prompt console? I don't have > "Team > > 1" column name in any of the tables. > > Nevertheless

Re: [sqlite] Programming API vs console

On 25 Apr 2013, at 1:54am, Igor Korot wrote: > Then why this query works in the command prompt console? I don't have "Team > 1" column name in any of the tables. > Nevertheless I'm going to change this and all other queries that use the > string literals ot use >

Re: [sqlite] Programming API vs console

James, On Wed, Apr 24, 2013 at 4:39 PM, James K. Lowden wrote: > On Wed, 24 Apr 2013 07:20:59 -0500 > "Jay A. Kreibich" wrote: > > > > query = wxString::Format( "INSERT INTO playersdrafted VALUES( %d, % > > > d, ( SELECT ownerid FROM owners WHERE

Re: [sqlite] Order of columns in group by statement affects query performance

On Wed, 24 Apr 2013 17:46:00 +0100 Simon Slavin wrote: > On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote: > > Note though that the query doesn't have an ORDER BY clause. It > > doesn't request rows in any particular order. SQLite could, in > >

Re: [sqlite] SQLite Shell with Custom FTS Tokenizer

On Wed, Apr 24, 2013 at 7:39 PM, Paul Vercellotti wrote: > > > Does someone have a clever way of getting a custom tokenizer into a build > of the sqlite shell, without modifying the shell sources? > Put your tokenizer in a shared library. See

[sqlite] SQLite Shell with Custom FTS Tokenizer

Hi there, We're using a custom FTS tokenizer in our database.  We'd like to use the SQLite shell to debug our database, but it can't deal with our FTS tables because the custom tokenizer isn't registered. Does someone have a clever way of getting a custom tokenizer into a build of the

Re: [sqlite] Programming API vs console

On Wed, 24 Apr 2013 07:20:59 -0500 "Jay A. Kreibich" wrote: > > query = wxString::Format( "INSERT INTO playersdrafted VALUES( %d, % > > d, ( SELECT ownerid FROM owners WHERE ownername = \"%s\" AND id = % > > d ), %d, %d, \"%s\" );", player.GetPlayerId(), leagueId, > > const_cast(

Re: [sqlite] I/O errors with WAL on ZFS

On 04/24/2013 10:25 PM, Richard Hipp wrote: > SQLite is using posix_fallocate() to allocate space for a region of shared > memory obtained using mmap(). If the space cannot be preallocated, then > when we use the mmapped region and an attempt is made to allocate the space > and the filesystem is

Re: [sqlite] I/O errors with WAL on ZFS

On Wed, Apr 24, 2013 at 3:25 PM, Richard Hipp wrote: > On Wed, Apr 24, 2013 at 12:21 PM, Sašo Kiselkov wrote: >> ... > > SQLite is using posix_fallocate() to allocate space for a region of shared > memory obtained using mmap(). If the space cannot be

Re: [sqlite] I/O errors with WAL on ZFS

On Wed, Apr 24, 2013 at 12:21 PM, Sašo Kiselkov wrote: > > Just as a quick follow-up on this, when I manually undefine > HAVE_POSIX_FALLOCATE, which makes SQLite fall back to the > truncate-and-write implementation, everything works fine. > > ZFS has been the filesystem of

Re: [sqlite] I/O errors with WAL on ZFS

On Wed, Apr 24, 2013 at 11:21 AM, Sašo Kiselkov wrote: > ZFS has been the filesystem of choice for SunOS-based systems for about > the last 5 years now, is becoming that for FreeBSD as we speak, and is More like 8 years :) > quickly gaining ground on Linux. The absence

Re: [sqlite] Programming API vs console

On Wed, Apr 24, 2013 at 12:59 PM, Igor Korot wrote: > Clemens, > > On Wed, Apr 24, 2013 at 12:21 PM, Clemens Ladisch wrote: > >> Igor Korot wrote: >> On Wed, Apr 24, 2013 at 12:50 AM, Clemens Ladisch < >> clem...@ladisch.de >> wrote: >> >

Re: [sqlite] Programming API vs console

Clemens, On Wed, Apr 24, 2013 at 12:21 PM, Clemens Ladisch wrote: > Igor Korot wrote: > On Wed, Apr 24, 2013 at 12:50 AM, Clemens Ladisch wrote: > > Igor Korot wrote: > >> ... > >> else > >> sqlite3_step( stmt ); >

Re: [sqlite] Programming API vs console

Igor Korot wrote: On Wed, Apr 24, 2013 at 12:50 AM, Clemens Ladisch Igor Korot wrote: >> ... >> else >> sqlite3_step( stmt ); > > You forgot to check for errors. > I guess the code that executes the COMMIT has the same

Re: [sqlite] index creation 30x slower on Mac than on Linux

On Tue, Apr 23, 2013 at 5:58 PM, Alan Frankel wrote: > ...Why is the Mac 30 times slower than Linux? They're both on the same > network, accessing the same network drive location, so neither has the > advantage of local disk access. > i'm speculating, but i have seen

Re: [sqlite] Programming API vs console

Hi, ALL, On Wed, Apr 24, 2013 at 11:11 AM, Igor Korot wrote: > > > On Wed, Apr 24, 2013 at 3:54 AM, Clemens Ladisch wrote: > >> Igor Korot wrote: >> > Clement, >> >> Who? ;-) >> > > Oops... > I promise I will never write an E-mail at 1:00 AM ;-) > > >>

Re: [sqlite] Programming API vs console

On Wed, Apr 24, 2013 at 3:54 AM, Clemens Ladisch wrote: > Igor Korot wrote: > > Clement, > > Who? ;-) > Oops... I promise I will never write an E-mail at 1:00 AM ;-) > > On Wed, Apr 24, 2013 at 12:50 AM, Clemens Ladisch >wrote: > >> Igor Korot

Re: [sqlite] sqlite4 and sqlite3_busy_timeout

I've solved these two (in my case sqlite3_busy_timeout isn't necessary and can just be removed, sqlite4_result_blob and sqlite4_result_text now take a xDel argument that can just be zeroed out, and sqlite4_create_collation now takes a callback to generate keys for the k/v storage engine that

Re: [sqlite] Order of columns in group by statement affects query performance

On 24 Apr 2013, at 5:14pm, Igor Tandetnik wrote: > Note though that the query doesn't have an ORDER BY clause. It doesn't > request rows in any particular order. SQLite could, in principle, reorder > columns in GROUP BY to take advantage of the index. I suppose the

Re: [sqlite] I/O errors with WAL on ZFS

On 04/24/2013 05:17 PM, Sašo Kiselkov wrote: > On 04/24/2013 04:44 PM, Sašo Kiselkov wrote: >> On 04/24/2013 04:41 PM, Richard Hipp wrote: >>> On Wed, Apr 24, 2013 at 10:28 AM, Sašo Kiselkov >>> wrote: >>> On 04/24/2013 03:57 PM, Richard Hipp wrote: > On Wed, Apr

Re: [sqlite] Order of columns in group by statement affects query performance

On 4/24/2013 11:49 AM, Larry Brasfield wrote: *Daniel Winter wrote:0* Table: Column A int, Column B int, Column C int One Index: A,B (combined) Query 1: SELECT A,B,count(*) from tableTest group by A,B Query 2: SELECT A,B,count(*) from tableTest group by B,A Query 1 will use the index,

Re: [sqlite] Order of columns in group by statement affects query performance

*Daniel Winter wrote:0* > I discovered that the order of columns in a group by affects the > performance of a query. Is this expected? Yes. > For example: > > Table: Column A int, Column B int, Column C int > One Index: A,B (combined) > > Query 1: SELECT A,B,count(*) from tableTest group by

Re: [sqlite] Fw: Fw: Is there a command to lock a database for a short while (0.25 sec, say)

On Wed, 24 Apr 2013 16:35:21 +0200 "J Trahair" wrote: > I've found that either one of these will lock the database: > > mstrSQL = "PRAGMA locking_mode = EXCLUSIVE;BEGIN EXCLUSIVE;" > mstrSQL = "PRAGMA locking_mode = EXCLUSIVE;BEGIN >

Re: [sqlite] I/O errors with WAL on ZFS

On 04/24/2013 04:44 PM, Sašo Kiselkov wrote: > On 04/24/2013 04:41 PM, Richard Hipp wrote: >> On Wed, Apr 24, 2013 at 10:28 AM, Sašo Kiselkov >> wrote: >> >>> On 04/24/2013 03:57 PM, Richard Hipp wrote: On Wed, Apr 24, 2013 at 8:28 AM, Sašo Kiselkov

[sqlite] Order of columns in group by statement affects query performance

Hello, I am using sqlite3 (3.7.15.2 at the moment) in a project. I discovered that the order of columns in a group by affects the performance of a query. Is this expected? For example: Table: Column A int, Column B int, Column C int One Index: A,B (combined) Query 1: SELECT A,B,count(*)

Re: [sqlite] Some basic questions

Hi all, Many thanks for the info. I'll look into using the changes function for what I need. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road,

Re: [sqlite] I/O errors with WAL on ZFS

On 04/24/2013 04:41 PM, Richard Hipp wrote: > On Wed, Apr 24, 2013 at 10:28 AM, Sašo Kiselkov wrote: > >> On 04/24/2013 03:57 PM, Richard Hipp wrote: >>> On Wed, Apr 24, 2013 at 8:28 AM, Sašo Kiselkov >> wrote: >>> I'm running into I/O errors

Re: [sqlite] I/O errors with WAL on ZFS

On Wed, Apr 24, 2013 at 10:28 AM, Sašo Kiselkov wrote: > On 04/24/2013 03:57 PM, Richard Hipp wrote: > > On Wed, Apr 24, 2013 at 8:28 AM, Sašo Kiselkov >wrote: > > > >> I'm running into I/O errors when trying to access a sqlite3 database > >>

Re: [sqlite] I/O errors with WAL on ZFS

On 04/24/2013 04:28 PM, Sašo Kiselkov wrote: > On 04/24/2013 03:57 PM, Richard Hipp wrote: >> On Wed, Apr 24, 2013 at 8:28 AM, Sašo Kiselkov wrote: >> >>> I'm running into I/O errors when trying to access a sqlite3 database >>> which is using WAL from my app. While using

[sqlite] Fw: Fw: Is there a command to lock a database for a short while (0.25 sec, say)

I've found that either one of these will lock the database: mstrSQL = "PRAGMA locking_mode = EXCLUSIVE;BEGIN EXCLUSIVE;" mstrSQL = "PRAGMA locking_mode = EXCLUSIVE;BEGIN EXCLUSIVE;COMMIT;" and that closing the connection a little while later (eg. 0.25sec)

Re: [sqlite] I/O errors with WAL on ZFS

On 04/24/2013 03:57 PM, Richard Hipp wrote: > On Wed, Apr 24, 2013 at 8:28 AM, Sašo Kiselkov wrote: > >> I'm running into I/O errors when trying to access a sqlite3 database >> which is using WAL from my app. While using journal_mode=delete, >> everything is fine, but as

Re: [sqlite] Some basic questions

David Wellman wrote: > Q1) Is it possible to execute sql commands asynchronously ? i.e. my program > issues the sql command and then 'loops' whilst waiting for the command to > finish. No; SQLite runs neither on a separate server nor in a separate process/ thread. SQLite is a library that runs

Re: [sqlite] Some basic questions

On 23 Apr 2013, at 7:57pm, David Wellman wrote: > Q1) Is it possible to execute sql commands asynchronously ? i.e. my program > issues the sql command and then 'loops' whilst waiting for the command to > finish. The main need for this is so that my user has a

Re: [sqlite] I/O errors with WAL on ZFS

On Wed, Apr 24, 2013 at 8:28 AM, Sašo Kiselkov wrote: > I'm running into I/O errors when trying to access a sqlite3 database > which is using WAL from my app. While using journal_mode=delete, > everything is fine, but as soon as I switch over to journal_mode=wal, I > just

[sqlite] I/O errors with WAL on ZFS

I'm running into I/O errors when trying to access a sqlite3 database which is using WAL from my app. While using journal_mode=delete, everything is fine, but as soon as I switch over to journal_mode=wal, I just get a load of I/O errors on any query, regardless if it is a SELECT or UPDATE/INSERT.

Re: [sqlite] index creation 30x slower on Mac than on Linux

On 23 Apr 2013, at 4:58pm, Alan Frankel wrote: > We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. > In order to make our lookups fast, we're creating an index table in the > database. On Linux, creating the table takes about 200 seconds.

[sqlite] I/O errors with WAL on ZFS

I'm running into I/O errors when trying to access a sqlite3 database which is using WAL from my app. While using journal_mode=delete, everything is fine, but as soon as I switch over to journal_mode=wal, I just get a load of I/O errors on any query, regardless if it is a SELECT or UPDATE/INSERT.

[sqlite] Some basic questions

Hi, I've just started using SQLite and having come from another dbms environment I'm trying to find out if some of the features that I'm used to using in my coding are available with Sqlite. I've done a lot of searching through the documentation and I apologise if the information is there but

[sqlite] index creation 30x slower on Mac than on Linux

We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. In order to make our lookups fast, we're creating an index table in the database. On Linux, creating the table takes about 200 seconds. On the Mac the same operation takes 6,400 seconds. Here's the "CREATE INDEX"

Re: [sqlite] Programming API vs console

On 4/24/2013 12:45 AM, Igor Korot wrote: In the beginning I'm issuing "BEGIN". In the end if everything is good I'm issuing "COMMIT", if not "ROLLBACK". So when running under debugger in Visual Studio, right after sqlite3_step() call I am issuing SELECT * FROM playersdrafted in the console. It

Re: [sqlite] Programming API vs console

On Tue, Apr 23, 2013 at 08:47:18PM -0700, Igor Korot scratched on the wall: > Here is the code: > > query = wxString::Format( "INSERT INTO playersdrafted VALUES( %d, %d, ( > SELECT ownerid FROM owners WHERE ownername = \"%s\" AND id = %d ), %d, %d, > \"%s\" );", player.GetPlayerId(), leagueId,

Re: [sqlite] Fw: Is there a command to lock a database for a short while (0.25 sec, say)

On 24 Apr 2013, at 11:58am, "J Trahair" wrote: > I had closed the connection after the COMMIT, but it works great if I don't. Your problem is not with closing the connection but with the COMMIT. The BEGIN goes with the COMMIT. When you go 'BEGIN EXCLUSIVE'

Re: [sqlite] Programming API vs console

On 24 Apr 2013, at 4:47am, Igor Korot wrote: > sqlite3_step( stmt ); > sqlite3_finalize( stmt ); Please check the values SQLite returns from the _step and _finalize calls to make sure it's not generating an error. Simon.

Re: [sqlite] Fw: Is there a command to lock a database for a short while (0.25 sec, say)

I had closed the connection after the COMMIT, but it works great if I don't. Thank you for that! I like the kitchen analogy, but it's more like 'Form an orderly queue for the electric citrus juicer' instead of all crowding round it and trying to juice 6 oranges and 4 lemons at once. In the end,

Re: [sqlite] Programming API vs console

Igor Korot wrote: > Clement, Who? ;-) > On Wed, Apr 24, 2013 at 12:50 AM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> ... >>> else >>> sqlite3_step( stmt ); >> >> You forgot to check for errors. >> I guess the code that executes the COMMIT has the same bug. > >

Re: [sqlite] Fw: Is there a command to lock a database for a short while (0.25 sec, say)

On Wed, 24 Apr 2013 11:19:32 +0200 "J Trahair" wrote: > Thanks for the replies so far. > > I've been trying to get the database to lock - I am using a program I > developed which runs on 2 of my computers but connected to the same SQLite > database: > >

Re: [sqlite] Programming API vs console

Clement, On Wed, Apr 24, 2013 at 12:50 AM, Clemens Ladisch wrote: > Igor Korot wrote: > > ... > > else > > sqlite3_step( stmt ); > > You forgot to check for errors. > I guess the code that executes the COMMIT has the same bug. > I am checking the error. It is from

[sqlite] Fw: Is there a command to lock a database for a short while (0.25 sec, say)

Thanks for the replies so far. I've been trying to get the database to lock - I am using a program I developed which runs on 2 of my computers but connected to the same SQLite database: mstrSQL = "PRAGMA locking_mode = EXCLUSIVE;BEGIN EXCLUSIVE;UPDATE Utilities SET OwnersName = '"

Re: [sqlite] Programming API vs console

Igor Korot wrote: > ... > else > sqlite3_step( stmt ); You forgot to check for errors. I guess the code that executes the COMMIT has the same bug. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] [Question] SQLITE_FCNTL_CHUNK_SIZE

Hi. Another question. Below source code is a part of "pager_write_pagelist()". In this code, dbSize of pPager or pgno of pList are compared with dbHistSize of pPager. However, szFile variable is only calculated from dbSize of pPager. /* Before the first write, give the VFS a hint of what the