Re: [sqlite] Widen output of file field for .databases CLI command

[Clark Christensen]

Hi Keith,

How about

pragma database_list;

 -Clark




From: Keith Christian 
To: sqlite-users@sqlite.org
Sent: Thursday, August 25, 2011 8:55 AM
Subject: [sqlite] Widen output of file field for .databases CLI command

Is there a parameter that will widen the "file" column when
".databases" is typed at the sqlite> prompt?  Occasionally the
database is several directories deep and the filename is lost or
truncated if the complete path is more than 58 characters wide.

If no parameter exists, could the "file" field be widened?

Keith
___
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] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 11:00pm, François wrote:

> I just have a last question: you are telling that opening connection
> at app launch and close it only at app exit (scenario A) may be less
> secure than open/close database at each transaction (scenario B), on a
> data integrity point of view. But you gave the contrary advice at the
> beginning of our exchanges. Why?

I'm not sure what you mean by 'secure'.  Opening and closing database 
connections takes far more time than just doing a transaction.  It's just like 
opening and closing a data file on disk: you don't tend to do it unnecessarily.

> Let's consider a simple example of a database used by only one iOS
> app, which performs many INSERT or UPDATE during a user session.
> Therefore it would be better to minimize data loss risk.

Generally, open a connection when you know you're going to need the database, 
and close it again when you know you're not going to need the database again, 
just as if you were reading or writing a text file.

I think you're doing premature optimization.  Don't worry too much about data 
loss.  You have to put some trust into the platform you're using, especially a 
hugely mass-produced item like an iPhone.  If you were writing for a 
life-critical system used in conditions where electronic devices crash often, 
things would be different.

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

Re: [sqlite] When open / close database on IOS ?

[François]

OK thank you for these new explanations.

I just have a last question: you are telling that opening connection
at app launch and close it only at app exit (scenario A) may be less
secure than open/close database at each transaction (scenario B), on a
data integrity point of view. But you gave the contrary advice at the
beginning of our exchanges. Why?

Let's consider a simple example of a database used by only one iOS
app, which performs many INSERT or UPDATE during a user session.
Therefore it would be better to minimize data loss risk.

Best Regards,

François



On 25 août, 23:36, Simon Slavin  wrote:
> On 25 Aug 2011, at 9:54pm, François wrote:
>
> > On 25 août, 19:29, Simon Slavin  wrote:
> >> When you use sqlite3_open() a database file that routine checks for many 
> >> signs that the database wasn't closed properly -- in other
> >> words that an app which previously had it open crashed rather than using 
> >> sqlite3_close().  It then uses all the clues available in the
> >> database file and any journal files to restore the database to a 'safe' 
> >> situation.
>
> > So we just have to close database in applicationWillterminate and
> > applicationWillResignActive methods because SQLite handles crashes
> > itself. This is amazing!
>
> If your application crashes you can't make it do anything else.  So you have 
> no other option.  Just think of it as if there's a 'rescue utility' built 
> into sqlite3_open().  It's not a perfect utility, and can't rescue from all 
> possible kinds of corruption, but it will handle the results of most kinds of 
> crashes and leave you with a usable database.
>
> > Wow it confuses me. In 3) you told that when app crashes outside a
> > transaction and before database close, there is no data loss.
>
> No.  The recovery after a database has crashed does not make sure there is no 
> data loss.  It tries to make sure the database is restored to a COMMIT point, 
> not half way through a transaction, or in a corrupt state where, for 
> instance, a row has been added to a table but the table's indexes haven't 
> been updated.  The recovery process could, for instance, lose an entire 
> transaction if you were unlucky with your crash's timing.
>
> > And now
> > you tell that until database is closed, data are not flushed to the
> > disk. I was thinking that flush to disk was performed at transaction
> > commit because for example, performing N INSERT requests is much
> > faster within a transaction than within N implicit ones. Please excuse
> > me but can you explain this to me ? I am quite confused now :-)
>
> The only time you can be sure that the SQLite database file on disk perfectly 
> reflects the state of your database is when you don't have any connections 
> open, and have given caches time to empty, and have given your disk hardware 
> time to do what it promised the motherboard it would do.  SQLite may or may 
> not choose to update the database file on disk at any time while you have the 
> connection open.  That's not your problem.
>
> If SQLite waited for a full and proper update of the disk file every time you 
> closed a transaction it would work very slowly indeed.  Nothing actually 
> works like that these days: we're used to computers that tell the user "I did 
> it" but then do a lot of the work afterwards.
>
> >> iPhones make this all very confusing, because working memory isn't very 
> >> different to 'disk' memory, and once apps are started they
> >> normally continue running until the device shuts down because the battery 
> >> runs low.  But these things were designed when things were
> >> different.
>
> > Are you telling that when you commit a transaction, data are "flushed"
> > into a "in-memory cache" version of the database, and when database is
> > closed then this cache is flushed to the database file ?
>
> No.  Sorry but this is more complicated than you have to care about at first 
> glance.  Please think of transactions and connections are two completely 
> different things.  You can write your program, for example, that until you've 
> committed a transaction, /nothing/ is written to disk.  Perhaps all the 
> INSERT and UPDATE commands are just held as text in memory until a SELECT or 
> COMMIT, with no data generated at all.  Certainly if you have two different 
> apps with connections to the same database, app1 could open a transaction, do 
> a thousand INSERTs, then wait for user input for three hours, and app2 won't 
> see any changes until app1 has done a COMMIT.  That's how SQL is designed.
>
> If you want to make sure a SQLite database on disk reflects the changes 
> you've made to it, close all your database connections.  Until then your 
> changes may be in journal files or in the memory associated with the app that 
> made the change.
>
> If you write your software the way the documentation says, it'll work fine.  
> If you want to learn more about the deep considerations behind your 
> 

