[sqlite] Slow deletion
Hi! I have an app that transform the input images to later somebody can choose the final version. I stored them in an SQLite blob table: create table blobs( filename varchar(255) not null, ext varchar(3) not null, size integer not null, primary key(filename, ext, size)) In the prior version of the program I used PGSQL for it, but this was extremely slow with bytea fields (the deletion was 1-2 hours sometimes). After this I used AnyDBM, but this don't useful: after 2 GB the inserts are very slowed down... In SQLite the insertion, select is good. But deletion also slow. In an 268 MB size db the delete from blobs is 1-5 minutes, the drop table blobs also... :-( I increased the size of pages, but only a little changes I see. May this caused by index? Because this is a primary key, I cannot drop it... What can I do to improve the speed on blob deletion? Thanks: dd ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite lets selecting a column that is not in the Group by clause
Hello
I am running the last version on SQLite (precompiled 3.6.23.1 binary
release for Windows as found on
http://www.sqlite.org/sqlite-3_6_23_1.zip) command line. Here is the
input:
create temporary table foo(col1 text, col2 text);
insert into foo values('a','b');
insert into foo values('a','c');
insert into foo values('b','d');
insert into foo values('b','e');
.mode
select col1, col2 from foo group by col1;
a|c
b|e
As you can see, that last query does not result in any error, however
col2 should not be selectable if not in the group by clause?
Is this a feature, a known bug or an unknown one? If this is a
feature, is there a pragma to forbid such a syntax?
Have a nice day
--
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow deletion
On Fri, 11 Jun 2010 00:49:47 -0700 (PDT) durumdara durumd...@gmail.com wrote: Hi! I have an app that transform the input images to later somebody can . I increased the size of pages, but only a little changes I see. May this caused by index? Because this is a primary key, I cannot drop it... What can I do to improve the speed on blob deletion? a) Make a database exclusive for BLOB and use ATTACH from the principal database. b) Add to BLOB-only database table a new colum Boolean with name dirty c) When add blobs set dirty to false. d) Don't delete any blob entry, just set the dirty value to true. e) When you do any select on this table check dirty=false too. f) Do one of this options when you can (idle or maintenance time) f1) Delete from blobtable where (dirty=true) f2) select * from blobtable where (dirty=false), copy to another db, table or temp table, drop db or drop/truncate blobtable. You can also index the blobtable by dirty colum for faster searches, don't know if Thanks: dd HTH ___ 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] Slow deletion
On Fri, 11 Jun 2010 12:38:55 +0200 Eduardo emor...@xroff.net wrote: Opsss, hit send too early You can also index the blobtable by dirty colum for faster searches, don't know if index helps on deletes if you use the f1) way. You can use the dirty colum with integers if you need more than 2 states. You can simulate mvcc using this approach too, but you need to modify all your sql before execute. Sorry. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] bug report: unhelpful error message when directory permissions are wrong
Hello, You website states that bugs reports should be sent to this list (http://www.sqlite.org/src/wiki?name=Bug+Reports). ** Bug report: unhelpful error message when directory permissions are wrong Steps to reproduce: 1. Make new database in the directory /var/database/ with root permissions. 2. Change file ower to normal user 3. Try to open database and make changes to it as the normal user What happends: There is the error message sqlite: Unable to open database file What was expected: The error message: sqlite: Unable to write to database due to file or folder permissions I just spent several hours trying to debug a PySqlite app which gave the unable to open database file error. Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Please change the error message to be more descriptive so that others could save time debugging the issue. You can find on Google tons of people wondering about this error, but few will ever find the solutions. Fixing the error message would solve it. ** -- Otto Kekäläinen www.sange.fi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: unhelpful error message when directory permissions are wrong
Hello, You website states that bugs reports should be sent to this list (http://www.sqlite.org/src/wiki?name=Bug+Reports). ** Bug report: unhelpful error message when directory permissions are wrong Steps to reproduce: 1. Make new database in the directory /var/database/ with root permissions. 2. Change file ower to normal user 3. Try to open database and make changes to it as the normal user What happends: There is the error message sqlite: Unable to open database file What was expected: The error message: sqlite: Unable to write to database due to file or folder permissions I just spent several hours trying to debug a PySqlite app which gave the unable to open database file error. Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Journal. SQLite create temp journals. That's why it need write permission to folder. Please change the error message to be more descriptive so that others could save time debugging the issue. You can find on Google tons of people wondering about this error, but few will ever find the solutions. Fixing the error message would solve it. ** I second that. At the beginning of sqlite learning i searched a lot to find the error. -- Otto Kekäläinen www.sange.fi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Artur Reilin sqlite.yuedream.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: unhelpful error message when directory permissions are wrong
Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Some reason here is a necessity to create a journal file at the same directory to be able to restore the database in case of crash or power outage. To create files in the directory one must have write permissions to it. Pavel On Fri, Jun 11, 2010 at 7:22 AM, Otto Kekäläinen o...@sange.fi wrote: Hello, You website states that bugs reports should be sent to this list (http://www.sqlite.org/src/wiki?name=Bug+Reports). ** Bug report: unhelpful error message when directory permissions are wrong Steps to reproduce: 1. Make new database in the directory /var/database/ with root permissions. 2. Change file ower to normal user 3. Try to open database and make changes to it as the normal user What happends: There is the error message sqlite: Unable to open database file What was expected: The error message: sqlite: Unable to write to database due to file or folder permissions I just spent several hours trying to debug a PySqlite app which gave the unable to open database file error. Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Please change the error message to be more descriptive so that others could save time debugging the issue. You can find on Google tons of people wondering about this error, but few will ever find the solutions. Fixing the error message would solve it. ** -- Otto Kekäläinen www.sange.fi ___ 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] SQLite lets selecting a column that is not in the Group by clause
As you can see, that last query does not result in any error, however
col2 should not be selectable if not in the group by clause?
Is this a feature, a known bug or an unknown one? If this is a
feature, is there a pragma to forbid such a syntax?
It's sort of a feature of SQLite. It's even documented on SQLite
website. And there's no way to prohibit such syntax - you have to
check your queries carefully.
Pavel
On Fri, Jun 11, 2010 at 5:30 AM, Benoit Mortgat mort...@gmail.com wrote:
Hello
I am running the last version on SQLite (precompiled 3.6.23.1 binary
release for Windows as found on
http://www.sqlite.org/sqlite-3_6_23_1.zip) command line. Here is the
input:
create temporary table foo(col1 text, col2 text);
insert into foo values('a','b');
insert into foo values('a','c');
insert into foo values('b','d');
insert into foo values('b','e');
.mode
select col1, col2 from foo group by col1;
a|c
b|e
As you can see, that last query does not result in any error, however
col2 should not be selectable if not in the group by clause?
Is this a feature, a known bug or an unknown one? If this is a
feature, is there a pragma to forbid such a syntax?
Have a nice day
--
Benoit Mortgat
20, avenue Marcel Paul
69200 Vénissieux, France
+33 6 17 15 41 58
+33 4 27 11 61 23
___
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] Avoiding Database Is Locked Error
I have recently upgraded an old system that managed its data in a dynamically allocated linked list to use SQLite instead. I have been extremely pleased with SQLite. Most operations have been greatly simplified and I am much more confident in the stability of the system and the integrity of the data. The system is made up of several independent programs which communicate by sending messages to the other programs. Only one program opens the SQLite database and the other programs request data by sending a message to this program. During normal operation I have no problems. However, while the program is running there are times I would like to use the command line SQLite tool to query or update the database. Since I planned to have only one program access the database I did not handle the database is locked condition in my program. During testing my program encountered the database is locked condition when I was using the SQLite command line tool while the program was running. I have avoided using the command line tool on the live system. Is there any way to ensure that the SQLite command line tool does not interfere with the operation of my program? I don't care how long it takes the command line tool to perform its queries as long as it doesn't interrupt the operation of my program. Also, are there any code examples that demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? I have been unable to find complete code examples. If anyone is interested, my program is written in C running on SCO Open Server 5.0.7. Thanks, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: unhelpful error message when directorypermissions are wrong
I did the following...so what kind of permissions do you have that are different from this? drwxr-xr-x 2 root root 4096 Jun 11 08:40 ./ -rw-r--r-- 1 root root 2048 Jun 11 08:40 test.db As a normal user I did this: sqlite3 test.db SQLite version 3.3.6 Enter .help for instructions sqlite insert into data_his values(3,'test'); SQL error: attempt to write a readonly database Clear as a bell... What's different about your setup? Different permissions on the directory or file? Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov Sent: Fri 6/11/2010 8:22 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug report: unhelpful error message when directorypermissions are wrong Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Some reason here is a necessity to create a journal file at the same directory to be able to restore the database in case of crash or power outage. To create files in the directory one must have write permissions to it. Pavel On Fri, Jun 11, 2010 at 7:22 AM, Otto Kekäläinen o...@sange.fi wrote: Hello, You website states that bugs reports should be sent to this list (http://www.sqlite.org/src/wiki?name=Bug+Reports). ** Bug report: unhelpful error message when directory permissions are wrong Steps to reproduce: 1. Make new database in the directory /var/database/ with root permissions. 2. Change file ower to normal user 3. Try to open database and make changes to it as the normal user What happends: There is the error message sqlite: Unable to open database file What was expected: The error message: sqlite: Unable to write to database due to file or folder permissions I just spent several hours trying to debug a PySqlite app which gave the unable to open database file error. Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Please change the error message to be more descriptive so that others could save time debugging the issue. You can find on Google tons of people wondering about this error, but few will ever find the solutions. Fixing the error message would solve it. ** -- Otto Kekäläinen www.sange.fi ___ 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] bug report: unhelpful error message when directorypermissions are wrong
What's different about your setup? Different permissions on the directory or file? Try to make file -rw-rw-rw-. Pavel On Fri, Jun 11, 2010 at 9:50 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: I did the following...so what kind of permissions do you have that are different from this? drwxr-xr-x 2 root root 4096 Jun 11 08:40 ./ -rw-r--r-- 1 root root 2048 Jun 11 08:40 test.db As a normal user I did this: sqlite3 test.db SQLite version 3.3.6 Enter .help for instructions sqlite insert into data_his values(3,'test'); SQL error: attempt to write a readonly database Clear as a bell... What's different about your setup? Different permissions on the directory or file? Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov Sent: Fri 6/11/2010 8:22 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug report: unhelpful error message when directorypermissions are wrong Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Some reason here is a necessity to create a journal file at the same directory to be able to restore the database in case of crash or power outage. To create files in the directory one must have write permissions to it. Pavel On Fri, Jun 11, 2010 at 7:22 AM, Otto Kekäläinen o...@sange.fi wrote: Hello, You website states that bugs reports should be sent to this list (http://www.sqlite.org/src/wiki?name=Bug+Reports). ** Bug report: unhelpful error message when directory permissions are wrong Steps to reproduce: 1. Make new database in the directory /var/database/ with root permissions. 2. Change file ower to normal user 3. Try to open database and make changes to it as the normal user What happends: There is the error message sqlite: Unable to open database file What was expected: The error message: sqlite: Unable to write to database due to file or folder permissions I just spent several hours trying to debug a PySqlite app which gave the unable to open database file error. Finally I found that in order to write to the database, Sqlite for some reason needs to have write permissions to the _folder_ the file resides in. Please change the error message to be more descriptive so that others could save time debugging the issue. You can find on Google tons of people wondering about this error, but few will ever find the solutions. Fixing the error message would solve it. ** -- Otto Kekäläinen www.sange.fi ___ 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] SQLite lets selecting a column that is not in the Group by clause
On Fri, Jun 11, 2010 at 11:30:43AM +0200, Benoit Mortgat scratched on the wall: select col1, col2 from foo group by col1; As you can see, that last query does not result in any error, however col2 should not be selectable if not in the group by clause?A Is this a feature, a known bug or an unknown one? If this is a feature, is there a pragma to forbid such a syntax? While the results are not all that useful, there is nothing that says you can't do this. You can't outright prohibit non-GROUP BY column references in the select header, since they can be passed to aggregate functions. So to address you concern, the short answer is, don't be stupid. It is always possible to write bad SQL that returns non-sense results. For any non-grouped column, the grouped value is from the last row of the group. Of course, the definition of last is somewhat undefined. This is actually true of grouped columns as well. If you GROUP BY using a non-unique collation (such as NOCASE), you'll see the same behavior-- the returned value will simply be the value from the last row. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause
Jay A. Kreibich j...@kreibi.ch wrote: On Fri, Jun 11, 2010 at 11:30:43AM +0200, Benoit Mortgat scratched on the wall: select col1, col2 from foo group by col1; As you can see, that last query does not result in any error, however col2 should not be selectable if not in the group by clause?A Is this a feature, a known bug or an unknown one? If this is a feature, is there a pragma to forbid such a syntax? While the results are not all that useful, there is nothing that says you can't do this. You can't outright prohibit non-GROUP BY column references in the select header, since they can be passed to aggregate functions. Well, many SQL engines, as well as SQL-92 standard, do prohibit such a syntax. According to the standard, in a statement using GROUP BY, any column reference that appears in SELECT clause must also appear in GROUP BY clause or be part of an argument of an aggregate function. SQLite allows naked non-grouped columns as an extension (which is occasionally useful). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bug report: unhelpful error message whendirectorypermissions are wrong
Yup -- that duplicates the problem.
Is there any reason why strerror() results can't be added to the error messages
when file i/o errors occur like this? It's used elsewhere in the code but not
for any error messages in the api.
I added this in sqlite3ErrStr (I've got a snapshot of 3.7.0 I'm working with)
if( ALWAYS(rc=0) rc(int)(sizeof(aMsg)/sizeof(aMsg[0])) aMsg[rc]!=0 ){
switch(rc) {
case SQLITE_CANTOPEN: return strerror(errno);
default:
return aMsg[rc];
}
Then I get this error:
./sqlite3 ../x1/test.db
SQLite version 3.7.0
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite insert into data_his values(3,'test');
Error: No such file or directory
So it appears that the initial attempt to open the journal may not be getting
an error? That it comes later?
I wasn't quite able to trace what's happening in the limited time I've got.
I expected to see a permission denied.
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Fri 6/11/2010 8:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug report: unhelpful error message
whendirectorypermissions are wrong
What's different about your setup? Different permissions on the directory or
file?
Try to make file -rw-rw-rw-.
Pavel
On Fri, Jun 11, 2010 at 9:50 AM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
I did the following...so what kind of permissions do you have that are
different from this?
drwxr-xr-x 2 root root 4096 Jun 11 08:40 ./
-rw-r--r-- 1 root root 2048 Jun 11 08:40 test.db
As a normal user I did this:
sqlite3 test.db
SQLite version 3.3.6
Enter .help for instructions
sqlite insert into data_his values(3,'test');
SQL error: attempt to write a readonly database
Clear as a bell...
What's different about your setup? Different permissions on the directory or
file?
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Pavel Ivanov
Sent: Fri 6/11/2010 8:22 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug report: unhelpful error message when
directorypermissions are wrong
Finally I found that in
order to write to the database, Sqlite for some reason needs to have
write permissions to the _folder_ the file resides in.
Some reason here is a necessity to create a journal file at the same
directory to be able to restore the database in case of crash or power
outage. To create files in the directory one must have write
permissions to it.
Pavel
On Fri, Jun 11, 2010 at 7:22 AM, Otto Kekäläinen o...@sange.fi wrote:
Hello,
You website states that bugs reports should be sent to this list
(http://www.sqlite.org/src/wiki?name=Bug+Reports).
**
Bug report: unhelpful error message when directory permissions are wrong
Steps to reproduce:
1. Make new database in the directory /var/database/ with root permissions.
2. Change file ower to normal user
3. Try to open database and make changes to it as the normal user
What happends:
There is the error message sqlite: Unable to open database file
What was expected:
The error message: sqlite: Unable to write to database due to file or
folder permissions
I just spent several hours trying to debug a PySqlite app which gave
the unable to open database file error. Finally I found that in
order to write to the database, Sqlite for some reason needs to have
write permissions to the _folder_ the file resides in.
Please change the error message to be more descriptive so that others
could save time debugging the issue. You can find on Google tons of
people wondering about this error, but few will ever find the
solutions. Fixing the error message would solve it.
**
--
Otto Kekäläinen
www.sange.fi
___
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
Re: [sqlite] Avoiding Database Is Locked Error
On 11 Jun 2010, at 2:50pm, Odekirk, Shawn wrote: Also, are there any code examples that demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? I have been unable to find complete code examples. Can I point out that this is yet another person asking for the same documentation ? I don't know enough about SQLite to write it, and I would very much like to see someone do so. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding Database Is Locked Error
Also, are there any code examples that demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? I have been unable to find complete code examples. Can I point out that this is yet another person asking for the same documentation ? I don't know enough about SQLite to write it, and I would very much like to see someone do so. I'd say there's no best practices here. Or let's say it another way: code experiencing best practices will be useless in the majority of use cases. And that kind of code is described in documentation and repeated on this list several times. I can repeat it once more: whenever you see database locked error rollback your transaction immediately and depending on your application start it from the beginning or return error message to the user. This will always work but it's not very convenient in many cases. SQLite is that kind of software that has pretty raw reaction on most events. And all processing of SQLite result codes depends on the application structure and its use cases. So SQLite user should be well aware of everything that's happening in his application or can happen outside (like command line tool) and implement appropriate reactions accordingly. For your particular case, Shawn, you can just do 3 things: 1. Ensure that you have no transactions started with SELECT and continued with INSERT/DELETE/UPDATE (if there are such then start them with BEGIN IMMEDIATE instead of BEGIN). 2. Set busy_timeout to some reasonable value (let's say 10 seconds). 3. When you work with the command line tool experience extreme carefulness and always keep in mind how your actions can impact your application: how long do you keep your transactions uncommitted or how long your SELECT statements are executed. All that times shouldn't be longer than busy_timeout you have set in the application. With all that you won't see database locked inside the application. Pavel On Fri, Jun 11, 2010 at 11:04 AM, Simon Slavin slav...@bigfraud.org wrote: On 11 Jun 2010, at 2:50pm, Odekirk, Shawn wrote: Also, are there any code examples that demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? I have been unable to find complete code examples. Can I point out that this is yet another person asking for the same documentation ? I don't know enough about SQLite to write it, and I would very much like to see someone do so. Simon. ___ 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] Avoiding Database Is Locked Error
On Fri, Jun 11, 2010 at 04:04:59PM +0100, Simon Slavin scratched on the wall: On 11 Jun 2010, at 2:50pm, Odekirk, Shawn wrote: Also, are there any code examples that demonstrate SQLite best practices, including how to correctly handle error conditions, such as the database is locked condition? I have been unable to find complete code examples. Can I point out that this is yet another person asking for the same documentation ? I don't know enough about SQLite to write it, and I would very much like to see someone do so. For what it is worth, Using SQLite has four or five pages specifically dealing with SQLITE_BUSY errors-- both how to avoid them and how to correctly handle them. This section was heavily revised in the final editing pass, largely because of the questions on this mailing list. I can no longer provide a reference, however. The book has entered final production and the online version has been removed from O'Reilly's feedback site. http://oreilly.com/catalog/9780596521196/ http://www.amazon.com/Using-SQLite-Jay-Kreibich/dp/0596521189/ -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Avoiding Database Is Locked Error
On 11 Jun 2010, at 4:33pm, Jay A. Kreibich wrote: For what it is worth, Using SQLite has four or five pages specifically dealing with SQLITE_BUSY errors-- both how to avoid them and how to correctly handle them. Well, that somewhat increased the chances that I'll check out that book. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using SQLite at Southeast Linuxfest. Was: Avoiding Database Is Locked Error
On Fri, Jun 11, 2010 at 12:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 11 Jun 2010, at 4:33pm, Jay A. Kreibich wrote: For what it is worth, Using SQLite has four or five pages specifically dealing with SQLITE_BUSY errors-- both how to avoid them and how to correctly handle them. Well, that somewhat increased the chances that I'll check out that book. FWIW, Jay has kindly provided an advance copy of his new book (in a 3-ring binder) which I will have on display at the SQLite table in the exhibit hall at the Southeast Linuxfestl in Spartanburg, SC tomorrow (Saturday) and Sunday. So if you would like to look at hardcopy of Using SQLite, please stop by. More information on SELF-2010 at http://www.souteastlinuxfest.org/ Simon. ___ 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] SQLite lets selecting a column that is not inthe Group by clause
2010/6/11 Igor Tandetnik itandet...@mvps.org: Well, many SQL engines, as well as SQL-92 standard, do prohibit such a syntax. According to the standard, in a statement using GROUP BY, any column reference that appears in SELECT clause must also appear in GROUP BY clause or be part of an argument of an aggregate function. SQLite allows naked non-grouped columns as an extension (which is occasionally useful). This is needed as replacement of the distinct on clause. I don't know other way to translate many queries from PostgreSQL to SQLite. -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
