Re: [sqlite] Unable to open database file

[Roger Binns]

Timo wrote:
> OperationalError: unable to open database file

Note that there is a group specifically for using Python with SQLite:

  http://groups.google.com/group/python-sqlite

Unfortunately SQLite's error reporting isn't particularly detailed when
opening databases.  There are a lot of things that have to happen beyond
just opening the database file.  For example the journal needs to be
checked for, applied and deleted if present.

Windows especially has issues with tag alongs - other programs like
virus checkers, backup applications, TortoiseSVN etc that track file
activity and then grovel in the files.  These can prevent the programs
actually using the files from doing things like deleting them (the tag
along has the file held open).  SQLite does have code to retry some
number of times hoping the tag along has finished, but if the system has
enough of these installed it may not be sufficient.

Note that tag alongs may not be the cause, but rather an example of
behaviour similar to what you are seeing.  Ultimately you'll need to
take an inventory of the machines looking for what is common and what is
different.  If you can reproduce the problem then use Process Monitor
from Sysinternals to see what programs are accessing the file/directory
and what error codes are being returned.

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

Re: [sqlite] Porting Sqlite to MQX OS: Question 2

[Kees Nuyt]

On Fri, 12 Mar 2010 02:22:31 -0800 (PST), GeoffW
 wrote:

>
>Hi
>
>Now I have got a half ported version of Sqlite up and running on MQX I cant
>put off any longer the question of file locking and how I tackle that ? For
>the moment i had made the xLock() and xUnlock() the same as in osWin.c
>version but with the Windows lock() and unlock() function calls commented
>out.
>
>The problem I have is that MQX's file system is just ANSI C, and it lacks
>any lock/unlock functions.
>
>As Sqlite has been ported to so many diverse operating systems I am hoping
>that this question of what to do for an ANSI only File system has already
>been hit and a workaround defined ?
>
>I am pretty hazy on this topic of file locking so I would appreciate some
>basic discussion and guidance. It might help if I explain my application a
>little. It is an embedded system where we are just running one application,
>but it is multithreaded. For my initial porting work I have made
>SQLITE_THREADSAFE = 0 (so I probably dont even need a file lock in the
>configuration I have at present ?)
>However I would of course like to make SQLITE_THREADSAFE = 1 so that
>multiple tasks can safely access a database.

(Disclaimer: this is not my specialism, I hope it helps
nevertheless)

SQLITE_THREADSAFE has effect on threads within the same
process (=task), especially when they use the same
connection. You need file locks for synchronisation between
different processes. If the filesystem doesn't provide them,
you could use the dot lockfile paradigm, which seems to be
associated with SQLITE_ENABLE_LOCKING_STYLE.

>Thanks for any guidance
>
>Regards Geoff
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]

[Luke Evans]

Just to be clear, from my point of view at least, the big difference is between:
- Multiple threads performing concurrent queries (with or without the shared 
cache)
vs
- Multiple processes performing concurrent queries 

In the latter case (for a number of queries chosen to lie within the expected 
number of hardware cores/hyperthreading on the test machine) the time taken to 
process all the queries is just a tiny fraction longer than the time it takes 
to process a single one.
Whereas in the former case I find that the time to process all the queries is 
commensurate with having lost much of the concurrency - much more akin to the 
time taken if you had serialised the processing of all the queries. 


On 2010-03-12, at 12:57 AM, Marcus Grimm wrote:

> I'm not able to reproduce the reported behavior that a single process
> queries faster than a single thread. 

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

Re: [sqlite] Way to close statement without finalize

[Pavel Ivanov]

Actually, sqlite3_reset() does release the lock on the database. What
made you think differently?

Pavel

