Re: [sqlite] Transaction triggers?

[Nico Williams]

It took just a few minutes to start on that approach.  I have the
necessary parse.y changes done and know what to do for the rest.  Time
to subscribe to sqlite-dev...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction triggers?

[Nico Williams]

BTW, I might implement and contribute something like this.  I'm free
to contribute any changes to SQLite3 that I make.  I'd need some
guidance though.  And what I cannot contribute is the high degree of
testing that SQLite3 is known for.

The parsing aspect of database triggers is simple enough.  But there's
a lot of little details.

The simplest way to avoid most of those details would be to pretend
(but not document!) that database triggers are like INSTEAD OF INSERT
triggers on a view named sqlite_, so that the
existing code infrastructure for ROW triggers can be used without
modification.  Then at the various events act as though an insert has
been done into that view.  This approach would also simplify much of
the testing (since existing testing would already be reaching every
branch in the ROW trigger code).

Would such a contribution be welcomed?

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

Re: [sqlite] Transaction triggers?

[Nico Williams]

On Fri, May 6, 2011 at 4:30 PM, Simon Slavin  wrote:
> On 6 May 2011, at 10:14pm, Nico Williams wrote:
>> Here's what I need:
>>
>> - at transaction time I need a way to record somewhere that the
>> transaction did start.  This would mostly be an insert into a table
>> with an INTEGER PRIMARY KEY AUTOINCREMENT column, and a time of
>> transaction start.  I can do without this by simply doing an insert
>> into that table if the relevant row didn't already exist.
>
> You can do an 'INSERT OR IGNORE'.

And INSERT INTO ... SELECT ... WHERE .  I know this -- it's what I mean above :)

>> - at transaction commit time I need to be able to RAISE() exceptions
>> if the concluding transaction does not meet certain requirements.
>> I.e., SELECT RAISE(ROLLBACK, 'Error: user did not x, y, or z') WHERE
>> .  I have no way to detect end of transaction
>> time, so I can't really do without this :(
>>
>> I'd also like to be able to do inserts/updates/deletes at transaction
>> commit time, as if the application's COMMIT has been a savepoint, but
>> I could live without this capability.
>
> You may be subverting the way SQL works.  The alternative kind of trigger to 
> ROW is not TRANSACTION, it's STATEMENT.  One can use several statements in a 
> TRANSACTION.

Well, D.R. Hipp at least at one point disagreed with you, since he
himself proposed something like this:

http://osdir.com/ml/db.sqlite.general/2003-04/msg00137.html

Moreover, other SQL RDBMes support transaction triggers.  See, for example:

http://en.wikipedia.org/wiki/Database_trigger

Finally, arguably triggers alone violate the SQL concept of a
declarative language.  I've shown here before how one can do quite a
bit of procedural programming using triggers in SQLite3.  So if it's
SQL purity you want, well, it's too late already :)  I grant you that
one might not want to further depart from the pure than one already
has, but that's a different argument than "you're leaving the pure
behind".

> I suspect your easiest way forward would be to implement that code inside 
> your application: instead of calling "BEGIN" and "COMMIT" make your own 
> routines for those two things.  Another way would be to take apart SQLite and 
> rewrite some of the code in ways that suit this one particular application.

One of the very nice things about SQLite3 is the availability of the
shell, and the fact that anyone can download SQLite3 and link it into
any application.

In the particular application I'm building I'd like to be able to
encode as much as possible of the business logic into the schema so
that I don't have to forbid direct access to the DB by any programs
other than those I write.  The reason for this is that I very much
foresee third parties wanting to write their own tools to manipulate
my application's database.  Moreover, I don't want to write all those
tools, as that's not what I'm getting paid to do -- enabling third
parties here is of great utility to me.  I believe that would be a
great feature for my application to have.  All I need is a way to run
some SELECT statements at COMMIT time, with those SELECTs possibly
RAISE()ing exceptions.  I could use more functionality too, but that's
the bare minimum I need.

I can detect transaction start, but not transaction end.  I need to
detect transaction end...

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

[sqlite] How to get returned errors programmatically from SQLite

[Rolf Marsh]

I apologize if this is not the correct place to ask this question.  I am 
using FMDB; I have an INSERT statement that is failing on a "constraint 
error" (duplicate record which is the correct error).

My question is: how do I determine programmatically (using Obj-C) what, 
if any, error was returned from the INSERT?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction triggers?

[Simon Slavin]

On 6 May 2011, at 10:14pm, Nico Williams wrote:

> Here's what I need:
> 
> - at transaction time I need a way to record somewhere that the
> transaction did start.  This would mostly be an insert into a table
> with an INTEGER PRIMARY KEY AUTOINCREMENT column, and a time of
> transaction start.  I can do without this by simply doing an insert
> into that table if the relevant row didn't already exist.

You can do an 'INSERT OR IGNORE'.

> - at transaction commit time I need to be able to RAISE() exceptions
> if the concluding transaction does not meet certain requirements.
> I.e., SELECT RAISE(ROLLBACK, 'Error: user did not x, y, or z') WHERE
> .  I have no way to detect end of transaction
> time, so I can't really do without this :(
> 
> I'd also like to be able to do inserts/updates/deletes at transaction
> commit time, as if the application's COMMIT has been a savepoint, but
> I could live without this capability.

You may be subverting the way SQL works.  The alternative kind of trigger to 
ROW is not TRANSACTION, it's STATEMENT.  One can use several statements in a 
TRANSACTION.

I suspect your easiest way forward would be to implement that code inside your 
application: instead of calling "BEGIN" and "COMMIT" make your own routines for 
those two things.  Another way would be to take apart SQLite and rewrite some 
of the code in ways that suit this one particular application.

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

[sqlite] Transaction triggers?

[Nico Williams]

I really, really need transaction, not row, triggers.  There's been
discussion of those here in the past (IIRC D.R. Hipp had a proposal
once).

Here's what I need:

 - at transaction time I need a way to record somewhere that the
transaction did start.  This would mostly be an insert into a table
with an INTEGER PRIMARY KEY AUTOINCREMENT column, and a time of
transaction start.  I can do without this by simply doing an insert
into that table if the relevant row didn't already exist.

 - at transaction commit time I need to be able to RAISE() exceptions
if the concluding transaction does not meet certain requirements.
I.e., SELECT RAISE(ROLLBACK, 'Error: user did not x, y, or z') WHERE
.  I have no way to detect end of transaction
time, so I can't really do without this :(

I'd also like to be able to do inserts/updates/deletes at transaction
commit time, as if the application's COMMIT has been a savepoint, but
I could live without this capability.

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

Re: [sqlite] Addition: Re: SQLite locking question

[Pavel Ivanov]

> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?

Yes, you are involved in a "magical" thinking. All that you said would
be true if any change that SQLite does in the database cache was
atomic. I.e. let's say SQLite needs to add a row, zing, and the row is
in there and all necessary cache pages are changed without other
thread ever noticing any inconsistent state during the process. Also
let's say SQLite needs to find some row in the table, zing, and it
already knows where that row is and other thread didn't change
anything during the search... All this cannot happen, thus mutex is
held.

