Web database can't generate id value?

[runrunforest]

I create a table in which id has properties, integer, primary key and
ASC, with that, when we insert new record, the id value is supposed
to
auto generate, Just like MySQL.
But It aint. below is my SQL. And my first row shows id/empty
content/"my first note". Did I set the database attr/property for id
column incorrectly or web database work differently from others
databases ?
// open database
if (window.openDatabase) {
db = openDatabase("my notes", "1.0",
"my notes on local database", 3000000000);
}
// create table
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS
notes (id INTEGER PRIMARY
KEY ASC, content TEXT, timestamp INTEGER)');
});
// insert data
var content="my first note";
db.transaction(function (tx)
{
tx.executeSql("INSERT INTO notes (content)
VALUES (?)", [content]);
});

[PhistucK]

Add "AUTOINCREMENT" after "PRIMARY KEY".

☆PhistucK

[runrunforest]

I added, my new create table query is 'CREATE TABLE IF NOT EXISTS
notes (id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT, timestamp
INTEGER)'

The id column values is now auto-generated.

I notice there is a new table called sqlite_sequence also created
itself, I didn't create it. Something wrong here ?

[PhistucK]

Dumitru, can you help here?

☆PhistucK

[PhistucK]

But should it be shown to the user? is the user supposed to handle it in any way?

☆PhistucK

On Wed, Sep 15, 2010 at 21:28, Dumitru Daniliuc <du...@chromium.org> wrote:

this is how sqlite keeps track of the autoincrement columns: http://www.sqlite.org/autoinc.html

dumi

[PhistucK]

So they should not be shown in the Developer Tools. Right?

Is this a fixable bug?

☆PhistucK

On Thu, Sep 16, 2010 at 21:11, Dumitru Daniliuc <du...@chromium.org> wrote:

no, the user should not touch that table. there are a few other tables that sqlite might create, such as sqlite_master, or sqlite_temp_master. those should not be touched as well. unfortunately, we cannot deny access to them in the WebSQLDBs API, because there's no way to tell if the user is trying to directly modify those tables (bad), or if those tables need to be modified as part of a query executing on an "authorized" table (good).

dumi

[runrunforest]

Guys, how would you get the last inserted id?

[runrunforest]

And how would you get the the current indexed auto increment

for example, below is a table with some records

id/content
1/desktop
2/latop
3/tablet

current indexed auto increment is 3. If I delete all the records. How
can retrieve the current indexed auto increment?

[PhistucK]

Perhaps

SELECT last_insert_rowid() FROM TableName

If so, you could have Googled it just like me -

http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=auto+incremented+id+inserted+record+sqlite&qscrl=1

http://stackoverflow.com/questions/531109/how-to-return-the-value-of-auto-increment-column-in-sqlite-with-vb6

☆PhistucK

[runrunforest]

Thank you, Phistuck

Actually I already found what you showed me, but I failed to put them
together.

I got a solution now. This is the code for getting last insert id in
web database

db.transaction(function (tx)
{
tx.executeSql("INSERT INTO mytable (content) VALUES (?)",
[content]);
tx.executeSql("SELECT last_insert_rowid() FROM mytable ",
[],function(tx,result){
alert(result.rows.item(0)['last_insert_rowid()']);
});
});

[PhistucK]

Cool, it works for me, too.

☆PhistucK