Re: [sqlite] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 9:54pm, François wrote:

> On 25 août, 19:29, Simon Slavin  wrote:
>> When you use sqlite3_open() a database file that routine checks for many 
>> signs that the database wasn't closed properly -- in other
>> words that an app which previously had it open crashed rather than using 
>> sqlite3_close().  It then uses all the clues available in the
>> database file and any journal files to restore the database to a 'safe' 
>> situation.
> 
> So we just have to close database in applicationWillterminate and
> applicationWillResignActive methods because SQLite handles crashes
> itself. This is amazing!

If your application crashes you can't make it do anything else.  So you have no 
other option.  Just think of it as if there's a 'rescue utility' built into 
sqlite3_open().  It's not a perfect utility, and can't rescue from all possible 
kinds of corruption, but it will handle the results of most kinds of crashes 
and leave you with a usable database.

> Wow it confuses me. In 3) you told that when app crashes outside a
> transaction and before database close, there is no data loss.

No.  The recovery after a database has crashed does not make sure there is no 
data loss.  It tries to make sure the database is restored to a COMMIT point, 
not half way through a transaction, or in a corrupt state where, for instance, 
a row has been added to a table but the table's indexes haven't been updated.  
The recovery process could, for instance, lose an entire transaction if you 
were unlucky with your crash's timing.

> And now
> you tell that until database is closed, data are not flushed to the
> disk. I was thinking that flush to disk was performed at transaction
> commit because for example, performing N INSERT requests is much
> faster within a transaction than within N implicit ones. Please excuse
> me but can you explain this to me ? I am quite confused now :-)

The only time you can be sure that the SQLite database file on disk perfectly 
reflects the state of your database is when you don't have any connections 
open, and have given caches time to empty, and have given your disk hardware 
time to do what it promised the motherboard it would do.  SQLite may or may not 
choose to update the database file on disk at any time while you have the 
connection open.  That's not your problem.

If SQLite waited for a full and proper update of the disk file every time you 
closed a transaction it would work very slowly indeed.  Nothing actually works 
like that these days: we're used to computers that tell the user "I did it" but 
then do a lot of the work afterwards.

>> iPhones make this all very confusing, because working memory isn't very 
>> different to 'disk' memory, and once apps are started they
>> normally continue running until the device shuts down because the battery 
>> runs low.  But these things were designed when things were
>> different.
> 
> Are you telling that when you commit a transaction, data are "flushed"
> into a "in-memory cache" version of the database, and when database is
> closed then this cache is flushed to the database file ?

No.  Sorry but this is more complicated than you have to care about at first 
glance.  Please think of transactions and connections are two completely 
different things.  You can write your program, for example, that until you've 
committed a transaction, /nothing/ is written to disk.  Perhaps all the INSERT 
and UPDATE commands are just held as text in memory until a SELECT or COMMIT, 
with no data generated at all.  Certainly if you have two different apps with 
connections to the same database, app1 could open a transaction, do a thousand 
INSERTs, then wait for user input for three hours, and app2 won't see any 
changes until app1 has done a COMMIT.  That's how SQL is designed.

If you want to make sure a SQLite database on disk reflects the changes you've 
made to it, close all your database connections.  Until then your changes may 
be in journal files or in the memory associated with the app that made the 
change.

If you write your software the way the documentation says, it'll work fine.  If 
you want to learn more about the deep considerations behind your questions, read



>  And that on
> iPhone/iPad, the "memory cache" is in fact the disk ?

It's just that there is no hard disk in an iPhone, just dynamic RAM and Flash 
RAM.  So really everything's in RAM.

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

Re: [sqlite] When open / close database on IOS ?

[François]

On 25 août, 19:29, Simon Slavin  wrote:
> On 25 Aug 2011, at 5:19pm, François wrote:
>
> > 3) So SQLite correctly deals with crashes (this is amazing!) But when
> > app crashes, database connection may be not closed. Could it be a
> > problem? For example, preventing from re-opening or reading database
> > later ?
>
> When you use sqlite3_open() a database file that routine checks for many 
> signs that the database wasn't closed properly -- in other
> words that an app which previously had it open crashed rather than using 
> sqlite3_close().  It then uses all the clues available in the
> database file and any journal files to restore the database to a 'safe' 
> situation.

So we just have to close database in applicationWillterminate and
applicationWillResignActive methods because SQLite handles crashes
itself. This is amazing!

> If you were part way through a transaction, then none of the operations in 
>that transaction will be present in the 'safe' version.

OK. This is quite obvious for me since crash occurs before transaction
commit.