BTW, if you just read data then locking cannot be an issue for you.
Turn off shared cache and you will be okay.


Pavel


On Fri, May 6, 2011 at 3:56 PM, Ole Reinhardt
 wrote:
> Hi Pavel,
>
>> >> Any other chance to speed this up (apart from the obvious "optimize the
>> >> query, do not use distinct on large tables)=
>> >
>> > Without seeing the query or database schema? Not really... Depending
>> > on the exact query an index on "xyz" might help.
>>
>> Another suggestion could be to turn off shared cache mode.
>
> Oh ok :)
>
> My understanding of the shared cache model was that it just ist intended
> for solving our problem by relaxing the locking a little and that there
> should not be any mutexes at all when using the uncomitted read mode.
>
> Have I missed anything?
>
> Bye,
>
> Ole
>
> --
>
> Thermotemp GmbH, Embedded-IT
>
> Embedded Hard-/ Software and Open Source Development,
> Integration and Consulting
>
> http://www.embedded-it.de
>
> Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen -
> tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97
>
> Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
> Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
> Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
> Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280
>
> ___
> 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] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi Pavel,

> >> Any other chance to speed this up (apart from the obvious "optimize the
> >> query, do not use distinct on large tables)=
> >
> > Without seeing the query or database schema? Not really... Depending
> > on the exact query an index on "xyz" might help.
> 
> Another suggestion could be to turn off shared cache mode.

Oh ok :)

My understanding of the shared cache model was that it just ist intended
for solving our problem by relaxing the locking a little and that there
should not be any mutexes at all when using the uncomitted read mode.

Have I missed anything?

Bye,

Ole

-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi Dan,

>[...]
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.

Ok, I see, so there is no chance to access the data realy in _parallel_?
(Just for reading).

> > Any other chance to speed this up (apart from the obvious "optimize the
> > query, do not use distinct on large tables)=
> 
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

The database scheme looks like this:

static gchar sql_create_table_log[] =
"CREATE TABLE IF NOT EXISTS log ( "
"  keyINTEGER, "
"  timestamp_sINTEGER, "
"  timestamp_us   INTEGER, "
"  type   INTEGER, "
"  level  INTEGER, "
"  event  INTEGER, "
"  source TEXT,"
"  dest   TEXT,"
"  text   TEXT,"
"  radio_id   INTEGER, "
"  latitude   REAL,"
"  longitude  REAL,"
"  speed  REAL,"
"  direction  INTEGER, "
"  fixINTEGER, "
"  alert  INTEGER, "
"  state  INTEGER, "
"  cstate INTEGER, "
"  PRIMARY KEY(key)"
")";

static gchar sql_create_index_log[] = 
"CREATE INDEX IF NOT EXISTS timestamp ON log (timestamp_s,
timestamp_us)";


The SQL query that takes that much time for each single step is the
following:

"SELECT DISTINCT radio_id FROM log WHERE event IN (%d, %d) AND
timestamp_s >= %ld AND timestamp_s <= %ld"



The request that shall be done in parallel is the following:

SELECT timestamp_s, timestamp_us, type, level, event, source, dest,
text, radio_id, latitude, longitude, speed, direction, key FROM log
WHERE (timestamp_s * 1000 + timestamp_us / 1000) > %lld %s AND key >
((SELECT MAX(key) FROM log) -2000) ORDER BY KEY DESC


Regards!

Ole





-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] System.Data.SQLite blob column exception

[Tim Butterfield]

It is storing files of various types, some text and some with binary
data.  The code is generic to handle any type of file with any
extension, some user defined.  My C# code cannot accurately know in
advance whether it is retrieving text or binary data.  The result set
could have multiples of text files and multiples of binary files in
any order.  If the first row contains a binary file, GetFieldType
returns byte[] for all rows, text or binary, since it uses an
internally cached value for that SQL statement.  When checking this,
GetSQLiteType pulls the SQLiteType from _fieldTypeArray[i] if that
position is not null.

Tim

On Fri, May 6, 2011 at 11:40 AM, Robert Simpson  wrote:
> So you're always wanting to call GetBytes() even if the column is text?
> What about calling GetFieldType() beforehand to determine whether or not to
> call GetString() or GetBytes()?
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
> Sent: Friday, May 06, 2011 9:31 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] System.Data.SQLite blob column exception
>
> I am using a DbDataReader to return the columns for a SQL  SELECT
> statement.  For the blob column, I am calling reader.GetOrdinal to get
> the column number, calling reader.GetBytes to get the size of the
> data, allocating the byte[], and then calling reader.GetBytes to read
> into the buffer.
>
> Tim
>
> On Fri, May 6, 2011 at 11:12 AM, Robert Simpson 
> wrote:
>> How are you fetching the data?  Are you using the generic reader[x] on a
>> datareader?  Are you using a DataSet/DataTable?
>>
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
>> Sent: Friday, May 06, 2011 8:58 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] System.Data.SQLite blob column exception
>>
>> On Fri, May 6, 2011 at 7:44 AM, Simon Slavin  wrote:
>>>
>>> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>>>
 The blob values are always inserted and read as a byte[].  If those
 bytes are those of a text file, it fails.  If I add a leading 0x00
 byte to force it to be binary, it works.
>>>
>>> Are you binding your parameters ?  If so, check that you're using
>> sqlite3_bind_blob() when binding something to your BLOB column.  Binding
> it
>> as text will not trigger an error, since SQLite is quite happy to bind
> text
>> into a BLOB column.  But if you take blobby data, and bind it using
>> sqlite3_bind_blob() into a column defined as a BLOB SQLite should be
>> retaining and reporting it as as blob, not as text.
>>>
>>> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than
>> binding parameters, them make sure you're using the correct format to
>> specify you have blob data (is it \x ?) rather than just supplying text.
>>
>> I'm using the System.Data.SQLite interface and not the direct C
>> interface methods.  All of my inserts are done using DbParameter (@p1,
>> @p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
>> the C# object type.  In the case of the blob column, that type is a C#
>> byte[], which sets the parameter type to DbType.Binary whether the
>> first byte is 0x00 or a more textual value.
>>
>> Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite blob column exception

[Robert Simpson]

So you're always wanting to call GetBytes() even if the column is text?
What about calling GetFieldType() beforehand to determine whether or not to
call GetString() or GetBytes()?

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
Sent: Friday, May 06, 2011 9:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite blob column exception

I am using a DbDataReader to return the columns for a SQL  SELECT
statement.  For the blob column, I am calling reader.GetOrdinal to get
the column number, calling reader.GetBytes to get the size of the
data, allocating the byte[], and then calling reader.GetBytes to read
into the buffer.

Tim

On Fri, May 6, 2011 at 11:12 AM, Robert Simpson 
wrote:
> How are you fetching the data?  Are you using the generic reader[x] on a
> datareader?  Are you using a DataSet/DataTable?
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
> Sent: Friday, May 06, 2011 8:58 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] System.Data.SQLite blob column exception
>
> On Fri, May 6, 2011 at 7:44 AM, Simon Slavin  wrote:
>>
>> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>>
>>> The blob values are always inserted and read as a byte[].  If those
>>> bytes are those of a text file, it fails.  If I add a leading 0x00
>>> byte to force it to be binary, it works.
>>
>> Are you binding your parameters ?  If so, check that you're using
> sqlite3_bind_blob() when binding something to your BLOB column.  Binding
it
> as text will not trigger an error, since SQLite is quite happy to bind
text
> into a BLOB column.  But if you take blobby data, and bind it using
> sqlite3_bind_blob() into a column defined as a BLOB SQLite should be
> retaining and reporting it as as blob, not as text.
>>
>> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than
> binding parameters, them make sure you're using the correct format to
> specify you have blob data (is it \x ?) rather than just supplying text.
>
> I'm using the System.Data.SQLite interface and not the direct C
> interface methods.  All of my inserts are done using DbParameter (@p1,
> @p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
> the C# object type.  In the case of the blob column, that type is a C#
> byte[], which sets the parameter type to DbType.Binary whether the
> first byte is 0x00 or a more textual value.
>
> Tim
> ___
> 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
>



-- 
Tim Butterfield
http://www.timbutterfield.com/
___
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] System.Data.SQLite blob column exception

