Hi Gilles, >> If you designate an integer column as also being the primary key, >> then SQLite will auto assign its value incrementally each time you >> insert a new row, unless you assign a value explicitly. > > In this case, why do we need to use "PRIMARY KEY AUTOINCREMENT"?
Adding "autoincrement" just ensures that once a number is used, it won't be used again. It's a mis-nomer, in my opinion since, as you point out, just "integer primary key" will auto increase the value. The web site explains it this way: >> http://www.sqlite.org/lang_createtable.html >> An INTEGER PRIMARY KEY column can also include the keyword >> AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B- >> Tree keys are automatically generated. Additional detail on >> automatic B-Tree key generation is available separately. >> http://www.sqlite.org/autoinc.html >> If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a >> slightly different ROWID selection algorithm is used. The ROWID >> chosen for the new row is one larger than the largest ROWID that >> has ever before existed in that same table. >> The behavior implemented by the AUTOINCREMENT keyword is subtly >> different from the default behavior. With AUTOINCREMENT, rows with >> automatically selected ROWIDs are guaranteed to have ROWIDs that >> have never been used before by the same table in the same database. >> And the automatically generated ROWIDs are guaranteed to be >> monotonically increasing. These are important properties in certain >> applications. But if your application does not need these >> properties, you should probably stay with the default behavior >> since the use of AUTOINCREMENT requires additional work to be done >> as each row is inserted and thus causes INSERTs to run a little >> slower. > Shouldn't "PRIMARY KEY" be enough to have SQLite auto-increment this > column if it's NULL? Yes. As above, I think you only use the autoincrement additional option of you have a specific reason to do so. >> I wouldn't expect any problems using dates in the YYYY-MM-DD >> format. But Julian (real) format is the preferred method and uses >> less memory (less bytes) and I suspect is quicker to process. > > Thanks for the tip. > >> either by creating a view in SQLite (and having PHP reference it) or >> else using directly from PHP. > > I've never used views in SQLite. I'll take a look at what they do > and if they offer a better solution. It's pretty straight forward. If, say, you have a table: create table MyTable( Birth date ); and you populate it using the preferred julianday format: insert into MyTable values ( julianday( '2008-02-18 23:31', 'utc' )) ; Note the utc option converts my local time to central greenwich time, so is globally relevant. It will store global time as a real: 2454515.02152778 which represents the time I sent this email, applicable to all of earth. I can create a view to display the date in my localtime: create temporary view MyView as select datetime( Birth, 'localtime' ) as Birth from MyTable ; And from PHP, or wherever, I can select from the view, just like I would a table: select Birth from MyView; which gives me the original: 2008-02-18 23:31:00 since I've in the same time zone as where the date was entered. You can use just plain "date" instead of "datetime" function to just show the date without the time. If you don't care about time zones, you can omit the utc and localtime parameters. Tom BareFeet _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