> > 4) As far as I understand, data integrity is handled by database
> > transactions, not by database close: I mean that data are flushed into
> > database file when transactions are committed, not when database is
> > closed. Is this true ? If yes, what is the exact role of the close ?
>
> It is unfortunate that the way computers work seems to tie these things 
> together but conceptually they are completely different.  And
> I'm sorry but for brevity's sake the following leaves out some picky details 
> about how SQLite actually works.
>
> Transactions are about programming and connections between data.  For 
> instance, you might want to INSERT an invoice and UPDATE that
> client's balance and either both of those things must be done or neither.  
> Transactions are about making sure that related changes to
> your data happen 'at the same time' even though, because SQL has no commands 
> which change more than one TABLE, you cannot issue one
> command that makes both changes.  You can use transactions in your software: 
> you can start a transaction, raise an invoice, update a
> client's balance, then read the new balance and notice it's less than zero, 
> and cancel all those changes by issuing ROLLBACK instead of
> COMMIT.  Everything inside SQLite happens in a transaction.  If you fail to 
> do BEGIN yourself before doing UPDATE, SQLite actually does
> BEGIN; UPDATE; COMMIT or nothing could be done.
>

Yes, it a quite traditional transaction definition.

> Closing the database connection is about files on disk.  While your app has a 
> connection to a database the files on the disk may not
> clearly reflect the true state of your data.  Various changes may be partly 
> made.  Two or more different programs may have transactions
> open.  You may be using some form of caching to delay changes so you can make 
> them all at once.  So if you have a SQLite application
> running while you take a complete backup of your hard disk, the data copied 
> with the file may be weird in all sorts of ways.  The
> sqlite3_close() command says "Save all this to disk, so I can forget it for 
> the night and it'll all be there for the morning.".  It
> makes sure that all your change are neatly expressed in files on disk, 
> instead of them being partly on disk and partly in the memory of
> some apps you're running.

Wow it confuses me. In 3) you told that when app crashes outside a
transaction and before database close, there is no data loss. And now
you tell that until database is closed, data are not flushed to the
disk. I was thinking that flush to disk was performed at transaction
commit because for example, performing N INSERT requests is much
faster within a transaction than within N implicit ones. Please excuse
me but can you explain this to me ? I am quite confused now :-)

> iPhones make this all very confusing, because working memory isn't very 
> different to 'disk' memory, and once apps are started they
> normally continue running until the device shuts down because the battery 
> runs low.  But these things were designed when things were
> different.

Are you telling that when you commit a transaction, data are "flushed"
into a "in-memory cache" version of the database, and when database is
closed then this cache is flushed to the database file ? And that on
iPhone/iPad, the "memory cache" is in fact the disk ?

Thank you,

Best Regards,


> Simon.
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://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] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 5:19pm, François wrote:

> 3) So SQLite correctly deals with crashes (this is amazing!) But when
> app crashes, database connection may be not closed. Could it be a
> problem? For example, preventing from re-opening or reading database
> later ?

When you use sqlite3_open() a database file that routine checks for many signs 
that the database wasn't closed properly -- in other words that an app which 
previously had it open crashed rather than using sqlite3_close().  It then uses 
all the clues available in the database file and any journal files to restore 
the database to a 'safe' situation.  If you were part way through a 
transaction, then none of the operations in that transaction will be present in 
the 'safe' version.

> 4) As far as I understand, data integrity is handled by database
> transactions, not by database close: I mean that data are flushed into
> database file when transactions are committed, not when database is
> closed. Is this true ? If yes, what is the exact role of the close ?

It is unfortunate that the way computers work seems to tie these things 
together but conceptually they are completely different.  And I'm sorry but for 
brevity's sake the following leaves out some picky details about how SQLite 
actually works.

Transactions are about programming and connections between data.  For instance, 
you might want to INSERT an invoice and UPDATE that client's balance and either 
both of those things must be done or neither.  Transactions are about making 
sure that related changes to your data happen 'at the same time' even though, 
because SQL has no commands which change more than one TABLE, you cannot issue 
one command that makes both changes.  You can use transactions in your 
software: you can start a transaction, raise an invoice, update a client's 
balance, then read the new balance and notice it's less than zero, and cancel 
all those changes by issuing ROLLBACK instead of COMMIT.  Everything inside 
SQLite happens in a transaction.  If you fail to do BEGIN yourself before doing 
UPDATE, SQLite actually does BEGIN; UPDATE; COMMIT or nothing could be done.

Closing the database connection is about files on disk.  While your app has a 
connection to a database the files on the disk may not clearly reflect the true 
state of your data.  Various changes may be partly made.  Two or more different 
programs may have transactions open.  You may be using some form of caching to 
delay changes so you can make them all at once.  So if you have a SQLite 
application running while you take a complete backup of your hard disk, the 
data copied with the file may be weird in all sorts of ways.  The 
sqlite3_close() command says "Save all this to disk, so I can forget it for the 
night and it'll all be there for the morning.".  It makes sure that all your 
change are neatly expressed in files on disk, instead of them being partly on 
disk and partly in the memory of some apps you're running.

iPhones make this all very confusing, because working memory isn't very 
different to 'disk' memory, and once apps are started they normally continue 
running until the device shuts down because the battery runs low.  But these 
things were designed when things were different.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[GB]

Simon Slavin schrieb am 25.08.2011 02:00:
> Had you thought of creating an explicit index on the rowid column, then 
> running ANALYZE again ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I tried that, with the same result as before. It still prefers the rowid.

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