[Tim Butterfield]

I am using a DbDataReader to return the columns for a SQL  SELECT
statement.  For the blob column, I am calling reader.GetOrdinal to get
the column number, calling reader.GetBytes to get the size of the
data, allocating the byte[], and then calling reader.GetBytes to read
into the buffer.

Tim

On Fri, May 6, 2011 at 11:12 AM, Robert Simpson  wrote:
> How are you fetching the data?  Are you using the generic reader[x] on a
> datareader?  Are you using a DataSet/DataTable?
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
> Sent: Friday, May 06, 2011 8:58 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] System.Data.SQLite blob column exception
>
> On Fri, May 6, 2011 at 7:44 AM, Simon Slavin  wrote:
>>
>> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>>
>>> The blob values are always inserted and read as a byte[].  If those
>>> bytes are those of a text file, it fails.  If I add a leading 0x00
>>> byte to force it to be binary, it works.
>>
>> Are you binding your parameters ?  If so, check that you're using
> sqlite3_bind_blob() when binding something to your BLOB column.  Binding it
> as text will not trigger an error, since SQLite is quite happy to bind text
> into a BLOB column.  But if you take blobby data, and bind it using
> sqlite3_bind_blob() into a column defined as a BLOB SQLite should be
> retaining and reporting it as as blob, not as text.
>>
>> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than
> binding parameters, them make sure you're using the correct format to
> specify you have blob data (is it \x ?) rather than just supplying text.
>
> I'm using the System.Data.SQLite interface and not the direct C
> interface methods.  All of my inserts are done using DbParameter (@p1,
> @p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
> the C# object type.  In the case of the blob column, that type is a C#
> byte[], which sets the parameter type to DbType.Binary whether the
> first byte is 0x00 or a more textual value.
>
> Tim
> ___
> 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
>



-- 
Tim Butterfield
http://www.timbutterfield.com/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite blob column exception

[Robert Simpson]

How are you fetching the data?  Are you using the generic reader[x] on a
datareader?  Are you using a DataSet/DataTable?  


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Butterfield
Sent: Friday, May 06, 2011 8:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite blob column exception

On Fri, May 6, 2011 at 7:44 AM, Simon Slavin  wrote:
>
> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>
>> The blob values are always inserted and read as a byte[].  If those
>> bytes are those of a text file, it fails.  If I add a leading 0x00
>> byte to force it to be binary, it works.
>
> Are you binding your parameters ?  If so, check that you're using
sqlite3_bind_blob() when binding something to your BLOB column.  Binding it
as text will not trigger an error, since SQLite is quite happy to bind text
into a BLOB column.  But if you take blobby data, and bind it using
sqlite3_bind_blob() into a column defined as a BLOB SQLite should be
retaining and reporting it as as blob, not as text.
>
> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than
binding parameters, them make sure you're using the correct format to
specify you have blob data (is it \x ?) rather than just supplying text.

I'm using the System.Data.SQLite interface and not the direct C
interface methods.  All of my inserts are done using DbParameter (@p1,
@p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
the C# object type.  In the case of the blob column, that type is a C#
byte[], which sets the parameter type to DbType.Binary whether the
first byte is 0x00 or a more textual value.

Tim
___
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] System.Data.SQLite blob column exception

[Simon Slavin]

On 6 May 2011, at 4:58pm, Tim Butterfield wrote:

> I'm using the System.Data.SQLite interface and not the direct C
> interface methods.  All of my inserts are done using DbParameter (@p1,
> @p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
> the C# object type.  In the case of the blob column, that type is a C#
> byte[], which sets the parameter type to DbType.Binary whether the
> first byte is 0x00 or a more textual value.

Okay, then I believe that your problem is with the System.Data.SQLite 
interface, which is acting in a way that the SQLite C library doesn't.

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

Re: [sqlite] System.Data.SQLite blob column exception

[Tim Butterfield]

On Fri, May 6, 2011 at 7:44 AM, Simon Slavin  wrote:
>
> On 6 May 2011, at 3:43am, Tim Butterfield wrote:
>
>> The blob values are always inserted and read as a byte[].  If those
>> bytes are those of a text file, it fails.  If I add a leading 0x00
>> byte to force it to be binary, it works.
>
> Are you binding your parameters ?  If so, check that you're using 
> sqlite3_bind_blob() when binding something to your BLOB column.  Binding it 
> as text will not trigger an error, since SQLite is quite happy to bind text 
> into a BLOB column.  But if you take blobby data, and bind it using 
> sqlite3_bind_blob() into a column defined as a BLOB SQLite should be 
> retaining and reporting it as as blob, not as text.
>
> If you're creating a fully text 'INSERT' or 'UPDATE' command rather than 
> binding parameters, them make sure you're using the correct format to specify 
> you have blob data (is it \x ?) rather than just supplying text.

I'm using the System.Data.SQLite interface and not the direct C
interface methods.  All of my inserts are done using DbParameter (@p1,
@p2, etc.) and DbCommand insert statements.  The DbParameter.Value is
the C# object type.  In the case of the blob column, that type is a C#
byte[], which sets the parameter type to DbType.Binary whether the
first byte is 0x00 or a more textual value.

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

Re: [sqlite] Backing up online SQLite data files

[Simon Slavin]

On 6 May 2011, at 3:05pm, Jaco Breitenbach wrote:

> On 6 May 2011 14:15, Igor Tandetnik  wrote:
>> 
>> 
>> http://sqlite.org/backup.html
>> 
> 
> I've also looked at that page.  However, that either involves modifying the
> main application or writing a separate backup utility application based on
> the examples provided.

Writing a backup utility is what I'd expect to be best in most situations.

> One thing that I've noticed, is that if the running database that is being
> backed up is continuously being written to, then following a call to
> sqlite3_sleep(), the backup will automatically be restarted.  This seems to
> indicate that my backups might never complete.

A backup using the backup API must be restarted if there is a change to any 
database file.  This is to ensure that the file is consistent.  As you can 
imagine it's almost impossible to backup a file which is continuously being 
written to, no matter what method you use.  The backup API does its best to 
take advantage of any short time when an update is not happening.

> Would a simple copy of the database, -shm and -wal files result in a
> corrupted database if these files are used to restore the system, or would
> it simply appear as if SQLite is recovering from a power failure?

When the database file is closed properly, there are no -shm or -wal files.  
The mere fact that these files exist is a sign that the database is probably 
not in a consistent state, and that some rescue procedures will be needed.  
SQLite does its best to recover a state between transactions when this happens 
but obviously this depends on partially-written data in the journal files.

> Sure,
> there may be a small amount of data loss in doing the copy without a lock,
> but that may be acceptable so long as the data that is in the database is
> not corrupted.

It will work properly only if all those files are copied at the same time.  
Unfortunately you can't depend on this.  For instance, a simple command to your 
shell to copy those files will copy one file first, then start copying the 
second file when the first is complete.  So you might copy the database file 
(presumably very long, taking perhaps a few seconds to copy) and then a journal 
file which corresponds to a few seconds later, by which time several other 
transactions may have been executed.  If SQLite encounters a situation like 
this it will usually be able to recover gracefully, but if you make this a part 
of your normal operating procedure, chances are you'll sooner or later get some 
sort of unpredictable corruption somewhere.

There are several ways you can get copies of your data which will be safe and 
consistent and uncorrupted.

(a) Stop your normal application in the normal way.  It will close the 
connections to the database, which will delete any journal files, leaving you 
with just one database file to copy.  Copy it however you want (rsync, copy, 
backup, whatever) and restart your application.

(b) Use the SQLite backup API, which will present you -- as soon as it has had 
long enough to do the copy safely -- with a copied database file under a 
different filename or directory.  This is specially designed for 24/7 
operations, with SQLite sneaking the copy in as soon as it won't interrupt 
normal business.  Usually at 3am in the morning.

(c) Freeze your normal applications, which will leave you with the database 
file and maybe some journal files, but they will all be consistent with 
one-another.  Copy database and any journal files and keep them all together.  
Unfreeze your normal applications.  When SQLite tries to connect to the backups 
it will notice the journal files and unfinished changes, do whatever needs to 
be done to restore to a savepoint or transaction boundary, and leave you with a 
safe and consistent database.

(d) The same as (c) but instead of freezing the apps, crash them and restart 
them once the copies have been made.  In this case you chance losing a partly 
finished transaction even in the normal copy of the database.

A lot of work went into making SQLite very good at handling situations (c) and 
(d), but obviously one of the other two are the 'proper' ways to do things.

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

Re: [sqlite] Backing up online SQLite data files

[Richard Hipp]

On Fri, May 6, 2011 at 10:05 AM, Jaco Breitenbach
wrote:

>
> Would a simple copy of the database, -shm and -wal files result in a
> corrupted database if these files are used to restore the system, or would
> it simply appear as if SQLite is recovering from a power failure?


SQLite goes to great lengths to make sure that information is written in the
correct order so that it can be recovered after a power failures.  If you
"copy" uses a different order, you could easily end up with corruption.

So, yes, your blind copy will corrupt database files.  Though it is likely
to work *most* of the time.  So you'll only get corruption occasionally -
usually in the field and with an important client, and always
unreproducible.



>  Sure,
> there may be a small amount of data loss in doing the copy without a lock,
> but that may be acceptable so long as the data that is in the database is
> not corrupted.
>
> Jaco
> ___
> 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] Query question

[Igor Tandetnik]

On 5/6/2011 10:03 AM, Matthew Jones wrote:
> Given a table with multiple columns in it how do I count how many
> entries there are with column A matching some value where column B is
> distinct?

select count(distinct b) from a where a=10;

-- 
Igor Tandetnik

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

Re: [sqlite] Query question

[Pavel Ivanov]

> sqlite> select * from a where a=10 group by b;
> 10|1
> 10|2
> sqlite> select * from a where a=11 group by b;
> 11|2
> 11|3
>
> How can I do count equivalent of such a query to find out how many
> distinct values of b there are for a given a? (That is get an answer of
> 2 in the above)

select count(distinct b) from a where a = 11;


Pavel


On Fri, May 6, 2011 at 10:03 AM, Matthew Jones  wrote:
> A simple query question that I really ought to know the answer to but don't:
>
> Given a table with multiple columns in it how do I count how many
> entries there are with column A matching some value where column B is
> distinct?
>
> sqlite> create table a (a, b);
> sqlite> insert into a values (10, 1);
> sqlite> insert into a values (10, 2);
> sqlite> insert into a values (10, 2);
> sqlite> insert into a values (11, 2);
> sqlite> insert into a values (11, 2);
> sqlite> insert into a values (11, 3);
> sqlite> select * from a where a=10 group by b;
> 10|1
> 10|2
> sqlite> select * from a where a=11 group by b;
> 11|2
> 11|3
>
> How can I do count equivalent of such a query to find out how many
> distinct values of b there are for a given a? (That is get an answer of
> 2 in the above)
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> Long Down Avenue
> Stoke Gifford
> Bristol.  BS34 8QZ
> Tel:   +44 (0) 117 312 7490
> Email:    matthew.jo...@hp.com
>
> Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks,
> RG12 1HN. Registered No: 690597 England
>
> The contents of this message and any attachments to it are confidential
> and may be legally privileged. If you have received this message in
> error, you should delete it from your system immediately and advise the
> sender.
>
> To any recipient of this message within HP, unless otherwise stated you
> should consider this message and attachments as "HP CONFIDENTIAL".
> ___
> 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] Query question

