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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
|
<html>
<head>
<title>firstworks Using the SQL Relay drop-in replacement library for PostgreSQL</title>
<link href="../css/styles.css" rel="stylesheet">
</head>
<body>
<span class="heading1">Using the SQL Relay drop-in replacement library for PostgreSQL</span><br><br>
<ul>
<li><a href="#whatis">What is a drop-in replacement library?</a></li>
<li><a href="#commandline">Using the drop-in replacement library with command-line programs</a></li>
<li><a href="#daemons">Using the drop-in replacement library with daemons</a></li>
<li><a href="#inetd">Using the drop-in replacement library with inetd/xinetd helper programs</a></li>
<li><a href="#modules">Using the drop-in replacement library with modules</a></li>
<li><a href="#work">Function support</a></li>
</ul>
<br>
<a name="whatis"></a>
<span class="heading2">What is a drop-in replacement library?</span><br>
<p>The SQL Relay drop-in replacement library for PostgreSQL clients allows you
to run an application that was written using the native PostgreSQL client API
against SQL Relay without rewriting the application.</p>
<p>The drop-in replacement library is a shared object library that implements
PostgreSQL client library functions as calls to similar SQL Relay API functions
which then map the results back into native PostgreSQL client data
structures.</p>
<p>There are a variety of reasons that you might want to use the SQL Relay
drop-in replacement library for PostgreSQL clients. An application written for
PostgreSQL could, for instance, be made to run queries against an Oracle or
Microsoft SQL Server database without modification. Or you could simply put
SQL Relay between an application and the PostgreSQL database that it normally
runs against to take advantage of SQL Relay's persistence, load balancing or
throttling facilities.</p>
<br>
<a name="commandline"></a>
<span class="heading2">Using the drop-in replacement library with command-line programs</span><br>
<p>You can use the SQL Relay drop-in replacement library for PostgreSQL clients
by loading the drop-in library and running your program.</p>
<p>The parameters that would ordinarily indicate which host, port, socket,
username and password to use to connect to PostgreSQL will be used as
parameters to connect to SQL Relay. The parameter that would ordinarily
indicate which database to connect to will be ignored. Instances of SQL Relay
are configured to connect to a single database, and that database will be used
by the client program.</p>
<p>In the following example, we're running the "psql" program against an
instance of SQL Relay running on the localhost, port 8009 against an Oracle
database. This instance of SQL Relay is configured with a username/password of
oracle8user/oracle8pass.</p>
<p>For sh-based shells:</p>
<blockquote><pre><b>
LD_PRELOAD=/usr/local/firstworks/lib/libpqsqlrelay.so
export LD_PRELOAD
psql -h localhost -p 8009 -U oracle8user -W
Password: oracle8pass
</b></pre></blockquote>
<p>For csh-based shells:</p>
<blockquote><pre><b>
setenv LD_PRELOAD /usr/local/firstworks/lib/libpqsqlrelay.so
psql -h localhost -p 8009 -U oracle8user -W
Password: oracle8pass
</b></pre></blockquote>
<p>The LD_PRELOAD environment variable instructs the dynamic loader to load
libpqsqlrelay.so before loading any other libraries for any programs.
The psql client program will still load the native PostgreSQL client library,
but since it loaded the SQL Relay drop-in replacement library first, function
calls that would normally be fulfilled by the native PostgreSQL client library
are fulfilled by the SQL Relay drop-in replacement library instead.</p>
<p>Below is a sample session using the psql command line client against an
Oracle database through SQL Relay.</p>
<blockquote>
<pre>
ORA<font color="#ff00ff">-06550</font>: line <font color="#ff00ff">1</font>, <font color="#6b59ce">column</font> <font color="#ff00ff">6</font>:
PLS<font color="#ff00ff">-00103</font>: Encountered the symbol <font color="#ff00ff">";"</font> when expecting one <font color="#6b59ce">of</font> the following:
<font color="#6b59ce">begin</font> case declare exit <font color="#6b59ce">for</font> goto <font color="#6b59ce">if</font> <font color="#6b59ce">loop</font> mod <font color="#6b59ce">null</font> pragma
raise <font color="#6b59ce">return</font> <font color="#a52829"><b>select</b></font> <font color="#a52829"><b>update</b></font> while <font color="#6b59ce">with</font> <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close <font color="#6b59ce">current</font> <font color="#a52829"><b>delete</b></font> fetch <font color="#a52829"><b>lock</b></font> <font color="#a52829"><b>insert</b></font> open <font color="#a52829"><b>rollback</b></font>
<font color="#a52829"><b>savepoint</b></font> <font color="#a52829"><b>set</b></font> sql <font color="#a52829"><b>execute</b></font> <font color="#a52829"><b>commit</b></font> forall merge
<a single-quoted SQL string> pipe
The symbol <font color="#ff00ff">"exit"</font> was substituted <font color="#6b59ce">for</font> <font color="#ff00ff">";"</font> <font color="#6b59ce">to</font> continue.
ORA<font color="#ff00ff">-06550</font>: line <font color="#ff00ff">1</font>, <font color="#6b59ce">column</font> <font color="#ff00ff">84</font>:
PLS<font color="#ff00ff">-00103</font>: Encountered the symbol <font color="#ff00ff">"end-of-file"</font> when expecting one <font color="#6b59ce">of</font> the following:
; <font color="#a52829"><b>comment</b></font> work <a SQL statement>
Attempted Query:
<font color="#6b59ce">BEGIN</font>; <font color="#a52829"><b>SELECT</b></font> usesuper <font color="#6b59ce">FROM</font> pg_catalog.pg_user <font color="#6b59ce">WHERE</font> usename = <font color="#ff00ff">'oracle8test'</font>; <font color="#a52829"><b>COMMIT</b></font>
Welcome <font color="#6b59ce">to</font> psql <font color="#ff00ff">7.3</font>.<font color="#ff00ff">4</font>-RH, the PostgreSQL interactive terminal.
<font color="#6b59ce">Type</font>: \copyright <font color="#6b59ce">for</font> distribution terms
\h <font color="#6b59ce">for</font> help <font color="#6b59ce">with</font> SQL commands
\? <font color="#6b59ce">for</font> help <font color="#6b59ce">on</font> internal slash commands
\g <font color="#a52829"><b>or</b></font> terminate <font color="#6b59ce">with</font> semicolon <font color="#6b59ce">to</font> <font color="#a52829"><b>execute</b></font> query
\q <font color="#6b59ce">to</font> quit
=> <font color="#a52829"><b>create</b></font> <font color="#6b59ce">table</font> testtable (col1 <font color="#298a52"><b>varchar2</b></font>(<font color="#ff00ff">60</font>), col2 <font color="#298a52"><b>number</b></font>(<font color="#ff00ff">5</font>,<font color="#ff00ff">2</font>));
=> <font color="#a52829"><b>insert</b></font> <font color="#6b59ce">into</font> testtable <font color="#6b59ce">values</font> (<font color="#ff00ff">'hello'</font>,<font color="#ff00ff">123</font>.<font color="#ff00ff">45</font>);
=> <font color="#a52829"><b>select</b></font> * <font color="#6b59ce">from</font> testtable;
COL1 | COL2
<font color="#0000ff">-------+--------</font>
hello | <font color="#ff00ff">123</font>.<font color="#ff00ff">45</font>
(<font color="#ff00ff">1</font> <font color="#6b59ce">row</font>)
=> \q
</pre>
</blockquote>
<br>
<a name="daemons"></a>
<span class="heading2">Using the drop-in replacement library with daemons</span><br>
<p>Using the SQL Relay drop-in replacement library for PostgreSQL with daemons
is simlar to using it on the command line. You just need to add the LD_PRELOAD
command to the startup script for the daemon before the command that starts the
daemon itself.</p>
<br>
<a name="inetd"></a>
<span class="heading2">Using the drop-in replacement library with inetd/xinetd helper programs</span><br>
<p>Inetd and xinetd are daemons that listen on ports and run helper programs to
service requests on those ports. The helper programs die off after the request
is serviced.</p>
<p>The easist way to get an inetd helper program to use the SQL Relay drop-in
replacement library for PostgreSQL is to add the LD_PRELOAD command to the
startup script for inetd/xinetd. Any command that inetd/xinetd runs will also
preload the library.</p>
<p>However, if some of the helper programs need to actually run against
PostgreSQL and not against SQL Relay, then you will have to do something
different. The easiest thing to do is create a script for each helper program
that needs to run against SQL Relay that runs the LD_PRELOAD command and then
runs the actual helper program, passing it all the necessary command line
arguments.</p>
<p>For example, lets say you have a pop3 server called pop3d that uses
PostgreSQL for user identification and you wanted to use SQL Relay instead of
PostgreSQL. The inetd.conf entry might look like this:</p>
<blockquote><pre>
pop3 stream tcp nowait root /usr/local/bin/pop3d
</pre></blockquote>
<p>An /etc/xinetd.d entry might look like this:
<blockquote><pre>
service pop3
{
socket_type = stream
wait = no
user = root
server = /usr/local/bin/pop3d
}
</pre></blockquote>
<p>You could write the a script called /usr/local/bin/pop3d-sqlrelay as
follows:</p>
<blockquote>
<pre>
<font color="#0000ff">#!/bin/sh</font>
<font color="#008a8c">LD_PRELOAD</font>=/usr/local/firstworks/lib/libpqsqlrelay.so
<font color="#a52829"><b>export </b></font><font color="#008a8c">LD_PRELOAD</font>
/usr/local/bin/pop3d <font color="#a520f7">$@</font>
</pre>
</blockquote>
<p>And modify the entries to call the script instead of pop3d as follows:</p>
<blockquote><pre>
pop3 stream tcp nowait root /usr/local/bin/pop3d-sqlrelay
</pre></blockquote>
<p>Or for xinetd:</p>
<blockquote><pre>
service pop3
{
socket_type = stream
wait = no
user = root
server = /usr/local/bin/pop3d-sqlrelay
}
</pre></blockquote>
<br>
<a name="modules"></a>
<span class="heading2">Using the drop-in replacement library with modules</span><br>
<p>You may want to use the SQL Relay drop-in replacement library for PostgreSQL
clients with a program that isn't compiled against the native PostgreSQL client
library but rather loads it as a module such as a program that uses ODBC or
Perl DBI, or an Apache/PHP application.</p>
<p>Using the SQL Relay drop-in replacement library with programs that load
the native PostgreSQL client library as a module is simlar to using it on the
command line. You just need to make sure that the LD_PRELOAD command is run
before the program starts.</p>
<p>If the program is a command line program, then run the LD_PRELOAD command
before running your program. Even though the program ultimately loads the
native PostgreSQL client library, all of it's functions will be overriden by the
SQL Relay drop-in replacement library.</p>
<p>If the program is a daemon then add the LD_PRELOAD command to the startup
script for the daemon.</p>
<p>If the program runs in the address space of a daemon, such as a PHP
application running under Apache's mod_php, then add the LD_PRELOAD command to
the startup script for the daemon. The caveat here is that all applications
running in the address space of the daemon will use the drop-in replacement
library instead of the native PostgreSQL library. It is not possible, for
example for a web server to run one PHP application directly against PostgreSQL
and another PHP application against SQL Relay using the drop-in replacement
library; if the drop-in replacement library is loaded, both applications will
end up using it.</p>
<p>If the program is spawned by a daemon, such as a cgi spawned by a web-server
or an inetd/xinetd helper program, then you can either add the LD_PRELOAD
command to the daemon's startup script or write a script to run the LD_PRELOAD
command and pass along the command line arguments (see the section
<a href="#inetd">Using the drop-in replacement library with inetd/xinetd helper
programs above)</a>).</p>
<br>
<a name="work"></a>
<span class="heading2">Function support</span><br>
<p>The SQL Relay drop-in replacement library for PostgreSQL implements most of
the native PostgreSQL client library's functions, but there are a few functions
that aren't implemented because SQL Relay doesn't have a good way to support
them. These functions return safe values or a failure condition.</p>
<p>Here is a list of functions that are implemented and functions that are not.
If your application uses one of the functions that is not implemented, you may
or may not be able to use it with the SQL Relay drop-in replacement library for
PostgreSQL.</p>
<table border="1">
<tr><th>Function</th><th>Implemented?</th></tr>
<tr><td>PQsetdbLogin</td><td>yes, but ignores the db, options and tty parameters</td></tr>
<tr><td>PQsetdb</td><td>yes</td></tr>
<tr><td>PQconnectdb</td><td>yes</td></tr>
<tr><td>PQfinish</td><td>yes</td></tr>
<tr><td>PQreset</td><td>yes</td></tr>
<tr><td>PQdb</td><td>yes</td></tr>
<tr><td>PQuser</td><td>yes</td></tr>
<tr><td>PQpass</td><td>yes</td></tr>
<tr><td>PQhost</td><td>yes</td></tr>
<tr><td>PQport</td><td>yes</td></tr>
<tr><td>PQtty</td><td>yes</td></tr>
<tr><td>PQoptions</td><td>yes</td></tr>
<tr><td>PQstatus</td><td>yes</td></tr>
<tr><td>PQerrorMessage</td><td>yes</td></tr>
<tr><td>PQsocket</td><td>no, always returns -1</td></tr>
<tr><td>PQbackendPID</td><td>no, always returns -1</td></tr>
<tr><td>PQgetssl</td><td>no, always returns 0</td></tr>
<tr><td>PQclientEncoding</td><td>yes</td></tr>
<tr><td>PQsetClientEncoding</td><td>yes</td></tr>
<tr><td>PQsetNoticeProcessor</td><td>yes</td></tr>
<tr><td>PQescapeString</td><td>yes</td></tr>
<tr><td>PQescapeBytea</td><td>yes</td></tr>
<tr><td>PQunescapeBytea</td><td>yes</td></tr>
<tr><td>PQclear</td><td>yes</td></tr>
<tr><td>PQexec</td><td>yes</td></tr>
<tr><td>PQresultStatus</td><td>yes</td></tr>
<tr><td>PQresStatus</td><td>yes</td></tr>
<tr><td>PQresultErrorMessage</td><td>yes, returns the SQL Relay error string</td></tr>
<tr><td>PQntuples</td><td>yes</td></tr>
<tr><td>PQnfields</td><td>yes</td></tr>
<tr><td>PQbinaryTuples</td><td>yes</td></tr>
<tr><td>PQfname</td><td>yes</td></tr>
<tr><td>PQfnumber</td><td>yes</td></tr>
<tr><td>PQftype</td><td>yes</td></tr>
<tr><td>PQfsize</td><td>yes</td></tr>
<tr><td>PQfmod</td><td>yes</td></tr>
<tr><td>PQcmdStatus</td><td>yes</td></tr>
<tr><td>PQoidStatus</td><td>yes</td></tr>
<tr><td>PQoidValue</td><td>yes</td></tr>
<tr><td>PQcmdTuples</td><td>yes</td></tr>
<tr><td>PQgetvalue</td><td>yes</td></tr>
<tr><td>PQgetlength</td><td>yes</td></tr>
<tr><td>PQgetisnull</td><td>yes</td></tr>
<tr><td>PQmakeEmptyPGresult</td><td>yes</td></tr>
<tr><td>PQprint</td><td>yes</td></tr>
<tr><td>PQdisplayTuples</td><td>yes</td></tr>
<tr><td>PQprintTuples</td><td>yes</td></tr>
<tr><td>PQconndefaults</td><td>always returns NULL</td></tr>
<tr><td>PQconninfoFree</td><td>yes</td></tr>
<tr><td>PQtrace</td><td>sort of, just calls sqlrconnection::debugOn()</td></tr>
<tr><td>PQuntrace</td><td>sort of, just calls sqlrconnection::debugOff()</td></tr>
<tr><td>PQgetline</td><td>no, NULL terminates string and returns EOF</td></tr>
<tr><td>PQputline</td><td>no, always returns EOF</td></tr>
<tr><td>PQgetlineAsync</td><td>no, NULL terminates buffer and returns EOF</td></tr>
<tr><td>PQputnbytes</td><td>no, always returns EOF</td></tr>
<tr><td>PQendcopy</td><td>no, always returns -1 for failure</td></tr>
<tr><td>PQmblen</td><td>only for UTF8, always returns 1</td></tr>
<tr><td>PQenv2encoding</td><td>only for UTF8</td></tr>
<tr><td>PQfn</td><td>no, always returns NULL</td></tr>
<tr><td>lo_open</td><td>no, always returns -1</td></tr>
<tr><td>lo_close</td><td>no, always returns -1</td></tr>
<tr><td>lo_read</td><td>no, always returns -1</td></tr>
<tr><td>lo_write</td><td>no, always returns -1</td></tr>
<tr><td>lo_lseek</td><td>no, always returns -1</td></tr>
<tr><td>lo_creat</td><td>no, always returns -1</td></tr>
<tr><td>lo_tell</td><td>no, always returns -1</td></tr>
<tr><td>lo_unlink</td><td>no, always returns -1</td></tr>
<tr><td>lo_import</td><td>no, always returns -1</td></tr>
<tr><td>lo_export</td><td>no, always returns -1</td></tr>
<tr><td>PQnotifies</td><td>no, always returns NULL</td></tr>
<tr><td>PQfreeNotify</td><td>no, does nothing</td></tr>
<tr><td colspan="2">
The following functions implement the PostgreSQL asynchronous query API.
SQL Relay doesn't have an asynchronous query API so they are implemented
as calls to synchronous query functions. They work, but in a synchronous
manner.
</td></tr>
<tr><td>PQconnectStart</td><td>yes</td></tr>
<tr><td>PQconnectPoll</td><td>yes</td></tr>
<tr><td>PQresetStart</td><td>yes</td></tr>
<tr><td>PQresetPoll</td><td>yes</td></tr>
<tr><td>PQrequestCancel</td><td>yes</td></tr>
<tr><td>PQsendQuery</td><td>yes</td></tr>
<tr><td>PQgetResult</td><td>yes</td></tr>
<tr><td>PQisBusy</td><td>yes</td></tr>
<tr><td>PQconsumeInput</td><td>yes</td></tr>
<tr><td>PQsetnonblocking</td><td>yes</td></tr>
<tr><td>PQisnonblocking</td><td>yes</td></tr>
<tr><td>PQflush</td><td>yes</td></tr>
<tr><td>PQsendSome</td><td>yes</td></tr>
</table>
</body>
</html>
|
