[sqlite] Re: Fastest way to check if new row or update existing one?

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 19:30]: > If row 50 does not exists it does nothing and I seem not to get > any return to know? http://sqlite.org/c3ref/changes.html > using pysqlite. I don’t know anything about pysqlite, but apparently you are looking for the `rowcount` attr

[sqlite] Re: Fastest way to check if new row or update existing one?

* Mag. Wilhelm Braun <[EMAIL PROTECTED]> [2007-12-25 15:10]: > QUESTION: is there a better way to make this important > decision? using Sqlite 1. If you are changing the entire row on every update, you can simply use `INSERT OR REPLACE` (assuming there is a UNIQUE column) to always do this i

[sqlite] Re: Improving performance of SQLite. Anyone heard of DeviceSQL?

* John Stanton <[EMAIL PROTECTED]> [2007-12-15 22:55]: > Which is the better model? False dilemma. Where there is a budget, there is no reason you can’t have both a good product and at least decent marketing. Even when the product isn’t good, it’s unlikely to be so useless as to violate the terms

[sqlite] Re: DeviceSQL

* John Stanton <[EMAIL PROTECTED]> [2007-12-12 17:55]: > In general claims of "20x" or even "5x" imply either serious > deficiencies in the compared product or a generous dose of > snake oil in the challenger. Depends. The outline given by Dr. Hipp about the product’s features may the claim quite

[sqlite] Re: .dump/.load not workin in 3.4.0 or later for "large" rows?

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-12-11 20:15]: > But, as has been pointed out, you can work around it using > a compile-time switch: > >gcc -DSQLITE_MAX_SQL_LENGTH=10 shell.c sqlite3.c -o sqlite3 > > I should probably modify the makefile to do this > automatically... Maybe

[sqlite] Re: In Mem Query Performance

Hi RaghavendraK, * RaghavendraK 70574 <[EMAIL PROTECTED]> [2007-06-25 08:45]: > When the DB is opened in "in Memory mode",performance of query > does not improve. For table which has 10 columns of type Text > and each column having 128bytes data and having a total of > 1 records. that is smal

[sqlite] Re: CAST

* Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]: > SQLite's typelessness is an asset if you work only with SQLite > but in any application that uses multiple database engines of > which SQLite is only one supported engine, the non-standard > typelessness is something that has to be worked a

[sqlite] Re: Problem with Unicode surrogates

* Jiri Hajek <[EMAIL PROTECTED]> [2007-05-16 14:40]: > As for security exploits, I don't see any, Unicode 4.0 standard > allows applications to ignore such incorrect characters. The Unicode standard is beside the point. There is lots of code that does not handle charsets and encodings correctly, w

[sqlite] Re: Help wiith SQL - first row of each group

* Ed Pasma <[EMAIL PROTECTED]> [2007-05-07 10:28]: > This solution may is tricky but has occasoinaly helped me. It > is written here dedicated for the example data. For real data > the leftpadding should likely be increased to the content of > the sorting key. Also the result may need to be convert

[sqlite] Re: Powered by SQLite image?

Hi Alberto, * Alberto Simões <[EMAIL PROTECTED]> [2007-05-04 22:10]: > Ok, I had some time today. What do you think of the one shown > in: http://dicionario-aberto.net/bin/dic.pl excellent! That looks exactly the way I imagined such a button should look. :-) * Alberto Simões <[EMAIL PROTECTED]>

[sqlite] Re: DB design questions

Hi Michael, * Michael Ruck <[EMAIL PROTECTED]> [2007-04-21 22:35]: > Thanks for your response. Do you have a recommendation for a > simpler data store, which supports only simple queries (like, > equals, not equals on attributes) and transactions? BerkeleyDB might be a candidate. It only stores k

[sqlite] Re: DB design questions

* Michael Ruck <[EMAIL PROTECTED]> [2007-04-20 16:15]: > Is there anyone who has experience with this kind of design, do > you have better ideas on modelling this kind of data? This is actually a very typical approach to storing arbitrarily structured data entities in an SQL database that everyone

[sqlite] Re: Some questions on hierarchical data (nested set model)

