1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
R version 3.3.1 (2016-06-21) -- "Bug in Your Hair"
Copyright (C) 2016 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
>
> ## dbWriteTable test
> ##
> ## Assumes that
> ## a) PostgreSQL is running, and
> ## b) the current user can connect
> ## both of which are not viable for release but suitable while we test
> ##
> ## Dirk Eddelbuettel, 10 Sep 2009
>
> ## only run this if this env.var is set correctly
> if (Sys.getenv("POSTGRES_USER") != "" & Sys.getenv("POSTGRES_HOST") != "" & Sys.getenv("POSTGRES_DATABASE") != "") {
+
+ ## try to load our module and abort if this fails
+ stopifnot(require(RPostgreSQL))
+ stopifnot(require(datasets))
+
+ ## load the PostgresSQL driver
+ drv <- dbDriver("PostgreSQL")
+
+ ## create two independent connections
+ con1 <- dbConnect(drv,
+ user=Sys.getenv("POSTGRES_USER"),
+ password=Sys.getenv("POSTGRES_PASSWD"),
+ host=Sys.getenv("POSTGRES_HOST"),
+ dbname=Sys.getenv("POSTGRES_DATABASE"),
+ port=ifelse((p<-Sys.getenv("POSTGRES_PORT"))!="", p, 5432))
+
+ con2 <- dbConnect(drv,
+ user=Sys.getenv("POSTGRES_USER"),
+ password=Sys.getenv("POSTGRES_PASSWD"),
+ host=Sys.getenv("POSTGRES_HOST"),
+ dbname=Sys.getenv("POSTGRES_DATABASE"),
+ port=ifelse((p<-Sys.getenv("POSTGRES_PORT"))!="", p, 5432))
+
+
+ if (dbExistsTable(con1, "rockdata")) {
+ cat("Removing rockdata\n")
+ dbRemoveTable(con1, "rockdata")
+ }
+
+ cat("begin transaction in con1\n")
+ dbGetQuery(con1, "BEGIN TRANSACTION")
+ cat("create table rockdata in con1\n")
+ dbWriteTable(con1, "rockdata", rock)
+ if (dbExistsTable(con1, "rockdata")) {
+ cat("PASS rockdata is visible through con1\n")
+ }else{
+ cat("FAIL rockdata is invisible through con1\n")
+ }
+ if (dbExistsTable(con2, "rockdata")) {
+ cat("FAIL rockdata is visible through con2\n")
+ }else{
+ cat("PASS rockdata is invisible through con2\n")
+ }
+ cat("commit in con1\n")
+ dbCommit(con1)
+ if (dbExistsTable(con2, "rockdata")) {
+ cat("PASS rockdata is visible through con2\n")
+ }else{
+ cat("FAIL rockdata is invisible through con2\n")
+ }
+
+ cat("remove the table from con1\n")
+ dbRemoveTable(con1, "rockdata")
+
+ if (dbExistsTable(con2, "rockdata")) {
+ cat("FAIL rockdata is visible through con2\n")
+ }else{
+ cat("PASS rockdata is invisible through con2\n")
+ }
+
+ cat("begin transaction in con1\n")
+ dbGetQuery(con1, "BEGIN TRANSACTION")
+ cat("create table rockdata in con1\n")
+ dbWriteTable(con1, "rockdata", rock)
+ if (dbExistsTable(con1, "rockdata")) {
+ cat("PASS rockdata is visible through con1\n")
+ }else{
+ cat("FAIL rockdata is invisible through con1\n")
+ }
+ cat("RollBack con1\n")
+ dbRollback(con1)
+ if (dbExistsTable(con1, "rockdata")) {
+ cat("FAIL rockdata is visible through con1\n")
+ }else{
+ cat("PASS rockdata is invisible through con1\n")
+ }
+
+ ## and disconnect
+ dbDisconnect(con2)
+ dbDisconnect(con1)
+ }
Loading required package: RPostgreSQL
Loading required package: DBI
begin transaction in con1
create table rockdata in con1
PASS rockdata is visible through con1
PASS rockdata is invisible through con2
commit in con1
PASS rockdata is visible through con2
remove the table from con1
PASS rockdata is invisible through con2
begin transaction in con1
create table rockdata in con1
PASS rockdata is visible through con1
RollBack con1
PASS rockdata is invisible through con1
[1] TRUE
>
> proc.time()
user system elapsed
0.539 0.039 0.715
|
