[sqlite] Re: Re: Re: COLLATE without ORDER BY?
Jonas Sandman <[EMAIL PROTECTED]>
wrote:
ext1 is one type of extension, in my case 'mp3'. I guess it doesn't
make
sense when you have only one, but potentially there can be a lot more
there. ('mp3', 'ogg', 'flac') etc...
And how exactly do you plan to specify such a list in a parameter?
Unfortunately, "COLLATION STRIP_ACCENT LIKE" doesn't seem to trigger
anything either.
First, it's "COLLATE", not "COLLATION". Second, LIKE doesn't use
collations. Note that a collation doesn't convert one string to another,
it just tells how two strings should be ordered. This information is
useless to LIKE.
What you probably want is a custom function, not a custom collation. A
function would take a string and return another string, with accents
removed. You can then use it like this:
select * from tableName
where StripAccents(field) = value;
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Re: COLLATE without ORDER BY?
:ext1 is one type of extension, in my case 'mp3'. I guess it doesn't make
sense when you have only one, but potentially there can be a lot more there.
('mp3', 'ogg', 'flac') etc...
Unfortunately, "COLLATION STRIP_ACCENT LIKE" doesn't seem to trigger
anything either. What makes me the most confused is that SQLite takes it as
a valid prepared statement. No error or anything.
I am using SQLite 3.3.17, which should be recent enough (latest?)
Best regards,
Jonas
On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
Jonas Sandman <[EMAIL PROTECTED]>
wrote:
> However, I tried this prepared statement:
>
> SELECT f.fileid, f.path, m.title, m.artist, m.album, m.genre,
> m.comment,
> m.track, m.year, m.length, m.bitrate, m.playcount, f.changed, f.size,
> m.tagged FROM Files f, Meta m WHERE m.fileid=f.fileid AND
> f.file_exists=1AND
> f.extension IN(:ext1) COLLATE STRIP_ACCENT
What do you bind to :ext1? This query doesn't make much sense to me. IN
operator requires a subselect or a list of values on the right side. If
you just have a single value in a list, why not write
f.extension = :ext1
Having said that, I believe the correct use of COLLATE clause with IN
operator should look like this:
f.extension COLLATE STRIP_ACCENT IN (value1, value2)
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: COLLATE without ORDER BY?
Thanks for the fast response Igor! However, I tried this prepared statement: SELECT f.fileid, f.path, m.title, m.artist, m.album, m.genre, m.comment, m.track, m.year, m.length, m.bitrate, m.playcount, f.changed, f.size, m.tagged FROM Files f, Meta m WHERE m.fileid=f.fileid AND f.file_exists=1AND f.extension IN(:ext1) COLLATE STRIP_ACCENT There's no difference if I remove the fileid comparison (those are INTEGER) and the file_exists (also INTEGER). f.extension is a VARCHAR(255). STRIP_ACCENT is created like this: sqlite3_create_collation16(m_db, (const char*)L"STRIP_ACCENT", SQLITE_UTF16LE, NULL, m_stripAccent); Right now it doesn't do anything (just returns 1) but I would expect the debugger to halt on my breakpoint there so it isn't called. There are no errors when I create either the collation or the prepared statement. The result is returned properly except that my collation isn't called. If I add an ORDER BY COLLATE STRIP_ACCENT to the select, the collation is called, but I'll assume that is just for determining the order and not for the comparison when retrieving the results. Best regards, Jonas On 5/25/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Jonas Sandman <[EMAIL PROTECTED]> wrote: > Can I make SQLite use a collation function when making a simple query? > Before sending the data to LIKE I could override and for example > strip the accent (*á' would become 'a' and 'é' would become 'e' etc). Recent versions of SQLite support syntax like this: select * from tableName where field = 'blah' collate MYCOLLATION; That is, you can specify a collation to use for each comparison. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: COLLATE without ORDER BY?
Jonas Sandman <[EMAIL PROTECTED]> wrote: Can I make SQLite use a collation function when making a simple query? Before sending the data to LIKE I could override and for example strip the accent (*á' would become 'a' and 'é' would become 'e' etc). Recent versions of SQLite support syntax like this: select * from tableName where field = 'blah' collate MYCOLLATION; That is, you can specify a collation to use for each comparison. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Documentation/specification of sqlite3_get_table/sqlite3_free_table
Hello, I have a couple of questions about the specification of sqlite3_get_table/sqlite3_free_table. I'm looking at the online docs at http://www.sqlite.org/capi3ref.html#sqlite3_get_table The questions are about when sqlite3_free_table() should be freed. Specifically: 1) Is it possible for sqlite3_get_table() to allocate memory when it fails (i.e., returns an error code)? 2) Is it safe to pass a NULL value to sqlite3_free_table()? Right now I'm assuming that it's conservative to always call sqlite3_free_table() whenever *resultp != NULL. I'd like to either (a) call it unconditionally, and know that it's safe when its argument is NULL, or (b) call it only when sqlite3_get_table succeeds, knowing that *resultp != NULL is guaranteed in that case. -- James
RE: [sqlite] Why doesn't this UPDATE work?
Try this UPDATE table1 SET column3 = ( SELECT column3 FROM table2 WHERE table2.column1 = table1.column1 AND table2.column2 = table1.column2) HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Brian Albert [mailto:[EMAIL PROTECTED] Sent: Friday, May 25, 2007 11:30 AM To: sqlite-users@sqlite.org Subject: [sqlite] Why doesn't this UPDATE work? I've been struggling with the following: update table1 set column3 = (select i.colum3 from table2 i, table1 t where t.column1 = i.column1 and t.column2 = i.column2) Reading the manual and this list I learned that this statement will grab the first result of my sub-select and populate it in every row (cruel, but I can see the logic). However what I want is for the different values that I get from the sub-select to be entered into the appropriate rows of column3. I looked at using INSERT or REPLACE but the subselect (when run standalone) returns 34001 rows, and table1 has 34004 rows - the result is all 125000 rows of table2 being appended to table1 (with NULLs in all but column3). Neither table has a primary key and I'm running 3.3.17 on MacOSX 10.4.9 Is UPDATE the correct way to do this? Also, is there an SQLITE chatroom on IRC? Many thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] index using and explain using question
dszhang wrote: > > by now the forst query is work well, but the second query is not,the second > query take about 5 times longer than the first one.i want to konw why? > i think may be the engine haven't use the index ,so i would prefer to ues > EXPLAIN syntax,but i don't know how to use it in C API and how to get the > EXPLAIN result in C API. > would anyone give me some idear about this,thans a lot. > > You can use either EXPLAIN or EXPLAIN QUERY PLAN as a prefix to any SQL statement (typically a query though). These convert the statement into a query that returns rows explaining how the query will be implemented. EXPLAIN returns five columns as shown below (with headers on). The opcodes of the vdbe (virtual database engine) machine are documented at http://www.sqlite.org/opcode.html and http://www.sqlite.org/vdbe.html sqlite> explain select * from t; addr opcode p1 p2 p3 -- -- -- -- -- 0 Goto 0 11 1 Integer 0 0 2 OpenRead 0 2 3 SetNumColu 0 2 4 Rewind 0 9 5 Column 0 0 6 Column 0 1 7 Callback 2 0 8 Next 0 5 9 Close 0 0 10 Halt 0 0 11 Transactio 0 0 12 VerifyCook 0 1 13 Goto 0 1 14 Noop 0 0 An EXPLAIN QUERY PLAN returns three columns. The output of explain query plan is not documented (to the best of my knowledge anyway), but is fairly self explanatory. It shows the order that tables are scanned and which indexes, if any, are used to speed up the scans. sqlite> explain query plan select * from t; order from detail -- -- -- 0 0 TABLE t HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Unique Index not working properly
On Fri, May 25, 2007 at 09:16:25 +0300, Cariotoglou Mike wrote:
> > > Seems that there is a problem on unique key fields when null
> > > values are allowed
> > >
> > > CREATE TABLE z (
> > > id VARCHAR(32) NOT NULL,
> > > f1 VARCHAR(32) NOT NULL,
> > > f2 VARCHAR(20),
> > > PRIMARY KEY (id)
> > > );
> > > CREATE UNIQUE INDEX z_I1 ON z (f1, f2)
> > >
> > > insert into z values ('1', '1', null);
> > > insert into z values ('2', '1', null);
> > >
> this should give a unique constraint error, but does not.
>
> testing with sql server and oracle shows that they *will* give an
> error. in this case, NULL = NULL seems to be true
>
> can this be fixed ? it is quite a deviation from standard behavior.
This page
http://www.sqlite.org/nulls.html
explains the matter, line "nulls are distinct in a UNIQUE column". It
says Oracle treats NULLs as distinct though.
--
Tomash Brechko
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] How can I get my query to run as fast as SQLiteSpy?
> I have a simple table with five columns and 450,000 rows. In > SQLiteSpy, I can run "SELECT * FROM trend_data" and get all > 450,000 rows in 4.5 seconds. But in my program, if I use > sqlite3_prepare() and > sqlite3_step() until I run out of data, it takes 55 seconds > to get through all rows. A test with hard-coded junk data > showed that my program is account for only 2 seconds of that. > If I use sqlite3_get_table(), I can cut my time in half, > which is nice, but I'm still taking 25 seconds to get the > same data SQLiteSpy is getting in 4. > How is SQLiteSpy doing it, and can I use the same trick? I suspect that SqLiteSpy is not extracting all the rows since you can't see 450,000 rows on the computer screen at any one time. It probably uses some form of double buffering method which extracts data as you scroll through the rows to give the allusion that it has extracted all the rows. You can limit the number of rows and what position you start using the terms LIMIT and OFFSET within your SQL statement. See http://www.sqlite.org/lang_select.html Rgds This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unique Index not working properly
> > Seems that there is a problem on unique key fields when null
> > values are allowed
> >
> > CREATE TABLE z (
> > id VARCHAR(32) NOT NULL,
> > f1 VARCHAR(32) NOT NULL,
> > f2 VARCHAR(20),
> > PRIMARY KEY (id)
> > );
> > CREATE UNIQUE INDEX z_I1 ON z (f1, f2)
> >
> > insert into z values ('1', '1', null);
> > insert into z values ('2', '1', null);
> >
this should give a unique constraint error, but does not.
testing with sql server and oracle shows that they *will* give an error. in
this case, NULL = NULL seems to be true
can this be fixed ? it is quite a deviation from standard behavior.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
