Re: [sqlite] When to close a db, take 2...
On Mon, Jun 21, 2010 at 11:56:09PM -0400, Sam Carleton scratched on the wall:
> I am NEVER capturing anything in the log file, so it looks like everything
> is returning the correct. I am a bit stumped right now, so if it is alright,
> I am going to post my code:
If you're doing a double close, it won't log an error, it will just crash.
> void CSQLiteDB::Close()
> {
> if(m_db)
> {
> sqlite3 *db = m_db;
> m_db == NULL;
No.
> sqlite3_close(db);
> }
> }
If you're manually calling ::Close() anywhere, when the object is
destroyed it will do a double close.
-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
[sqlite] When to close a db, take 2...
Ok, after a bit of testing this evening, the close which is crashing the
system has ALWAYS been the same close statement, the close after the Apache
Module has initialized the data structure to process the request. I went in
and made sure that each and every sqlite call is log if there is a result an
error. I also did the same for the authentication code, which is where I
saw the close crash once or twice yesterday.
I am NEVER capturing anything in the log file, so it looks like everything
is returning the correct. I am a bit stumped right now, so if it is alright,
I am going to post my code:
/* *** */
/* Basic sqlite class to manage the open/close */
/* *** */
class CSQLiteDB
{
public:
CSQLiteDB();
~CSQLiteDB(void);
int OpenV2( const char *filename, int flags, const char *zVfs );
void Close();
operator sqlite3*() { return m_db; }
private:
static int BusyHandler_Callback(void* data, int count);
int BusyHandler(int count);
sqlite3 *m_db;
int m_sleepTime;
};
int CSQLiteDB::BusyHandler_Callback(void* data, int count)
{
CSQLiteDB *This = (CSQLiteDB*) data;
return This->BusyHandler(count);
}
CSQLiteDB::CSQLiteDB()
: m_db(NULL)
, m_sleepTime(0)
{
}
CSQLiteDB::~CSQLiteDB()
{
Close();
}
int CSQLiteDB::OpenV2( const char *filename, int flags, const char *zVfs)
{
int rc = sqlite3_open_v2(filename, &m_db, flags, zVfs);
if( rc == SQLITE_OK)
{
sqliteExtInit(m_db);
rc = sqlite3_busy_handler( m_db, CSQLiteDB::BusyHandler_Callback,
this);
}
return rc;
}
void CSQLiteDB::Close()
{
if(m_db)
{
sqlite3 *db = m_db;
m_db == NULL;
sqlite3_close(db);
}
}
int CSQLiteDB::BusyHandler(int count)
{
// sum of 1 to n = (n * (n + 1)) / 2
// the range is from 9 to 18 (45ms to 171ms, total 1020)
if( count > 9 )
return 0;
if(count > 1)
{
m_sleepTime += count + 8;
}
else
{
m_sleepTime = 45;
}
Sleep( m_sleepTime);
return 1;
}
/* * */
/* function that open/closes the db, and where we find the crash */
/* * */
CSQLiteDB db;
const char * pszSystemDBFile = GetSystemDBFile();
int rc = db.OpenV2( pszSystemDBFile,
SQLITE_OPEN_READONLY|SQLITE_OPEN_FULLMUTEX, NULL);
if(rc == SQLITE_OK)
{
rc = db_LoadDbConfigModuleSettings(r, db, m_configSetting);
if(rc == SQLITE_OK)
{
if (m_configSetting->pszRootDir)
{
m_configSetting->pszEventFile = ap_server_root_relative(
r->pool, apr_pstrcat( r->pool, GetRootDir(), "/event.xml", NULL));
m_configSetting->pszEventDB = ap_server_root_relative(
r->pool, apr_pstrcat( r->pool, GetRootDir(), EVENT_DB_FILENAME, NULL));
}
else
{
ap_log_rerror(APLOG_MARK, APLOG_ERR, APR_SUCCESS, r, "The
\'Event Folder\' is NOT set yet.");
}
}
else
{
ap_log_rerror(APLOG_MARK, APLOG_ERR, APR_SUCCESS, r, "Unable to
load configuration (%d): %s", rc, sqlite3_errmsg(db));
}
}
else
{
ap_log_rerror(APLOG_MARK, APLOG_ERR, APR_SUCCESS, r, "Unable to open
promenade db file (%d): %s", rc, GetSystemDBFile());
}
db.Close(); /* THE CRASH */
/* ** */
/* function that creates the prepare statement */
/* ** */
int db_LoadDbConfigModuleSettings(request_rec *r, sqlite3 *db, struct
photo_parata_cfg * pCfg)
{
sqlite3_stmt *stmt = NULL;
const char* pzTail = NULL;
int rc = sqlite3_prepare_v2(db,
SQL_CMD(DBLOOKUP_GET_CATEGORY_VALUE_SQL), &stmt, &pzTail);
if( rc == SQLITE_OK)
{
rc = GetDirectoryStringValue(r, db, stmt, SYSTEMSETTINGS, ROOTDIR,
pCfg, offsetof(photo_parata_cfg, pszRootDir));
if( ::IsLicenseType( pCfg->eLicenseType, pltProfessional))
{
if( rc == SQLITE_OK) rc = GetIntValue(r, db, stmt,
SYSTEMSETTINGS, DEFAULTISINSLIDESHOW, pCfg, offsetof(photo_parata_cfg,
iDefaultInSlideShow));
if( rc == SQLITE_OK) rc = GetIntValue(r, db, stmt, KVSSETTINGS,
ENABLEFAVORITES, pCfg, offsetof(photo_parata_cfg, iEnableFavorites));
if( rc == SQLITE_OK) rc = GetIntValue(r, db, stmt, KVSSETTINGS,
ISLOGINREQUIRED, pCfg, offsetof(photo_parata_cfg, iIsLoginRequired));
}
}
else
{
ap_log_rerror(APLOG_MARK, APLOG_ERR, APR_SUCCESS, r, "%s| Unable to
prepare statement (%d): %s", GetProcessId(r), rc, sqlite3_errmsg(db));
}
if(stmt) {
rc = sqlite3_finalize(stmt);
if( rc != SQLITE_OK) ap_log_rerror(APLOG_MARK, APLOG_ERR,
APR_SUCCESS, r, "%s| sqlite3_finalize() --> (%d): %s", GetProces
Re: [sqlite] how often to open the db
On Mon, Jun 21, 2010 at 10:44 PM, Sam Carleton wrote: > I have asked this Q a number of times over the last year and NEVER gotten > ANYONE to even comment on it. I am wondering why: > > Am I opening the DB too much? what is too much? I mean, the computer is not going to get tired. But, are you experiencing speed bottlenecks? > > My usage of SQLite is in an Apache module that opens the DB each time it > needs info from the DB: For authentication it is open/closed, for the > initialization of the request the db is open/closed, for the processing of > the request, the db is opened and closed. If all of the above is happening within one logical transaction (and, I don't mean, a db transaction -- since you mentioned Apache, I mean, a "job," a "click" from the user's point of view), you certainly can and should open a single db connection, do everything you want to do, close the db, and then return the result to the user. At least, that is how I do it. In fact, once the Apache process ends, the db connection should close automatically, although it doesn't hurt to close it explicitly. > > Is this OK, or should I open it once for the whole request and close it when > the whole request is finished? > > Sam > ___ > 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] how often to open the db
I have asked this Q a number of times over the last year and NEVER gotten ANYONE to even comment on it. I am wondering why: Am I opening the DB too much? My usage of SQLite is in an Apache module that opens the DB each time it needs info from the DB: For authentication it is open/closed, for the initialization of the request the db is open/closed, for the processing of the request, the db is opened and closed. Is this OK, or should I open it once for the whole request and close it when the whole request is finished? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error in date.test with encryption codec
The date14 test uses hexio_write, which is invalid when opened with an encryption codec Regards, Noah Hart -- View this message in context: http://old.nabble.com/Error-in-date.test-with-encryption-codec-tp28953028p28953028.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] Error in pagesize.test with encryption codec
pagesize returns incorrect results when using a codec with an encryption
routine,
since the Vacuum is not allowed to change the page size of an encrypted
database
Suggested changes, change the test to open the databases without encryption
by adding
-key {}
to all the
sqlite3 db test.db
statements
Regards,
Noah Hart
--
View this message in context:
http://old.nabble.com/Error-in-pagesize.test-with-encryption-codec-tp28952580p28952580.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] Error in exclusive2.test with encryption codec
exclusive2returns incorrect results when using a codec with an encryption
routine,
since the pagerChangeCounter reads directly from the file
Suggested changes, run the test without encryption
Line 23
+# Disable encryption on the database for this test.
+sqlite3 db test.db -key {}
Regards,
Noah Hart
--
View this message in context:
http://old.nabble.com/Error-in-exclusive2.test-with-encryption-codec-tp28952460p28952460.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] Error in nan.test with encryption codec
nan returns incorrect results when using a codec with an encryption routine,
since test nan-3.1 reads directly from the file via hexio_read test.db
2040 8
Suggested changes, run the test without encryption, or skip test 14 when
running with a codec
line 27:
+# Disable encryption on the database for this test.
+sqlite3 db test.db -key {}
167:
- sqlite3 db test.db
+ sqlite3 db test.db -key {}
173:
- sqlite3 db test.db
+ sqlite3 db test.db -key {}
179:
- sqlite3 db test.db
+ sqlite3 db test.db -key {}
185:
- sqlite3 db test.db
+ sqlite3 db test.db -key {}
Regards,
Noah Hart
--
View this message in context:
http://old.nabble.com/Error-in-nan.test-with-encryption-codec-tp28952408p28952408.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] Error in pcache.test with encryption codec
pcachereturns incorrect results when using a codec with an encryption
routine,
since test pcache-1.14 writes directly to offset 24 in the header
Suggested changes, run the test without encryption, or skip test 14 when
running with a codec
line 31:
- sqlite3 db test.db
+sqlite3 db test.db -key {}
Regards,
Noah Hart
--
View this message in context:
http://old.nabble.com/Error-in-pcache.test-with-encryption-codec-tp28952275p28952275.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] Error in vacuum2.test with encryption codec
vacuum2 returns incorrect results when using a codec with an encryption
routine
Suggested changes:
line 31:
+# Disable encryption on the database for this test.
sqlite3 db test.db -key {}
Change line 70
- sqlite3 db2 test.db
+sqlite3 db2 test.db -key {}
Noah Hart
--
View this message in context:
http://old.nabble.com/Error-in-vacuum2.test-with-encryption-codec-tp28952227p28952227.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] Error in filefmt.test with encryption codec
filefmt return incorrect results when using a codec with an encryption
routine
filefmt-1.1...
Expected: [53514C69746520666F726D6174203300]
Got: [19C10CA15F9AB24A612E680FE7816B25]
This happens because the sqlite db command in TCL opens the database with
the option -key {xyzzy}
when compiled with a codec
Suggested fix
do_test filefmt-1.1 {
sqlite3 db test.db
db eval {CREATE TABLE t1(x)}
db close
hexio_read test.db 0 16
} {53514C69746520666F726D6174203300}
change all the "sqlite3 db test.db" type lines to
sqlite3 db test.db -key {}
This allows the test to run correctly.
If there is a better method for running these tests with an encryption
codec, please let me know
Noah Hart
--
View this message in context:
http://old.nabble.com/Error-in-filefmt.test-with-encryption-codec-tp28952009p28952009.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] Error in jrnlmode2.test with encryption codec
jrnlmode2-2.4 & 2-2.6, return incorrect results when using a codec with an
encryption routine
Expected: [0 {1 2 3 4 5 6}]
Got: [1 {file is encrypted or is not a database}]
This happens because the sqlite db command in TCL opens the database with
the option -key {xyzzy}
# Use the pager codec if it is available
#
if {[sqlite3 -has-codec] && [info command sqlite_orig]==""} {
rename sqlite3 sqlite_orig
proc sqlite3 {args} {
if {[llength $args]==2 && [string index [lindex $args 0] 0]!="-"} {
lappend args -key {xyzzy}
}
uplevel 1 sqlite_orig $args
}
}
However, when additional db commands are defined, there is no key added when
a codec exists
Suggested correction for 2-2.4 & 2-2.6
do_test jrnlmode2-2.4 {
if {[sqlite3 -has-codec]} {
sqlite3 db2 test.db -readonly 1 -key {xyzzy}
} else {
sqlite3 db2 test.db -readonly 1
}
catchsql { SELECT * FROM t1 } db2
} {0 {1 2 3 4 5 6}}
do_test jrnlmode2-2.6 {
if {[sqlite3 -has-codec]} {
sqlite3 db2 test.db -readonly 1 -key {xyzzy}
} else {
sqlite3 db2 test.db -readonly 1
}
catchsql { SELECT * FROM t1 } db2
} {0 {1 2 3 4 5 6}}
Thanks,
Noah
--
View this message in context:
http://old.nabble.com/Error-in-jrnlmode2.test-with-encryption-codec-tp28951848p28951848.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] Error in minmax3.test with SQLITE_HAS_CODEC
minmax3.test has a routine at line 23
# This procedure sets the value of the file-format in file 'test.db'
# to $newval. Also, the schema cookie is incremented.
#
proc set_file_format {newval} {
hexio_write test.db 44 [hexio_render_int32 $newval]
set schemacookie [hexio_get_int [hexio_read test.db 40 4]]
incr schemacookie
hexio_write test.db 40 [hexio_render_int32 $schemacookie]
return {}
}
Clearly if the database is encrypted, then plugging data into bytes 40-44
will corrupt page 1.
Question:
Should this test be commented out with a test like :
if {![sqlite3 -has-codec]} ...
or, should a codec implementation leave the schema bytes on page 1 alone?
Thanks,
Noah
--
View this message in context:
http://old.nabble.com/Error-in-minmax3.test-with-SQLITE_HAS_CODEC-tp28951238p28951238.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] When to close a db?
On Mon, Jun 21, 2010 at 12:24 PM, Roger Binns wrote: > > On 06/21/2010 08:44 AM, Sam Carleton wrote: > > I know I cannot afford ... > > For some reason you are assuming that your time is free and that you have > no > other things to do with it (opportunity cost). That is how you should be > weighing all of this. > Trust me, I know the value of my time. If this software I have developed ( http://www.miltonstreet.com) was how I paid the mortgage, then, yea, I would buy the expensive tools which will save me loads of time! But it isn't, it is the software I develop in the evenings and weekends (aka my none core work time) that pays the electric bill. If it generated the funds to purchase real tools, I would purchase them in a heart beat, but it doesn't. Today, this program has not cost me anything other than time and that is how I plan to keep it! > And still refusing to go anywhere Linux where there is an excellent tool > for > exactly this problem that doesn't cost money, but will cost come time makes > no sense at all. > I am not "refusing" to go anywhere near Linux, the system is just too complex to make the port right now. As soon as the front end of the server is in a cross platform, I am am going to port the backend, too. The frontend is currently .Net, in the next major phase, it is getting ported to Qt to run on both the PC and the Mac. I am hoping/assuming that once I get it running on both the PC and the Mac, the port to Linux will be really quick and easy. So, all in time! P.S. anyone of my customers that runs across this post in the future, don't bother asking for the Linux version, if it does come into existence at some point, it will be for internal use ONLY. I simply don't have the bandwidth to support three different OS's, supporting both Windows and OSX is going to be tough enough! Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Oracle joins the SQLite Consortium
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/21/2010 04:47 AM, Igor Sereda quoted Oracle: >> The application-level behavior of the two products is identical, I did test the Oracle version a few months ago and found that statement to be false. Oracle know about the issues but gave the impression that they would not be fixing a SQLITE_BUSY related one (the entire process hangs instead of busy error being returned) claiming performance benefits! Random examples of other things that broke and might eventually be fixed: - - :memory: databases have an arbitrary (small) limit on size - - VFS calls/usage are ignored without error - - Backup not implemented but returns ok - - Different error codes and behaviour when database is corrupt or not a database - - Files can be re-opened by name during the lifetime of a connection, which allows for various attacks/permissions issues. (By contrast SQLite opens the file once during sqlite3_open so deleting/renaming/permissions does not cause later errors.) - - There is no easy equivalent to the amalgamation (there is an amalgamation but it doesn't compile) On the SQL side I didn't detect any difference. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwflTYACgkQmOOfHg372QTY0gCeNIw3nhVM+Bq1lJ2VLE+tJE5A MS4AnitLodGhk5O44KIx3PFLsmmbMUhs =gM74 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to close a db?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/21/2010 08:44 AM, Sam Carleton wrote: > I know I cannot afford ... For some reason you are assuming that your time is free and that you have no other things to do with it (opportunity cost). That is how you should be weighing all of this. And still refusing to go anywhere Linux where there is an excellent tool for exactly this problem that doesn't cost money, but will cost come time makes no sense at all. I run all my releases through valgrind while running a test suite with 99.6% code coverage because it is a far better way of catching problems than having crashes and trying to do a post-mortem debug and figure out things with time running backwards. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwfkkMACgkQmOOfHg372QTLSACdGLJjAXQnzTTVOD5q0wIzJBiA TS4AnAqorIUfOL0LQHg5EsLBowIS5yOI =XEmT -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Oracle joins the SQLite Consortium
On Mon, Jun 21, 2010 at 04:47:12AM -0700, Igor Sereda wrote: > > Wow, that's interesting news. Berkeley DB is still GPL/commercial, I guess? I > hope SQLite will keep on going under public domain, including its B-tree > level. > > Also, here's an interesting statement in the BDB/SQLite announcement: > > > > Thus, applications written to the SQLite version 3 API can switch to using > > Oracle Berkeley DB with no code changes, by re-linking against the > > Berkeley DB SQLite library. The application-level behavior of the two > > products is identical, but the advanced features of Berkeley DB provide > > SQLite applications improved performance, concurrency, scalability, and > > reliability. > > > > Could you please comment on that? > Does that mean SQLite storage level is less reliable than BDB? My own opinion, not that of DRH If you look at the features of BDB, it includes functionality such as replication, enabling high availability across multiple hosts. From an integrity point of view, SQLite is generally as reliable as the hardware underneath it. From an outside point of view, SQLite is only as reliable as the box it is running on, and should the box be unavailable, so is your SQLite data. > Are there any performance measurements and comparison of SQLite vs. > SQLite/BDB? > Does SQLite/BDB really provide more concurrency, while maintaining > SERIALIZABLE isolation level? BDB provides MVCC, though I've no idea if this spans process boundaries. MVCC is well known for allowing increased concurrency but is difficult to implement without extra IPC primitives that SQLite doesn't have access to. That said, the WAL work currently in progress appears to offer simple MVCC conceptually similar to how Oracle implements MVCC (keeping old copies of pages about), so this should increase concurrency somewhat in the cases where WAL can be used. BDB can probably enable multiple writers if it uses versioned rows or page/row level MVCC, as opposed to SQLite's table level locking. I'd be surprised if Oracle post any SQLite vs. SQLite/DBD benchmarks, and I'd also be surprised if BDB licensees are allowed to publish such data. Regards, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to close a db?
On Mon, Jun 21, 2010 at 11:29 AM, Pavel Ivanov wrote: >> hopefully there are better tools there for debugging this >> type of thing. > > There are, but usually they are not free. Quick googling for "valgrind > for windows" brought up commercial products Purify and Insure++ and > free product DUMA. I used Purify myself occasionally - it's not bad, > but you have to buy it. You can try other products and see what it > shows you. I know I cannot afford Purify, just looked at Insure++'s web site for a price, didn't see one. You know the saying: If you have to ask how much, you cannot aford it:) Guess I have to find this one the hard way! Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to close a db?
> hopefully there are better tools there for debugging this > type of thing. There are, but usually they are not free. Quick googling for "valgrind for windows" brought up commercial products Purify and Insure++ and free product DUMA. I used Purify myself occasionally - it's not bad, but you have to buy it. You can try other products and see what it shows you. Pavel On Mon, Jun 21, 2010 at 11:16 AM, Sam Carleton wrote: > On Mon, Jun 21, 2010 at 9:37 AM, Roger Binns wrote: > >> As an example you would experience something like this if SQLite got >> initialized during the config phase. Just to make things even more >> complicated ASLR is turned off if you have Wine installed under Linux so I >> was only seeing the problem on some machines. > > Roger, > > Your post bring me back to what I asked in my last post: > > On Sun, Jun 20, 2010 at 11:31 PM, Sam Carleton > wrote: >> >> Through one request from the client, I open and close the database >> multiple times. Since everything is very module, I am taking that >> approach with the DB, too: For authentication it is open/closed, for >> the initialization of the request the db is open/closed, for the >> processing of the request, the db is opened and closed. All of these >> are using the one and only C++ class to open and close the db. This >> approach allows me to keep all the open's and closes VERY close >> together, there might be lots of code that gets called because of a >> function call in between, but the function that does the open, does >> the close. This is why I took this approach. Since there are >> different threads access the DB at the same time, could this approach >> be causing problems I am unaware of? Would I be better off switching >> over to an approach of: each request opens the DB once and uses the >> same connection through the whole request? > > My point is: I don't do ANYTHING db initialization in start up phase > of loading my module. In the documentation for sqlite3_initialize() > it says, "The sqlite3_initialize() routine is called internally by > many other SQLite interfaces so that an application usually does not > need to invoke sqlite3_initialize() directly." So the Apache module > never calls sqlite3_initialize() directly, it allows the first > sqlite3_open_v2() to call it. > > I also don't believe any of the reading of module parameters > (configuration stuff that happens both times Apache loads the module) > every uses the database, they are all reading in info from flat files > which are used when a request comes in, to connect to the database. > > Oh, and this error does *NOT* happen all the time... The first thing > the web browser does once it connects is start a slide show, so within > 5 seconds there is a new request coming in from each browser all the > time. I have to open browser, after browser, sometime it happens on > one of the requests from the first browser (normally not) or it > happens after opening a bunch of them. The the specific close in > which the crash happens is one of two places in the beginning of the > request, either in the authentication section or in the process of > initialization of the request object, which happens after > authentication. > > I would love to port this code to Linux, but... It is not stand > alone, it depends on a .Net WinForms app to do all the initialization > and configuration of the Apache web server, there are also a total of > 6 different project (one is the Apache module, the rest are Axis2/C > Web Services and library classes), there is some Window specific code > in the Axis2/C projects because Axis2/C doesn't have good file > support. The long and the short of it is, I think it would take me > about a month to port to Linux, just to find one bug? Not a good > investment of my time. I am hoping to port the whole thing to OSX > some time next year (which is why I am using SQLite and not SQL Server > Express), hopefully there are better tools there for debugging this > type of thing. > > Sam > ___ > 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] When to close a db?
On Mon, Jun 21, 2010 at 9:37 AM, Roger Binns wrote: > As an example you would experience something like this if SQLite got > initialized during the config phase. Just to make things even more > complicated ASLR is turned off if you have Wine installed under Linux so I > was only seeing the problem on some machines. Roger, Your post bring me back to what I asked in my last post: On Sun, Jun 20, 2010 at 11:31 PM, Sam Carleton wrote: > > Through one request from the client, I open and close the database > multiple times. Since everything is very module, I am taking that > approach with the DB, too: For authentication it is open/closed, for > the initialization of the request the db is open/closed, for the > processing of the request, the db is opened and closed. All of these > are using the one and only C++ class to open and close the db. This > approach allows me to keep all the open's and closes VERY close > together, there might be lots of code that gets called because of a > function call in between, but the function that does the open, does > the close. This is why I took this approach. Since there are > different threads access the DB at the same time, could this approach > be causing problems I am unaware of? Would I be better off switching > over to an approach of: each request opens the DB once and uses the > same connection through the whole request? My point is: I don't do ANYTHING db initialization in start up phase of loading my module. In the documentation for sqlite3_initialize() it says, "The sqlite3_initialize() routine is called internally by many other SQLite interfaces so that an application usually does not need to invoke sqlite3_initialize() directly." So the Apache module never calls sqlite3_initialize() directly, it allows the first sqlite3_open_v2() to call it. I also don't believe any of the reading of module parameters (configuration stuff that happens both times Apache loads the module) every uses the database, they are all reading in info from flat files which are used when a request comes in, to connect to the database. Oh, and this error does *NOT* happen all the time... The first thing the web browser does once it connects is start a slide show, so within 5 seconds there is a new request coming in from each browser all the time. I have to open browser, after browser, sometime it happens on one of the requests from the first browser (normally not) or it happens after opening a bunch of them. The the specific close in which the crash happens is one of two places in the beginning of the request, either in the authentication section or in the process of initialization of the request object, which happens after authentication. I would love to port this code to Linux, but... It is not stand alone, it depends on a .Net WinForms app to do all the initialization and configuration of the Apache web server, there are also a total of 6 different project (one is the Apache module, the rest are Axis2/C Web Services and library classes), there is some Window specific code in the Axis2/C projects because Axis2/C doesn't have good file support. The long and the short of it is, I think it would take me about a month to port to Linux, just to find one bug? Not a good investment of my time. I am hoping to port the whole thing to OSX some time next year (which is why I am using SQLite and not SQL Server Express), hopefully there are better tools there for debugging this type of thing. Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question About SQLITE and AIR efficiency
Does you query involve LIKE? That has been overriden in AIR so it won't make use of an index. If your queries permit, use GLOB instead. I have used SQLite with Adobe AIR without any performance problems/slowness other than the one above relating to LIKE. N.B. If your database was designed outside of AIR, be aware that Adobe and SQLite differ with respect to primary keys. In SQLite, a PK defined as INT (as distinct from INTEGER) is a normal column whereas one defined as INTEGER (verbatim, i.e. upper-case I, uppercase N, uppercase T, uppercase E, etc) is treated as an alias for the ROWID; whereas in AIR, INT and INTEGER are treated the *same* -- as aliases for the ROWID. This can wreak havoc if you have any tables with INT primary keys as the wrong rows may be incorporated into the result-set by joins. I discovered this and brought this to Adobe's attention months ago and their decision was to document it rather than to change their implementation. Regards Tim Romano Swarthmore PA On Sun, Jun 20, 2010 at 7:44 PM, Richard Hipp wrote: > On Sun, Jun 20, 2010 at 6:11 PM, Felipe Aramburu > wrote: > > > I have a query that I can execute in about 150ms in a sqlite tool like > > sqlite expert professional that takes 1200ms when I execute the query > from > > AIR. > > > > > This might be because AIR is using an older version of SQLite that lacks > some optimization that makes your query run faster. The following > information would be useful in diagnosing the problem: > > (1) "SELECT sqlite_version()" run on both AIR and "SQLite Expert > Professional" > (2) Your schema > (3) The query you are running. > > > > > > I am using prepared statements, synchronous connection. Does anyone have > > any > > idea why a query takes 8 times longer in air? > > > > Felipe > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to close a db?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/20/2010 08:31 PM, Sam Carleton wrote: > So, that seems to leave a memory error in my code, which is also very > tough to find. All the code that is blowing up is within an Apache > Module and I am following the Apache rules very closely. To top it > off, the code crashes in two different places, two different > open/closes of the database. I'd strongly recommend you setup Linux and use valgrind to track this down. It is the right tool for the job and your code should be portable. You can use Ubuntu with something called Wubi (insert the Ubuntu CD while running Windows) and it won't require re-partitioning (a virtual disk for Ubuntu is created as a file under Windows). I have been caught out when writing Apache modules in the past. What happens is that modules are loaded during the configuration phase and then unloaded afterwards and then reloaded during the runtime phase. The reloading was at a different address because of ASLR: http://en.wikipedia.org/wiki/Address_space_layout_randomization I was doing too much initialization in the configuration phase keeping pointers around till the runtime phase which now pointed to unloaded memory. It was very confusing what was going on. As an example you would experience something like this if SQLite got initialised during the config phase. Just to make things even more complicated ASLR is turned off if you have Wine installed under Linux so I was only seeing the problem on some machines. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwfaysACgkQmOOfHg372QSDqQCeIu5qKL6LLBTCUhg9QdF5Yq23 AUkAmwfYFYdaROqJNkWILmDCn1eB/GpK =7vkO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Yet another SQLite wrapper for C++
hi,
i've written a small (<100 lines) c++ wrapper for sqlite.
it supports:
*) prepared statements
*) NULL
*) BLOBs
here is a small usage example:
---SDB sdb("test.sdb");
sdb.exec("drop table test");
sdb.exec("create table test (x, y, z)");
sdb.exec("create index testXIdx on test (x)");
sdb.exec("create index testYIdx on test (y)");
sdb.exec("create index testZIdx on test (z)");
SDB::Statement insert(sdb, "insert into test values(?, ?, ?)");
insert.exec("hello", 42, "world");
insert.exec("hello", "world", 42);
char *buf = (char *)malloc(1024*1024);
memset(buf, 0, 1024*1024);
insert.exec("hello", SDB::null(), SDB::Blob(buf, 1024*1024));
free(buf);
SDB::Statement select(sdb, "select x, y, z from test where x = ? order by x, y,
z");
if (select.exec("hello") == SQLITE_ROW)
{
do
{
const char *x = select.getText(0);
int y = select.getInt(1);
const char *y2 = select.getText(1);
SDB::Blob z = select.getBlob(2);
const char *z2 = select.getText(2);
printf("x=%s, y=%d(%s), z=0x%08lx/%d(%s)\n", x, y, y2, z.m_data, z.m_len, z2);
}
while (select.step() == SQLITE_ROW);
}
---
and here is the code (no download, just inline, i hope its small enough :-)
---
#include "sqlite3.h"
#include
#include
#include
#include
using namespace std;
class SDB
{
public:
struct Null {};
static Null null() { return Null(); }
struct Blob
{
const void *m_data;
int m_len;
Blob(const void *data=NULL, int len=0): m_data(data), m_len(len) {}
};
SDB(const char *fname)
{
check(sqlite3_open(fname, &sdb));
}
~SDB() { check(sqlite3_close(sdb)); }
class Statement
{
public:
Statement(SDB &db, const char *query=NULL) : sdb(db), sqlst(NULL) {
prepare(query); }
~Statement() { if (sqlst) check(sqlite3_finalize(sqlst)); }
int prepare(const char *query) { if (!query) return SQLITE_ERROR; int ok =
sqlite3_prepare_v2(sdb.sdb, query, strlen(query), &sqlst, NULL); check(ok,
query); return ok; }
int step() { int ok = sqlite3_step(sqlst); check(ok); return ok; }
int exec() { int ok = reset(); if (ok != SQLITE_OK) return ok; return step(); }
template int exec(T1 v1) { int ok = reset(); if (ok != SQLITE_OK)
return ok; bind(1, v1); return step(); }
template int exec(T1 v1, T2 v2) { int ok = reset(); if
(ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2); return step(); }
template int exec(T1 v1, T2 v2, T3 v3) {
int ok = reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2);
bind(3, v3); return step(); }
template int exec(T1 v1, T2
v2, T3 v3, T4 v4) { int ok = reset(); if (ok != SQLITE_OK) return ok; bind(1,
v1); bind(2, v2); bind(3, v3); bind(4, v4); return step(); }
template int
exec(T1 v1, T2 v2, T3 v3, T4 v4, T5 v5) { int ok = reset(); if (ok != SQLITE_OK)
return ok; bind(1, v1); bind(2, v2); bind(3, v3); bind(4, v4); bind(5, v5);
return step(); }
template int exec(T1 v1, T2 v2, T3 v3, T4 v4, T5 v5, T6 v6) { int ok =
reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2); bind(3, v3);
bind(4, v4); bind(5, v5); bind(6, v6); return step(); }
template int exec(T1 v1, T2 v2, T3 v3, T4 v4, T5 v5, T6 v6, T7
v7) { int ok = reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2,
v2); bind(3, v3); bind(4, v4); bind(5, v5); bind(6, v6); bind(7, v7); return
step(); }
template int exec(T1 v1, T2 v2, T3 v3, T4 v4, T5
v5, T6 v6, T7 v7, T8 v8) { int ok = reset(); if (ok != SQLITE_OK) return ok;
bind(1, v1); bind(2, v2); bind(3, v3); bind(4, v4); bind(5, v5); bind(6, v6);
bind(7, v7); bind(8, v8); return step(); }
template int exec(T1 v1, T2 v2, T3
v3, T4 v4, T5 v5, T6 v6, T7 v7, T8 v8, T9 v9) { int ok = reset(); if (ok !=
SQLITE_OK) return ok; bind(1, v1); bind(2, v2); bind(3, v3); bind(4, v4);
bind(5, v5); bind(6, v6); bind(7, v7); bind(8, v8); bind(9, v9); return step();
}
template int exec(T1
v1, T2 v2, T3 v3, T4 v4, T5 v5, T6 v6, T7 v7, T8 v8, T9 v9, T10 v10) { int ok =
reset(); if (ok != SQLITE_OK) return ok; bind(1, v1); bind(2, v2); bind(3, v3);
bind(4, v4); bind(5, v5); bind(6, v6); bind(7, v7); bind(8, v8); bind(9, v9);
bind(10, v10); return step(); }
Null getNull(int i=0) { return null(); }
Blob getBlob(int i=0) { return Blob(sqlite3_column_blob(sqlst, i),
sqlite3_column_bytes(sqlst, i)); }
int getInt(int i=0) { return sqlite3_column_int(sqlst, i); }
sqlite3_int64 getInt64(int i=0) { return sqlite3_column_int64(sqlst, i); }
double getDouble(int i=0) { return sqlite3_column_double(sqlst, i); }
const char *getText(int i=0) { return (const char *)sqlite3_column_text(sqlst,
i); }
private:
SDB& sdb;
sqlite3_stmt *sqlst;
void check(int ok, const char *stmt=NULL) { sdb.check(ok, stmt); }
int reset() { int ok = sqlite3_reset(sqlst); check(ok); return ok; }
void bind(int i, const
Re: [sqlite] Oracle joins the SQLite Consortium
Wow, that's interesting news. Berkeley DB is still GPL/commercial, I guess? I hope SQLite will keep on going under public domain, including its B-tree level. Also, here's an interesting statement in the BDB/SQLite announcement: > Thus, applications written to the SQLite version 3 API can switch to using > Oracle Berkeley DB with no code changes, by re-linking against the > Berkeley DB SQLite library. The application-level behavior of the two > products is identical, but the advanced features of Berkeley DB provide > SQLite applications improved performance, concurrency, scalability, and > reliability. > Could you please comment on that? Does that mean SQLite storage level is less reliable than BDB? Are there any performance measurements and comparison of SQLite vs. SQLite/BDB? Does SQLite/BDB really provide more concurrency, while maintaining SERIALIZABLE isolation level? Thanks! Igor D. Richard Hipp wrote: > > The SQLite developers are pleased to announce that Oracle has joined > the SQLite Consortium. > > The SQLite Consortium is a collaboration of major users of SQLite > designed to ensure the continuing vitality and independence of > SQLite. In exchange for sponsorship, SQLite Consortium Members > receive enterprise-level technical support, access to proprietary > SQLite add-ons such as the SQLite Encryption Extension and TH3, and > guarantees that SQLite will continue to be actively maintained and > developed and that it will not fall under the control of a competitor. > > Oracle uses the parser, code generator, and virtual machine from > SQLite in its Berkeley DB product. Additional information about > Berkeley DB's SQL API is available at > > http://www.oracle.com/technology/products/berkeley-db/sql.html > > > D. Richard Hipp > d...@hwaci.com > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://old.nabble.com/Oracle-joins-the-SQLite-Consortium-tp28947200p28947624.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] Oracle joins the SQLite Consortium
Question also. Can one trust that when you look at what those folks are doing to SMI, or whatever they are called now. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, June 21, 2010 5:31 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Oracle joins the SQLite Consortium On 21 Jun 2010, at 12:10pm, Francisco Azevedo wrote: > On 21-06-2010 11:58, D. Richard Hipp wrote: >> guarantees that SQLite will continue to be actively maintained and >> developed and that it will not fall under the control of a competitor. > > Sorry, but.. isn't oracle the "competitor" ? :) The guy who runs Hertz Rent-A-Car rents Avis. 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] Oracle joins the SQLite Consortium
On 21 Jun 2010, at 12:10pm, Francisco Azevedo wrote: > On 21-06-2010 11:58, D. Richard Hipp wrote: >> guarantees that SQLite will continue to be actively maintained and >> developed and that it will not fall under the control of a competitor. > > Sorry, but.. isn't oracle the "competitor" ? :) The guy who runs Hertz Rent-A-Car rents Avis. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Oracle joins the SQLite Consortium
On 21-06-2010 11:58, D. Richard Hipp wrote: > guarantees that SQLite will continue to be actively maintained and > developed and that it will not fall under the control of a competitor. Sorry, but.. isn't oracle the "competitor" ? :) Francisco A ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: A DLL for my WinXP
You also can search Google "sqlite dll download" and follow the first hit ... Am 21.06.2010 12:56, schrieb Arbol One: > Helloo! is any body there??!! > nock nock ... Is this group dead??!! > > Where can I find the DLL file for my winxp? > TIA > > > > Original Message > Subject: A DLL for my WinXP > Date: Sun, 20 Jun 2010 20:59:20 -0400 > From: Arbol One > To: sqlite-users@sqlite.org > > > > Where can I find the DLL file for my winxp? > TIA > -- * Codeswift GmbH * Kräutlerweg 20a A-5020 Salzburg Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 3212 / 1001404 engelsch...@codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: A DLL for my WinXP
Here: http://www.sqlite.org/sqlitedll-3_6_23_1.zip Am 21.06.2010 12:56, schrieb Arbol One: > Helloo! is any body there??!! > nock nock ... Is this group dead??!! > > Where can I find the DLL file for my winxp? > TIA > > > > Original Message > Subject: A DLL for my WinXP > Date: Sun, 20 Jun 2010 20:59:20 -0400 > From: Arbol One > To: sqlite-users@sqlite.org > > > > Where can I find the DLL file for my winxp? > TIA > -- * Codeswift GmbH * Kräutlerweg 20a A-5020 Salzburg Tel: +49 (0) 8662 / 494330 Mob: +49 (0) 171 / 4487687 Fax: +49 (0) 3212 / 1001404 engelsch...@codeswift.com www.codeswift.com / www.swiftcash.at Codeswift Professional IT Services GmbH Firmenbuch-Nr. FN 202820s UID-Nr. ATU 50576309 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Oracle joins the SQLite Consortium
The SQLite developers are pleased to announce that Oracle has joined the SQLite Consortium. The SQLite Consortium is a collaboration of major users of SQLite designed to ensure the continuing vitality and independence of SQLite. In exchange for sponsorship, SQLite Consortium Members receive enterprise-level technical support, access to proprietary SQLite add-ons such as the SQLite Encryption Extension and TH3, and guarantees that SQLite will continue to be actively maintained and developed and that it will not fall under the control of a competitor. Oracle uses the parser, code generator, and virtual machine from SQLite in its Berkeley DB product. Additional information about Berkeley DB's SQL API is available at http://www.oracle.com/technology/products/berkeley-db/sql.html D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: A DLL for my WinXP
Helloo! is any body there??!! nock nock ... Is this group dead??!! Where can I find the DLL file for my winxp? TIA Original Message Subject:A DLL for my WinXP Date: Sun, 20 Jun 2010 20:59:20 -0400 From: Arbol One To: sqlite-users@sqlite.org Where can I find the DLL file for my winxp? TIA -- This email is for the sole use of the intended recipient and may contain confidential or privileged information. Unauthorized use of its contents is prohibited. If you have received this email in error, please notify the sender immediately via return email and then delete the original email. Think Green before printing this email ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When to close a db?
Sam, Could it be that you have prepared statements still active when you're trying to close the db? (not finalized, that is) -- Tito Sent from my iPhone On 20 Jun 2010, at 23:17, Sam Carleton wrote: > I am getting some strange behavior out of my app, which happens to be > both an Apache module and some Axis2/C Web Services which run under > Apache. From time to time, it is VERY inconsistent, when the code > calls sqlite3_close() the Apache server crashes. I don't recall the > error right off. > > From reading the documentation, it looks to me like sqlite3_close() > should be called if *ppDb has a value, irregardless of the result code > from the sqlite3_open_v2() call. Is there more to it? > > Sam > ___ > 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
