Re: [sqlite] SQLite version 3.7.0
Roger Binns wrote: > On 07/21/2010 08:01 PM, Darren Duncan wrote: >> Simply substituting in 3.7.0 causes a few new test failures for me with the >> Perl >> binding, DBD::SQLite, citing "disk I/O error". > > I can't speak for the Perl binding, but some of the underlying error > handling (invalid filenames) have been tweaked between the Unix and Windows > VFS implementations. (I believe they tried to make both consistent with > each other.) > > For example with my test suite running on Windows, invalid filenames used to > get False returned from xAccess but now I get IO Error. With normal > operation there is no problem. > > What this means is that you'll need someone who understands the DBD:SQLite > tests to say what the issue is :-) On that note, I got this report from someone on Windows: Latest SVN trunk tested on win32 Strawberry perl v1.12.1 : all tests pass, no problem. ... and I was using a Unixen. I think that I'm going to test more combinations myself, tomorrow. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.0
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/21/2010 08:01 PM, Darren Duncan wrote: > Simply substituting in 3.7.0 causes a few new test failures for me with the > Perl > binding, DBD::SQLite, citing "disk I/O error". I can't speak for the Perl binding, but some of the underlying error handling (invalid filenames) have been tweaked between the Unix and Windows VFS implementations. (I believe they tried to make both consistent with each other.) For example with my test suite running on Windows, invalid filenames used to get False returned from xAccess but now I get IO Error. With normal operation there is no problem. What this means is that you'll need someone who understands the DBD:SQLite tests to say what the issue is :-) Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkxH2XEACgkQmOOfHg372QQH2QCgnneNYQ7zPcdJEpXI/xz03PG9 VQwAniRb9nSfitGhfeF50AUpwRg6jWtF =s4aM -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_ABORT is set instead of SQLITE_SCHEMA when precompiled statement expires
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 07/21/2010 12:22 PM, Aleksander Morgado wrote: > Now, apart from the issue above, is there any other method to know if a > precompiled statement expired? I see that sqlite3_expired() is marked as > deprecated, so what would be the equivalent? The theory is that if you use prepare_v2 then SQLITE_SCHEMA is handled internally so the expired stuff is not relevant. In practise you get different error codes between issuing a fresh prepare and a statement that has been expired. More gory details at: http://www.sqlite.org/src/info/8d510499cc Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkxHxCsACgkQmOOfHg372QTmVgCfVa9y4gKaY5a0lKrMV0LP5get 9dEAoKZu7YPNtqQexHxwC79/PU8KJbtj =rrdp -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.7.0
D. Richard Hipp wrote: > Of course, if you do happen to run into problems, please let me know at once. > Thanks! Simply substituting in 3.7.0 causes a few new test failures for me with the Perl binding, DBD::SQLite, citing "disk I/O error". However, it is more likely that the problem is in DBD::SQLite or on my machine, than in SQLite itself, and will be investigated. If its not just me, then one can see the problem for themselves by downloading http://search.cpan.org/CPAN/authors/id/A/AD/ADAMK/DBD-SQLite-1.30_03.tar.gz then running "perl util/getsqlite.pl 3.7.0" and then building and running "make test". Building the same version pristine, without the "perl util/getsqlite.pl 3.7.0", passes all tests. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very Slow DB Access After Reboot on Windows
On 21 Jul 2010, at 4:56pm, Samet YASLAN wrote: > I have a 30MB DB file with 4 tables. > Execution time for a query is 1 sec normally but it is like 40 secs > after restarting window. > This seems to be related with file caching of Windows. The same source > code does not cause this problem on Linux. What is the name of the database file ? I understand that there is a Windows bug where it tries to cache all of files with certain extensions like '.db'. Try changing the filename extention to '.sqlite' or something else unusual. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite website bug
Hy. I think i found a "bug" on the website. Please take a look. from the news page of the website: --- 2009-Sep-11 - Version 3.6.18 Beginning with this release, the SQLite source code is tracked and managed using the Fossil distributed configuration management system. SQLite was previously versioned using CVS. The entire CVS history has been imported into Fossil. The older CVS repository remains on the website [ but is not read-only. ] -- This sentence with this ending does not make sense to me. If "but" is used that means that the cvs IS read-only. The case that it is NOT read-only seems wrong anyway. Best regards. Klein Tamás Márton (A happy user of sqlite) +36304771400 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_ABORT is set instead of SQLITE_SCHEMA when precompiled statement expires
Hi all,
I'm using sqlite 3.6.22-1 in ubuntu 10.04.
If a precompiled statement become expired, and sqlite3_step() is called,
documentation says that it will fail with a SQLITE_SCHEMA error.
/* Opcode: Expire P1 * * * *
**
** Cause precompiled statements to become expired. An expired
statement
** fails with an error code of SQLITE_SCHEMA if it is ever
executed
** (via sqlite3_step()).
**
** If P1 is 0, then all SQL statements become expired. If P1 is
non-zero,
** then only the currently executing statement is affected.
*/
case OP_Expire: {
if( !pOp->p1 ){
sqlite3ExpirePreparedStatements(db);
}else{
p->expired = 1;
}
break;
}
Above, p->expired is set to 1 for the current statement. But when
opening a read-only or read-write cursor for the database table during a
sqlite3_step() with that statement, if the expired flag is found set,
SQLITE_ABORT is set as error code, instead of SQLITE_SCHEMA, as the
documentation says, which seems an error:
case OP_OpenRead:
case OP_OpenWrite: {
int nField;
KeyInfo *pKeyInfo;
int p2;
int iDb;
int wrFlag;
Btree *pX;
VdbeCursor *pCur;
Db *pDb;
if( p->expired ){
--rc = SQLITE_ABORT;
++rc = SQLITE_SCHEMA;
break;
}
Now, apart from the issue above, is there any other method to know if a
precompiled statement expired? I see that sqlite3_expired() is marked as
deprecated, so what would be the equivalent?
Cheers,
--
Aleksander
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very Slow DB Access After Reboot on Windows
I have a 30MB DB file with 4 tables. Execution time for a query is 1 sec normally but it is like 40 secs after restarting window. This seems to be related with file caching of Windows. The same source code does not cause this problem on Linux. Any ideas? -- *Samet YASLAN* ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.7.0
SQLite version 3.7.0 is now available on the website http://www.sqlite.org/ The most important change in version 3.7.0 is that SQLite now supports write-ahead logs as an optional method for transaction control, for improved performance and concurrency. Additional information can be found here: http://www.sqlite.org/wal.html We are actually already using the write-ahead logging feature on the SQLite website itself, in the Fossil DVCS that tracks all changes to the SQLite source tree. (Yes, the SQLite write-ahead log code is stored in an SQLite write-ahead log database - how's that for recursion!) http://www.sqlite.org/src The added concurrency of the write-ahead log journaling mode allows multiple users to be doing extended read operations, such as checking out historical versions of the SQLite code or looking at extended timelines simultaneously with developers making new checkins, adding or editing tickets, or actually rebuilding the entire 10-year source code database. The write-ahead log code has performed very well so far for us. Version 3.7.0 also marks the official cut-over to our new SQLite logo and a new color scheme for the website. We hope you like the new look. The 114-day time span since the previous release (version 3.6.23.1) is the longest span between consecutive releases in the 10-year history of SQLite. Much of that time was spent testing and stressing the new write-ahead log feature. This is probably the most thoroughly tested release of SQLite that we have every produced. And so even though the write-ahead logging feature is entirely new, we are very hopeful that the 3.7.0 release will prove to be stable and robust and ready for production use. Of course, if you do happen to run into problems, please let me know at once. Thanks! 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] UPDATEing a SELECTion in one shot
Kees Nuyt, out of the goodness of his heart, wrote:
> On Wed, 21 Jul 2010 14:28:12 -0400, "jose isaias cabrera"
> wrote:
>
>>
>>Greetings and salutations.
>>
>>I believe this is possible, but I can not seem to get the syntax from the
>>site. I have these 3 commands and 3 different steps that I want to put
>>into
>>one:
>>
>>1. result1 = SELECT sum(price) FROM table1 WHERE subProjID = 24;
>>2. result2 = SELECT sum(price) FROM table2 WHERE ProjID = 8;
>>3. BEGIN;
>> UPDATE LSOpenSubProjects SET price = result1 WHERE subProjID = 24;
>> UPDATE LSOpenSubProjects SET udate = now WHERE subProjID = 24;
>> UPDATE LSOpenProjects SET price = result2 WHERE ProjID = 8;
>> UPDATE LSOpenProjects SET udate = now WHERE ProjID = 8;
>>END;
>>
>>How can I put all of these 3 steps into one?
>
> BEGIN IMMEDIATE TRANSACTION;
> UPDATE LSOpenSubProjects
> SET
> price = (
> SELECT sum(price)
> FROM table1
> WHERE subProjID = 24
> ),
> udate = julianday('now')
> WHERE subProjID = 24;
> UPDATE LSOpenProjects
> SET
> price = (
> SELECT sum(price)
> FROM table2
> WHERE subProjID = 8
> ),
> udate = julianday('now')
> WHERE ProjID = 8;
> COMMIT TRANSACTION;
>
> (untested)
thanks. This one is also nice.
josé
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATEing a SELECTion in one shot
On Wed, 21 Jul 2010 14:28:12 -0400, "jose isaias cabrera"
wrote:
>
>Greetings and salutations.
>
>I believe this is possible, but I can not seem to get the syntax from the
>site. I have these 3 commands and 3 different steps that I want to put into
>one:
>
>1. result1 = SELECT sum(price) FROM table1 WHERE subProjID = 24;
>2. result2 = SELECT sum(price) FROM table2 WHERE ProjID = 8;
>3. BEGIN;
> UPDATE LSOpenSubProjects SET price = result1 WHERE subProjID = 24;
> UPDATE LSOpenSubProjects SET udate = now WHERE subProjID = 24;
> UPDATE LSOpenProjects SET price = result2 WHERE ProjID = 8;
> UPDATE LSOpenProjects SET udate = now WHERE ProjID = 8;
>END;
>
>How can I put all of these 3 steps into one?
BEGIN IMMEDIATE TRANSACTION;
UPDATE LSOpenSubProjects
SET
price = (
SELECT sum(price)
FROM table1
WHERE subProjID = 24
),
udate = julianday('now')
WHERE subProjID = 24;
UPDATE LSOpenProjects
SET
price = (
SELECT sum(price)
FROM table2
WHERE subProjID = 8
),
udate = julianday('now')
WHERE ProjID = 8;
COMMIT TRANSACTION;
(untested)
>thanks,
>
>josé
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATEing a SELECTion in one shot
Pavel Ivanov willingly and out of the goodness of his heart wrote: > If the following can be considered as one step then do it like this: > > BEGIN; > UPDATE LSOpenSubProjects > SET price = (SELECT sum(price) FROM table1 WHERE subProjID = 24), >udate = now > WHERE subProjID = 24; > UPDATE LSOpenProjects > SET price = (SELECT sum(price) FROM table2 WHERE ProjID = 8), >udate = now > WHERE ProjID = 8; > END; It is one step for me. :-) Thanks. I wish I had written to the list without spending two hours trying to write this. Thanks. josé > > On Wed, Jul 21, 2010 at 2:28 PM, jose isaias cabrera > wrote: >> >> Greetings and salutations. >> >> I believe this is possible, but I can not seem to get the syntax from the >> site. I have these 3 commands and 3 different steps that I want to put >> into >> one: >> >> 1. result1 = SELECT sum(price) FROM table1 WHERE subProjID = 24; >> 2. result2 = SELECT sum(price) FROM table2 WHERE ProjID = 8; >> 3. BEGIN; >> UPDATE LSOpenSubProjects SET price = result1 WHERE subProjID = 24; >> UPDATE LSOpenSubProjects SET udate = now WHERE subProjID = 24; >> UPDATE LSOpenProjects SET price = result2 WHERE ProjID = 8; >> UPDATE LSOpenProjects SET udate = now WHERE ProjID = 8; >> END; >> >> How can I put all of these 3 steps into one? >> >> thanks, >> >> josé >> >> ___ >> 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] UPDATEing a SELECTion in one shot
If the following can be considered as one step then do it like this: BEGIN; UPDATE LSOpenSubProjects SET price = (SELECT sum(price) FROM table1 WHERE subProjID = 24), udate = now WHERE subProjID = 24; UPDATE LSOpenProjects SET price = (SELECT sum(price) FROM table2 WHERE ProjID = 8), udate = now WHERE ProjID = 8; END; Pavel On Wed, Jul 21, 2010 at 2:28 PM, jose isaias cabrera wrote: > > Greetings and salutations. > > I believe this is possible, but I can not seem to get the syntax from the > site. I have these 3 commands and 3 different steps that I want to put into > one: > > 1. result1 = SELECT sum(price) FROM table1 WHERE subProjID = 24; > 2. result2 = SELECT sum(price) FROM table2 WHERE ProjID = 8; > 3. BEGIN; > UPDATE LSOpenSubProjects SET price = result1 WHERE subProjID = 24; > UPDATE LSOpenSubProjects SET udate = now WHERE subProjID = 24; > UPDATE LSOpenProjects SET price = result2 WHERE ProjID = 8; > UPDATE LSOpenProjects SET udate = now WHERE ProjID = 8; > END; > > How can I put all of these 3 steps into one? > > thanks, > > josé > > ___ > 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] UPDATEing a SELECTion in one shot
Greetings and salutations. I believe this is possible, but I can not seem to get the syntax from the site. I have these 3 commands and 3 different steps that I want to put into one: 1. result1 = SELECT sum(price) FROM table1 WHERE subProjID = 24; 2. result2 = SELECT sum(price) FROM table2 WHERE ProjID = 8; 3. BEGIN; UPDATE LSOpenSubProjects SET price = result1 WHERE subProjID = 24; UPDATE LSOpenSubProjects SET udate = now WHERE subProjID = 24; UPDATE LSOpenProjects SET price = result2 WHERE ProjID = 8; UPDATE LSOpenProjects SET udate = now WHERE ProjID = 8; END; How can I put all of these 3 steps into one? thanks, josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cost of PRAGMA database_list
On Wed, Jul 21, 2010 at 11:23 AM, Simon Slavin wrote: > > On 21 Jul 2010, at 3:53pm, Sam Carleton wrote: > > > In the end, I am looking for the best way to determine if the connection > > contains the correct EventDB, since a connection can live for a LONG LONG > > time. > > Sorry, I misunderstood. I thought that your process lasted only long > enough to answer one HTTP query. > > > Right now I am using PRAGMA database_list to get the list, find if there > is > > an EventDB and if so, is it the correct one, each and every time the > system > > gets a connection from the pool. This could happen between 3 to 8 times > in > > one request. > > Could you keep, for each connection, its own independent variable with the > currently connected EventDB path in ? Since only this routine establishes a > connection nothing can change it behind its back. Keeping the information > in your own variable will be much 'cheaper' than having to execute an SQLite > command and parse the result every time. > > In other words, "I am connection number 2. I am new and I have no been > ATTACHed to any EventDB." or "I am connection number 2. I have been > ATTACHed to EventDB X." > Well, since DBD is part of the Apache Foundation, I can hack the code all I like, though I would prefer not. By default my code knows nothing about a connection other than it is a connection to the database. > > The question is: Is it worth my time to hook into the whole request > process > > before any other part of my modules run and do this check only once, so > the > > rest of the code that gets the same connection 3 to 8 times can skipt the > > check, or is the cost of this check so small that it is not worth my time > to > > optimize? > > Since all 3 (or 8) calls to service a single HTTP request happen within a > short time, I suspect that for each HTTP request it's only worth checking > only once. > I could not agree more then it will be more efficient of the code to only do the check once. But I must factor in the efficiency of my time as a developer and the efficiency of code maintenance, as well. For a time perspective, what I have is working well, if the execution cost is low, let it do it 3 to 8 times and I can refocus on what my customers really care about, the business logic. If it is a really expensive, then I have to identify the correct hook to get that will guarantee me that it will be called before anything else in the request, but not called too early that connections cannot be had, I have to implement the hook. And the biggest thing is maintenance, if I start relying on this hook to be before anything else, I need to make sure that it stays that way in the future. Right now I don't have any early hooking stuff, but that might change in the future, one never knows. What a headache to chase down if another hook is put in front of this one because the knowledge was lost on the exact details of why this hook happened when and where it happens. So from my time and long term maintenance, what I have now is the 'cheapest' solution. But the question is, how big of a performance price will I pay for this 'cheap' solution? I know my code is really short and fast and as far as I am concerned a zero on the cost scale, especially on modern machines. I am wondering how much work there is to actually preform the PRAGMA database_list, is that speedy fast because it is all in memory, or is there some actual disk IO that needs to happen to return the result set? If it is all in memory, I am guessing that it, like my code has a cost close to zero, if it has to hit a file, then I don't know and I am hoping someone with a better understanding of how SQLite works might know. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cost of PRAGMA database_list
On 21 Jul 2010, at 3:53pm, Sam Carleton wrote: > In the end, I am looking for the best way to determine if the connection > contains the correct EventDB, since a connection can live for a LONG LONG > time. Sorry, I misunderstood. I thought that your process lasted only long enough to answer one HTTP query. > Right now I am using PRAGMA database_list to get the list, find if there is > an EventDB and if so, is it the correct one, each and every time the system > gets a connection from the pool. This could happen between 3 to 8 times in > one request. Could you keep, for each connection, its own independent variable with the currently connected EventDB path in ? Since only this routine establishes a connection nothing can change it behind its back. Keeping the information in your own variable will be much 'cheaper' than having to execute an SQLite command and parse the result every time. In other words, "I am connection number 2. I am new and I have no been ATTACHed to any EventDB." or "I am connection number 2. I have been ATTACHed to EventDB X." > The question is: Is it worth my time to hook into the whole request process > before any other part of my modules run and do this check only once, so the > rest of the code that gets the same connection 3 to 8 times can skipt the > check, or is the cost of this check so small that it is not worth my time to > optimize? Since all 3 (or 8) calls to service a single HTTP request happen within a short time, I suspect that for each HTTP request it's only worth checking only once. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cost of PRAGMA database_list
On Wed, Jul 21, 2010 at 9:47 AM, Simon Slavin wrote: > > On 21 Jul 2010, at 2:30pm, Sam Carleton wrote: > > > My issue with only attaching to the EventDB for only as long as a command > is > > trying to use it is: The EventDB is used a LOT, a WHOLE lot. If I > attach > > and drop between each little call, then within one HTTP request I might > > attach and drop 3 to 8 times. What is the cost associated with > > attaching/dropping a database? Since it involves attaching to a physical > > file, I am assuming it is a bit high. > > > > As far as telling my application when the EventDB file is out of date: > It > > would be pretty easy for me to add a timestamp to the EventDB variable > that > > is in the primary database, but... Once I get a connection from the > > connection pool, how do I know the age of that connection to know if the > > EventDB it is attached to is the new one or the old one? Is there some > > connection age or timestamp I could use? I am guessing not, which is why > I > > am using the PRAGMA database_list to determine if the EventDB is attached > > and if so, find the physical path to the EventDB file to see if it > matches > > with the current system settings. > > Ah. If all you are doing is replying to one HTTP request then that'll take > less than one second, right ? You don't have to worry about the 'right' > EventsDB file changing during a request because it doesn't last long enough. > Your users will have no way of telling if their data is almost one entire > second out of date. So you don't need a complicated semaphore method to > communicate with existing processes, all you need is a way to tell a new > process which events database file to attach. > > As you note, this can be done various ways: put another text file on the > server called 'currentevents.txt' and in that file put the full path of the > current event.sqlite file. Or you could use an 'include' file for your PHP > code, or whatever language you're using for your web backend, and write > software to rewrite that 'include' file. Or you could store the path as > something that can be looked up in another SQLite database, though that > would be a little more 'expensive'. > Simon, For what it matters, the code is not PHP, it is 100% C/C++ in a custom Apache Module and in Axis2/C Web Services. As far as how to communicate to the modules (Apache Modules and Axis2/C Web Services) what the current EventDB file and when it was change is already taken care of, has been for a long time now. Here is what has changed: In the old code, the modules where *NOT* using connection pools, each part of a request would open a connection to the database it needed to talk to (the system DB or EventDB), do unto the DB what it needed and closed the connection. With this approach the EventDB was open/closes each and every time. The problem though is that one request might open/close the System DB 1 to 3 times and EventDB 3 to 6 times! The clients where dropping connections because of DB errors, it looked like it was errors in closing the DB. When I looked at the new features on Apache 2.2, at the very bottom of the page I saw this "DBD Framework (SQL Database API)": http://httpd.apache.org/docs/2.2/new_features_2_2.html The last paragraph on the DBD Framework states "New modules SHOULD now use these APIs for all SQL database operations. Existing applications SHOULD be upgraded to use it where feasible, either transparently or as a recommended option to their users." So that is what I am doing, upgrading to DBD Framework. How the DBD works is this: When the web server starts up it connection to one DB, in my case the SystemDB. As the name implies it is system wide and will never change for the life of the server. At load time (of the Apache server) the min, max, and keep numbers are defined. The end result is: If you define a min of 4, 20, and 8 respectfully, there will ALWAYS be 4 connection to the SystemDB, a max of 20 connections and once 8 connections are created a normal minimum of 8. Each time a request asks for a connection, the DBD gives it one. A VERY important fact is the connection a HTTP request gets will be the same though out the life of that request. What this means to my modules is: When an module gets a connection it is from this pool which might have been one of the original 4, one of the other connections created when the system was under load, or a brand new one that was created for the request. The whole idea is the DBD manages all that for the business logic. As far as raw performance, that goes way up because connections are not being created and destoried all the time. The drawback for me, though is that in my case, the second DB, the EventDB can change at ANYTIME. It could be that the server has been sitting 100% idle for a few hours, the admin changes the EventDB, then goes to access the new one. The 1st 4 connection the request gets from the pool will be one of the 4 minimum con
Re: [sqlite] Cost of PRAGMA database_list
On 21 Jul 2010, at 2:30pm, Sam Carleton wrote: > My issue with only attaching to the EventDB for only as long as a command is > trying to use it is: The EventDB is used a LOT, a WHOLE lot. If I attach > and drop between each little call, then within one HTTP request I might > attach and drop 3 to 8 times. What is the cost associated with > attaching/dropping a database? Since it involves attaching to a physical > file, I am assuming it is a bit high. > > As far as telling my application when the EventDB file is out of date: It > would be pretty easy for me to add a timestamp to the EventDB variable that > is in the primary database, but... Once I get a connection from the > connection pool, how do I know the age of that connection to know if the > EventDB it is attached to is the new one or the old one? Is there some > connection age or timestamp I could use? I am guessing not, which is why I > am using the PRAGMA database_list to determine if the EventDB is attached > and if so, find the physical path to the EventDB file to see if it matches > with the current system settings. Ah. If all you are doing is replying to one HTTP request then that'll take less than one second, right ? You don't have to worry about the 'right' EventsDB file changing during a request because it doesn't last long enough. Your users will have no way of telling if their data is almost one entire second out of date. So you don't need a complicated semaphore method to communicate with existing processes, all you need is a way to tell a new process which events database file to attach. As you note, this can be done various ways: put another text file on the server called 'currentevents.txt' and in that file put the full path of the current event.sqlite file. Or you could use an 'include' file for your PHP code, or whatever language you're using for your web backend, and write software to rewrite that 'include' file. Or you could store the path as something that can be looked up in another SQLite database, though that would be a little more 'expensive'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cost of PRAGMA database_list
On Tue, Jul 20, 2010 at 8:34 PM, Simon Slavin wrote: > > On 21 Jul 2010, at 12:42am, Sam Carleton wrote: > > > There are two equally > > important requires, one is to connect to the second "EventDB", the other > is > > that the system admin can change the EventDB at any time. > > You mean change the contents of that file, or change which file is > nominated as the EventDB file ? > the later, at one point in time EventDB might be D:\Events\Hopeville Classic\event.sqlite and later it will be D:\Events\Main Street Classic\event.sqlite True that it doesn't change often, but the admin can change it at will. > > > The second > > requirement means that connection can be attached to a database called > > EventDB, but it is the wrong (old) DB. > > What signifies the 'right' EventDB ? Are you creating at least two > different files (a right one and a wrong one) and giving them the same > filename in the same directory ? If they're different files they should > have different filenames. Some of your questions are answered above. Ultimately the concept is an "event" is like a word processor document or spreadsheet document. The event.sqlite file is only one physical file which makes up the whole of an "event". All the contents of the folder (Hopeville Classic or Main Street Classic) in which the event.sqlite resides makes up the whole of the "event". The system admin can, at will, go in and "load" a new "event", aka point the system to a new folder or simply create a whole new event. > It seems to me that you either need to attach an EventDB file only as long > as a command is trying to use it. If you can't do that you need a way to > tell your application that the EventsDB file it's currently connected to is > out of date, and it's time to go find a new EventDB file. > First off, please remember that I am doing in a web server that is using connection pooling. This is a very important fact... My issue with only attaching to the EventDB for only as long as a command is trying to use it is: The EventDB is used a LOT, a WHOLE lot. If I attach and drop between each little call, then within one HTTP request I might attach and drop 3 to 8 times. What is the cost associated with attaching/dropping a database? Since it involves attaching to a physical file, I am assuming it is a bit high. As far as telling my application when the EventDB file is out of date: It would be pretty easy for me to add a timestamp to the EventDB variable that is in the primary database, but... Once I get a connection from the connection pool, how do I know the age of that connection to know if the EventDB it is attached to is the new one or the old one? Is there some connection age or timestamp I could use? I am guessing not, which is why I am using the PRAGMA database_list to determine if the EventDB is attached and if so, find the physical path to the EventDB file to see if it matches with the current system settings. I am VERY open to any ideas that will help me improve the performance and design, I simply am not seeing any. I know for a fact that I am using both the Web Server and the database is a none classical ways, which makes for all sorts of funny requirements. The whole objective of my system is to allow none technical folks setup and manage a web server on an intranet and easily back things up without knowing a darn thing about web servers or databases! So far folks love the system, but the hoops I have to jump through to make it all work seamlessly, wow! Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Google suggestions using FTS3
We are at the early stages of development of a full text search facility on a small (<5000 rows) database with a few text fields in an embedded application. One of our requirements is to implement Google-like suggestion prompts as the user enters the search string. A similar requirements was discussed in a previous thread (http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2009-December/018352.html ) that seems to indicate that it was possible to search the keyword database and that performance enhancements were in the pipeline that would improve the performance of this search. Does anyone have any advice or practical experience to help us here. Dave ** This message is confidential and intended only for the addressee. If you have received this message in error, please immediately notify the postmas...@nds.com and delete it from your system as well as any copies. The content of e-mails as well as traffic data may be monitored by NDS for employment and security purposes. To protect the environment please do not print this e-mail unless necessary. NDS Limited. Registered Office: One London Road, Staines, Middlesex, TW18 4EX, United Kingdom. A company registered in England and Wales. Registered no. 3080780. VAT no. GB 603 8808 40-00 ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