Re: [sqlite] When open / close database on IOS ?

[François]

Simon,

Thank you for this so fast answer!

1) OK, this is exactly what I was expecting.

2) OK, you perfectly understood my question.

Let me add two linked questions:

3) So SQLite correctly deals with crashes (this is amazing!) But when
app crashes, database connection may be not closed. Could it be a
problem? For example, preventing from re-opening or reading database
later ?

4) As far as I understand, data integrity is handled by database
transactions, not by database close: I mean that data are flushed into
database file when transactions are committed, not when database is
closed. Is this true ? If yes, what is the exact role of the close ?

Thank you

Best Regards,

François


On 25 août, 17:53, Simon Slavin  wrote:
> On 25 Aug 2011, at 4:32pm, François wrote:
>
> > 1) Do you suggest to open only one connection for all database
> > requests in the application, and to close it when user exits from the
> > application (scenario A) ? Or to use one connection for each
> > transaction (scenario B) ?
>
> One connection for all transactions should be fine.  Unless your application 
> needs database access while it's in the background, the connection should be 
> closed when the application is shifted to the background or, of course, quit. 
>  Open the connection again either when the app is foregrounded, or the first 
> time you need SQLite access and haven't opened the connection yet.
>
> > 2) If scenario A, how to be sure that database is properly closed when
> > user exits ? I mean, using applicationWillTerminate and
> > applicationWillResignActive methods helps but this is not sufficient
> > because there are other cases where user exists. For example, if he
> > kills himself the app or if it crashes.
>
> If the user kills the app properly, using the app row, the app should still 
> receive applicationWillTerminate.  I think the only situation when it does 
> not receive this is when the operating system decides the app has stopped 
> responding to events -- like the 'Force Quit' situation for OS X.  In which 
> case, we are dealing with a crash, so let's consider that part of the 
> question.
>
> I have checked the operation of SQLite on iOS under crashing situations and 
> it seems to correctly deal with this situation, recovering data from journal 
> files and restoring to an uncorrupted state, just as it does on other 
> platforms.  My testing was with a simple database and normal transactions so 
> I didn't try many complicated or unusual situations, but everything I tried 
> worked as expected.
>
> There are some aspects of operation under iOS 5 I can't comment on here, but 
> there will be no real problems if you follow Apple's guidelines for startup 
> and shutdown of iOS apps.
>
> I will also be interested in answers from other programmers.  Writing apps 
> for the iPhone isn't like normal programming !
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-us...@sqlite.orghttp://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] Widen output of file field for .databases CLI command

[Keith Christian]

Is there a parameter that will widen the "file" column when
".databases" is typed at the sqlite> prompt?  Occasionally the
database is several directories deep and the filename is lost or
truncated if the complete path is more than 58 characters wide.

If no parameter exists, could the "file" field be widened?

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

Re: [sqlite] When open / close database on IOS ?

[Simon Slavin]

On 25 Aug 2011, at 4:32pm, François wrote:

> 1) Do you suggest to open only one connection for all database
> requests in the application, and to close it when user exits from the
> application (scenario A) ? Or to use one connection for each
> transaction (scenario B) ?

One connection for all transactions should be fine.  Unless your application 
needs database access while it's in the background, the connection should be 
closed when the application is shifted to the background or, of course, quit.  
Open the connection again either when the app is foregrounded, or the first 
time you need SQLite access and haven't opened the connection yet.

> 2) If scenario A, how to be sure that database is properly closed when
> user exits ? I mean, using applicationWillTerminate and
> applicationWillResignActive methods helps but this is not sufficient
> because there are other cases where user exists. For example, if he
> kills himself the app or if it crashes.

If the user kills the app properly, using the app row, the app should still 
receive applicationWillTerminate.  I think the only situation when it does not 
receive this is when the operating system decides the app has stopped 
responding to events -- like the 'Force Quit' situation for OS X.  In which 
case, we are dealing with a crash, so let's consider that part of the question.

I have checked the operation of SQLite on iOS under crashing situations and it 
seems to correctly deal with this situation, recovering data from journal files 
and restoring to an uncorrupted state, just as it does on other platforms.  My 
testing was with a simple database and normal transactions so I didn't try many 
complicated or unusual situations, but everything I tried worked as expected.

There are some aspects of operation under iOS 5 I can't comment on here, but 
there will be no real problems if you follow Apple's guidelines for startup and 
shutdown of iOS apps.

I will also be interested in answers from other programmers.  Writing apps for 
the iPhone isn't like normal programming !

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

[sqlite] When open / close database on IOS ?

[François]

Hello,

Just two questions concerning iPhone/iPad apps:

1) Do you suggest to open only one connection for all database
requests in the application, and to close it when user exits from the
application (scenario A) ? Or to use one connection for each
transaction (scenario B) ?

2) If scenario A, how to be sure that database is properly closed when
user exits ? I mean, using applicationWillTerminate and
applicationWillResignActive methods helps but this is not sufficient
because there are other cases where user exists. For example, if he
kills himself the app or if it crashes.

Thank you !

Best Regards,

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

Re: [sqlite] Split Function for SQLite?

[Max Vlasov]

