Skip to content

Instantly share code, notes, and snippets.

@nkottary

nkottary/test.jl

Created Feb 22, 2016
Embed
What would you like to do?
ODBC jq/remodel branch testing for various database backends
using ODBC
using DataFrames
@linux_only const TEMP_FILE = "/tmp/tmp.csv"
@windows_only const TEMP_FILE = "tmp.csv"
const MYSQL_DSN = "UbuntuMySQL"
const MSSQL_DSN = "MSSQL_Server"
function test_mysql()
hndl = ODBC.DSN(MYSQL_DSN)
ODBC.Source(hndl, "DROP DATABASE IF EXISTS remodeltest;")
ODBC.Source(hndl, "CREATE DATABASE remodeltest;")
ODBC.Source(hndl, """
CREATE TABLE remodeltest.Employee
(
ID INT NOT NULL AUTO_INCREMENT,
Name VARCHAR(255),
Salary FLOAT(7,2),
JoinDate DATE,
LastLogin DATETIME,
LunchTime TIME,
OfficeNo TINYINT,
JobType ENUM('HR', 'Management', 'Accounts'),
Senior BIT(1),
empno SMALLINT,
PRIMARY KEY (ID)
);""")
ODBC.Source(hndl, """
INSERT INTO remodeltest.Employee (Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, JobType, Senior, empno) VALUES ('John', 10000.50, '2015-8-3', '2015-9-5 12:31:30', '12:00:00', 1, 'HR', b'1', 1301), ('Tom', 20000.25, '2015-8-4', '2015-10-12 13:12:14', '13:00:00', 12, 'HR', b'1', 1422), ('Jim', 30000.00, '2015-6-2', '2015-9-5 10:05:10', '12:30:00', 45, 'Management', b'0', 1567), ('Tim', 15000.50, '2015-7-25', '2015-10-10 12:12:25', '12:30:00', 56, 'Accounts', b'1', 3200);
""")
# ODBC.Source(hndl, "INSERT INTO remodeltest.Employee () VALUES ();")
src = ODBC.Source(hndl, "select * from remodeltest.Employee;")
sink = CSV.Sink(TEMP_FILE)
Data.stream!(src, sink)
df = readtable(TEMP_FILE)
end
function test_postgre()
hndl = ODBC.DSN("postgres", "postgres", "postgres")
ODBC.Source(hndl, "DROP TABLE IF EXISTS Employee")
ODBC.Source(hndl, """
CREATE TABLE Employee
(
ID SERIAL PRIMARY KEY,
Name VARCHAR(255),
Salary NUMERIC(7, 2),
JoinDate DATE,
LastLogin TIMESTAMP,
LunchTime TIME,
OfficeNo SMALLINT,
Senior BIT(1),
empno SMALLINT
)""")
ODBC.Source(hndl, """
INSERT INTO Employee (Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, Senior, empno) VALUES ('John', 10000.50, '2015-8-3', '2015-9-5 12:31:30', '12:00:00', 1, b'1', 1301), ('Tom', 20000.25, '2015-8-4', '2015-10-12 13:12:14', '13:00:00', 12, b'1', 1422), ('Jim', 30000.00, '2015-6-2', '2015-9-5 10:05:10', '12:30:00', 45, b'0', 1567), ('Tim', 15000.50, '2015-7-25', '2015-10-10 12:12:25', '12:30:00', 56, b'1', 3200);
""")
# ODBC.Source(hndl, "INSERT INTO Employee () VALUES ();")
src = ODBC.Source(hndl, "select * from Employee;")
sink = CSV.Sink(TEMP_FILE)
Data.stream!(src, sink)
df = readtable(TEMP_FILE)
end
function test_oracle()
hndl = ODBC.DSN("oracle_dsn")
ODBC.Source(hndl, "DROP TABLE Employee")
ODBC.Source(hndl, """
CREATE TABLE Employee
(
Name VARCHAR(255),
Salary NUMERIC(7, 2),
JoinDate DATE,
LastLogin TIMESTAMP,
LunchTime TIMESTAMP,
OfficeNo SMALLINT,
empno NUMBER(38)
)""")
ODBC.Source(hndl, """
INSERT INTO Employee (Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, empno) VALUES ('John', 10000.50, TO_DATE('2015-08-03', 'YYYY-mm-dd'), TO_DATE('2015-09-05 12:31:30', 'YYYY-mm-dd HH24:MI:SS'), TO_DATE('12:00:00', 'HH24:MI:SS'), 1, 1301)""")
ODBC.Source(hndl, """
INSERT INTO Employee (Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, empno) VALUES ('Tom', 20000.25, TO_DATE('2015-08-04', 'YYYY-mm-dd'), TO_DATE('2015-10-12 13:12:14', 'YYYY-mm-dd HH24:MI:SS'), TO_DATE('13:00:00', 'HH24:MI:SS'), 12, 1422)""")
ODBC.Source(hndl, """
INSERT INTO Employee (Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, empno) VALUES ('Jim', 30000.00, TO_DATE('2015-6-2', 'YYYY-mm-dd'), TO_DATE('2015-9-5 10:05:10', 'YYYY-mm-dd HH24:MI:SS'), TO_DATE('12:30:00', 'HH24:MI:SS'), 45, 1567)""")
ODBC.Source(hndl, """
INSERT INTO Employee (Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, empno) VALUES ('Tim', 15000.50, TO_DATE('2015-07-25', 'YYYY-mm-dd'), TO_DATE('2015-10-10 12:12:25', 'YYYY-mm-dd HH24:MI:SS'), TO_DATE('12:30:00', 'HH24:MI:SS'), 56, 3200)""")
# ODBC.Source(hndl, "INSERT INTO Employee () VALUES ();")
src = ODBC.Source(hndl, "select * from Employee;")
sink = CSV.Sink(TEMP_FILE)
Data.stream!(src, sink)
df = readtable(TEMP_FILE)
end
function test_mssql()
hndl = ODBC.DSN(MSSQL_DSN, "sa", "password")
ODBC.Source(hndl, "USE master")
ODBC.Source(hndl, """
IF EXISTS(SELECT * FROM sys.databases WHERE name='remodeltest')
DROP DATABASE remodeltest""")
ODBC.Source(hndl, "CREATE DATABASE remodeltest")
ODBC.Source(hndl, "USE remodeltest")
ODBC.Source(hndl, """
CREATE TABLE Employee
(
ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(255),
Salary FLOAT,
JoinDate DATE,
LastLogin DATETIME,
LunchTime TIME,
OfficeNo TINYINT,
Senior BIT,
empno SMALLINT
);""")
ODBC.Source(hndl, """
INSERT INTO Employee
(Name, Salary, JoinDate, LastLogin, LunchTime, OfficeNo, Senior, empno)
VALUES ('John', 10000.50, '2015-8-3', '2015-9-5 12:31:30', '12:00:00', 1, 1, 1301),
('Tom', 20000.25, '2015-8-4', '2015-10-12 13:12:14', '13:00:00', 12, 1, 1422),
('Jim', 30000.00, '2015-6-2', '2015-9-5 10:05:10', '12:30:00', 45, 0, 1567),
('Tim', 15000.50, '2015-7-25', '2015-10-10 12:12:25', '12:30:00', 56, 1, 3200);
""")
# ODBC.Source(hndl, "INSERT INTO remodeltest.Employee () VALUES ();")
src = ODBC.Source(hndl, "select * from Employee;")
sink = CSV.Sink(TEMP_FILE)
Data.stream!(src, sink)
df = readtable(TEMP_FILE)
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.