[Jaco Breitenbach]

Hi Matthew,

sqlite> select * from a;
10|1
10|2
10|2
11|2
11|2
11|3

sqlite> select c.a, count(*) from (select distinct a, b from a) c group by
c.a;
10|2
11|2

Was that the result you were after?

Jaco


On 6 May 2011 15:03, Matthew Jones  wrote:

> A simple query question that I really ought to know the answer to but
> don't:
>
> Given a table with multiple columns in it how do I count how many
> entries there are with column A matching some value where column B is
> distinct?
>
> sqlite> create table a (a, b);
> sqlite> insert into a values (10, 1);
> sqlite> insert into a values (10, 2);
> sqlite> insert into a values (10, 2);
> sqlite> insert into a values (11, 2);
> sqlite> insert into a values (11, 2);
> sqlite> insert into a values (11, 3);
> sqlite> select * from a where a=10 group by b;
> 10|1
> 10|2
> sqlite> select * from a where a=11 group by b;
> 11|2
> 11|3
>
> How can I do count equivalent of such a query to find out how many
> distinct values of b there are for a given a? (That is get an answer of
> 2 in the above)
>
> Thanks
>
> --
> Matthew Jones
> Hewlett-Packard Ltd
> Long Down Avenue
> Stoke Gifford
> Bristol.  BS34 8QZ
> Tel:   +44 (0) 117 312 7490
> Email:matthew.jo...@hp.com
>
> Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks,
> RG12 1HN. Registered No: 690597 England
>
> The contents of this message and any attachments to it are confidential
> and may be legally privileged. If you have received this message in
> error, you should delete it from your system immediately and advise the
> sender.
>
> To any recipient of this message within HP, unless otherwise stated you
> should consider this message and attachments as "HP CONFIDENTIAL".
> ___
> 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] Out of memory? How could that be on a 32G iPhone?

