Top

A Linux User Reference

Search tips
  • search ignores words that are less than 4 characters in length
  • searches are case insensitve
  • if a search does not return anything try it in Boolean mode then Query expansion mode by checking the appropriate radio button e.g. searching for 'cron' in just the Administration category returns nothing - presumably because the 50% threshold is reached. Boolean mode ignores this threshold so a search for 'cron' returns several hits
  • in Boolean mode preceding a word with a '+' means the result must include that word, a '-' means it must not
  • in Boolean mode '+crontab -anacron' means match articles about crontab that DO NOT mention anacron
  • to match a phrase e.g. 'manage system' check the Boolean mode radio button and enclose the phrase in quotes "some phrase ..."
  • in Query expansion mode the search context is expanded beyond the keywords you entered - relevancy of hits may well be degraded

DATABASE

SqLite3

  • Serverless, zero-configuration, transactional SQL database

    "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain."

    While having a look at django I used sqlite3 as the back-end. What follows are the steps I took to create the DB.

    First of all download sqlite3 and install. Each DB instance is a single file, change directory to wherever you want the instance to reside.

  • Create a db instance

    Create DB called Corner_shop

    # sqlite3 Corner_shop
    SQLite version 3.6.10
    Enter ".help" for instructions
    Enter SQL statements terminated with a ";"
    
    sqlite> .help
    .bail ON|OFF           Stop after hitting an error.  Default OFF
    .databases             List names and files of attached databases
    .dump ?TABLE? ...      Dump the database in an SQL text format
    .echo ON|OFF           Turn command echo on or off
    .exit                  Exit this program
    .....
    .tables ?PATTERN?      List names of tables matching a LIKE pattern
    .timeout MS            Try opening locked tables for MS milliseconds
    .timer ON|OFF          Turn the CPU timer measurement on or off
    .width NUM NUM ...     Set column widths for "column" mode
    sqlite>
    

    The command 'sqlite3' starts an interactive session. If the optional parameter 'name of a database file' is provided then, if the file exists it is opened else it is created.

  • Create a basic table
    sqlite> create table Products(
       ...> Product_code varchar(6) not null primary key,
       ...> Product_name varchar(25),
       ...> Supplier varchar(60)
       ...> );
    

    Select all rows from table

    sqlite> select * from Products;
    Prod  Product_name   Supp
    ----  -------------  ----
    p00001  Super Coffee   The Bean Factory, Lower St., St.Annes
    p00002  Super Tea      The Leaf Specialists, Upper St., London
    p00003  Baked Beans    Cheap Supermarket, Dodge Lane, Blackpool
    p00004  Special Lager  Booze 4 us, Sot Lane, Newcastle
    p00005  Whacko Brandy  Booze 4 us, Sot Lane, Newcastle
    p00006  Finest Tobacco  Tout-les-jour, Best Rue, Calais
    sqlite>
    

    I know, this table has magically aquired some data!! Insert statements follow a tad later.

  • Create a table with a foreign key constraint
    sqlite> create table Product_history(
       ...> Product_id varchar(8) not null,
       ...> Opening_stock int(6),
       ...> Purchases int(6),
       ...> Closing_stock int(6),
       ...> Sales int(6),
       ...> Unit_cost decimal(8,2),
       ...> Unit_sale decimal(8,2),
       ...> Date date,
       ...> foreign key(Product_id) references Products(Product_code) on insert cascade \
            on update cascade
       ...> );
    

    Test the foreign key constraint

    sqlite> insert into Product_history values("p00x7","35","96","48","83","0.55","0.95","2009-07-01");
    SQL error: insert on table "Product_history" violates foreign key constraint
    

    'p00x7' is an unknown product, it does not exist in the Products table.

  • List existing tables in db instance
    sqlite> .tables
    Product_history  Products
    
  • Triggers

    Create a trigger

    create trigger cost_test after insert on InvoiceItem begin
       -----> insert into CostHistory (prod_id, cost, vat, date)
       -----> select prod_id, cost, vat, date() from InvoiceItem where prod_id=new.prod_id;
       -----> end;
    

    Delete a trigger

    sqlite> drop trigger cost_test;
    

    Create trigger before insert

    sqlite> create trigger fki_prod_id
       ...> before insert on Product_history
       ...> for each row begin
       ...> select raise(rollback, 'insert on table "Product_history" violates foreign key constraint')
       ...> where new.Product_id is not null and (
       ...> select Product_code from Products
       ...> where Product_code = new.Product_id) is null;
       ...> end;
    

    For each row of data to be inserted into Product_History check first that it's product id exists in the Products table.

  • Insert data using a delimited data file
    .import
    sqlite> .separator ";"
    sqlite> .import /home/mark/scripts/sqlite/products.txt Products
    
    sqlite> .separator ","
    sqlite> .import /home/mark/scripts/sqlite/prodhist.txt Product_history
    

    Full pathname to file or reletive to location of the database file.

    '.separator' defines the data file's field separator or delimiter.

  • Insert and update data
    sqlite> insert into Products values("p00001","Super Coffee","The Bean Factory, \
            Lower St., St.Annes");
    
    sqlite> insert into Products values("p00002","Super Tea","The Leaf Specialists, \
            Upper St., London");
    

    Update a table

    sqlite> update StockHistory set close=null, date=null;
    
  • Insert data using sql statements in a file
    .read
    sqlite> select * from Products;
    
    sqlite>
    sqlite> .read /home/mark/insert-sql.txt
    sqlite>
    sqlite> select * from Products;
    p00001|Super Coffee|The Bean Factory, Lower St., St.Annes
    p00002|Super Tea|The Leaf Specialists, Upper St., London
    sqlite>
    

    File /home/mark/insert-sql.txt contains the same two insert statements used in the previous insert example.

  • Create an index on existing tables
    sqlite> create index ids on Products(Product_code);
    sqlite> create index id on Product_history(Product_id, Date);
    
  • View table data in different modes
    .mode

    Default mode

    sqlite> select * from Products;
    p00001|Super Coffee|The Bean Factory, Lower St., St.Annes
    p00002|Super Tea|The Leaf Specialists, Upper St., London
    .....
    

    Line mode

    sqlite> .mode line
    sqlite> select * from Products;
    Product_code = p00001
    Product_name = Super Coffee
        Supplier = The Bean Factory, Lower St., St.Annes
    Product_code = p00002
    Product_name = Super Tea
        Supplier = The Leaf Specialists, Upper St., London
    Product_code = p00003
    .....
    

    Column mode

    sqlite> .mode column
    sqlite> select * from Product_history;
    p00001      0    12    4      8     1.9    2.49    2009-04-01
    p00002      0    12    1      11    1.35   1.99    2009-04-01
    .....
    p00005      6    12    3      15    1.4    2.05    2009-07-01
    p00006      40   500   150    390   2.7    7.5     2009-07-01
    
  • View output in different formats
    .explain, .separator

    Remove column headings and change field separator

    sqlite> .explain off
    sqlite> .separator "|"
    sqlite> select * from prods;
    p00001|Super Coffee|2.49|17.5
    p00002|Super Tea|1.99|17.5
    .....
    

    Display column headings

    sqlite> .explain on
    sqlite> select * from prods;
    prod  desc           pric  vat
    ----  -------------  ----  ----
    p00001  Super Coffee   2.49  17.5
    p00002  Super Tea      1.99  17.5
    p00003  Baked Beans    0.65  17.5
    .....
    

    Turn headings off again and set field separator to [space]

    sqlite> .explain off
    sqlite> .separator " "
    sqlite> select * from prods;
    p00001 Super Coffee 2.49 17.5
    p00002 Super Tea 1.99 17.5
    p00003 Baked Beans 0.65 17.5
    .....
    
  • Show create statements
    .schema

    Display all create statements for the database

    sqlite> .schema
    CREATE TABLE Product_history(Product_id varchar(8) not null, Opening_stock int(6), \
    Purchases int(6), Closing_stock int(6), Sales int(6), Unit_cost decimal(8,2), \
    Unit_sale decimal(8,2), Date date, foreign key(Product_id) references Products(Product_code) \
    on insert cascade on update cascade);
    
    CREATE TABLE Products(Product_code varchar(6) not null primary key, Product_name varchar(25), \
    Supplier varchar(60));
    
    CREATE INDEX id on Product_history(Product_id, Date);
    
    CREATE INDEX ids on Products(Product_code);
    
    CREATE TRIGGER fki_prod_id
    before insert on Product_history
    for each row begin
    SELECT RAISE(ROLLBACK, 'insert on table "Product_history" violates foreign key constraint ')
    where new.Product_id is not null and (select Product_code from Products \
    where Product_code = new.Product_id) is null;
    end;
    
    CREATE TRIGGER fku_prod_id
    before update on Product_history
    for each row begin
    SELECT RAISE(ROLLBACK, 'update on table "Product_history" violates foreign key constraint ')
    where new.Product_id is not null and (select Product_code from Products \
    where Product_code = new.Product_id) is null;
    end;
    sqlite>
    
  • Delete a table, a row, muliple rows, all rows

    Delete table

    sqlite> drop table Products;
    

    Delete all rows in a table

    sqlite> delete from StockTake;
    

    Use a where clause to be more selective about which rows are to be deleted.

    Delete multiple rows in a table using a list

    sqlite> delete from Stockhistory where id in (7,8,9,10,11,12,13,14);
    
  • Create a copy of a table
    sqlite> select * from Invoice;
    inv000001|v00001|2009-04-01
    inv000002|v00002|2009-04-01
    .....
    sqlite>
    sqlite> create table inv as select * from Invoice;
    sqlite> select * from inv;
    inv000001|v00001|2009-04-01
    inv000002|v00002|2009-04-01
    inv000003|v00003|2009-04-01
    .....
    
  • Creat a composite primary key using foreign keys
    sqlite> create table items(
      id integer,
      invoice_ref varchar(20),
      vend_id varchar(6),
      prod_id varchar(6),
      qty integer,
      cost decimal(8,2),
      vat decimal(4,2),
      foreign key(invoice_ref,vend_id) references inv(invoice_ref,vend_id), 
      primary key(invoice_ref,vend_id)
    );
    
  • Date and time
    some sqlite3 doc examples
    Variable Description
    %d Day of month: 00
    %f Fractional seconds: SS.SSS
    %H Hour: 00-24
    %j Day of year: 001-366
    %J Julian day number
    %m Month: 01-12
    %M Minute: 00-59
    %s Seconds since 1970-01-01
    %S Seconds: 00-59
    %w Day of week 0-6 with sunday==0
    %W Week of year: 00-53
    %Y Year: 0000-9999
    %% %

    All other date and time functions can be expressed in terms of strftime():

    Function Equivalent strftime()
    date(...) strftime('%Y-%m-%d', ...)
    time(...) strftime('%H:%M:%S', ...)
    datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
    julianday(...) strftime('%J', ...)

    Select date

    sqlite> select date();
    2011-04-28
    

    Compute the current date

    sqlite> SELECT date('now');
    20011-04-28
    

    Compute the last day of the current month

    sqlite> SELECT date('now','start of month','+1 month','-1 day');
    2011-04-30
    

    Compute the date and time given a unix timestamp 1092941466

    sqlite> SELECT datetime(1092941466, 'unixepoch');
    2004-08-19 18:51:06
    

    Compute the date and time given a unix timestamp 1092941466 and compensate for your local timezone

    sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
    2004-08-19 19:51:06
    

    Compute the current unix timestamp

    sqlite> SELECT strftime('%s','now');
    1304010838
    

    Compute the number of days since the signing of the US Declaration of Independent

    sqlite> SELECT julianday('now') - julianday('1776-07-04');
    85764.7176691434
    

    Compute the number of seconds since a particular moment in 2004

    sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
    231086280
    

    Compute the date of the first Tuesday in October for the current year

    sqlite> SELECT date('now','start of year','+9 months','weekday 2');
    2011-10-04
    

    Compute the time since the unix epoch in seconds (like strftime'%s', 'now') except includes fractional part)

    sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
    1304010669.04302