[sqlite] GUID/UUID in sqlite.
Hi, I am designing a database where-in my column data is UUID. I am trying to figure out which is the best way to handle UUID since if stored as text the length would be 32 characters (though UUIDs are 128 bit size) and select query based on UUIDs might not be really fast (I might be wrong here.) Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] GUID/UUID in sqlite.
Thanks, I will try both (ASCII and BLOB) approaches and see speed vs. storage trade-offs. Any more ideas are welcome. Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 29, 2007 11:26 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] GUID/UUID in sqlite. You could experiment with making your 128 bit entity a BLOB. The alternative would be to represent it it in ASCII. Changing its radix would probably be the significant overhead, not the Sqlite storage. Prakash Reddy Bande wrote: > Hi, > > I am designing a database where-in my column data is UUID. > I am trying to figure out which is the best way to handle UUID since if > stored as text the length would be 32 characters (though UUIDs are 128 > bit size) and select query based on UUIDs might not be really fast (I > might be wrong here.) > > Regards, > > Prakash Reddy Bande > Altair Engg. Inc, > Troy, MI > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] GUID/UUID in sqlite.
Thanks, This actually solves another problem also where the uid generation algorithm fails to generate a true uuid. Since table u has uuid column unique, my database will never have a uuid repeated. But the number of uuids I can have will now be dependent on what integer can accommodate (I am sure it's a large number and I hope my database will not consume all of it). Moreover, when the database no longer uses a uuid, it can be removed from this table too, which might mean a considerable work for application developer :-). Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI -Original Message- From: Scott Hess [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 29, 2007 1:47 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] GUID/UUID in sqlite. In either case, if you use the UUID as a primary key in more than one table, you should consider having one table to convert the UUID to a 64-bit id, and use that as the primary keys on the other tables. If you have UUID as a primary key, your table will have 2 b-trees, one for the index of UUID to rowid, the other to map the rowid to row data. Once you have 2 such tables, it can be more efficient to break out the mapping of the UUID to an internal id you use elsewhere. Example, instead of: CREATE TABLE t ( uuid TEXT PRIMARY KEY, ... ); INSERT INTO t (uuid, ...) VALUES (?, ...); Do: CREATE TABLE u ( uuid TEXT UNIQUE, internalid INTEGER PRIMARY KEY ); CREATE TABLE t ( fk_internalid INTEGER PRIMARY KEY, ... ); BEGIN; INSERT INTO u (uuid, internalid) VALUES (?, NULL); INSERT INTO t (fk_internalid, ...) VALUES (LAST_INSERT_ROWID(), ...); COMMIT; -scott On 8/29/07, John Stanton <[EMAIL PROTECTED]> wrote: > You could experiment with making your 128 bit entity a BLOB. The > alternative would be to represent it it in ASCII. Changing its radix > would probably be the significant overhead, not the Sqlite storage. > > Prakash Reddy Bande wrote: > > Hi, > > > > I am designing a database where-in my column data is UUID. > > I am trying to figure out which is the best way to handle UUID since if > > stored as text the length would be 32 characters (though UUIDs are 128 > > bit size) and select query based on UUIDs might not be really fast (I > > might be wrong here.) > > > > Regards, > > > > Prakash Reddy Bande > > Altair Engg. Inc, > > Troy, MI > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem inserting blob
Hi, I am trying to insert blob in a sqlite database using sqlite.exe . create table t (x blob); insert into t values (x'ccaaffee'); insert into t values (x'ccaa-ffee'); // This line give the error SQL error: unrecognized token: "x'ccaa" Where am I going wrong? Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI
RE: [sqlite] Problem inserting blob
OK, I figured it out X should be followed by only hex characters 0-9, a-f. Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI -Original Message- From: Prakash Reddy Bande [mailto:[EMAIL PROTECTED] Sent: Friday, August 31, 2007 4:00 PM To: sqlite-users@sqlite.org Subject: [sqlite] Problem inserting blob Hi, I am trying to insert blob in a sqlite database using sqlite.exe . create table t (x blob); insert into t values (x'ccaaffee'); insert into t values (x'ccaa-ffee'); // This line give the error SQL error: unrecognized token: "x'ccaa" Where am I going wrong? Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Group by without aggregates
Hi, I have the following table: CREATE TABLE users (SMD_cid varchar(50), uname varchar(50), version integer); select SMD_cid, uname, version from users; 2a946e78-0659-4746-aab7-6486ae9a26d3|auser|1 b0b520e9-54b3-224d-8183-09ef08501451|auser|1 9272f455-2c2a-7e4d-a140-c30fac34ab09|auser|0 66c6263d-e792-f94e-8f57-fd49c2e7b6a6|auser|0 select SMD_cid, uname, version from users group by version; SMD_cid|uname|version 66c6263d-e792-f94e-8f57-fd49c2e7b6a6|auser|0 b0b520e9-54b3-224d-8183-09ef08501451|auser|1 I was excepting an error saying columns SMD_cid and uname are not aggregate. But the query did give some results. How does SQLite choose which row to return from a group? Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI
[sqlite] Sqlite 3.5.1 stability
Hi, Is Sqlite 3.5.1 a stable release? Should I move from 3.4.0 to 3.5.1. The reason I am asking this is that we get a linker error _TryEnterCriticalSection and to fix it we must get http://www.sqlite.org/cvstrac/chngview?cn=4399 If I get the files in this check-in can I rely on the fact that 3.5.1 will be as stable as 3.4.0. The reason I am moving over is that the sources for fts are based 3.5.1. How can I get the sources of fts based on 3.4.0 if I have to use 3.4.0? Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI
[sqlite] Multiple process using the same database
Hello, I have more than one process using the same database. When process 1 modifies the database, I want to get some kind of notification to the other processes. Ideally I would like to know the table and the rowid that are affected by the change in the database. I need this to synchronize any in memory cached data in my processes. How do I go about achieving this? Regards, Prakash Reddy Bande Altair Engg. Inc, Troy, MI
[sqlite] Multiple connections and triggers
Hi, I am in a situation where-in there are multiple connections to a single sqlite database. When one connection updates the database other connections would like to get the information about the change (asynchronously, for example when user wants he will use some refresh like command of the application). I tried it by doing the following. 1. Create a table create table updatelog (cid text) 2. The I create the trigger: create trigger updateaddresstrigger update on address begin insert into updatelog values (NEW.cid); end; The table address has a column cid. On refresh I query the updatelog to get the cid of all changed addresses. The problem here is when do I delete the data from updatelog, since if I don't do so then at every refresh, I will be updating the cid address which is already updated. What I want to make sure that a cid entry is removed only for the connection which has refreshed but is still available for connections that have not yet refreshed. I basically want to create trigger, and then associate the action from the application which connects to the database. Something like in my application I would do: sqlite3_open(); And run a command like this one create temp table updatalog (cid text) attach trigger updateaddresstrigger update on address (not a valid command but I just want to express what kind of solution I am looking for) begin insert into updatelog values (NEW.cid); end; The on refresh I would like to ` delete from updatelog Doing this, I can have every connection have its own updatelog table Prakash Reddy Bande Altair Engg. Inc, Troy, MI
Re: [sqlite] (no subject)
Me too, all attempts to unsubscribe have failed. I have put a filter, but still my .pst is becoming big. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Pece Cvetkoski [pcvetko...@yahoo.com] Sent: Saturday, May 22, 2010 10:49 AM To: sqlite-users@sqlite.org Subject: [sqlite] (no subject) please unuscribed pcvetko...@yahoo.com, you insoult my mail, and i want to unuscribe for your contacts, thanx ___ 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] Locking issue on NFS filesystem
Hi, I have stumbled upon the issue as described in http://sqlite.org/faq.html#q5 (But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem.) The question is, do we have a workaround. Our application has to store data in user home directory (RHEL/SLES/CentOS) and the home directory might be on a NFS device (as is my home directory). stat -f -c %X /users/prakash returns nfs. I am vaguely imagining if we could (at compile or run time) have SQLite use dot file locking. Let me describe the problem I am facing. The database file does not exist and the call to sqlite3_open, does not return (or atleast does not return as long as I waited ~10 mins). I killed the process using kill / kill -9, but process went into zombie state. Also I observed that the database file got created (may not be usable) and another file nfs00. was created and locked by my app. I compiled the latest sqlite 3.6.23.1 and ran the following command sqlite /users/prakash/test.db I got the sqlite command prompt, however, the command .tables resulted in hang. So, again, I am trying to figure out a solution/workaround. Concurrency is not the most major concern and hence if we can use a different locking mechanism, that will be nice. Regards, Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Locking issue on NFS filesystem
Hi, Our application is a simple desktop application with a simple install and run setup. It is not possible to tell users to apply workaround of sharing the drive via SMB etc. I was hoping we have a way to enable dot file locking. For Mac OS X, sqlite allows SQLITE_ENABLE_LOCKING_STYLE compile option, why not leverage that for NFS issues too? May be I am completely wrong, but I feel dot file locking may work as long as file read/write/execute permissions are available. Switching SQLITE_ENABLE_LOCKING_STYLE on RHEL resulted in compile errors. There is no configure option but by setting CFLAGS, CCFLAGS I tried compile sqlite with this option. Regards, From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin [slav...@bigfraud.org] Sent: Sunday, June 13, 2010 10:38 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Locking issue on NFS filesystem On 14 Jun 2010, at 3:16am, Prakash Reddy Bande wrote: > I have stumbled upon the issue as described in http://sqlite.org/faq.html#q5 > (But use caution: this locking mechanism might not work correctly if the > database file is kept on an NFS filesystem.) > > The question is, do we have a workaround. Our application has to store data > in user home directory (RHEL/SLES/CentOS) and the home directory might be on > a NFS device (as is my home directory). > stat -f -c %X /users/prakash returns nfs. Which version of NFS ? Locking was introduced in version 4. However, locking even under NFS sucks. That warning in the SQLite FAQ isn't there because the writers of SQLite are bad programmers, it's there because locking under many NFS installations is not implemented properly. You may have an alternative of accessing your NFS drive as a shared drive. If you do this, then locking is implemented by the networking protocol, not by the driver of the space being shared. So if you have a way of sharing your NFS drive via SMB or AFS, or some other common space-sharing system, you might be able to get around the NFS problems completely. 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] Locking issue on NFS filesystem
Hi, Do you mean I shall use int sqlite3_open_v2( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb, /* OUT: SQLite db handle */ int flags, /* Flags */ const char *zVfs/* Name of VFS module to use */ ); With the last argument "unix-dotfile". BTW I am using it just as one uses database and our app also has connectors to client/server RDBMS. We use sqlite in a mode where app is used for in a mode where only one process uses it and moreover the process is single threaded too. Regards, Prakash From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns [rog...@rogerbinns.com] Sent: Monday, June 14, 2010 8:30 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Locking issue on NFS filesystem -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/14/2010 02:56 AM, Prakash Reddy Bande wrote: > Our application is a simple desktop application with a simple install and run > setup. It is not possible to tell users to apply workaround of sharing the > drive via SMB etc. BTW SMB won't be much better than NFS. (A lot of things have to line up right.) You haven't specified if you are using SQLite because it is a convenient file format or because the transactions and durability matter. If it is the latter then you cannot use a networked filesystem. > I was hoping we have a way to enable dot file locking. It is available by default. Use "unix-dotfile" as the VFS name in your open call. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwWIP8ACgkQmOOfHg372QT6YQCZAVqZLFg94FlljT7PkZz0jkxP RH8AoI2daz7YpQ3K7aYNVkG4Qpojqhdf =jh3O -END PGP SIGNATURE- ___ 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] File Locking And Concurrency In SQLite Version 3
Hello, I want to use sqlite as a db on a shared network drive (windows) (user would map the network drive to say T:). I read the documentation section "File Locking And Concurrency In SQLite Version 3" (http://www.sqlite.org/lockingv3.html) Where following is clearly mentioned: "SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem." My question: Is it possible to verify if the network drive on which the DB is place can be used? What I would like to do is when user tries to load the db (sqlite3_open) first run the verification and inform the user that database is placed on a drive that does not support the locking mechanism required by our product. I am using sqlite-3.7.2 Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] File Locking And Concurrency In SQLite Version 3
Hi, Thanks for the response. That is what I was planning to do, i.e. write some code that does the verification before I call sqlite3_open. However, I am not sure what code should I write. I mean the code I write to do the verification should be a representation of what sqlite3 does. Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Friday, November 12, 2010 1:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] File Locking And Concurrency In SQLite Version 3 On 12 Nov 2010, at 5:58pm, Prakash Reddy Bande wrote: > My question: Is it possible to verify if the network drive on which the DB is > place can be used? Great question. Unfortunately LockFile() generally does /not/ work. If you want to perform a check there are too many combinations of access methods and version numbers to use logic. I can only recommend you write some code that actually tries to do the locking and checks to see whether it worked. I have an application which does something similar in a Unix setup. It requires that the user runs two test applications at the same time -- either on the same computer or on two different computers. 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
[sqlite] Busy handler not called
Hi, I have set a busy handler. int ret = sqlite3_open(dbname.c_str(), &m_ppDb); sqlite3_busy_handler(m_ppDb, &hwLMsqlite3BusyHandler, 0); However it is not getting called. Here is what I am doing: 1. Using the sqlite3.exe run the following commands begin transaction; update users set name="hello" 2. Note I have not yet commited. >From my program I call int ret = sqlite3_exec(m_ppDb, query.c_str(), &hwLMsqlite3TableCallback, &rs, &zErr); I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called. I am using is 3.7.2 Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Busy handler not called
Hi, Basically I am doing the same query i.e. int ret = sqlite3_exec(m_ppDb, "begin transaction", &hwLMsqlite3TableCallback, &rs, &zErr); int ret = sqlite3_exec(m_ppDb, "update users set name=\"something\"", &hwLMsqlite3TableCallback, &rs, &zErr); The second one return SQLITE_BUSY as expected (since begin transaction does no locking as per documentation of sqlite) Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Friday, November 12, 2010 4:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Busy handler not called > int ret = sqlite3_exec(m_ppDb, query.c_str(), &hwLMsqlite3TableCallback, &rs, > &zErr); > > I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called. What query do you use? Pavel On Fri, Nov 12, 2010 at 4:51 PM, Prakash Reddy Bande wrote: > Hi, > > I have set a busy handler. > > int ret = sqlite3_open(dbname.c_str(), &m_ppDb); > sqlite3_busy_handler(m_ppDb, &hwLMsqlite3BusyHandler, 0); > > However it is not getting called. Here is what I am doing: > 1. Using the sqlite3.exe run the following commands > begin transaction; > update users set name="hello" > > 2. Note I have not yet commited. > > From my program I call > > int ret = sqlite3_exec(m_ppDb, query.c_str(), &hwLMsqlite3TableCallback, &rs, > &zErr); > > I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called. > > I am using is 3.7.2 > > Regards, > > Prakash Bande > Altair Engg. Inc. > Troy MI > Ph: 248-614-2400 ext 489 > Cell: 248-404-0292 > > ___ > 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] Busy handler not called
Hi, Got it. I did read this, but probably did not understand very well. Yes, it makes sense why busy handler is not called. May be it is a good idea to site some examples in the documentation... Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Friday, November 12, 2010 7:16 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Busy handler not called On Fri, Nov 12, 2010 at 04:51:11PM -0500, Prakash Reddy Bande scratched on the wall: > Hi, > > I have set a busy handler. > > int ret = sqlite3_open(dbname.c_str(), &m_ppDb); > sqlite3_busy_handler(m_ppDb, &hwLMsqlite3BusyHandler, 0); > > However it is not getting called. As the docs for sqlite3_busy_handler() point out, this is exactly how it is expected to work: <http://www.sqlite.org/c3ref/busy_handler.html> The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed. > 1. Using the sqlite3.exe run the following commands > begin transaction; > update users set name="hello" > > 2. Note I have not yet commited. This will grab the reserved lock, and knows it will want to get an exclusive to finish the transaction. > From my program I call > > int ret = sqlite3_exec(m_ppDb, query.c_str(), &hwLMsqlite3TableCallback, &rs, > &zErr); > > I get SQLITE_BUSY and hwLMsqlite3BusyHandler is not getting called. You said this is another update, so same thing... Any attempt to promote to a reserved lock will sense a possible deadlock and skip the busy handler. At this point, there is no way out of this situation other than having the second query rollback. Exec is likely to do this automatically, aborting the second UPDATE, and returning the original SQLITE_BUSY error. -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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read database from multiple processes
Hi, I have a database placed on a shared drive. Two processes (from different hosts) do a bunch of select commands. I have a busy handler that sleeps of 1 second in each attempt and bails out after 10 attempts. The observation is that, if only one process is running (on any host) the results are returned pretty fast. However, if both processes are doing the selects concurrently the performance is dead slow. Note that each process is doing somewhere around 5000 selects. My understanding is that when only selects are happening there is no locking involved. I am using 3.7.2. Regards, Prakash Bande Altair Engg. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Queries getting slow with concurrency
Hi, I have observed that when multiple processes from "different" hosts are concurrently doing the queries, the performance of sqlite degrades considerably. Queries are read only hence I believe that there should not be any locking issues (no busy handler called). I will dig a little more in my application (not much I can do except check if my busy handler is getting called). Only other problem I can think of is file system does not like being read concurrently, but we use this file system (NTFS network drive) for lot of other operations which happen concurrently. Regards, Prakash Bande ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OPEN_NOMUTEX
Hi, As per documentation opening a database with SQLITE_OPEN_NOMUTEX flag opens it in multi-threaded mode. http://www.sqlite.org/threadsafe.html says In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. I just wanted to be certain that this is true in case of multi-cpu machines and cache-coherence related issues won't surface. Regards, Prakash Bande Director - Hyperworks Enterprise Software Altair Eng. Inc. Troy MI Ph: 248-614-2400 ext 489 Cell: 248-404-0292 My Secure Drop Box: https://ftam1.altair.com/dropbox/~Ursfnk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite
Hi, I wanted to get an understanding of SQLite Encryption Extension. I am currently using sqlite-3.7.2, and if I want to use SEE, would it be equally compatible. Well, the http://www.hwaci.com/sw/sqlite/see.html page does not give much information, i.e. do still build sqlite from public and inject encryption or it is a parallel code base. Well, all I want to actually do is store passwords in a column and hence it should be encrypted. Of course, alternatively I can do encryption for that value in my application. But I guess it is a nice option to keep the entire db encrypted so that users cannot explore and accidently mess with it using sqlite CLI. Regards, Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Header pointers in table callback
Hi,
We were looking at the ways we can optimize our application. Our app does a
simple sqlite3_exec and sends the callback as below. The data is just a
map >
int sqlite3TableCallback(void* data, int ncols, char** values, char** headers)
{
map >& table = *(( map >*) data);
for (int i = 0; i < ncols; i++)
{
if(values[i])
table.Data[headers[i]].push_back(string(values[i]));
else
table.Data[headers[i]].push_back(string());
}
return 0;
}
Here, we wanted to optimize the string construction of string in
table.Data[headers[i]]. We were happy to notice that the headers were pointing
to the same address, hence we are planning to enhance the callback data so that
it can track map, and then in the table.Data[headers[i]] we can
pass reference to the string preventing its construction and destruction.
Agreed, there will be another look-up in the map, for char* to string.
Well, the bottom line, is it safe to assume that char** headers will be
pointing to the same address through out the query (i.e. each time callback is
called for a matching row).
Regards,
Prakash Bande
Altair Engg. Inc.
Troy MI
Ph: 248-614-2400 ext 489
Cell: 248-404-0292
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