[Rolf Marsh]

Thank you for the clarification... I appreciate it...

Regards,
Rolf

On 5/6/11 6:23 AM, Jean-Denis Muys wrote:
> On 6 mai 2011, at 15:09, Rolf Marsh wrote:
>
> I figured this out... it was a "pilot-error" on my part...but I do have
> an additional question (see below).
>
> On 5/5/11 9:45 AM, Jean-Denis Muys wrote:
> On 4 mai 2011, at 23:22, Rolf Marsh wrote:
>
>
>
>
> a 32GB iPhone doesn't have 32GB of RAM. It has 32GB of storage space. That's 
> vastly different.
>
> Storage space as in SQLite d/b?
>
> Storage space as in space available on your hard disk.
>
> If you run your app in the simulator, the available storage is literally the 
> available disk space on the OS X volume where your user account is stored. 
> You can find your application somewhere at ~/Library/Application 
> Support/iPhone Simulator/4.3.2/Applications. And your sqlite file is probably 
> in the Documents subdirectory there. You can examine it from the terminal, 
> look up its size, whatever.
>
> When you run your app on the device, the available storage is basically what 
> iTunes tells you is available in the nice bar graph at the bottom of the 
> summary screen.
> Also the *total* storage space is displayed by Xcode's Organizer window under 
> the "Capacity" designation.
>
> JD
>
>
>
> ___
> 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] Backing up online SQLite data files

[Jaco Breitenbach]

Hi Igor,

On 6 May 2011 14:15, Igor Tandetnik  wrote:

> Jaco Breitenbach  wrote:
> > My application has several data files open (one main, several other
> > attached) during normal operation.  The journaling mode is WAL.  I need
> to
> > perform an online backup of the data files without shutting down the
> > application.
>
> http://sqlite.org/backup.html
>
>
I've also looked at that page.  However, that either involves modifying the
main application or writing a separate backup utility application based on
the examples provided.

One thing that I've noticed, is that if the running database that is being
backed up is continuously being written to, then following a call to
sqlite3_sleep(), the backup will automatically be restarted.  This seems to
indicate that my backups might never complete.

Would a simple copy of the database, -shm and -wal files result in a
corrupted database if these files are used to restore the system, or would
it simply appear as if SQLite is recovering from a power failure?  Sure,
there may be a small amount of data loss in doing the copy without a lock,
but that may be acceptable so long as the data that is in the database is
not corrupted.

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

[sqlite] Query question

[Matthew Jones]

A simple query question that I really ought to know the answer to but don't:

Given a table with multiple columns in it how do I count how many 
entries there are with column A matching some value where column B is 
distinct?

sqlite> create table a (a, b);
sqlite> insert into a values (10, 1);
sqlite> insert into a values (10, 2);
sqlite> insert into a values (10, 2);
sqlite> insert into a values (11, 2);
sqlite> insert into a values (11, 2);
sqlite> insert into a values (11, 3);
sqlite> select * from a where a=10 group by b;
10|1
10|2
sqlite> select * from a where a=11 group by b;
11|2
11|3

How can I do count equivalent of such a query to find out how many 
distinct values of b there are for a given a? (That is get an answer of 
2 in the above)

Thanks

-- 
Matthew Jones
Hewlett-Packard Ltd
Long Down Avenue
Stoke Gifford
Bristol.  BS34 8QZ
Tel:   +44 (0) 117 312 7490
Email:matthew.jo...@hp.com

Hewlett-Packard Limited registered Office: Cain Road, Bracknell, Berks, 
RG12 1HN. Registered No: 690597 England

The contents of this message and any attachments to it are confidential 
and may be legally privileged. If you have received this message in 
error, you should delete it from your system immediately and advise the 
sender.

To any recipient of this message within HP, unless otherwise stated you 
should consider this message and attachments as "HP CONFIDENTIAL".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Addition: Re: SQLite locking question

[Pavel Ivanov]

>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.

Another suggestion could be to turn off shared cache mode.


Pavel


On Fri, May 6, 2011 at 7:15 AM, Dan Kennedy  wrote:
> On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
>> Hi Dan,
>>
 I have to correct myself a little... the hanging sqlite3_open_v2() does
 not happe while the prepare of the first query but during the first
 sqlite3_step() of the first query...
>>>
>>> Each shared-cache has a mutex associated with it. When
>>> sqlite3_step is called it grabs the mutexes for all shared-caches
>>> it might use. The mutexes are not released until the call
>>> to sqlite3_step() returns.
>>>
>>> So if you have one query that spends a lot of time in sqlite3_step()
>>> you are going to block your other threads. Unfortunately, that is
>>> the nature of shared-cache mode.
>>
>> Thanks for the info. But is this even true when enabling read
>> uncommitted isolation mode using:
>>
>> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
>> 0,&err)
>>
>> ??
>>
>> I suspect it's the master table lock that is hold there, right?
>
> You are correct that your query will hold a shared-lock on the
> master table at the shared cache level, but it's the mutex that
> is causing you problems. Shared-cache locks are held for the
> duration of a transaction to ensure transactions are correctly
> isolated. In this case the master table is locked to make sure
> that the table your query is accessing is not dropped by another
> thread while you are using it.
>
> Mutexes are held for the duration of an sqlite3_step() call to make
> sure SQLite doesn't segfault when two threads try to access the same
> shared-cache object.
>
>> Any other chance to speed this up (apart from the obvious "optimize the
>> query, do not use distinct on large tables)=
>
> Without seeing the query or database schema? Not really... Depending
> on the exact query an index on "xyz" might help.
>
> 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] Out of memory? How could that be on a 32G iPhone?

[Jean-Denis Muys]

On 6 mai 2011, at 15:09, Rolf Marsh wrote:

I figured this out... it was a "pilot-error" on my part...but I do have
an additional question (see below).

On 5/5/11 9:45 AM, Jean-Denis Muys wrote:
On 4 mai 2011, at 23:22, Rolf Marsh wrote:




a 32GB iPhone doesn't have 32GB of RAM. It has 32GB of storage space. That's 
vastly different.

Storage space as in SQLite d/b?

Storage space as in space available on your hard disk.

If you run your app in the simulator, the available storage is literally the 
available disk space on the OS X volume where your user account is stored. You 
can find your application somewhere at ~/Library/Application Support/iPhone 
Simulator/4.3.2/Applications. And your sqlite file is probably in the Documents 
subdirectory there. You can examine it from the terminal, look up its size, 
whatever.

When you run your app on the device, the available storage is basically what 
iTunes tells you is available in the nice bar graph at the bottom of the 
summary screen.
Also the *total* storage space is displayed by Xcode's Organizer window under 
the "Capacity" designation.

JD



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

Re: [sqlite] Backing up online SQLite data files

[Igor Tandetnik]

Jaco Breitenbach  wrote:
> My application has several data files open (one main, several other
> attached) during normal operation.  The journaling mode is WAL.  I need to
> perform an online backup of the data files without shutting down the
> application.

