Table creation: Difference between revisions
(Puttin) |
|||
| Line 1: | Line 1: | ||
{{task|Database operations}} |
{{draft task|Database operations}} |
||
In this task, the goal is to create a table to exemplify most commonly used data types and options. |
In this task, the goal is to create a table to exemplify most commonly used data types and options. |
||
Revision as of 05:16, 6 December 2010
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>
PL/I
<lang PL/I> declare 1 table (100),
2 name character (20) varying,
2 address,
3 number fixed decimal,
3 street character (30) varying,
3 suburb character (30) varying,
3 zip picture '9999',
2 transaction_date date,
2 sex character (1),
2 suppress_junk_mail bit (1);
</lang>
PicoLisp
<lang PicoLisp>(scl 2)
(class +Account +Entity) (rel id (+Key +Number)) (rel created (+Date)) (rel active (+Bool)) (rel username (+Key +String)) (rel balance (+Number) 2) (rel age (+Number)) (rel notes (+Blob))
(pool "account.db") # Create database
(new! '(+Account)
'id 12345 'username "John Doe" 'balance 77.22 'created (date 2009 5 13) )
(new! '(+Account)
'id 12346 'username "Jane Miller" 'active T 'created (date 2009 5 14) 'balance 123.75 )
(let Fmt (-13 -10 -9 -11 10)
(tab Fmt "account_id" "created" "active" "username" "balance")
(for This (collect 'id '+Account)
(tab Fmt
(: id)
(dat$ (: created))
(if (: active) "Yes" "No")
(: username)
(money (: balance)) ) ) )</lang>
Output:
account_id created active username balance 12345 20090513 No John Doe 77.22 12346 20090514 Yes Jane Miller 123.75
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>
REXX
REXX doesn't have tables (structures), as there is only one data type in REXX:
character
However, tables (or structures) can be constructed by using stemmed arrays,
the index would (should) be a unique identifier, something akin to a SSN
(Social Security Number) or something similar.
<lang rexx>
id=000112222 /*could be a SSN or some other unique id (or number).*/
table.id.!lastname ='Smith' table.id.!firstname='Robert' table.id.!middlename='Jon' table.id.!dob ='06/09/1946' table.id.!gender='m' table.id.!phone ='(111)-222-3333' table.id.!addr ='123 Elm Drive\Apartment 6A' table.id.!town ='Gotham City' table.id.!state ='NY' table.id.!zip ='12345-6789' </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 | +------------+------------+--------+-------------+---------+