Hi Jef, * Jef Driesen <[EMAIL PROTECTED]> [2007-04-11 16:10]: > I managed to solve this problem now. I had to write my solution > (see below) in pseudo sql code (e.g some extra non-sql code was > required) because sqlite does not support stored procedures. It > think it is doable to incorporate th

[sqlite] Re: Some questions on hierarchical data (nested set model)

Hi Jef, * Jef Driesen <[EMAIL PROTECTED]> [2007-04-06 11:20]: > Q1. Which is more efficient? Two simple queries or one self > join? > > I have seen two different types of queries to retrieve a tree. > The first one uses two very simple queries: > > SELECT lft, rgt FROM tree WHERE name = @name; >

[sqlite] Re: storing funky text in TEXT field

* Clark Christensen <[EMAIL PROTECTED]> [2007-04-05 17:25]: > I hate it when the CGI transaction clobbers characters. You > can set the content-encoding in the HTML to UTF-8, and it might > help, but I think the conversion from the urlencoded value is > dependent on the web server platform's encod

[sqlite] Re: SQL and SQLite pronounciation?

* Dennis Cote <[EMAIL PROTECTED]> [2007-04-04 22:30]: > I prefer "ess cue el" and "ess cue light" myself. That’s what I say. Regards, -- Aristotle Pagaltzis // - To unsubscribe, send email to [

[sqlite] Re: Fwd: database is locked error on Mac OS X

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-03-10 17:30]: > Do "configure". Then edit the Makefile that is generated. It would be nice if these options were mapped to `--enable-foo` switches on the configure script… would that take a lot of work? (Alternatively, if you don’t want to do it yours

[sqlite] Re: UNIQUE constraint on column

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-02-04 13:35]: > "A. Pagaltzis" <[EMAIL PROTECTED]> wrote: > > It's a pity that INSERT OR IGNORE (apparently?) does not set > > last_insert_id properly regardless of outcome, > > Consider this case:

[sqlite] Re: Memory database to file

* David Champagne <[EMAIL PROTECTED]> [2007-02-01 15:45]: > I suppose since no one replied to this, that it's not possible > to do it. Just wanted to confirm. Thank you... http://en.wikipedia.org/wiki/Warnock%27s_Dilemma :-) Regards, -- Aristotle Pagaltzis //

[sqlite] Re: UPDATE OR REPLACE same as UPDATE?

* Joe Wilson <[EMAIL PROTECTED]> [2007-02-04 00:25]: > Does anyone know whether UPDATE OR REPLACE is portable to any > other popular database? Not to MySQL. I don’t have any experience with other engines, much as I wish. (I’d much prefer PostgreSQL but I have no choice.) Regards, -- Aristotle Pa

[sqlite] Re: UNIQUE constraint on column

* Dennis Cote <[EMAIL PROTECTED]> [2007-02-03 17:20]: > I suspect the reduction from executing three statements > (insert, select, insert) down to only two (insert insert) would > probably provide about the same performance increase as the 5% > to 10% speedup he saw by replacing the separate select

[sqlite] Re: UNIQUE constraint on column

* Dennis Cote <[EMAIL PROTECTED]> [2007-02-03 01:30]: > If you wanted to model what the insert or ignore is doing more > directly, you could do something like this. > > rowid = select rowid from Strings where value = 'foo'; > if rowid is null then >insert into Strings values ('foo'); >row

[sqlite] Re: UNIQUE constraint on column

* Nicolas Williams <[EMAIL PROTECTED]> [2007-02-01 00:10]: > Sounds like you should want to use INSERT OR IGNORE ... INTO > Strings and then SELECT the rowid of the string for use in > INSERTing INTO Object. That was my first thought as well. Does SQLite return the row’s last_insert_id correctly i

[sqlite] Re: Shared Lock Transactions

* Jay Sprenkle <[EMAIL PROTECTED]> [2007-01-22 15:20]: > My understanding was that a "shared lock" is a metaphor, and > IMHO, a fairly stupid one. If you lock a room, nobody else can > get in, it's not a mechanism for sharing, it's a mechanism for > preventing sharing. Reasoning by analogy rarely

[sqlite] Re: Shared Lock Transactions

* Jay Sprenkle <[EMAIL PROTECTED]> [2007-01-22 01:40]: > This makes little sense. There are no 'shared' locks. I’m not sure where you got this idea, but shared locks are an OS-level concept in Unix. You cannot acquire a shared lock on a file as long as there are exclusive locks on it, and you cann

