Table creation: Difference between revisions
m Never use {{header|SQLite}}, because this wrongly sets semantic property ((implemented in language::SQLite)), when SQLite is not a language. |
J |
||
Line 4: | Line 4: | ||
See also: |
See also: |
||
* [[Table Creation - Address]] |
* [[Table Creation - Address]] |
||
=={{header|J}}== |
|||
If we define a <code>table</code> as a named collection of columns, and we define a <code>type</code> 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). |
|||
=={{header|Oz}}== |
=={{header|Oz}}== |
Revision as of 13:28, 28 September 2011
In this task, the goal is to create a database table to exemplify most commonly used data types and options.
See also:
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).
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 | +------------+------------+--------+-------------+---------+