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).
FunL
<lang funl>import db.* import util.*
Class.forName( 'org.h2.Driver' ) conn = DriverManager.getConnection( "jdbc:h2:~/test", "sa", "" ) statement = conn.createStatement() statement.execute(
CREATE TABLE Persons ( PersonID int, FirstName varchar(255), LastName varchar(255), Address varchar(255), City varchar(255), Province char(2) ) )
statement.execute(
INSERT INTO Persons VALUES
(1, 'Sylvia', 'Henry', '5896 Cotton Prairie Wharf', 'Parrsboro', 'SK'),
(2, 'Kelly', 'Saunders', '3608 Indian Island Promenade', 'Goober Hill', 'SK'),
(3, 'Vernon', 'Douglas', '394 Dusty Impasse', 'Muleshoe', 'NS'),
(4, 'Jim', 'Fleming', '2523 Quaking Fawn Trace', 'Halbrite', 'ON'),
(5, 'Roderick', 'Owens', '7596 Umber View', 'Frognot', 'SK')
)
statement.execute( "SELECT * FROM Persons ORDER BY LastName" ) print( TextTable.apply(statement.getResultSet()) ) conn.close()</lang>
- Output:
+----------+-----------+----------+------------------------------+-------------+----------+ | PERSONID | FIRSTNAME | LASTNAME | ADDRESS | CITY | PROVINCE | +----------+-----------+----------+------------------------------+-------------+----------+ | 3 | Vernon | Douglas | 394 Dusty Impasse | Muleshoe | NS | | 4 | Jim | Fleming | 2523 Quaking Fawn Trace | Halbrite | ON | | 1 | Sylvia | Henry | 5896 Cotton Prairie Wharf | Parrsboro | SK | | 5 | Roderick | Owens | 7596 Umber View | Frognot | SK | | 2 | Kelly | Saunders | 3608 Indian Island Promenade | Goober Hill | SK | +----------+-----------+----------+------------------------------+-------------+----------+
Mathematica
<lang Mathematica>Needs["DatabaseLink`"];conn = OpenSQLConnection[JDBC["mysql", "databases:1234/conn_test"], "Username" -> "test"] SQLCreateTable[conn, SQLTable["TEST"],If[Length[#] == 0,SQLColumn[StringJoin[#,"COL"],"DataTypeName" -> #],SQLColumn[StringJoin[#1, "COL"], "DataTypeName" -> #1,"DataLength" -> #2]] & /@ {"TINYINT", "SMALLINT", "INTEGER","BIGINT", "NUMERIC", "DECIMAL", "FLOAT", "REAL", "DOUBLE", "BIT","LONGVARBINARY", "VARBINARY", "BINARY","LONGVARCHAR",{"VARCHAR", 5},{"CHAR", 3},"DATE","TIME","TIMESTAMP","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
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>
Racket
This is the relevant part of Table creation/Postal addresses#Racket that creates the DB table: <lang racket>
- lang racket
(require db) (define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create)) </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.
Run BASIC
Run Basic supports all features of SQLite. This is a sample of a item master <lang runbasic>
- sql execute("
CREATE TABLE item ( itemNum SMALLINT(4), descr VARCHAR(30), short VARCHAR(10), cartSw CHAR(1), itemCat CHAR(2), itemType VARCHAR(4), itemDate DATE, uomId VARCHAR(4), decml TINYINT(2), onHand FLOAT, onOrder DECIMAL(9,2), eoq INT(11), weight DECIMAL(8,1) , length DECIMAL(8,1) , width DECIMAL(8,1) , height DECIMAL(8,1) , compNum INT(10), status CHAR(1), itemUrl VARCHAR(200), photoId VARCHAR(40), photoHigh SMALLINT(4), photoWide SMALLINT(4), specs TEXT, notes TEXT, treatUomId VARCHAR(4) , errMinQty FLOAT , errMaxQty FLOAT , wrnMinQty FLOAT , wrnMaxQty FLOAT , labUomId VARCHAR(4) , labErrMin FLOAT , labErrMax FLOAT , labWrnMin FLOAT )
CREATE UNIQUE INDEX item_descr ON item( descr, itemNum); CREATE UNIQUE INDEX item_itemNum ON item(itemNum);" </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 | +------------+------------+--------+-------------+---------+