Re: [sqlite] Reading strings and blobs

[Nuno Lucas]

[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

[Shawn Walker]

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

[Roger Binns]

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

[D. Richard Hipp]

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]>

Re: [sqlite] Question about sqlite TEMP table

[D. Richard Hipp]

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] Basic qns about TEMP table

[R S]

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

[Ming Qiang Lin]

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.

[Mrs. Brisby]

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.

[Mrs. Brisby]

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 

RE: [sqlite] Multi-threading.

[D. Richard Hipp]

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.

[Tim McDaniel]

> -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.

[Jay Sprenkle]

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.

[Paul G]

- 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.

[Mrs. Brisby]

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

[Chuck Pahlmeyer - MTI]

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

[Chuck Pahlmeyer - MTI]

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

[Clark Christensen]

--- 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

[Austin Ziegler]

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

[LURKIN Denis]

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

[Jay Sprenkle]

> 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] order of reception of results at the time of a request SQL

[Nicholas Choate]

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] CURRENT_TIME returns false value

[cyril . scetbon]

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

[LURKIN Denis]

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.

[sqlite] sqlite_busy in multithreaded application

[Neelamegam Appadurai]

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