Table creation
In this task, the goal is to create a database table to exemplify most commonly used data types and options.
See also:
AWK
AWK + SQLite
AWK is just a glue language. Simply pipe the creation command into SQLite and capture the output.
<lang awk>#!/bin/sh -f
awk '
BEGIN {
"echo \"create table pow (name, rank, serno);\" |sqlite3 pow.db" | getline print "Result: " $0 exit;
} ' </lang>
J
If we define a table as a named collection of columns, and we define a type as a mechanism for the representation of some kind of data, then:
<lang j>stocks=: |: ,: ;:'date trans symbol qty price' insertStock=: 3 :'0#stocks=: stocks,.y' insertStock@".;._2]0 :0
'2006-01-05'; 'BUY'; 'RHAT'; 100; 35.14 '2006-03-28'; 'BUY'; 'IBM'; 1000; 45.00 '2006-04-05'; 'BUY'; 'MSOFT'; 1000; 72.00 '2006-04-06'; 'SELL'; 'IBM'; 500; 53.00
)</lang>
declares a table and some data within that table.
And, here's an example of sorting:
<lang j>cols=: cols=: [:; {."1@[ <@i.`(<@i.@#@[)@.(=&(<,'*')@]"1 0) cutopen@] sortBy=: [ /: a: |:@,. [ }.@{~ cols from=: cols~ {"0 _ ] select=: |:
select '*' from stocks sortBy 'price'
┌─────┬──────┬─────┬────┬──────────┐ │trans│symbol│price│qty │date │ ├─────┼──────┼─────┼────┼──────────┤ │BUY │RHAT │35.14│100 │2006-01-05│ ├─────┼──────┼─────┼────┼──────────┤ │BUY │IBM │45 │1000│2006-03-28│ ├─────┼──────┼─────┼────┼──────────┤ │BUY │MSOFT │72 │1000│2006-04-05│ ├─────┼──────┼─────┼────┼──────────┤ │SELL │IBM │53 │500 │2006-04-06│ └─────┴──────┴─────┴────┴──────────┘</lang>
Note that this particular example is both overly general in some senses (for example, named column handling has features not demonstrated here) and overly specific in others (for example, I did not implement sort in descending order).
Also, a properly tuned system would likely use different code (for example, you could get better performance if you put an entire column into a box instead of introducing a new box for each element in a column).
Mathematica
<lang Mathematica>Needs["DatabaseLink`"];conn = OpenSQLConnection[JDBC["mysql", "databases:1234/conn_test"], "Username" -> "test"] SQLCreateTable[conn, SQLTable["TEST"],
{
SQLColumn["TINYINTCOL", "DataTypeName" -> "TINYINT"],
SQLColumn["SMALLINTCOL", "DataTypeName" -> "SMALLINT"],
SQLColumn["INTEGERCOL", "DataTypeName" -> "INTEGER"],
SQLColumn["BIGINTCOL", "DataTypeName" -> "BIGINT"],
SQLColumn["NUMERICCOL", "DataTypeName" -> "NUMERIC"],
SQLColumn["DECIMALCOL", "DataTypeName" -> "DECIMAL"],
SQLColumn["FLOATCOL", "DataTypeName" -> "FLOAT"],
SQLColumn["REALCOL", "DataTypeName" -> "REAL"],
SQLColumn["DOUBLECOL", "DataTypeName" -> "DOUBLE"],
SQLColumn["BITCOL", "DataTypeName" -> "BIT"],
SQLColumn["LONGVARBINARYCOL", "DataTypeName" -> "LONGVARBINARY"],
SQLColumn["VARBINARYCOL", "DataTypeName" -> "VARBINARY"],
SQLColumn["BINARYCOL", "DataTypeName" -> "BINARY"],
SQLColumn["LONGVARCHARCOL", "DataTypeName" -> "LONGVARCHAR"],
SQLColumn["VARCHARCOL", "DataTypeName" -> "VARCHAR",
"DataLength" -> 5],
SQLColumn["CHARCOL", "DataTypeName" -> "CHAR", "DataLength" -> 3],
SQLColumn["DATECOL", "DataTypeName" -> "DATE"],
SQLColumn["TIMECOL", "DataTypeName" -> "TIME"],
SQLColumn["TIMESTAMPCOL", "DataTypeName" -> "TIMESTAMP"],
SQLColumn["OBJECTCOL", "DataTypeName" -> "OBJECT"]
}]</lang>
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>
PARI/GP
The most natural way to store tabular data in GP is in a matrix: <lang parigp>m=matrix(10,3); m[1,] = ["Barack", "Obama", 20500]; \\ ...</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>
Ruby
This code is enough to create a PStore (or open an existing PStore).
<lang ruby>require 'pstore' db = PStore.new "filename.pstore"</lang>
The example at Table creation/Postal addresses#Ruby puts Ruby objects into the PStore.
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 | +------------+------------+--------+-------------+---------+