[sqlite] porting sqlite3 to embeded os-----lock question
I am porting sqlite3 to the embeded os,such as threadx,nucleus,ect.. I am writing the file such as os_threadx.c,os_nucleus.c according to the os_win.c,os_unix.c. I have read the os_win.c and find that there is a switcher OS_WINCE in the struct winFile. Is this mean the windows platform don't need the function such as share memory(CreateFileMappingW,MapViewOfFile) inside the OS_WINCE swither? whether I should realize the share memory lock function in the embeded os? I have find there is not a direct way similar to the windows share memory and the interface funcitons. It seems difficult to simulate the share memory funciton and it's lock function in my embeded os. Does it mean I must realize it. or the porting will fail. another question: There is also a little difficult to realize the sqlite3WinThreadSpecificData function to get the thread information, Is this also must realize ? thanks a lot. allen.zhang
Re: [sqlite] Limiting the size of a database?
Try the traditional way and use disk partitions/filesystems. Joe Wilson wrote: --- Ron Stevens <[EMAIL PROTECTED]> wrote: Is it possible to tell SQLite to limit the size that a database may grow to? It would be useful for storage constrained applications. This is a tricky problem. What would you have the database do if an insert failed upon reaching the limit? What about the space for the journal files? You could change the I/O subsystem's seek and write calls to have them fail upon exceeding a threshold, but I'm not certain if that's useful. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I'm Starving for New User Information
km4hr wrote: Is there a sqlite introduction for programmers wanting to use the sqlite C API? The info on the web site is pretty sparse. There seems to be plenty of info regarding the use of sqlite3 all over the web. But not much on getting set up to write programs that use sqlite. I have some specific questions below. I am a C programmer and I want use sqlite in my programs. First question is about installation. I went to the sqlite download page and got a ".so" file. This is obviously a shared library but I can't find any installation document that confirms this. What do I do with the ".so" file? I guess I need to put it somewhere, but where? I'm using Fedora Core4 at the moment but what if I also want to use sqlite on RedHat 4? I'd eventually like to use sqlite on HPUX 11.0 but I'll be happy for the moment to get myself oriented on Linux. Are there any link instructions? How do I link a C program on Fedora Core4 Linux for example? "gcc myprog.c -o myprog sqlite.so"? Do I need to set a library path environment variable to point to where the ".so" file is located? Or what? Is a C program that uses sqlite statically linked? That is, is the final executable a self contained program that can be moved from one computer to similar computer? Or does it require supporting sqlite files/libraries to be installed on each computer where the program is run? Can I just copy a program that uses the C API to a similar computer and run it? I assume I would at least have to copy some sqlite data file as well. No? Once I've created a C program that uses sqlite can I administer its database with sqlite3 or do I have to write utility programs to create a database, tables, view table contents, etc? I'd really like to understand how sqlite works. Not internally, but things like where is the data stored? What does it mean to "install" sqlite? If I run a C program in separate directories where does the data get stored? In a common file somewhere? Or does each program have its own sqlite data file? On the sqlite web site there's a brief 5 minute getting started explanation. It explains how to get going using sqlite3. But where do I go after that for more detailed understanding? How do you backup sqlite data? Just copy a data file? Or do you use sqlite3 to dump a file of sql statements? The architecture is unclear to me. I'm hungry to learn more. Is there a summary document somewhere? I'm finding plenty of tutorials on how to use sqlite3, the command line interface. But is there anything that explains the basic architecture? Installation? administration? The sqlite C API documentation seems pretty clear. I can even find helpful documents on the web. I just can't find anything describing how to install and administer sqlite or basically how it works. thanks Just load the source, run configure, make and make install - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Curious performance issue with large number of inserts
On 5 May 2007, at 17:11, Joe Wilson wrote: What timings do you get when you run the perl script in my last email? Your script inevitably was fine. What seems to cause the trouble is something nasty in the pathnames. I've modified my perl script to sanitize them by removing any non-ASCII characters and this makes it all just work. Unfortunately I can't find the offending pathname. It would be interesting to know what went wrong - I'd imagine seriously bad things in strings would make it die. It's possible that it missed a closing quote char and ended up eating a basically infinite string (though I'd expect it to grow much faster than it did in that case, if it grew at all). So I guess this was basically an SQL injection attack in the form of carefully chosen Unix pathnames :-) Thanks for the help! --tim - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: INSERT OR REPLACE without new rowid
On 24/04/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Trey Mack <[EMAIL PROTECTED]> wrote: > I'd like to perform an update to a row if it exists (uniquely > identified by 3 text columns), otherwise insert a new row with the > right data. INSERT OR REPLACE looks good, but it generates a new > primary key each time > there is a conflict. If the row exists, I need to keep the original > primary key > (rowid). > > Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ? You can do UPDATE ... WHERE keyfield='xxx'; then use sqlite3_changes to see whether any update has in fact taken place, and run INSERT if not. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - This is a good solution! Thanks for your idea man! ;) -- Cesar Rodas http://www.cesarodas.com/ Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [sqlite] INSERT OR REPLACE without new rowid
Phani, INSERT OR REPLACE is *close* to an UPSERT / MERGE / whatever you wanna call it, but changes the rowid (actually, creates a whole new row, I think) on collision with a constraint. An example to illustrate: SQLite version 3.3.11 Enter ".help" for instructions sqlite> .mode column sqlite> .headers on sqlite> sqlite> create table t (id integer primary key autoincrement, a text, b text, c text); sqlite> create unique index idx1 on t (a, b, c); sqlite> sqlite> insert into t (a, b, c) values ('a', 'b', 'c'); sqlite> insert into t (a, b, c) values ('b', 'b', 'c'); sqlite> sqlite> select * from t; id a b c -- -- -- -- 1 a b c 2 b b c sqlite> sqlite> insert or replace into t (a, b, c) values ('a', 'b', 'c'); sqlite> sqlite> select * from t; id a b c -- -- -- -- 2 b b c 3 a b c I needed to retain the previous rowid (1) on collision. For what it's worth, I'm just selecting to test for existence first (I need the rowid anyway, so no real penalty), so no problem here. There are other ways, like performing an UPDATE WHERE a = 'a' AND b = 'b' AND c = 'c', then testing if (sqlite3_changes() == 0), if so, INSERT the data, which is likely faster (thanks for the idea Igor). I think the SQL standard is going to adopt the MERGE command for this type of operation, but I'm not sure.. is anyone in the know? It's in ORACLE now, surely elsewhere too.. - Trey - Original Message - From: "B V, Phanisekhar" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 08, 2007 10:59 AM Subject: RE: [sqlite] INSERT OR REPLACE without new rowid Hi Trey, Even I was looking for something like this. But I don't think SQL allows you to do this. I didn't get what u said about INSERT OR REPLACE looks good. Regards, Phani -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 24, 2007 9:11 PM To: sqlite-users@sqlite.org Subject: [sqlite] INSERT OR REPLACE without new rowid Hello all, I'd like to perform an update to a row if it exists (uniquely identified by 3 text columns), otherwise insert a new row with the right data. INSERT OR REPLACE looks good, but it generates a new primary key each time there is a conflict. If the row exists, I need to keep the original primary key (rowid). Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ? Thanks, Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INSERT OR REPLACE without new rowid
Hi Trey, Even I was looking for something like this. But I don't think SQL allows you to do this. I didn't get what u said about INSERT OR REPLACE looks good. Regards, Phani -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 24, 2007 9:11 PM To: sqlite-users@sqlite.org Subject: [sqlite] INSERT OR REPLACE without new rowid Hello all, I'd like to perform an update to a row if it exists (uniquely identified by 3 text columns), otherwise insert a new row with the right data. INSERT OR REPLACE looks good, but it generates a new primary key each time there is a conflict. If the row exists, I need to keep the original primary key (rowid). Any way to do this short of SELECT.. if (exists) UPDATE else INSERT ? Thanks, Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] My HPUX Notes
Essien Essien wrote: that was really funny you know :) And your response was really obnoxious. You could have simply said that the common way to do things on POSIX is "./configure; make; make install;" which would accomplish the same thing and left it at that. Rubbing someone's nose in their inexperience is extremely rude. -- Glenn McAllister <[EMAIL PROTECTED]> +1 416 348 1594 SOMA Networks, Inc. http://www.somanetworks.com/ +1 416 977 1414 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Can I execute queries from an sqlite3_update_hook callback function?
Jef Driesen <[EMAIL PROTECTED]> wrote: I'm trying to execute a query from the callback function that is registered with sqlite3_update_hook. But sqlite3_prepare_v2 always returns SQLITE_MISUSE. Is it not allowed to execute queries from the callback function? I'm was trying to use the callback function as a notification mechanism to update my GUI. Whenever I receive a notification on an insert/delete, I want to retrieve the new/modified row and update the displayed data. Post yourself a message from inside the hook, update UI from that message's handler. Most UI frameworks I know of have a concept of a message or event queue to which you can post user-defined events. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Column naming between 3.2.0 and 3.2.8
Eric Boudaillier <[EMAIL PROTECTED]> wrote: > > I started with sqlite 3.2.0. Now I try to use the version 3.2.8, Why not use 3.3.17? > With sqlite 3.2.0, "arr" contains: > > arr(*) = equipment_type_name io_type_id io_type_name > alarm_if_active > arr(alarm_if_active) = DC1 > arr(equipment_type_name) = CB72_C > arr(io_type_id) = 71 > arr(io_type_name)= DC1 > > With sqlite 3.2.8, "arr" contains: > > arr(*) = equipment_type_name io_type.io_type_id > io_type_name alarm_if_active > arr(alarm_if_active) = DC1 > arr(equipment_type_name) = CB72_C > arr(io_type.io_type_id) = 71 <= Now has the table in its name > arr(io_type_name)= DC1 > > Is there something wrong in 3.2.8? Or have I to update all the queries? > Version 3.2.8 was so long ago that I do not know what changed. In the future, it would be best if you put an AS clause on each of your result columns to assign a particular name to them. That way you do not depend on the (undocumented) names that SQLite assigns by default. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Column naming between 3.2.0 and 3.2.8
Hello all, I use (tcl)sqlite since May 2005, primarily in an application which build a database of electrical equipment along a railway line. I started with sqlite 3.2.0. Now I try to use the version 3.2.8, installed with ActiveTcl. But I have a lots of errors due to the column naming. For example, the following query don't give the same column names: db eval { SELECT equipment_type_name, io_type.io_type_id, io_type_name, alarm_if_active FROM (equipment_type JOIN equipment_type_has_io_type USING (equipment_type_id)) JOIN io_type USING (io_type_id) JOIN io_type_digital_input USING (io_type_id) } arr { break } The result is in the "arr" variable. With sqlite 3.2.0, "arr" contains: arr(*) = equipment_type_name io_type_id io_type_name alarm_if_active arr(alarm_if_active) = DC1 arr(equipment_type_name) = CB72_C arr(io_type_id) = 71 arr(io_type_name)= DC1 With sqlite 3.2.8, "arr" contains: arr(*) = equipment_type_name io_type.io_type_id io_type_name alarm_if_active arr(alarm_if_active) = DC1 arr(equipment_type_name) = CB72_C arr(io_type.io_type_id) = 71 <= Now has the table in its name arr(io_type_name)= DC1 I have checked the two PRAGMA controlling the column naming, and they are identical in both version. I have not seen a note concerning this change. Is there something wrong in 3.2.8? Or have I to update all the queries? Thanks. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE-CORRUPT : Message and the database corruption
Dear Users, I am developing a C++ application with SQLite, we have found SQLite database to get corrupt under rare circumstances, We are unable to specifically reproduce the circumstances, but the database files get corrupt under stress and load, What I would like to know is whether error in our code mainly 'Pointer Corruption' can contribute to corruption of SQLite databases or is the error can be on some other options. Additional Information to Use: Linux Compilation Parameters: SQLite version: 3.2.8 SQLite CFlags used: -pipe -Wall -g0 -O2 SQLite Pre Processors: -DNO_TCL -DNDEBUG -DHAVE_USLEEP -DTEMP_STORE=3 -DSQLITE_MAX_PAGE_SIZE=32768 -DTHREADSAFE=1 ARFLAGS = rcs Pragma options on creation of database file: PRAGMA synchronous = 2 PRAGMA temp_store = 2; PRAGMA default_cache_size = 4000 PRAGMA page_size = 1024 OS Environments where this problem normally occurs, Linux (Debian / Fedora Core / Suse Linux) Thanks in advance for your help Thanks & Regards, Jayavasanthan J - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] My HPUX Notes
On Mon, 2007-05-07 at 12:28 -0700, km4hr wrote: > Notes on how I got sqlite running on hpux 11.0 > > To install sqlite on hpux: > * download sqlite-3.3.17.tar.gz from web site. > (I unzipped the file on linux using gunzip I think. Then copied >the tar file to /opt on the unix box. I guess gunzip works on hpux also.) > * cd to /opt and untar the file. (tar xf sqlite-3.3.17.tar) > * cd to sqlite... dir created by tar. > * create a "bld" directory as recommended in sqlite README. > * cd to bld. > * Use GNU bash version of "make" (came with hpux 11.0 on my computer > /opt/OpenSource/...) to > perform the build instructions given in sqlite README. The hpux version of > make doesn't work. It chokes on the "+=" operators in the make file. > * after "making" sqlite, look in the "bld/.libs" directory for the sqlite > library files. "sqlite3" command line program is there as well. > * no "sqlite.h" file is provided by the install (?) but "sqlite.h.in" > provided works. Mostly works. It's easiest if you run the "make install" target too. i.e.: $ mkdir /home/km4hr/sqlite_install/ $ ../sqlite/configure --prefix=/home/km4hr/sqlite_install/ $ make install Then you wind up with sub-directories "bin", "include" and "lib" under /home/kv4hr/sqlite_install/. Everything is more or less where you expect it to be. You'll still need to use the gnu make of course. Dan. > * here's how I compiled the test program given on the sqlite web site: > cd to my home directory. > Copied contents of C test program on web site into file "myprog.c". > Changed "#include " to "#include "/sqlite.h.in". > Then: "cc -o myprog myprog.c /opt/sqlite/bld/.libs/libsqlite3.a" > * Used /opt/sqlite/bld/.libs/sqlite3 to create database "test.db" (create > table...) > and to add some records in a table (insert into table ...). > * run myprog test program to dump the table. > Ex: myprog test.db "select * from tablename" > > Works! Success! > > * Now I need to figure out where to install the sqlite library and header > file permanently on hpux. > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] unicode like UPPER and LOWER ?
On Tue, 2007-05-08 at 10:45 +0700, Kirill wrote: > Good day, > > SQLite version 3.3.17 > Enter ".help" for instructions > sqlite> create table tbl1(t1 varchar(10)); > sqlite> insert into tbl1 values('софт'); - lowChar > sqlite> insert into tbl1 values('СОФТ'); - upChar > sqlite> select * from tbl1; > софт > СОФТ > sqlite> select * from tbl1 where t1 like '%оф%'; - lowChar > софт - lowChar > > :( > > what do?: > > sqlite> select * from tbl1 where t1 like '%оф%'; - lowChar > софт - lowChar > СОФТ - upChar > By default, SQLite only knows about the upper and lower case equivalents for ASCII characters. You can override the built-in LIKE operator with an external (possibly unicode aware) version by overriding the like(X,Y) and like(X,Y,E) scalar functions as described here: http://www.sqlite.org/lang_expr.html There is some ***UNTESTED*** code for an SQLite extension to do this using the ICU library at: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/icu.c You could use this directly, or as an example to develop your own LIKE function. If you do use it and find bugs, please report them here or in cvstrac. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can I execute queries from an sqlite3_update_hook callback function?
I'm trying to execute a query from the callback function that is registered with sqlite3_update_hook. But sqlite3_prepare_v2 always returns SQLITE_MISUSE. Is it not allowed to execute queries from the callback function? I'm was trying to use the callback function as a notification mechanism to update my GUI. Whenever I receive a notification on an insert/delete, I want to retrieve the new/modified row and update the displayed data. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Help wiith SQL - first row of each group
Tricky it is - and original. Thanks for all the suggestions - I'll try Ed's "trick" and Tomash's function - Another option - similar to Tomash's solution - is processing the rows ordered by group and priority through a "collapsing" callback That only "accepts" the first row per group. Thanks again, Eliedaat -Original Message- From: Ed Pasma [mailto:[EMAIL PROTECTED] Sent: Monday, May 07, 2007 10:28 PM To: Adler, Eliedaat Cc: sqlite-users@sqlite.org Subject: RE: [sqlite] Help wiith SQL - first row of each group This solution may is tricky but has occasoinaly helped me. It is written here dedicated for the example data. For real data the leftpadding should likely be increased to the content of the sorting key. Also the result may need to be converted to the expected data type, it has now become text. SELECT g, SUBSTR (MAX (SUBSTR (' ' || p, -2, 2) || v), 3, 1) v FROM t GROUP BY g ; *** This email message and any attachments thereto are intended only for use by the addressee(s) named above, and may contain legally privileged and/or confidential information. If the reader of this message is not the intended recipient, or the employee or agent responsible to deliver it to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the [EMAIL PROTECTED] and destroy the original message. *** - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] My HPUX Notes
that was really funny you know :) On POSIX systems you're supposed to do three things to get your software installed (usually a standard set of three commands). 1. ./configure (some packages leave this out, since they don't use GNU autotools) 2. make (there's almost always this, or an alternative, CMAKE, Scons, ant, etc) 3. make install (if there's a Makefile, you'll be able to do a make install, if the software is supposed to be used anyways... and all replacements have equivalents - ant deploy for instance). So please, please, please, your steps are all wrong... you're not installing sqlite the way you're supposed to install from source on POSIX systems, go back and do the steps.. and you know try to learn about building stuff from source (google would seem to be a good referal source here :) ) PS: I really had to reply to this, seeing as your steps are all wrong, and this mail will be archived and someone else (2 yrs from now), may _actually_ follow your steps (omg!!!) :P Also, I'll take a one step long-suffering act (sigh), you can email me offlist, for a _OneTime_ tutorial (if you really want to learn the right way). After that, read this email again, and you'll see how ridiculous it sounds. peace bro, Essien On 5/7/07, km4hr <[EMAIL PROTECTED]> wrote: Notes on how I got sqlite running on hpux 11.0 To install sqlite on hpux: * download sqlite-3.3.17.tar.gz from web site. (I unzipped the file on linux using gunzip I think. Then copied the tar file to /opt on the unix box. I guess gunzip works on hpux also.) * cd to /opt and untar the file. (tar xf sqlite-3.3.17.tar) * cd to sqlite... dir created by tar. * create a "bld" directory as recommended in sqlite README. * cd to bld. * Use GNU bash version of "make" (came with hpux 11.0 on my computer /opt/OpenSource/...) to perform the build instructions given in sqlite README. The hpux version of make doesn't work. It chokes on the "+=" operators in the make file. * after "making" sqlite, look in the "bld/.libs" directory for the sqlite library files. "sqlite3" command line program is there as well. * no "sqlite.h" file is provided by the install (?) but "sqlite.h.in" provided works. * here's how I compiled the test program given on the sqlite web site: cd to my home directory. Copied contents of C test program on web site into file "myprog.c". Changed "#include " to "#include "/sqlite.h.in". Then: "cc -o myprog myprog.c /opt/sqlite/bld/.libs/libsqlite3.a" * Used /opt/sqlite/bld/.libs/sqlite3 to create database "test.db" (create table...) and to add some records in a table (insert into table ...). * run myprog test program to dump the table. Ex: myprog test.db "select * from tablename" Works! Success! * Now I need to figure out where to install the sqlite library and header file permanently on hpux. -- View this message in context: http://www.nabble.com/I%27m-Starving-for-New-User-Information-tf3701471.html#a10363806 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -