Categories

Bluetrait
        Bluetrait
            Videos
            Solar
            Coding
            Geek
            General
            Coding
                PHP
                Bluetrait
                WordPress
                    Plugins
                PHP
                Bluetrait (Program)
            Geek
                Juniper
                Cisco
                IBM N2200 8363
                PCs
                Spam
                IPv6
                Apple
                NetScreen
                Internet
            General
                Uni

Wed, 05 Dec 2007 4:02 PM

SQLite is a pain

Michael Dale

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.


Comments

On Thu, 06 Dec 2007 at 2:56 AM, Anonymous wrote 1. Date functions: 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: Comment Link

On Thu, 06 Dec 2007 at 8:24 AM, Michael Dale (of michaeldale.com.au) wrote Thanks for the comment! 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. 2: Comment Link

On Wed, 10 Sep 2008 at 4:13 AM, Jason wrote Hey Michael, #2 is actually super easy - you just create a temporary table, insert all of your data from your old table to the temporary table, then create a new table with the structure you want, then import the data from your temporary table into your new table, then delete the temporary table. it's so easy, don't you just love SQLite?! http://www.sqlite.org/omitted.html 3: Comment Link

Comments?

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.

Message

Name

Email (not shown)

WWW (optional)

Allow contact form email

Remember details