On Thu, Aug 25, 2011 at 9:34 AM, Max Vlasov  wrote:
>
> Also theoretically it is possible to use virtual tables for this. So
> when your virtual query implementation accepts list in some way ('23,
> 14, 1, 7, 9') and returns the table when querying
>

I tried to implement something like this and it seems it works (very
easy for anyone familiar with virtual tables). The table itself
doesn't need any init data (and doesn't even contain) and absorbs data
from the query itself

So finally the db/program that wants to use it creates it with something like
  CREATE VIRTUAL TABLE cmlist Using vtcommalist
Table format
  CREATE TABLE [xxx] ([CommaList] TEXT, [Value] TEXT)
And example query is
  SELECT value FROM cmlist WHERE commalist='45,56,78,125'

So we finally got
"45"
"56"
"78"
"125"

Internally my xFilter just stores the commalist from the query and
uses it for First/Next

The only problem with this approach is that rowid makes sense only for
queries mentioned.
So
  SELECT rowid FROM cmlist
can't return valid results

I wonder can it be a problem for complex queries when sqlite decides
itself what to query and maybe relies on the correctness of rowid.

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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Carlos Rocha]

>
> It seems equally logical to me that one of A or B might be evaluated, and if 
> it were false, then the other might not be evaluated.

I don't think so if efficiency matters. Of course the rule could be to 
evaluated from right to left instead, but it's good to have just one 
rule, and again, it seems logical to me that it should be from left to right

>
> And it would be logical to choose which of A or B to evaluated on a predicted 
> cost and probability of an advantageous false result.

I don't see how predicting cost and probability could help here.
rowID BETWEEN 100 AND 200 are roughly 1M, and createdAt BETWEEN 
'2011-08-01' AND '2011-08-02' could be 10M. In a simple case like this 
it's good to leave the wheel to who knows the db.

>
> but hay.  Who said their could only be one logical approach.
>
>   Alex
>
>
> On 24 Aug 2011, at 20:12, Carlos Rocha wrote:
>
>> Don't know how SQLite should behave in this case, but seems logical to
>> me that A and B would force that A is always evaluated, and B is
>> evaluated only if A is true.
>> I would change the order of the two betweens:
>>
>> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND
>> '2011-08-02' AND itemID BETWEEN 100 AND 200
>>
>>> Hi all,
>>>
>>> I have a table like this:
>>>
>>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>>> CREATE INDEX createIdx on t(createdAt);
>>>
>>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>>> current content.
>>>
>>> When perfoming a Statement like this:
>>>
>>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>>>
>>> the analyzer always chooses the rowid index which results in a scan over
>>> one million rows. It would have to scan only a few dozen rows if it
>>> chose createIdx instead (which is also a covering index). Looking at the
>>> sqlite_stat2 table shows that there is no data for the rowid index.
>>> Could this be the reason for the suboptimal query plan? The choice works
>>> as expected if itemID is a regular column with an index on it.
>>>
>>> regards
>>> gerd
>>> ___
>>> 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] Custom Collation comparing only firt character?

[Simon Slavin]

On 25 Aug 2011, at 6:58am, Roberto Colnaghi wrote:

> int collationAnyCIAI(void *arg1, int str1Length, const void *str1, int 
> str2Length, const void *str2) {
>   NSString *strA = [NSString hexStringWithData:str1 ofLength:1];
>   NSString *strB = [NSString hexStringWithData:str2 ofLength:1];

>From the above 'ofLength', you are looking at only the first ASCII character 
>of the two strings, as Igor said.

You need to load two bytes at a time (because you specified UTF-16) and you 
need to iterate along the length of the two strings rather than just look along 
the first byte.

I suggest you do it by writing a 'normalize' function which turns each string 
into a string without any accents on the characters.  Then once you've 
converted both string 1 and string 2 you can compare the results using any 
normal string comparison routines.

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

Re: [sqlite] Custom Collation comparing only firt character?

[Michael Stephenson]

Most likely, since you say only the first character is being compared, is
that you have an ANSI/Unicode issue.  Specifically, it sounds like a Unicode
string is being passed to your collation function, which is expecting ANSI
and then finding a 0 at the second byte is determining that that is the end
of the string, thus exiting after the first character.

I have a collation function (Windows, code page 1252) that sounds like what
you are doing.  It was actually a bit complex to get it right (I think it's
right).  If you're interested, I could post it.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roberto Colnaghi
Sent: Thursday, August 25, 2011 1:59 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Custom Collation comparing only firt character?











Hi,


I'm using iOS SQLite with a custom collation.


I've registered it:
sqlite3_create_collation(sqlDatabase, 
"anyCIAI", 
SQLITE_UTF16, 
nil,
collationAnyCIAI);


And it is used like this:
"select * from Team where Name = 'SOMETHING' COLLATE anyCIAI;"


It works though only the FIRST character seems to be compared instead of the
whole "Name".
Is there anything missing here?


The collation method should compare a á à ã... and so on as equal.

Thank you!










int collationAnyCIAI(void *arg1, int str1Length, const void *str1, int
str2Length, const void *str2) {
NSString *strA = [NSString hexStringWithData:str1 ofLength:1];
NSString *strB = [NSString hexStringWithData:str2 ofLength:1];
int striA;
sscanf([strA cString], "%x", );
int striB;
sscanf([strB cString], "%x", );


//convert to accentless
//aA with accent to capital A
if( (striA >= 192 && striA <= 197) || (striA >= 224 && striA <= 229)
){
striA = 65;
}
//çÇ to C
if( striA == 199 || striA == 231 ){
striA = 67;
}
//eE with accent to capital E
if( (striA >= 200 && striA <= 203) || (striA >= 232 && striA <= 235)
){
striA = 69;
}
//iI with accent to capital I
if( (striA >= 204 && striA <= 207) || (striA >= 236 && striA <= 239)
){
striA = 73;
}
//oO with accent to capital O
if( (striA >= 210 && striA <= 214) || (striA >= 242 && striA <= 246)
){
striA = 79;
}
//uU with accent to capital U
if( (striA >= 217 && striA <= 220) || (striA >= 249 && striA <= 252)
){
striA = 85;
}
//a-z to A-Z
if( striA >= 97 && striA <= 122 ){
striA -= 32;
}


//convert to accentless
//aA with accent to capital A
if( (striB >= 192 && striB <= 197) || (striB >= 224 && striB <= 229)
){
striB = 65;
}
//çÇ to C
if( striB == 199 || striB == 231 ){
striB = 67;
}
//eE with accent to capital E
if( (striB >= 200 && striB <= 203) || (striB >= 232 && striB <= 235)
){
striB = 69;
}
//iI with accent to capital I
if( (striB >= 204 && striB <= 207) || (striB >= 236 && striB <= 239)
){
striB = 73;
}
//oO with accent to capital O
if( (striB >= 210 && striB <= 214) || (striB >= 242 && striB <= 246)
){
striB = 79;
}
//uU with accent to capital U
if( (striB >= 217 && striB <= 220) || (striB >= 249 && striB <= 252)
){
striB = 85;
}
//a-z to A-Z
if( striB >= 97 && striB <= 122 ){
striB -= 32;
}

int result = striA - striB;


return result;
} 
___
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] Determining size of insert row/query before inserting