[sqlite] Re: does select reliably return insert order?

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-01-20 23:55]: > > I believe that the SQL spec doesn't specify the order of > > returned rows if you don't specify an order. > > SQL doesn't, but SQLite might. Without maintaining order OFFSET > (also not part SQL standart) without ORDER BY wouln't make

[sqlite] Re: .mode html output character conversion

* T&B <[EMAIL PROTECTED]> [2007-01-06 13:05]: > When using SQLite's HTML output mode it converts some > characters to HTML code, such as: > > & -> & > < -> < > > But doesn't for other characters, such as: > > > -> > > " -> " > ' -> ' > © -> © (copyright symbol) > all other non-ascii characte

[sqlite] Re: When to release version 3.3.10?

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2007-01-05 13:15]: > The question is: should I rush out 3.3.10 to cover this > important bug fix, wait a week to see if any other bugs > surface, or do the usual 1-2 month release schedule and > let people effected by this bug apply the patch above. I think

[sqlite] Re: What about Foreign Key support (when?)

* Marc Ruff <[EMAIL PROTECTED]> [2007-01-05 17:35]: > So next feature to expect is Foreign Key constraints!? Great! > > When? Any plans yet? You can use triggers to enforce FKs until they are natively enforced:

[sqlite] Re: sqlite performance, locking & threading

* Emerson Clarke <[EMAIL PROTECTED]> [2006-12-30 15:15]: > My complaint, if you want to call it that. Was simply that > there are seemingly artificial constraints on what you can and > cant do accross threads. They are not artificial. The options for making the API of a library with complex intern

[sqlite] Re: multiple selects in a single prepare

* Igor Tandetnik <[EMAIL PROTECTED]> [2006-12-29 17:35]: > Why not > > select state, count(*) > where state in ('Normal', 'Critical') > group by state; > > ? Clever! * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-12-29 17:40]: > SELECT count(state='Normal'), count(state='Critical') FROM tbl1;

[sqlite] Re: multiple selects in a single prepare

* chetana bhargav <[EMAIL PROTECTED]> [2006-12-29 17:20]: > Actually I am trying to retrieve values in a single step. > > My queries need to be something like, > > select count(*) from tbl1 where state='Normal';select count(*) from tbl1 > where state='Critical' > > I got to have these two as se

[sqlite] Re: multiple selects in a single prepare

* chetana bhargav <[EMAIL PROTECTED]> [2006-12-28 06:00]: > Just wanted to know can we have multiple quries in a single > prepare statement seperated by semicolons.Something like, > > Select count(*) from tbl where name="foo";select count(*) from tbl1 where > name = "bar" Just how is that suppos

[sqlite] Re: converting from mysql?

* Gussimulator <[EMAIL PROTECTED]> [2006-11-12 18:00]: > what are the available tools to convert a mysql db onto this > engine ( sqlite3 ) ? thanks - i can take scripts (php, phyton, > perhaps ruby although i dont have it installed now) but any > tool for windows would suffice too! SQL databases

[sqlite] Re: Is it bad to...

* David Gewirtz <[EMAIL PROTECTED]> [2006-11-10 02:45]: > In my ongoing attempt to find the best approach for integrating > SQLite into a threaded Web-serving environment, I came upon > a truly crude kludge, and I wanted to know if it's bad from an > SQLite architectural standpoint. I know it's bad

[sqlite] Re: Date data type

* Clark Christensen <[EMAIL PROTECTED]> [2006-11-07 18:05]: > If I had it to do over, I would probably store my datetimes as > -MM-DD HH:MM:SS strings. Make that a “T” instead of a space, and add timezone offset info (either “Z” for UTC or “+HHMM” for an offset), then you have RFC 3339 datetim

[sqlite] Re: Regarding sqlite3_exec

* Da Martian <[EMAIL PROTECTED]> [2006-10-25 15:05]: > Its was not meant as an insult, however you did set the tone > with your post (ala: Either you want the data from the query, > or you don't.). I mearly responded in kind. If you live in > glass houses dont throw stones and all that. I mean its

[sqlite] Re: Regarding sqlite3_exec

* Da Martian <[EMAIL PROTECTED]> [2006-10-25 14:15]: > 1) If there are too many results the user will have to wait > a long time before they see anything because we will be > buffering away results. The application will appear slugish. > The user would get bored. I can point you to dozens of studie