On Fri, Mar 12, 2010 at 3:17 PM, TeDe  wrote:
> Hello Folks,
>
> I'm looking for a way to close a statement, but do not want to finalize
> it. I have 2 statement I would like to execute in a loop. Since I want
> to reuse them, I do not want to finalize them. Unfortunately
> sqlite3_reset() does not release the lock on the database. So I need a
> function that does something between finalize() and reset().
>
> Here is some pseudo code.
>
> prepare(stmt1);
> prepare(stmt2);
>
> for(int i = 0; i < anzahl; i++){
>      bind_value(stmt1);
>      step(stmt1);
>      close(stmt1);
>
>      bind_value(stmt2);
>      step(stmt2);
>      close(stmt2);
> }
>
> finalize(stmt1);
> finalize(stmt2);
>
> Can somebody help me with this? Is there such a function? Or do I have
> to open 2 separate connections (in the same thread)?
>
> Best regards,
>
> Thomas
> ___
> 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] Way to close statement without finalize

[TeDe]

Hello Folks,

I'm looking for a way to close a statement, but do not want to finalize
it. I have 2 statement I would like to execute in a loop. Since I want
to reuse them, I do not want to finalize them. Unfortunately
sqlite3_reset() does not release the lock on the database. So I need a
function that does something between finalize() and reset().

Here is some pseudo code.

prepare(stmt1);
prepare(stmt2);

for(int i = 0; i < anzahl; i++){  
  bind_value(stmt1);
  step(stmt1);
  close(stmt1);   

  bind_value(stmt2);
  step(stmt2);
  close(stmt2);
}

finalize(stmt1);
finalize(stmt2);

Can somebody help me with this? Is there such a function? Or do I have
to open 2 separate connections (in the same thread)?

Best regards,

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

Re: [sqlite] Sqlite without 64-bit integers?

[Dave Toll]

I have been successfully using SQLite on a 32-bit embedded system for
some time without any problems - you can add -DSQLITE_INT64_TYPE=long to
the compile line, no need to hack the source. The only issue I have seen
is that it is not possible to use -DSQLITE_DEBUG as it enables some
assert() calls that will always fail on a 32-bit system. This makes
debugging SQLite issues rather difficult, but does not seem to cause any
serious problems.

Cheers,
Dave.


-Original Message-
From: yamada [mailto:rafael.yam...@gmail.com] 
Sent: Thursday, March 11, 2010 8:59 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Sqlite without 64-bit integers?



Hi,

I am having the same problem here.
Did someone tested the solution gave by mattias? Are there any problems?


Mattias Ohlsson-3 wrote:
> 
> I'm trying to use the SQLite database in an embedded system that does
not
> have 64-bit integers, i.e. long long, which is used in SQLite. I've
> managed
> to compile by definining the 64-bit types to 32-bit types:
> 
> typedef long int sqlite_int64;
> #define UINT64_TYPE unsigned long int
> 
> I've also run some simple tests and it seems to work. However, I'm a
bit
> worried that this might lead me into problems down the road. Has
anyone
> tried to do this before? What kind of problems might I run into?
> 

-- 
View this message in context:
http://old.nabble.com/Sqlite-without-64-bit-integers--tp2337711p27866285
.html
Sent from the SQLite mailing list archive at Nabble.com.


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

[sqlite] Unable to open database file

[Timo]

Hello,

Lately I got some messages from a couple of user who get the following error
message when using my program:

OperationalError: unable to open database file

I myself (and lots of others) have no problem at all.

I searched a bit and found some with the same problem. They all solved it by
making the database directory writable and not passing a tilde as path.
I am 100% sure that the directory is writable, because my program creates a
logfile in the same directory with no problems. And I'm also not passing a
tilde as path.

Here is some code that could help:

# Get the application data folder
# This will return: C:\Documents and Settings\user\Application Data\myapp
PREFDIR = os.path.join(os.environ['APPDATA'], 'myapp')

# Connect to the database
def db_connect():

conn = sqlite3.connect(os.path.join(PREFDIR, 'myapp.db')) # This line
gives the error

conn.text_factory = str
cursor = conn.cursor()
return (conn, cursor)



Anyone can help me with this?

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

Re: [sqlite] novice question: marrying SQLite 3.6.19 with CGI::Application 4.31

[P Kishor]

