Re: [sqlite] Reading strings and blobs
[23-07-2005 1:29, D. Richard Hipp escreveu] On Fri, 2005-07-22 at 19:15 -0500, Shawn Walker wrote: Is there a way to have sqlite to call a callback function to read in strings and blobs? No. If your blobs are too big to fit in memory, perhaps you should consider storing them each in a separate file and then store just the filename in the database. I'm currently using a version control system called monotone that uses sqlite (http://venge.net/monotone). Most of the time they store file deltas and most files are small, but there are people that have ideas like using monotone to version big binary files (there was even a person that wanted to use monotone to version DVD ISOs :D ). What I want to say with this is that it seems a use case where having the possibility to minimize memory usage would be great for them when handling this type of cases, as one of the advantages of monotone is exactly having a single database file to hold all the version info. You could argue that then sqlite is not the right database engine for the job, but i would think that if sqlite isn't, then what is? I'm not a monotone developer, just someone with limited info on some of the sqlite problems they are facing. Best Regards, ~Nuno Lucas
Re: [sqlite] Reading strings and blobs
D. Richard Hipp wrote: On Fri, 2005-07-22 at 19:15 -0500, Shawn Walker wrote: Is there a way to have sqlite to call a callback function to read in strings and blobs? No. If your blobs are too big to fit in memory, perhaps you should consider storing them each in a separate file and then store just the filename in the database. They are not too big for the memory, the point is that reading from sql is one set of memory and then I need put that blob into another a file. If there was a way that callback could be done, that small set of memory would pass it to the call back and then I can set it into a file.
Re: [sqlite] Reading strings and blobs
If your blobs are too big to fit in memory, perhaps you should consider storing them each in a separate file and then store just the filename in the database. That assumes you have a filesystem :-) I too would like random access to strings and blobs (especially the latter). In many cases isn't about fitting into memory, but rather just minimizing memory consumption. For example if another component uses 64KB buffers, then it would be most convenient to access the blobs 64KB at a time. Roger
Re: [sqlite] Reading strings and blobs
On Fri, 2005-07-22 at 19:15 -0500, Shawn Walker wrote: > Is there a way to have sqlite to call a callback function to read in > strings and blobs? No. If your blobs are too big to fit in memory, perhaps you should consider storing them each in a separate file and then store just the filename in the database. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Reading strings and blobs
Is there a way to have sqlite to call a callback function to read in strings and blobs? What I mean is that we have some very large data and rather not consume all those memory, so what I want to do is pass a user defined pointer that contains a pointer to a file handle (as an example) and that sqlite will call the callback function with the string/blob data and the callback function will write to the file. Is that possible?
Re: [sqlite] Question about sqlite TEMP table
On Fri, 2005-07-22 at 19:44 -0300, Ming Qiang Lin wrote: > It says that temp table is visible to the process it created, but how > about in this same process, I join a temp table with non-temp table?. > Since I am having Exception says: "no such table" when I do the join. > The text is wrong and has now been corrected. See http://www.sqlite.org/cvstrac/chngview?cn=2559 TEMP tables in SQLite work like they do in every other SQL database engine. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Re: Question about sqlite TEMP table
I forgot to mention, I do have two connections in ONE process, one of the connection is used to create those temp tables. but still, after temp table is created, none of the two connection can access to the Temp tables. On 7/22/05, Ming Qiang Lin <[EMAIL PROTECTED]> wrote: > Hi All, > > Following is the sqlite CREATE TABLE definition: > > If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and > "TABLE" then the table that is created is only visible to the process > that opened the database and is automatically deleted when the > database is closed. Any indices created on a temporary table are also > temporary. Temporary tables and indices are stored in a separate file > distinct from the main database file. > > If a is specified, then the table is created in the > named database. It is an error to specify both a and > the TEMP keyword, unless the is "temp". If no database > name is specified, and the TEMP keyword is not present, the table is > created in the main database. > > > It says that temp table is visible to the process it created, but how > about in this same process, I join a temp table with non-temp table?. > Since I am having Exception says: "no such table" when I do the join. > > Please help! > > Thank you in advance. > > > Mica >
[sqlite] Basic qns about TEMP table
1) When I want to move/insert records from a TEMP table into my regular table, can I do this within a transaction? 2) Suppose my Table/(and Temp Table) have a schema like CREATE [TEMP] TABLE My[Temp]Test ( id primary key not null, value int); and I use insert into MyTest(null, {intvalue}) to populate my DBs. When I finally move records from my Temp Table into my Regular Table, will the id be automtically sequential? So if MyTest contained: 1, 1 2, 5 3, 6 and MyTempTest contained: 1,7 2, 11 3, 44 after insertion would they look like: 1,1 2, 5 3, 6 4,7 5, 11 6, 44 Also is insert into Table(null,...) the most efficient way of inserting a record when the id is unique? Thanks!
[sqlite] Question about sqlite TEMP table
Hi All, Following is the sqlite CREATE TABLE definition: If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible to the process that opened the database and is automatically deleted when the database is closed. Any indices created on a temporary table are also temporary. Temporary tables and indices are stored in a separate file distinct from the main database file. If a is specified, then the table is created in the named database. It is an error to specify both a and the TEMP keyword, unless the is "temp". If no database name is specified, and the TEMP keyword is not present, the table is created in the main database. It says that temp table is visible to the process it created, but how about in this same process, I join a temp table with non-temp table?. Since I am having Exception says: "no such table" when I do the join. Please help! Thank you in advance. Mica
RE: [sqlite] Multi-threading.
On Fri, 2005-07-22 at 17:23 -0400, D. Richard Hipp wrote: > On Fri, 2005-07-22 at 14:00 -0700, Tim McDaniel wrote: > > It is hard, if not impossible, with modern GUI > > systems to write any relatively complex app that is both performant and > > graphically responsive without using threads. > > > > Not true. > > The event model works just fine for GUI programming. > The bulk of my consulting practice (for the past 13 > years) has been writing high-performance GUIs running > in front of numerically intensive scientific and > engineering applications. Most of these run on > windows - at least over the past 5 years. None > of them have ever used more than a single thread. I wasn't about to consider Windows a modern GUI system :)
RE: [sqlite] Multi-threading.
On Fri, 2005-07-22 at 14:00 -0700, Tim McDaniel wrote: > > History has demonstrated that programmers building > > multithreaded applications tend to produce buggier code, and > > code that touches more pages than a non-threaded version. As > > a result, the non-threaded version is easier to write, safer, > > and runs faster. > > So, what's your point? That writing things the easy way leads to safer, > less buggy, faster code? That's hardly a point. The original poster > presented one of the more compelling reasons for multi-threading in > modern apps, the GUI. It is hard, if not impossible, with modern GUI > systems to write any relatively complex app that is both performant and > graphically responsive without using threads. I have no problems writing GTK applications without threads. I suspect the toolkits you are familiar with are inadequate. Most GUI toolkits rely heavily on callback interfaces. Closures and signal-based event notification are much more beneficial than threads: they produce fewer bugs, and are easier to program still. But this isn't an argument for closures and signal-based event notification. This is a general argument against threads. > At least for the short term, Moore's Law is slowing down, we might as > well start calling it Moore's Dream. All main CPUs are going multicore, > even game consoles, and one of the only realistic ways to take advantage > of that is through multi-threading. Saying it is hard doesn't change > reality. The other "realistic" way is multiple processes, which is the point I argue for. Threads can be useful, but they're only faster than processes if they touch less pages. Threads require more memory (synchronization primitives, etc) and many languages that encourage threading provide no mechanism to isolate those privileges onto a few pages. Those page hits tend to cause MORE page-table access than similarly-written programs than use processes. I use threads sparingly, and try to keep all memory access on the stack when I do use threads so that I am NOT touching any heap-memory (rather as little as possible), and therefore, require few synchronization primitives. I tend to not see very many programmers doing this. I suspect this is because programmers believe "threads are faster and cheaper, so everyone should use them." In general, threads don't solve problems because the problems that they CAN solve, people rarely face, and the problems that they are ATTEMPTING to solve can be solved in better, cleaner ways. > > > As another user also mentioned, a Windows system works > > better with few > > > processes with many threads. > > > > Windows uses threads because x86 page tables are expensive to > > load. It doesn't help: the system-call method Windows uses > > eats any benefit that it has, again producing net-zero. > > This being THE reason Windows emphasizes threads over processes is hard > to swallow. What? That threads are cheaper than processes? If Windows has another reason for using threads instead of processes I'd love to hear it. With all of the downsides to threads, the only reasonable one is that they _may_ be cheaper than processes. Unfortunately, that textbook _may_ is a reality _isn't_ in most cases. Nevertheless, my point to this statement was lost: Windows could optimize process creation, but it could also optimize system calls. Those steps would make the parent-threads' argument moot (not to mix metaphors). > > > I am also interested in your comments on Pointers and GoTo. I note > > > that Java is 100% pointers. Apart from basic types, all > > object access > > > is by pointer. > > > > Java uses references, not pointers. > > This is purely semantic nit picking. That's your opinion. Other languages have both. When they do, the distinction is more important. > > > Using Exceptions correctly, I have never felt the need for a GoTo. > > > Exceptions do the same as GoTo, accept, maybe, in a slightly more > > > developed and useful way. > > > > Exceptions are slower than goto. They are also less > > straightforward when deeply nested (long chains of throws XYZ > > come to mind...) > > > > I would agree that exceptions are not a good replacement for gotos. > However, having been a professional C++ programmer for over 10 years, I > have never needed a goto. This probably stems more from the fact that > with C++/Java/C# you don't really need gotos, but with C/Basic/etc there > are arguably things that you can't do, or would be quite hard to do, > without gotos. I'm glad I gave you an opportunity to post your VC. Meanwhile, while C doesn't _need_ gotos: for (i = 0; i < n; i++) for (j = 0;j < m; j++) if (q(i,j)) goto TAIL; TAIL: looks better than the alternative. Just because you don't _need_ a condom doesn't mean it's not a good idea. I think that avoiding goto is good for the same reason that avoiding threads are good. Once you've gotten used to knowing why they're bad is when you can begin to safe
RE: [sqlite] Multi-threading.
On Fri, 2005-07-22 at 14:00 -0700, Tim McDaniel wrote: > It is hard, if not impossible, with modern GUI > systems to write any relatively complex app that is both performant and > graphically responsive without using threads. > Not true. The event model works just fine for GUI programming. The bulk of my consulting practice (for the past 13 years) has been writing high-performance GUIs running in front of numerically intensive scientific and engineering applications. Most of these run on windows - at least over the past 5 years. None of them have ever used more than a single thread. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Multi-threading.
> -Original Message- > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: Friday, July 22, 2005 3:07 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Multi-threading. > > > However the need for multi-threads is compelling. > Especially in a GUI > > environment. For instance a Mail reader. Where one thread > is needed > > to ensure the GUI is drawn correctly and respond to GUI events. > > Another to download and dispatch mail. (My Thunderbird has 10 > > threads. This may be a bit of overkill :) > > No. Threads are not a need. They allow you to use blocking > system calls in parallel without extra page table loads. > > History has demonstrated that programmers building > multithreaded applications tend to produce buggier code, and > code that touches more pages than a non-threaded version. As > a result, the non-threaded version is easier to write, safer, > and runs faster. So, what's your point? That writing things the easy way leads to safer, less buggy, faster code? That's hardly a point. The original poster presented one of the more compelling reasons for multi-threading in modern apps, the GUI. It is hard, if not impossible, with modern GUI systems to write any relatively complex app that is both performant and graphically responsive without using threads. At least for the short term, Moore's Law is slowing down, we might as well start calling it Moore's Dream. All main CPUs are going multicore, even game consoles, and one of the only realistic ways to take advantage of that is through multi-threading. Saying it is hard doesn't change reality. > > > > As another user also mentioned, a Windows system works > better with few > > processes with many threads. > > Windows uses threads because x86 page tables are expensive to > load. It doesn't help: the system-call method Windows uses > eats any benefit that it has, again producing net-zero. This being THE reason Windows emphasizes threads over processes is hard to swallow. > > > I am also interested in your comments on Pointers and GoTo. I note > > that Java is 100% pointers. Apart from basic types, all > object access > > is by pointer. > > Java uses references, not pointers. This is purely semantic nit picking. > > > Using Exceptions correctly, I have never felt the need for a GoTo. > > Exceptions do the same as GoTo, accept, maybe, in a slightly more > > developed and useful way. > > Exceptions are slower than goto. They are also less > straightforward when deeply nested (long chains of throws XYZ > come to mind...) > I would agree that exceptions are not a good replacement for gotos. However, having been a professional C++ programmer for over 10 years, I have never needed a goto. This probably stems more from the fact that with C++/Java/C# you don't really need gotos, but with C/Basic/etc there are arguably things that you can't do, or would be quite hard to do, without gotos. > > These are just my opinions :) > > They are wrong. > I hope there was a hint of sarcasm in that last comment. The original poster obviously didn't hit everything on the nail, but there is a whole world of gray between right and wrong. Tim
Re: [sqlite] Multi-threading.
On 7/22/05, Paul G <[EMAIL PROTECTED]> wrote: > > Using Exceptions correctly, I have never felt the need for a GoTo. > > Exceptions do the same as GoTo, accept, maybe, in a slightly more > > developed and useful way. > > then you haven't coded anything complex enough to require them. i can tell > you that they are an absolute necessity when dealing with a lot of nesting, I've found the state machine to be a wonderful concept for eliminating the need for gotos.
Re: [sqlite] Multi-threading.
- Original Message - From: "Ben Clewett" <[EMAIL PROTECTED]> To: Sent: Wednesday, July 20, 2005 12:26 PM Subject: Re: [sqlite] Multi-threading. > Dr Hipp, > > I am just playing devils advocate here because I have completed much > Java programming in a multi-threaded application. :) > > I understand the problems of multi-threading. I am reminded that it > took nearly 20 years of development to get multi-processor support in a > modern OS stable. Much success for this can be attributed to Semaphore > Flags. With CPU hardware support to ensure that the semaphore it's self > cannot be controlled by more than one process. whatcha talkin bout willis? all you need to fence access to a shared resource is a set of atomic operations which carry the semantics you are looking for. this has been around for quite some time. > Multi-thread applications suffer the same problems. Without semaphore > flags or 20 years of development. say what? > A novice programmer can happily > create a second thread and quickly create terribly applications. yes. non-novices too, hence richard's advice. > As another user also mentioned, a Windows system works better with few > processes with many threads. this has to do with process creation being a very expensive operation in windows. just because a certain platform cripples something doesn't make it bad. > I believe the issue is not whether to use threads, but to use them > correctly. Which is not a hard thing to do with a little support. you've got no idea. there are *very* few threading interfaces which make writing correct code reasonably easy. java and .net are certainly not among them. erlang is, but the trouble is they're implemented as microthreads, so you're still stuck with problems making blocking syscalls. > This is where Java (and .NET) work well. If you use them correctly. > They provide thread-safe objects. Which have been designed to use > semaphore flags internally. If the programmer uses these thread-safe > objects correctly, they will not encounter thread issues. For instance, > all communication between threads should be exclusively through these > thread-safe objects. stop misusing 'semaphore' already, semaphore is not a synonym for a synchronization object - it is a kind of a synchronization object and has a very narrow definition. > Further, Java and .NET provide Sycronisation methods. The defining of a > method to be synchronised automatically creates the locks to ensure > thread safe access. you don't get the point. this (as opposed to more intuitive primitives *around* threading, such as actors) is just a couple lines of code here and there in a language which does not have syntax for it. coders have to understand the issues and use the locking primitives they are given correctly. i'd say your opinions, as expressed in your message, demonstrate that the mere presence of higher level sycnhronization primitives in a development platform does not magically make users of said platform understand threading issues. > I am also interested in your comments on Pointers and GoTo. I note that > Java is 100% pointers. Apart from basic types, all object access is by > pointer. this is why teaching java in cs 101 is a bad idea, people have no understanding of how things work 'under the hood' anymore. java uses references, which are implemented as primitives in the jvm itself. > Using Exceptions correctly, I have never felt the need for a GoTo. > Exceptions do the same as GoTo, accept, maybe, in a slightly more > developed and useful way. then you haven't coded anything complex enough to require them. i can tell you that they are an absolute necessity when dealing with a lot of nesting, which may or may not deal with error handling. dijkstra was preaching against using goto to create spaghetti code; many experienced coders use gotos to *improve* readability and hence maintainability of their code. 'using'-like constructs give you *some* of this, but you will still want gotos even in languages where both exceptions and using are available when dealing with non-trivial logic. > These are just my opinions :) no offense, but you probably want to have the correct data from which to derive your opinions ;) -p
Re: [sqlite] Multi-threading.
On Wed, 2005-07-20 at 17:26 +0100, Ben Clewett wrote: > Dr Hipp, > > I am just playing devils advocate here because I have completed much > Java programming in a multi-threaded application. :) > > I understand the problems of multi-threading. I am reminded that it > took nearly 20 years of development to get multi-processor support in a > modern OS stable. Much success for this can be attributed to Semaphore > Flags. With CPU hardware support to ensure that the semaphore it's self > cannot be controlled by more than one process. ITC in 1970 supported multiple threads trivially. > Multi-thread applications suffer the same problems. Without semaphore > flags or 20 years of development. A novice programmer can happily > create a second thread and quickly create terribly applications. > However the need for multi-threads is compelling. Especially in a GUI > environment. For instance a Mail reader. Where one thread is needed to > ensure the GUI is drawn correctly and respond to GUI events. Another to > download and dispatch mail. (My Thunderbird has 10 threads. This may > be a bit of overkill :) No. Threads are not a need. They allow you to use blocking system calls in parallel without extra page table loads. History has demonstrated that programmers building multithreaded applications tend to produce buggier code, and code that touches more pages than a non-threaded version. As a result, the non-threaded version is easier to write, safer, and runs faster. > As another user also mentioned, a Windows system works better with few > processes with many threads. Windows uses threads because x86 page tables are expensive to load. It doesn't help: the system-call method Windows uses eats any benefit that it has, again producing net-zero. > I believe the issue is not whether to use threads, but to use them > correctly. Which is not a hard thing to do with a little support. > This is where Java (and .NET) work well. If you use them correctly. > They provide thread-safe objects. Which have been designed to use > semaphore flags internally. If the programmer uses these thread-safe > objects correctly, they will not encounter thread issues. For instance, > all communication between threads should be exclusively through these > thread-safe objects. Java uses threads poorly. They're expensive to set up, and many Java programmers yield to non-blocking methods as Java closures tend to be easier to program, and faster too. > Further, Java and .NET provide Sycronisation methods. The defining of a > method to be synchronised automatically creates the locks to ensure > thread safe access. ... > I am also interested in your comments on Pointers and GoTo. I note that > Java is 100% pointers. Apart from basic types, all object access is by > pointer. Java uses references, not pointers. > Using Exceptions correctly, I have never felt the need for a GoTo. > Exceptions do the same as GoTo, accept, maybe, in a slightly more > developed and useful way. Exceptions are slower than goto. They are also less straightforward when deeply nested (long chains of throws XYZ come to mind...) > These are just my opinions :) They are wrong.
[sqlite] possible optimization to sqlite3BtreeMoveto
I've been working with SQLite 3.2.1 and trying to do database creation as fast as possible. In my situation, I've been using auto-incremented key values. One thing that I've noted is that a fair amount of time was being spent in sqlite3BtreeMoveto(). As I understand it, the general algorithm there is to do a binary search for an appropriate place for insertion of a new element. As per standard binary search, the search is started in the middle of the range ( pCur->idx = (lwr+upr)/2; ). For the case of auto-incremented keys, it seems that we'd always be finding an entry at the far right (upper) end of the range. I put in a simple modification to start the search at the upper end of the range ( pCur->idx = upr; ). With this, I was able to do inserts with the example program at (http://anchor.homelinux.org/SQLiteTuning) about 10-15% faster. My simple change to sqlite3BtreeMoveTo() in btree.c was: ... int firstPass=1; while( lwr<=upr ){ void *pCellKey; i64 nCellKey; /* * Since we are inserting with auto increment key, we should expect * to find match at top end of range. Start search there. */ if (firstPass) { pCur->idx = upr; firstPass=0; } else { pCur->idx = (lwr+upr)/2; } ... I'm not sure of all of the implications of such a change, so am I not able to offer complete code for it. I suspect that there may be various flags that could be examined to know when to use this different starting point, etc. I mention this as a suggestion for active developers of SQLite as something to be considered. Thanks for your consideration, --Chuck Pahlmeyer
Re: [sqlite] optimizing database creation speed
My data types are similar to those in your example. Using the suggestions on your page, I was able to increase performance a fair bit. I'm able to get near 240,000 inserts/second on my 3GHz Xeon Linux system with your example program. I did find a place for optimization in the btree code which I'll mention in a separate post. Thanks to all who replied. --Chuck Pahlmeyer Al Danial wrote: On 7/21/05, Chuck Pahlmeyer - MTI <[EMAIL PROTECTED]> wrote: I have an application in which I'd like to create a database as quickly as possible. The application has a batch process at startup which creates the data. I am using a single transaction for all of the INSERT statements. I'm also using prepared statements to alleviate some of the overhead for processing SQL text. Typical table sizes are on the order of a few million rows. I can get about 50,000 rows/second on my 3GHz Linux system, but would like to do better. What are the data types of the columns? For integers and floats I've seen insert speeds of over 300,000 rows/second. One thing that helps a lot is building SQLite with optimization flags that are tweaked to your CPU. http://anchor.homelinux.org/SQLiteTuning shows some good settings for Pentium4, Opteron, and Athlon. That site also gives specs for a system which can do nearly 310,000 inserts/sec for a table having one integer and three floats. Other things to look at: disk drives with large densities (>= 300 GB) and high RPM, different file systems (xfs has proven to be fast but I bet the old ext2 may be faster still), different Linux kernels. I've found a marginal (3%) performance boost by dowloading the beta for GCC v4.1, then building SQLite (again, with all the optimization tweaks) with it instead of GCC 3.x. But that's a lot of hassle for little gain.-- Al
Re: [sqlite] optimizing database creation speed
--- Al Danial <[EMAIL PROTECTED]> wrote: > On 7/21/05, Chuck Pahlmeyer - MTI <[EMAIL PROTECTED]> > wrote: > > I have an application in which I'd like to create a > database as > > quickly as possible. The application has a batch > process at > > startup which creates the data. I am using a single > transaction > > for all of the INSERT statements. I'm also using > prepared statements > > to alleviate some of the overhead for processing SQL > text. Typical > > table sizes are on the order of a few million rows. I > can get > > about 50,000 rows/second on my 3GHz Linux system, but > would like to > > do better. > Maybe it's obvious, but worth mentioning that if you have many (or complex) indexes on the target table, insert speed could be affected. If you have the option, it might go faster to complete the inserts, then add whatever indexes your app requires. -Clark
Re: [sqlite] order of reception of results at the time of a request SQL
On 7/22/05, LURKIN Denis <[EMAIL PROTECTED]> wrote: > I have do many tests. It appears that the results of requests > each time are received in the same order. This order is that of > creation of the table. If I have make a CREATE TABLE X (int, char, > int, char), I receive the results of my request SQL in the good order: > int, char, int, char. However I made only a "select *". I did not > specify the name of the even put columns nor a "order by". Thus I have > the impression that I will always receive the results in the order of > creation of the table when I make a "select *". > > Can I in being sure? If it is possible, I would like several > opinions on the question. In general, yes. The order of columns in a table is generally fixed. However, it is a Bad Idea to do a SELECT * in SQL code because if you modify the database between r1 and r2, your code will probably break. On the other hand, *row* order in a table is never guaranteed without an ORDER BY clause. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] order of reception of results at the time of a request SQL
Thank you to all, I thus will pay attention in my code and to put the columns in the order and thus not to use the "select *". It will be more careful. Denis Lurkin.
Re: [sqlite] order of reception of results at the time of a request SQL
> I have do many tests. It appears that the results of requests > each time are received in the same order. This order is that of > creation of the table. If I have make a CREATE TABLE X (int, char, > int, char), I receive the results of my request SQL in the good order: > int, char, int, char. However I made only a "select *". I did not > specify the name of the even put columns nor a "order by". Thus I have > the impression that I will always receive the results in the order of > creation of the table when I make a "select *". > > Can I in being sure? If it is possible, I would like several > opinions on the question. Just because it works today doesn't mean it will work tomorrow. If a new version of sqlite is produced that works differently your code will be broken. If the database table schema is changed by a user your code will be broken. The only way you are guaranteed it will work is by using an order by clause and explicitly calling out the columns..
Re: [sqlite] sqlite_busy in multithreaded application
On 7/21/05, Neelamegam Appadurai <[EMAIL PROTECTED]> wrote: > Hi, > Though they were lot of discussion on this, I could not find solution > in my case. To elaborate the happening. > Let me say i have two threads > 1.ReadThread > 2. DeleteThread > > My ReadThread does the following. > 1. Compile and Create SQLStatement > 2. Generate QueryObject > 3. Query and finalize . > 4. Reset Statement and finalize statement. > > My DeleteThread > 1.Compile and create SQLStatement > 2. Do Delete > 3. Reset and Finalize. > > I have implemented my lock mechanism which is such that Step2 and > Step3 are inside Mylock(only one thread can access) so that Delete > thread will not gain access to DB during the stage. > > I get SQLite_Busy, database is locked, from my deleteThread which can > happen only > between step 1 and step2 or between step 3 and step4 of read thread. > Is it neccessary for me to have reset statement of readThread also inside > lock. > I am using CppSQLiteWrapper. > Is it neccessary for me to have my lock before Step1 and unlock after Step4. > Thanks for your time > I don't think sqlite is happy creating two statements at once. -- Cory Nelson http://www.int64.org
Re: [sqlite] order of reception of results at the time of a request SQL
I have created a small 4 column d-base and if I do a "select * from table" sqlite always returns to me the order of the data in the order that it was listed in the create statement. (Actually, I based a lot of functionality in a small C++ program I wrote on this, and it has never failed. I've only recently modified it, to help ensure that it never fails.) Again, to ensure your getting the columns in the right order you could specify the order in a select statement. Puneet Kishor <[EMAIL PROTECTED]> wrote: On Jul 22, 2005, at 2:11 AM, LURKIN Denis wrote: > Hello, > > I have do many tests. It appears that the results of requests > each time are received in the same order. This order is that of > creation of the table. If I have make a CREATE TABLE X (int, char, > int, char), I receive the results of my request SQL in the good order: > int, char, int, char. However I made only a "select *". I did not > specify the name of the even put columns nor a "order by". Thus I have > the impression that I will always receive the results in the order of > creation of the table when I make a "select *". > > Can I in being sure? If it is possible, I would like several > opinions on the question. > why don't you try it? You are in the best position to answer this by just carrying out a few tests. However, as was pointed out in an earlier reply, the only way to be really sure is to NOT do a "SELECT * FROM table" but instead use named columns as in "SELECT a, b FROM table" By the way, ORDER BY has nothing to do with the order of the columns. It is not quite clear what you are trying to achieve -- a specific order of columns or a specific order of the results in the columns. Either way, specify what you want and ye shall receive that. Named columns will insure the former and ORDER BY will insure the latter. -- Puneet Kishor __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] order of reception of results at the time of a request SQL
On Jul 22, 2005, at 2:11 AM, LURKIN Denis wrote: Hello, I have do many tests. It appears that the results of requests each time are received in the same order. This order is that of creation of the table. If I have make a CREATE TABLE X (int, char, int, char), I receive the results of my request SQL in the good order: int, char, int, char. However I made only a "select *". I did not specify the name of the even put columns nor a "order by". Thus I have the impression that I will always receive the results in the order of creation of the table when I make a "select *". Can I in being sure? If it is possible, I would like several opinions on the question. why don't you try it? You are in the best position to answer this by just carrying out a few tests. However, as was pointed out in an earlier reply, the only way to be really sure is to NOT do a "SELECT * FROM table" but instead use named columns as in "SELECT a, b FROM table" By the way, ORDER BY has nothing to do with the order of the columns. It is not quite clear what you are trying to achieve -- a specific order of columns or a specific order of the results in the columns. Either way, specify what you want and ye shall receive that. Named columns will insure the former and ORDER BY will insure the latter. -- Puneet Kishor
Re: [sqlite] CURRENT_TIME returns false value
Why didn't I think to it ? thanks sqlite-users :-) Selon Joseph Bruni <[EMAIL PROTECTED]>: > whalesong:~ brunij$ date > Thu Jul 21 13:40:42 MST 2005 > whalesong:~ brunij$ sqlite3 /dev/null 'select current_time' > 20:40:44 > > > It works for me. I'm located at GMT-7. Where are you? > > Perhaps what you wanted was: > > select datetime('now','localtime'); > > > > > > On Jul 21, 2005, at 1:32 PM, Cyril Scetbon wrote: > > > Hi, > > > > I don't understand why when I use select CURRENT_TIME from sqlite I > > get a false value as follows : > > > > [EMAIL PROTECTED]:~$ date > > Thu Jul 21 22:37:20 CEST 2005 > > [EMAIL PROTECTED]:~$ sqlite3 /tmp/test.db "select CURRENT_TIME" > > 20:37:26 > > > > > > thanks > > > >
Re: [sqlite] order of reception of results at the time of a request SQL
Hello, I have do many tests. It appears that the results of requests each time are received in the same order. This order is that of creation of the table. If I have make a CREATE TABLE X (int, char, int, char), I receive the results of my request SQL in the good order: int, char, int, char. However I made only a "select *". I did not specify the name of the even put columns nor a "order by". Thus I have the impression that I will always receive the results in the order of creation of the table when I make a "select *". Can I in being sure? If it is possible, I would like several opinions on the question. Thank you in advance, Denis Lurkin.