sobota, 4 grudnia 2010

SQLite 3 - cheat sheet

  1. Create new database initialised with given sql script

    sqlite3 -init /path/to/script/centrum2.sql crtest.db
    

    alternativly load script in interactive mode
    sqlite3 crtest.db
    sqlite3> .read /path/to/script/centrum2.sql 
    
  2. Rename particular table
    alter table TBL_PRODUCTS rename to PRODUCTS_OLD;
    
  3. Rename particular column name without loosing data

    Actually it is not possible directly. The workaround can be to create an empty table with new set of columns and populate it with data comes from original table.

    create table PRODUCTS_NEW (..);
    insert into PRODUCTS_NEW select * from PRODUCTS;
    drop table PRODUCTS;
    alter table PRODUCTS_NEW rename to PRODUCTS;
    

    Another workaround can be to use CREATE TABLE .. AS SELECT statement.

    create table PRODUCTS_NEW as select id, name, code as pcode, description from PRODUCTS;
    alter table PRODUCTS_NEW rename to PRODUCTS;