Re: [sqlite] any way to SELECT the 100th-104th row of a table?
At 3:00 PM -0700 10/7/07, Adam Megacz wrote: Hello. This is probably a stupid question, but... Is there any way to include some phrase in a SELECT clause that will match only the Nth-Mth rows of a table, for some values of N and M? Note that ROWID isn't what I'm looking for -- if you delete rows from a table the ROWID no longer matches the "row number". SELECT * FROM table LIMIT 120 OFFSET 100; http://sqlite.org/lang_select.html "The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached." -pmb - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Q about new SQLite API
At 1:17 PM + 11/7/06, [EMAIL PROTECTED] wrote: QUESTION 1: sqlite3_prepare_v2 is the merely the working name for the new function. What should the official name be? I like sqlite3_prepare_v2. It lets me know that it's very similar to sqlite3_prepare, so lots of existing documentation still applies. It lets me know it's different, so I have to be aware that some docs may not. It leaves room for future changes (v3) in a way that similar constructs (ng) do not. And it follows the same naming pattern as the 'sqlite3' prefix. Something totally different, like sqlite3_compile, would work if sqlite3_prepare was removed from the library, but if they're both there but named differently like that, it will take new devs longer to discover which they should use and why. -pmb - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] v3.2.1 and current differences!
At 2:20 PM -0400 6/26/06, [EMAIL PROTECTED] wrote: SQLite 3.3.0 can read and write all prior versions of SQLite databases. But SQLite 3.2.8 cannot read or write a database created by SQLite 3.3.0, unless you use PRAGMA legacy_file_format=TRUE; prior to creating the database, or unless you compile 3.3.0 with -DSQLITE_DEFAULT_FILE_FORMAT=1. Does this forward-incompatibility include the PRAGMA user_version? Ie., Can 3.2.8 read at least the user_version from a 3.3.0 file, even if it can't read anything else? Are there web pages that describe the pros and cons of the new file format? -pmb
Re: [sqlite] Feature Request: Open from fd
At 3:53 PM -0600 1/4/06, Aaron Laffin wrote: On 1/4/06, Peter Bierman <[EMAIL PROTECTED]> wrote: Related to a project I'm working on, it would be useful for me to be able to open a database file via passing an already open file descriptor to the sqlite open() call. sqlite3_openfd(). It seems to me that this would cause problems with the creation of the journal file. SQLite places it in the same directory as the db file and it is transient. SQLite wouldn't know where to put it if all it had were a file descriptor for the main db file. It also wouldn't know where to find it for a potential rollback on open. The design scheme of the journal would have to change to support this. Hmm, good point. Perhaps I could also pass the fd of the journal (if I find one)? I really want to separate the privileged operations I need to perform from the bulk of my code. (This is for a setuid program.) The privileged operations I need to perform are fairly minimal. -pmb
[sqlite] Feature Request: Open from fd
Related to a project I'm working on, it would be useful for me to be able to open a database file via passing an already open file descriptor to the sqlite open() call. sqlite3_openfd(). I need this because I'd prefer to resolve some filesystem permissions access issues in a separate process from my database code. Is this the appropriate place to make this request, or is there a better way to ask? ;-) -pmb
Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2
At 1:54 PM -0500 11/1/05, [EMAIL PROTECTED] wrote: Please let me restate the proposed change: I desire to change SQLite so that it no longer distinguishes between integer and real. The two concepts are merged into a unified "numeric" type. And because all number values are of the same type, they need to always be treated in the same way. A division should return a result that includes the fractional part, if there is one, regardless of the particular representation of the operands. I'm not a DB person by trade, I'm a programmer. I have grown to love SQLite for it's very 'C' like philosophy of making the underlying mechanisms transparent. Part of that is the manifest typing, which lets me control exactly what's going into the database, and lets me optimize that for best efficiency. My $0.02 is that combining INTEGER and REAL is the wrong direction. It takes information away, information that sqlite is perhaps unique in preserving. It seems that the column affinity mechanism already holds the answer to this problem. If a column could have REAL affinity, then whatever data was stored in that column would have an opportunity to act according to the SQL standard, without losing metadata about the original data. http://www.sqlite.org/datatype3.html says: A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no floating point component (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class. I propose a similar affinity be added called "REAL", that would behave the same as NUMERIC, except that INTEGERs would be converted to REAL. Also, I'm surprised that the column affinity isn't applied when the data is read from the database vs inserted into the database, but that's a separate topic. -pmb
Re: [sqlite] How to determine if a column is autoincremented?
Actually, on SQLite, 'INTEGER PRIMARY KEY' does designate a special type of autoincremented column. The internal 64 bit rowid is used directly in that case, which is essentially 'free' storage. http://www.sqlite.org/faq.html#q1 http://www.sqlite.org/lang_createtable.html http://www.sqlite.org/autoinc.html -pmb At 12:19 PM -0700 10/22/05, Mario Gutierrez wrote: Thanks for the reply. This would not work as I could define a table like this CRETE TABLE my_table ( id INTEGER PRIMARY KEY, ... ) This would meet your criteria, but 'id' is not an autoincremented column. -- Mario Gutierrez mario.l.gutierrez @ hotmail.com From: "David M. Cook" <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to determine if a column is autoincremented? Date: Sat, 22 Oct 2005 10:52:20 -0700 On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote: I'm writing a SQLite adapter for a code generation tool. I'm a little stumped on how you query SQLite to determine if a column is autoincremented. I've tried An auto-increment column is defined as INTEGER PRIMARY KEY, so look for primary key columns of type 'INTEGER'. Dave Cook _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