[sqlite] Re: Regarding sqlite3_exec

* Da Martian <[EMAIL PROTECTED]> [2006-10-25 11:35]: > >What prevents you from doing the same yourself? Just collect > >the data in a memory buffer as you get it, before processing > >it. > > Nothing but effiency as discussed. Basically, as Mr Cote has > said, its either a count(*) query or O(n) s

[sqlite] Re: Regarding sqlite3_exec

* Da Martian <[EMAIL PROTECTED]> [2006-10-24 16:15]: > It appears that DBs like oracle etc.. get the whole resultset > into temporary memory/space and then return the query (at least > for unions this appears true), although this is just based off > assumptions based on observations. What prevents

[sqlite] Re: new sqlite-based webserver

* Paul M <[EMAIL PROTECTED]> [2006-10-20 17:35]: > Can this server handle multipart form uploads(multiple files > from one form)? I remeber in perl I had to implement the > support for that and regular form submissions manually. I tell > you that was a pain. You didn’t use the CGI module, did you?

[sqlite] Re: SQLite Order BY

* John Stanton <[EMAIL PROTECTED]> [2006-10-09 19:35]: > Sorting data is time consuming, a physical law is involved. At > best it is an nlog(n) process. Only when you sort by comparing elements with each other. Bucket sort runs in O(n), f.ex. And quantum sort is O(1). ;-) Algorithms that run faste

[sqlite] Re: The term "flat-file" as applied to sqlite

* Griggs, Donald <[EMAIL PROTECTED]> [2006-09-25 22:10]: > 2) If instead, I'm unaware of another popular use of the term > "flat file" -- then I'd be glad to learn this. I think there's another explanation for how this term came about. Dr. Hipp has asserted many timed that SQLite should be thought

[sqlite] Re: format for column names

* Nikki Locke <[EMAIL PROTECTED]> [2006-08-30 14:40]: > Using [] is a Microsoft thing. More portable to use double > quotes... I’d use the square brackets anyway. sqlite> create table foo ( "bar baz" text ); sqlite> insert into foo values ( "quux" ); OK… sqlite> select [bar baz] fro

[sqlite] Re: Seems like a bug in the parser

* Alexei Alexandrov <[EMAIL PROTECTED]> [2006-08-23 09:20]: > All other databases I know will complain if you give them this query. Except MySQL, glory that it is. Regards, -- Aristotle Pagaltzis // --

[sqlite] Re: Foreign Keys

* John Newby <[EMAIL PROTECTED]> [2006-08-10 00:25]: > it recognises them if you put them in your create table > statement but it does not enforce them, is this correct? Yes. But note that you can retrofit enforcement yourself. Take a look at Enforce Foreign Key Integrity in SQLite with Trigg

[sqlite] Re: Reading the same table from two threads

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-07-26 16:40]: > It has been suggested that I add a mutex to every SQLite > database connection. Client code can easily use trivial wrappers to supply such serialisation for itself, though. > The downside is the (minimal) additional overhead of acquiri

Re: [sqlite] reg:sqlite usage

* sandhya <[EMAIL PROTECTED]> [2006-07-19 14:10]: > Also you wanna want to say that we shouldn't use this in Client > /Server applications.Like,Connecting to the Sqlite server > through the application and performing all the operations > through(application) it and updating the server. Not “should

Re: [sqlite] optimizing your sql

* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-11 20:35]: > I think perhaps the correlated subquery optimization is really > another name for rewriting it so the smallest table is the > driving table. It probably doesn't matter how you write the sql > as long as you get the smallest table as the drivi

Re: [sqlite] optimizing your sql

* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-11 20:15]: > On 7/11/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote: > >* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-10 17:30]: > >> // - Use SQL Joins instead of using sub-queries > > > >Not so fast there. I ha

Re: [sqlite] optimizing your sql

* Jay Sprenkle <[EMAIL PROTECTED]> [2006-07-10 17:30]: > // - Use SQL Joins instead of using sub-queries Not so fast there. I have accelerated queries by several 100 percent by turning joins into subqueries. On other occasions I did so by turning subqueries into joins. The performance of joins vs