On Fri, Mar 12, 2010 at 10:51 AM, William Bulley  wrote:
> I am trying to learn CGI::Application using this "tutorial":
>
>   
>
> Inside the accompanying application files from:
>
>   
>
> the example CGI::Application source code uses DBI to connect with MySQL.
> I wanted to chance this to use SQLite and I am getting an error.
>
> Here is the original MySQL centric code:
>
>   freebsd% cat DB.pm.orig
>   package Poli::DB;
>
>   use strict;
>   use warnings;
>
>   use Class::DBI::Loader;
>
>   my $loader = Class::DBI::Loader->new(
>       dsn                => 'dbi:mysql:database=poli',
>       user               => 'poli',
>       password           => 'poli',
>       namespace          => 'Poli::DB',
>       additional_classes => ['Class::DBI::FromForm'],
>       relationships      => 1,
>   );
>
>   1;
>
> Here is my changed (SQLite) version.  Changed only in dsn, user, and password:
>
> freebsd% cat DB.pm
>   package Poli::DB;
>
>   use strict;
>   use warnings;
>
>   use Class::DBI::Loader;
>
>   my $loader = Class::DBI::Loader->new(
>       dsn                => 
> 'dbi:SQLite:database=/home/web/src/mvc/mvc_samples/sql/mvc.sqlite3',


I have no idea how Class::DBI works, but from the DBD::SQLite docs,
the correct DSN string for sqlite is

DBI->connect("dbi:SQLite:dbname=$dbfile","","");

Note, 'dbname' instead of 'database'

That said, you may be making things too hard for yourself. I use
CGI::Application::Plugin::DBH, and it just works.

Finally, note there is a CGI::Application mailing list, for CGI::App
questions. That may be more suitable than this more sqlite-focused
list.


>       user               => '',
>       password           => '',
>       namespace          => 'Poli::DB',
>       additional_classes => ['Class::DBI::FromForm'],
>       relationships      => 1,
>   );
>
>   1;
>
> In my attempt to verify the Perl code of the provided application, I see the 
> following:
>
>   freebsd% cat petition.pl
>   #!/usr/bin/perl
>   use strict;
>   use warnings;
>
>   use lib qw(/home/web/src/mvc/mvc_samples/cgiapp/lib);
>   use Poli::CGI::Petition;
>
>   my $app = Poli::CGI::Petition->new();
>   $app->run();
>
>   freebsd% perl -c petition.pl
>   DBI connect('database=/home/web/src/mvc/mvc_samples/sql/mvc.sqlite3','',...)
>   failed: unable to open database file at 
> /usr/local/lib/perl5/site_perl/5.10.1/Class/DBI/Loader/SQLite.pm line 101
>   unable to open database file at 
> /usr/local/lib/perl5/site_perl/5.10.1/Class/DBI/Loader.pm line 83
>   Compilation failed in require at 
> /home/web/src/mvc/mvc_samples/cgiapp/lib/Poli/CGI/Petition.pm line 9.
>   BEGIN failed--compilation aborted at 
> /home/web/src/mvc/mvc_samples/cgiapp/lib/Poli/CGI/Petition.pm line 9.
>   Compilation failed in require at petition.pl line 6.
>   BEGIN failed--compilation aborted at petition.pl line 6.
>
> I don't know why SQLite is unable to open the database.  When I run sqlite3 
> from the
> command line (%sqlite3 /home/web/src/mvc/mvc_samples/sql/mvc.sqlite3) I am 
> able to
> see the tables created for this application.  While empty at present, I 
> believe the
> database is constructed properly (per a provided DDL file).
>
> Regards,
>
> web...
>
> --
> William Bulley                     Email: w...@umich.edu
>
> 72 characters width template ->|
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] novice question: marrying SQLite 3.6.19 with CGI::Application 4.31

[William Bulley]

I am trying to learn CGI::Application using this "tutorial":

   

Inside the accompanying application files from:

   

the example CGI::Application source code uses DBI to connect with MySQL.
I wanted to chance this to use SQLite and I am getting an error.

