File: oledb.sql

package info (click to toggle)
virtuoso-opensource 6.1.6+dfsg2-4
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, buster, sid, stretch
  • size: 260,992 kB
  • ctags: 125,220
  • sloc: ansic: 652,748; sql: 458,419; xml: 282,834; java: 61,031; sh: 40,031; cpp: 36,890; cs: 25,240; php: 12,692; yacc: 9,523; lex: 7,018; makefile: 6,157; jsp: 4,484; awk: 1,643; perl: 1,013; ruby: 1,003; python: 326
file content (467 lines) | stat: -rw-r--r-- 14,400 bytes parent folder | download | duplicates (2)
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
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
--
--  oledb.sql
--
--  $Id$
--
--  VIRTOLEDB supporting procedures.
--
--  This file is part of the OpenLink Software Virtuoso Open-Source (VOS)
--  project.
--
--  Copyright (C) 1998-2012 OpenLink Software
--
--  This project is free software; you can redistribute it and/or modify it
--  under the terms of the GNU General Public License as published by the
--  Free Software Foundation; only version 2 of the License, dated June 1991.
--
--  This program is distributed in the hope that it will be useful, but
--  WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
--  General Public License for more details.
--
--  You should have received a copy of the GNU General Public License along
--  with this program; if not, write to the Free Software Foundation, Inc.,
--  51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
--
--

--
-- OLEDB Data Types:
--
-- DBTYPE_EMPTY		= 0,
-- DBTYPE_NULL		= 1,
-- DBTYPE_I2		= 2,
-- DBTYPE_I4		= 3,
-- DBTYPE_R4		= 4,
-- DBTYPE_R8		= 5,
-- DBTYPE_CY		= 6,
-- DBTYPE_DATE		= 7,
-- DBTYPE_BSTR		= 8,
-- DBTYPE_IDISPATCH	= 9,
-- DBTYPE_ERROR		= 10,
-- DBTYPE_BOOL		= 11,
-- DBTYPE_VARIANT	= 12,
-- DBTYPE_IUNKNOWN	= 13,
-- DBTYPE_DECIMAL	= 14,
-- DBTYPE_UI1		= 17,
-- DBTYPE_I1		= 16,
-- DBTYPE_UI2		= 18,
-- DBTYPE_UI4		= 19,
-- DBTYPE_I8		= 20,
-- DBTYPE_UI8		= 21,
-- DBTYPE_FILETIME	= 64,
-- DBTYPE_GUID		= 72,
-- DBTYPE_BYTES		= 128,
-- DBTYPE_STR		= 129,
-- DBTYPE_WSTR		= 130,
-- DBTYPE_NUMERIC	= 131,
-- DBTYPE_UDT		= 132,
-- DBTYPE_DBDATE	= 133,
-- DBTYPE_DBTIME	= 134,
-- DBTYPE_DBTIMESTAMP	= 135,
-- DBTYPE_HCHAPTER	= 136,
-- DBTYPE_PROPVARIANT	= 138,
-- DBTYPE_VARNUMERIC	= 139,
-- DBTYPE_VECTOR	= 0x1000,
-- DBTYPE_ARRAY		= 0x2000,
-- DBTYPE_BYREF		= 0x4000,
-- DBTYPE_RESERVED	= 0x8000
--
--
-- The SEARCHABLE Column Values:
--
-- DB_UNSEARCHABLE	0x01
-- DB_LIKE_ONLY		0x02
-- DB_ALL_EXCEPT_LIKE	0x03
-- DB_SEARCHABLE	0x04
--
--
-- DBCOLUMNFLAGS:
--
-- DBCOLUMNFLAGS_ISBOOKMARK		0x1		1
-- DBCOLUMNFLAGS_MAYDEFER		0x2		2
-- DBCOLUMNFLAGS_WRITE			0x4		4
-- DBCOLUMNFLAGS_WRITEUNKNOWN		0x8		8
-- DBCOLUMNFLAGS_ISFIXEDLENGTH		0x10		16
-- DBCOLUMNFLAGS_ISNULLABLE		0x20		32
-- DBCOLUMNFLAGS_MAYBENULL		0x40		64
-- DBCOLUMNFLAGS_ISLONG			0x80		128
-- DBCOLUMNFLAGS_ISROWID		0x100		256
-- DBCOLUMNFLAGS_ISROWVER		0x200		512
-- DBCOLUMNFLAGS_CACHEDEFERRED		0x1000		4096
-- DBCOLUMNFLAGS_ISCHAPTER		0x2000		8192
-- DBCOLUMNFLAGS_SCALEISNEGATIVE	0x4000		16384
-- DBCOLUMNFLAGS_KEYCOLUMN		0x8000		32768
-- DBCOLUMNFLAGS_ISROWURL		0x10000		65536
-- DBCOLUMNFLAGS_ISDEFAULTSTREAM	0x20000		131072
-- DBCOLUMNFLAGS_ISCOLLECTION		0x40000         262144
-- DBCOLUMNFLAGS_ISSTREAM		0x80000		524288
-- DBCOLUMNFLAGS_ISROWSET		0x100000	1048576
-- DBCOLUMNFLAGS_ISROW			0x200000	2097152
-- DBCOLUMNFLAGS_ROWSPECIFICCOLUMN	0x400000	4194304
--
--
-- Virtuoso Data Types:
--
-- DV_SHORT_INT		188
-- DV_LONG_INT		189
-- DV_SINGLE_FLOAT	190
-- DV_DOUBLE_FLOAT	191
-- DV_NUMERIC		219
-- DV_SHORT_STRING	181
-- DV_LONG_STRING	182
-- DV_STRICT_STRING	238
-- DV_WIDE		225
-- DV_LONG_WIDE		226
-- DV_BIN		222
-- DV_BLOB		125
-- DV_BLOB_BIN		131
-- DV_BLOB_WIDE		132
-- DV_DATE		129
-- DV_TIME		210
-- DV_DATETIME		211
-- DV_TIMESTAMP		128
-- DV_TIMESTAMP_OBJ	208
--
--
--


