Table creation
Create a database table to exemplify most commonly used data types and options.
- Task
- Related task
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 | +----------+-----------+----------+------------------------------+-------------+----------+
Lua
<lang 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","John.Doe000@ExampleEmail.com"); Insert(#Columns.ID,"Jane","Miller","Jane.Miller000@ExampleEmail.com"); Insert(#Columns.ID,"Eerie","Crate","Eeriecrate@ExampleEmail.com"); --[[ ]]--
listTables();</lang>
- Output:
----------------------------------------------------- | ID| FName| LName | Email | ----------------------------------------------------- | 0 | John | Doel | John.Doe000@ExampleEmail.com | | 1 | Jane | Miller| Jane.Miller000@ExampleEmail.com| | 2 | Eerie| Crate | Eeriecrate@ExampleEmail.com | -----------------------------------------------------
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>
Oracle
Great SCOTT! from utlsampl.sql <lang 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);
</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>
Perl 6
In Perl 6, there is no 'database' type built in, so it is somewhat ambiguous when specifying 'create a database table'. Perl 6 offers bindings to most common databases through its DBIish module but mostly abstracts away the differences between the underlying databases, which hides many of the finer distinctions of what may be stored where. The actual data types and options available are properties of the database used.
If on the other hand, we are meant to show built in collective types that may be used to hold tabular data, this may be of some use.
In general, a container type can hold objects of any data type, even instances of their own type; allowing 'multi-dimensional' (tabular) containers.
Perl 6 offers two broad categories of collective container types; those that do the Positional role and those that do Associative. Positional objects are collective objects that access the individual storage slots using an integer index. Associative objects use some sort of other pointer (typically string) to access their storage slots.
The various Associative types mostly differ in their value handling. Hash, Map and QuantHash may have any type of object as their value. All the others have some specific, usually numeric, type as their value.
Positional - Object that supports looking up values by integer index
Array Sequence of itemized objects
List Immutable sequence of objects
Associative - Object that supports looking up values by key (typically string)
Bag Immutable collection of distinct objects with integer weights
BagHash Mutable collection of distinct objects with integer weights
Hash Mapping from strings to itemized values
Map Immutable mapping from strings to values
Mix Immutable collection of distinct objects with Real weights
MixHash Mutable collection of distinct objects with Real weights
QuantHash Collection of objects represented as hash keys
Set Immutable collection of distinct objects, no value except 'present'
SetHash Mutable collection of distinct objects, no value except 'present'
If you want a persistent instance of any of these types, you need to declare the name with some scope constraint, but the are no prerequisites to creating instances. Simply assigning values to them will call them into existence.
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.!firstname = 'Robert' table.id.!middlename = 'Jon' table.id.!lastname = 'Smith' 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>
Ring
<lang ring>
- Project : Table creation
- Date : 2017/10/16
- Author : Gal Zsolt (~ CalmoSoft ~)
- Email : <calmosoft@gmail.com>
load "stdlib.ring" oSQLite = sqlite_init()
sqlite_open(oSQLite,"mytest.db")
sql = "CREATE TABLE COMPANY(" +
"ID INT PRIMARY KEY NOT NULL," +
"NAME TEXT NOT NULL," +
"AGE INT NOT NULL," +
"ADDRESS CHAR(50)," +
"SALARY REAL );"
sqlite_execute(oSQLite,sql)
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Mahmoud', 29, 'Jeddah', 20000.00 ); " +
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Ahmed', 27, 'Jeddah', 15000.00 ); " +
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" +
"VALUES (3, 'Mohammed', 31, 'Egypt', 20000.00 );" +
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" +
"VALUES (4, 'Ibrahim', 24, 'Egypt ', 65000.00 );"
sqlite_execute(oSQLite,sql)
aResult = sqlite_execute(oSQLite,"select * from COMPANY") for x in aResult
for t in x
see t[2] + nl
next
next see copy("*",50) + nl for x in aResult
see x["name"] + nl
next sqlite_close(oSQLite) </lang> Output:
1 Mahmoud 29 Jeddah 20000.0 2 Ahmed 27 Jeddah 15000.0 3 Mohammed 31 Egypt 20000.0 4 Ibrahim 24 Egypt 65000.0 ************************************************** Mahmoud Ahmed Mohammed Ibrahim
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 | +------------+------------+--------+-------------+---------+