Re: [sqlite] Compression
[EMAIL PROTECTED] wrote: Hello all, First of all, allow me to wish everyone a Happy New Year and I hope it'll be a good one for all. My question is (and I've raised this topic back in September, but didn't get back to it since), does anyone have a free/commercial add-on for SQLite v3 to perform on-the-fly compression/decompression of data, preferably on a field level (compress just one of the fields, not the whole table)? Thank you, Dennis I had a file size problem so I considered this. My googling didn't turn up any solutions and then, upon further thought, I decided that this probably wouldn't work for most applications. I think the data would have to be compressed on a field basis and in that case, I think you would only get good compression on fairly long fields and then only if you didn't ever want to use those in a query. (I have to assume that decompressing fields in order to find out whether they match a where condition would be deathly slow... I assume that it would cause a lot of problems with indexing as well...) So, under those conditions, it seems like you could move the compression/decompression out of SQLite and into your program... compress strings before you write them to the database; decompress strings after you're retrieved them. I may have a need like this. However, the original problem didn't fit this structure (no long strings), so I never pursued this... -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] absolute vs. relative path to database on Cygwin
Greg Miller wrote: You used a backslash, escaping the 'c' character. Notice that the error message refers to "c:cygwin" rather than "c:\cygwin", which isn't equivalent. Try "ls c:/cygwin" instead. I thought I had before posting... but you're right, this works! -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] absolute vs. relative path to database on Cygwin
Markus Hoenicka wrote: Well, that doesn't work, but the following does: sqlite c:/cygwin/usr/local/share/refdb/db/refdb i.e. absolute paths require a DOS-style drive letter. Are you doing this at the Cygwin prompt or Window's command prompt? My installation of Cygwin doesn't recognize DOS style paths at all: $ ls c:\cygwin ls: c:cygwin: No such file or directory -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] Row count in database
Christopher Petrilli wrote: Has anyone had any experience in storing a million or more rows in a SQLite3 database? I've got a database that I've been building, which gets 250 inserts/second, roughly, and which has about 3M rows in it. At that point, the CPU load is huge. Note that I've got syncing turned off, because I'm willing to accept the risks. Thoughts? Chris I have a database with almost 1.4 records in one table and over half a million in another. I treat them like they are read-only so I cannot advise you about write performance except to urge you to read the document on www.sqlite.org about performance... I do notice that doing anything like creating an index takes a while, as do joins. When I do, I hear my variable speed fan rev-up, so the load is definitely higher... Here's a silly idea: If you have enough RAM and you don't care about sync'ing then could you run things in memory... like on a RAM disk or buy one of those RAM hard-drives? -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] absolute vs. relative path to database on Cygwin
Markus Hoenicka wrote: Hi, I've just built 2.8.15 on Cygwin which worked without a hitch (thanks to those who added Cygwin support in the past). However, I came across a problem that makes working with databases a bit inconvenient. It seems like the library does not understand absolute paths, only relative paths. You can simply test this It's a bit of a long-shot, but have you tried the 'cygdrive' path syntax? $ sqite /cygdrive/c/cygwn/usr/local/share/refdb/db/refdb (assuming you installed Cygwin on C:\cygwin) How did you make it on Windows? I wonder if there is a switch in there somewhere that is tripping you up. -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
[sqlite] Database size
I have quite a lot of data. Naturally, as an SQLite 3.08 database, my data are significantly bigger than when they were a flat text file. As an aside, I'm very impressed with SQLite. Basic SELECTS in this 1.4 million record database are more or less instantaneous. The performance doing more complicated reads (e.g., with grouping and counts; doing LIKE searches) is extremely good. Anyway, when I browse the freshly created database (using xxd), I see long stings of zero bytes: 00033f0: 4255 4c4c 4354 3036 3631 312d 3337 3332 BULLCT06611-3732 0003400: 0d00 0900 6600 0066 00d1 0135 01a0 ..f..f...5.. 0003410: 0207 026f 02d4 0336 03bc ...o...6 0003420: 0003430: 0003440: 0003450: 0003460: 68b6 4b0a 001f 4d35 2123 ..h.K...M5!# 0003470: 1121 0030 3630 3436 3938 3435 434f 4d4d .!.060469845COMM Line 00033f0 is the end of one record and 0003470 is the start of the next. It looks like SQLite pads records with zero bytes? Is this necessary? Are there any options I can use to make the database use less disk space? Thanks! -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
Re: [sqlite] SQLite Tutorial - [Request For Comments]
D. Richard Hipp wrote: [EMAIL PROTECTED] wrote: The formatting seems all messed up after the 3rd example in the "ATTACH command" section. (As viewed from Mozilla.) I didn't notice any obvious formatting issues viewing it using Netscape 7.2 or Firefox Preview 1.0 (on Windows XP). I plan on printing this to read it carefully.. it looks very useful. I know there's considerable interest in using sqlite in the Free Pascal/Lazarus/Delphi/Kylix communities ... I don't think those bindings are mentioned. -Alan -- Alan Mead - [EMAIL PROTECTED] People often find it easier to be a result of the past than a cause of the future.
[sqlite] .import function
Maybe I'm blind but I've looked and don't see the answer to this... what is the format for the file to be imported using .import in the sqlite3 CLI database management tool? -Alan
Re: [sqlite] Why this SQL does not work?
Brass Tilde wrote: select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. I'm not the original poster.. but I'm trying to come up to speed on SQL/sqlite and I'm having some trouble understanding this... What is an example of a static subquery? The above is an example of a static sub-query. It's a query within a query. Hmm.. thanks but I'm still confused... isn't the above an example of a non-static query that used variables from the main query? -Alan
Re: [sqlite] Why this SQL does not work?
Gerhard Haering wrote: On Thu, Nov 11, 2004 at 09:40:44AM -0500, [EMAIL PROTECTED] wrote: [...] The following SQL does not work, complaint is "Error: no such column: B.GROUP_ID" select ID, (select SERVER_ID from A where A.ID=B.GROUP_ID) as GROUP_ID from B [...] Looks like you haven't read this resource, yet: http://sqlite.org/omitted.html It mentions, among others: Variable subqueries Subqueries must be static. They are evaluated only once. They may not, therefore, refer to variables in the main query. I'm not the original poster.. but I'm trying to come up to speed on SQL/sqlite and I'm having some trouble understanding this... What is an example of a static subquery? And how would one re-write this to eliminate the subquery? -Alan
