Re: [sqlite] Multi-threading.
Mrs. Brisby wrote: chances are you can't use threads correctly either. This mailing list is an excellent example of how many "professional programmers" simply can't deal with threads- every problem they run into, it's "how do I make sqlite work with threads". If you have to ask that question, you simply have no idea what you're doing. I wasn't going to drag this thread on any longer, but just in case this view is popular... SQLite is a great library, but calling it multi-threaded is stretching the common definition of the term. I consider SQLite multi-thread "tolerant" :) It detects and allows you to deal with potential threading related issues as opposed to dealing with those issues for the developer ( real thread "support" in my view ). Given the good doctor's stance on threads, I am grateful for what thread tolerance is in the library :) We get all those threads related questions because SQLite is difficult to use with threads and has sparse sometimes conflicting documentation on the subject. Don't get me wrong, again I am not complaining, it is *way* better than the other options out there. But you can't expect someone to magically figure out an API based on one or two sentence definitions and comments in a header file without asking a few questions and making a few mistakes. Regards, Kervin
Re: [sqlite] ORDER BY question
I tried that but got: "no such collation sequence: NUMERIC" "Dan Kennedy" <[EMAIL PROTECTED]To: sqlite-users@sqlite.org o.com> cc: Subject: Re: [sqlite] ORDER BY question 07/26/2005 02:48 PM Please respond to sqlite-users --- [EMAIL PROTECTED] wrote: > > Hello, > > I'm using sqlite3 and have a list of numbers I'd like returned sorted in > increasing order: > 1 1.1 1.10 1.11 1.9 10 11 9 12 > > However, if their column is defined as numeric, the 1.10 gets converted to > 1.1 (which is not what I want) and the result (using ORDER BY) is: > 1 1.1 1.1 1.11 1.9 9 10 11 12 > > I tried defining the column as a text and while the 1.10 is preserved, but > I get dictionary-style results: > 1 1.1 1.10 1.11 1.9 10 11 12 9 You could define the column as text and use "ORDER BY COLLATE NUMERIC". Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer
Re: [sqlite] Multi-threading.
On Mon, 2005-07-25 at 09:00 +0100, Ben Clewett wrote: > I like what you say about computer science being a Science. This is > exactly my point. A science is a collection of theories which hold true > within a domain until otherwise dissproven. For instance Isac Newtons's > law of gravety and Einstain's law. Both are true within a fixed domain. > Both are used today. Neither are truelly corrrect. There will be > another more complex theroy in time. You have that backwards. They remain theory until proven. Not the other way around. > This is the same with Threading. There may be places where this is > useful. There may be places where it should not be used. Both are > theories within computer science which hold true in a limited domain. > So when I said this was my opinion, I should have stated this is my > theory within a fixed domain. However when you stated that I was wrong, > I don't think this is compatible of your statement that Computer Science > is a Science. Nothing in any science is either right or wrong. That's > a Religion. You have that backwards. Science most certainly has right and wrong. We also find it useful to examine things that aren't known to be right or wrong, but it is that that makes science different than religion. > I don't want to spend ages on the argument, I can see there are > passionate views. I only want to explore the idea that threading is a > viable strategy for a modern high level language and can produce stable > applications with low development time. That's incorrect. Threading increases development time and produces less stable applications. In fairness: it's the skill level of the engineer that does this, but then, they _did_ chose to use a threaded development model. http://www.google.com/search?hl=en&lr=&q=problems+with +multithreading&btnG=Search Maybe you're special. Most people aren't special and as soon as you throw threads into the equation they can't find their ass with both hands. > For instance a call to a TCP blocking Wait. It's perfectly possible for > one thread to cycle round handing GUI events, then checking on the port > for new packets. > > But an alternate method is to partition the code space into two > autonomous threads using their own variable set. Two simple sets of > code which are not coupled, and remain highly cohesive and encapsulated. That's why we have the select() system call. That's why GTK uses glib to encapsulate file descriptors (including those of sockets) so that event programming can allow the GUI events to be processed "immediately" and then handle "other things" without worrying about threads. If you use a language like Java or Javascript (or lisp or perl or etc, etc) you get something called a "closure" - and they make using event-programming-think a real pleasure, because you don't have to worry about locking, or surprises because race conditions simply won't occur. http://www.kegel.com/c10k.html Should be read by anyone thinking they need threads "for performance reasons". > Communicating through a thread-safe object. Such code is fast to > write, easy to read, robust and does the job. I can complete this in c# > in ten minutes, and it will not break. With large amounts of time, > better methods may be available. But this theory is viable within its > domain. This is speculative, and doesn't provide anything useful to anyone reading this thread. > > I wasn't about to consider Windows a modern GUI system :) > > Are you saying it's not modern, or not a GUI? It may not be prefect and > it is very complex. But it's not about to go away, and it's installed > on modern computers. More importantly, my views on threads remain. If > you give the GUI it's own thread, you have implemented a simple solution > to ensure that the correct cursor and mouse events are honoured. Then a > worker thread is free to do what ever it likes without being coupled to > the GUI. Simple robust coding using thread-safe objects. It's a joke, and it's moot anyway. As Richard pointed out, Windows doesn't demand threads from its programmers either. > I am also interested in peoples views that threading is slower. Since > all processors are likely to be hyperthreaded, multicore, or both. I > can see no place where this will be true in the future. Threading is often slower, but if you're worried about the performance drops that _threads_ cause, you wouldn't use C#. Threading is _harder_to_program_. It makes buggier programs. Fewer people can follow and debug it as the program gets larger. And worst of all: people are continually encouraged to use it for exactly the opposite reason. > > Java uses references, not pointers. > > Is there any important difference? My point was not about language. It > was a question to Dr Hipp about what he feels is missing from the Java > pointer (reference) model. Perhaps I should have explained better. I brought this up. Pointers hav
Re: [sqlite] Multi-threading.
On Mon, 2005-07-25 at 09:48 -0500, Jay Sprenkle wrote: > The theory has been proposed that threads aren't better than separate > processes, or application implemented context switching. Does anyone > have an experiment that will prove the point either way? It will have > to be OS specific though, since I'm sure not all thread > implementations are equal. Define "better". It's evident that many bugs are more common, and some kinds of bugs only appear in threading applications. It's also debatable which is easier to program. I think the only useful measure might be context-switch performance. But note that if two processes (A and B) are doing rapid context switches between eachother, if their pages are all COW the page tables don't need to be updated. Your overhead would at most be comparison time. This behavior occurs quite frequently on unix systems. The question one postulates at this point is comparing page pointers more expensive than locks/system calls. The answer seems to be "usually not" although I have indeed run into cases where the answer is "yes". On other systems, where we have _three_ processes; two that COW their page tables and a third that has it's own address space, rapid context switches between all three if the third process goes between each of the other two (A->c->B->c->A->etc) - such as it is under Windows NT kernels that move many operations like network I/O onto a separate "process" - complete with its own address space and etc. On those systems, locks are definitely cheaper. However: Note that even on those systems, scheduling tasks like that are generally better optimized by moving both A and B's tasks into a single event-driven loop (and let c use the other processor). As a side note, many unix applications can be developed for a single event-driven loop and then with a few well placed fork()s be able to take advantage of multiprocessor systems immediately. If you take memory from your stack- you could even use pthread_create(). In this situation, however, I'd like to point out that we're not using a thread-programming-think even if the underlying technology might be called a thread.
Re: [sqlite] New Query Question
David Fowler wrote: Really sorry guys, AS files, should indeed be AS tables, thats what does not work. Sorry again for the copy/pasting/editing error. When I remember to name everything correctly, I still can't select anything by table.column sytax that is in the derived table, wether the derived table is aliased or not (I think it has to be aliased in SQLite, but not in mySQL). So to recap, this is what doesn't work, but I think should. SELECT tables.id FROM ( SELECT table4.location, table4.id for this line, try SELECT table4.location as location, table4.id as id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) AS tables GROUP BY tables.id ; This doesnt work either: SELECT table4.id FROM ( SELECT table4.location, table4.id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) GROUP BY table4.id ; Nor does specifying id only. Any input would be appreciated, as I cant think of a way I can work around this yet. Thanks SQLites behaviour is just getting worse. SELECT tables.* FROM ( SELECT table.field1, table.field2 FROM table ) as tables ; This will work. But as soon as i do the following it errors with invalid column again SELECT tables.field1 FROM ( SELECT table.field1, table.field2 try: SELECT table.field1 as field1, table.field2 as field2 FROM table ) as tables ; And when I do this, it works again SELECT tables.field1 FROM ( SELECT field1, field2 FROM table ) as tables ; My problem is when i do the latter with a larger query (SELECTs from more than one table), the column name becomes ambiguous, and the query fails once again. My problems with SQLite are really starting to get to me, I don't want to go back to MySQL, even if it is whats currently working, I moved to this database for its footprint and speed, but its features are starting to get me worried. What else does this database do wrong? This is an issue with the column names being returned by the engine. I think that theres a pragma that makes it behave as you expect it to. It's some combination of having pragma short_column_names = 0; and some other pragma like full_column_names or something there is a wiki page, as well as a bunch of discussion about this topic on the mailing list. One thing that I've made a habit of is aliasing all my column names, all the time, no matter what. John LeSueur
Re: [sqlite] New Query Question
Really sorry guys, AS files, should indeed be AS tables, thats what does not work. Sorry again for the copy/pasting/editing error. When I remember to name everything correctly, I still can't select anything by table.column sytax that is in the derived table, wether the derived table is aliased or not (I think it has to be aliased in SQLite, but not in mySQL). So to recap, this is what doesn't work, but I think should. SELECT tables.id FROM ( SELECT table4.location, table4.id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) AS tables GROUP BY tables.id ; This doesnt work either: SELECT table4.id FROM ( SELECT table4.location, table4.id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) GROUP BY table4.id ; Nor does specifying id only. Any input would be appreciated, as I cant think of a way I can work around this yet. Thanks SQLites behaviour is just getting worse. SELECT tables.* FROM ( SELECT table.field1, table.field2 FROM table ) as tables ; This will work. But as soon as i do the following it errors with invalid column again SELECT tables.field1 FROM ( SELECT table.field1, table.field2 FROM table ) as tables ; And when I do this, it works again SELECT tables.field1 FROM ( SELECT field1, field2 FROM table ) as tables ; My problem is when i do the latter with a larger query (SELECTs from more than one table), the column name becomes ambiguous, and the query fails once again. My problems with SQLite are really starting to get to me, I don't want to go back to MySQL, even if it is whats currently working, I moved to this database for its footprint and speed, but its features are starting to get me worried. What else does this database do wrong?
Re: [sqlite] New Query Question
Really sorry guys, AS files, should indeed be AS tables, thats what does not work. Sorry again for the copy/pasting/editing error. When I remember to name everything correctly, I still can't select anything by table.column sytax that is in the derived table, wether the derived table is aliased or not (I think it has to be aliased in SQLite, but not in mySQL). So to recap, this is what doesn't work, but I think should. SELECT tables.id FROM ( SELECT table4.location, table4.id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) AS tables GROUP BY tables.id ; This doesnt work either: SELECT table4.id FROM ( SELECT table4.location, table4.id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) GROUP BY table4.id ; Nor does specifying id only. Any input would be appreciated, as I cant think of a way I can work around this yet. Thanks
Re: [sqlite] SQLite 3.2 install on Mac OS X
The linking did work, but I think I'm still missing something: sudo make install Results in: tclsh ../tclinstaller.tcl 3.2 couldn't open ".libs/libtclsqlite3.dylib": no such file or directory while executing "open $LIBFILE" invoked from within "set in [open $LIBFILE]" (file "../tclinstaller.tcl" line 23) make: *** [tcl_install] Error 1 Looks as though it's trying to find .dylibs which don't exist. Is there some tcl/tk dependency I'm missing? Or perhaps I can just move sqlite3 and libsqlite3.a and/or libtclsqlite3.a to some where in my PATH. Thx, Andrew
Re: [sqlite] SQLite 3.2 install on Mac OS X
cd /usr/bin ln -s tclsh8.4.4 tclsh will probably do it for you, or to cover all the bases, cd /usr/bin ln -s tclsh8.4.4 tclsh8.4 ln -s tclsh8.4 tclsh Regards
[sqlite] SQLite 3.2 install on Mac OS X
I've been trying to get SQLite3 installed on Mac OS X. It compiles fine, but I get errors during the 'make install' concerning tclsh. tclsh is there in /usr/bin/tclsh8.4.4 How should I modify the install script, or should I create a ln of some sort for tclsh? Oh, and /usr/bin is in my PATH. Thanks, Andrew
Re: [sqlite] ORDER BY question
--- [EMAIL PROTECTED] wrote: > > Hello, > > I'm using sqlite3 and have a list of numbers I'd like returned sorted in > increasing order: > 1 1.1 1.10 1.11 1.9 10 11 9 12 > > However, if their column is defined as numeric, the 1.10 gets converted to > 1.1 (which is not what I want) and the result (using ORDER BY) is: > 1 1.1 1.1 1.11 1.9 9 10 11 12 > > I tried defining the column as a text and while the 1.10 is preserved, but > I get dictionary-style results: > 1 1.1 1.10 1.11 1.9 10 11 12 9 You could define the column as text and use "ORDER BY COLLATE NUMERIC". Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
[sqlite] ORDER BY question
Hello, I'm using sqlite3 and have a list of numbers I'd like returned sorted in increasing order: 1 1.1 1.10 1.11 1.9 10 11 9 12 However, if their column is defined as numeric, the 1.10 gets converted to 1.1 (which is not what I want) and the result (using ORDER BY) is: 1 1.1 1.1 1.11 1.9 9 10 11 12 I tried defining the column as a text and while the 1.10 is preserved, but I get dictionary-style results: 1 1.1 1.10 1.11 1.9 10 11 12 9 Any quick and easy solutions. Thanks, SD The information transmitted is intended only for the person(s)or entity to which it is addressed and may contain confidential and/or legally privileged material. Delivery of this message to any person other than the intended recipient(s) is not intended in any way to waive privilege or confidentiality. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by entities other than the intended recipient is prohibited. If you receive this in error, please contact the sender and delete the material from any computer. For Translation: http://www.baxter.com/email_disclaimer
Re: [sqlite] New Query Question
David Fowler wrote: > The problem is I can't get passed > here. SQLite gives me a "SQL error: no such > column: tables.id" What is the alias "files" for? Should not it be "table"? pf
Re: [sqlite] New Query Question
On 7/26/05, Clark Christensen <[EMAIL PROTECTED]> wrote: > > > --- David Fowler <[EMAIL PROTECTED]> wrote: > > > Hello again to everyone. > > I'm having trouble with the following query: > > SELECT tables.id FROM ( > > SELECT table4.location, table4.id > > FROM table1 > > INNER JOIN table2 ON (table1.class_id = table2.class_id) > > INNER JOIN table3 ON (table2.name_id = table3.name_id) > > INNER JOIN table4 ON (table4.id = table3.id) > > INNER JOIN table5 ON (table4.table5_id = table5.id) > > ) AS files GROUP BY tables.id > > ; without seeing the real sql it's hard to optimize it
Re: [sqlite] New Query Question
--- David Fowler <[EMAIL PROTECTED]> wrote: > Hello again to everyone. > I'm having trouble with the following query: > SELECT tables.id FROM ( > SELECT table4.location, table4.id > FROM table1 > INNER JOIN table2 ON (table1.class_id = table2.class_id) > INNER JOIN table3 ON (table2.name_id = table3.name_id) > INNER JOIN table4 ON (table4.id = table3.id) > INNER JOIN table5 ON (table4.table5_id = table5.id) > ) AS files GROUP BY tables.id > ; > > I know the query doesn't look useful like this, but with > WHERE and UNION ALL > it will be (for me). The problem is I can't get passed > here. SQLite gives me > a "SQL error: no such column: tables.id" even if I remove > the GROUP by, I > still revieve the error. > It seems that SQLite cannot reference aliased columns > properly from the > derived table. Am I right or is there a problem with my > SQL? This works fine > in MySQL. I can even remove the alias and reference the > column by its > original table name. However thats not possible in SQLite > either. > Any ideas appreciated once again, thank you. > - Dave > FWIW, maybe I'm missing something, but I don't see where you've aliased anything as "tables". In the example, it looks like you've aliased the subquery as "files". -Clark
[sqlite] New Query Question
Hello again to everyone. I'm having trouble with the following query: SELECT tables.id FROM ( SELECT table4.location, table4.id FROM table1 INNER JOIN table2 ON (table1.class_id = table2.class_id) INNER JOIN table3 ON (table2.name_id = table3.name_id) INNER JOIN table4 ON (table4.id = table3.id) INNER JOIN table5 ON (table4.table5_id = table5.id) ) AS files GROUP BY tables.id ; I know the query doesn't look useful like this, but with WHERE and UNION ALL it will be (for me). The problem is I can't get passed here. SQLite gives me a "SQL error: no such column: tables.id" even if I remove the GROUP by, I still revieve the error. It seems that SQLite cannot reference aliased columns properly from the derived table. Am I right or is there a problem with my SQL? This works fine in MySQL. I can even remove the alias and reference the column by its original table name. However thats not possible in SQLite either. Any ideas appreciated once again, thank you. - Dave
Re: [sqlite] Index generation efficiency
On 7/25/05, Mathieu Blondel <[EMAIL PROTECTED]> wrote: > Hi everyone, > I'm using sqlite3 for my project, Nihongo Benkyo, which is a japanese > dictionary and learning tool. Cool! I'm currently using sqlite 3.x in a lightweight japanese dictionary of my own, Gozoku (http://dev.int64.org/gozoku.html). > This program can import data from files in various formats in the sqlite > database. Generally speaking, one import does about 500,000 INSERT queries > in a single transaction and it is working fine. > > The main benefit from using a transaction is to reduce the number of disc > accesses if my understanding is correct. However, if I put indexes on some > columns, the import becomes very slow. I don't know the sqlite code but it > sounds to me like the indexes are generated on the fly which seems to > require a lot of work. > > I found out it is much more efficient to drop all the indexes, do my big > transaction and then recreate all the indexes. Dropping indexes is not a > really long process and just creating the indexes after the end of the > transaction seems quicker. But if think it is quite dirty to do so everytime > I import new data in the database. I do this when generating my database but it's not a problem as I don't modify the database afterward. > I guess there may be good reasons not to generate the indexes all at once at > the end of the transaction. So first I would be glad to have some > explanation about this. Then, do you have a better solution than mine to > speed up the indexes generation ? Is it for example possible to ask > explicitly sqlite not to update an index and then ask explicitly "by hand" > to update the indexes. I'm guessing this is because you can have select queries in a transaction which wouldn't work properly with an incomplete index. It would certainly be interesting to make a pragma that makes sqlite queue up index generation until the next select or end of transaction. > Thanks, > Mathieu. > > -- Cory Nelson http://www.int64.org
Re: [sqlite] Index generation efficiency
On 7/25/05, Mathieu Blondel <[EMAIL PROTECTED]> wrote: > I found out it is much more efficient to drop all the indexes, do my big > transaction and then recreate all the indexes. Dropping indexes is not a > really long process and just creating the indexes after the end of the > transaction seems quicker. But if think it is quite dirty to do so everytime > I import new data in the database. > This is the recommended practice for large loads to oracle. Having a switch that turns off indexing would have to be very carefully done to avoid creating a bad database, or creating more problems than it solves..
Re: [sqlite] sqlite3_set_authorizer
--- Marco Bambini <[EMAIL PROTECTED]> wrote: > From the official documentation of the sqlite3_set_authorizer routine: > "This routine registers a callback with the SQLite library. The > callback is invoked (at compile-time, not at run-time) for each > attempt to access a column of a table in the database." > > What does means at compile-time and not at run-time? > It means that is checked when the sqlite3_prepare routine is invoked? Correct. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Re: [sqlite] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
Martin Jenkins wrote:
v00d00 dave wrote:
Martin Jenkins schrieb:
CREATE TABLE `blacklist` (
`blcfid` int(11) NOT NULL default '0',
`blndid` int(11) NOT NULL default '0',
PRIMARY KEY (`blcfid`,`blndid`)
) TYPE=MyISAM;
If you run the sqlite3 command line utility and .read your script
you'll see why it doesn't work - you need to change the back-ticks
(`) to single quotes (') and delete "TYPE=MyISAM". I didn't see any
disk image malformed messages, so can't help there.
Martin
hi! of course, i do so - like i wrote in the first email:
---
cat myproject.mysqldump \
| sed "s/\`/\"/g" \
| sed -e 's/TYPE=MyISAM/ /g' \
| sqlite3 myproject.sqlite3
---
like mentioned before: the syntax of the sql is fine - if i change
the ORDER of the tables manually, it runs fine.
thanks anyways
Doh! So you did. :) FWIW, I made the changes manually and the script
imports with no errors.
I forgot to say this is with sqlite 3.2.1 on XP SP2. Sorry for the lack
of rigour - hay fever's getting to me.
Martin
[sqlite] sqlite3_set_authorizer
From the official documentation of the sqlite3_set_authorizer routine: "This routine registers a callback with the SQLite library. The callback is invoked (at compile-time, not at run-time) for each attempt to access a column of a table in the database." What does means at compile-time and not at run-time? It means that is checked when the sqlite3_prepare routine is invoked? Thanks a lot for you help. Regards, Marco Bambini smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
v00d00 dave wrote:
Martin Jenkins schrieb:
CREATE TABLE `blacklist` (
`blcfid` int(11) NOT NULL default '0',
`blndid` int(11) NOT NULL default '0',
PRIMARY KEY (`blcfid`,`blndid`)
) TYPE=MyISAM;
If you run the sqlite3 command line utility and .read your script
you'll see why it doesn't work - you need to change the back-ticks
(`) to single quotes (') and delete "TYPE=MyISAM". I didn't see any
disk image malformed messages, so can't help there.
Martin
hi! of course, i do so - like i wrote in the first email:
---
cat myproject.mysqldump \
| sed "s/\`/\"/g" \
| sed -e 's/TYPE=MyISAM/ /g' \
| sqlite3 myproject.sqlite3
---
like mentioned before: the syntax of the sql is fine - if i change the
ORDER of the tables manually, it runs fine.
thanks anyways
Doh! So you did. :) FWIW, I made the changes manually and the script
imports with no errors.
Martin
Re: [sqlite] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
hi! i found a solution: the problem only occurs on ubuntu(hoary) machines. i tried my scripts on a plain sarge machine, and it worked fine! shame on me: i _thought_ i did try on sarge before, but it was an ubuntu machine, too. if anyone is still interested in this bug, feel free to email, i will try helping tracing it down. i really wonder, what might be wrong with hoary, so those unpredictable strange error occur... thanks anyways dave
Re: [sqlite] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
When executing 'mysqldump', use the '--compatible=ansi' flag.
Ben
Martin Jenkins wrote:
CREATE TABLE `blacklist` (
`blcfid` int(11) NOT NULL default '0',
`blndid` int(11) NOT NULL default '0',
PRIMARY KEY (`blcfid`,`blndid`)
) TYPE=MyISAM;
If you run the sqlite3 command line utility and .read your script you'll
see why it doesn't work - you need to change the back-ticks (`) to
single quotes (') and delete "TYPE=MyISAM". I didn't see any disk image
malformed messages, so can't help there.
Martin
Re: [sqlite] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
Martin Jenkins schrieb:
CREATE TABLE `blacklist` (
`blcfid` int(11) NOT NULL default '0',
`blndid` int(11) NOT NULL default '0',
PRIMARY KEY (`blcfid`,`blndid`)
) TYPE=MyISAM;
If you run the sqlite3 command line utility and .read your script you'll
see why it doesn't work - you need to change the back-ticks (`) to
single quotes (') and delete "TYPE=MyISAM". I didn't see any disk image
malformed messages, so can't help there.
Martin
hi! of course, i do so - like i wrote in the first email:
---
cat myproject.mysqldump \
| sed "s/\`/\"/g" \
| sed -e 's/TYPE=MyISAM/ /g' \
| sqlite3 myproject.sqlite3
---
like mentioned before: the syntax of the sql is fine - if i change the
ORDER of the tables manually, it runs fine.
thanks anyways
Re: [sqlite] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
CREATE TABLE `blacklist` (
`blcfid` int(11) NOT NULL default '0',
`blndid` int(11) NOT NULL default '0',
PRIMARY KEY (`blcfid`,`blndid`)
) TYPE=MyISAM;
If you run the sqlite3 command line utility and .read your script you'll
see why it doesn't work - you need to change the back-ticks (`) to
single quotes (') and delete "TYPE=MyISAM". I didn't see any disk image
malformed messages, so can't help there.
Martin
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. == Thanks for the very informative reply! I think you must be right about the OR problem, infact I'm sure, because my (original) query succeeds very quickly without it, even though the original merges SIX tables together, using only the (,) operator (no INNER JOIN ON - Creating 1.5 billion possible rows). But as soon as I add an OR, the query crawls to a stunning 10 minutes! INNER JOIN must play a part in speed though too, as when using it I get down from 10m to 15s, but that was still unnacceptable (I think the speed increase was because the possible rows was reduced to 17 thousand-ish). Rearranging the tables got me down to about 1s, which is just acceptable. I think I may switch to using UNION again, because at first I didn't see it as a real solution and carried on looking for something better. Then see what happens, hopefully I wont even be able to time it without doing debug in my program then, which will be nice. Thanks again. - Dave
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] sqlite3 & importing mysql-dumps: "SQL error: database disk image is malformed"
hi! versions: sqlite3: v3.2.1 with corresponding version of libsqlite3 (/usr/lib/libsqlite3.so.0.8.6) testing oses: ubuntu hoary and debian sarge i get various errors, if importing the attached file with --- rm -f myproject.sqlite3 cat myproject.mysqldump \ | sed "s/\`/\"/g" \ | sed -e 's/TYPE=MyISAM/ /g' \ | sqlite3 myproject.sqlite3 --- first of all, if i only try to import the script, i get: --- INSERT INTO "conffile" VALUES (0,'/etc/samba/smb.conf','samba main config file',NULL); SQL error: no such table: conffile --- even it was just created. i "fixed" this problem with creating a dummy table BEFORE importing with: --- echo "create table dummy(i integer not null primary key);" | sqlite3 # inserting of the script here echo "drop table dummy;" | sqlite3 sykconf.sqlite3 --- not nice, but worked until now i did my tests this way, and was happy since it worked - but now, with the attached db-schema, i get: --- SQL error: database disk image is malformed --- hello ? i deleted the db, and re-created it from scratch - so, how can the image be malformed ? and: we have a side-effect, since if i change the order of the creation of tables in the .mysqldump file, all works fine - by just changing the order of the statements, not the statements themself. i guess, my input is okay, but there is something in the statements, which confuses sqlite3 so, i am at a point, where i dont know, how to continue - i really need help - i guess, i am doing something very wrong, and dont know what. background: why do i use mysql is simple: i miss good configuration/admin tools for a sqlite db - i could write an sql script in a text editor - i am able to do so, but i prefer a "gui way" today. so, i have a mysqldb, which i administer with my favorite tool, and then just "dump" the db into a sqlite db - so, i have both: the nice user interface plus admin tools of mysql and the simplicity of sqlite - if you know a better way, tell me. without above errors, that concept would work very well and fit all my needs. -- MySQL dump 9.11 -- -- Host: localhostDatabase: sykconf -- -- -- Server version 4.0.23_Debian-3ubuntu2-log -- -- Table structure for table `blacklist` -- CREATE TABLE `blacklist` ( `blcfid` int(11) NOT NULL default '0', `blndid` int(11) NOT NULL default '0', PRIMARY KEY (`blcfid`,`blndid`) ) TYPE=MyISAM; -- -- Dumping data for table `blacklist` -- -- -- Table structure for table `conffile` -- CREATE TABLE `conffile` ( `cfcfid` int(11) NOT NULL default '0', `cffname` varchar(255) NOT NULL default '', `cfdescr` varchar(255) default NULL, `cftemplate` longtext, PRIMARY KEY (`cfcfid`) ) TYPE=MyISAM; -- -- Dumping data for table `conffile` -- INSERT INTO `conffile` VALUES (0,'/etc/samba/smb.conf','samba main config file',NULL); INSERT INTO `conffile` VALUES (1,'/etc/postfix/master.cf','postfix master file',NULL); INSERT INTO `conffile` VALUES (2,'/etc/apache2/apache2.conf','main config file of apache2',NULL); -- -- Table structure for table `confparams` -- CREATE TABLE `confparams` ( `cpcpid` int(11) NOT NULL default '0', `cpcfid` int(11) NOT NULL default '0', `cpndid` int(11) NOT NULL default '0', `cpcsid` int(11) NOT NULL default '0', `cpreplace` mediumtext, PRIMARY KEY (`cpcpid`) ) TYPE=MyISAM; -- -- Dumping data for table `confparams` -- INSERT INTO `confparams` VALUES (0,0,0,0,'sykosch'); INSERT INTO `confparams` VALUES (1,0,1,0,'simpsons'); INSERT INTO `confparams` VALUES (2,0,0,1,'sylix samba server'); -- -- Table structure for table `confsearchfor` -- CREATE TABLE `confsearchfor` ( `cscsid` int(11) NOT NULL default '0', `cscfid` int(11) NOT NULL default '0', `cssearch` varchar(255) NOT NULL default '', `csdescr` mediumtext NOT NULL, PRIMARY KEY (`cscsid`) ) TYPE=MyISAM; -- -- Dumping data for table `confsearchfor` -- INSERT INTO `confsearchfor` VALUES (0,0,'$$WORKGROUP','Setzt die Arbeitsgruppe der SMB-Domain'); INSERT INTO `confsearchfor` VALUES (1,0,'$$SERVERSTRING','Setzt den Serverstring des SMB-Servers'); -- -- Table structure for table `node` -- CREATE TABLE `node` ( `ndndid` int(11) NOT NULL default '0', `ndname` varchar(50) default NULL, `ndcomment` varchar(255) default NULL, PRIMARY KEY (`ndndid`) ) TYPE=MyISAM; -- -- Dumping data for table `node` -- INSERT INTO `node` VALUES (0,'Default Values','Here we get all the default-values'); INSERT INTO `node` VALUES (1,'Root Node','Here should all the config data be located. All settings are saved in this node.'); INSERT INTO `node` VALUES (2,'Cluster Node I','Will be used in forthcoming Cluster Version'); INSERT INTO `node` VALUES (3,'Cluster Node II','Will be used in forthcoming Cluster Version');
