Skip to content

Latest commit

 

History

History
145 lines (103 loc) · 3.58 KB

setup.md

File metadata and controls

145 lines (103 loc) · 3.58 KB
Raw

Test Setup

This file contains settings needed to setup local testing of odbc. It is most useful for someone trying to develop the R package, so they can run the unit tests locally.

SQL Server test setup

Install the microsoft drivers

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

ini files

First we need to install the drivers and setup the ini files

odbc.ini

[MicrosoftSQLServer]
driver = ODBC Driver 17 for SQL Server
Server = 127.0.0.1
port = 1433

Then we need to start an instance of SQL Server in a docker container.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Password12" \
   -p 1433:1433 --name sql1 \
   -d mcr.microsoft.com/mssql/server:2017-latest

Then do some configuration of the server to add a testuser and create the test database

library(DBI); con <- dbConnect(odbc::odbc(), "SQLServer", UID = 'SA', PWD = 'Password12')

# Add a test user, but currently unused
dbExecute(con, "USE test")
dbExecute(con, "EXEC sp_configure 'contained database authentication', 1")
dbExecute(con, "RECONFIGURE")
dbExecute(con, "alter database test set containment = partial")
dbExecute(con, "CREATE USER testuser with password = 'Password12'")
dbExecute(con, "GRANT CONTROL TO testuser")
dbExecute(con, "DROP USER testuser")

# Create a test database
dbExecute(con, "CREATE DATABASE test")

RODBC

We need to install RODBC for benchmarking in the README. The CRAN version of RODBC uses iODBC, so to use unixODBC we need to recompile it from source, specifying the odbc manager explicitly.

install.packages("RODBC", type = "source", INSTALL_opts="--configure-args='--with-odbc-manager=odbc'")

Linux

Create docker container

docker run -v "$(pwd)":"/opt/$(basename $(pwd))":delegated --security-opt=seccomp:unconfined --link sql1 -it rstudio/r-base:3.6.1-bionic /bin/bash

In docker

curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
#Ubuntu 18.04
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql17
apt-get install -y unixodbc-dev
[MicrosoftSQLServer]
driver = ODBC Driver 17 for SQL Server
Server = sql1
port = 1433
Database = test

Oracle

A huge pain.

Get the DB container

docker login

docker pull store/oracle/database-enterprise:12.2.0.1

Start the container

The -P is important to setup the port forwarding from the docker container

docker run -d -it --name oracle_db -P store/oracle/database-enterprise:12.2.0.1

Query the port and edit the ports in tnsnames.ora

docker port oracle_db

Contents of snsnames.ora

ORCLCDB=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=32769))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLCDB.localdomain)))
ORCLPDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=32769))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)))

Set the current working directory as the

Add a new user to the DB

docker exec -it oracle_db bash -c "source /home/oracle/.bashrc; sqlplus SYS/Oradoc_db1 AS SYSDBA"

alter session set "_ORACLE_SCRIPT"=true;

create user test identified by 12345;

GRANT ALL PRIVILEGES TO TEST;
Sys.setenv("TNS_ADMIN" = getwd())
con <- dbConnect(odbc::odbc(), "OracleODBC-19")