// post · 636
SQLite is a pain
SQLite is a pain to work with.
The problems I'm having:
- Where are the DATE functions!?
- Why can I not modify a table stucture with the MODIFY command!?
- Why don't LIMITs work on deleting and updating!?
Such a pain.
The only good thing about SQLite is that it just comes with PHP5 and you don't need to install anything.
MySQL has been working perfectly as usual.
// leave a comment
HTML allowed: <a href="" title="" rel=""></a> <b></b> <blockquote cite=""></blockquote> <em></em> <i></i> <strike></strike> <strong></strong> <li></li> <ol></ol> <ul></ul>
ie: <b>bold</b>
Your comment may need to be reviewed before it is published.
// comments
sqlite> select current_timestamp;
2007-12-05 15:47:03
sqlite> select current_date;
2007-12-05
sqlite> select current_time;
15:47:08
sqlite> select current_timestamp;
2007-12-05 15:47:11
See http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions
2. MODIFY is not a standard SQL command. You can use ALTER TABLE to a limited degree.
3. LIMIT, for those databases that supported, are not supported for UPDATE and DELETE statements (e.g. Postgres), only MySQL does this.
1) I'm not sure if these date functions will do what I want. Some examples:
On my archive page I use the following SQL:
SELECT count(post_date) as `count`, YEAR(post_date) as `year`, MONTH(post_date) as `month`, MONTHNAME(post_date) as `month_name` FROM $bt_tb->posts WHERE post_type = 'published' GROUP BY year,month ORDER BY year;
I don't see how this is possible with SQLite.
Another example:
DAYOFMONTH(post_date) = :day
Again I don't know if this is possible.
2) I want to use the MODIFY command to change the column structure. i.e I no longer want "NOT NULL". In MySQL I do the following:
ALTER TABLE $bt_tb->users MODIFY active_key varchar(32)
How would one do this in SQLite?
3) I can live with this.
Again thanks for the comment. Hopefully I can work out how to get this stuff working in SQLite.