RE: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 ?
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Thu 10/6/2005 10:14 AM > Subject: Re: [sqlite] Problem/Bug: "SELECT 5 / 2;" returns 2 > <[EMAIL PROTECTED]> wrote: > > Richard, I like to ask you, just to make sure; has the `order by` > > "bug"/issue, as result from automatic float->int conversion, been > > considered by you and if yes, do you plan any changes? > > > > > You have a simple workaround: Just add 0.0 to anything > that you definitely want to be floating point. > > I will continue to trying to figure out a reasonable method > of addressing your concerns. In other databases the behavior in sqlite is the expected behavior. E.g. IBM db2udb, Ingres, Microsoft SQL Server. Oracle is different because of the wierd NUMBER datatype which is used to store both ints and decimal - I don't know about any others. In most databases the datatypes of the operands dictate the result type; which is the sqlite behavior. Chris
RE: [sqlite] Maintaining a sequence that's not rowid
> From:Dan Kennedy [mailto:[EMAIL PROTECTED] > Sent:Wed 10/5/2005 11:30 PM > /* Retrieve next id in sequence: */ > BEGIN; > SELECT id FROM id_allocator; /* This is the id to use */ > UPDATE id_allocator SET id = id + 1; > COMMIT; /* Can't use the id until the transaction successfully commits! */ Just a side note; Traditionally this is done the other way around, i.e. update then select. The reason for this is the (small) window in the critical section for another session to issue the select at the same time causing a deadlock. Locking the table exclusively at the beginnng of the transaction avoids that problem. Chris
RE: [sqlite] replaced awk mkopcode scripts now perl based
> On 5/23/2005 Doug Henry [mailto:[EMAIL PROTECTED] wrote:
> I have always used awka to convert awk scripts to C code,
> allowing for a native execuatable to be built, seems to be
> the most compatible solution.
>
That's cool! Thanks for the info on http://awka.sourceforge.net/ I'd not
seen that before.
Solaris awk compatibility issues can often be resolved by using "nawk"
(which is installed in the base OS along with awk). I've not tried using
sqlite under Solaris so this is more of a general comment, the SUN awk
is pretty basic. As Tiago pointed out gawk will behave as expected BUT
you do need to go ahead and install it.
Could a Solaris developer/user try using nawk? I just spent a few mins
getting sqlite-3.2.1.tar.gz from http://sqlite.org/download.html and
converted all the "awk" calls into "nawk" calls.
% diff ORIG_Makefile.in HACKED_Makefile.in
225c225
< | awk '{print $$5,$$6}' >last_change
---
> | nawk '{print $$5,$$6}' >last_change
326c326
< sort -n -b +2 opcodes.h | awk -f $(TOP)/mkopcodec.awk >opcodes.c
---
> sort -n -b +2 opcodes.h | nawk -f $(TOP)/mkopcodec.awk
>opcodes.c
329c329
< cat parse.h $(TOP)/src/vdbe.c | awk -f $(TOP)/mkopcodeh.awk
>opcodes.h
---
> cat parse.h $(TOP)/src/vdbe.c | nawk -f $(TOP)/mkopcodeh.awk
>opcodes.h
A quick "./configure ; make" on an old Solaris 2.6 box and I ended up
with an sqlite3 binary that handled a simple create/insert/select piece
of SQL; not conclusive but a good indicator. I don't have TCL so I
wasn't able to run the test suite.
Using nawk (for Solaris) would remove dependencies on extra tools (like
gawk or perl).
Chris
RE: [sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)
> -Original Message-
> From: Andrew Piskorski [mailto:[EMAIL PROTECTED]
> Sent: Sunday, May 02, 2004 12:09 PM
> To: Puneet Kishor
> Cc: D. Richard Hipp; SQLite
> Subject: Re: [sqlite] CONCAT in SQLite (was Re: [sqlite]
> correct syntax
> for CASE...)
>
>
> On Sun, May 02, 2004 at 01:59:34PM -0500, Puneet Kishor wrote:
>
> > >Probably you mean the concatenate operator, which in
> > >SQL is ||, not &. & is not an SQL operator as far as
>
> > drats... concat is '+' in Javascript, '.' in Perl, '&' in
> Access and
> > SQL Server, '||' in SQlite, and, well, 'CONCAT' in Oracle.
> Why can't
> > the entire world just speak Hindi ;-).
>
> It is '||' in Oracle, and I believe PostgreSQL as well. 'CONCAT' may
> also work there, but if so I've never seen it used.
>
CONCAT is used in a few DBMS's as it allows one to use a function call instead of an
(infix?) operator.
E.g.
select concat('Hello', ' world') from mytable
Obviously many nested uses of concat() can look rather hairy...
As a side note SQL Server also uses "+" (I think "&" was just added to keep Access
users happy :-p).
Chris
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQLite and ODBC/JDBC driver
> -Original Message- > From: Jean-Eric Cuendet [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 06, 2004 6:06 AM > To: [EMAIL PROTECTED] > Subject: [sqlite] SQLite and ODBC/JDBC driver > > There is an ODBC driver here: http://www.ch-werner.de/sqliteodbc/ > But there is no client/server, the server is embedded in the > ODBC driver > on the client. > > I think that we could modify the ODBC driver to communicate through > sockets with the SQLite on the server but that would be a lot > of work.. Sounds like you want an ODBC proxy server. I've not used one but there are some floating around, just "google" for odbc proxy. This one looks interesting http://www.fastflow.it/dbtcp/ SQLRelay can do a similar job (and can connect to ODBC) BUT it does not have an ODBC client api, it has it's own. Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] SQL engine on our file formats
> -Original Message- > From: Jean-Eric Cuendet [mailto:[EMAIL PROTECTED] > Sent: Friday, March 19, 2004 8:07 AM > To: [EMAIL PROTECTED] > Subject: [sqlite] SQL engine on our file formats > > > Hi, > We have our own files containing data for our automatic > testing line. We > would be interested to serve these informations through SQL. One idea > would be to put them in an SQL database but we would like to > stick with > our text only files: easy to manage, to edit, to version. If you are wedded to the text files, then the only options I can see are: 1) Re-import the text files into an sqlite database (in the background) when ever the times stamps of the text files have changed - a poor mans replication, its not really a good idea if the data set is large. 2) Don't use sqlite, use something else. Options off the top of my head: shsql - http://midriff.sourceforge.net/sqlman/html/Contents.html Perl DBI modules that can use text files via SQL (there is at least on CSV based one) ODBC driver similar to perl DBI modules that access text files via SQL interface (I've not seen any ODBC drivers that do this under Linux, I've only seen these under win32). JDBC of the same form. Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Re: OK to drop support for legacy file formats?
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Friday, February 06, 2004 6:13 AM > To: [EMAIL PROTECTED] > Subject: [sqlite] Re: OK to drop support for legacy file formats? > > > D. Richard Hipp wrote: > > > > I am proposing to drop support for this auto-update feature. > > Beginning with 2.8.12, if you attempt to open a database file > > built using version 2.5.6 or earlier, the open attempt will > > fail (with an appropriate error message). You will have to > > update the database file manually. > > > > What if instead of refusing to open the database at all, > it opened the database read-only. Would opening older > databases read-only mitigate any hardships that refusing > to open older databases might cause? > My 2 cents, that sounds like the most user friendly approach, that way if a user has an old format database BUT for some inexplicable reason they only have the newer version of sqlite; they could at least use the sqlite terminal monitor to ".dump" it and re-create it via the usual: sqlite old.db .dump | sqlite new.db Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Fatal error loading large table
> -Original Message- > From: Tom Poindexter [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 11, 2003 4:01 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] Fatal error loading large table > > > I'm trying to load a fairly large table (~ 7.5 million rows, > 1.5 gb of raw > data) with the 'copy' command, and I'm getting a sqlite fatal error: > > sqlite> copy or replace tab1 from 'tab1.can'; > sqlite> select count(*) from tab1; > 31624 > sqlite> copy or replace tab2 from 'tab2.can'; > SQL error: database disk image is malformed > > The 'tab1' copy works fine, just under 30 seconds to load the > 31,624 rows. > In loading 'tab2', the copy runs for 50 minutes (or longer, I > didn't time it), > The database is empty except for the table definitions. My > platform is > AIX 5.2, sqlite complied with gcc. I'm not a frequent user > of AIX; sqlite > seems to work fine, as does tclsqlite.so . > This is just a workaround/diagnostic suggestion; what happens if: a) you commit after the 1st copy OR b) you copy tab2 first then copy tab1? If tab1 copy was an example to show that sqlite copy was working I guess my suggestions are moot :-). The other thing to consider; have you seen a working >2Gb sqlite database under AIX yet? I know sqlite supports big databases but it might be platform specific. Have you seen the size of the files in the database dir near the 50 min mark/end of copy? Good luck, Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Win32 coders: change os.c?
> -Original Message- > From: Arthur Hsu [mailto:[EMAIL PROTECTED] > Sent: Friday, November 21, 2003 12:24 PM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Win32 coders: change os.c? > > > Not quite. IMHO, it's better that os.c catches this UNICODE > macro, and then > uses MultiByteToWideChar and WideCharToMultiByte inside. The > other way is > to use an assert(FALSE) to prevent compilation using UNICODE, > then the naive > programmers like me will know something needs to be taken care of. > That's how we deal with this with the WinCE port of sqlite where we only have the Wide versions of the functions available, however to remain consistent with the single/multi byte filenames, etc. we use the Wide<->MultiByte. I concur with Arthur, I'd prefer the existing function names to be used. Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] sqlite with eVC++
> -Original Message- > From: Eric Pankoke [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 20, 2003 10:06 PM > To: [EMAIL PROTECTED] > Subject: [sqlite] sqlite with eVC++ > > > Is there anyone on this group that uses sqlite with Embedded > Visual C++ for > the PocketPC? If so, what do I need to do to get a project > with sqlite > commands to compile? Any help would be greatly appreciated. Thanks. > Check out http://sqlite-wince.sourceforge.net/ its not been updated in a while so its the 2.80 version. Good luck, Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Performance problem
> -Original Message- > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > > MySQL has stated in-documentation that it uses a B-tree for > it's index. > I think this is a mistake- especially for larger indexes. > Using several B-trees attached to a hash-table is much faster > (if the hash is fast or your data is uniform). Following this train of thought (this isn't a feature request!); some DBMS's support different structures for tables/indices and the DBA can specify the required structure depending in the expected data usage/patterns (and a 2ndary index need not be the same structure as the primary table structure, allowing for, say, a hash table and b-tree 2ndary's as per the example above). E.g. Ingres has; Heap (yep, completely unstructured), B-tree, Hash, and ISAM (there is also an R-tree but that is only for spatial datatypes so it's not as interesting for this discussion). It all depends on the data and how it is used as to which structure should/could be used. A typical example of the hash primary and b-tree 2ndary is a unique customer id so that the customer record can be hit directly with the hash, or if the hash is not perfect, through a couple of overflow pages (compared to a b-tree which always will need to jump through a few pages in the index, admittedly that may only be an improvement of microsecs versus millisecs in lookup time). The b-tree 2ndary would then be for things like customer name (which are often "duplicated", potentual for lots of people called "Mr smith") in case one needs to perform searches on a customer name (who say, forgot their customer id). - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] (Fwd) Re: [sqlite] Testing the new SQLite mailing list
> -Original Message- > From: Bert Verhees [mailto:[EMAIL PROTECTED] > Tell us which emailclients we should use, maybe we can learn > from you, > do not forget, a part of sqlite-users is Windows-user, and sqlite is > also a Windows-product. > For me, I use Windows and Linux, as it comes. But I do not > want to start > another computer just because I want to answer to a sqlite-list-email > I've been quiet on this debate as but I thought this line of thinking was a good idea. I wouldn't presume to suggest people use a specific email client but I (have) to use MS outlook (2000). So here is how I deal with sqlite postings (and other mailing lists), most of this stuff is obvious but it's a start. I admit this off topic to sqlite so I'll keep it short: If I want to respond to a message I hit the "Reply to All" button, sometimes I'll clean up the to/CC fields (I've not bothered today so this is going to Bert, Gerhard and CC'ing the list). Most of my email exchanges (not on the sqlite list) are between multiple people so the All button is what I often use anyway. To deal with filing sqlite mailing list emails (I receive individual ones) I have a rule setup that looks a string in the header, the string I look for is the list id string "List-Id: SQLite Users Mailing List ", on a a match it gets moved into an sqlite folder. I'm not sure what email client I would choose if given a free choice (I have no choice so why look), probably Mozilla or the Thunderbird spin-off. Hope this helps, Chris - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