-- build PROVIDER_TYPES Schema Rowset
create procedure oledb_get_types (in type integer, in best_match_restrict integer)
{
  declare TYPE_NAME NVARCHAR(32);
  declare DATA_TYPE SMALLINT;
  declare COLUMN_SIZE INTEGER;
  declare LITERAL_PREFIX, LITERAL_SUFFIX NVARCHAR(5);
  declare CREATE_PARAMS NVARCHAR(64);
  declare IS_NULLABLE, CASE_SENSITIVE SMALLINT;
  declare SEARCHABLE INTEGER;
  declare UNSIGNED_ATTRIBUTE, FIXED_PREC_SCALE, AUTO_UNIQUE_VALUE SMALLINT;
  declare LOCAL_TYPE_NAME NVARCHAR(32);
  declare MINIMUM_SCALE, MAXIMUM_SCALE SMALLINT;
  declare GUID, TYPELIB, VERSION NVARCHAR(32);
  declare IS_LONG, BEST_MATCH, IS_FIXEDLENGTH SMALLINT;

  result_names (TYPE_NAME, DATA_TYPE, COLUMN_SIZE,
                LITERAL_PREFIX, LITERAL_SUFFIX,
		CREATE_PARAMS,
		IS_NULLABLE, CASE_SENSITIVE, SEARCHABLE, UNSIGNED_ATTRIBUTE,
		FIXED_PREC_SCALE, AUTO_UNIQUE_VALUE,
		LOCAL_TYPE_NAME,
		MINIMUM_SCALE, MAXIMUM_SCALE,
		GUID, TYPELIB, VERSION,
		IS_LONG, BEST_MATCH, IS_FIXEDLENGTH);

  if (type = 2 or type is null) -- DBTYPE_I2
    {
      result (N'smallint', 2, 5, N'', N'', NULL, 1, 0, 3, 0, 1, 1, N'smallint', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }
  if (type = 3 or type is null) -- DBTYPE_I4
    {
      result (N'int', 3, 10, N'', N'', NULL, 1, 0, 3, 0, 1, 1, N'int', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }
  if (type = 4 or type is null) -- DBTYPE_R4
    {
      result (N'real', 4, 7, N'', N'e0', NULL, 1, 0, 3, 0, 0, 0, N'real', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }
  if (type = 5 or type is null) -- DBTYPE_R8
    {
      result (N'float', 5, 15, N'', N'e0', NULL, 1, 0, 3, 0, 0, 0, N'float', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }
  if (type = 128 or type is null) -- DBTYPE_BYTES
    {
      result (N'varbinary', 128, 2000, N'0x', N'', N'max length', 1, 0, 4, 0, 0, 0, N'varbinary', NULL, NULL, NULL, NULL, NULL, 0, 1, 0);
      if (best_match_restrict is null or best_match_restrict = 0)
        {
          result (N'long varbinary', 128, 2147483647, N'0x', N'', NULL, 1, 0, 1, 0, 0, 0, N'long varbinary', NULL, NULL, NULL, NULL, NULL, 1, 0, 0);
      	  result (N'timestamp', 128, 10, N'0x', N'', NULL, 0, 0, 3, 0, 0, 0, N'timestamp', NULL, NULL, NULL, NULL, NULL, 0, 0, 1);
	}
    }
  if (type = 129 or type is null) -- DBTYPE_STR
    {
      result (N'varchar', 129, 2000, N'''', N'''', N'max length', 1, 1, 4, 0, 0, 0, N'varchar', NULL, NULL, NULL, NULL, NULL, 0, 1, 0);
      if (best_match_restrict is null or best_match_restrict = 0)
        result (N'long varchar', 129, 2147483647, N'''', N'''', NULL, 1, 1, 1, 0, 0, 0, N'long varchar', NULL, NULL, NULL, NULL, NULL, 1, 0, 0);
    }
  if (type = 130 or type is null) -- DBTYPE_WSTR
    {
      result (N'nvarchar', 130, 1000, N'N''', N'''', N'length', 1, 1, 4, 0, 0, 0, N'nvarchar', NULL, NULL, NULL, NULL, NULL, 0, 1, 0);
      if (best_match_restrict is null or best_match_restrict = 0)
        result (N'long nvarchar', 130, 1073741823, N'N''', N'''', NULL, 1, 1, 1, 0, 0, 0, N'long nvarchar', NULL, NULL, NULL, NULL, NULL, 1, 0, 0);
    }
  if (type = 131 or type is null) -- DBTYPE_NUMERIC
    {
      result (N'decimal', 131, 40, N'', N'', N'precision,scale', 1, 0, 3, 0, 0, 0, N'decimal', 0, 15, NULL, NULL, NULL, 0, 1, 1);
      if (best_match_restrict is null or best_match_restrict = 0)
        result (N'numeric', 131, 40, N'', N'', N'precision,scale', 1, 0, 3, 0, 0, 0, N'numeric', 0, 15, NULL, NULL, NULL, 0, 0, 1);
    }
  if (type = 133 or type is null) -- DBTYPE_DBDATE
    {
      result (N'date', 133, 10, N'{d ''', N'''}', NULL, 1, 0, 3, 0, 0, 0, N'date', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }
  if (type = 134 or type is null) -- DBTYPE_DBTIME
    {
      result (N'time', 134, 8, N'{t ''', N'''}', NULL, 1, 0, 3, 0, 0, 0, N'time', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }
  if (type = 135 or type is null) -- DBTYPE_DBTIMESTAMP
    {
      result (N'datetime', 135, 19, N'{ts ''', N'''}', NULL, 1, 0, 3, 0, 0, 0, N'datetime', NULL, NULL, NULL, NULL, NULL, 0, 1, 1);
    }

  -- TODO: the 'any' type.

  return;
}
;

create function oledb_dbtype(in dv integer) returns smallint
{
  if (dv = 188)	-- DV_SHORT_INT
    return 2;	-- DBTYPE_I2

  if (dv = 189) -- DV_LONG_INT
    return 3;   -- DBTYPE_I4

  if (dv = 190) -- DV_SINGLE_FLOAT
    return 4;   -- DBTYPE_R4

  if (dv = 191) -- DV_DOUBLE_FLOAT
    return 5;   -- DBTYPE_R8

  if (dv = 219) -- DV_NUMERIC
    return 131; -- DBTYPE_NUMERIC

  if (dv = 181 or dv = 182 or dv = 238 or dv = 125) -- DV_SHORT_STRING, DV_LONG_STRING, DV_STRICT_STRING, DV_BLOB
    return 129; -- DBTYPE_STR

  if (dv = 225 or dv = 226 or dv = 132) -- DV_WIDE, DV_LONG_WIDE, DV_BLOB_WIDE
    return 130; -- DBTYPE_WSTR

  if (dv = 222 or dv = 131) -- DV_BIN, DV_BLOB_BIN
    return 128; -- DBTYPE_BYTES

  if (dv = 129) -- DV_DATE
    return 133; -- DBTYPE_DBDATE

  if (dv = 210) -- DV_TIME
    return 134; -- DBTYPE_DBTIME

  if (dv = 211) -- DV_DATETIME
    return 135; -- DBTYPE_DBTIMESTAMP

  if (dv = 128 or dv = 208) -- DV_TIMESTAMP, DV_TIMESTAMP_OBJ
    return 128; -- DBTYPE_BYTES

  -- by default
  return 129; -- DBTYPE_STR
}
;

create function oledb_dbflags(in dv integer, in nullable integer) returns integer
{
  declare flags integer;

  flags := 0;
  if (dv = 128 or dv = 208)	-- DV_TIMESTAMP, DV_TIMESTAMP_OBJ
    {
      flags := 16 + 512;	-- DBCOLUMNFLAGS_ISFIXEDLENGTH, DBCOLUMNFLAGS_ISROWVER
    }
  else
    {
      if (dv = 188 or dv = 189 or dv = 190 or	-- DV_SHORT_INT, DV_LONG_INT, DV_SINGLE_FLOAT
          dv = 191 or dv = 219 or dv = 129 or	-- DV_DOUBLE_FLOAT, DV_NUMERIC, DV_DATE
          dv = 210 or dv = 211)			-- DV_TIME, DV_DATETIME
        flags := 16 + 4;			-- DBCOLUMNFLAGS_ISFIXEDLENGTH, DBCOLUMNFLAGS_WRITE
      else if (dv = 125 or dv = 132 or dv = 131)	-- DV_BLOB, DV_BLOB_WIDE, DV_BLOB_BIN
        flags := 128 + 4;		-- DBCOLUMNFLAGS_ISLONG, DBCOLUMNFLAGS_WRITE
      else
	flags := 4;			-- DBCOLUMNFLAGS_WRITE

      if (nullable is null or nullable <> 1)
	flags := flags + 32 + 64;	-- DBCOLUMNFLAGS_ISNULLABLE, DBCOLUMNFLAGS_MAYBENULL
    }

  return flags;
}
;

create function oledb_char_max_len(in dv integer, in prec integer) returns integer
{
  if (dv = 188 or dv = 189 or dv = 190 or	-- DV_SHORT_INT, DV_LONG_INT, DV_SINGLE_FLOAT
      dv = 191 or dv = 219 or dv = 129 or	-- DV_DOUBLE_FLOAT,  DV_NUMERIC, DV_DATE
      dv = 210 or dv = 211)			-- DV_TIME, DV_DATETIME
    return null;
  if (dv = 225 or dv = 226 or dv = 132) -- DV_WIDE, DV_LONG_WIDE, DV_BLOB_WIDE
    {
      if (prec < 1073741823)
        return prec;
      return 1073741823;
    }
  return prec;
}
;

create function oledb_char_oct_len(in dv integer, in prec integer) returns integer
{
  if (dv = 188 or dv = 189 or dv = 190 or	-- DV_SHORT_INT, DV_LONG_INT, DV_SINGLE_FLOAT
      dv = 191 or dv = 219 or dv = 129 or	-- DV_DOUBLE_FLOAT,  DV_NUMERIC, DV_DATE
      dv = 210 or dv = 211)			-- DV_TIME, DV_DATETIME
    return null;
  if (dv = 225 or dv = 226 or dv = 132) -- DV_WIDE, DV_LONG_WIDE, DV_BLOB_WIDE
    {
      if (prec < 1073741823)
        return prec * 2;
      return 2147483646;
    }
  return prec;
}
;

create function oledb_num_prec(in dv integer, in prec integer) returns smallint
{
  if (dv = 188)	-- DV_SHORT_INT
    return 5;
  if (dv = 189)	-- DV_LONG_INT
    return 10;
  if (dv = 190)	-- DV_SINGLE_FLOAT
    return 7;
  if (dv = 191) -- DV_DOUBLE_FLOAT
    return 15;
  if (dv = 219)	-- DV_NUMERIC
    {
      if (prec < 40)
        return prec;
      return 39;
    }
  return null;
}
;

create function oledb_num_scale(in dv integer, in scale integer) returns smallint
{
  if (dv = 219)	-- DV_NUMERIC
    return scale;
  return null;
}
;

create function oledb_datetime_prec(in dv integer, in prec integer) returns smallint
{
  if (dv = 211) -- DV_DATETIME
    return 6;
  return null;
}
;

create procedure oledb_procedure_parameters(
  in cat nvarchar, in sch nvarchar, in proc nvarchar, in param nvarchar
)
{
  declare PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME, PARAMETER_NAME nvarchar(128);
  declare ORDINAL_POSITION, PARAMETER_TYPE, PARAMETER_HASDEFAULT smallint;
  declare PARAMETER_DEFAULT nvarchar;
  declare IS_NULLABLE, DATA_TYPE smallint;
  declare CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH integer;
  declare NUMERIC_PRECISION, NUMERIC_SCALE smallint;
  declare DESCRIPTION nvarchar;
  declare TYPE_NAME nvarchar(32);
  declare LOCAL_TYPE_NAME nvarchar;
  declare cols, elt any;
  declare i, n integer;

  cat := upper(cat);
  sch := upper(sch);
  proc := upper(proc);
  param := upper(param);

  result_names (PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME, PARAMETER_NAME,
                ORDINAL_POSITION, PARAMETER_TYPE, PARAMETER_HASDEFAULT, PARAMETER_DEFAULT,
                IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
                NUMERIC_PRECISION, NUMERIC_SCALE, DESCRIPTION, TYPE_NAME, LOCAL_TYPE_NAME);

  for
    select P_NAME from DB.DBA.SYS_PROCEDURES where
      (cat is null or upper(cast(name_part(P_NAME, 0) as NVARCHAR)) = cat) and
      (sch is null or upper(cast(name_part(P_NAME, 1) as NVARCHAR)) = sch) and
      (proc is null or upper(cast(name_part(P_NAME, 2) as NVARCHAR)) = proc) and
      __proc_exists (P_NAME, 0) is null
    order by P_NAME
  do
    {
      cols := procedure_cols (P_NAME);
      if (cols)
	{
	  n := length (cols);
	  i := 0;
	  while (i < n)
	    {
	      elt := aref (cols, i);
              -- do not return SQL_RESULT_COL columns
	      if ((param is null or upper(cast(aref(elt, 3) as NVARCHAR)) = param)
		  and aref(elt, 4) <> 3)
		{
		  result (
		    aref(elt, 0),
		    aref(elt, 1),
		    aref(elt, 2),
		    aref(elt, 3),
		    aref(elt, 9),
		    case aref(elt, 4) when 2 then 2 when 4 then 3 when 5 then 4 else 1 end,
		    0,
		    NULL,
		    either(aref(elt, 8), -1, 0),
		    oledb_dbtype(aref(elt, 5)),
		    oledb_char_max_len(aref(elt, 5), aref(elt, 7)),
		    oledb_char_max_len(aref(elt, 5), aref(elt, 7)),
		    oledb_num_prec(aref(elt, 5), aref(elt, 7)),
		    oledb_num_scale(aref(elt, 5), aref(elt, 6)),
		    NULL,
		    dv_type_title(aref(elt, 5)),
		    NULL
		  );
		}
	      i := i + 1;
	    }
	}
    }
}
;

create function oledb_procedure_definition(in name nvarchar) returns nvarchar
{
  declare text varchar;
  declare more long varchar;
  if (__any_grants('DB.DBA.SYS_PROCEDURES', 1, 'P_TEXT'))
    {
      select P_TEXT, P_MORE into text, more from DB.DBA.SYS_PROCEDURES
        where upper(cast(P_NAME as NVARCHAR)) = upper(cast(name as NVARCHAR));
      if (text is null)
	return cast(more as NVARCHAR);
      return cast(text as NVARCHAR);
    }
  return NULL;
}
;

grant execute on oledb_get_types to public
;
grant execute on oledb_dbtype to public
;
grant execute on oledb_dbflags to public
;
grant execute on oledb_char_max_len to public
;
grant execute on oledb_char_oct_len to public
;
grant execute on oledb_num_prec to public
;
grant execute on oledb_num_scale to public
;
grant execute on oledb_datetime_prec to public
;
grant execute on oledb_procedure_parameters to public
;
grant execute on oledb_procedure_definition to public
;