Re: [sqlite] Shared cache benefit question
Just give it a try and see what happens. You just need to enable the shared cache once. I'd think the blocking would not be any different with the shared cache enabled. But you should get reduced I/O load since the cache will be larger and accessible to all threads. HTH, Ken Doug <[EMAIL PROTECTED]> wrote: I have a heavily threaded app (I know, evil) where there might be 50 threads accessing 10 databases. Each thread always calls sqlite3_open when it starts working with a database and sqlite3_close when it's done (so no sharing of handles across threads). A thread might have two or more handles open to separate databases at once. And separate threads can be working on the same database at once. It's extremely rare for a second process to ever access the databases. I'd guess typically 70% of the database activity is INSERTS or UPDATES, with 25% simple single-table SELECTS, and the occasional large SELECT (joining tables, etc). Right now every database connection has its own page cache, all the default size. Some threads do very little work and don't use their full cache, where others could definitely benefit from a larger cache. I'd like to have a single, quite large, cache that the threads share with the hopes that the 'smaller' threads would use what they need and the 'larger' threads would be able to take advantage of the larger cache size available to them. Given that, is this a good scenario for using the shared cache? I've read http://www.sqlite.org/sharedcache.html but I'm not confident enough in my understanding to know whether I'll run into more or less blocking. Thanks for any insight. Doug ___ 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] Multiple Row Updates
On Fri, Mar 21, 2008 at 07:09:29PM -0600, John Stanton scratched on the wall: > Use this sequence - > >sqlite3_prepare_v2 > while not finished >sqlite3_bind_xxx >sqlite3_step > until SQLITE_DONE >sqlite3_reset > repeat >sqlite3_finalize > > The sqlite3_reset readies the compiled statement for binding with > another value. It isn't a bad idea to put a sqlite3_clear_bindings() right before or after the _reset. Reset gets the statement ready for another transaction, but it doesn't clear any of the existing bindings. _clear_bindings resets them all to NULL. In theory, this only matters if you fail to bind all the parameters on every loop, but the defensive programmer in me likes the idea of keeping data from one update from "leaking" to the next update if something goes wrong. It also means the state of your stmt is exactly the same each time you enter your update loop, which can simplify your bind logic if your application/database uses default values. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
"Fabiano Sidler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Igor Tandetnik schrieb: >> You don't have a single row in dbapp view that has fields='surname'. >> The only record you have is one where fields='name,surname'. So no >> row matches condition, and thus no row gets deleted. Your trigger >> never even runs. > > That's weird. So Sqlite first does read from the view before actually > perform a DELETE? Of course. You seem to expect it to infer trigger parameters from the WHERE clause, but that is, in general, impossible. The WHERE clause could be arbitrarily complex (e.g. containing subselects or performing calculations on view fields). E.g. delete from dbapp where tablename||','||fields = 'employees,surname'; Or it may not have a WHERE clause at all: delete from dbapp; How would you expect this to work? > And how could I then implement what I wanted my view > to do? As far as I see, you can't. I guess it's back to the drawing board. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
Igor Tandetnik schrieb: > You don't have a single row in dbapp view that has fields='surname'. The > only record you have is one where fields='name,surname'. So no row > matches condition, and thus no row gets deleted. Your trigger never even > runs. That's weird. So Sqlite first does read from the view before actually perform a DELETE? And how could I then implement what I wanted my view to do? Thanks for your fast reply! ;) Greetings, Fabiano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
"Fabiano Sidler" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > sqlite> select * from dbapp; > employees|name,surname > sqlite> delete from dbapp where tablename='employees' and > fields='surname'; > sqlite> delete from dbapp where tablename='employees' and > fields='surname'; > sqlite> delete from dbapp where tablename='employees' and > fields='surname'; > sqlite> select * from dbapp; > employees|name,surname You don't have a single row in dbapp view that has fields='surname'. The only record you have is one where fields='name,surname'. So no row matches condition, and thus no row gets deleted. Your trigger never even runs. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is the database file created?
On Sat, 22 Mar 2008 22:06:12 +0900, you wrote: >The command line is >in my email and its result. I don't think there is any mention of in-memory >database in the feature list either. I thought it might help to improve the >documentation. I agree the http://sqlite.org/quickstart.html page should explicitly state that using no command line parameters at all does create a in-memory database, especially because it contradicts the documentation about sqlite3_open(), sqlite3_open16() and sqlite3_open_v2(): An empty filename parameter for sqlite3_open() creates a temporary on-disk database, whereas the magic filename ":memory:" refers to an in-memory database. >Thanks > >Fred -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] delete on view
Ok, I've removed that nested DELETE command. Now I have still the problem
that I can't delete tablenames with multiple field entries. The full sql
I have is as follows:
---
create table dbapp_tablenames (
tablenameID integer primary key,
tablename text not null unique on conflict ignore
);
create table dbapp_tablefields (
tablenameID integer not null,
tablefield text not null
);
insert into dbapp_tablenames (tablename)
values ('employees');
insert into dbapp_tablenames (tablename)
values ('offices');
insert into dbapp_tablefields (tablenameID,tablefield)
select tablenameID,'administration' from dbapp_tablenames where
tablename='offices';
insert into dbapp_tablefields (tablenameID,tablefield)
select tablenameID,'name' from dbapp_tablenames where tablename='employees';
insert into dbapp_tablefields (tablenameID,tablefield)
select tablenameID,'surname' from dbapp_tablenames where
tablename='employees';
create view dbapp as
select tablename,group_concat(tablefield) as fields
from dbapp_tablefields as fs
join dbapp_tablenames as ts on (ts.tablenameID=fs.tablenameID)
group by ts.tablenameID;
create trigger dbapp_insert instead of insert on dbapp begin
select raise(rollback, 'field already in table')
where (
select tablefield from dbapp_tablefields f
join dbapp_tablenames t on (f.tablenameID=t.tablenameID)
where tablefield=new.fields and t.tablename=new.tablename) is not null;
insert into dbapp_tablenames (tablename) values (new.tablename);
insert into dbapp_tablefields (tablenameID, tablefield)
select tablenameID,new.fields from dbapp_tablenames
where tablename=new.tablename;
end;
create trigger dbapp_delete instead of delete on dbapp begin
delete from dbapp_tablefields where tablefield=old.fields; /* HERE */
/*
where tablenameID=(
select tablenameID from dbapp_tablenames
where tablename=old.tablename)
and tablefield=old.fields;
*/
--where tablefield=old.fields;
delete from dbapp_tablenames
where tablename=old.tablename and not exists (
select * from dbapp_tablefields f
where f.tablenameID = dbapp_tablenames.tablenameID
and tablefield=old.fields);
end;
---
So consider the following output of this database:
---
sqlite> select * from dbapp;
employees|name,surname
offices|administration
sqlite> delete from dbapp where tablename='offices' and fields='administration';
sqlite> select * from dbapp;
employees|name,surname
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> delete from dbapp where tablename='employees' and fields='surname';
sqlite> select * from dbapp;
employees|name,surname
---
I don't get a clue how to find the error? Or is that indeed a Sqlite bug?
Thanks for answering...
Greetings,
Fabiano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is the database file created?
"Fred Janon" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > and since it doesn't show a file, I presume that sqlite does actually > support in-memory temporary databases? Yes. > Where is is documented? http://sqlite.org/c3ref/open.html the paragraph that mentions :memory: database. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Shared cache benefit question
I have a heavily threaded app (I know, evil) where there might be 50 threads accessing 10 databases. Each thread always calls sqlite3_open when it starts working with a database and sqlite3_close when it's done (so no sharing of handles across threads). A thread might have two or more handles open to separate databases at once. And separate threads can be working on the same database at once. It's extremely rare for a second process to ever access the databases. I'd guess typically 70% of the database activity is INSERTS or UPDATES, with 25% simple single-table SELECTS, and the occasional large SELECT (joining tables, etc). Right now every database connection has its own page cache, all the default size. Some threads do very little work and don't use their full cache, where others could definitely benefit from a larger cache. I'd like to have a single, quite large, cache that the threads share with the hopes that the 'smaller' threads would use what they need and the 'larger' threads would be able to take advantage of the larger cache size available to them. Given that, is this a good scenario for using the shared cache? I've read http://www.sqlite.org/sharedcache.html but I'm not confident enough in my understanding to know whether I'll run into more or less blocking. Thanks for any insight. Doug ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is the database file created?
On Sat, 22 Mar 2008 22:06:12 +0900, "Fred Janon" <[EMAIL PROTECTED]> wrote: >Thanks, I did already read that page and all the other ones. I was asking a >question about SQLLite3.exe very precisely, I thought. The command line is >in my email and its result. I don't think there is any mention of in-memory >database in the feature list either. I thought it might help to improve the >documentation. What more do you need? "At a shell or DOS prompt, enter: "sqlite3 test.db". This will create a new database named "test.db". (You can use a different name if you like.)" http://sqlite.org/quickstart.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to determine current lock state
Hello, Is there a way to determine current lock state of a database? More specifically, I’d like to be able to tell whether the main database in the current session is under SHARED lock, or under RESERVED/PENDING/EXCLUSIVE lock. This is needed for unit tests and assertions. Thanks! Igor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is the database file created?
Thanks, I did already read that page and all the other ones. I was asking a question about SQLLite3.exe very precisely, I thought. The command line is in my email and its result. I don't think there is any mention of in-memory database in the feature list either. I thought it might help to improve the documentation. Thanks Fred On 3/22/08, Christian Smith <[EMAIL PROTECTED]> wrote: > > On Sat, Mar 22, 2008 at 06:44:45PM +0900, Fred Janon wrote: > > Hi, > > > > I read the documentation, features and faq and could not find anything > that > > specifies where (which directory) the database file is stored. I > launched > > sqlite3.exe on windows without a database name, using the '.databases' > > command, I get: > > > > sqlite> .databases > > seq name file > > --- --- > > -- > > 0main > > sqlite> > > > > and since it doesn't show a file, I presume that sqlite does actually > > support in-memory temporary databases? Where is is documented? > > > SQLite supports file and/or in-memory databases. The file is wherever you > tell it to be. Start the sqlite3.exe command with an (unused) filename and > you'll see that file created when you do any writes to this new database. > > You might want to start here: > http://sqlite.org/quickstart.html > > > > > Thanks > > > > Fred > ___ > 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] Where is the database file created?
On Sat, Mar 22, 2008 at 06:44:45PM +0900, Fred Janon wrote: > Hi, > > I read the documentation, features and faq and could not find anything that > specifies where (which directory) the database file is stored. I launched > sqlite3.exe on windows without a database name, using the '.databases' > command, I get: > > sqlite> .databases > seq name file > --- --- > -- > 0main > sqlite> > > and since it doesn't show a file, I presume that sqlite does actually > support in-memory temporary databases? Where is is documented? SQLite supports file and/or in-memory databases. The file is wherever you tell it to be. Start the sqlite3.exe command with an (unused) filename and you'll see that file created when you do any writes to this new database. You might want to start here: http://sqlite.org/quickstart.html > > Thanks > > Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Where is the database file created?
Hi, I read the documentation, features and faq and could not find anything that specifies where (which directory) the database file is stored. I launched sqlite3.exe on windows without a database name, using the '.databases' command, I get: sqlite> .databases seq name file --- --- -- 0main sqlite> and since it doesn't show a file, I presume that sqlite does actually support in-memory temporary databases? Where is is documented? SQLite is cool, I started using it today, created a couple of tables, used a few selects with join with no surprise. Nice and clean. Good job SQlite team. I am doing a project on Android, I guess you'll see more and more people working on Android. Thanks Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