[Stephan Beal]

On Thu, Aug 25, 2011 at 2:29 PM, Simon Slavin  wrote:

> I think your overall best strategy here it not to run any system so close
> to the margin it's likely to fail.  Define some amount of free space for
> your system -- for example 10Kb for an embedded system or 1Meg for a desktop
> computer -- and if you have less than that amount of space free, take
> emergency measures and refuse to accept new data.
>

A slight elaboration on that: this approach might not suffice on Unix
systems. Filesystems on Unix tend to (but not always) have a certain
percentage of the space reserved for the superuser (to keep user-space
processes from filling up the system, which can lead to all kinds of
problems on Unix systems, including (potentially) the inability to log in
and fix the problem). e.g. just because your tools report that a 100GB drive
still has 10GB free, 5GB of that might not be allocatable to non-root
processes.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Determining size of insert row/query before inserting

[Simon Slavin]

On 25 Aug 2011, at 10:52am, Pero Mirko wrote:

> I want to determine how much disk space a certain insert will take before
> actually executing it so I can fail insert before doing it rather than
> trying to insert, then receive SQLite Disk I/O error when the disk is
> filled.
> 
> So I would determine first how much disk space is left, then determine
> approximately how much next query will take on disk and if there is not
> enough space fail before even trying to write.

SQLite stores information in your database split up into pages.  Each part of 
your data: basic information, the data for a table, each index for a table, has 
pages assigned to it.  All pages for a particular database file are the same 
size.  A typical page size might be 2048 bytes.  You can use a PRAGMA statement 
to find out how big the pages are for a particular database file.

When SQLite needs to store new bytes it first tries to fit them into existing 
page assigned to that kind of data.  So, for example, adding a row to a table 
might involve adding 240 bytes to one set of pages assigned to the data itself, 
22 bytes to the pages for one index and 119 bytes to the pages for another 
index.  If the new data fits into space already available in those pages, no 
new space might be needed to insert the new record.  However, you might be 
particularly unlucky and you might need to grab three new pages.

Without analysis of the current state of the database file there's no way to 
tell, in the above example, whether SQLite might need to grab 0, 1, 2 or 3 
pages in order to save the new data.  In addition, if you're using journaling 
(which you are by default) you have in the same folder on the disk a journal 
file which also grows and shrinks unpredictably depending on how you're 
managing your transactions.

I think your overall best strategy here it not to run any system so close to 
the margin it's likely to fail.  Define some amount of free space for your 
system -- for example 10Kb for an embedded system or 1Meg for a desktop 
computer -- and if you have less than that amount of space free, take emergency 
measures and refuse to accept new data.

> Also, another problem is that once disk space is low and query fails with
> I/O error it leaves journal file.

Oh sorry, I now see you know that.

> Apparently the database is not corrupted
> as it seems to do auto-rollback and on next access of database the journal
> file is gone. But why is it there when write fails due to low disk space?
> 
> The process goes like this
> 1. BEGIN EXCLUSIVE
> 2. some INSERT
> 3. sqlite3_step() (fails to low disk space)
> 4. journal file is not deleted even if I do manual ROLLBACK...
> 
> However, after closing database and reopening it journal is deleted and
> apparently database can be accessed normally and it doesn't seem to be
> corrupted although its size is not truncated to previous size before INSERT.

