Re: [sqlite] Structural detection of AUTOINCREMENT
Csaba wrote: > >> BTW, the autoincrement keyword must appear after the integer primary key >> phrase, not in the middle as you have show it. > > This was an interesting note. I am using the SQLite that came with my > PHP v. 5.2.6 (built Feb 13, 2008), which is SQLite 2.8.17. > Turns out that the AUTOINCREMENT key was accepted in the position > as I had stated and was autoincrementing just fine. However, it > turned out that I got the same autoincrementing functionality when > I removed the AUTOINCREMENT keyword! No surprise that I couldn't > find a structural difference between the two in this version of SQLite. > The autoincrement keyword has only been used in SQLite since version 3.1.0. For a very long time SQLite has supported special functionality for an "integer primary key" column as an alias of the table's btree key. This column will also increment by default, but behaves differently than an autoincrement column in the face of deletes. See http://www.sqlite.org/lang_createtable.html and http://www.sqlite.org/autoinc.html for more details. > > Useful example, thanks. PHP provides a way to ask sqlite > to carry out a direct command (via http://php.net/sqlite_exec ) > but they did not provide for retrieving any data from this > function. In light of this, is there any way to direct output from > an sqlite command into a table? > Not in SQL as far as I know, but I don't know what the PHP wrapper does with pragma commands. > Though the sqlite_sequence table seems not to exist in > my version of SQLite, should you be able to see it in yours > by querying the sqlite_master table ala: > SELECT * FROM sqlite_master WHERE type='table'; > I ask this because sqlite_master itself is not listed in sqlite_master > Yes, the sqlite_sequence table is listed in the sqlite_master table. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Structural detection of AUTOINCREMENT
Thanks for your reply (and also thanks, Richard, for
your reply about sqlite3_table_column_metadata)
I've interspersed a few remarks, please see below.
On Mon, Jun 16, 2008 at 4:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Csaba wrote:
>> Is there any way to detect, based strictly on querying the
>> structure of a table/database whether there is an AUTOINCREMENT
>> set? That is to say, without analyzing the original SQL creation
>> statement, and without inserting a new element into the database.
>>
>
> Yes there is. The autoincrement keyword can only be applied to an
> integer primary key column, and when it is, sqlite creates a entry in
> its internal sqlite_sequence table with the name set to the name of the
> table.
>
> Note, a table's sequence entry isn't created until the first row has
> been added to the table. Also, the internal sequence table is not listed
> by a normal .tables meta command, but it is listed if you use a wildcard
> pattern.
Thanks for this excellent information
> BTW, the autoincrement keyword must appear after the integer primary key
> phrase, not in the middle as you have show it.
This was an interesting note. I am using the SQLite that came with my
PHP v. 5.2.6 (built Feb 13, 2008), which is SQLite 2.8.17.
Turns out that the AUTOINCREMENT key was accepted in the position
as I had stated and was autoincrementing just fine. However, it
turned out that I got the same autoincrementing functionality when
I removed the AUTOINCREMENT keyword! No surprise that I couldn't
find a structural difference between the two in this version of SQLite.
I tested with:
$dbFileName = "delme.db";
// prepare to put the database in the current directory:
$dbPath = dirname(__FILE__) . "/$dbFileName";
@unlink ($dbPath);// kill it in case it's already there
if (!($db = sqlite_open($dbPath, 0666, $sqliteerror)))
die("Cannot open database file: $dbPath");
$tbl = "myTable";
$tblSpec = "Id INTEGER AUTOINCREMENT PRIMARY KEY, RunLen INTEGER";
$sql = "CREATE TABLE $tbl ($tblSpec)";
sqlite_query ($db, $sql); // created here
sqlite_query ($db, "INSERT INTO $tbl VALUES(null,13)");
sqlite_query ($db, "INSERT INTO $tbl VALUES(null,96)");
$aOut = sqlite_array_query($db, "SELECT * FROM $tbl");// get results
> Since autoincrement can only be used with integer primary key columns,
> the column name in the table can be determined by looking at the output
> of the pragma table_info() command. It will be the primary key column
> indicated by the value 1 in the pk column.
Yes. I've also got this in my version
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> create table t (id integer primary key autoincrement);
> sqlite> .tables '%'
> sqlite_sequence t
> sqlite> .header on
> sqlite> .mode column
> sqlite> select * from sqlite_sequence;
> sqlite> insert into t values(1);
> sqlite> select * from sqlite_sequence;
> nameseq
> -- --
> t 1
> sqlite> pragma table_info(t);
> cid nametypenotnull dflt_value pk
> -- -- -- -- -- --
> 0 id integer 0 1
>
> HTH
> Dennis Cote
Useful example, thanks. PHP provides a way to ask sqlite
to carry out a direct command (via http://php.net/sqlite_exec )
but they did not provide for retrieving any data from this
function. In light of this, is there any way to direct output from
an sqlite command into a table?
Though the sqlite_sequence table seems not to exist in
my version of SQLite, should you be able to see it in yours
by querying the sqlite_master table ala:
SELECT * FROM sqlite_master WHERE type='table';
I ask this because sqlite_master itself is not listed in sqlite_master
Thanks,
Csaba Gabor from Vienna
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Structural detection of AUTOINCREMENT
Csaba wrote: > Is there any way to detect, based strictly on querying the structure of a > table/database > whether there is an AUTOINCREMENT set? That is to say, without analyzing > the > original SQL creation statement, and without inserting a new element into > the database. > Yes there is. The autoincrement keyword can only be applied to an integer primary key column, and when it is, sqlite creates a entry in its internal sqlite_sequence table with the name set to the name of the table. Note, a table's sequence entry isn't created until the first row has been added to the table. Also, the internal sequence table is not listed by a normal .tables meta command, but it is listed if you use a wildcard pattern. BTW, the autoincrement keyword must appear after the integer primary key phrase, not in the middle as you have show it. Since autoincrement can only be used with integer primary key columns, the column name in the table can be determined by looking at the output of the pragma table_info() command. It will be the primary key column indicated by the value 1 in the pk column. SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table t (id integer primary key autoincrement); sqlite> .tables '%' sqlite_sequence t sqlite> .header on sqlite> .mode column sqlite> select * from sqlite_sequence; sqlite> insert into t values(1); sqlite> select * from sqlite_sequence; nameseq -- -- t 1 sqlite> pragma table_info(t); cid nametypenotnull dflt_value pk -- -- -- -- -- -- 0 id integer 0 1 HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Structural detection of AUTOINCREMENT
Is there any way to detect, based strictly on querying the structure of a
table/database
whether there is an AUTOINCREMENT set? That is to say, without analyzing
the
original SQL creation statement, and without inserting a new element into
the database.
Consider:
CREATE Table myTab (col1 INTEGER AUTOINCREMENT PRIMARY KEY)
vs.
CREATE Table myTab (col1 INTEGER PRIMARY KEY)
I have not found a difference upon looking at:
SELECT * FROM sqlite_master WHERE type='table' AND name='myTab';
PRAGMA table_info('myTab');
The following is empty:
PRAGMA index_list('myTab');
so index_info does not seem relevant either.
Am I missing something? Thanks,
Csaba Gabor from Vienna
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