Re: [sqlite] Multiple Users

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-07-03 21:35]: > How many Users can be reader/writer to a DB at the same time, > if all User controlled by the logical Locking-System? > > I have searched in Mail-Archiv and in Docu, but I dont found > any Infos about concurrent Users. That’s because

Re: [sqlite] how make a case insensitive index

* Wilfried Mestdagh <[EMAIL PROTECTED]> [2006-06-26 15:45]: > How to make a case insensitieve index ? Add `COLLATE NOCASE` to the column definition. See http://www.sqlite.org/datatype3.html Regards, -- Aristotle Pagaltzis //

Re: [sqlite] Need sql query help

* onemind <[EMAIL PROTECTED]> [2006-06-25 17:00]: > The thing is, i am going to need to use different letters each > time to search through over 200,000 words in a database and it > needs to be fast. 200,000 words is nothing. If they’re 5 letters on average, that’s some 1.1MB of data. You can grep

Re: [sqlite] Need sql query help

* Ulrik Petersen <[EMAIL PROTECTED]> [2006-06-25 17:55]: > 5) Use the function with the regex '[spqd]' to search for words > containing the letters "s", "p", "q", OR "d". Doing it for all > letters (AND) may be doable with a single regex, It is doable with an NFA engine like PCRE, but it’s compli

Re: [sqlite] problem with creating a table

* Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 17:05]: > A. Pagaltzis wrote: > >.headers on > >SELECT [order] FROM foo > > But how would you know the name of the col is "order" not > "[order]"? That’s what `.headers on` was supposed

Re: [sqlite] problem with creating a table

* Bijan Farhoudi <[EMAIL PROTECTED]> [2006-06-25 16:35]: > Thanks for your answer but still I am getting an error message: > sqlite> create table foo(i integer, [order] integer); > sqlite> .sch > CREATE TABLE foo(i integer, [order] integer); > sqlite> insert into foo values(1,2); > sqlite> select o

Re: [sqlite] Need sql query help

* onemind <[EMAIL PROTECTED]> [2006-06-25 16:05]: > If i had a table wit a word column that had a huge list of > words and i wanted to select every word that contained all > these letters "qdsa". SELECT * FROM words WHERE word LIKE '%q%' AND word LIKE '%d%' AND

Re: [sqlite] Improving insert speed?

* Tony Harris <[EMAIL PROTECTED]> [2006-06-24 19:05]: > Is this about average, or is there a way I might be able to get > a little more speed out of it? Put a transaction around your INSERTs, at least around batches of a few thousand each, and you’ll get much better speed. Regards, -- Aristotle

Re: [sqlite] Avoiding duplicate record insertion

Hi David, * David D Speck <[EMAIL PROTECTED]> [2006-06-18 06:25]: > What would the most elegant way be to insert a name and address > entry into the main table ONLY if it is not already there? I > could see doing a SELECT WHERE lname = new_lname AND fname = > new_fname, AND street = new_street, et

Re: [sqlite] Avoiding Fragmentation of SQLite database file ???

* Florian Weimer <[EMAIL PROTECTED]> [2006-06-14 18:50]: > * Jay Sprenkle: > > On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote: > >> Any solution to that (which does not force end-user of app > >> to manage sqlite file fragments or to defragment disk) ? > > > > A scheduled task or cron job is

Re: [sqlite] Problems with multiple threads?

* Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 22:55]: > It's entirely possible I'm reading these docs incorrectly, but > this strategy has worked quite well for me. No, I don’t see any error in your reading. My apologies; I should have consulted the docs instead of going by mailing list posts. I

Re: [sqlite] Problems with multiple threads?

* Pat Wibbeler <[EMAIL PROTECTED]> [2006-06-07 20:50]: > Beginning everything with BEGIN IMMEDIATE should eliminate the > possibility of deadlock, but you will serialize read-only > operations. Why? BEGIN IMMEDIATE acquires a for-read lock. Multiple for-read locks can be acquired concurrently. It

Re: [sqlite] Random error SQLITE_CANTOPEN on Windows XP SP2 with sqlite3_exec

