Table creation
- Task
Create a database table to exemplify most commonly used data types and options.
- Related task
Arturo
; Helper functions
createTable: function [][
query db {!sql DROP TABLE IF EXISTS users}
query db {!sql
CREATE TABLE users (
ID INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL,
email TEXT NOT NULL,
age INTEGER
)
}
]
addUser: function [name, email, age][
query.id db ~{!sql
INSERT INTO users (username, email, age)
VALUES ('|name|','|email|','|age|')
}
]
findUser: function [name][
query db ~{!sql
SELECT *
FROM users
WHERE username='|name|'
}
]
db: open.sqlite "users.db"
createTable
print ["added user with id:" addUser "JohnDoe" "jodoe@gmail.com" 35]
print ["added user with id:" addUser "JaneDoe" "jadoe@gmail.com" 14]
print ["getting user with name: JohnDoe =>" findUser "JohnDoe"]
close db
- Output:
added user with id: 1 added user with id: 2 getting user with name: JohnDoe => [[1 JohnDoe jodoe@gmail.com 35]]
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;
}
'
BASIC256
# create a new database file or open it
dbopen "mydbtest.sqlite3"
# delete the existing table in Personas - If it is a new database, the error is captured
onerror errortrap
dbexecute "drop table Personas;"
offerror
# create the table and enter data into it
dbexecute "create table Personas (id integer, nombre text, apellido text, edad integer, direccion string(50), salario decimal);"
dbexecute "insert into Personas values (1, 'Juan', 'Hdez', 52, '123 Calle Principal', 50000.00);"
# open a recordset and loop through the rows of data
print "Contents of the Personas table:"
dbopenset "select * from Personas order by nombre;"
while dbrow()
print dbint(0) + " " + dbstring(1) + " " + dbstring(2) + " " + dbint(3) + " " + dbstring(4) + " " + dbfloat(5)
end while
dbcloseset
# close all
dbclose
end
errortrap:
# accept the error - show nothing - return to the next statement
return
- Output:
Contents of the Personas table: 1 Juan Hdez 52 123 Calle Principal 50000.0
C
Most database tables store data as text, number (mostly integer) and date or date time columns. Specialized requirements would need BLOB and other datatypes. The following implementation requires SQLite.
#include <sqlite3.h>
#include <stdlib.h>
#include <stdio.h>
int main()
{
sqlite3 *db = NULL;
char *errmsg;
const char *code =
"CREATE TABLE employee (\n"
" empID INTEGER PRIMARY KEY AUTOINCREMENT,\n"
" firstName TEXT NOT NULL,\n"
" lastName TEXT NOT NULL,\n"
" AGE INTEGER NOT NULL,\n"
" DOB DATE NOT NULL)\n" ;
if ( sqlite3_open("employee.db", &db) == SQLITE_OK ) {
sqlite3_exec(db, code, NULL, NULL, &errmsg);
sqlite3_close(db);
} else {
fprintf(stderr, "cannot open db...\n");
sqlite3_close(db);
exit(EXIT_FAILURE);
}
return 0;
}
DuckDB
The SQL presented in the #SQL_PL and #PostgreSQL sections on this page is processed by DuckDB without incident and is therefore not repeated here. Instead, the output of the DuckDB 'DESCRIBE' command is shown for the four tables.
describe dept;
- Output:
┌─────────────┬──────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├─────────────┼──────────────┼─────────┼─────────┼─────────┼─────────┤ │ deptno │ DECIMAL(2,0) │ NO │ PRI │ │ │ │ dname │ VARCHAR │ NO │ UNI │ │ │ │ loc │ VARCHAR │ YES │ │ │ │ └─────────────┴──────────────┴─────────┴─────────┴─────────┴─────────┘
describe emp;
- Output:
┌─────────────┬──────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├─────────────┼──────────────┼─────────┼─────────┼─────────┼─────────┤ │ empno │ DECIMAL(4,0) │ NO │ PRI │ │ │ │ ename │ VARCHAR │ YES │ │ │ │ │ job │ VARCHAR │ YES │ │ │ │ │ mgr │ DECIMAL(4,0) │ YES │ │ │ │ │ hiredate │ DATE │ YES │ │ │ │ │ sal │ DECIMAL(7,2) │ YES │ │ │ │ │ comm │ DECIMAL(7,2) │ YES │ │ │ │ │ deptno │ DECIMAL(2,0) │ YES │ │ │ │ └─────────────┴──────────────┴─────────┴─────────┴─────────┴─────────┘
describe account;
- Output:
┌─────────────┬─────────────┬─────────┬─────────┬────────────────────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├─────────────┼─────────────┼─────────┼─────────┼────────────────────────┼─────────┤ │ account_id │ INTEGER │ NO │ PRI │ nextval('account_seq') │ │ │ created │ DATE │ NO │ │ now() │ │ │ active │ BOOLEAN │ NO │ │ 't' │ │ │ username │ VARCHAR │ NO │ UNI │ │ │ │ balance │ FLOAT │ YES │ │ 0 │ │ │ age │ SMALLINT │ YES │ │ │ │ │ notes │ VARCHAR │ YES │ │ │ │ └─────────────┴─────────────┴─────────┴─────────┴────────────────────────┴─────────┘
describe account_note;
- Output:
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤ │ account_id │ INTEGER │ NO │ UNI │ │ │ │ created │ TIMESTAMP │ NO │ │ now() │ │ │ note │ VARCHAR │ NO │ UNI │ │ │ └─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
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 | +----------+-----------+----------+------------------------------+-------------+----------+
FreeBASIC
#include once "sqlite3.bi"
Const NULL As Any Ptr = 0
Dim As sqlite3 Ptr db
Dim As zstring Ptr errMsg
Dim As sqlite3_stmt Ptr stmt
If sqlite3_open(":memory:", @db) <> SQLITE_OK Then
Print "Could not open database: "; sqlite3_errmsg(db)
sqlite3_close(db)
Sleep
End 1
End If
' Create the table
Dim As String sql = "CREATE TABLE Persons(" _
& "ID INT PRIMARY KEY NOT NULL," _
& "NAME TEXT NOT NULL," _
& "SURNAME TEXT NOT NULL," _
& "AGE INT NOT NULL," _
& "ADDRESS CHAR(50)," _
& "SALARY REAL );"
If sqlite3_exec(db, sql, NULL, NULL, @errMsg) <> SQLITE_OK Then
Print "Error creating table: "; *errMsg
sqlite3_free(errMsg)
Else
Print "Table created successfully"
End If
' Insert some data
sql = "INSERT INTO Persons (ID, NAME, SURNAME, AGE, ADDRESS, SALARY) " _
& "VALUES (1, 'John', 'Doe', 30, '123 Main St', 50000.00);"
If sqlite3_exec(db, sql, NULL, NULL, @errMsg) <> SQLITE_OK Then
Print "Error inserting data: "; *errMsg
sqlite3_free(errMsg)
End If
' Display the contents of the table
sql = "SELECT * FROM Persons;"
If sqlite3_prepare_v2(db, sql, -1, @stmt, NULL) <> SQLITE_OK Then
Print "Error preparing statement: "; sqlite3_errmsg(db)
Else
While sqlite3_step(stmt) = SQLITE_ROW
Print "ID: "; sqlite3_column_int(stmt, 0)
Print "Name: "; *cast(zstring ptr, sqlite3_column_text(stmt, 1))
Print "Surname: "; *cast(zstring ptr, sqlite3_column_text(stmt, 2))
Print "Age: "; sqlite3_column_int(stmt, 3)
Print "Address: "; *cast(zstring ptr, sqlite3_column_text(stmt, 4))
Print "Salary: "; sqlite3_column_double(stmt, 5)
Print
Wend
End If
sqlite3_finalize(stmt)
sqlite3_close(db)
Sleep
- Output:
Table created successfully ID: 1 Name: Juan Surname: Hdez Age: 52 Address: 123 Main St Salary: 50000
Go
This uses a key/value store rather than a relational database to create the table.
package main
import (
"encoding/binary"
"encoding/json"
"fmt"
"github.com/boltdb/bolt"
"log"
)
type StockTrans struct {
Id int // this will be auto-incremented by db
Date string
Trans string
Symbol string
Quantity int
Price float32
Settled bool
}
// save stock transaction to bucket in db
func (st *StockTrans) save(db *bolt.DB, bucket string) error {
err := db.Update(func(tx *bolt.Tx) error {
b := tx.Bucket([]byte(bucket))
id, _ := b.NextSequence()
st.Id = int(id)
encoded, err := json.Marshal(st)
if err != nil {
return err
}
return b.Put(itob(st.Id), encoded)
})
return err
}
// itob returns an 8-byte big endian representation of i.
func itob(i int) []byte {
b := make([]byte, 8)
binary.BigEndian.PutUint64(b, uint64(i))
return b
}
func check(err error) {
if err != nil {
log.Fatal(err)
}
}
func main() {
// create database
db, err := bolt.Open("store.db", 0600, nil)
check(err)
defer db.Close()
// create bucket
err = db.Update(func(tx *bolt.Tx) error {
_, err := tx.CreateBucketIfNotExists([]byte("stocks"))
return err
})
check(err)
transactions := []*StockTrans{
{0, "2006-01-05", "BUY", "RHAT", 100, 35.14, true},
{0, "2006-03-28", "BUY", "IBM", 1000, 45, true},
{0, "2006-04-06", "SELL", "IBM", 500, 53, true},
{0, "2006-04-05", "BUY", "MSOFT", 1000, 72, false},
}
// save transactions to bucket
for _, trans := range transactions {
err := trans.save(db, "stocks")
check(err)
}
// print out contents of bucket
fmt.Println("Id Date Trans Sym Qty Price Settled")
fmt.Println("------------------------------------------------")
db.View(func(tx *bolt.Tx) error {
b := tx.Bucket([]byte("stocks"))
b.ForEach(func(k, v []byte) error {
st := new(StockTrans)
err := json.Unmarshal(v, st)
check(err)
fmt.Printf("%d %s %-4s %-5s %4d %2.2f %t\n",
st.Id, st.Date, st.Trans, st.Symbol, st.Quantity, st.Price, st.Settled)
return nil
})
return nil
})
}
- Output:
Id Date Trans Sym Qty Price Settled ------------------------------------------------ 1 2006-01-05 BUY RHAT 100 35.14 true 2 2006-03-28 BUY IBM 1000 45.00 true 3 2006-04-06 SELL IBM 500 53.00 true 4 2006-04-05 BUY MSOFT 1000 72.00 false
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=: [:; {."1@[ <@i.`(<@i.@#@[)@.(=&(<,'*')@]"1 0) cutopen@]
sortBy=: [ /:"1 2 (<__)|:@,. [ }.@{~ cols
from=: cols~ {"0 _ ]
select=: |:
select '*' from stocks sortBy 'price'
┌──────────┬─────┬──────┬────┬─────┐
│date │trans│symbol│qty │price│
├──────────┼─────┼──────┼────┼─────┤
│2006-01-05│BUY │RHAT │100 │35.14│
├──────────┼─────┼──────┼────┼─────┤
│2006-03-28│BUY │IBM │1000│45 │
├──────────┼─────┼──────┼────┼─────┤
│2006-04-06│SELL │IBM │500 │53 │
├──────────┼─────┼──────┼────┼─────┤
│2006-04-05│BUY │MSOFT │1000│72 │
└──────────┴─────┴──────┴────┴─────┘
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).
Julia
using SQLite
conn = SQLite.DB() # in-memory
SQLite.execute!(conn, """
create table stocks
(date text, trans text, symbol text,
qty real, price real)
""")
# Insert a row of data
SQLite.execute!(conn, """
insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)
""")
for v 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]]
SQLite.query(conn, "insert into stocks values (?,?,?,?,?)", values = v)
end
df = SQLite.query(conn, "select * from stocks order by price")
println(df)
- Output:
4×5 DataFrames.DataFrame │ Row │ date │ trans │ symbol │ qty │ price │ ├─────┼──────────────┼────────┼─────────┼────────┼───────┤ │ 1 │ "2006-01-05" │ "BUY" │ "RHAT" │ 100.0 │ 35.14 │ │ 2 │ "2006-03-28" │ "BUY" │ "IBM" │ 1000.0 │ 45.0 │ │ 3 │ "2006-04-06" │ "SELL" │ "IBM" │ 500.0 │ 53.0 │ │ 4 │ "2006-04-05" │ "BUY" │ "MSOFT" │ 1000.0 │ 72.0 │
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();
- 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 | -----------------------------------------------------
M2000 Interpreter
M2000 Environment use ADO to connect to databases. Default type is Mdb (Acess 2007).
We can use text from UTF16LE set. Here we have Greek letters in Memo.
MODULE SIMPLEBASE {
BASE "ALFA" ' ERASED IF FOUND THE NAME OF "ALFA.MDB"
EXECUTE "ALFA", { CREATE TABLE Employees(ID autoincrement primary key, LastName VARCHAR(40) , FirstName VARCHAR(40) NOT NULL, MyMemo TEXT )}
APPEND "ALFA","Employees",,"George","Getbreak","Γεια χαρά από Ελλάδα"
RETRIEVE "ALFA", "Employees", 1,"",""
READ MANY, ID, LASTNAME$, FIRSTNAMES$, MEMO$
PRINT $(4, 6), "BASE:","ALFA"
Print "ID:",ID
PRINT "NAME:",LASTNAME$ + " " + FIRSTNAMES$
PRINT "MEMO:",
REPORT MEMO$
CLOSE BASE "ALFA"
PRINT
}
SIMPLEBASE
Mathematica /Wolfram Language
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"}]
Nim
Nim standard library provides two modules to work with SQL databases. We use here the high level one for SQLite.
import db_sqlite
let dbc = open(":memory:", "", "", "")
dbc.exec(sql"create table stocks(date text, trans text, symbol text, qty real, price real)")
# Insert a row of data.
dbc.exec(sql"insert into stocks values ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)")
for v 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)]:
dbc.exec(sql"insert into stocks values (?, ?, ?, ?, ?)", v[0], v[1], v[2], v[3], v[4])
# Data retrieval.
for row in dbc.fastRows(sql"select * from stocks order by price"):
echo row
- Output:
@["2006-01-05", "BUY", "RHAT", "100.0", "35.14"] @["2006-03-28", "BUY", "IBM", "1000.0", "45.0"] @["2006-04-06", "SELL", "IBM", "500.0", "53.0"] @["2006-04-05", "BUY", "MSOFT", "1000.0", "72.0"]
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
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];
\\ ...
Perl
# 20211218 Perl programming solution
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");
$dbh->do("CREATE TABLE stocks (
date TEXT, trans TEXT, symbol TEXT, qty REAL, price REAL
)");
my $sth = $dbh->prepare( "INSERT INTO stocks VALUES (?,?,?,?,?)" );
my @DATA = ( '"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' );
for ( @DATA ) { $sth->execute( split /,/ ) or die }
$sth = $dbh->prepare("SELECT * FROM stocks ORDER BY price") or die;
$sth->execute();
my $format = "%-15s %-15s %-15s %-15s %-15s\n";
printf $format, $sth->{NAME}->@* ;
print '=' x 75 , "\n";
while ( my @row = $sth->fetchrow_array ) { printf $format, @row }
- Output:
date trans symbol qty price =========================================================================== "2006-01-05" "BUY" "RHAT" 100 35.14 "2006-03-28" "BUY" "IBM" 1000 45 "2006-04-06" "SELL" "IBM" 500 53 "2006-04-05" "BUY" "MSOFT" 1000 72
Phix
include pSQLite.e constant sqlcode = """ CREATE TABLE IF NOT EXISTS employee ( empID INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT NOT NULL, lastName TEXT NOT NULL, age INTEGER NOT NULL, dob DATE NOT NULL)""" sqlite3 db = sqlite3_open("employees.sqlite") integer res = sqlite3_exec(db,sqlcode) if res=SQLITE_OK then sqlite3_close(db) else printf(1,"sqlite3_exec error: %d [%s]\n",{res,sqlite_last_exec_err}) end if
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
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);
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
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))
Raku
(formerly Perl 6) In Raku, there is no 'database' type built in, so it is somewhat ambiguous when specifying 'create a database table'. Raku 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.
Raku 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 there are no prerequisites to creating instances. Simply assigning values to them will call them into existence.
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.!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'
Ring
# Project : Table creation
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)
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).
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);"
Scala
using SLICK FRM
// Use H2Profile to connect to an H2 database
import slick.jdbc.H2Profile.api._
import scala.concurrent.ExecutionContext.Implicits.global
object Main extends App {
// Definition of the SUPPLIERS table
class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
def name = column[String]("SUP_NAME")
def street = column[String]("STREET")
def city = column[String]("CITY")
def state = column[String]("STATE")
def zip = column[String]("ZIP")
// Every table needs a * projection with the same type as the table's type parameter
def * = (id, name, street, city, state, zip)
}
val suppliers = TableQuery[Suppliers]
// Definition of the COFFEES table
class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
def name = column[String]("COF_NAME", O.PrimaryKey)
def supID = column[Int]("SUP_ID")
def price = column[Double]("PRICE")
def sales = column[Int]("SALES")
def total = column[Int]("TOTAL")
def * = (name, supID, price, sales, total)
// A reified foreign key relation that can be navigated to create a join
def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)
}
val coffees = TableQuery[Coffees]
val setup = DBIO.seq(
// Create the tables, including primary and foreign keys
(suppliers.schema ++ coffees.schema).create
)}
SQL PL
CREATE TABLE dept (
deptno NUMERIC(2)
NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname VARCHAR(14)
NOT NULL CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR(13)
);
CREATE TABLE emp (
empno NUMERIC(4)
NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR(10),
job VARCHAR(9),
mgr NUMERIC(4),
hiredate DATE,
sal DECIMAL(7,2)
CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm DECIMAL(7,2),
deptno NUMERIC(2)
CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
Output:
db2 => CREATE TABLE dept ( db2 (cont.) => deptno NUMERIC(2) db2 (cont.) => NOT NULL CONSTRAINT dept_pk PRIMARY KEY, db2 (cont.) => dname VARCHAR(14) db2 (cont.) => NOT NULL CONSTRAINT dept_dname_uq UNIQUE, db2 (cont.) => loc VARCHAR(13) db2 (cont.) =>); DB20000I The SQL command completed successfully. db2 => CREATE TABLE emp ( db2 (cont.) => empno NUMERIC(4) db2 (cont.) => NOT NULL CONSTRAINT emp_pk PRIMARY KEY, db2 (cont.) => ename VARCHAR(10), db2 (cont.) => job VARCHAR(9), db2 (cont.) => mgr NUMERIC(4), db2 (cont.) => hiredate DATE, db2 (cont.) => sal DECIMAL(7,2) db2 (cont.) => CONSTRAINT emp_sal_ck CHECK (sal > 0), db2 (cont.) => comm DECIMAL(7,2), db2 (cont.) => deptno NUMERIC(2) db2 (cont.) => CONSTRAINT emp_ref_dept_fk db2 (cont.) => REFERENCES dept(deptno) db2 (cont.) =>); DB20000I The SQL command completed successfully. db2 => INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); DB20000I The SQL command completed successfully. db2 => INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS'); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); DB20000I The SQL command completed successfully. db2 => INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10); DB20000I The SQL command completed successfully. db2 => describe table dept; Data type Column Column name schema Data type name Length Scale Nulls ------------------------------- --------- ------------------- ---------- ----- ------ DEPTNO SYSIBM DECIMAL 2 0 No DNAME SYSIBM VARCHAR 14 0 No LOC SYSIBM VARCHAR 13 0 Yes 3 record(s) selected. db2 => describe table emp; Data type Column Column name schema Data type name Length Scale Nulls ------------------------------- --------- ------------------- ---------- ----- ------ EMPNO SYSIBM DECIMAL 4 0 No ENAME SYSIBM VARCHAR 10 0 Yes JOB SYSIBM VARCHAR 9 0 Yes MGR SYSIBM DECIMAL 4 0 Yes HIREDATE SYSIBM DATE 4 0 Yes SAL SYSIBM DECIMAL 7 2 Yes COMM SYSIBM DECIMAL 7 2 Yes DEPTNO SYSIBM DECIMAL 2 0 Yes 8 record(s) selected. db2 => select * from dept; DEPTNO DNAME LOC ------ -------------- ------------- 10. ACCOUNTING NEW YORK 20. RESEARCH DALLAS 2 record(s) selected. db2 => select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ------ ---------- --------- --------- ------ 7369. SMITH CLERK 7902. 12/17/1980 800.00 - 20. 7566. JONES MANAGER 7839. 04/02/1981 2975.00 - 20. 7782. CLARK MANAGER 7839. 06/09/1981 2450.00 - 10. 7788. SCOTT ANALYST 7566. 04/19/1987 3000.00 - 20. 7839. KING PRESIDENT - 11/17/1981 5000.00 - 10. 7876. ADAMS CLERK 7788. 05/23/1987 1100.00 - 20. 7902. FORD ANALYST 7566. 12/03/1981 3000.00 - 20. 7934. MILLER CLERK 7782. 01/23/1982 1300.00 - 10. 8 record(s) selected.
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 | +------------+------------+--------+-------------+---------+
Wren
Version 1
Wren-cli does not currently have access to any external databases so, for the purposes of this task, we devise our own very simple in-memory database format, create a table, add some records to it and display them.
In practice, a binary search would be needed to find records quickly by key given that the records are being maintained in sorted order. However, for now we use a sequential search instead.
import "./dynamic" for Enum, Tuple
import "./fmt" for Fmt
import "./sort" for Cmp, Sort
var FieldType = Enum.create("FieldType", ["text", "num", "int", "bool"])
var Field = Tuple.create("Field", ["name", "fieldType", "maxLen"])
class Table {
construct new(name, fields, keyIndex) {
_name = name
_fields = fields
_keyIndex = keyIndex // the zero based index of the field to sort on
_records = []
_fmt = ""
for (f in _fields) {
var c = f.name.count
var l = f.maxLen.max(c)
if (f.fieldType == FieldType.text ||f.fieldType == FieldType.bool) {
l = -l
}
_fmt = _fmt + "$%(l)s "
}
_fmt = _fmt.trimEnd()
}
name { _name }
showFields() {
System.print("Fields for %(_name) table:\n")
Fmt.print("$-20s $4s $s", "name", "type", "maxlen")
System.print("-" * 33)
for (f in _fields) {
Fmt.print("$-20s $-4s $d", f.name, FieldType.members[f.fieldType], f.maxLen)
}
}
cmp_ { Fn.new { |r1, r2|
return (Num.fromString(r1[_keyIndex]) - Num.fromString(r2[_keyIndex])).sign
}}
addRecord(record) {
var items = record.split(", ")
_records.add(items)
Sort.insertion(_records, cmp_) // move new record into sorted order
}
showRecords() {
System.print("Records for %(_name) table:\n")
var h = Fmt.slwrite(_fmt, _fields.map { |f| f.name }.toList)
System.print(h)
System.print("-" * h.count)
for (r in _records) {
Fmt.lprint(_fmt, r)
}
}
removeRecord(key) {
for (i in 0..._records.count) {
if (_records[i][_keyIndex] == key.toString) {
_records.removeAt(i)
return
}
}
}
findRecord(key) {
for (i in 0..._records.count) {
if (_records[i][_keyIndex] == key.toString) {
return _records[i].join(", ")
}
}
return null
}
}
var fields = []
fields.add(Field.new("id", FieldType.int, 2))
fields.add(Field.new("date", FieldType.text, 10))
fields.add(Field.new("trans", FieldType.text, 4))
fields.add(Field.new("sym", FieldType.text, 5))
fields.add(Field.new("qty", FieldType.int, 4))
fields.add(Field.new("price", FieldType.num, 5))
fields.add(Field.new("settled", FieldType.bool, 5))
// create table
var table = Table.new("Stock_transactions", fields, 0)
// add records in unsorted order
table.addRecord("3, 2006-04-06, SELL, IBM, 500, 53.00, true")
table.addRecord("1, 2006-01-05, BUY, RHAT, 100, 35.14, true")
table.addRecord("4, 2006-04-05, BUY, MSOFT, 1000, 72.00, false")
table.addRecord("2, 2006-03-28, BUY, IBM, 1000, 45.00, true")
// show the table's fields
table.showFields()
System.print()
// show the table's records in sorted order
table.showRecords()
// find a record by key
System.print("\nThe record with an id of 2 is:")
System.print(table.findRecord(2))
// delete a record by key
table.removeRecord(3)
System.print("\nThe record with an id of 3 will be deleted, leaving:\n")
table.showRecords()
- Output:
Fields for Stock_transactions table: name type maxlen --------------------------------- id int 2 date text 10 trans text 4 sym text 5 qty int 4 price num 5 settled bool 5 Records for Stock_transactions table: id date trans sym qty price settled -------------------------------------------------- 1 2006-01-05 BUY RHAT 100 35.14 true 2 2006-03-28 BUY IBM 1000 45.00 true 3 2006-04-06 SELL IBM 500 53.00 true 4 2006-04-05 BUY MSOFT 1000 72.00 false The record with an id of 2 is: 2, 2006-03-28, BUY, IBM, 1000, 45.00, true The record with an id of 3 will be deleted, leaving: Records for Stock_transactions table: id date trans sym qty price settled -------------------------------------------------- 1 2006-01-05 BUY RHAT 100 35.14 true 2 2006-03-28 BUY IBM 1000 45.00 true 4 2006-04-05 BUY MSOFT 1000 72.00 false
Version 2
The above module provides a more generic way to create simple databases and was not available when the first version was written.
import "./table" for FieldInfo, Table, Records
var fields = [
FieldInfo.new("id", Num),
FieldInfo.new("date", String),
FieldInfo.new("trans", String),
FieldInfo.new("sym", String),
FieldInfo.new("qty", Num),
FieldInfo.new("price", Num),
FieldInfo.new("settled", Bool)
]
// create table
var table = Table.new("Stock_transactions", fields)
// add records
table.add([3, "2006-04-06", "SELL", "IBM" , 500, 53.00, true ])
table.add([1, "2006-01-05", "BUY" , "RHAT" , 100, 35.14, true ])
table.add([4, "2006-04-05", "BUY" , "MSOFT", 1000, 72.00, false])
table.add([2, "2006-03-28", "BUY" , "IBM" , 1000, 45.00, true ])
var colWidths = [2, 10, 4, 5, 4, 5.2, 7] // for listings
// show the table's fields
table.listFields()
System.print()
// sort the records by 'id' and show them
var sortFn = Fn.new { |s, t| s[0] < t[0] }
var records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)
// find a record by key
System.print("\nThe record with an id of 2 is:")
System.print(table.find(2))
// delete a record by key
table.remove(3)
System.print("\nThe record with an id of 3 will be deleted, leaving:\n")
records = table.sortedRecords(sortFn)
Records.list(table.fields, records, "Records for %(table.name) table:\n", colWidths)
- Output:
Fields for Stock_transactions table: name kind ------- ------ id Num date String trans String sym String qty Num price Num settled Bool Records for Stock_transactions table: id date tran sym qty price settled -- ---------- ---- ----- ---- ----- ------- 1 2006-01-05 BUY RHAT 100 35.14 true 2 2006-03-28 BUY IBM 1000 45.00 true 3 2006-04-06 SELL IBM 500 53.00 true 4 2006-04-05 BUY MSOFT 1000 72.00 false The record with an id of 2 is: [2, 2006-03-28, BUY, IBM, 1000, 45, true] The record with an id of 3 will be deleted, leaving: Records for Stock_transactions table: id date tran sym qty price settled -- ---------- ---- ----- ---- ----- ------- 1 2006-01-05 BUY RHAT 100 35.14 true 2 2006-03-28 BUY IBM 1000 45.00 true 4 2006-04-05 BUY MSOFT 1000 72.00 false