http://sqlite.org/backup.html

-- 
Igor Tandetnik

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

[sqlite] Backing up online SQLite data files

[Jaco Breitenbach]

Dear experts,

My application has several data files open (one main, several other
attached) during normal operation.  The journaling mode is WAL.  I need to
perform an online backup of the data files without shutting down the
application.

What is the risk in simply copying all files, i.e. data files, -shm and -wal
files to a backup directory?  Could the sqlite3 built-in .backup command be
used while the application is accessing the data files?  Is there a specific
backup utility available for SQLite?

Any comments and suggestions will be much appreciated.

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

Re: [sqlite] Out of memory? How could that be on a 32G iPhone?

[Rolf Marsh]

I figured this out... it was a "pilot-error" on my part...but I do have 
an additional question (see below).

On 5/5/11 9:45 AM, Jean-Denis Muys wrote:
> On 4 mai 2011, at 23:22, Rolf Marsh wrote:
>
>
>
> Prior to getting this error, I opened the d/b and inserted one (1) very
> small record...
> Where do I start looking?  I am using FMDB, ZBarSDK (used to read
> barcodes), but I can't imagine that's using all of my memory... and I
> have the d/b set to be a singleton, as indicated by the NSLog entries...
>
> How do I tell how much active memory I'm using?  Where do I start
> looking (I'm a newbie, as you can probably tell by now) :-P
>
>
> a 32GB iPhone doesn't have 32GB of RAM. It has 32GB of storage space. That's 
> vastly different.  Storage space as in SQLite d/b?
>
> How much RAM an iOS device has is not published by Apple. Of course, as a 
> developer, it's easy to find out. And the answer is:
>
> iPhone: 128 MB
> iPhone 3G:  can't remember
> iPhone 3GS: can't remember (and too lazy to lmgtfy.com  
> that).
> iPhone 4: 256 MB
>
> iPad: 256 MB
> iPad 2: 512 MB
>
> Regarding how much free RAM you still have at any one time, the function 
> commonly suggested looks like:
>
>
>
> natural_t  freeMemory(void) {
>  mach_port_t   host_port = mach_host_self();
>  mach_msg_type_number_t   host_size = sizeof(vm_statistics_data_t) / 
> sizeof(integer_t);
>  vm_size_t   pagesize;
>  vm_statistics_data_t vm_stat;
>
>  host_page_size(host_port,&pagesize);
>
>  if (host_statistics(host_port, HOST_VM_INFO, 
> (host_info_t)&vm_stat,&host_size) != KERN_SUCCESS) NSLog(@"Failed to fetch vm 
> statistics");
>
>  natural_t   mem_used = (vm_stat.active_count + vm_stat.inactive_count + 
> vm_stat.wire_count) * pagesize;
>  natural_t   mem_free = vm_stat.free_count * pagesize;
>  natural_t   mem_total = mem_used + mem_free;
>
>  return mem_free;
> }
>
> ___
> 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] System.Data.SQLite blob column exception

[Simon Slavin]

On 6 May 2011, at 3:43am, Tim Butterfield wrote:

> The blob values are always inserted and read as a byte[].  If those
> bytes are those of a text file, it fails.  If I add a leading 0x00
> byte to force it to be binary, it works.

Are you binding your parameters ?  If so, check that you're using 
sqlite3_bind_blob() when binding something to your BLOB column.  Binding it as 
text will not trigger an error, since SQLite is quite happy to bind text into a 
BLOB column.  But if you take blobby data, and bind it using 
sqlite3_bind_blob() into a column defined as a BLOB SQLite should be retaining 
and reporting it as as blob, not as text.

If you're creating a fully text 'INSERT' or 'UPDATE' command rather than 
binding parameters, them make sure you're using the correct format to specify 
you have blob data (is it \x ?) rather than just supplying text.

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

Re: [sqlite] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi Dan,

> > I have to correct myself a little... the hanging sqlite3_open_v2() does
> > not happe while the prepare of the first query but during the first
> > sqlite3_step() of the first query...
> 
> Each shared-cache has a mutex associated with it. When
> sqlite3_step is called it grabs the mutexes for all shared-caches
> it might use. The mutexes are not released until the call
> to sqlite3_step() returns.
> 
> So if you have one query that spends a lot of time in sqlite3_step()
> you are going to block your other threads. Unfortunately, that is
> the nature of shared-cache mode.

Thanks for the info. But is this even true when enabling read
uncommitted isolation mode using:

sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
0,&err)

??

I suspect it's the master table lock that is hold there, right?

Any other chance to speed this up (apart from the obvious "optimize the
query, do not use distinct on large tables)=

Bye,

Ole


-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Out of memory? How could that be on a 32G iPhone?

[Jean-Denis Muys]

On 4 mai 2011, at 23:22, Rolf Marsh wrote:



Prior to getting this error, I opened the d/b and inserted one (1) very
small record...
Where do I start looking?  I am using FMDB, ZBarSDK (used to read
barcodes), but I can't imagine that's using all of my memory... and I
have the d/b set to be a singleton, as indicated by the NSLog entries...

How do I tell how much active memory I'm using?  Where do I start
looking (I'm a newbie, as you can probably tell by now) :-P


a 32GB iPhone doesn't have 32GB of RAM. It has 32GB of storage space. That's 
vastly different.

How much RAM an iOS device has is not published by Apple. Of course, as a 
developer, it's easy to find out. And the answer is:

iPhone: 128 MB
iPhone 3G:  can't remember
iPhone 3GS: can't remember (and too lazy to lmgtfy.com that).
iPhone 4: 256 MB

iPad: 256 MB
iPad 2: 512 MB

Regarding how much free RAM you still have at any one time, the function 
commonly suggested looks like:



natural_t  freeMemory(void) {
mach_port_t   host_port = mach_host_self();
mach_msg_type_number_t   host_size = sizeof(vm_statistics_data_t) / 
sizeof(integer_t);
vm_size_t   pagesize;
vm_statistics_data_t vm_stat;

host_page_size(host_port, &pagesize);

if (host_statistics(host_port, HOST_VM_INFO, (host_info_t)&vm_stat, 
&host_size) != KERN_SUCCESS) NSLog(@"Failed to fetch vm statistics");

natural_t   mem_used = (vm_stat.active_count + vm_stat.inactive_count + 
vm_stat.wire_count) * pagesize;
natural_t   mem_free = vm_stat.free_count * pagesize;
natural_t   mem_total = mem_used + mem_free;

return mem_free;
}

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

Re: [sqlite] Addition: Re: SQLite locking question

[Dan Kennedy]

On 05/06/2011 05:53 PM, Ole Reinhardt wrote:
> Hi Dan,
>
>>> I have to correct myself a little... the hanging sqlite3_open_v2() does
>>> not happe while the prepare of the first query but during the first
>>> sqlite3_step() of the first query...
>>
>> Each shared-cache has a mutex associated with it. When
>> sqlite3_step is called it grabs the mutexes for all shared-caches
>> it might use. The mutexes are not released until the call
>> to sqlite3_step() returns.
>>
>> So if you have one query that spends a lot of time in sqlite3_step()
>> you are going to block your other threads. Unfortunately, that is
>> the nature of shared-cache mode.
>
> Thanks for the info. But is this even true when enabling read
> uncommitted isolation mode using:
>
> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
> 0,&err)
>
> ??
>
> I suspect it's the master table lock that is hold there, right?

