Commit | Line | Data |
---|---|---|
97df14cd JM |
1 | BEGIN |
2 | EXECUTE IMMEDIATE 'DROP TABLE stream'; | |
3 | EXCEPTION | |
4 | WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; | |
5 | END; | |
6 | / | |
7 | ||
8 | CREATE TABLE stream ( | |
9 | stream_id integer, | |
02936b42 JM |
10 | open number(1,0), -- stream open (1) or closed (0) |
11 | data CLOB, -- queued stream data | |
12 | rl_prompt varchar2(256) -- prompt for readline input | |
97df14cd JM |
13 | ); |
14 | ||
15 | -- stdin | |
16 | INSERT INTO stream (stream_id, open, data, rl_prompt) | |
17 | VALUES (0, 0, '', ''); | |
18 | -- stdout | |
19 | INSERT INTO stream (stream_id, open, data, rl_prompt) | |
20 | VALUES (1, 0, '', ''); | |
21 | ||
22 | -- --------------------------------------------------------- | |
23 | ||
06951f55 JM |
24 | BEGIN |
25 | EXECUTE IMMEDIATE 'DROP TABLE file_io'; | |
26 | EXCEPTION | |
27 | WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; | |
28 | END; | |
29 | / | |
30 | ||
31 | CREATE TABLE file_io ( | |
32 | path varchar2(1024), -- file to read/write | |
02936b42 | 33 | data CLOB, -- file data |
06951f55 JM |
34 | error varchar2(1024), -- any errors during read |
35 | in_or_out varchar2(4) -- input ('in') or output ('out') | |
36 | ); | |
37 | ||
38 | -- --------------------------------------------------------- | |
39 | ||
02936b42 JM |
40 | CREATE OR REPLACE PACKAGE io IS |
41 | PROCEDURE open(sid integer); | |
42 | PROCEDURE close(sid integer); | |
43 | FUNCTION read(sid integer DEFAULT 0) RETURN CLOB; | |
44 | FUNCTION readline(prompt varchar, sid integer DEFAULT 0) RETURN CLOB; | |
45 | PROCEDURE write(input CLOB, sid integer DEFAULT 1); | |
46 | PROCEDURE writeline(data CLOB, sid integer DEFAULT 1); | |
47 | FUNCTION wait_rl_prompt(sid integer DEFAULT 0) RETURN varchar; | |
48 | PROCEDURE wait_flushed(sid integer DEFAULT 1); | |
49 | FUNCTION file_open_and_read(path varchar) RETURN varchar; | |
50 | END io; | |
51 | / | |
52 | show errors; | |
53 | ||
54 | CREATE OR REPLACE PACKAGE BODY io AS | |
55 | ||
56 | PROCEDURE open(sid integer) AS | |
8119e744 | 57 | PRAGMA AUTONOMOUS_TRANSACTION; |
97df14cd | 58 | BEGIN |
2866f9a8 | 59 | -- DBMS_OUTPUT.PUT_LINE('io.open(' || sid || ') start'); |
97df14cd JM |
60 | UPDATE stream SET data = '', rl_prompt = '', open = 1 |
61 | WHERE stream_id = sid; | |
62 | COMMIT; | |
2866f9a8 | 63 | -- DBMS_OUTPUT.PUT_LINE('io.open(' || sid || ') done'); |
97df14cd | 64 | END; |
97df14cd | 65 | |
02936b42 | 66 | PROCEDURE close(sid integer) AS |
8119e744 | 67 | PRAGMA AUTONOMOUS_TRANSACTION; |
97df14cd | 68 | BEGIN |
2866f9a8 | 69 | -- DBMS_OUTPUT.PUT_LINE('io.close(' || sid || ') start'); |
97df14cd JM |
70 | UPDATE stream SET rl_prompt = '', open = 0 |
71 | WHERE stream_id = sid; | |
72 | COMMIT; | |
2866f9a8 | 73 | -- DBMS_OUTPUT.PUT_LINE('io.close(' || sid || ') done'); |
97df14cd | 74 | END; |
97df14cd | 75 | |
2866f9a8 | 76 | -- read: |
97df14cd JM |
77 | -- read from stream stream_id in stream table. Waits until there is |
78 | -- either data to return or the stream closes (NULL data). Returns | |
79 | -- NULL when stream is closed. | |
02936b42 | 80 | FUNCTION read(sid integer DEFAULT 0) RETURN CLOB IS |
97df14cd | 81 | PRAGMA AUTONOMOUS_TRANSACTION; |
02936b42 | 82 | input CLOB; |
97df14cd JM |
83 | isopen integer; |
84 | sleep real; | |
85 | BEGIN | |
86 | sleep := 0.05; | |
87 | -- poll / wait for input | |
88 | WHILE true | |
89 | LOOP | |
90 | -- atomic get and set to empty | |
91 | -- LOCK TABLE stream IN EXCLUSIVE MODE; | |
92 | SELECT data, open INTO input, isopen FROM stream | |
93 | WHERE stream_id = sid; | |
8119e744 | 94 | IF input IS NOT NULL THEN |
97df14cd JM |
95 | UPDATE stream SET data = '' WHERE stream_id = sid; |
96 | COMMIT; | |
97 | RETURN trim(TRAILING chr(10) FROM input); | |
98 | END IF; | |
99 | -- '' -> no input, NULL -> stream closed | |
100 | --RAISE NOTICE 'read input: [%] %', input, stream_id; | |
101 | IF isopen = 0 THEN | |
150011e4 | 102 | raise_application_error(-20001, |
2866f9a8 | 103 | 'io.read: stream ''' || sid || ''' is closed', TRUE); |
97df14cd JM |
104 | END IF; |
105 | SYS.DBMS_LOCK.SLEEP(sleep); | |
106 | IF sleep < 0.5 THEN | |
107 | sleep := sleep * 1.1; -- backoff | |
108 | END IF; | |
109 | END LOOP; | |
110 | END; | |
97df14cd | 111 | |
02936b42 | 112 | -- readline: |
97df14cd | 113 | -- set prompt and wait for readline style input on the stream |
02936b42 | 114 | FUNCTION readline(prompt varchar, sid integer DEFAULT 0) RETURN CLOB IS |
97df14cd JM |
115 | PRAGMA AUTONOMOUS_TRANSACTION; |
116 | BEGIN | |
117 | -- set prompt / request readline style input | |
118 | -- LOCK TABLE stream IN EXCLUSIVE MODE; | |
8119e744 JM |
119 | IF sid = 0 THEN |
120 | wait_flushed(1); | |
121 | ELSIF sid = 1 THEN | |
122 | wait_flushed(0); | |
123 | END IF; | |
97df14cd JM |
124 | UPDATE stream SET rl_prompt = prompt WHERE stream_id = sid; |
125 | COMMIT; | |
126 | ||
02936b42 | 127 | RETURN read(sid); |
97df14cd | 128 | END; |
97df14cd | 129 | |
02936b42 | 130 | PROCEDURE write(input CLOB, sid integer DEFAULT 1) AS |
97df14cd JM |
131 | PRAGMA AUTONOMOUS_TRANSACTION; |
132 | BEGIN | |
133 | -- LOCK TABLE stream IN EXCLUSIVE MODE; | |
134 | UPDATE stream SET data = data || input WHERE stream_id = sid; | |
135 | COMMIT; | |
136 | END; | |
97df14cd | 137 | |
02936b42 | 138 | PROCEDURE writeline(data CLOB, sid integer DEFAULT 1) AS |
97df14cd JM |
139 | PRAGMA AUTONOMOUS_TRANSACTION; |
140 | BEGIN | |
02936b42 | 141 | write(data || TO_CLOB(chr(10)), sid); |
97df14cd | 142 | END; |
97df14cd JM |
143 | |
144 | -- --------------------------------------------------------- | |
145 | ||
146 | -- wait_rl_prompt: | |
147 | -- wait for rl_prompt to be set on the given stream and return the | |
148 | -- rl_prompt value. Errors if stream is already closed. | |
02936b42 | 149 | FUNCTION wait_rl_prompt(sid integer DEFAULT 0) RETURN varchar IS |
97df14cd JM |
150 | PRAGMA AUTONOMOUS_TRANSACTION; |
151 | isopen integer; | |
02936b42 | 152 | prompt CLOB; |
97df14cd JM |
153 | sleep real; |
154 | datas integer; | |
155 | BEGIN | |
156 | sleep := 0.05; | |
157 | WHILE true | |
158 | LOOP | |
9fc524f1 | 159 | LOCK TABLE stream IN EXCLUSIVE MODE; |
97df14cd JM |
160 | SELECT open, rl_prompt INTO isopen, prompt |
161 | FROM stream WHERE stream_id = sid; | |
8119e744 | 162 | SELECT count(stream_id) INTO datas FROM stream WHERE data IS NOT NULL; |
9fc524f1 JM |
163 | |
164 | IF isopen = 0 THEN | |
150011e4 | 165 | raise_application_error(-20001, |
2866f9a8 | 166 | 'io.wait_rl_prompt: stream ''' || sid || ''' is closed', TRUE); |
97df14cd | 167 | END IF; |
9fc524f1 JM |
168 | |
169 | -- wait until all channels have flushed | |
170 | IF datas = 0 AND prompt IS NOT NULL THEN | |
97df14cd JM |
171 | UPDATE stream SET rl_prompt = '' WHERE stream_id = sid; |
172 | COMMIT; | |
173 | -- Prompt is returned single-quoted because sqlplus trims | |
174 | -- trailing whitespace in select output. | |
175 | RETURN '''' || prompt || ''''; | |
176 | END IF; | |
9fc524f1 | 177 | COMMIT; |
97df14cd JM |
178 | |
179 | DBMS_LOCK.SLEEP(sleep); | |
180 | IF sleep < 0.5 THEN | |
181 | sleep := sleep * 1.1; -- backoff | |
182 | END IF; | |
183 | END LOOP; | |
184 | END; | |
97df14cd | 185 | |
02936b42 | 186 | PROCEDURE wait_flushed(sid integer DEFAULT 1) AS |
97df14cd JM |
187 | PRAGMA AUTONOMOUS_TRANSACTION; |
188 | pending integer; | |
189 | sleep real; | |
190 | BEGIN | |
191 | sleep := 0.05; | |
192 | WHILE true | |
193 | LOOP | |
8119e744 JM |
194 | SELECT count(stream_id) INTO pending FROM stream |
195 | WHERE stream_id = sid AND data IS NOT NULL; | |
97df14cd JM |
196 | IF pending = 0 THEN RETURN; END IF; |
197 | DBMS_LOCK.SLEEP(sleep); | |
198 | IF sleep < 0.5 THEN | |
199 | sleep := sleep * 1.1; -- backoff | |
200 | END IF; | |
201 | END LOOP; | |
202 | END; | |
06951f55 JM |
203 | |
204 | -- --------------------------------------------------------- | |
205 | ||
02936b42 | 206 | FUNCTION file_open_and_read(path varchar) RETURN varchar IS |
06951f55 | 207 | PRAGMA AUTONOMOUS_TRANSACTION; |
02936b42 JM |
208 | sleep real; |
209 | content CLOB; | |
06951f55 JM |
210 | error_msg varchar2(1024); |
211 | BEGIN | |
212 | sleep := 0.05; | |
213 | -- TODO: use unique ID instead of path | |
214 | INSERT INTO file_io (path, data, error, in_or_out) | |
215 | VALUES (path, NULL, NULL, 'in'); | |
216 | WHILE true | |
217 | LOOP | |
218 | LOCK TABLE file_io IN EXCLUSIVE MODE; | |
219 | SELECT data, error INTO content, error_msg | |
220 | FROM file_io WHERE path = path AND ROWNUM = 1; | |
221 | ||
222 | IF error_msg IS NOT NULL THEN | |
223 | raise_application_error(-20010, | |
224 | 'open_and_read error: ''' || error_msg || '''', TRUE); | |
225 | END IF; | |
226 | ||
227 | IF content IS NOT NULL THEN | |
228 | DELETE FROM file_io WHERE path = path; | |
229 | COMMIT; | |
230 | RETURN content; | |
231 | END IF; | |
232 | COMMIT; | |
233 | ||
234 | -- keep waiting | |
235 | DBMS_LOCK.SLEEP(sleep); | |
236 | IF sleep < 0.5 THEN | |
237 | sleep := sleep * 1.1; -- backoff | |
238 | END IF; | |
239 | END LOOP; | |
240 | END; | |
06951f55 | 241 | |
02936b42 | 242 | PROCEDURE file_read_response(path varchar, data varchar) AS |
06951f55 JM |
243 | PRAGMA AUTONOMOUS_TRANSACTION; |
244 | BEGIN | |
245 | UPDATE file_io SET data = data WHERE path = path; | |
246 | END; | |
06951f55 | 247 | |
02936b42 JM |
248 | END io; |
249 | / | |
250 | show errors; |