* Roger Binns <[EMAIL PROTECTED]> [2006-06-05 00:05]: > I don't know if anyone has tracked how well Spotlight (Mac) or > Beagle (Linux) work with SQLite files. They cause no problems. The semantics of the filesystem API on *nix systems differ from those of Windows; in short, a file and its name ar

Re: [sqlite] [Video] An Introduction to SQLite

* Joe Wilson <[EMAIL PROTECTED]> [2006-06-04 17:55]: > In the video DRH mentioned that he plans to work on random > access of BLOBs. I found the part where he talks about the test suite and how static typing hides mistakes very cool. (Enough so that I intend to transcribe those.) His mention of u

[sqlite] [Video] An Introduction to SQLite

Hi, for those who haven’t noticed, a video of a 45-min talk by Dr. Hipp about SQLite that he gave at Google has been posted on Google Video: http://video.google.com/videoplay?docid=-5160435487953918649 Not much nitty-gritty, but a nice 20,000 ft view; there isn’t much news to me in it, but there

Re: [sqlite] Strange behavior with sum

* Klint Gore <[EMAIL PROTECTED]> [2006-06-02 07:30]: > sqlite> select cast(sum(f1) as double)/cast(sum(f2) as double) from bob; > 0.869779988128673 Just casting one of them is sufficient, btw. Regards, -- Aristotle Pagaltzis //

Re: [sqlite] .import difficulty

* cstrader232 <[EMAIL PROTECTED]> [2006-06-02 15:40]: > strange error though because ";" can't be part of a tablename > can it? sqlite> create table [b;] (a,b,c); sqlite> select * from sqlite_master; type name tbl_name rootpage sql -

Re: [sqlite] .import difficulty

* cstrader232 <[EMAIL PROTECTED]> [2006-06-02 15:00]: > I'm having trouble importing. I'm using sqlite3 from dos. > > sqlite3 test.db; > create table b (a, b, c); > .import "test.txt" b; > > returns "no such table b" No, it reports `Error: no such table: b;` – note the semicolon. A table called

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

* Jay Sprenkle <[EMAIL PROTECTED]> [2006-05-30 17:00]: > You could sign up an autoresponder email account (like paypal) > and it would stay signed up forever. It would always respond to > the query email with a reply including the original text of the > message. You'd need to set it up so they had

Re: [sqlite] LIMIT and paging records

* Mikey C <[EMAIL PROTECTED]> [2006-05-29 17:10]: > Do you imagine Google loads 8 billions records into memory when > the user is just viewing 10 results in page 5 after a broad > search? You can’t ask Google for more than the first 1,000 hits on any search. (Go ahead and try.) There is a reason f

Re: [sqlite] Purging the mailing list roles.

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-29 12:50]: > I wonder if I need to implement some kind of mechanism that > requires you to either send a message to the mailing list or > else renew your subscription every 3 months. Does anybody have > any experience with other mailing lists that re

Re: [sqlite] seeking answers for a few questions about indexes

* Brannon King <[EMAIL PROTECTED]> [2006-05-26 21:35]: > I would think this would be a valuable optimization for Sqlite > to perform automatically. When it finds an OR operation, it > should look to see if duplicating the query and using the UNION > ALL compounder would allow it to use additional i

Re: [sqlite] can you speed this query up?

* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-23 09:40]: > Now we can group together the conditions which do not involve > the `bounds` table: > > (r.qi = 5604 AND r.ri <= 5468) OR (r.ri = 5468 AND r.qi <= 5604) > AND r.qi >= b.bqis > AND

Re: [sqlite] can you speed this query up?

* Adrian Ho <[EMAIL PROTECTED]> [2006-05-23 16:05]: > On Tue, May 23, 2006 at 08:50:56AM +0200, A. Pagaltzis wrote: > > * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]: > > > What you have to do is: > > > > > >SELECT qi, ri, drl,

Re: [sqlite] can you speed this query up?

Hi Brannon, * Brannon King <[EMAIL PROTECTED]> [2006-05-23 20:05]: > Thank you for taking the time to sort out my query! NP. I have only recently studied SQL in depth, and this was an interesting exercise. > This one above was as slow as the original. Yes, as expected – it is exactly the same

Re: [sqlite] can you speed this query up?

