Re: [sqlite] Cloudscape?
Andrew Piskorski wrote: Anyone know much of anything about IBM's Cloudscape database? Advantages or disadvantages vs. SQLite? A template wiki page is at http://www.sqlite.org/cvstrac/wiki?p=SqliteVersusDerby Please correct and or supplement this page as you are able. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] LEFT JOIN doesn't work as expected
At 8:12 PM +0200 8/3/04, Rolf Schaeuble wrote: SELECT object.object_id, properties.value_table, string_values.value, int_values.value FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON (properties.value_table = 1) AND (properties.property_id = string_values.property_id) LEFT OUTER JOIN int_values ON (properties.value_table = 2) AND (properties.property_id = int_values.property_id) ; Your original query looks mal-formed. Join clauses are meant to be used only for saying what the related columns are, and not any filtering conditions. You do filtering in a WHERE clause. Like this: SELECT object.object_id, properties.value_table, string_values.value, int_values.value FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON properties.property_id = string_values.property_id LEFT OUTER JOIN int_values ON properties.property_id = int_values.property_id WHERE (properties.table_value = 1 AND string_values.property_id IS NOT NULL) OR (properties.table_value = 2 AND int_values.property_id IS NOT NULL) ; Even then, I think that your value_table field serves no purpose at all, and you can just do this, assuming that string_values and int_values records will not exist when they shouldn't: SELECT object.object_id, string_values.value, int_values.value FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON properties.property_id = string_values.property_id LEFT OUTER JOIN int_values ON properties.property_id = int_values.property_id ; Moreover, the above query assumes you can have objects without any properties (which is valid). However, if all objects must have at least one property, then you can remove the object table from the query, as it doesn't add anything then. -- Darren Duncan
[sqlite] Re: - [sqlite] Cloudscape?
Windows download is 628.1MB. There goes your hard drive. 'Nuff said really. rayB |-+> | | Andrew Piskorski | | | <[EMAIL PROTECTED]| | | m> | | || | | 04/08/2004 06:26 | | | Please respond to| | | sqlite-users | | || |-+> >--| | | | To: [EMAIL PROTECTED] | | cc: | | Subject: - [sqlite] Cloudscape? | >--| Anyone know much of anything about IBM's Cloudscape database? Advantages or disadvantages vs. SQLite? http://www-306.ibm.com/software/data/cloudscape/ http://www.zdnet.com.au/news/software/0,261733,39155170,00.htm I hadn't heard of it before, so I'm curious. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/ *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
[sqlite] Cloudscape?
Anyone know much of anything about IBM's Cloudscape database? Advantages or disadvantages vs. SQLite? http://www-306.ibm.com/software/data/cloudscape/ http://www.zdnet.com.au/news/software/0,261733,39155170,00.htm I hadn't heard of it before, so I'm curious. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] LEMON Examples
I'll second sporkey, would you be able to post your calc source for us all to see? TIA, -j On Tue, 03 Aug 2004 15:38:53 -0400, John Cohen <[EMAIL PROTECTED]> wrote: > Hi all, > > Thanks for all the help! I got it all working fine.. The wrapper did the > trick - I also want to try some speed tests with the others, and see which > is the fastest (which I doubt there is a measurable difference in them, but > its always worth the effort). > > Thanks again, > John > > _ > Don't just search. Find. Check out the new MSN Search! > http://search.msn.click-url.com/go/onm00200636ave/direct/01/ > >
[sqlite] LEFT JOIN doesn't work as expected
Hello, today I've stumbled over a problem that I can't seem to find an answer for. I have the following tables: CREATE TABLE object ( object_id INTEGER PRIMARY KEY ); CREATE TABLE properties ( object_id INTEGER, property_id INTEGER, value_table INTEGER ); CREATE TABLE string_values ( property_id INTEGER PRIMARY KEY, value VARCHAR(255) ); CREATE TABLE int_values ( property_id INTEGER PRIMARY KEY, value INTEGER ); In English: I have a list of objects. Each object can have 0..n properties. A property is either a string or an integer (and later maybe ever more different data types). In the 'properties' table, the 'value_table' field indicates in which table the value of this property is stored: if it's 1, the value is stored in 'string_values'; if it's 2, the value is stored in 'int_values'. Please note that I have left out the indexes to keep it simple; just assume that I have an index on each field that I use in WHERE and JOIN-ON statements. The follwing SQL statements populate the database with an object, an int-property and a string-property: -- Insert one object INSERT INTO object VALUES (1); -- Insert one string property INSERT INTO properties VALUES (1, 100, 1); INSERT INTO string_values VALUES (100, "Hallo"); -- Insert one int property INSERT INTO properties VALUES (1, 100, 2); INSERT INTO int_values VALUES (100, 123); As you can see, the same values can be used in 'string_values.value' and 'int_values.value'. This means, that only the combination of 'value_table'/'property_id' uniquely identifies a property value. When loading the objects into memory, I query for all properties of all objects. For this I use the following select statement: SELECT object.object_id, properties.value_table, string_values.value, int_values.value FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON (properties.value_table = 1) AND (properties.property_id = string_values.property_id) LEFT OUTER JOIN int_values ON (properties.value_table = 2) AND (properties.property_id = int_values.property_id) ; The format of the result should look like this: +---+-+-+--+ | object_id | value_table | string_values.value | int_values.value | +---+-+-+--+ Using the SQL statement from above, the result I would like to get is this: +---+-+-+--+ | object_id | value_table | string_values.value | int_values.value | +---+-+-+--+ | 1 | 1 | Hallo | NULL | | 1 | 2 | NULL | 123 | +---+-+-+--+ This works well with MySQL (which is where I first developed the select statement). When using it with SQLite, however, I get the following result: +---+-+-+--+ | object_id | value_table | string_values.value | int_values.value | +---+-+-+--+ | 1 |NULL | NULL | NULL | +---+-+-+--+ I'm by no means a SQL expert (I've basically just started to play around with it), but my theory is this: When MySQL processes the query, it performs the join from left to right. First, it finds all objects. For each object, it finds all properties. For each property, - it finds all 'string values' - it finds all 'int values' This would match the result of the query. But it looks like SQLite performs the query in another order, which results in the NULL results. I could find a select statement that produces the desired result in SQLite: SELECT object.object_id, properties.value_table, string_values.value, int_values.value FROM object LEFT OUTER JOIN properties ON object.object_id = properties.object_id LEFT OUTER JOIN string_values ON ( (properties.value_table = 1) AND (properties.property_id = string_values.property_id) ) OR (properties.value_table IS NULL) LEFT OUTER JOIN int_values ON ( (properties.value_table = 2) AND (properties.property_id = int_values.property_id) ) OR (properties.value_table IS NULL) ; However, this query is *very* slow. For a single object with a few properties, this doesn't matter. When using a database with 100.000 entries, each having a few properties, the second select is several orders of magnitude slower than the first one, since it can't make full
Re: [sqlite] force unlocking?
Hello Ara, On 3 ago 2004, at 10:09, Ara.T.Howard wrote: regarding atomic creation of lockfiles: this can be done using link(2). i have a library and command line tool for doing so, but have never tested it on a mac. it can be found at http://raa.ruby-lang.org/project/lockfile/ I have checked the link(2)'s man page on my Mac and it states that it's an atomic operation. Thanks Ara, -- Tito smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] LEMON Examples
D. Richard Hipp wrote:
John Cohen wrote:
The last suggestion worked great (thanks!). But still, I still have a
small problem. It won't accept more than one 'statement'. I know
why, but can't fix it. Take a look:
$ ./a.out
7 + 9 + 7 + 3 / (5 + 7);
23.25
3 + 3 + 3;
Fatal Error: Parser is hopelessly lost...
Now, I know why, because my grammar look like this:
in ::= stmt ENDLINE.
stmt(A) ::= expression. { printf("%u\n", a); }
expression(A) ::= INTEGER(B). { A = B; }
expression(A) ::= expression(B) PPLUS expression(C). { A = B + C; }
expression(A) ::= expression(B) PSUB expression(C). { A = B - C; }
...etc...
Now its obvious why that will only accept one statement, but how to
fix it? SQLite doesn't offer any help here because the parser seems
to be reinitialized each statement, but that really isn't an option in
my case. Is there any way? In bison, you can simply make the start
token right recursive (like in my last message):
in ::= in stmt ENDLINE.
There's a bug in lemon such that it doesn't like the primary
non-terminal to be recursive. So do this:
main ::= in.
Then in addition to what you have it should work.
Correction: There *used* to be this bug in lemon. I think
it is now fixed. The problem above is that there is no
initial case for the in term. You need this:
in ::= .
in ::= in stmt ENDLINE.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Analytic commands
I wonder, has anyone experimented with implementing OLAP-style extensions for SQLite? E.g. ROLLUP, CUBE and the various functions operating on query-results-in-sequence. Iain MacKay
Re: [sqlite] force unlocking?
On Mon, 2 Aug 2004, Tito Ciuro wrote: Hello Ara, On 2 ago 2004, at 9:59, Ara.T.Howard wrote: the simplest way to do this is to create a file for each db, say db.lock, and to apply a blocking read/write to this file depending on the intent of your operation. the contents of this file are not important - it is merely used as a mutex that processes can coordinate access to the database with. the key is to ask for the locks in a blocking fashion - this should ensure that only one process is accessing the database at a given moment and prevent sqlite from ever needing to return 'database is locked'. A word of caution to Mac OS X SQLiters. The following statement applies to Jaguar (Mac OS X 10.2) and earlier. I'm not sure it it's still the case with Panther (10.3) and Tiger (10.4) though: Creating a file is not an atomic operation and may produce a race condition if two or more processes attempt to create such lock file at the same time, on the same place. The solution in this case is to create a directory instead. Using this route, there won't be race conditions. There is a small performance hit, but considering that a file lock may cause you trouble, I would think that it's better to bite the bullet and go for the safest solution. Again, I'm not sure this applies to Panther or Tiger. Regards, -- Tito hello tito- i'm speaking of using a pre-existing file and appying an fcntl based lock to this file, not creating a lockfile, so it does not matter that the creation is not atomic, for example: ~ > sqlite db 'create table foo(bar)' ~ > touch lock now in code you will do either blocking request for fcntl read lock on 'lock' access db in usual way release fcntl lock or blocking fcntl request for write lock on 'lock' access db in usual way release fcntl lock the lock requests must use the F_SETLKW flag - so they are blocking procedures. if all processes accessing the db using this protocol then you can be guarenteed to never get a 'database is locked' error because you'll only access the database (which applies it's own fcntl locks to the db in non-blocking fashion) after getting a valid lock. it's critical to use fcntl based locks because only they work on NFS and it's critical to use another file because a blocking lock applied to the db would be superceeded by the first fcntl lock the sqlite api requested. regarding atomic creation of lockfiles: this can be done using link(2). i have a library and command line tool for doing so, but have never tested it on a mac. it can be found at http://raa.ruby-lang.org/project/lockfile/ cheers. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] in memory databases
Hi
I am trying to move to memory database from file based sqlite db, I want my memory
database to be
shared across various threads in my process.
How do I do it?
>From the Wiki documentation, I found the following
* (defvar db2 (sql:connect '(":memory:")
:database-type :sqlite
:make-default nil
:if-exists :old))
+ Will this "if-this:old" help me do this?
+ Is it supported in V3.x?
+ I read in the documentation that "Copy" command will no longer be supported (V3.0
onwards), then
how do we get to prepare the memory db, any ideas/suggestions for doing that?
+ Is it possible to have multiple processes running on the same machine with separate
memory
database, while the threads inside a process share the same memory database?
Can somebody give me a c++ syntax for doing this...or point me to a document that
explains more on
this?
Thanks & Regards
Rajesh
__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail
Re: [sqlite] LEMON Examples
On Tue, 2 Aug 2004, John Cohen wrote: > >in ::= in stmt ENDLINE. > >But that doesn't seem to work in lemon. I've also tried making the 2nd >statement right recursive, but that doesn't work either because it doesn't >seem to ever reduce all the way. > >Any ideas? Much thanks to all. Just make the start token a simple wrapper for a right recursive token to allow multiple lines. in ::= stmt_list . stmt_list ::= stmt_list stmt ENDLINE . stmt_list ::= stmt ENDLINE . That should do it. > >John > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
