Re: [sqlite] how can we solve IF EXIST in SQLite
its showing an error near "if": syntax error Kees Nuyt wrote: > > On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew > wrote: > >> >>hey thanx for the reply... u leave the things happening inside.. wat i jus >>wanna do is i wanna insert a new row to a table >>the table will be like this >>stock_id PKproduct_id FK quantitystock_date >>1 10001028-05-2009 >>10001 1001 527-05-2009 >> >>and wen i insert a new row with values NULL, 1000, 15,30-05-2009 >> >>i dont want want it as a new recorde i jus want to update the first row coz >>its also having the same product id i jus want set the quantity = 10+15 and >>the date new date that is 30-05-2009 >>and suppose if i insert row with different product_id it should be inserted >>as it is.. > > Pseudocode: > BEGIN; > UPDATE stock_tab SET . WHERE stock_id = 1; > if sqlite_error() > INSERT INTO stock_tab SET (...) VALUES (...); > endif > COMMIT; > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23846618.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] SQLite command line interpreter vs DLL
Is there a reason why the command line interpreter supports more functions while the dll does not? I would like to create an application using SQLite with and in memory database. I have some big text files (csv), which can be imported nicely by the command line interpreter using the .import command. However when later on, I'd like to manipulate data, using the DLL - or it's compiled version - in my application I cannot access the in memory database created earlier using the command line interpreter. Obviously the DLL open command creates a new in-memory database instance. On the other hand, the DLL itself does not offer the great .import or .output functionalities.. I wonder why the DLL version does not support those dot commands supported by the comand line version? Best regards, Zsolt Sera Vienna ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
you should use the insert or replace statement, it inserts if the row doesnt exist, if teh row does exists then it updates the row. --- On Wed, 6/3/09, robinsmathew wrote: From: robinsmathew Subject: Re: [sqlite] how can we solve IF EXIST in SQLite To: sqlite-users@sqlite.org Date: Wednesday, June 3, 2009, 3:15 AM its showing an error near "if": syntax error Kees Nuyt wrote: > > On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew > wrote: > >> >>hey thanx for the reply... u leave the things happening inside.. wat i jus >>wanna do is i wanna insert a new row to a table >>the table will be like this >>stock_id PK product_id FK quantity stock_date >>1 1000 10 28-05-2009 >>10001 1001 5 27-05-2009 >> >>and wen i insert a new row with values NULL, 1000, 15, 30-05-2009 >> >>i dont want want it as a new recorde i jus want to update the first row coz >>its also having the same product id i jus want set the quantity = 10+15 and >>the date new date that is 30-05-2009 >>and suppose if i insert row with different product_id it should be inserted >>as it is.. > > Pseudocode: > BEGIN; > UPDATE stock_tab SET . WHERE stock_id = 1; > if sqlite_error() > INSERT INTO stock_tab SET (...) VALUES (...); > endif > COMMIT; > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-can-we-solve-IF-EXIST-in-SQLite-tp23828274p23846618.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
I can perfectly understand the decision made few years ago,and the result is splendid, I use SQLite every days. I am just wondering why not introducing C++? for better memory management for example (RAII) I am just wondering, don't reply aggressively please ... Cheers, Sylvain On Wed, Jun 3, 2009 at 12:03 AM, John Stanton wrote: > A good tool ltries to be "bette"r, not "easier". Libraries compiled > from C are compatible with C++. In general optimization is better with > C compilers and the support libraries are more compact and effcient. > Producing Sqlite in well written and documented ANSI C was a very sound > decision. > > If you want to incorporate Sqlite in a C++ environment use C++ wrappers > for the API calls. > > Sylvain Pointeau wrote: > > ... because I experienced C++ to be easier with the classes and resource > > management via the destructor.I was just wondering why C++ is not used? > > > > was it for a performance issue? > > or a compiler issue? > > or anything else? > > > > I just read the Linus Torvalds comment on the C++ for Git > > What do you think? > > > > Cheers, > > Sylvain > > > > On Tue, Jun 2, 2009 at 6:16 PM, John Stanton > wrote: > > > > > >> Why wuld you want to do such a thing? > >> > >> Sylvain Pointeau wrote: > >> > >>> Hello, > >>> I would like to know if someone already though about to introduce C++ > in > >>> SQLite? > >>> I just think about a minimal subset of C++ that will not make any > >>> performance penalty > >>> (like C with classes) > >>> > >>> is it a performance issue? > >>> is it a deployment/compiler issue? > >>> or any issue? > >>> > >>> Please don't make any aggressive reply, I am a very nice guy :-) > >>> > >>> Cheers, > >>> Sylvain > >>> ___ > >>> 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 > > > > ___ > 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] how can we solve IF EXIST in SQLite
On 3/06/2009 5:15 PM, robinsmathew wrote:
> its showing an error near "if": syntax error
"it", my crystal ball tells me, is an SQL processor, behaving much as
expected when fed what looks like an "if" statement in some other
language ...
> Kees Nuyt wrote:
>> Pseudocode:
google("pseudocode")
>> BEGIN;
>> UPDATE stock_tab SET . WHERE stock_id = 1;
>> if sqlite_error()
>> INSERT INTO stock_tab SET (...) VALUES (...);
>> endif
>> COMMIT;
Try this exploded version, written using functions in some arbitrary
wrapper language, which you need to translate into whatever language you
are using, supplying missing arguments like a connection and maybe a
cursor, and adding error-checking where appropriate:
exec_sql("BEGIN")
exec_sql("UPDATE stock_tab SET . WHERE stock_id = 1")
if an_error_happened():
exec_sql("INSERT INTO stock_tab SET (...) VALUES (...)")
exec_sql("COMMIT")
HTH,
John
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Schema design and/or SELECT construction
Hello: I have about 3000 electronic images that I am preparing to distribute to my family members. They use a variety of operating systems, so I am providing the images on remote hard drives, with the images divided into folders based on years the images were created. All images were obtained via scanning of negatives and slides, and each image has been keyworded in EXIF format. What I want is to create a master SQLite database catalog of all the images, and I have come up with a proposed schema: TABLE main (all pertinent image data such as date, location in folder, etc.) TABLE core_keywords (id, name) --This table would only hold the names of my immediate family members, each with a corresponding id TABLE other_keyword (id,name) -- all other keywords I have thought to create two keyword fields in the main table, one to hold the ids of the core_keywords (comma separated) and one to hold the ids of the other_keywords, also comma separated. What I cannot devise is an elegant method to SELECT based on the core_keywords to achieve the following sorts: 1- Find images with a single core_keyword id, that is, only images of a single person, no other core persons in the image 2- Find images with a specific set of core_keyword ids, such as 1 and 6 or 2 and 5 and 7, etc., with no other core persons in the image The idea is to create a document with lists of all images that are exclusive to single individuals, specific pairs, etc., so that family members can easily find themselves or groups, regardless of image catalog software they use on their particular systems, which may or may not be able to perform these types of sorts. I am not asking anyone to actually write the SELECT statements for me, but rather point me toward the operands that would achieve my goal. I have read through the documentation, and I cannot seem to generate the logic in my head to SELECT WHERE core_id is only 4. If anyone has an idea on a more efficient database design, or TABLE schema, please do not hesitate to proffer your thoughts. I am hoping to have it all figured out BEFORE I load up the tables with data. (I am actually still scanning images at this stage, but trying to prepare for the next phase.) Thank you very much for your time and consideration. Craig Smith cr...@macscripter.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
On Wed, Jun 3, 2009 at 8:59 AM, Sylvain Pointeau wrote: > I can perfectly understand the decision made few years ago,and the result is > splendid, I use SQLite every days. > > I am just wondering why not introducing C++? for better memory management > for example (RAII) > > I am just wondering, don't reply aggressively please ... If you keep asking when you've already received a perfectly good answer (about portability), people will start to think you're trying to start an argument (about memory management). For instance, someone might say: "If you really want to use an object-oriented extension to C with decent memory management, you don't want C++, you want Objective-C" ;) Playfully yours, H ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Perticular Field encription in sqlite3 database
On Wed, Jun 3, 2009 at 1:04 AM, Roger Binns wrote: > But wait, there is more. If rows store the same password then they will > have the same hash which means if either user sees the file they can > compromise the other user. In addition crackers have generated tables > mapping all letter and number combinations for possible passwords up to > a certain length and their hash values, called rainbow tables. Yes this > is a large amount of data - one rainbow table is a 64gb download. But > you can simply find a matching hash value and corresponding string that > made it. The defense against this attack is to add random data to the > password before hashing it, aka salting. You can store the salt in the > clear. That helps considerably against the rainbow table attack and > also means that different rows having the same password will have a > different hash due to the different salt. > > http://www.codinghorror.com/blog/archives/000949.html > http://en.wikipedia.org/wiki/Rainbow_table I often wondered about this, but never followed it up because of laziness. Thanks for the clear explanation. -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Charter Member, Open Source Geospatial Foundation http://www.osgeo.org/ Science Commons Fellow, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ --- collaborate, communicate, compete === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
On Wed, Jun 3, 2009 at 5:02 AM, Hamish Allan wrote: > On Wed, Jun 3, 2009 at 8:59 AM, Sylvain Pointeau > wrote: > >> I can perfectly understand the decision made few years ago,and the result is >> splendid, I use SQLite every days. >> >> I am just wondering why not introducing C++? for better memory management >> for example (RAII) >> >> I am just wondering, don't reply aggressively please ... > > If you keep asking when you've already received a perfectly good > answer (about portability), people will start to think you're trying > to start an argument (about memory management). Indeed. Very good reply. To Sylvain, once again: speculating on what went into the minds of the developers, when they set out to develop SQLite, they chose the best, most concise, most portable, most universally compilable, mother of almost all languages. Once they developed something that was free, fast and cheap, there was no reason to change. Case closed. If you thing C++ can do a better job at doing what SQLite does on all the variety of platforms that it runs on flawlessly, well, the source code is available in public domain -- go ahead and create SQLite++ by transcribing each function into the language of your choice. May the better plan win. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite command line interpreter vs DLL
On Wed, Jun 3, 2009 at 2:22 AM, Fam. Sera wrote: > > Is there a reason why the command line interpreter supports more functions > while the dll does not? > > I would like to create an application using SQLite with and in memory > database. I have some big text files (csv), which can be imported nicely by > the command line interpreter using the .import command. However when later > on, I'd like to manipulate data, using the DLL - or it's compiled version - > in my application I cannot access the in memory database created earlier > using the command line interpreter. Obviously the DLL open command creates a > new in-memory database instance. On the other hand, the DLL itself does not > offer the great .import or .output functionalities.. > > I wonder why the DLL version does not support those dot commands supported by > the comand line version? The "command line version" is not a version -- it is an application created with sqlite, the library (in your case, the dll). The dot commands are convenience utilities added to the application. You could perhaps create your own application using the dll that does the same as the command line version does. > > Best regards, > > Zsolt Sera > Vienna > > > > > ___ > 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, Geospatial Data http://sciencecommons.org Nelson Institute, UW-Madison http://www.nelson.wisc.edu/ --- collaborate, communicate, compete === Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
yes true, case closed then ! Many thanks for all of your answers. Cheers, Sylvain On Wed, Jun 3, 2009 at 12:17 PM, P Kishor wrote: > On Wed, Jun 3, 2009 at 5:02 AM, Hamish Allan wrote: > > On Wed, Jun 3, 2009 at 8:59 AM, Sylvain Pointeau > > wrote: > > > >> I can perfectly understand the decision made few years ago,and the > result is > >> splendid, I use SQLite every days. > >> > >> I am just wondering why not introducing C++? for better memory > management > >> for example (RAII) > >> > >> I am just wondering, don't reply aggressively please ... > > > > If you keep asking when you've already received a perfectly good > > answer (about portability), people will start to think you're trying > > to start an argument (about memory management). > > Indeed. Very good reply. > > To Sylvain, once again: speculating on what went into the minds of the > developers, when they set out to develop SQLite, they chose the best, > most concise, most portable, most universally compilable, mother of > almost all languages. Once they developed something that was free, > fast and cheap, there was no reason to change. Case closed. > > If you thing C++ can do a better job at doing what SQLite does on all > the variety of platforms that it runs on flawlessly, well, the source > code is available in public domain -- go ahead and create SQLite++ by > transcribing each function into the language of your choice. > > May the better plan win. > > > -- > Puneet Kishor > ___ > 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] SQLite command line interpreter vs DLL
Fam. Sera schrieb: > Is there a reason why the command line interpreter supports more functions > while the dll does not? > > I would like to create an application using SQLite with and in memory > database. I have some big text files (csv), which can be imported nicely by > the command line interpreter using the .import command. However when later > on, I'd like to manipulate data, using the DLL - or it's compiled version - > in my application I cannot access the in memory database created earlier > using the command line interpreter. Obviously the DLL open command creates a > new in-memory database instance. On the other hand, the DLL itself does not > offer the great .import or .output functionalities.. > > I wonder why the DLL version does not support those dot commands supported by > the comand line version? > > Best regards, > > Zsolt Sera > Vienna > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > It's easy enoug to transform sqlite command line tool into a GUI-Window. For short there are three different approaches : A) You use Borland's C++ 5.0 which has a window control that emulates a command line window. I did this with other command line applications and it works. B) You can use the code for sqlite and do the following : Overwrite all standard functions for stdio with your own versions and translate them into calls for GUI routines. This sounds terrible but in reallity is is very easy. What you really need is an edit control and a minimal amount of own code for redirection. When you use this method you'll end up with having a emulated command window in your application which allows always administration from a GUI application with the power of the sqlite command line and it's dot commands. I think that many programmers would profit from a little changed sqlite command line tool in which each of the included standard function names would be Pointers to functions or to an "struct-object" with a function pointer table. That would make porting and such uses as a GUI command line much easier. I did it this way and have to search->rename each time a changed version appears. C) The third way to keep the functionality of sqlite command line and integrate it in your application would be to seperate the integrated parser of this tiny useful tool and wrap all user interface routines with GUI or script-functions. This is also not very hard to achive because sqlite command line tool is prepared for this tasks and has a string parser so it doesn't really care from which source the string really comes. If you really need such a administrative windows or a function interface in your gui application sqlite command line is very good and easily to transform into a gui application. Method B is the one i personaly prefered because when you once have a window which is able to simulate a console you can exchange your personal administrative dll with the regular code after search-rename. Some would advice to redefine standard function names with makros but that is dangerous. It's better to rename them like printf ==> myprintf and declare a function pointer for myprintf. With a Makro selection you can than declare this pointers as standard function pointers or substitute them with your own implementations. Hope i could help you ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
Hi all, I'd like to comment this topic as well. I think C is a very good choice for projects like sqlite. The point is that C is in contrary to C++ very easily portable. If you have the bad luck that you have to port a programm on a new plattform for which no compiler suites exist than the choice of C for such a project pays of. You'll find a few short understandable and editable C-Compiler implementations on the internet. As an example TCC, LCC or PCC. They are short enough to port to a new plattform. To port TCC to a new plattform as an example needs about one week when you have to start from zero because you only need to refine the code generation interface for the destined target system. sqlite uses a few dozen standard function calls and the most of them are easily to implement in C itself. And don't forget that C is designed as a portable assembly language so it's really fast. If you now look at C++ you'll find only two mentionable Compiler implementations which you can use for a porting task. And these toolchains require more than the knowledge of C with which you could port TCC or PCC. When you look closer to the sqlite code you'll find, that the code uses objectoriented techniques on many places. Many of the interfaces to different functionality like vfs or fts are objects in a portable way. A C++ object is nothing different than a table of functions and data pointers and this is used in sqlite as well but the difference is, that this implementation is portable and interchangeable between compilers. If you would start to use C++ than you would end up compiling a interface with one compiler and being unable to use this interface from another compiler or even a compiler with a newer release date. C++ only supports compile time modularity and no runtime compatibility between libraries written in this language and compiled with different versions. The binary implementation of OOP abilities is always proprietary. The support of the code would be horrible because you'd have to deal with incompatibilities between different implementations. To summarize my opinion : C is the best choice for portable projects like sqlite. C is fast enough and if you don't have a compiler you can create one. If you don't have a standard library you can use free libraries. I would wish that drh would go a step further and avoid using awk and tcl for the code creation process. This would solve the problem with Preprocessor definitions which can be set during compile time but where some have also to be set while you create lemon. It is possible to exchange each of the awk and tcl scripts which are used during code generation and the generation of the amalgamation with only C this would make the whole thing simpler to port and more independent from any external tools or plattforms and it would also make it possible to use all of the creation makros while you create the sourcecode. Ibrahim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting last inserted rowid?
On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: > Nuno Lucas writes: >> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: >>> Hello, >>> >>> How can I determine the rowid of the last insert if I am accessing the >>> db from different threads? If I understand correctly, >>> last_insert_rowid() won't work reliably in this case. >> >> It should work if you do: >> >> BEGIN >> INSERT ... >> last_insert_rowid() >> END > > That would be very nice. But does "it should work" mean that you know > that it works (and it is documented and guaranteed)? The above sounds a > bit uncertain to me... It just means I'm too old to assume anything is certain. The Universe is always conspiring against you ;-) What I mean is that if it doesn't work, then you found a bug, most probably in your own code. Regards, ~Nuno Lucas > > Thanks for the clarification, > > -Nikolaus > > -- > »Time flies like an arrow, fruit flies like a Banana.« > > PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C > > ___ > 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] Schema design and/or SELECT construction
What's wrong with a properly normalised schema like this: create table main (id, name,...); create table keyword (id, label); create table crossref (id_main, id_keyword); The concept of core and other keywords is a bit arbitrary. What is important (i.e. core) today might not be so tomorrow. Parsing comma separated lists in a single attribute is likely to be a bother. On Mon, 2009-06-01 at 21:21 -0700, Craig Smith wrote: > Hello: > > I have about 3000 electronic images that I am preparing to distribute > to my family members. They use a variety of operating systems, so I > am providing the images on remote hard drives, with the images divided > into folders based on years the images were created. All images were > obtained via scanning of negatives and slides, and each image has been > keyworded in EXIF format. > > What I want is to create a master SQLite database catalog of all the > images, and I have come up with a proposed schema: > > TABLE main (all pertinent image data such as date, location in folder, > etc.) > TABLE core_keywords (id, name) --This table would only hold the names > of my immediate family members, each with a corresponding id > TABLE other_keyword (id,name) -- all other keywords > > I have thought to create two keyword fields in the main table, one to > hold the ids of the core_keywords (comma separated) and one to hold > the ids of the other_keywords, also comma separated. What I cannot > devise is an elegant method to SELECT based on the core_keywords to > achieve the following sorts: > > 1- Find images with a single core_keyword id, that is, only images of > a single person, no other core persons in the image > 2- Find images with a specific set of core_keyword ids, such as 1 and > 6 or 2 and 5 and 7, etc., with no other core persons in the image > > The idea is to create a document with lists of all images that are > exclusive to single individuals, specific pairs, etc., so that family > members can easily find themselves or groups, regardless of image > catalog software they use on their particular systems, which may or > may not be able to perform these types of sorts. > > I am not asking anyone to actually write the SELECT statements for me, > but rather point me toward the operands that would achieve my goal. I > have read through the documentation, and I cannot seem to generate the > logic in my head to SELECT WHERE core_id is only 4. > > If anyone has an idea on a more efficient database design, or TABLE > schema, please do not hesitate to proffer your thoughts. I am hoping > to have it all figured out BEFORE I load up the tables with data. (I > am actually still scanning images at this stage, but trying to prepare > for the next phase.) > > Thank you very much for your time and consideration. > > > Craig Smith > cr...@macscripter.net > > > > ___ > 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] [OT] Basic Netbeans project won't run on SQLite
Greetings, i'm trying to use SQLite for an academic project, with Netbeans (has to be java). I followed "new desktop application » database" and chose SQLite (after installing said plugin). But even for a test run, it gives me this error: Jun 3, 2009 2:49:12 PM org.jdesktop.application.Application$1 run SEVERE: Application class desktopapplication2.DesktopApplication2 failed to launch java.lang.IllegalArgumentException: An exception occured while creating a query in EntityManager at oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl.createQuery(EntityManagerImpl.java:209) It goes on and on, the full error's here[1], but why is it throwing *oracle* errors if i'm using SQLite?! The database is there, readable, has a tet table that Netbeans can find and it lists its contents, the JBDC driver's there as well (since i could select it)... Any suggestions would be appreciated, i haven't been able to find decent info on the net (most likely i'm not searching right). TIA, Nuno Magalhães [1] http://pastebin.com/m247e6077 -- () ascii ribbon campaign - against html e-mail /\ ascii-rubanda kampajno - kontraŭ html-a retpoŝto ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
Well said! C++ provides the average programmer "automated" Object Orientation, or better stated, good programming practices, at a price. A good C programmer can equal and most times exceed the results of a C++ programming project. As previously stated C is really just a lazy man's Assembler, and you can't get any closer to the hardware, unless you are into machine language, independently wealthy, with no need for sleep. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Ibrahim A Sent: Wednesday, June 03, 2009 6:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite programmed in C++ Hi all, I'd like to comment this topic as well. I think C is a very good choice for projects like sqlite. The point is that C is in contrary to C++ very easily portable. If you have the bad luck that you have to port a programm on a new plattform for which no compiler suites exist than the choice of C for such a project pays of. You'll find a few short understandable and editable C-Compiler implementations on the internet. As an example TCC, LCC or PCC. They are short enough to port to a new plattform. To port TCC to a new plattform as an example needs about one week when you have to start from zero because you only need to refine the code generation interface for the destined target system. sqlite uses a few dozen standard function calls and the most of them are easily to implement in C itself. And don't forget that C is designed as a portable assembly language so it's really fast. If you now look at C++ you'll find only two mentionable Compiler implementations which you can use for a porting task. And these toolchains require more than the knowledge of C with which you could port TCC or PCC. When you look closer to the sqlite code you'll find, that the code uses objectoriented techniques on many places. Many of the interfaces to different functionality like vfs or fts are objects in a portable way. A C++ object is nothing different than a table of functions and data pointers and this is used in sqlite as well but the difference is, that this implementation is portable and interchangeable between compilers. If you would start to use C++ than you would end up compiling a interface with one compiler and being unable to use this interface from another compiler or even a compiler with a newer release date. C++ only supports compile time modularity and no runtime compatibility between libraries written in this language and compiled with different versions. The binary implementation of OOP abilities is always proprietary. The support of the code would be horrible because you'd have to deal with incompatibilities between different implementations. To summarize my opinion : C is the best choice for portable projects like sqlite. C is fast enough and if you don't have a compiler you can create one. If you don't have a standard library you can use free libraries. I would wish that drh would go a step further and avoid using awk and tcl for the code creation process. This would solve the problem with Preprocessor definitions which can be set during compile time but where some have also to be set while you create lemon. It is possible to exchange each of the awk and tcl scripts which are used during code generation and the generation of the amalgamation with only C this would make the whole thing simpler to port and more independent from any external tools or plattforms and it would also make it possible to use all of the creation makros while you create the sourcecode. Ibrahim ___ 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] [OT] Basic Netbeans project won't run on SQLite [SOLVED]
Eh... even though i chose an SQLite database, Netbeans doesn't include the sqlite.jar ... -- () ascii ribbon campaign - against html e-mail /\ ascii-rubanda kampajno - kontraŭ html-a retpoŝto ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
Indeed. Very good reply. > To Sylvain, once again: speculating on what went into the minds of the > developers, when they set out to develop SQLite, they chose the best, > most concise, most portable, most universally compilable, mother of > almost all languages. Once they developed something that was free, > fast and cheap, there was no reason to change. Case closed. > > If you thing C++ can do a better job at doing what SQLite does on all > the variety of platforms that it runs on flawlessly, well, the source > code is available in public domain -- go ahead and create SQLite++ by > transcribing each function into the language of your choice. > > May the better plan win. > > This is something of a digression but is pertinent. Colleagues who worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was disillusioned with C++ and its wide deployment and would encourage people not to use it unless there were clear advantages. In our own company we came to the same conclusion as Dr Hipp and used ANSI C for our compilers and database software. C can be anything you want it to be. For example you can ensure portability by incorporating your own memory management system and tightly manage your use of foreign libraries. for quality assurance You have access to highly optimizing compilers which can produce executables as good as those written by a skilled Assembler programmer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema design and/or SELECT construction
On Mon, Jun 01, 2009 at 09:21:40PM -0700, Craig Smith scratched on the wall: > Hello: > > I have about 3000 electronic images that I am preparing to distribute > to my family members. They use a variety of operating systems, so I > am providing the images on remote hard drives, with the images divided > into folders based on years the images were created. All images were > obtained via scanning of negatives and slides, and each image has been > keyworded in EXIF format. > > What I want is to create a master SQLite database catalog of all the > images, and I have come up with a proposed schema: > > TABLE main (all pertinent image data such as date, location in folder, > etc.) > TABLE core_keywords (id, name) --This table would only hold the names > of my immediate family members, each with a corresponding id > TABLE other_keyword (id,name) -- all other keywords > I have thought to create two keyword fields in the main table, one to > hold the ids of the core_keywords (comma separated) and one to hold > the ids of the other_keywords, also comma separated. Generally, I would call this a bad idea. By most people's thinking it also breaks First Normal Form. Besides, you can't do database operations on comma separated lists. If you need a one-to-many (one pic to many keywords) then do it right and build an actual one-to-many relationship between two tables. Although, in this case, I'd say it is actually many-to-many. Might I suggest: TABLE pictures (pic_id INTEGER PRIMARY KEY, filename NOT NULL UNIQUE, . ); TABLE keywords (key_id INTEGER PRIMARY KEY, is_core bool, word NOT NULL UNIQUE); TABLE pic_to_key (pic_id, key_id, PRIMARY KEY (pic_id, key_id)); INDEX pic_to_key (key_id, pick_id); -- just because Define your pictures. Define your keywords. You can flag specific keywords as "core." Define which pics have which keywords. You can then use a keyword to find pictures, or use pictures to find keywords. > What I cannot > devise is an elegant method to SELECT based on the core_keywords to > achieve the following sorts: > > 1- Find images with a single core_keyword id, that is, only images of > a single person, no other core persons in the image Lookup the key_id. Find all the pics with that key idea. Find all the key ids for those pictures. Count them, select the pics with 1. > 2- Find images with a specific set of core_keyword ids, such as 1 and > 6 or 2 and 5 and 7, etc., with no other core persons in the image Same basic idea, left as an exercise to the reader. I'll offer this hint, however: This is a situation that will sometimes lead to the need to do Relational Divison, something that isn't directly supported by SQL. Have a look at Celko's article on it: http://www.dbazine.com/ofinterest/oi-articles/celko1 You'll often end up with a "vertical" list of key_ids (a picture joined against pic_to_key to get a list of keys) and what you need is a "horizontal" list to run through your logic statements. That all points to Relational Division. Or you can just move the selection logic to your applicaiton. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compose the sql sentence?
On 3 Jun 2009, at 7:05am, liubin liu wrote: > the first step is to tell if there is the data in the table. > if the answer is not, I want to insert a row of data into the table > if the answer is yes, I need to update the row of data acccording to > the > data inputting from me. INSERT OR REPLACE INTO table (columns) VALUES (values) This will use the columns and indices you have already defined as UNIQUE to decide whether it should INSERT a new row or REPLACE an existing one. So take care in creating UNIQUE columns or a UNIQUE index that does what you want. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
On Wed, Jun 03, 2009 at 12:25:14AM +0200, Jan scratched on the wall: > Hi, > > If you don't want to update, but you do want to query for entire > > subtrees, do give nested sets more consideration. > > But as Jay pointed out: Nested sets only work with one parent. Do they? You can think of nested sets as basically sets of parenthesis. So the tree: A / \ B C / /|\ D E F G Turns into: (A:(B:(D:))(C:(E:)(F:)(G:))) As you can see, quite literally "nested sets" (or "sets of sets"). Each node can have exactly one parent (the containing set) and zero or more (with "more" being > 2) children. In the case of a family tree, you can get around the "one parent" by extracting the table structure out to a detail table, so that the tree table only has "person_id" values that point back to some master "person" table. You can then just build two nested sets: one that represents all fathers and one that represents all mothers. The "father" table will still have daughters, but daughters will always lack any children (in the "father" table). [I think that will work. My morning coffee has just about worn off.] Of course, this cancels out many of the query optimizations that nested sets are good at, since you'll frequently need to combine data from the two trees to get what you want. But it would be possible. The bigger issue is that nested sets assume a perfect tree structure. It has to lead back to a "point." You could, in theory, do a family tree for a single person by turning the table up-side down, but if you're trying to track breeding over a group of animals you need not so much a tree as a scattered mesh that generally trends in one direction. Unless you started out with exactly one male and one female, a nested set isn't going to cut it. I'm also unsure about cross-generational links (Like a son being a half-brother kind of thing) that might happen in lab animals. adjacency lists can deal with all of these quite easily. You can have multiple NULL parents for the "tops" of different sub-trees, and the tree structure is localized to a node and it's parents, meaning the links can go all over the place. As for AVL trees, I'm just confused by that suggestion. AVL trees, like B-trees, Red/Black trees, or just about any kind of balanced tree are designed to hold sorted lists. The whole idea of balanced trees is that the tree structure can rearrange itself at will, just as long as the leaf nodes keep their order and are fast to find. You can't hold a tree structure in a AVL tree since the tree structure is prone to changing if you add or remove leaf nodes. Or am I missing something? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
just curious. why not a table for stricty for the linkages structure similar to create table Linkages ( Parent_Id int, Child_Id int PRIMARY KEY (Parent_Id, Child_Id)) This would allow a lot of flexability in the parenting, you could have (A:B), (A:C), (B:D), (B:E), (B:F), (E:G) and if you wanted to get a little insane, (A:D), etc. --- On Wed, 6/3/09, Jay A. Kreibich wrote: From: Jay A. Kreibich Subject: Re: [sqlite] Db design question (so. like a tree) To: "General Discussion of SQLite Database" Date: Wednesday, June 3, 2009, 12:36 PM On Wed, Jun 03, 2009 at 12:25:14AM +0200, Jan scratched on the wall: > Hi, > > If you don't want to update, but you do want to query for entire > > subtrees, do give nested sets more consideration. > > But as Jay pointed out: Nested sets only work with one parent. Do they? You can think of nested sets as basically sets of parenthesis. So the tree: A / \ B C / /|\ D E F G Turns into: (A:(B:(D:))(C:(E:)(F:)(G:))) As you can see, quite literally "nested sets" (or "sets of sets"). Each node can have exactly one parent (the containing set) and zero or more (with "more" being > 2) children. In the case of a family tree, you can get around the "one parent" by extracting the table structure out to a detail table, so that the tree table only has "person_id" values that point back to some master "person" table. You can then just build two nested sets: one that represents all fathers and one that represents all mothers. The "father" table will still have daughters, but daughters will always lack any children (in the "father" table). [I think that will work. My morning coffee has just about worn off.] Of course, this cancels out many of the query optimizations that nested sets are good at, since you'll frequently need to combine data from the two trees to get what you want. But it would be possible. The bigger issue is that nested sets assume a perfect tree structure. It has to lead back to a "point." You could, in theory, do a family tree for a single person by turning the table up-side down, but if you're trying to track breeding over a group of animals you need not so much a tree as a scattered mesh that generally trends in one direction. Unless you started out with exactly one male and one female, a nested set isn't going to cut it. I'm also unsure about cross-generational links (Like a son being a half-brother kind of thing) that might happen in lab animals. adjacency lists can deal with all of these quite easily. You can have multiple NULL parents for the "tops" of different sub-trees, and the tree structure is localized to a node and it's parents, meaning the links can go all over the place. As for AVL trees, I'm just confused by that suggestion. AVL trees, like B-trees, Red/Black trees, or just about any kind of balanced tree are designed to hold sorted lists. The whole idea of balanced trees is that the tree structure can rearrange itself at will, just as long as the leaf nodes keep their order and are fast to find. You can't hold a tree structure in a AVL tree since the tree structure is prone to changing if you add or remove leaf nodes. Or am I missing something? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ 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] how can we solve IF EXIST in SQLite
On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood wrote: >you should use the insert or replace statement, >it inserts if the row doesnt exist, if the row >does exists then it updates the row. No, that doesn't fulfil the requirement, because quantity isn't incremented. >--- On Wed, 6/3/09, robinsmathew wrote: > > >From: robinsmathew >Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >To: sqlite-users@sqlite.org >Date: Wednesday, June 3, 2009, 3:15 AM > > > >its showing an error near "if": syntax error > > >Kees Nuyt wrote: >> >> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew >> wrote: >> >>> >>>hey thanx for the reply... u leave the things happening inside.. wat i jus >>>wanna do is i wanna insert a new row to a table >>>the table will be like this >>>stock_id PK product_id FK quantity stock_date >>>1 1000 10 28-05-2009 >>>10001 1001 5 27-05-2009 >>> >>>and wen i insert a new row with values NULL, 1000, 15, 30-05-2009 >>> >>>i dont want want it as a new recorde i jus want to update the first row >coz >>>its also having the same product id i jus want set the quantity = 10+15 >and >>>the date new date that is 30-05-2009 >>>and suppose if i insert row with different product_id it should be >inserted >>>as it is.. >> >> Pseudocode: >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; >> -- >> ( Kees Nuyt -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to change the default values option when build SQLite 3.6.14
Hi All, I would like to build the SQLite 3.6.14 to following the steps as mentioned in the document tar xzf sqlite.tar.gz ;# Unpack the source tree into "sqlite" mkdir bld ;# Build will occur in a sibling directory cd bld ;# Change to the build directory ../sqlite/configure ;# Run the configure script make ;# Run the makefile. make install ;# (Optional) Install the build products That is the build to use the default option but I want to change the of SQLITE_THREADSAFE=1 to SQLITE_THREADSAFE=2. How to change this option at the compiler time. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
I thought about the adjacency lists. The columns would basically look like this I guess: animal_id (PK), animal_id (father), aninmal_id (mother) Since I cant do a loop in sql how could I build a trigger securing, that no child is e.g a father of it's own father (or grand-father and so on)? To achieve that I would have to loop through the whole hierarchy of ancestors. Jay A. Kreibich schrieb: > On Wed, Jun 03, 2009 at 12:25:14AM +0200, Jan scratched on the wall: >> Hi, >>> If you don't want to update, but you do want to query for entire >>> subtrees, do give nested sets more consideration. >> But as Jay pointed out: Nested sets only work with one parent. Do they? > > You can think of nested sets as basically sets of parenthesis. > > So the tree: > >A > / \ > B C > / /|\ >D E F G > > Turns into: > > (A:(B:(D:))(C:(E:)(F:)(G:))) > > As you can see, quite literally "nested sets" (or "sets of sets"). > > Each node can have exactly one parent (the containing set) and zero > or more (with "more" being > 2) children. > > > > In the case of a family tree, you can get around the "one parent" > by extracting the table structure out to a detail table, so that the > tree table only has "person_id" values that point back to some > master "person" table. You can then just build two nested sets: one > that represents all fathers and one that represents all mothers. The > "father" table will still have daughters, but daughters will always > lack any children (in the "father" table). > > [I think that will work. My morning coffee has just about worn off.] > > Of course, this cancels out many of the query optimizations that > nested sets are good at, since you'll frequently need to combine data > from the two trees to get what you want. But it would be possible. > > The bigger issue is that nested sets assume a perfect tree structure. > It has to lead back to a "point." You could, in theory, do a family > tree for a single person by turning the table up-side down, but if > you're trying to track breeding over a group of animals you need not > so much a tree as a scattered mesh that generally trends in one > direction. Unless you started out with exactly one male and one > female, a nested set isn't going to cut it. > > I'm also unsure about cross-generational links (Like a son being a > half-brother kind of thing) that might happen in lab animals. > adjacency lists can deal with all of these quite easily. You can > have multiple NULL parents for the "tops" of different sub-trees, and > the tree structure is localized to a node and it's parents, meaning > the links can go all over the place. > > > > > > As for AVL trees, I'm just confused by that suggestion. AVL trees, > like B-trees, Red/Black trees, or just about any kind of balanced > tree are designed to hold sorted lists. The whole idea of balanced > trees is that the tree structure can rearrange itself at will, just > as long as the leaf nodes keep their order and are fast to find. You > can't hold a tree structure in a AVL tree since the tree structure is > prone to changing if you add or remove leaf nodes. > > Or am I missing something? > >-j > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Add to mailing list
___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Select case when ((Select stock_id from Table where Stock_Id = ?) IS NULL) then insert into Table else update Table end; --- On Wed, 6/3/09, Kees Nuyt wrote: From: Kees Nuyt Subject: Re: [sqlite] how can we solve IF EXIST in SQLite To: sqlite-users@sqlite.org Date: Wednesday, June 3, 2009, 2:15 PM On Wed, 3 Jun 2009 00:42:53 -0700 (PDT), Harold Wood wrote: >you should use the insert or replace statement, >it inserts if the row doesnt exist, if the row >does exists then it updates the row. No, that doesn't fulfil the requirement, because quantity isn't incremented. >--- On Wed, 6/3/09, robinsmathew wrote: > > >From: robinsmathew >Subject: Re: [sqlite] how can we solve IF EXIST in SQLite >To: sqlite-users@sqlite.org >Date: Wednesday, June 3, 2009, 3:15 AM > > > >its showing an error near "if": syntax error > > >Kees Nuyt wrote: >> >> On Tue, 2 Jun 2009 03:36:46 -0700 (PDT), robinsmathew >> wrote: >> >>> >>>hey thanx for the reply... u leave the things happening inside.. wat i jus >>>wanna do is i wanna insert a new row to a table >>>the table will be like this >>>stock_id PK product_id FK quantity stock_date >>>1 1000 10 28-05-2009 >>>10001 1001 5 27-05-2009 >>> >>>and wen i insert a new row with values NULL, 1000, 15, 30-05-2009 >>> >>>i dont want want it as a new recorde i jus want to update the first row >coz >>>its also having the same product id i jus want set the quantity = 10+15 >and >>>the date new date that is 30-05-2009 >>>and suppose if i insert row with different product_id it should be >inserted >>>as it is.. >> >> Pseudocode: >> BEGIN; >> UPDATE stock_tab SET . WHERE stock_id = 1; >> if sqlite_error() >> INSERT INTO stock_tab SET (...) VALUES (...); >> endif >> COMMIT; >> -- >> ( Kees Nuyt -- ( Kees Nuyt ) c[_] ___ 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] Db design question (so. like a tree)
On Wed, Jun 03, 2009 at 10:51:52AM -0700, Harold Wood scratched on the wall: > just curious. why not a table for stricty for the linkages > ? > structure similar to > ? > create table Linkages > ( > ??? Parent_Id int, > ??? Child_Id? int > ??? PRIMARY KEY (Parent_Id, Child_Id)) > ? > This would allow a lot of flexability in the parenting, you could > have (A:B), (A:C), (B:D), (B:E), (B:F), (E:G) and if you > wanted to get a little insane, (A:D), etc. This is essentially a bridge-table that creates a Many-to-Many relationship between nodes, allowing for each node to have an arbitrary number of parents and children. At this point you're representing not so much a tree as any generic directed graph. If, in the context of the OP's situation, we assume each individual animal has exactly one father and one mother we can simplify this design, since we know there should be exactly two Linkages rows with a given Child_Id. Knowing that, we can just roll the two Parent_Id values from those two Linkages rows into the Animal row as Mother_Id and Father_Id. If you needed to represent something more complex, like "We know the Mother is animal #435, but the Father may be either #596 or #854" you could use a bridge table like this to associate multiple Father (or Mother) relationships, possibly with other meta-data about the Linkage, like a probability. This design is very much the next abstraction from a traditional adjacency list. It is more flexible, but it is also more complex. -j > --- On Wed, 6/3/09, Jay A. Kreibich wrote: > > > From: Jay A. Kreibich > Subject: Re: [sqlite] Db design question (so. like a tree) > To: "General Discussion of SQLite Database" > Date: Wednesday, June 3, 2009, 12:36 PM > > > On Wed, Jun 03, 2009 at 12:25:14AM +0200, Jan scratched on the wall: > > Hi, > > > If you don't want to update, but you do want to query for entire > > > subtrees, do give nested sets more consideration. > > > > But as Jay pointed out: Nested sets only work with one parent. Do they? > > ? You can think of nested sets as basically sets of parenthesis. > > ? So the tree: > > ? ? ???A > ? ? ? / \ > ? ???B???C > ? ? /???/|\ > ???D???E F G > > ? Turns into: > > ? ? (A:(B:(D:))(C:(E:)(F:)(G:))) > > ? As you can see, quite literally "nested sets" (or "sets of sets"). > > ? Each node can have exactly one parent (the containing set) and zero > ? or more (with "more" being > 2) children. > > > > ? In the case of a family tree, you can get around the "one parent" > ? by extracting the table structure out to a detail table, so that the > ? tree table only has "person_id" values that point back to some > ? master "person" table.? You can then just build two nested sets: one > ? that represents all fathers and one that represents all mothers.? The > ? "father" table will still have daughters, but daughters will always > ? lack any children (in the "father" table). > ? > ? [I think that will work.? My morning coffee has just about worn off.] > > ? Of course, this cancels out many of the query optimizations that > ? nested sets are good at, since you'll frequently need to combine data > ? from the two trees to get what you want.? But it would be possible. > > ? The bigger issue is that nested sets assume a perfect tree structure. > ? It has to lead back to a "point."? You could, in theory, do a family > ? tree for a single person by turning the table up-side down, but if > ? you're trying to track breeding over a group of animals you need not > ? so much a tree as a scattered mesh that generally trends in one > ? direction.? Unless you started out with exactly one male and one > ? female, a nested set isn't going to cut it. > > ? I'm also unsure about cross-generational links (Like a son being a > ? half-brother kind of thing) that might happen in lab animals. > ? adjacency lists can deal with all of these quite easily.? You can > ? have multiple NULL parents for the "tops" of different sub-trees, and > ? the tree structure is localized to a node and it's parents, meaning > ? the links can go all over the place. > > > > > > ? As for AVL trees, I'm just confused by that suggestion.? AVL trees, > ? like B-trees, Red/Black trees, or just about any kind of balanced > ? tree are designed to hold sorted lists.? The whole idea of balanced > ? trees is that the tree structure can rearrange itself at will, just > ? as long as the leaf nodes keep their order and are fast to find.? You > ? can't hold a tree structure in a AVL tree since the tree structure is > ? prone to changing if you add or remove leaf nodes. > > ? Or am I missing something? > > ???-j > > -- > Jay A. Kreibich < J A Y? @? K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs.? We have > a protractor."???"I'll go home and see if I can scrounge up a ruler > and a piece of string."? --from Anathem by Neal Stephenson > ___ >
Re: [sqlite] How to change the default values option when build SQLite 3.6.14
You can do during configuration: ../sqlite/configure -DSQLITE_THREADSAFE=2 Pavel On Wed, Jun 3, 2009 at 2:27 PM, Joanne Pham wrote: > Hi All, > I would like to build the SQLite 3.6.14 to following the steps as mentioned > in the document > tar xzf sqlite.tar.gz ;# Unpack the source tree into "sqlite" > mkdir bld ;# Build will occur in a sibling directory > cd bld ;# Change to the build directory > ../sqlite/configure ;# Run the configure script > make ;# Run the makefile. > make install ;# (Optional) Install the build products > That is the build to use the default option but I want to change the of > SQLITE_THREADSAFE=1 to SQLITE_THREADSAFE=2. How to change this option at the > compiler time. > Thanks, > JP > > > > ___ > 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] sqlite programmed in C++
if I had to do a library, I would probably choose C++,at least the subset C with classes. I will then export the main function as "C" many libraries are done like that now. for example http://www.ode.org/ is mostly written in C++ but has an interface in C I also use Qt because it has a clean API, I find GTK very difficult to use and understand. I also found this article very interesting: http://unthought.net/c++/c_vs_c++.html so I think there no good or bad choice, probably sqlite made the best choice as this is now embed in so many plateform. I would personally choose C++ if I had to do sqlite in 2009, but I am not not able to do it, and it already exists :-) (I use it every day) Cheers, Sylvain On Wed, Jun 3, 2009 at 5:23 PM, John Stanton wrote: > Indeed. Very good reply. > > To Sylvain, once again: speculating on what went into the minds of the > > developers, when they set out to develop SQLite, they chose the best, > > most concise, most portable, most universally compilable, mother of > > almost all languages. Once they developed something that was free, > > fast and cheap, there was no reason to change. Case closed. > > > > If you thing C++ can do a better job at doing what SQLite does on all > > the variety of platforms that it runs on flawlessly, well, the source > > code is available in public domain -- go ahead and create SQLite++ by > > transcribing each function into the language of your choice. > > > > May the better plan win. > > > > > This is something of a digression but is pertinent. Colleagues who > worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was > disillusioned with C++ and its wide deployment and would encourage > people not to use it unless there were clear advantages. > > In our own company we came to the same conclusion as Dr Hipp and used > ANSI C for our compilers and database software. C can be anything you > want it to be. For example you can ensure portability by incorporating > your own memory management system and tightly manage your use of > foreign libraries. for quality assurance You have access to highly > optimizing compilers which can produce executables as good as those > written by a skilled Assembler programmer. > ___ > 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] How to change the default values option when build SQLite 3.6.14
Thanks a lot Pavel. JP From: Pavel Ivanov To: General Discussion of SQLite Database Sent: Wednesday, June 3, 2009 12:59:28 PM Subject: Re: [sqlite] How to change the default values option when build SQLite 3.6.14 You can do during configuration: ../sqlite/configure -DSQLITE_THREADSAFE=2 Pavel On Wed, Jun 3, 2009 at 2:27 PM, Joanne Pham wrote: > Hi All, > I would like to build the SQLite 3.6.14 to following the steps as mentioned > in the document > tar xzf sqlite.tar.gz ;# Unpack the source tree into "sqlite" > mkdir bld ;# Build will occur in a sibling directory > cd bld ;# Change to the build directory > ../sqlite/configure ;# Run the configure script > make ;# Run the makefile. > make install ;# (Optional) Install the build products > That is the build to use the default option but I want to change the of > SQLITE_THREADSAFE=1 to SQLITE_THREADSAFE=2. How to change this option at the > compiler time. > Thanks, > JP > > > > ___ > 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] Db design question (so. like a tree)
On Wed, Jun 03, 2009 at 09:11:23PM +0200, Jan scratched on the wall: > I thought about the adjacency lists. The columns would basically look > like this I guess: > > animal_id (PK), animal_id (father), aninmal_id (mother) > > Since I cant do a loop in sql how could I build a trigger securing, that > no child is e.g a father of it's own father (or grand-father and so on)? > To achieve that I would have to loop through the whole hierarchy of > ancestors. You can't with just SQL. This is the whole issue with adjacency lists. Most basic operations, like finding ancestor lists, counting tree depths, finding a list of all children or descendants, etc., require some kind of loop. I suppose you could write some C function that did the verification for your trigger, but most of the basic "tree-like" operations have to be done by the application or some logic level above SQL itself. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema design and/or SELECT construction
Chris and Jay: Thank you both very much for your comments; that solves it. I am not a trained database designer, but I have resources on normalization; I simply neglected to consult them, thinking that this was a SELECT problem, not a design problem. Your input was just what I was hoping for. > The concept of core and other keywords is a bit arbitrary. > What is important (i.e. core) today might not be so tomorrow. > > Parsing comma separated lists in a single attribute is > likely to be a bother. > Generally, I would call this a bad idea. By most people's > thinking it also breaks First Normal Form. > > Besides, you can't do database operations on comma separated lists. > If you need a one-to-many (one pic to many keywords) then do it right > and build an actual one-to-many relationship between two tables. Craig Smith cr...@macscripter.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++ ...
John Stanton wrote: > This is something of a digression but is pertinent. Colleagues who > worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was > disillusioned with C++ and its wide deployment and would encourage > people not to use it unless there were clear advantages. > > In our own company we came to the same conclusion as Dr Hipp and used > ANSI C for our compilers and database software. C can be anything you > want it to be. For example you can ensure portability by incorporating > your own memory management system and tightly manage your use of > foreign libraries. for quality assurance You have access to highly > optimizing compilers which can produce executables as good as those > written by a skilled Assembler programmer. Good points. IIRC, Firebird, once a C based database system (Interbase by Borland), was re-written in C++ by a team of people who simply "liked" C++. The change happened between version 1.x and 2.x I think. The upshot is, there was a fork in the project, but the 1.x code lives on. It seemed to me a lot of effort and I'm not sure what gains they are claiming, because I lost all interest in Firebird when I realized that the project team were about to pour a whole bunch of resources into re-writing it for its own sake, when better returns could have been made on upgrading the existing code. Personally, I like SQLite (and ANS C for that matter) just fine the way they are. Perhaps Sylvain would prefer to have a look at the Firebird 2.x project for a C++ based DBMS should the implementation language be an issue. Cheers, Rob Sciuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting last inserted rowid?
Nuno Lucas writes: > On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: >> Nuno Lucas writes: >>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: Hello, How can I determine the rowid of the last insert if I am accessing the db from different threads? If I understand correctly, last_insert_rowid() won't work reliably in this case. >>> >>> It should work if you do: >>> >>> BEGIN >>> INSERT ... >>> last_insert_rowid() >>> END >> >> That would be very nice. But does "it should work" mean that you know >> that it works (and it is documented and guaranteed)? The above sounds a >> bit uncertain to me... > > It just means I'm too old to assume anything is certain. The Universe > is always conspiring against you ;-) > > What I mean is that if it doesn't work, then you found a bug, most > probably in your own code. Well, now you are in direct contradiction to Igor who says that it does not work: , | >> If all threads share the same connection, it is your responsibility | >> to make "insert then retrieve last rowid" an atomic operation, using | >> thread synchronization mechanism of your choice. Just as with any | >> access to shared data. | > | > Is BEGIN ... COMMIT sufficient for that? | | No. Transaction is also maintained per connection. Starting a | transaction would prevent other connections from making concurrent | changes, but wouldn't block other threads using the same connection. ` Any third opinions or references to documentation? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting last inserted rowid?
On 4/06/2009 8:22 AM, Nikolaus Rath wrote: > Nuno Lucas writes: >> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: >>> Nuno Lucas writes: On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: > Hello, > > How can I determine the rowid of the last insert if I am accessing the > db from different threads? If I understand correctly, > last_insert_rowid() won't work reliably in this case. It should work if you do: BEGIN INSERT ... last_insert_rowid() END >>> That would be very nice. But does "it should work" mean that you know >>> that it works (and it is documented and guaranteed)? The above sounds a >>> bit uncertain to me... Guaranteed? You're expecting a lot. Most software that you pay large sums of money for guarantee not much more than that the version numbers will be monotonically increasing. >> It just means I'm too old to assume anything is certain. The Universe >> is always conspiring against you ;-) >> >> What I mean is that if it doesn't work, then you found a bug, most >> probably in your own code. > > Well, now you are in direct contradiction to Igor who says that it does > not work: > > , > | >> If all threads share the same connection, it is your responsibility > | >> to make "insert then retrieve last rowid" an atomic operation, using > | >> thread synchronization mechanism of your choice. Just as with any > | >> access to shared data. > | > > | > Is BEGIN ... COMMIT sufficient for that? > | > | No. Transaction is also maintained per connection. Starting a > | transaction would prevent other connections from making concurrent > | changes, but wouldn't block other threads using the same connection. > ` > > > Any third opinions or references to documentation? I don't see Igor saying it doesn't work. He says that it is not sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- just as you need to "with any access to shared data" if you have multiple threads per connection. "if it doesn't work, then you found a bug, most probably in your own code" is not "in direct contradiction to Igor" ... I'd call it corroborative of Igor. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi Mathew,
> hi am new to SQLite can anybody please tell me how this query can be
> solved
> in SQLite?
>
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
> prod_batch_code=1000)
>UPDATE stock_tab
>SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
> WHERE
> oduct_batch_code=1000 )
>WHERE prod_batch_code=1000
> ELSE
>INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,
>DATETIME('NOW') );
Your if/then/else structure is a branching procedure. SQL is a
language for manipulating sets and so does not facilitate procedural
branching such as if/then/else or loops.
SQL, being a language dealing with sets, is designed to perform
actions on entire sets or subsets of data.
So, instead of saying "test this, branch here if true, there if
false", you need to instead say "do this to the subset that tests
true, and do that to the subset that tests false".
So, something like this:
begin immediate
;
update Stock_Tab
set Stock_Qty = Stock_Qty +
(select Purchase_Qty from Purchase_Tab where Product_batch_code = 1000)
where Prod_batch_code = 1000
;
insert into Stock_Tab (Stock_ID, Prod_Batch_Code, Stock_Qty, Stock_Date)
values (20009, 1003, 200, datetime('now'))
where not exists (select 1 from Stock_Tab where Prod_Batch_Code = 1000)
;
commit
;
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
SQL does have branching logic.
(SELECT CASE
WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT 1) IS NULL)
THEN
0
ELSE
(SELECT StoreId FROM History WHERE ItemId = NEW.ID AND UnitPrice = (SELECT
MIN(UnitPrice) FROM HISTORY WHERE ItemId = NEW.ID))
END);
i use it in my current project.
you could modify this to meet the goal of insert x or update y.
Woody
--- On Wed, 6/3/09, BareFeet wrote:
From: BareFeet
Subject: Re: [sqlite] how can we solve IF EXIST in SQLite
To: "General Discussion of SQLite Database"
Date: Wednesday, June 3, 2009, 8:29 PM
Hi Mathew,
> hi am new to SQLite can anybody please tell me how this query can be
> solved
> in SQLite?
>
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
> prod_batch_code=1000)
> UPDATE stock_tab
> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
> WHERE
> oduct_batch_code=1000 )
> WHERE prod_batch_code=1000
> ELSE
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,
> DATETIME('NOW') );
Your if/then/else structure is a branching procedure. SQL is a
language for manipulating sets and so does not facilitate procedural
branching such as if/then/else or loops.
SQL, being a language dealing with sets, is designed to perform
actions on entire sets or subsets of data.
So, instead of saying "test this, branch here if true, there if
false", you need to instead say "do this to the subset that tests
true, and do that to the subset that tests false".
So, something like this:
begin immediate
;
update Stock_Tab
set Stock_Qty = Stock_Qty +
(select Purchase_Qty from Purchase_Tab where Product_batch_code = 1000)
where Prod_batch_code = 1000
;
insert into Stock_Tab (Stock_ID, Prod_Batch_Code, Stock_Qty, Stock_Date)
values (20009, 1003, 200, datetime('now'))
where not exists (select 1 from Stock_Tab where Prod_Batch_Code = 1000)
;
commit
;
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml
___
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] Types for strings, non-expert question
Roger Binns wrote: > I assume you are talking about a major release (ie SQLite v4 not 3.7). > Yes, that's what I'm talking about. I would expect v3 and v4 to be maintained in parallel until most users have updated their code to work with v4. Of course users could continue to use v3 indefinitely, but as some point it would go into feature freeze and all new development would take place in the v4 branch. That wouldn't mean that v3 suddenly stops working. This is basically what happen with the transition from v2 to v3. In fact there are still some users happily using v2. I suspect the transition would actually be quite quick, though existing versions of v3 would continue to be used for many years. > > My list of good changes to make are:... > Those are exactly the kinds of changes that I think should be collected and published so they can be vetted by users and the developers before the changes are implemented. I think it could help to avoid future errors like the sqlite_column_text return type issue. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi Harold, > SQL does have branching logic. > > (SELECT CASE > WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT > 1) IS NULL) > THEN > 0 > ELSE > (SELECT StoreId FROM History WHERE ItemId = NEW.ID AND > UnitPrice = (SELECT MIN(UnitPrice) FROM HISTORY WHERE ItemId = > NEW.ID)) > END); > > i use it in my current project. Let me clarify. By "branching logic" I mean branching (eg if/then or loop) to perform an action such as update, insert, delete, create etc. The case/when/then construct is a function, not procedural branching (at least by my definition above). It will return different results depending on the test, but it can't be used to perform different actions based on the test. > you could modify this to meet the goal of insert x or update y. No, that won't work. You can't put an action (such as an update or an insert) inside a case statement. You can only put expressions (including select statements) within a case statement. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
robinsmathew wrote: > hey thanx for the reply... u leave the things happening inside.. wat i jus > wanna do is i wanna insert a new row to a table > the table will be like this > stock_id PKproduct_id FK quantitystock_date > 1 10001028-05-2009 > 10001 1001 527-05-2009 > > and wen i insert a new row with values NULL, 1000, 15,30-05-2009 > > i dont want want it as a new recorde i jus want to update the first row coz > its also having the same product id i jus want set the quantity = 10+15 and > the date new date that is 30-05-2009 > and suppose if i insert row with different product_id it should be inserted > as it is.. > > Martin Engelschalk wrote: > >> Hi, >> >> what language is this? it certainly is not SQL or a "query". >> When I saw this I though "What language is this? It's certainly not English." :-) It seems to me that robinsmathew should investigate the shift key, and the spell check functions in his email client. Is it just me, or do others find jibberish like "wat, jus, wanna, i, wen, etc..." to be very distracting and not the least bit "cool"? Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how can we solve IF EXIST in SQLite
Hi Dennis, > When I saw this I though "What language is this? It's certainly not > English." :-) > > It seems to me that robinsmathew should investigate the shift key, > and the spell check functions in his email client. Agreed. There were also missing characters from the "English" and SQL. A little proof reading goes a long way. > Is it just me, or do others find jibberish like "wat, jus, wanna, i, > wen, etc..." to be very distracting and not the least bit "cool"? Yes, very distracting, and inconsiderate form of communication, especially when asking for help. It takes far less time for one person to punctuate their own text than 200 readers to try to mentally insert punctuation after receiving. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Db design question (so. like a tree)
Jay A. Kreibich wrote: > > You can't with just SQL. This is the whole issue with adjacency lists. > Most basic operations, like finding ancestor lists, counting tree depths, > finding a list of all children or descendants, etc., require some > kind of loop. > > I have posted about the "materialized path" approach to trees in SQL which I have used very successfully. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html for the original post (also see http://www.mail-archive.com/sqlite-users@sqlite.org/msg19507.html for some additional ideas about formatting reports). This is a variation of the adjacency list technique that also stores a path through the tree to each node (hence the name "materialized path" as opposed to a virtual path stored as a sequence of links in the adjacency list). This path is maintained automatically by triggers. Having the paths available turns many common tree queries into pattern matches against the path using like conditions on standard SQL queries. Unless your tees are very large I find this method to be a very good alternative to simple adjacency lists or nested sets. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting last inserted rowid?
John Machin writes: > On 4/06/2009 8:22 AM, Nikolaus Rath wrote: >> Nuno Lucas writes: >>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: Nuno Lucas writes: > On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: >> Hello, >> >> How can I determine the rowid of the last insert if I am accessing the >> db from different threads? If I understand correctly, >> last_insert_rowid() won't work reliably in this case. > It should work if you do: > > BEGIN > INSERT ... > last_insert_rowid() > END That would be very nice. But does "it should work" mean that you know that it works (and it is documented and guaranteed)? The above sounds a bit uncertain to me... > > Guaranteed? You're expecting a lot. Most software that you pay large > sums of money for guarantee not much more than that the version numbers > will be monotonically increasing. I trust you know what I mean. Guaranteed in the sense that the developers try to make sqlite behave in this way and in contrast to "it just happens to work right now, but it might change anytime without warning". >>> It just means I'm too old to assume anything is certain. The Universe >>> is always conspiring against you ;-) >>> >>> What I mean is that if it doesn't work, then you found a bug, most >>> probably in your own code. >> >> Well, now you are in direct contradiction to Igor who says that it does >> not work: >> >> , >> | >> If all threads share the same connection, it is your responsibility >> | >> to make "insert then retrieve last rowid" an atomic operation, using >> | >> thread synchronization mechanism of your choice. Just as with any >> | >> access to shared data. >> | > >> | > Is BEGIN ... COMMIT sufficient for that? >> | >> | No. Transaction is also maintained per connection. Starting a >> | transaction would prevent other connections from making concurrent >> | changes, but wouldn't block other threads using the same connection. >> ` >> >> >> Any third opinions or references to documentation? > > > I don't see Igor saying it doesn't work. He says that it is not > sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- > just as you need to "with any access to shared data" if you have > multiple threads per connection. > > "if it doesn't work, then you found a bug, most probably in your own > code" is not "in direct contradiction to Igor" ... I'd call it > corroborative of Igor. > Now I'm confused. I want to know if it will be sufficient to wrap my last_insert_rowid() call between BEGIN .. and END in order to make it return the rowid that was last inserted by the same thread even if multiple threads are using the same connection (but different cursors). As I understand Nuno, he is saying that this is sufficient. Igor OTOH is saying that it's not sufficient, I need to use additional mechanism. Where am I wrong? -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++
Excellent. I posted a question of development of sqlite in C++ long before. But this discussion benefited me and I think all those who depends upon C++ may have got a good direction. I am using sqlite with my own C++ wrapper since I am using VC++ to develop applications for last 5 years. Thanks, Rajesh Nair. On 6/4/09, Sylvain Pointeau wrote: > if I had to do a library, I would probably choose C++,at least the subset C > with classes. > > I will then export the main function as "C" > > many libraries are done like that now. > for example http://www.ode.org/ is mostly written in C++ but has an > interface in C > > I also use Qt because it has a clean API, > I find GTK very difficult to use and understand. > > I also found this article very interesting: > http://unthought.net/c++/c_vs_c++.html > > so I think there no good or bad choice, > probably sqlite made the best choice as this is now embed in so many > plateform. > > I would personally choose C++ if I had to do sqlite in 2009, > but I am not not able to do it, and it already exists :-) > (I use it every day) > > Cheers, > Sylvain > > > On Wed, Jun 3, 2009 at 5:23 PM, John Stanton wrote: > >> Indeed. Very good reply. >> > To Sylvain, once again: speculating on what went into the minds of the >> > developers, when they set out to develop SQLite, they chose the best, >> > most concise, most portable, most universally compilable, mother of >> > almost all languages. Once they developed something that was free, >> > fast and cheap, there was no reason to change. Case closed. >> > >> > If you thing C++ can do a better job at doing what SQLite does on all >> > the variety of platforms that it runs on flawlessly, well, the source >> > code is available in public domain -- go ahead and create SQLite++ by >> > transcribing each function into the language of your choice. >> > >> > May the better plan win. >> > >> > >> This is something of a digression but is pertinent. Colleagues who >> worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was >> disillusioned with C++ and its wide deployment and would encourage >> people not to use it unless there were clear advantages. >> >> In our own company we came to the same conclusion as Dr Hipp and used >> ANSI C for our compilers and database software. C can be anything you >> want it to be. For example you can ensure portability by incorporating >> your own memory management system and tightly manage your use of >> foreign libraries. for quality assurance You have access to highly >> optimizing compilers which can produce executables as good as those >> written by a skilled Assembler programmer. >> ___ >> 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 > -- Regards Rajesh Nair ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting last inserted rowid?
On 4/06/2009 12:57 PM, Nikolaus Rath wrote: > John Machin writes: >> On 4/06/2009 8:22 AM, Nikolaus Rath wrote: >>> Nuno Lucas writes: On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: > Nuno Lucas writes: >> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: >>> Hello, >>> >>> How can I determine the rowid of the last insert if I am accessing the >>> db from different threads? If I understand correctly, >>> last_insert_rowid() won't work reliably in this case. >> It should work if you do: >> >> BEGIN >> INSERT ... >> last_insert_rowid() >> END > That would be very nice. But does "it should work" mean that you know > that it works (and it is documented and guaranteed)? The above sounds a > bit uncertain to me... >> Guaranteed? You're expecting a lot. Most software that you pay large >> sums of money for guarantee not much more than that the version numbers >> will be monotonically increasing. > > I trust you know what I mean. Guaranteed in the sense that the > developers try to make sqlite behave in this way and in contrast to "it > just happens to work right now, but it might change anytime without > warning". "guarantee X" means "try to ensure X"?? You trust that I know that you mean that > It just means I'm too old to assume anything is certain. The Universe is always conspiring against you ;-) What I mean is that if it doesn't work, then you found a bug, most probably in your own code. >>> Well, now you are in direct contradiction to Igor who says that it does >>> not work: >>> >>> , >>> | >> If all threads share the same connection, it is your responsibility >>> | >> to make "insert then retrieve last rowid" an atomic operation, using >>> | >> thread synchronization mechanism of your choice. Just as with any >>> | >> access to shared data. >>> | > >>> | > Is BEGIN ... COMMIT sufficient for that? >>> | >>> | No. Transaction is also maintained per connection. Starting a >>> | transaction would prevent other connections from making concurrent >>> | changes, but wouldn't block other threads using the same connection. >>> ` >>> >>> >>> Any third opinions or references to documentation? >> >> I don't see Igor saying it doesn't work. He says that it is not >> sufficient; *YOU* must maintain atomicity, using mutexes or whatever -- >> just as you need to "with any access to shared data" if you have >> multiple threads per connection. >> >> "if it doesn't work, then you found a bug, most probably in your own >> code" is not "in direct contradiction to Igor" ... I'd call it >> corroborative of Igor. >> > > Now I'm confused. I want to know if it will be sufficient to wrap my > last_insert_rowid() call between BEGIN .. and END in order to make it > return the rowid that was last inserted by the same thread even if > multiple threads are using the same connection (but different cursors). > > As I understand Nuno, he is saying that this is sufficient. Igor OTOH is > saying that it's not sufficient, I need to use additional mechanism. As Igor pointed out, if you have multiple threads using the same connection, you ALREADY need mutexes or whatever to maintain atomicity. If you don't have that, yes you need to "use additional mechanism" BUT this constitutes an EXISTING bug in your code. Perhaps Nuno should have added a rider like "(presuming your existing code is not stuffed)". > Where am I wrong? In wasting time on semantic confusion instead of implementing it and testing the bejaysus out of it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite programmed in C++ ...
Hi, I also like C very much even if I prefer C++ Please note that I was asking to introduce C++ not to re-write (which would be a total non-sense) I just repeat that I use sqlite every days, and I find it awesome, I love it. C or C++? at the end : who cares? I strongly believe that the persons are essential, more than the programming language Cheers, Sylvain On Wed, Jun 3, 2009 at 11:53 PM, Rob Sciuk wrote: > > John Stanton wrote: > > This is something of a digression but is pertinent. Colleagues who > > worked with Bjarne Thorstrup (inventer of C++) tell me that Bjarne was > > disillusioned with C++ and its wide deployment and would encourage > > people not to use it unless there were clear advantages. > > > > In our own company we came to the same conclusion as Dr Hipp and used > > ANSI C for our compilers and database software. C can be anything you > > want it to be. For example you can ensure portability by incorporating > > your own memory management system and tightly manage your use of > > foreign libraries. for quality assurance You have access to highly > > optimizing compilers which can produce executables as good as those > > written by a skilled Assembler programmer. > > Good points. > > IIRC, Firebird, once a C based database system (Interbase by Borland), was > re-written in C++ by a team of people who simply "liked" C++. The change > happened between version 1.x and 2.x I think. The upshot is, there was a > fork in the project, but the 1.x code lives on. It seemed to me a lot of > effort and I'm not sure what gains they are claiming, because I lost all > interest in Firebird when I realized that the project team were about to > pour a whole bunch of resources into re-writing it for its own sake, when > better returns could have been made on upgrading the existing code. > > Personally, I like SQLite (and ANS C for that matter) just fine the way > they > are. Perhaps Sylvain would prefer to have a look at the Firebird 2.x > project > for a C++ based DBMS should the implementation language be an issue. > > Cheers, > Rob Sciuk > ___ > 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] Getting last inserted rowid?
If you are inserting in order then selecting the max value from an indexed column should work. Harold Wood -Original Message- From: Nikolaus Rath Sent: Wednesday, June 03, 2009 3:22 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Getting last inserted rowid? Nuno Lucas writes: > On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: >> Nuno Lucas writes: >>> On Tue, May 26, 2009 at 5:17 PM, Nikolaus Rath wrote: Hello, How can I determine the rowid of the last insert if I am accessing the db from different threads? If I understand correctly, last_insert_rowid() won't work reliably in this case. >>> >>> It should work if you do: >>> >>> BEGIN >>> INSERT ... >>> last_insert_rowid() >>> END >> >> That would be very nice. But does "it should work" mean that you know >> that it works (and it is documented and guaranteed)? The above sounds a >> bit uncertain to me... > > It just means I'm too old to assume anything is certain. The Universe > is always conspiring against you ;-) > > What I mean is that if it doesn't work, then you found a bug, most > probably in your own code. Well, now you are in direct contradiction to Igor who says that it does not work: , | >> If all threads share the same connection, it is your responsibility | >> to make "insert then retrieve last rowid" an atomic operation, using | >> thread synchronization mechanism of your choice. Just as with any | >> access to shared data. | > | > Is BEGIN ... COMMIT sufficient for that? | | No. Transaction is also maintained per connection. Starting a | transaction would prevent other connections from making concurrent | changes, but wouldn't block other threads using the same connection. ` Any third opinions or references to documentation? Best, -Nikolaus -- »Time flies like an arrow, fruit flies like a Banana.« PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C ___ 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] how can we solve IF EXIST in SQLite
Hmm, I have a view, its strictly a bunch of bit columns. Default value is 0=false, this view has a huge trigger on it. I use the different columns to activate particular sections of the trigger code, within those I do inserts, deletes, updates etc. It was a design around not having stored procedures. Harold Wood & Meyuni Gani -Original Message- From: BareFeet Sent: Wednesday, June 03, 2009 6:21 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] how can we solve IF EXIST in SQLite Hi Harold, > SQL does have branching logic. > > (SELECT CASE > WHEN ((SELECT StoreId From History WHERE ItemId = NEW.ID LIMIT > 1) IS NULL) > THEN > 0 > ELSE > (SELECT StoreId FROM History WHERE ItemId = NEW.ID AND > UnitPrice = (SELECT MIN(UnitPrice) FROM HISTORY WHERE ItemId = > NEW.ID)) > END); > > i use it in my current project. Let me clarify. By "branching logic" I mean branching (eg if/then or loop) to perform an action such as update, insert, delete, create etc. The case/when/then construct is a function, not procedural branching (at least by my definition above). It will return different results depending on the test, but it can't be used to perform different actions based on the test. > you could modify this to meet the goal of insert x or update y. No, that won't work. You can't put an action (such as an update or an insert) inside a case statement. You can only put expressions (including select statements) within a case statement. Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ 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] Perticular Field encription in sqlite3 database
On Tue, Jun 02, 2009 at 11:04:05PM -0700, Roger Binns scratched on the wall: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Sambasivarao Vemula wrote: > > I want to encrypt a perticular field in sqlite3 database. > > You can use the SQLite Encryption Extension which will encrypt the whole > database. This is by far the easiest approach and you won't have to > worry about accidentally encrypting some fields and not others. After > all if you are storing some valuable information, how do you know the > rest won't be valuable either? > > http://www.hwaci.com/sw/sqlite/see.html > > > I want encrypt a password field in a table > Encrypting the password is a very poor approach to password security. Depends on what you're after. There are good reasons to store passwords in a recoverable format. > Best practise is to store a one way hash of the password. A one way > hash is a checksum of the data that is computationally infeasible to > recover the original data from. Two example hashes are md5 and sha1. But, as you pointed out later in your message, in today's world of multi-GHz, multi-core machines, the hash of something as small and simple as a password is brute-force crackable by most people in a reasonable time period (a few months). This is something a stronger hash cannot protect against... the password search space just isn't that large. So in today's world you pretty much have to assume that if someone has your password database-- no matter how it is encoded (encryption or hash)-- you're screwed and you've already lost. The big disadvantage of hash systems is that the during the authentication process you need to get the password from the user to the hash database in a recoverable format, which is more or less a fancy way of saying "in the clear." It might be hidden inside a encrypted channel of some sort, but the password must be recoverable. And that's the whole problem: If you store passwords using some kind of one-way transformation, you need to transmit them in a recoverable format. This allows the hash to be done on the database side and a comparison done. The hash must be done on the database side or you're open to replay attacks. If you want to transmit the passwords in a non-recoverable format (e.g. after a one-way transformation such as a hash) you need to store the passwords in a recoverable format. The hash can be done client side using a random server provided salt (important), but the database has to have an original password to generate a comparison hash. It is very much a "Damned if you do, Damned if you don't" kind of thing. In general I'd go with a hashed database and in-the-clear transmission, but only if I'm operating in an HTTPS or some similar encrypted channel (where someone else wrote the channel encryption!). Hashing on the client side assumes we "own" the client, but also lets a cracker run test values through the hash algorithm. If they sniff a hash and know our hash algorithm, they can off-line brute force the system. So, ideally, even hashed passwords need to be encrypted during transport. That said, getting anywhere with a "transmit hashed, store recoverable" system it is a fair amount of work, and for a simple system that is otherwise unencrypted, it is a fair approach. Clearly a "transmit recoverable, store hashed" system has a huge dependency on a solid channel encryption. Without an encrypted transport you're technically open either way. The question is mostly how much you want to make them work for it, and how secure you think you can keep your server. The only way you can keep a password unrecoverable all the time-- both in the database as well as during transmission-- is using a public key/private key system. That takes some serious understanding and is very easy to get wrong, even if you're using a library for an existing algorithm. As you said, security is very *very* hard. Many people have a hard time thinking like an attacker and it only takes one minor screw-up in the code or design for everything to fall apart. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to compose the sql sentence?
Thank you a lot! I created a table: CREATE TABLE data ( num INTEGER, di CHAR(4), data CHAR(12), time1 INTEGER, time2 INTEGER, format CHAR(1) ); and create a index: CREATE INDEX i_data ON data (num, di, time1); I want to do: first tell whether there is a record in the table "data" according to the index "i_data". to update the record if there is a record; to insert the record if there isn't any record. when I run the sql: INSERT OR REPLACE INTO data (num, di, time1) VALUES (12, '1290', '732e4a39', 8323000, 8323255, 22); the sqlite3 report a error: SQL error: 6 values for 3 columns Does It mean the method isn't the right way? Simon Slavin-2 wrote: > > > On 3 Jun 2009, at 7:05am, liubin liu wrote: > >> the first step is to tell if there is the data in the table. >> if the answer is not, I want to insert a row of data into the table >> if the answer is yes, I need to update the row of data acccording to >> the >> data inputting from me. > > INSERT OR REPLACE INTO table (columns) VALUES (values) > > This will use the columns and indices you have already defined as > UNIQUE to decide whether it should INSERT a new row or REPLACE an > existing one. So take care in creating UNIQUE columns or a UNIQUE > index that does what you want. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/how-to-compose-the-sqlite---if-exists-%28select-...%29---update-...--else---insert-into...-tp23845882p23864558.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
