Introspection and RowID / INTEGER PRIMARY KEY

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Introspection and RowID / INTEGER PRIMARY KEY

Andrew Rodland
Is there any possibility that in a future version of SQLite, pragma table_info
or some other pragma could return a simple boolean "this column is the rowid"
indicator? As demonstrated under "Goofy Behavior Alert" at
http://www.sqlite.org/lang_createtable.html#rowid it is possible (if unlikely)
for a column to appear as type='INTEGER' and pk=1 in the pragma table_info
results and yet *not* be the rowid. This is an annoyance that could easily be
solved simply by letting SQLite communicate its own determination about the
column back to the user.

Thanks,

Andrew
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Introspection and RowID / INTEGER PRIMARY KEY

Simon Slavin-3

On 19 May 2010, at 12:05pm, Andrew Rodland wrote:

> Is there any possibility that in a future version of SQLite, pragma table_info
> or some other pragma could return a simple boolean "this column is the rowid"
> indicator? As demonstrated under "Goofy Behavior Alert" at
> http://www.sqlite.org/lang_createtable.html#rowid it is possible (if unlikely)
> for a column to appear as type='INTEGER' and pk=1 in the pragma table_info
> results and yet *not* be the rowid.

Sometimes no columns are the rowid.  For instance, any case where there are no suitable columns:

CREATE TABLE texts(thisText TEXT)

This table does still have a rowid, but you can't access it using any of the columns you've declared.  Rather than find out what SQLite has done internally to create a rowid, might it be possible for you to refer explicitly to the column "_ROWID_" in your code ?  Unless this name is used in the TABLE definition, it is guaranteed to return the rowid values.

SELECT _rowid_, * FROM texts

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Introspection and RowID / INTEGER PRIMARY KEY

Andrew Rodland
In reply to this post by Andrew Rodland
> On 19 May 2010, at 12:05pm, Andrew Rodland wrote:
> > Is there any possibility that in a future version of SQLite, pragma
> > table_info or some other pragma could return a simple boolean "this
> > column is the rowid" indicator? As demonstrated under "Goofy Behavior
> > Alert" at
> > http://www.sqlite.org/lang_createtable.html#rowid it is possible (if
> > unlikely) for a column to appear as type='INTEGER' and pk=1 in the
> > pragma table_info results and yet *not* be the rowid.
>
> Sometimes no columns are the rowid.  For instance, any case where there are
> no suitable columns:
>
> CREATE TABLE texts(thisText TEXT)
>
> This table does still have a rowid, but you can't access it using any of
> the columns you've declared.  Rather than find out what SQLite has done
> internally to create a rowid, might it be possible for you to refer
> explicitly to the column "_ROWID_" in your code ?  Unless this name is
> used in the TABLE definition, it is guaranteed to return the rowid values.
>
> SELECT _rowid_, * FROM texts

I understand that, but that's not what I need. I'm working on the code that
loads a database schema into an ORM, and I need to properly set the flag
"is_auto_increment" which is used to identify a column that will be
automatically set by the DB. I'm not looking to access the rowid directly, I
truly am just looking for information about a specific column -- will it have
a certain behavior or not?
In SQLite at present the question is equivalent to "is it the INTEGER PRIMARY
KEY as defined at http://www.sqlite.org/lang_createtable.html#rowid ?" which
is equivalent to "is it an alias of the rowid?" So that's the question I'm
looking to be able to answer.

Thanks again

Andrew

P.S. I'd appreciate it if you could CC me on replies, as I'm not on the list
and having to copy/paste from GMANE is a bit lame and breaks threading :)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Introspection and RowID / INTEGER PRIMARY KEY

Simon Slavin-3

On 20 May 2010, at 1:21am, Andrew Rodland wrote:

> I understand that, but that's not what I need. I'm working on the code that
> loads a database schema into an ORM, and I need to properly set the flag
> "is_auto_increment" which is used to identify a column that will be
> automatically set by the DB. I'm not looking to access the rowid directly, I
> truly am just looking for information about a specific column -- will it have
> a certain behavior or not?

Sorry, but as far as I know the only way to find that out is to look at column 'sql' of TABLE sqlite_master and parse the creation statement.  The appropriate fix to SQLite would involve adding another column to the result from 'PRAGMA table_info()'.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users