Table creation

In this task, the goal is to create a table to exemplify most commonly used data types and options.

PostgreSQL

Postgres developers, please feel free to add additional data-types you commonly use to this example.

-- This is a comment

CREATE SEQUENCE account_seq start 100;
CREATE TABLE account (
  account_id  int4        PRIMARY KEY DEFAULT nextval('account_seq'),
  created     date        not null default now(),
  active      bool        not null default 't',
  username    varchar(16) unique not null,
  balance     float       default 0,
  age         int2,
  notes       text
);

CREATE TABLE account_note (
  account_id  int4      not null REFERENCES account,
  created     timestamp not null default now(),
  note        text      not null,
  unique(account_id, note)
); 
-- bool:       't', 'f' or NULL
-- int2:       -32768 to +32767
-- int4:       -2147483648 to +2147483647
-- float:      decimal
-- date:       obvious
-- timestamp:  date time
-- char(#):    space padded text field with length of #
-- varchar(#): variable length text field up to #
-- text:       not limited

Python+SQLite

Library: SQLite

The sqlite3 database is a part of the Python standard library. It does not associate type with table columns, any cell can be of any type. <lang python>>>> import sqlite3 >>> conn = sqlite3.connect(':memory:') >>> c = conn.cursor() >>> c.execute(create table stocks (date text, trans text, symbol text,

qty real, price real))

<sqlite3.Cursor object at 0x013263B0> >>> # Insert a row of data c.execute("""insert into stocks

         values ('2006-01-05','BUY','RHAT',100,35.14)""")

<sqlite3.Cursor object at 0x013263B0> >>> for t in [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),

         ('2006-04-05', 'BUY', 'MSOFT', 1000, 72.00),
         ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
        ]:

c.execute('insert into stocks values (?,?,?,?,?)', t)

<sqlite3.Cursor object at 0x013263B0> <sqlite3.Cursor object at 0x013263B0> <sqlite3.Cursor object at 0x013263B0> >>> # Data retrieval >>> c = conn.cursor() >>> c.execute('select * from stocks order by price') <sqlite3.Cursor object at 0x01326530> >>> for row in c: print row

(u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.140000000000001) (u'2006-03-28', u'BUY', u'IBM', 1000.0, 45.0) (u'2006-04-06', u'SELL', u'IBM', 500.0, 53.0) (u'2006-04-05', u'BUY', u'MSOFT', 1000.0, 72.0) >>> </lang>

Tcl

Tables, as used in relational databases, seem far away conceptually from Tcl. However, the following code demonstrates how a table (implemented as a list of lists, the first being the header line) can be type-checked and rendered: <lang Tcl>proc table_update {_tbl row args} {

   upvar $_tbl tbl
   set heads [lindex $tbl 0]
   if {$row eq "end+1"} {
       lappend tbl [lrepeat [llength $heads] {}]
       set row [expr [llength $tbl]-1]
   }
   foreach {key val} $args {
       set col [lsearch $heads $key*]
       foreach {name type} [split [lindex $heads $col] |] break
       if {$type eq "float"} {set type double}
       if {$type eq "date"} {
           if [catch {clock scan $val}] {
               error "bad date value $val"
           }
       } elseif {$type ne ""} {
           if ![string is $type -strict $val] {
               error "bad $type value $val"
           }
       }
       lset tbl $row $col $val
   }

} proc table_format table {

   set maxs {}
   foreach item [lindex $table 0] {
       set item [lindex [split $item |] 0]
       lappend maxs [string length $item]
   }
   foreach row [lrange $table 1 end] {
       set i 0
       foreach item $row max $maxs {
           if {[string length $item]>$max} {lset maxs $i [string length $item]}
           incr i
       }
   }
   set head +
   foreach max $maxs {append head -[string repeat - $max]-+}
   set res $head\n
   foreach row $table {
       if {$row eq [lindex $table 0]} {
           regsub -all {\|[^ ]+} $row "" row
       }
       append res |
       foreach item $row max $maxs {
            append res [format " %-${max}s |" $item]
       }
       append res \n
       if {$row eq [lindex $table 0]} {
           append res $head \n
       }
   }
   append res $head

}

------------------------------------- Test and demo:

set mytbl [list [list \

                    account_id|int \
                    created|date  \
                    active|bool \
                    username \
                    balance|float \
                   ]]

table_update mytbl end+1 \

   account_id 12345 \
   username   "John Doe" \
   balance    0.0 \
   created    2009-05-13

table_update mytbl end+1 \

   account_id 12346 \
   username   "Jane Miller" \
   balance    0.0 \
   created    2009-05-14

puts [table_format $mytbl]</lang> Output:

+------------+------------+--------+-------------+---------+
| account_id | created    | active | username    | balance |
+------------+------------+--------+-------------+---------+
| 12345      | 2009-05-13 |        | John Doe    | 0.0     |
| 12346      | 2009-05-14 |        | Jane Miller | 0.0     |
+------------+------------+--------+-------------+---------+