Here is the original MySQL centric code:

   freebsd% cat DB.pm.orig
   package Poli::DB;

   use strict;
   use warnings;

   use Class::DBI::Loader;

   my $loader = Class::DBI::Loader->new(
   dsn=> 'dbi:mysql:database=poli',
   user   => 'poli',
   password   => 'poli',
   namespace  => 'Poli::DB',
   additional_classes => ['Class::DBI::FromForm'],
   relationships  => 1,
   );

   1;

Here is my changed (SQLite) version.  Changed only in dsn, user, and password:

freebsd% cat DB.pm
   package Poli::DB;

   use strict;
   use warnings;

   use Class::DBI::Loader;

   my $loader = Class::DBI::Loader->new(
   dsn=> 
'dbi:SQLite:database=/home/web/src/mvc/mvc_samples/sql/mvc.sqlite3',
   user   => '',
   password   => '',
   namespace  => 'Poli::DB',
   additional_classes => ['Class::DBI::FromForm'],
   relationships  => 1,
   );

   1;

In my attempt to verify the Perl code of the provided application, I see the 
following:

   freebsd% cat petition.pl
   #!/usr/bin/perl
   use strict;
   use warnings;

   use lib qw(/home/web/src/mvc/mvc_samples/cgiapp/lib);
   use Poli::CGI::Petition;

   my $app = Poli::CGI::Petition->new();
   $app->run();

   freebsd% perl -c petition.pl
   DBI connect('database=/home/web/src/mvc/mvc_samples/sql/mvc.sqlite3','',...)
   failed: unable to open database file at 
/usr/local/lib/perl5/site_perl/5.10.1/Class/DBI/Loader/SQLite.pm line 101
   unable to open database file at 
/usr/local/lib/perl5/site_perl/5.10.1/Class/DBI/Loader.pm line 83
   Compilation failed in require at 
/home/web/src/mvc/mvc_samples/cgiapp/lib/Poli/CGI/Petition.pm line 9.
   BEGIN failed--compilation aborted at 
/home/web/src/mvc/mvc_samples/cgiapp/lib/Poli/CGI/Petition.pm line 9.
   Compilation failed in require at petition.pl line 6.
   BEGIN failed--compilation aborted at petition.pl line 6.

I don't know why SQLite is unable to open the database.  When I run sqlite3 
from the
command line (%sqlite3 /home/web/src/mvc/mvc_samples/sql/mvc.sqlite3) I am able 
to
see the tables created for this application.  While empty at present, I believe 
the
database is constructed properly (per a provided DDL file).

Regards,

web...

--
William Bulley Email: w...@umich.edu

72 characters width template ->|
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rowid availability from views is inconsistent

[Alexey Pechnikov]

Hello!

On Friday 12 March 2010 11:47:40 Ben Harper wrote:
> Is there some recommended workaround that I can use to ensure that I always 
> get non-null rowids with every VIEW?
> 
> CREATE TABLE one (i32 INTEGER);
> INSERT INTO one VALUES(100);
> INSERT INTO one VALUES(200);
> CREATE VIEW v1 AS SELECT   * FROM one;
> CREATE VIEW v2 AS SELECT rowid,* FROM one;   -- My current best guess

Do you have the ROWID column in the "one" table? No. But why you want to have 
it in the results of the
"select * from one"?

System columnt ROWID must be explicitly defined in all select. As example, 
PostgreSQL has a few system columns too
and you may explicitly define these in your queries too.

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

Re: [sqlite] if exist

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
>> Yours returns 1 or 0. Mine returns length if found, otherwise 0.
> 
> That's true but the subject title led me to believe that the OP
> intended to have a 0 vs. nonzero return for not-exists vs exists condition.

Perhaps. Nevertheless, the answer to your specific question, namely "isn't the 
following exactly the same?", is no.
-- 
Igor Tandetnik

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

Re: [sqlite] if exist

[Jean-Christophe Deschamps]

>Yours returns 1 or 0. Mine returns length if found, otherwise 0.

That's true but the subject title led me to believe that the OP 
intended to have a 0 vs. nonzero return for not-exists vs exists condition.



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

Re: [sqlite] if exist

[Igor Tandetnik]