* Brannon King <[EMAIL PROTECTED]> [2006-05-23 01:40]: > It seems that I yet need help with another query. This one is just too slow. > I've included the "explain" and the table schema. I've been using the > prepare/step model directly. What should I change on my indexing to make it > faster? > >

Re: [sqlite] can you speed this query up?

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]: > What you have to do is: > >SELECT qi, ri, drl, score > FROM ... > WHERE score=(SELECT max(score) FROM ...) Actually, in cases such as this, the easiest approach is to use `LIMIT`: SELECT qi, ri, drl, score FROM ..

Re: [sqlite] can you speed this query up?

Hi Brannon, * Brannon King <[EMAIL PROTECTED]> [2006-05-23 05:35]: * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-23 02:35]: > >select qi, ri, drl, max(score), min(score) from ... > > > >What values of qi, ri, and drl would you want > >this query to return? > > > >What you have to do is: > > > >

Re: [sqlite] Integer / Numeric calculations

* Mikey C <[EMAIL PROTECTED]> [2006-05-22 08:00]: > If have tried cast both rating and votes and the result to NUMERIC but still > an integer. Cast one of them to REAL. SELECT CAST( rating AS REAL ) / votes FROM foo; Regards, -- Aristotle Pagaltzis //

Re: [sqlite] Adding colomns when the database/schema already exists

Hi strafer, * [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-13 19:25]: > The 'Time' string represents a colomn in the database. If the > database does not contains this colomn, I'd like to add it with > default value, in this case, '0'. Or 'NULL' for strings. > > Is it possible to do something l

Re: [sqlite] XML to SQLite upload

* Steve O'Hara <[EMAIL PROTECTED]> [2006-05-07 11:20]: > This is the right approach, when I worked in the SGML world > with a component versioning system, we called it the > "non-linear" design. > > By going down this road, your table schema is static and can > cope with any type of DTD without ch

Re: [sqlite] sqlite driven web-site

Hi Vivek, * Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 07:35]: > Has someone done something like that and would share their > experience on this topic. have a look at Catalyst: http://www.catalystframework.org/ (Installation can be a pain; if you have problems, don’t miss CatInABox: http://

Re: [sqlite] XML to SQLite upload

Hi Vivek, * Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 06:40]: > Sorry, I was not clear in my previous email. I do know the > structure of the for the XML - I was wondering if there was a > direct upload capability, once I know the structure of the XML. well, you can map generic XML to a rel

Re: [sqlite] XML to SQLite upload

* John Stanton <[EMAIL PROTECTED]> [2006-05-05 05:45]: > We feed XML into an SQLITE database, but the XML DTD maps the > database in structure and names. To get general XML data and > load it into any database requires a program of some > description to translate names and structures. That was the

Re: [sqlite] XML to SQLite upload

* Rajan, Vivek K <[EMAIL PROTECTED]> [2006-05-05 02:20]: > Does someone have XML to SQLite upload utility in perl/C++? That’s like asking if someone has an ASCII to CSV “upload utility”. It doesn’t make any sense. Regards, -- Aristotle Pagaltzis //

Re: [sqlite] sqlite puzzle

And to correct myself one last time: * A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-03 00:30]: > Assuming your client names are unique, this should work: > > SELECT > ( > SELECT > COUNT(*) > FROM clients c2 >

Re: [sqlite] sqlite puzzle

* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-03 00:30]: > I tried to do it with a join to see if that would work better, > but I’m too frazzled to figure it out right now. I must be more frazzled than I thought. SELECT n1.name, COUNT( n2.name ) rank FROM names

Re: [sqlite] sqlite puzzle

* JP <[EMAIL PROTECTED]> [2006-05-02 22:10]: > SQLite provides a way to get the N-th row given a SQL statement, with > LIMIT 1 and OFFSET . > > Can the reverse be done in an efficient way? For example, given a table > with 1million names, how can I return the row number for a particular > elem

Re: [sqlite] sqlite puzzle

* Kurt Welgehausen <[EMAIL PROTECTED]> [2006-05-02 22:15]: > No, you can't do that in SQL. You can. > The results of an SQL query are a set of rows; the rows are not > produced in any guaranteed order. If this was true, how would `LIMIT` work? Sure, the results do not have any implicit order, b