Journal files are used to store information about pending transactions and 
rollback points (a little handwaving here for simplicity).  If there are none 
of those, the journal file isn't needed.   So when a database file is closed 
(has been properly closed by _close()) or if a file is open but no changes have 
been made to it, there's no need for a journal file to exist.

If SQLite opens a database file which it knows nothing else is using and finds 
a journal file, it knows there was some sort of crash the last time the 
database was used.  It uses the information in the journal file to restore the 
database to a non-corrupted state, then acts as if you closed and reopened the 
database file.  Consequently, because there are no now pending transactions, 
the journal file disappears.

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

Re: [sqlite] Custom Collation comparing only firt character?

[Igor Tandetnik]

Roberto Colnaghi  wrote:
> It works though only the FIRST character seems to be compared instead of the 
> whole "Name".
> Is there anything missing here?
> 
> int collationAnyCIAI(void *arg1, int str1Length, const void *str1, int 
> str2Length, const void *str2) {
> NSString *strA = [NSString hexStringWithData:str1 ofLength:1];

Well, I know nothing about Objective C, but the "ofLength:1" part looks highly 
suspicious. Shouldn't str1Length appear in there somewhere?
-- 
Igor Tandetnik

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

Re: [sqlite] SQL-Beginner question about filtering

[Igor Tandetnik]

Scholz Maik (CM-AI/PJ-CF42)  wrote:
> I am little bit confused about filtering SQL query's.
> 
> My table:
> ROWID   V   A   B
> ===
> 1   0   0   1
> 2   0   0   2
> 3   0   1   1
> 4   0   1   2
> 5   1   0   2
> 6   2   0   2
> 
> With my SQL knowledge I am able filter the result
> Like this:
> SELECT rowid,* from table WHERE V==0 | V==1;

I'm pretty sure you mean WHERE V=0 or V=1 . You can also write it as WHERE V in 
(0, 1) .  | is a bitwise-OR operator; it just happens to work in your case.

> But additionally, I need to eliminate duplicate (A+B)
> rows.
> My wanted result is:
> ROWID   V   A   B
> ===
> 1   0   0   1
> 3   0   1   1
> 4   0   1   2
> 5   1   0   2

Any particular reason you are choosing the row with ROWID of 5 and not 2? In 
other words, if you have duplicates, by what principle do you choose which row 
to keep?
-- 
Igor Tandetnik

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

Re: [sqlite] System.Data.SQLite.xml -- documentation for the wrongassembly

[Joe Mistachkin]

Jonas Bähr wrote:
> 
> The zip archives with the precompiled binaries for version 1.0.74.0
> include a file System.Data.SQLite.xml, which is supposed to hold the xml
> documentation for the System.Data.SQLite.dll. However, this file
> contains the code documentation of the "testlinq" assembly while the
> actual documentation for "System.Data.SQLite" is missing.
> 

This issue was recently fixed (Aug 9th) and will be included in the
1.0.75.0 packages when they are released.  For details of the fix, see
check-in:

http://system.data.sqlite.org/index.html/info/15b1d9e667

--
Joe Mistachkin

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

[sqlite] SQL-Beginner question about filtering

[Scholz Maik (CM-AI/PJ-CF42)]

Hi,
I am little bit confused about filtering SQL query's.

My table:
ROWID   V   A   B
===
1   0   0   1
2   0   0   2
3   0   1   1
4   0   1   2
5   1   0   2
6   2   0   2

With my SQL knowledge I am able filter the result
Like this:
SELECT rowid,* from table WHERE V==0 | V==1;
ROWID   V   A   B
===
1   0   0   1
2   0   0   2
3   0   1   1
4   0   1   2
5   1   0   2

But additionally, I need to eliminate duplicate (A+B)
rows.
My wanted result is:
ROWID   V   A   B
===
1   0   0   1
3   0   1   1
4   0   1   2
5   1   0   2

Anybody has a hint for a SQL beginner?

The operation shall be as compact as possible because
I have to run it on a embedded system with large tables.

Thanks in advance
Maik

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

[sqlite] Custom Collation comparing only firt character?

[Roberto Colnaghi]

Hi,


I'm using iOS SQLite with a custom collation.


I've registered it:
sqlite3_create_collation(sqlDatabase, 
"anyCIAI", 
SQLITE_UTF16, 
nil,
collationAnyCIAI);


And it is used like this:
"select * from Team where Name = 'SOMETHING' COLLATE anyCIAI;"


It works though only the FIRST character seems to be compared instead of the 
whole "Name".
Is there anything missing here?


The collation method should compare a á à ã... and so on as equal.

Thank you!










int collationAnyCIAI(void *arg1, int str1Length, const void *str1, int 
str2Length, const void *str2) {
NSString *strA = [NSString hexStringWithData:str1 ofLength:1];
NSString *strB = [NSString hexStringWithData:str2 ofLength:1];
int striA;
sscanf([strA cString], "%x", );
int striB;
sscanf([strB cString], "%x", );


//convert to accentless
//aA with accent to capital A
if( (striA >= 192 && striA <= 197) || (striA >= 224 && striA <= 229) ){
striA = 65;
}
//çÇ to C
if( striA == 199 || striA == 231 ){
striA = 67;
}
//eE with accent to capital E
if( (striA >= 200 && striA <= 203) || (striA >= 232 && striA <= 235) ){
striA = 69;
}
//iI with accent to capital I
if( (striA >= 204 && striA <= 207) || (striA >= 236 && striA <= 239) ){
striA = 73;
}
//oO with accent to capital O
if( (striA >= 210 && striA <= 214) || (striA >= 242 && striA <= 246) ){
striA = 79;
}
//uU with accent to capital U
if( (striA >= 217 && striA <= 220) || (striA >= 249 && striA <= 252) ){
striA = 85;
}
//a-z to A-Z
if( striA >= 97 && striA <= 122 ){
striA -= 32;
}


//convert to accentless
//aA with accent to capital A
if( (striB >= 192 && striB <= 197) || (striB >= 224 && striB <= 229) ){
striB = 65;
}
//çÇ to C
if( striB == 199 || striB == 231 ){
striB = 67;
}
//eE with accent to capital E
if( (striB >= 200 && striB <= 203) || (striB >= 232 && striB <= 235) ){
striB = 69;
}
//iI with accent to capital I
if( (striB >= 204 && striB <= 207) || (striB >= 236 && striB <= 239) ){
striB = 73;
}
//oO with accent to capital O
if( (striB >= 210 && striB <= 214) || (striB >= 242 && striB <= 246) ){
striB = 79;
}
//uU with accent to capital U
if( (striB >= 217 && striB <= 220) || (striB >= 249 && striB <= 252) ){
striB = 85;
}
//a-z to A-Z
if( striB >= 97 && striB <= 122 ){
striB -= 32;
}

int result = striA - striB;


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

Re: [sqlite] INTEGER PRIMARY KEY and SQLITE_ENABLE_STAT2

[Alex Bowden]

logical?

It seems equally logical to me that one of A or B might be evaluated, and if it 
were false, then the other might not be evaluated.

And it would be logical to choose which of A or B to evaluated on a predicted 
cost and probability of an advantageous false result.

but hay.  Who said their could only be one logical approach.

Alex


On 24 Aug 2011, at 20:12, Carlos Rocha wrote:

> Don't know how SQLite should behave in this case, but seems logical to 
> me that A and B would force that A is always evaluated, and B is 
> evaluated only if A is true.
> I would change the order of the two betweens:
> 
> SELECT itemID FROM t WHERE createdAt BETWEEN '2011-08-01' AND 
> '2011-08-02' AND itemID BETWEEN 100 AND 200
> 
>> Hi all,
>> 
>> I have a table like this:
>> 
>> CREATE TABLE t (itemID INTEGER PRIMARY KEY, createdAt DATETIME);
>> CREATE INDEX createIdx on t(createdAt);
>> 
>> SQLite is compiled using SQLITE_ENABLE_STAT2 and table is ANALYZEd with
>> current content.
>> 
>> When perfoming a Statement like this:
>> 
>> SELECT itemID FROM t WHERE itemID BETWEEN 100 AND 200 AND
>> createdAt BETWEEN '2011-08-01' AND '2011-08-02'
>> 
>> the analyzer always chooses the rowid index which results in a scan over
>> one million rows. It would have to scan only a few dozen rows if it
>> chose createIdx instead (which is also a covering index). Looking at the
>> sqlite_stat2 table shows that there is no data for the rowid index.
>> Could this be the reason for the suboptimal query plan? The choice works
>> as expected if itemID is a regular column with an index on it.
>> 
>> regards
>> gerd
>> ___
>> 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] System.Data.SQLite.xml -- documentation for the wrong assembly

[Jonas Bähr]

Hi,

The zip archives with the precompiled binaries for version 1.0.74.0
include a file System.Data.SQLite.xml, which is supposed to hold the xml
documentation for the System.Data.SQLite.dll. However, this file
contains the code documentation of the "testlinq" assembly while the
actual documentation for "System.Data.SQLite" is missing.
This may be due to an error in the release automation,
System.Data.SQLite.Linq.xml has the expected content.

I looked into both, the x86 and x64 versions as well as for .NET-4 and
.NET-3.5; every "precompiled binary" package from your site [1] ships a
System.Data.SQLite.xml with the wrong content.
The setups are missing any of the XML documentation.
It would be nice if you could include the correct files in future
releases as without them the "IntelliSense" feature of Visual Studio
does not work for the types provided by the  SQLite assemblies.

[1] http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

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

[sqlite] Determining size of insert row/query before inserting

[Pero Mirko]

How would you recommend to approach this problem:

I want to determine how much disk space a certain insert will take before
actually executing it so I can fail insert before doing it rather than
trying to insert, then receive SQLite Disk I/O error when the disk is
filled.

So I would determine first how much disk space is left, then determine
approximately how much next query will take on disk and if there is not
enough space fail before even trying to write.

Is there any way to do this?

Also, another problem is that once disk space is low and query fails with
I/O error it leaves journal file. Apparently the database is not corrupted
as it seems to do auto-rollback and on next access of database the journal
file is gone. But why is it there when write fails due to low disk space?

The process goes like this
1. BEGIN EXCLUSIVE
2. some INSERT
3. sqlite3_step() (fails to low disk space)
4. journal file is not deleted even if I do manual ROLLBACK...

However, after closing database and reopening it journal is deleted and
apparently database can be accessed normally and it doesn't seem to be
corrupted although its size is not truncated to previous size before INSERT.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users