Jean-Christophe Deschamps wrote:
>> Andrea Galeazzi wrote:
>>> I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
>>> INT length.
>>> I need a statement in order to yield 0 when the key doesn't exist.
>> 
>> Well, "select 0;" fits your spec (you never said what should be
>> returned when the key does exist). I would hazard a guess that you
>> meant something like this:
>> 
>> select coalesce((select length from T where id=?), 0);
> 
> Sorry to ask but isn't the following exactly the same, but even easier
> to read?
> 
> select exists(select rowid from T where id=?);

Yours returns 1 or 0. Mine returns length if found, otherwise 0.

Igor Tandetnik

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

Re: [sqlite] rowid availability from views is inconsistent

[Ben Harper]

I only have this problem when I'm using a view on virtual tables, and since I 
have complete control over them, I guess I could just explicitly declare a 
rowid INTEGER PRIMARY KEY column for my virtual tables, and that should solve 
the problem.

Thanks,
Ben

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: 12 March 2010 11:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] rowid availability from views is inconsistent


On 12 Mar 2010, at 8:47am, Ben Harper wrote:

> Under certain conditions, VIEWs do not have rowid values.
>
> Is there some recommended workaround that I can use to ensure that I always 
> get non-null rowids with every VIEW?
>
> CREATE TABLE one (i32 INTEGER);
> INSERT INTO one VALUES(100);
> INSERT INTO one VALUES(200);
> CREATE VIEW v1 AS SELECT   * FROM one;
> CREATE VIEW v2 AS SELECT rowid,* FROM one;   -- My current best guess

Good example.  The posts I've seen here suggest that if you ever expect to 
reference the rowid number anywhere in your system you should explicitly 
declare it as a row in your table.  By using the unofficially declared 'rowid' 
you are doing something clever but as you've seen it can come back and bite 
you.  So ...

CREATE TABLE one (rowid INTEGER PRIMARY KEY, i32 INTEGER);
INSERT INTO one (i32) VALUES(100);

makes the declaration explicit.  Then if you ever use '*' it will pick up that 
column as well.

If you have a table already declared with lots of data in it, and want to 
declare this column at this point, I'm not sure whether SQLite will do it 
without creating another column.  You might be able to save some space by 
dumping the table to SQL commands, modifying the CREATE TABLE and INSERT 
instructions, then deleting and recreating the table using that file.

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] if exist

[Jean-Christophe Deschamps]

>Andrea Galeazzi wrote:
> > I've got a table T made up of only two fields: INT id (PRIMARY KEY) and
> > INT length.
> > I need a statement in order to yield 0 when the key doesn't exist.
>
>Well, "select 0;" fits your spec (you never said what should be 
>returned when the key does exist). I would hazard a guess that you 
>meant something like this:
>
>select coalesce((select length from T where id=?), 0);

Sorry to ask but isn't the following exactly the same, but even easier 
to read?

select exists(select rowid from T where id=?);




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

[sqlite] Porting Sqlite to MQX OS: Question 2

[GeoffW]

Hi

Now I have got a half ported version of Sqlite up and running on MQX I cant
put off any longer the question of file locking and how I tackle that ? For
the moment i had made the xLock() and xUnlock() the same as in osWin.c
version but with the Windows lock() and unlock() function calls commented
out.

The problem I have is that MQX's file system is just ANSI C, and it lacks
any lock/unlock functions.

As Sqlite has been ported to so many diverse operating systems I am hoping
that this question of what to do for an ANSI only File system has already
been hit and a workaround defined ?

I am pretty hazy on this topic of file locking so I would appreciate some
basic discussion and guidance. It might help if I explain my application a
little. It is an embedded system where we are just running one application,
but it is multithreaded. For my initial porting work I have made
SQLITE_THREADSAFE = 0 (so I probably dont even need a file lock in the
configuration I have at present ?)
However I would of course like to make SQLITE_THREADSAFE = 1 so that
multiple tasks can safely access a database.

Thanks for any guidance

Regards Geoff




-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-OS%3A-Question-2-tp27874124p27874124.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] rowid availability from views is inconsistent

[Simon Slavin]

