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;
> name seq
> ---------- ----------
> t 1
> sqlite> pragma table_info(t);
> cid name type notnull 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
