Re: [sqlite] updating SQLite to implement The Third Manifesto
Darren Duncan wrote: >> I never understood that restriction. I read in the books: "since we >> have defined things this ways from a formal point of view there's no >> room for NULL". And my question is well, why don't you change the >> definitions to augment the datatype sets with a special constant NULL >> which is by definition not present in any datatype? Wouldn't that >> give an analogous theory more aligned with real world? > > If you want to have a data type which can represent only a single > value and use it to mean unknown, and all instances of that value are > equal, then that would be fine. I think you've missed the concept of NULL. You seem to be thinking of the NULL pointer as used in most programming languages. They use a constant to indicate an empty pointer and call it NULL. A NULL is NOT a specific value, it's the absence of value or information. Since you don't know what a particular NULL is, it can be anything. > > The main problem with NULL is more how it is used in SQL than the idea > itself. > > For one thing, SQL's NULL violates the logical principle that after > you say "set foo to bar, then foo equals bar". With every normal data > type, if "foo := 1; bar := foo;" then a subsequent comparison of "foo > = bar" would return true. But with nulls, if you say "foo := NULL; > bar:= foo", then a subsequent comparison of "foo = bar" does not > return true. > > More simply, with nulls, saying "foo = foo" will not return true, > which flies in the face of common sense. > > All sorts of other problems in SQL result from that basic situation, > that no NULL value ever equals itself. Given what I've said about NULL being unknown, it follows that NULL!=NULL which is why each NULL is unique in a unique index and you can only test for it with isnull. The only problems I've had with NULL have stemmed from my programmers view of NULL. > > But its worse than that, in that SQL isn't even consistent with itself > in how it treats nulls. With some kinds of operations or queries, it > treats every null being unique, and in other situations it treats them > all as being equal. No normal data type has this problem. What situations treat them all as being equal? Off hand I can't think of any, but then my SQL experience isn't that broad. signature.asc Description: OpenPGP digital signature
Re: [sqlite] Plugin help requested
cstrader wrote: > I'm trying create a .dll. The .dll compiles and looks fine and the code runs > fine in console mode, but the host cannot read the .dll when the sqlite3_open > line below is uncommented. Any ideas why would be appreciated. thanks! > > #include > #include > > #define _SQLITE3_H_ extern "C" __declspec(dllexport) > > #define PLUGINAPI2 extern "C" __declspec(dllexport) The problem is that you've declared the symbol for export. Just remove the __declspec part and it should work.
Re: [sqlite] Triggers and TEMP tables: ticket #1689
[EMAIL PROTECTED] wrote: > A user complains in ticket #1689 that SQLite does not > allow triggers in one database to refer to tables in > a different database or to TEMP tables. ... > Should I remove the tests from SQLite that prevent > triggers in one database from referring to tables in > a different database? Or should I leave things as > they are and close ticket #1689 with a remark of > "works as designed". I don't really see any reason to do it. When I use attach, I create a function to open the main db and attach any extra db's as well as add any temp triggers I may need. I couldn't find anything in the ticket to indicate that the user couldn't use this method. After all, there's nothing to prevent someone from dropping the trigger, altering the db, the re-creating the trigger if a persistent version were allowed. signature.asc Description: OpenPGP digital signature
Re: [sqlite] Multi-threading.
Mrs. Brisby wrote: Now, if you're feeling like you're on a high horse, go ahead: point out a single common platform where threads are the necessity. Here's some hints: * it's not windows * it's not unix * it's not java (closures) * it's not lisp (closures) * it's not forth (coroutines) * it's not macos (see unix) * it's not gtk+ (closures/event pump) Argh... gtk+ uses threads on windows. The g_io_channel async api is implemented this way. This is done because windows has no async file io api. There may be other places/platforms where glib/gtk+ uses threads. signature.asc Description: OpenPGP digital signature
Re: [sqlite] Query locking up SQLite
Try using UNION instead of OR. Several posts have mentioned that OR can dissable the use of an index. You're also right about table order being important, there's information on the wiki about this. I would guess that the best order is table3, table5, table4, table2, table1 since you're actually searching on tables 3&5 and everything else follows from them. David Fowler wrote: I don't think the LIKE is the problem, I used = there too. All the id columns a primary keys, so I assume that means they're indexed well enough. Update on 5 table query: I can now do the query with INNER JOINs, and it returns instantly with the correct results. The problem appears to be the order of the tables in the JOINs. The first table is large (4000 odd rows) and it has absolutely no conditions on it (the second doesn't help either). What I should have done was have the tables that result in not many rows first, then add others as I go. signature.asc Description: OpenPGP digital signature
[sqlite] Question about temp table performance
I have two versions of the same algorithm. The first operates directly on the main db table. The second operates on a temp table containing only the working set. The problem is that the second version is about 20x slower, 1.5 sec versus 30 sec. If the EXISTS line in the second version is commented out the execution time drops to 9 sec. Any ideas? Version 1: sq_res=sqlite3_prepare(db,"UPDATE group_article SET parent=null WHERE group_id=?;",-1,&stmt,NULL); sq_res=sqlite3_prepare(db,"UPDATE group_article SET parent=" "( SELECT article.id FROM refs ,article " "WHERE refs.article_id=group_article.article_id " "AND reference=hash " "AND EXISTS (SELECT id FROM group_article WHERE group_id=?1 AND article_id=article.id) " "ORDER BY refs.id DESC LIMIT 1 ) " "WHERE group_id=?1;",-1,&stmt,NULL); Version 2: sq_res=sqlite3_prepare(db,"CREATE TEMP TABLE thrd(aid UNIQUE, parent);",-1,&stmt,NULL); sq_res=sqlite3_prepare(db,"INSERT INTO thrd(aid) SELECT article_id FROM group_article " "WHERE group_id=?;", -1, &stmt, NULL); sq_res=sqlite3_prepare(db,"UPDATE thrd SET parent=" "( SELECT article.id FROM refs ,article " "WHERE refs.article_id=thrd.aid " "AND reference=hash " "AND EXISTS (SELECT aid FROM thrd WHERE aid=article.id) " "ORDER BY refs.id DESC LIMIT 1 ) " ";",-1,&stmt,NULL); sq_res=sqlite3_prepare(db,"UPDATE group_article SET parent=" "( SELECT parent FROM thrd " "WHERE aid=article_id ) " "WHERE group_id=? ;", -1, &stmt, NULL); signature.asc Description: OpenPGP digital signature
