Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
198 lines (172 sloc) 5.55 KB
Rebol [
Title: "ODBC Test Script for Firebird 3.0"
Description: {
This script does some basic table creation, assuming you have
configured an ODBC connection with the DSN "Rebol" that has a "test"
database inside it. Then it queries to make sure it can get the
data back out.
}
version: 0.0.4
date: 2-June-2017
notes: {add a format-sql function, avoid using reserved names for tables, don't drop tables so we can examine in another DB browser}
]
system/options/dump-size: 1000
failures: copy []
success: copy []
tables: [
bit "BOOLEAN" [#[TRUE] #[FALSE] #[TRUE]]
; tinyint_s "SMALLINT" [-128 -10 0 10 127]
; tinyint_u "SMALLINT UNSIGNED" [0 10 20 30 255]
smallint_s "SMALLINT" [-32768 -10 0 10 32767]
; smallint_u "SMALLINT UNSIGNED" [0 10 20 30 65535]
integer_s "INT" [-2147483648 -10 0 10 2147483647]
; integer_u "INT UNSIGNED" [0 10 20 30 4294967295]
bigint_s "BIGINT" [-9223372036854775808 -10 0 10 9223372036854775807]
;
; Note: though BIGINT unsigned storage in ODBC can store the full range of
; unsigned 64-bit values, Rebol's INTEGER! is always signed. Hence it
; is limited to the signed range.
;
; bigint_u "BIGINT UNSIGNED" [0 10 20 30 9223372036854775807]
; real "DOUBLE" [-3.4 -1.2 0.0 5.6 7.8]
double "DOUBLE PRECISION" [-3.4 -1.2 0.0 5.6 7.8]
float "FLOAT(20)" [-3.4 -1.2 0.0 5.6 7.8]
numeric "NUMERIC(18,2)" [-3.4 -1.2 0.0 5.6 7.8]
decimal "DECIMAL(3,2)" [-3.4 -1.2 0.0 5.6 7.8]
date "TIMESTAMP" [12-Dec-2012 21-Apr-1975]
time "TIME" [10:00 11:01:12 12:13:14.1 12:13:14.12 03:04:00.123]
timestamp "TIMESTAMP" [30-May-2017/14:23:08 12-Dec-2012]
char "CHAR(3)" [{abc} {def} {ghi}]
varchar "VARCHAR(10)" ["" "abc" "defgh" "jklmnopqrs"]
nchar "NCHAR(3)" ["abc" "ταБ" "ghi"]
nvarchar "VARCHAR(10)" ["" "abc" "ταБЬℓσ" "٩(●̮̮̃•̃)۶"]
binary "CHAR(3)" [#{000000} #{010203} #{FFFFFF}]
varbinary "CHAR(10)" [#{} #{010203} #{DECAFBADCAFE}]
blob "BLOB(10)" [#{} #{010203} #{DECAFBADCAFE}]
]
connection: open odbc://Rebol
statement: first connection
debug: func [x][print/eval x x]
format-sql: function [
{replace all ? in sql-data/1 [string!] with values from sql-data/2 [block!]}
sql-data [block!]
][
sql-string: copy sql-data/1
values: sql-data/2
for-each value values [
case [
number? :value [
replacement: value
]
word? :value [
replacement: value
]
any [time? :value string? :value ][
replacement: ajoin [{'} value {'}]
]
date? :value [
; convert to GMT
attempt [value: value - value/zone | value/zone: 0:00]
replace value: form value "/" space
replacement: ajoin [{'} value {'}]
]
binary? :value [
replacement: form value
replace replacement "{" {'}
replace replacement "}" {'}
replace replacement "#" {x}
]
logic? :value [
replacement: either :value ["TRUE"]["FALSE"]
]
true [
print/eval ["oops, missed one " type-of value]
fail "switch does not match type-of"
]
]
replace sql-string "?" replacement
]
sql-string
]
clear failures
clear success
for-each [name sqltype content] tables [
;
; Drop table if it exists
; comment {
trap [
print ["dropping table" uppercase join-of "REB" form name]
insert statement
debug unspaced [
{DROP TABLE "} uppercase join-of "REB" form name {"}
]
]
;}
; Create table, each one of which has a single field "value" as the
; primary key, of the named type.
; id integer generated by default as identity primary key
;
dump [name sqltype content]
insert statement
debug unspaced [
{CREATE TABLE "}
join-of "REB" uppercase form name
{" (}
{ID integer generated by default as identity primary key, }
{"SQLVALUE"}
space sqltype space
{NOT NULL}
{)}
]
; insert statement {commit;}
; Insert the values. As a side effect, here we wind up testing the
; parameter code for each type.
;
for-each value content [
; insert statement
s: format-sql
reduce compose/deep [
unspaced [
{INSERT INTO "}
join-of "REB" uppercase form name
{" ("SQLVALUE") VALUES (?)}
] [(value)]
]
dump s
insert statement s
]
; insert statement "commit;"
; Query the rows and make sure the values that come back are the same
;
insert statement
debug unspaced [
{SELECT "SQLVALUE" FROM "} join-of "REB" uppercase form name {"}
]
rows: copy statement
actual: copy []
for-each row rows [
; assert [1 = length-of row]
append actual first row
]
print mold actual
print mold content
print either (sort copy actual) = (sort copy content) [
append success sqltype
"QUERY MATCHED ORIGINAL DATA"
][
append failures sqltype
"QUERY DID NOT MATCH ORIGINAL DATA"
]
print-newline
]
insert statement "commit"
close statement
close connection
unless empty? failures [
print "Failed on: "
dump failures
]
unless empty? success [
print "Success on:"
dump success
]