On 12 Mar 2010, at 8:47am, Ben Harper wrote:

> Under certain conditions, VIEWs do not have rowid values.
> 
> Is there some recommended workaround that I can use to ensure that I always 
> get non-null rowids with every VIEW?
> 
> CREATE TABLE one (i32 INTEGER);
> INSERT INTO one VALUES(100);
> INSERT INTO one VALUES(200);
> CREATE VIEW v1 AS SELECT   * FROM one;
> CREATE VIEW v2 AS SELECT rowid,* FROM one;   -- My current best guess

Good example.  The posts I've seen here suggest that if you ever expect to 
reference the rowid number anywhere in your system you should explicitly 
declare it as a row in your table.  By using the unofficially declared 'rowid' 
you are doing something clever but as you've seen it can come back and bite 
you.  So ...

CREATE TABLE one (rowid INTEGER PRIMARY KEY, i32 INTEGER);
INSERT INTO one (i32) VALUES(100);

makes the declaration explicit.  Then if you ever use '*' it will pick up that 
column as well.

If you have a table already declared with lots of data in it, and want to 
declare this column at this point, I'm not sure whether SQLite will do it 
without creating another column.  You might be able to save some space by 
dumping the table to SQL commands, modifying the CREATE TABLE and INSERT 
instructions, then deleting and recreating the table using that file.

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

Re: [sqlite] Porting Sqlite to MQX Operating system

[GeoffW]

hello 

I thought I had better update this and confess to my sins. Good job no one
is reading this thread as it is an embarassingly stupid mistake :rules:

I cant believe I did this and then took so long to spot it.

