You have chosen to reply to a digest post you received, and as a result, I have no idea what thread you are talking about. Please note at least two things -- one, don't hijack threads because they break conversation, digress, distract, and confuse; this includes not replying to digests; and two, you don't have to cc your email to me -- remember, I am also subscribed to the list. When you cc to me, I get two copies, and that simply hurts the rain forests.
On 5/19/08, Hildemaro Carrasquel <[EMAIL PROTECTED]> wrote: > Thank you Punk and all..... > > My question is because i want to make a application that it can have approx > 10.000 registers, but there are many application are writing in data base > sqlite, this write can be at the same time, Is it possible this? As I said in response to your earlier email (I am sure I remember it now -- it was about SQLite server or how many concurrent connections it can support or something like that) -- SQLite will respond to each request as it comes in. You have design concurrency in your application. > > 2008/5/20 <[EMAIL PROTECTED]>: > > Send sqlite-users mailing list submissions to > > sqlite-users@sqlite.org > > > > To subscribe or unsubscribe via the World Wide Web, visit > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > or, via email, send a message with subject or body 'help' to > > [EMAIL PROTECTED] > > > > You can reach the person managing the list at > > [EMAIL PROTECTED] > > > > When replying, please edit your Subject line so it is more specific > > than "Re: Contents of sqlite-users digest..." > > > > > > Today's Topics: > > > > 1. Re: design question / discussion (Rich Rattanni) > > 2. Re: Calculating Difference between Time using SQLite (P Kishor) > > 3. Sqlite3 (Hildemaro Carrasquel) > > 4. Re: sqlite3_transfer_bindings obsolete? (Ralf Junker) > > 5. Re: Sqlite3 (P Kishor) > > 6. Re: Indexing virtual tables (Aladdin Lamp?) > > 7. sqlite3_mprintf() best practice (Aladdin Lamp?) > > 8. sqlite3Atoi64() and input string "0" (Aladdin Lamp?) > > 9. FW: SQLite : text datatype and referential integrity > > (palmer ristevski) > > > > > > > ---------------------------------------------------------------------- > > > > Message: 1 > > Date: Sun, 18 May 2008 12:44:55 -0400 > > From: "Rich Rattanni" <[EMAIL PROTECTED]> > > Subject: Re: [sqlite] design question / discussion > > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > > Message-ID: > > > <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset=ISO-8859-1 > > > > Thanks for your reply. I have done some quick timing tests on my > > system; a vacuum can take 5 or more minutes (synchronous full), and a > > delete and recreate is rougly 3 seconds. I think I did such a test > > with a 30MB database. The database resides on a jffs2 file system > > (compression off), which seems to have a constant time for deletions. > > > > I should have included I am using sqlite 3.4.0. > > > > > > On Sun, May 18, 2008 at 4:45 AM, <[EMAIL PROTECTED]> wrote: > > >> Hi I have a general design question. I have the following senario... > > > > > > IMHO your design sound reasonable. In relation with the vacuum question > > > I suggest try to delete and re-create each table and watch both timings. > > > > > > HTH > > > > > > Adolfo > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > ------------------------------ > > > > Message: 2 > > Date: Sun, 18 May 2008 17:24:50 -0400 > > From: "P Kishor" <[EMAIL PROTECTED]> > > Subject: Re: [sqlite] Calculating Difference between Time using SQLite > > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > > Message-ID: > > > <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset=ISO-8859-1 > > > > On 5/16/08, P Kishor <[EMAIL PROTECTED]> wrote: > > > On 5/16/08, Scott Baker <[EMAIL PROTECTED]> wrote: > > > > Miguel wrote: > > > > > Estimates, > > > > > First of all, excuse my English, I recognise that it is not my > strong. > > > > > I need to do a query on a table and I return the difference in > minutes > > > > > between > > > > > two times loaded in the table. > > > > > Which would be the best way to make these differences. > > > > > Since already thank you very much and greetings. > > > > > > > > > > > > If you convert both dates to unixtime (seconds) and subtract you'll > get > > > > seconds between the two dates. Then divide by 60. > > > > > > > > SELECT (strftime('%s','now') - strftime('%s','2004-01-01 02:34:56')) > / 60; > > > > > > > > > > > > > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions > > > > > > > > > > > > > > well, no not really. The original question is about subtracting date > > > in one row from the date in another row. This is an Igor-level > > > question, but the following comes to my mind -- > > > > > > Given the following table, > > > > > > qlite> create table t (a, b datetime); > > > sqlite> insert into t values (1, '1993-01-01 00:00:30'); > > > sqlite> insert into t values (2, '1992-02-12 00:12:29'); > > > sqlite> select * from t; > > > 1|1993-01-01 00:00:30 > > > 2|1992-02-12 00:12:29 > > > sqlite> select (julianday(t1.b) - julianday(t2.b)) d from t t1 left > > > join t t2 on t1.a = t2.a and t1.a = 1 or t2.a = 2 and d > 0; > > > > > > Replace julianday with the datetime function of your choice. Still, my > > > solution is not really good because I can't figure out how to get > > > exactly the result I want, but it almost gets me there. > > > > > > > > > > > > fwiw, here is the correct solution for "returning the difference in > > minutes between two times loaded in a table" > > > > sqlite> .s > > CREATE TABLE t (i, a); > > sqlite> select * from t; > > i a > > ---------- ------------------- > > 1 1993-01-01 00:00:00 > > 2 1993-01-10 00:19:00 > > 3 1994-01-10 00:19:00 > > 4 1994-01-12 00:19:00 > > sqlite> select (strftime('%s',t1.a) - strftime('%s',t2.a))/60 d from t > > t1 join t t2 on t1.i != t2.i and t1.i = 1 and t2.i = 2; > > d > > ---------- > > -12979 > > sqlite> > > > > replace 1 and 2 in the query above with the row ids that you want to > query. > > > > > > > > > > > > > > > -- > > > > > > > Scott Baker - Canby Telcom > > > > RHCE - System Administrator - 503.266.8253 > > > > > > > > > > -- > > Puneet Kishor http://punkish.eidesis.org/ > > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > > > > > > ------------------------------ > > > > Message: 3 > > Date: Tue, 20 May 2008 08:49:39 +1930 > > From: "Hildemaro Carrasquel" <[EMAIL PROTECTED]> > > Subject: [sqlite] Sqlite3 > > To: sqlite-users@sqlite.org > > Message-ID: > > > <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset=ISO-8859-1 > > > > Hello.- > > > > I want to know, how many connections does Sqlite3 support? > > > > -- > > Ing. Hildemaro Carrasquel > > Ingeniero de Proyectos > > Cel.: 04164388917/04121832139 > > > > > > ------------------------------ > > > > Message: 4 > > Date: Mon, 19 May 2008 15:38:56 +0200 > > From: Ralf Junker <[EMAIL PROTECTED]> > > Subject: Re: [sqlite] sqlite3_transfer_bindings obsolete? > > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > > Message-ID: <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset="us-ascii" > > > > > > >> Drat. It doesn't look like there's a way to see what's already been > > >> bound to a statement either, correct? > > > > See this thread for a previous disuccsion of the problem: > > > > > http://www.mail-archive.com/sqlite-users@sqlite.org/msg28610.html > > > > Ralf > > > > > > > > ------------------------------ > > > > Message: 5 > > Date: Mon, 19 May 2008 08:41:53 -0500 > > From: "P Kishor" <[EMAIL PROTECTED]> > > Subject: Re: [sqlite] Sqlite3 > > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > > Message-ID: > > > <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset=ISO-8859-1 > > > > On 5/19/08, Hildemaro Carrasquel <[EMAIL PROTECTED]> wrote: > > > Hello.- > > > > > > I want to know, how many connections does Sqlite3 support? > > > > SQLite is not a server in a traditional sense. It is the client *and* > > the server. It is embedded in your application. In that sense, it can > > support as many connections as your application can support. > > Concurrent requests made to the same db are queued up and replied to > > as they happen. > > > > > > > > > > > > > -- > > > Ing. Hildemaro Carrasquel > > > Ingeniero de Proyectos > > > Cel.: 04164388917/04121832139 > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > -- > > Puneet Kishor http://punkish.eidesis.org/ > > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > > > > > > ------------------------------ > > > > Message: 6 > > Date: Mon, 19 May 2008 16:01:24 +0200 > > From: Aladdin Lamp? <[EMAIL PROTECTED]> > > Subject: Re: [sqlite] Indexing virtual tables > > To: <sqlite-users@sqlite.org> > > Message-ID: <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset="iso-8859-1" > > > > > > Nobody? Did I make myself clear or do you need more (or maybe less!) > explanations? > > Thanks, > > Aladdin > > > > > From: [EMAIL PROTECTED] > > > To: sqlite-users@sqlite.org > > > Date: Sat, 17 May 2008 16:41:49 +0200 > > > Subject: [sqlite] Indexing virtual tables > > > > > > > > > Hi! Here is what I'm still trying to achieve: > > > - I have a custom file format having "records" and file offsets. > > > - Each record in that custom file format has the same number of fields, > but the records itself are variable length, that's why I need a file offset > to quickly locate a record. One other way (if you can wait for a very long > time...) is to walk sequentially the records list to get the desired record. > > > - I've implemented a working SQLite "virtual table" in order to be able > to read and query my custom file format through SQLite. > > > - Now, basically what I'd like would be to "CREATE INDEX" on a field of > my virtual table to take advantage of it in the xBestIndex callback. But the > documentation says that we cannot use "CREATE INDEX" on virtual tables. > > > > > > Let's say the data in the field "F1" of my virtual table "VFILE", and > the file offsets are the following: > > > F1 fileoffset > > > ---------------------- > > > a 10 > > > b 21 > > > z 34 > > > x 45 > > > a 51 > > > x 69 > > > z 73 > > > a 88 > > > x 94 > > > > > > I want to index the column F1, to be able to have a quick response to > queries like: > > > select * from VFILE where F1='x' > > > > > > At this point, I think I have only 3 possible strategies: > > > 1. Use SQLite tables to "fake" a standard index using SQLite tables > > > 2. Use internal SQLite B-Tree routines to implement my index > (sqlite3BtreeCreateTable and stuff) > > > 3. Implement my own B-Tree and sort algorithms to achieve this, > externally to SQLite > > > > > > Strategy 3 is precisely what I'm trying to avoid (too much work and > testing :-) ). > > > Strategy 2 is strongly discouraged by DRH. > > > > > > Then strategy 1 seems to be (like you've just said) the only way to go: > > > > > > a) Duplicate the data to be indexed (and the file offsets to use) > > > > > > create table INDEX1_SORT as select F1, fileoffset from VFILE order by > F1, fileoffset > > > > > > INDEX1_SORT: physical table > > > F1 fileoffset > > > ---------------------- > > > a 10 > > > a 51 > > > a 88 > > > b 21 > > > x 45 > > > x 69 > > > x 94 > > > z 34 > > > z 73 > > > > > > b) Create an index on that data > > > > > > OPTION 1: Use SQLite CREATE INDEX at this point. > > > b.1.1) create index on INDEX1_SORT(F1) > > > > > > OPTION 2: Fake index with custom tables > > > b.2.1) create table INDEX2_SUM as select F1, min(INDEX1_SORT.rowid) as > 'minrow', max(INDEX1_SORT.rowid) as 'maxrow' from INDEX1_SORT group by F1 > > > > > > INDEX2_SUM: physical table > > > F1 minrow maxrow > > > --------------------------------------- > > > a 1 3 > > > b 4 4 > > > x 5 7 > > > z 8 9 > > > > > > b.2.2) create index on INDEX_2_SUM(F1) > > > > > > > > > > ********************************************************************* > > > > > > Usage for option 2: > > > - Use INDEX2_SUM to fetch the requested value in the initial query > (select * from VFILE where F1='x') > > > - Get data from table INDEX1_SORT between rowid "minrow" (5) and > "maxrow" (7) > > > - For each line, use the given file offset to locate the real data in > the custom file format file. > > > - Read 3 records at fileoffet = 45,69,94 and return them to SQLite. > > > > > > I really feel like all this is not very optimal. > > > What is the best strategy to achieve optimal speed and needed storage? > > > Am I missing a trivial point? > > > > > > Thank you for any help on that! > > > Aladdin > > > > > >> Date: Mon, 12 May 2008 15:37:22 -0700 > > >> From: [EMAIL PROTECTED] > > >> To: sqlite-users@sqlite.org > > >> Subject: Re: [sqlite] Indexing virtual tables > > >> > > >> I'm not quite clear on your question - why wouldn't you just create > > >> any indices you need within the virtual-table implementation itself? > > >> Sort of like how fts uses SQLite tables to implement data-storage for > > >> the full-text index. > > >> > > >> -scott > > >> > > >> > > >> On Mon, May 5, 2008 at 10:13 AM, Aladdin Lamp? wrote: > > >>> > > >>> Just thinking again about indexing strategies on virtual tables, I'm > wondering why virtual tables could not be indexed using the "normal" SQLite > command "INDEX". Indeed, I just expected that the data inside the column of > the virtual table could be sequentially scanned (using the "xColumn" > callback), producing the same result as if it were a real table. Is that way > of seeing things flawed? > > >>> > > >>> Any hook allowing to use SQLite internal indexing techniques for > virtual tables? Maybe using direct b-tree manipulation (even if I know it's > not recommended)? I'm not very keen on developing my own from stratch. > Dealing with "big" tables that don't fit into memory does not seem so easy > because I'll have to use a temporary disk file... > > >>> > > >>> Some help would be greatly appreciated! > > >>> Aladdin > > >>> > > >>> > _________________________________________________________________ > > >>> Faites vous de nouveaux amis gr?ce ? l'annuaire des profils Messenger > ! > > >>> > http://home.services.spaces.live.com/search/?page=searchresults&ss=true&FormId=AdvPeopleSearch&form=SPXFRM&tp=3&sc=2&pg=0&Search.DisplayName=Nom+public&search.gender=&search.age=&Search.FirstName=Pr%C3%A9nom&Search.LastName=Nom&search.location=Lieu&search.occupation=Profession&search.interests=amis&submit=Rechercher > > >>> _______________________________________________ > > >>> 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 > > > > > > > _________________________________________________________________ > > > Faites vous de nouveaux amis gr?ce ? l'annuaire des profils Messenger ! > > > > http://home.services.spaces.live.com/search/?page=searchresults&ss=true&FormId=AdvPeopleSearch&form=SPXFRM&tp=3&sc=2&pg=0&Search.DisplayName=Nom+public&search.gender=&search.age=&Search.FirstName=Pr%C3%A9nom&Search.LastName=Nom&search.location=Lieu&search.occupation=Profession&search.interests=amis&submit=Rechercher > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > _________________________________________________________________ > > Retouchez, classez et partagez vos photos gratuitement avec le logiciel > Galerie de Photos ! > > http://www.windowslive.fr/galerie/ > > > > ------------------------------ > > > > Message: 7 > > Date: Mon, 19 May 2008 16:15:12 +0200 > > From: Aladdin Lamp? <[EMAIL PROTECTED]> > > Subject: [sqlite] sqlite3_mprintf() best practice > > To: <sqlite-users@sqlite.org> > > Message-ID: <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset="iso-8859-1" > > > > > > Peeking at the SQLite source code, I see different usage pattern or the > sqlite3_mprintf() function: > > - sqlite3_mprintf("direct static string without %"); > > - sqlite3_mprintf("%s", zString); > > > > What's the recommended usage and best practice for this function? > > I think that using the sqlite3_mprintf(zString) function on an untrusted > string, could lead to a security problem (buffer overflow) in case zString > *could* contain some "%..." format strings, and the - normal - practice > should be: > > - use sqlite3_mprintf("%s", zString); when the string could be provided by > user code (and may contain format strings) > > - use sqlite3_mprintf("direct static string without %"); when we are > absolutely sure that the string cannot, in any situation, contain format > strings. > > > > Is that all or are there other considerations to take into account? > > > > Thanks and have a nice day, > > Aladdin > > > > > _________________________________________________________________ > > Caroline vient de mettre ? jour son profil Messenger ! Connectez-vous ! > > > http://login.live.com/login.srf?wa=wsignin1.0&rpsnv=10&ct=1198837564&rver=4.0.1534.0&wp=MBI&wreply=http:%2F%2Fhome.services.spaces.live.com%2F&lc=1036&id=73625 > > > > ------------------------------ > > > > Message: 8 > > Date: Mon, 19 May 2008 16:29:48 +0200 > > From: Aladdin Lamp? <[EMAIL PROTECTED]> > > Subject: [sqlite] sqlite3Atoi64() and input string "0" > > To: <sqlite-users@sqlite.org> > > Message-ID: <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset="iso-8859-1" > > > > > > Hi! Just wanted to say that the sqlite3Atoi64() function doesn't seem to > work properly when zNum = "0", because the while( zNum[0]=='0' ){ zNum++; } > skips it, leading to an empty string and i == 0. Then, the test "if( c!=0 || > i==0 || i>19 )" always return 0 (false), meaning that the conversion did not > succeed... > > Is that the intended behaviour? > > Bye, > > Aladdin > > > > SQLITE_PRIVATE int sqlite3Atoi64(const char *zNum, i64 *pNum){ > > i64 v = 0; > > int neg; > > int i, c; > > while( isspace(*(u8*)zNum) ) zNum++; > > if( *zNum=='-' ){ > > neg = 1; > > zNum++; > > }else if( *zNum=='+' ){ > > neg = 0; > > zNum++; > > }else{ > > neg = 0; > > } > > while( zNum[0]=='0' ){ zNum++; } /* Skip over leading zeros. Ticket #2454 > */ > > for(i=0; (c=zNum[i])>='0' && c<='9'; i++){ > > v = v*10 + c - '0'; > > } > > *pNum = neg ? -v : v; > > if( c!=0 || i==0 || i>19 ){ > > /* zNum is empty or contains non-numeric text or is longer > > ** than 19 digits (thus guaranting that it is too large) */ > > return 0; > > }else if( i > > > _________________________________________________________________ > > Retouchez, classez et partagez vos photos gratuitement avec le logiciel > Galerie de Photos ! > > http://www.windowslive.fr/galerie/ > > > > ------------------------------ > > > > Message: 9 > > Date: Mon, 19 May 2008 15:35:11 +0000 > > From: palmer ristevski <[EMAIL PROTECTED]> > > Subject: [sqlite] FW: SQLite : text datatype and referential integrity > > To: <sqlite-users@sqlite.org> > > Message-ID: <[EMAIL PROTECTED]> > > Content-Type: text/plain; charset="Windows-1252" > > > > > > > > > > From: [EMAIL PROTECTED] > > To: [EMAIL PROTECTED] > > Subject: SQLite : text datatype and referential integrity > > Date: Sat, 17 May 2008 17:09:25 +0000 > > > > > > > > > > > > > > > > > > Hi, > > Say one defines a column to be of text type, > > but you want only 'text' to contain only alphabetic characters, > > no numeric characters, how would one create this rule and enforce it > > in SQLite SQL or does one have to use triggers. > > Another question I have is, has proper referential integrity been finally > established > > and things like full joins and other joins. > > I am fairly new to SQLite and in researching things I would find things > about > > older versions of SQLite and then hear rumors that they have been fixed in > the newer > > version, but I am not sure. > > > > Palmer > > > > E-mail for the greater good. Join the i?m Initiative from Microsoft. > > > > > _________________________________________________________________ > > E-mail for the greater good. Join the i?m Initiative from Microsoft. > > > http://im.live.com/Messenger/IM/Join/Default.aspx?source=EML_WL_ > GreaterGood > > > > ------------------------------ > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > End of sqlite-users Digest, Vol 5, Issue 47 > > ******************************************* > > > > > > -- > Ing. Hildemaro Carrasquel > Ingeniero de Proyectos > Cel.: 04164388917/04121832139 -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