You are correct that your query will hold a shared-lock on the
master table at the shared cache level, but it's the mutex that
is causing you problems. Shared-cache locks are held for the
duration of a transaction to ensure transactions are correctly
isolated. In this case the master table is locked to make sure
that the table your query is accessing is not dropped by another
thread while you are using it.

Mutexes are held for the duration of an sqlite3_step() call to make
sure SQLite doesn't segfault when two threads try to access the same
shared-cache object.

> Any other chance to speed this up (apart from the obvious "optimize the
> query, do not use distinct on large tables)=

Without seeing the query or database schema? Not really... Depending
on the exact query an index on "xyz" might help.

Dan.

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

Re: [sqlite] Addition: Re: SQLite locking question

[Dan Kennedy]

On 05/06/2011 05:17 PM, Ole Reinhardt wrote:
> Hi all,
>
> I have to correct myself a little... the hanging sqlite3_open_v2() does
> not happe while the prepare of the first query but during the first
> sqlite3_step() of the first query...

Each shared-cache has a mutex associated with it. When
sqlite3_step is called it grabs the mutexes for all shared-caches
it might use. The mutexes are not released until the call
to sqlite3_step() returns.

So if you have one query that spends a lot of time in sqlite3_step()
you are going to block your other threads. Unfortunately, that is
the nature of shared-cache mode.

Dan.



>
> So in pseudo code the database access looks like this:
>
> first thread:
>
> sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
> SQLITE_OPEN_NOMUTEX, NULL);
> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
> 0,&err)
> sqlite3_prepare_v2(log_database_local, "select distinct xyz from log
> where ...)
>
> while(sqlite3_step(sql_stmt) == SQLITE_ROW)...
>
> While every step takes _very_ long time as the log table has quite a lot
> entries (>  5.000.000)
>
>
>
> second thread:
>
> sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
> SQLITE_OPEN_NOMUTEX, NULL);
>
> --->  This sqlite3_open_v2 does not return until the prepare
> sqlite3_step() statement of the first thread has completed...
>
>
> again: Any help or short hint would be very appreciated!
>
> Thanks in advance,
>
> Ole Reinhardt
>

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

Re: [sqlite] Confused

[arjabh say]

Thanks for the help.
I will go for creating separate indexes for my solution.

Regards,
Arjabh
On Fri, May 6, 2011 at 3:23 PM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hello Arjabh,
>
> This is a good approach.
>
> I am not sure what you mean by "affecting a query". Every index
> - makes your database larger
> - slows down inserts (a little bit)
> - speeds up select/update/delete (often dramatically)
>
> Concerning Columns B an C:
> If your deletes are of the form "delete from MyTable where B = 15" and
> "delete from MyTable where C = 15", you should create two separate
> indexes on B and C.
> If your deletes are of the form  "delete from MyTable where B = 15 AND C
> = 15", you should create one multi-column-index on both columns B and C.
>
> Martin
>
> Am 06.05.2011 11:19, schrieb arjabh say:
>  > Thank you Martin.
> > That was very helpful.
> >
> > According to project requirement, mostly I have to do select query on
> Column
> > A, and delete query based on columns B and C.
> > I created index for the columns B and C as well, and now the queries are
> > running pretty fast (completes in ms :D ).
> >
> > Is this a good approach ?
> > Creating indexes affects the insert/update/delete query, isnt it?
> >
> > Thanks and Regards,
> > Arjabh
> > On Fri, May 6, 2011 at 2:06 PM, Martin Engelschalk<
> > engelsch...@codeswift.com>  wrote:
> >
> >> Hello Arjabh,
> >>
> >> the autoindex is created for the rowid, because you did not define a
> >> column with type "integer primary key". See here:
> >> http://www.sqlite.org/lang_createtable.html#rowid
> >>
> >> SQLite can only use an index for where - clause in your statements if
> >> the columns in the where clause are the same as the first columns of an
> >> index.
> >> As you have an index (the primary key) on columnns A and B, a where
> >> clause like "where a = 15" will use this index, A where clause like
> >> "where a = 15 and b = 17" will also find the rows using this index. Such
> >> a query will be very fast.
> >>
> >> If you fire a query using other columns or combinations of columns (like
> >> "where c = 1"), SQLite has to scan the full table for rows matching
> >> this. This will take a long time, depending on the size of the table.
> >>
> >> You might want to read this for further information:
> >> http://www.sqlite.org/queryplanner.html
> >>
> >> Martin
> >>
> >> Am 06.05.2011 06:17, schrieb arjabh say:
> >>   >  Hi All,
> >>> I have a sqlite database with single table with columns A, B and C.
> >>> I have created composite primary key on A and B, and an autoindex was
> >>> created (dont know on which columns this index is created).
> >>>
> >>> When I fire select/delete query with WHERE clause on column A, it is
> >>> completed in milliseconds, whereas when I fire the same kind of query
> on
> >>> basis of columns B or C, it takes couple of minutes.
> >>> What is causing this difference?
> >>>
> >>> A point to note: The table contains millions of rows.
> >>>
> >>> Thanks and Regards,
> >>> Arjabh
> >>> ___
> >>> 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
> ___
> 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] Addition: Re: SQLite locking question

[Ole Reinhardt]

Hi all,

I have to correct myself a little... the hanging sqlite3_open_v2() does
not happe while the prepare of the first query but during the first
sqlite3_step() of the first query...

So in pseudo code the database access looks like this:

first thread:

sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
SQLITE_OPEN_NOMUTEX, NULL);
sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL,
0, &err)
sqlite3_prepare_v2(log_database_local, "select distinct xyz from log
where ...)

while(sqlite3_step(sql_stmt) == SQLITE_ROW)...

While every step takes _very_ long time as the log table has quite a lot
entries (> 5.000.000)



second thread:

sqlite3_open_v2("log.sqlite", SQLITE_OPEN_READONLY |
SQLITE_OPEN_NOMUTEX, NULL);

---> This sqlite3_open_v2 does not return until the prepare
sqlite3_step() statement of the first thread has completed...


again: Any help or short hint would be very appreciated!

Thanks in advance,

Ole Reinhardt

-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

[sqlite] SQLite locking question

[Ole Reinhardt]

Hi all,

we have a little problem with the locking of sqlite connections in
combination with the shared cache model.

We are using sqlite 3.6.10 (on Ubuntu 9.04) or later. Same problem
occurs on the latest ubuntu version with sqlite 3.7.4.

Problem:

We have a multi threaded application that connects from different
threads to a common sqlite database that contains just one large table
(kind of log).

We have enabled shared cache globaly and open the database connections
for read/write from the main thread and read only / multi threading
enabled (SQLITE_OPEN_READONLY | SQLITE_OPEN_NOMUTEX) from the other
threads.

After opening the database connection "Read-Uncommitted Isolation Mode"
is directly enabled using 