int sqlite3_os_init(void)
{
static sqlite3_vfs mqxVfs = {
1, /* iVersion */
0,   /* szOsFile ->ARRRGGGH <*/
MAX_PATH,  /* mxPathname */
etc 

I started creating the ported file from osWin.c, I had quite a struggle
getting it to compile initially using CodeWarrior, so I was making several
temporary hacks to allow it to compile, which I then revisited later to
correct. I had replaced the sizeof(winFile) with a zero, and then forgot to
change it to sizeof(mqxFile).
This caused a fun crash down in the bowels of the paging code.

Once I corrected this one liner it fixed the crash and sqlite is basically
up and running now. I still have a fair bit of work left to implement some
interface functions which are just dummy stubs at present, but I am making
progress now.

Regards Geoff




-- 
View this message in context: 
http://old.nabble.com/Porting-Sqlite-to-MQX-Operating-system-tp27792715p27874110.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Fastest concurrent read-only performance (+ threads vsprocesses) [BUG?]

[Marcus Grimm]

Hi again,

I have played again with some variations of the speed test:

I'm not able to reproduce the reported behavior that a single process
queries faster than a single thread. During my tests I don't get a
big difference, even when I turn off some of the locking
via SQLITE_OPEN_NOMUTEX.

I have also played once more with different cache sizes:
Using a very low cache size of 2000 resulted in a query
speed of 15 sec, changing to 15 reduces to 10 sec.
So this is expected.

Shared cache mode still doesn't really affect the query speed,
this is still puzzling for me but as I allready mentioned:
For me that is only a theoretical issue since in practice
sqlite is doing very fast anyhow.

Increasing the number of threads does affect the overall
read performance slightly, example:
1 Thread: 11.2 sec
16 Threads: 8.2 sec
Single Process: 11sec.

I still would expect a better scaling, but timing measures on
the other hand are very difficult and I might be doing them wrong.

In case the test program is of interested to anybody, here
is the latest version:

http://www.exomio.de/SqliteSpeedTest2.c

Marcus

Marcus Grimm wrote:
> Hi Luke,
> 
> 
> Luke Evans wrote:
>> Hi Marcus,
>>
>> Well, I'd certainly be interested in looking at your code.  Can you mail a 
>> zip, or post to a web or file hosting site?  Thanks.
> 
> ok, please try this:
> http://www.exomio.de/SqliteSpeedTest.c
> 
> I havent tried yet to compare the numbers when using one main
> process rather than one single thread. Would be in fact really
> strange if that makes any difference. I'll try to do this using my
> test code soon.
> 
> In any case: yes, if we all understand share-cache correctly we could
> assume a nicer effect when using more than one reader thread, but
> since DRH hates threads anyhow it is unlikely that there will be
> any change... :-)
> 
> 
> Marcus
> 
> 
>> Your results seem to broadly agree with mine: multithreaded querying can 
>> save a percentage of time (10-30%?) over the same queries issued serially 
>> with no wait interval.
>> My queries are a little more complicated, so that may explain why I come in 
>> nearer a 13% saving, whereas your best case is more like 28%.  It's 
>> interesting how little the shared cache size seems to affect actual 
>> throughput, and even really whether it is on at all makes a relatively small 
>> difference here (ignoring the perceived benefits on memory pressure).
>>
>> I guess, for me the question boils down to why running these queries in 
>> their own process (which admittedly will use more memory resources) is so 
>> much more beneficial to overall throughput - when there (probably) ought to 
>> be a way to get the same partitioning between threads/connections in the 
>> multithreaded model.  In other words, I'm expecting/hoping for a way to get 
>> each thread to behave as if the query was in a separate process - getting 
>> its own private resources so that there's absolutely no need for 
>> synchronisation with activity on other threads - particularly for this 
>> read-only case.
>> For this small number of threads/queries (relative to the number of 
>> processing cores I have), the difference is between 1.6s and 8.34s, which is 
>> really quite significant. 
>>
>> -- Luke
>>
>>
>>
>> On 2010-03-11, at 5:58 AM, Marcus Grimm wrote:
>>
>>> I have followed the discussion about this issue with interest since
>>> my usage of sqlite involves threads and sharedcache mode as well.
>>> I have also generated a little speed test program that uses
>>> multible threads and shared cache to read *some* rows out of the
>>> sqlite DB. It might not help here but here are my results:
>>>
>>> Test cenario:
>>> + DB is has two tables and one index, DB size is 1.5Gb, main
>>>   table contains 150 rows, a child table has 20 * 150 rows.
>>> + Windows 7, intel i7 processor
>>> + Query is simple like "SELECT * FROM TABLE WHERE ID = ?",
>>>   ID is the primary key and thus has an index on it.
>>>   The result is used to query in a 2nd table.
>>> + page size is not changed, thus 1024 bytes on windows
>>> + Each threads opens its own DB connection, thus time
>>>   for sqlite3_open_v2 is included in the running time.
>>> + PRAGMA read_uncommitted = True;
>>> + Uses 3.6.22 - SEE Version
>>>
>>>
>>> Test-1: shared cache on, cache size = 10
>>> a) 8 queries, distributed over 8 threads: 4.6 sec
>>> b) 8 queries, distributed over 4 threads: 5.8 sec
>>> c) 8 queries, single thread: 6.3 sec
>>>
>>> Test-2: shared cache off, cache size = 10
>>> a) 8 queries, distributed over 8 threads: 5.6 sec
>>> b) 8 queries, distributed over 4 threads: 6.0 sec
>>> c) 8 queries, single thread: 6.3 sec
>>>
>>> Personally, I wouldn't call it a bug (bugs in sqlite are extremely
>>> rare), but it looks a bit disappointing how sqlite scales when
>>> multiple threads/processors are involved. I was expecting a much
>>> higher effect on read speed when shared-cache on/off is compared.
>>>
>>> I tried to pla

[sqlite] rowid availability from views is inconsistent

[Ben Harper]

Under certain conditions, VIEWs do not have rowid values.

Is there some recommended workaround that I can use to ensure that I always get 
non-null rowids with every VIEW?

CREATE TABLE one (i32 INTEGER);
INSERT INTO one VALUES(100);
INSERT INTO one VALUES(200);
CREATE VIEW v1 AS SELECT   * FROM one;
CREATE VIEW v2 AS SELECT rowid,* FROM one;   -- My current best guess


SELECT rowid,i32 FROM v1;
|100
|200

--- vs ---

SELECT rowid,i32 FROM v2;
1|100
2|200


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