Re: [sqlite] Request for comment: Proposed SQLite API changes
On 11/4/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > The authorizer is used to protect against SQL injection attaches > when the SQL text originates from user input. Typically an > application will turn the authorizer on when preparing user-supplied > SQL then turn it right back off again so that its own internal > SQL can run unfiltered. Example: > > sqlite3_set_authorizer(db, ignore_passwd_column); > stmt1 = sqlite3_prepare(db, zSqlFromUser) > sqlite3_set_authorizer(db, 0); > stmt2 = sqlite3_prepare(db, zInternalSql); > sqlite3_step(stmt1); -- Oops! Might try to recompile! Well, obviously, part of the sqlite3_stmt structure, then, is the authorizer that is used on that particular statement ;) So change it so that *if* you decide to do this, sqlite3_stmt also has its authorizer as well as the original SQL statement. Better might be to have an alternative data structure (sqlite3_stmt2) with calls as appropriate. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] Case-sensitive table names
On 9/28/05, Ivo Kulms <[EMAIL PROTECTED]> wrote: > Sorry, I was a little bit unclear. > > My problem is that I have the need to treat "table1" and "TABLE1" as two > single entities. You need to treat table1 and TABLE1 as *separate* or the same? And I apologize -- at least with the version of sqlite3 that I have (3.2.1/3.2.2), "table1" and "TABLE1" are in fact treated the same. They're not *supposed* to be, although table1 and TABLE 1 are supposed to be. In code: SELECT table1 ... SELECT TABLE1 These are the same and should be treated the same. SELECT "table1" SELECT "TABLE1" These are not the same and shouldn't be treated the same. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
On 8/10/05, ender <[EMAIL PROTECTED]> wrote: > Austin Ziegler wrote: >>On 8/9/05, ender <[EMAIL PROTECTED]> wrote: >>> So my simple feature request would be: allow '`' as a quoting symbol - >>> as mySQL does. Or - what would be as helpful as the other idea - allow >>> unquoted column names with leading digits - as mySQL does. >>> Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337 >> I think that supporting '`' (backquote) would be a bad idea. Then >> again, MySQL ignores so much of the SQL standard that I think that >> importing any idea from MySQL is a bad idea. > What are your reasons for thinking supporting '`' is a bad idea? 1. It's not part of the standard. 2. It's not part of the standard. 3. MySQL does it. Therefore, no one else should do it, because: 4. It's not part of the standard (and most of what MySQL does is not part of the standard). If you've learned "SQL" on MySQL, you haven't learned SQL. You've learned MySQL. The same applies to people who want to do [tablename] -- it's a Microsoftism that (IMO) should be *de-supported* in SQLite because it's ugly and wrong. The only quotes that SQL92 supports are ' and "; ' indicates data, " indicates literal interpretation of table and column names. Not `, not []. If MySQL does it and it's not part of the standard, I'm opposed to it. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] Finding max of each group?
On 8/10/05, Colin Fleming <[EMAIL PROTECTED]> wrote: > I'm writing a messaging system, and I have the following problem. > I have a conversations table, and a messages table that has a > foreign key to conversations. I can do something like the > following to provide a summary: > > select conversation_id, count(*), max(unread), max(updated_on) > from messages > where conversation_id in ( ) > group by conversation_id; > > (code may contain typos, sorry, haven't got my code right here). > > I use max(updated_on) to get the date of the most recent message > in the conversation. Is there a way to return the ID of this > message? Maybe, but I'm wondering why you're creating Yet Another Messaging System instead of using something like XMPP (an IETF standard with several open source implementations for both clients and servers). That said, you can *probably* get it with a secondary query using the information from the first. SELECT message_id FROM messages WHERE conversation_id = ? -- use the conversation_id AND updated_on = ? -- use the updated on value A little more dynamically, maybe: SELECT message_id FROM messages WHERE conversation_id, updated_on IN (SELECT conversation_id, MAX(updatd_on) FROM messages WHERE conversation_id IN () GROUP BY conversation_id); One might even be able to combine some of this as a subquery on the FROM line, but that would require a bit of experimentation on your part, since you're going to be more familiar with your data. Something like (completely untested here): SELECT m.conversation_id, q.message_id, COUNT(m.*), MAX(m.unread), MAX(m.updated_on) FROM messages m, (SELECT mm.conversation_id, mm.message_id FROM messages mm WHERE mm.conversation_id, mm.updated_on IN (SELECT mmm.conversation_id, MAX(mmm.updated_on) FROM messages mmm WHERE mmm.conversation_id IN () GROUP BY conversation_id)) q WHERE m.conversation_id = mm.conversation_id GROUP BY m.conversation_id, q.message_id; Maybe. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
On 8/9/05, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> [09-08-2005 19:05, ender escreveu]
> [...]
>> So my simple feature request would be: allow '`' as a quoting
>> symbol - as mySQL does. Or - what would be as helpful as the
>> other idea - allow unquoted column names with leading digits - as
>> mySQL does.
> Does MySQL allows [0_xy] to specify column names?
I don't think so.
> If I remember correctly, that is the standard SQL way to have
> columns with spaces in the middle, and sqlite allows columns
> starting with a digit in this way...
I'm not sure that it's a standard. I know Microsoft SQL Server and
Access DB use it, but from what I recall of the SQL92 standard, only
double quotes ("0_xy") are permitted for case-sensitive or otherwise
abnormal table and column names. Tables specified without this are
to be treated as uppercase only.
-austin
--
Austin Ziegler * [EMAIL PROTECTED]
* Alternate: [EMAIL PROTECTED]
Re: [sqlite] RFC Beginning digit in column name
On 8/9/05, ender <[EMAIL PROTECTED]> wrote: > > Dear developers, > > a while back, our project decided to switch from mysql to usage of > various SQL backends, among this sqlite3 as our favorite. Now recently > we encountered a problem, just a tiny one, but one we are not able to > solve with all used SQl backends in a common sense. > > The problem arised within a table that had a column called '0_uid'. As > you might guess, the problem comes from the beginning digit in the > column name. I looked arround quite a while and learned, that it was > pure luck that we haven't had problems with that so far - as we relied > upon an SQL extension that mySQL offered. After some reading and trying > I found out, that sqlite3 also was able to handle such columns, but the > column name has to be quoted. Ok, that sounds fair - BUT I had to learn > one sad thing... mySQL and sqlite3 don't agree upon a common subset of > symbols to quote. Whilest the one accepts ' and ", the other relies upon `. > > So my simple feature request would be: allow '`' as a quoting symbol - > as mySQL does. Or - what would be as helpful as the other idea - allow > unquoted column names with leading digits - as mySQL does. > Also see ticket # 1337 http://www.sqlite.org/cvstrac/tktview?tn=1337 I think that supporting '`' (backquote) would be a bad idea. Then again, MySQL ignores so much of the SQL standard that I think that importing any idea from MySQL is a bad idea. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] International Language Support
On 8/4/05, Dan Wellisch <[EMAIL PROTECTED]> wrote: > Are you saying that 8859-1 encoding does not work with these > international versions of MS Windows, so we would need to ensure > that we are putting UTF-8 chars in the data? This would make sense > if the OS uses UTF-8 chars. in the WHERE clause so that it is > searching against 8859-1 chars. What I'm saying is that unless you're explicitly doing something to ensure that your input is 8859-1, you're getting something else. In fact, on English Windows, you're probably not getting 8859-1, either, you're getting Windows 1252 (I *think* that's the right number), which is similar to, but not *quite* the same as 8859-1. If you're just using char* (or std::string) and getting input from Windows, then you're getting it in ANSI/OEM, most of the time, which is most decidedly *not* 8859-1. It might be Windows 1252, but it's not necessarily Windows 1252 on non-English versions of Windows. If you're compiling with UNICODE and are using TCHAR*, you'll be getting wchar* (or std::wstring), which is actually UCS-2 (related in some way to UTF-16, but again not *quite* the same since UCS-2 doesn't support surrogates). This is better than ANSI/OEM, but not by much because it causes other problems. The real trick is that UTF-8 (which SQLite uses internally; I haven't quite understood this part in the documentation, and haven't yet needed to, because I don't think it does any auto-conversion for you) and US ASCII (that is to say, the first 128 characters of ANSI) map perfectly. If it's something where you can test this, you might be able to reproduce it by trying to use a high-bit character (e.g., c-cedilla or something) in the English version to see if it searches right. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] International Language Support
On 8/4/05, Dan Wellisch <[EMAIL PROTECTED]> wrote: > We just put a SQLlite application in production. It handles the > display of ISO 8859-1characters just fine if they appear in the > search results. > > However, users that are running German, French, etc. versions of > Microsoft Windows are complaining that search results are coming > back with 0 results whereas we know that these querys work when > the app. is running on the English version of MS Windows. > > What do we do to fix this so this app works correctly on any > language version of OS? Furthermore, we also have a version of > this app. that runs under Mac OSX so I pose the same question > concerning that OS. You may need to do some data massaging to make sure that it's *not* ISO-8859-1 but rather convert UTF-8. This may require a couple of round trips with WideCharToMultibyte and MultibyteToWideChar so that you go from the OEM encoding (presuming that you're using char* as your characters) to UTF-8 via Windows wide characters (which are UCS-2, IIRC). Alternatively, use something like Iconv to do this. The main problem is (likely) that you're inserting data as if it were always ISO 8859-1, when it's not. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] how to get last autoincrement value?
On 8/3/05, Khamis Abuelkomboz <[EMAIL PROTECTED]> wrote: > Hi > > I'm using SQLite in my application and am very excited about this little > and fast database. > > How can I retrieve the last created autoincrement value? > On MySQL and SQLServer or just the SQL standard I can fire the following > select statement: > SELECT @@IDENTITY Actually, as far as I know, SELECT @@IDENTITY isn't part of any ANSI SQL standard. It cerrtainly won't work on Oracle ;) That said, you can get the identity value of an autoincremented key with sqlite3_last_insert_rowid (http://sqlite.org/capi3ref.html#sqlite3_last_insert_rowid). It's something of a cheat, but it works and works very well. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] order of reception of results at the time of a request SQL
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] proper SQL DELETE syntax
On 7/14/05, Brass Tilde <[EMAIL PROTECTED]> wrote: >>> 'Twere me, I'd do it like this: >>> >>> delete timeline >>> from del_timelines >>> wheretimeline.name = del_timelines.name >>> and timeline.lastchange <= del_timelines.last_change >> I guess you mean... from timeline,del_timeline ... > No, I meant what I wrote, except for the difference in > "lastchange" to "last_change". > >> But regardless, this form of the DELETE command does not (seem) >> to work in SQLite. Perhaps it is not part of the SQL standard. > That's quite possible. I didn't test it using SQLite, but that > form *does* work on MS SQL Server. Neither is valid SQL. MySQL's is wrong, as is SQLServer's. They're convenience for people who don't know SQL otherwise. -austin -- Austin Ziegler * [EMAIL PROTECTED] * Alternate: [EMAIL PROTECTED]
Re: [sqlite] proper SQL DELETE syntax
On 7/14/05, Downey, Shawn <[EMAIL PROTECTED]> wrote:
>>> DELETE from timeline where timeline.name=del_timelines.name and
>>> timeline.last_change<=del_timelines.last_change;
...
>> delete timeline
>> from del_timelines
>> wheretimeline.name = del_timelines.name
>> and timeline.lastchange <= del_timelines.last_change
> I guess you mean... from timeline,del_timeline ...
>
> But regardless, this form of the DELETE command does not (seem) to work
> in SQLite. Perhaps it is not part of the SQL standard.
As far as I know, it isn't.
The canonical way would be:
DELETE
FROM timeline t1
WHERE t1.ROWID IN (SELECT ROWID
FROM timeline t, del_timelines d
WHERE t.name = d.name
AND t.last_change <= d.last_change);
-austin
--
Austin Ziegler * [EMAIL PROTECTED]
* Alternate: [EMAIL PROTECTED]
