Table creation: Difference between revisions
m (→{{header|Python}}+SQLite: link to sqlite) |
m (moved Table Creation to Table creation) |
(No difference)
|
Revision as of 15:03, 6 February 2010
You are encouraged to solve this task according to the task description, using any language you may know.
In this task, the goal is to create a table to exemplify most commonly used data types and options.
See also:
Oz
<lang oz>declare
[Sqlite] = {Module.link ['x-ozlib:/sqlite/Sqlite.ozf']}
DB = {Sqlite.open 'test.db'}
in
try %% show strings as text, not as number lists {Inspector.configure widgetShowStrings true}
%% create table {Sqlite.exec DB "create table stocks(date text, trans text, symbol test," #"qty real, price real)" _} %% insert using a SQL string {Sqlite.exec DB "insert into stocks values " #"('2006-01-05','BUY','RHAT',100,35.14)" _} %% insert with insert procedure for T in
[r(date:"2006-03-28" trans:"BUY" symbol:"IBM" qty:1000 price:45.00) r(date:"2006-04-05" trans:"BUY" symbol:"MSOFT" qty:1000 price:72.00) r(date:"2006-04-06" trans:"SELL" symbol:"IBM" qty:500 price:53.00)]
do
{Sqlite.insert DB stocks T}
end %% read table and show rows in Inspector for R in {Sqlite.exec DB "select * from stocks order by price"} do
{Inspect R}
end
catch E then {Inspect E} finally {Sqlite.close DB} end
</lang>
PostgreSQL
Postgres developers, please feel free to add additional data-types you commonly use to this example.
<lang sql>-- 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</lang>
Python+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 | +------------+------------+--------+-------------+---------+