Table creation

From Rosetta Code
Table creation is a draft programming task. It is not yet considered ready to be promoted as a complete task, for reasons that should be found in its talk page.

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.

#!/bin/sh -f
awk '
BEGIN {
"echo \"create table pow (name, rank, serno);\" |sqlite3 pow.db" | getline
print "Result: " $0
exit;
}
'

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:

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
)

declares a table and some data within that table.

And, here's an example of sorting:

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.141002006-01-05
├─────┼──────┼─────┼────┼──────────┤
│BUY │IBM │4510002006-03-28
├─────┼──────┼─────┼────┼──────────┤
│BUY │MSOFT │7210002006-04-05
├─────┼──────┼─────┼────┼──────────┤
│SELL │IBM │535002006-04-06
└─────┴──────┴─────┴────┴──────────┘

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

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()
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       |
+----------+-----------+----------+------------------------------+-------------+----------+

Lua

Columns = {};
Columns.ID = {};
Columns.FName = {};
Columns.LName = {};
Columns.Email = {};
Columns.Names = {"ID","FName","LName","Email"};
 
function Insert(id,fname,lname,email)
table.insert(Columns.ID, id);
table.insert(Columns.FName, fname);
table.insert(Columns.LName, lname);
table.insert(Columns.Email, email);
end
 
for i,v in pairs(Columns.ID) do
print(v,Columns.FName[i],Columns.LName[i]);
end
 
function getMax(Table)
local cmax = #Table
for i,v in pairs(Columns[Table]) do
if #tostring(v) > cmax then
cmax = #tostring(v)
end
end
return cmax;
end
 
function listTables()
local Total = (#Columns.Names*2)+1;
for i,v in pairs(Columns.Names) do
Total = Total + getMax(v);
end
print()
local CS = "|";
for i,v in pairs(Columns.Names) do
CS = CS.." "..v..string.rep(" ",(getMax(v)-#v)).."|";
end
print(string.rep("-",Total).."\n"..CS.."\n"..string.rep("-",Total))
for it = 1,#Columns.ID do
CS = "|";
for i,v in pairs(Columns.Names) do
CS = CS.." "..Columns[v][it]..string.rep(" ",(getMax(v)-(#tostring((Columns[v][it]))))).."|";
end
print(CS);
end
print(string.rep("-",Total));
end
 
--[[Inserting items]]--
Insert(#Columns.ID,"John","Doel","[email protected]");
Insert(#Columns.ID,"Jane","Miller","[email protected]");
Insert(#Columns.ID,"Eerie","Crate","[email protected]");
--[[ ]]--
 
listTables();
Output:
-----------------------------------------------------
| ID| FName| LName | Email                          |
-----------------------------------------------------
| 0 | John | Doel  | [email protected]   |
| 1 | Jane | Miller| [email protected]|
| 2 | Eerie| Crate | [email protected]    |
-----------------------------------------------------

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"}]

Oracle

Great SCOTT! from utlsampl.sql

 
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
 

Oz

Translation of: Python
Library: SQLite
Library: Ozsqlite
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
 

PARI/GP

The most natural way to store tabular data in GP is in a matrix:

m=matrix(10,3);
m[1,] = ["Barack", "Obama", 20500];
\\ ...

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);

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)) ) ) )

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.

-- 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

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.

>>> 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)
>>>

Racket

This is the relevant part of Table creation/Postal addresses#Racket that creates the DB table:

 
#lang racket
(require db)
(define postal (sqlite3-connect #:database "/tmp/postal.db" #:mode 'create))
 

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.

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'

Ruby

This code is enough to create a PStore (or open an existing PStore).

require 'pstore'
db = PStore.new "filename.pstore"

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

 
#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);"
 

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:

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]

Output:

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