Re: [sqlite] SQLite lets selecting a column that is not inthe Group by clause

[Alexey Pechnikov]

2010/6/11 Igor Tandetnik :
> 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

[sqlite] "Using SQLite" at Southeast Linuxfest. Was: Avoiding Database Is Locked Error

[Richard Hipp]

On Fri, Jun 11, 2010 at 12:12 PM, Simon Slavin  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] Avoiding Database Is Locked Error

[Simon Slavin]

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

Re: [sqlite] Avoiding Database Is Locked Error

[Jay A. Kreibich]

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

[Pavel Ivanov]

>> 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  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

[Simon Slavin]

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] bug report: unhelpful error message whendirectorypermissions are wrong

[Black, Michael (IS)]

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)
 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  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] SQLite lets selecting a column that is not inthe Group by clause

[Igor Tandetnik]

Jay A. Kreibich  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] SQLite lets selecting a column that is not in the Group by clause

[Jay A. Kreibich]

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] bug report: unhelpful error message when directorypermissions are wrong

[Pavel Ivanov]

> 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)
 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  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] bug report: unhelpful error message when directorypermissions are wrong

[Black, Michael (IS)]

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  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] Avoiding Database Is Locked Error

[Odekirk, Shawn]

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] SQLite lets selecting a column that is not in the Group by clause

[Pavel Ivanov]

> 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  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

Re: [sqlite] bug report: unhelpful error message when directory permissions are wrong

[Pavel Ivanov]

> 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  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] bug report: unhelpful error message when directory permissions are wrong

[Artur Reilin]

> 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

[sqlite] bug report: unhelpful error message when directory permissions are wrong

[Otto Kekäläinen]

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] Slow deletion

[Eduardo]

On Fri, 11 Jun 2010 12:38:55 +0200
Eduardo  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

Re: [sqlite] Slow deletion

[Eduardo]

On Fri, 11 Jun 2010 00:49:47 -0700 (PDT)
durumdara  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

[sqlite] SQLite lets selecting a column that is not in the Group by clause

[Benoit Mortgat]

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] Slow deletion

[durumdara]

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