if (sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;",
NULL, 0, &err) != SQLITE_OK) {...




If the log table is well filled (~ 5.000.000 entries) we do different
requests on this table from different threads. They are read-only right
now.

One request takes very long time to complete (select distinct xyz
from ... where ...).

While this request is processed, opening the same database from another
thread blocks until the sqlite3_prepare for the first thread is
completed, even if shared cache model is enabled.



Is there any chance to open the same database from different threads to
do read-only requests _without_ blocking the requests at any place?

We need to be able to read the contents from the table in parallel from
different threads even if one single database requests takes very long
time.


Any help or short hint would be very appreciated!

Thanks in advance,

Ole Reinhardt

-- 

Thermotemp GmbH, Embedded-IT

Embedded Hard-/ Software and Open Source Development, 
Integration and Consulting

http://www.embedded-it.de

Geschäftsstelle Siegen - Steinstraße 67 - D-57072 Siegen - 
tel +49 (0)271 5513597, +49 (0)271-73681 - fax +49 (0)271 736 97

Hauptsitz - Hademarscher Weg 7 - 13503 Berlin
Tel +49 (0)30 4315205 - Fax +49 (0)30 43665002
Geschäftsführer: Jörg Friedrichs, Ole Reinhardt
Handelsregister Berlin Charlottenburg HRB 45978 UstID DE 156329280 

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

Re: [sqlite] Confused

[Martin Engelschalk]

Hello Arjabh,

This is a good approach.

I am not sure what you mean by "affecting a query". Every index
- makes your database larger
- slows down inserts (a little bit)
- speeds up select/update/delete (often dramatically)

Concerning Columns B an C:
If your deletes are of the form "delete from MyTable where B = 15" and 
"delete from MyTable where C = 15", you should create two separate 
indexes on B and C.
If your deletes are of the form  "delete from MyTable where B = 15 AND C 
= 15", you should create one multi-column-index on both columns B and C.

Martin

Am 06.05.2011 11:19, schrieb arjabh say:
> Thank you Martin.
> That was very helpful.
>
> According to project requirement, mostly I have to do select query on Column
> A, and delete query based on columns B and C.
> I created index for the columns B and C as well, and now the queries are
> running pretty fast (completes in ms :D ).
>
> Is this a good approach ?
> Creating indexes affects the insert/update/delete query, isnt it?
>
> Thanks and Regards,
> Arjabh
> On Fri, May 6, 2011 at 2:06 PM, Martin Engelschalk<
> engelsch...@codeswift.com>  wrote:
>
>> Hello Arjabh,
>>
>> the autoindex is created for the rowid, because you did not define a
>> column with type "integer primary key". See here:
>> http://www.sqlite.org/lang_createtable.html#rowid
>>
>> SQLite can only use an index for where - clause in your statements if
>> the columns in the where clause are the same as the first columns of an
>> index.
>> As you have an index (the primary key) on columnns A and B, a where
>> clause like "where a = 15" will use this index, A where clause like
>> "where a = 15 and b = 17" will also find the rows using this index. Such
>> a query will be very fast.
>>
>> If you fire a query using other columns or combinations of columns (like
>> "where c = 1"), SQLite has to scan the full table for rows matching
>> this. This will take a long time, depending on the size of the table.
>>
>> You might want to read this for further information:
>> http://www.sqlite.org/queryplanner.html
>>
>> Martin
>>
>> Am 06.05.2011 06:17, schrieb arjabh say:
>>   >  Hi All,
>>> I have a sqlite database with single table with columns A, B and C.
>>> I have created composite primary key on A and B, and an autoindex was
>>> created (dont know on which columns this index is created).
>>>
>>> When I fire select/delete query with WHERE clause on column A, it is
>>> completed in milliseconds, whereas when I fire the same kind of query on
>>> basis of columns B or C, it takes couple of minutes.
>>> What is causing this difference?
>>>
>>> A point to note: The table contains millions of rows.
>>>
>>> Thanks and Regards,
>>> Arjabh
>>> ___
>>> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused

[arjabh say]

Thank you Martin.
That was very helpful.

According to project requirement, mostly I have to do select query on Column
A, and delete query based on columns B and C.
I created index for the columns B and C as well, and now the queries are
running pretty fast (completes in ms :D ).

Is this a good approach ?
Creating indexes affects the insert/update/delete query, isnt it?

Thanks and Regards,
Arjabh
On Fri, May 6, 2011 at 2:06 PM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:

> Hello Arjabh,
>
> the autoindex is created for the rowid, because you did not define a
> column with type "integer primary key". See here:
> http://www.sqlite.org/lang_createtable.html#rowid
>
> SQLite can only use an index for where - clause in your statements if
> the columns in the where clause are the same as the first columns of an
> index.
> As you have an index (the primary key) on columnns A and B, a where
> clause like "where a = 15" will use this index, A where clause like
> "where a = 15 and b = 17" will also find the rows using this index. Such
> a query will be very fast.
>
> If you fire a query using other columns or combinations of columns (like
> "where c = 1"), SQLite has to scan the full table for rows matching
> this. This will take a long time, depending on the size of the table.
>
> You might want to read this for further information:
> http://www.sqlite.org/queryplanner.html
>
> Martin
>
> Am 06.05.2011 06:17, schrieb arjabh say:
>  > Hi All,
> >
> > I have a sqlite database with single table with columns A, B and C.
> > I have created composite primary key on A and B, and an autoindex was
> > created (dont know on which columns this index is created).
> >
> > When I fire select/delete query with WHERE clause on column A, it is
> > completed in milliseconds, whereas when I fire the same kind of query on
> > basis of columns B or C, it takes couple of minutes.
> > What is causing this difference?
> >
> > A point to note: The table contains millions of rows.
> >
> > Thanks and Regards,
> > Arjabh
> > ___
> > 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] Confused

[Martin Engelschalk]

Hello Arjabh,

the autoindex is created for the rowid, because you did not define a 
column with type "integer primary key". See here: 
http://www.sqlite.org/lang_createtable.html#rowid

SQLite can only use an index for where - clause in your statements if 
the columns in the where clause are the same as the first columns of an 
index.
As you have an index (the primary key) on columnns A and B, a where 
clause like "where a = 15" will use this index, A where clause like 
"where a = 15 and b = 17" will also find the rows using this index. Such 
a query will be very fast.

If you fire a query using other columns or combinations of columns (like 
"where c = 1"), SQLite has to scan the full table for rows matching 
this. This will take a long time, depending on the size of the table.

You might want to read this for further information: 
http://www.sqlite.org/queryplanner.html

Martin

Am 06.05.2011 06:17, schrieb arjabh say:
> Hi All,
>
> I have a sqlite database with single table with columns A, B and C.
> I have created composite primary key on A and B, and an autoindex was
> created (dont know on which columns this index is created).
>
> When I fire select/delete query with WHERE clause on column A, it is
> completed in milliseconds, whereas when I fire the same kind of query on
> basis of columns B or C, it takes couple of minutes.
> What is causing this difference?
>
> A point to note: The table contains millions of rows.
>
> Thanks and Regards,
> Arjabh
> ___
> 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