Debian Codegraph @

driver_characteristics

raw
1 =head1 Database and Driver Characteristics
2
3 This document is designed to give you a flavour of the functionality
4 and quirks of the different DBI drivers and their databases.
5
6 The line between the functionality and quirks of a given driver and the
7 functionality and quirks of its corresponding database is blurred. In
8 some cases the database has functionality that the driver can't or
9 doesn't access, in others the driver may emulate functionality that the
10 database doesn't support, like placeholders. So when you see the terms
11 driver and database below, take them with a pinch of salt.
12
13 We don't attempt to describe the drivers and database in full detail
14 here, we're only interested in the key features that are most commonly
15 used. And for those features we're just providing an outline guide.
16 Consult the database documentation for full details.
17
18 The primary goals are:
19
20 - to provide a simple overview of each driver and database.
21 - to help you initially select a suitable DBI driver and database.
22 - to help you quickly identify potential issues if you need to port
23 an application from one driver and database to another.
24
25 =head2 Driver Name, Version, Author and Contact Details
26
27 This driver summary is for DBD::XBase version 0.130.
28
29 The driver author is Jan Pazdziora and he can be contacted at
30 jpx dash perl at adelton dot com.
31
32 =head2 Supported Database Versions and Options
33
34 The DBD::XBase module supports dBaseIII and IV and Fox* flavors of
35 dbf files, including their dbt and fpt memo files.
36
37
38 =head2 Connect Syntax
39
40 Details of the syntax of the dsn including any optional parts.
41
42 The DBI->connect Data Source Name (DSN) should include the directory
43 where the dbf files are located as the third part.
44
45 dbi:XBase:/path/to/directory
46
47 It defaults to current directory.
48
49 Details of any driver specific attributes applicable to the
50 connect method.
51
52 There are no driver specific attributes for the DBI->connect method.
53
54
55 =head2 Numeric Data Handling
56
57 What numeric data types do the database and driver support? (INTEGER,
58 FLOAT, NUMBER(p,s) etc). What is the maximum scale and precision for
59 each type?
60
61 DBD::XBase supports generic NUMBER(p,s), FLOAT(p,s) and INTEGER(l)
62 types. Maximul scale and precision unknown, limited by Perl's handling
63 of numbers. In the dbf files, the numbers are stored as ASCII strings,
64 or binary integers or floats.
65
66 Existing dbf files come with the field types defined in the dbf file
67 header. Numeric types can be either stored as ASCII string or in some
68 binary format. DBD::XBase (via XBase.pm) parses this information and reads and
69 writes the fields in that format.
70
71 When you create a I<new> dbf file (via CREATE TABLE), the numeric
72 fields are always created in the traditional XBase way, as an ASCII
73 string. (The XBase.pm module offer more control over this.)
74
75 Does the database and driver support numbers outside the valid range
76 for perl numbers?
77 Are numbers returned as strings in this case?
78
79 Numeric fields are always returned as perl numeric values, not strings,
80 so numbers outside of Perl's valid range are not possible (this
81 restriction might be withdrawn in the future).
82
83
84 =head2 String Data Handling
85
86 What string data types does the database support? (CHAR, VARCHAR, etc)
87
88 DBD::XBase knows CHAR(length) and VARCHAR(length), both are stored as
89 fixed length chars however. These can contain any binary values.
90
91 What is the maximum size for each type?
92
93 The maximum length is 65535 characters for both types (even if the
94 older dBase's only allowed 255 characters, so created dbf might not be
95 portable to other xbase compatible software).
96
97 Are any types blank padded? If so which, e.g., CHAR.
98
99 Both CHAR and VARCHAR are blank padded (unless ChopBlanks set).
100
101 How does the database handle data with the 8th bit set (national
102 language character sets etc)?
103
104 Data with the 8th bit set are handles transparently, no national
105 language character set conversions are done.
106
107 Is Unicode supported?
108
109 Since the string types can store binary data, Unicode strings can be
110 stored.
111
112
113 =head2 Date Data Handling
114
115 What date, time and date+time data types are supported
116 and what is their valid range and resolution?
117
118 What is the default output format for each?
119
120 What is the default input format for each?
121 Are multiple input format recognised?
122
123 DBD::XBase supports these date and time types:
124
125 DATE
126 DATETIME
127 TIME
128
129 The DATE type holds an eight character string in the format
130 `YYYYMMDD'. Only that format can be used for input and output.
131 DBD::XBase doesn't check for validity of the values.
132
133 The DATETIME and TIME types store (internally) a 4 byte integer day
134 value (Julian Day System) and a 4 byte integer seconds value (that
135 counts 1/1000's of a second since midnight). DBD::XBase inputs and
136 outputs these types using a floating point unix-style
137 seconds-since-epoch value (possibly with decimal part and possibly
138 negative). This might change in the future.
139
140
141 If only part of a date is specified, how does the rest default?
142
143 If two digit years can be used, how is the century determined?
144
145 N/A
146
147 Can the default format be changed? If so, how (both for a single
148 expression in an sql statement, and as a database connection default)?
149
150 No.
151
152 How can I get the current date+time in an SQL expression?
153
154 There is no way to get the current date/time.
155
156 How can I input date and date+time values in other formats?
157
158 How can I output date and date+time values in other formats?
159
160 N/A
161
162 What kinds of date and time arithmetic and functions are supported?
163
164 None.
165
166 What SQL expression can be used to convert from an integer "seconds
167 since 1-jan-1970 GMT" value to the corresponding database date+time?
168 If not possible, then what perl expression can be used?
169
170 N/A
171
172 What SQL expression can be used to convert from a database date+time
173 value into the corresponding "seconds since 1-jan-1970 GMT" value?
174 If not possible, then what perl expression can be used?
175
176 N/A
177
178 How does the database deal with time zones, especially where the
179 client inserting a date, the server and a client reading the date
180 are all in different time zones?
181
182 There is no time zone handling.
183
184
185 =head2 LONG/BLOB Data Handling
186
187 What LONG/BLOB data types does the database support?
188 (LONG, LONG RAW, CLOB, BLOB, BFILE etc)
189
190 DBD::XBase supports a MEMO data type. BLOB can be used as an alias for
191 MEMO.
192
193 With dBaseIII dbt files, the memo
194 field cannot contain \x1a byte, with dBaseIV and Fox* dbt/fpt's any
195 value can be stored.
196
197 What are their maximum sizes?
198
199 At least 2 GB are possible for all types of memo files.
200
201 Which types, if any, must be passed to and from the database as pairs
202 of hex digits?
203
204 N/A
205
206 Do the LongReadLen and LongTruncOk attributes work as defined?
207 What is the maximum value, if any, for LongReadLen?
208
209 The LongReadLen and LongTruncOk attributes are ignored/are broken
210 (will be fixed).
211
212 Is special handling required for some or all LONG/BLOB data types?
213
214 N/A
215
216
217 =head2 Other Data Handling issues
218
219 Does the driver support type_info method?
220
221 The DBD::XBase driver supports the type_info method.
222
223
224 =head2 Transactions and Transaction Isolation
225
226 Does the database support transactions?
227
228 DBD::XBase does not support transactions.
229
230 If so, what is the default transaction isolation level?
231
232 N/A
233
234 What other isolation levels are supported and how can they
235 be enabled per-connection or per-transaction?
236
237 N/A
238
239
240 =head2 Explicit Locks
241
242 What is the default locking behaviour for reading and writing?
243
244 DBD::XBase does not lock the tables (files) it is working on.
245 (Hopefully some mechanism will be provided in the future.)
246
247 What statements can be used to explicitly lock a table with
248 various types/levels of lock? E.g., "lock table ...".
249
250 N/A
251
252 How can a select statement be modified to lock the selected rows
253 from being changed by another transaction.
254 E.g., "select ... for update".
255
256 N/A
257
258
259 =head2 No-Table Expression Select Syntax
260
261 What syntax is used for 'selecting' a constant expression
262 from the database? E.g., "select 42 from dual" (Oracle).
263
264 You can't select a constant expression using DBD::XBase. Only
265 table field names, or * for all, can be selected.
266
267
268 =head2 Table Join Syntax
269
270 If equi-joins are supported but don't use the standard
271 "WHERE a.field = b.field" syntax, then describe the syntax.
272
273 DBD::XBase does not support table joins.
274
275 Are 'outer joins' supported, if so with what syntax?
276
277 N/A
278
279
280 =head2 Table and Column Names
281
282 What is the max size of table names? And column names?
283
284 The XBase format stores each table as a distinct file. The
285 table names are limited by filesystem's maximum filename length.
286
287 Column names are limited to 11 characters.
288
289 What characters are valid without quoting?
290
291 Table and field names have to start with letter, a combination of
292 letters, digits and underscores may follow.
293
294 Can table and field names be quoted? If so, how?
295 What characters are valid with quoting?
296
297 DBD::XBase does not support putting quotes around table or column names.
298
299 Are table/column names stored as uppercase, lowercase or
300 as-entered?
301 Are table/column names case sensitive?
302
303 Table names are stored and treated as entered. The case sensitivity
304 depends on the filesystem that the file is stored on. Column names are
305 stored as uppercase and are not case sensitive.
306
307 Can national character sets (with the 8th bit set) be used?
308
309 National character sets can be used.
310
311
312 =head2 Case sensivity of like operator
313
314 Is the LIKE operator case sensitive?
315 If so, how can case insensitive LIKE operations be performed?
316
317 The LIKE operator is not case sensitive. There is (currently) no case
318 sensitive operator.
319
320
321 =head2 Row ID
322
323 If the database supports a 'row id' pseudocolumn, what is
324 it called? E.g., 'rowid' in Oracle, 'tid' in Ingres.
325
326 DBD::XBase does not support a 'row id' pseudocolumn.
327
328 Can it be treated as a string when fetching and reusing in
329 later statements? If not, what special handling is required?
330
331 N/A
332
333
334 =head2 Automatic Key or Sequence Generation
335
336 Does the database offer automatic key generation such as
337 'auto increment' or 'system generated' keys?
338
339 Does the database support sequence generators?
340 If so, what syntax is used?
341
342 DBD::XBase does not support automatic key generation or sequence
343 generators owing to the limitations of the XBase format.
344
345
346 =head2 Automatic Row Numbering and Row Count Limiting
347
348 Can you select a row-numbering pseudocolumn and if so, what
349 is it called?
350
351 DBD::XBase does not support a row-numbering pseudocolumn.
352
353
354 =head2 Parameter binding
355
356 Is parameter binding supported by the database, emulated by the
357 driver or not supported at all?
358
359 Parameter binding is implemented in the driver.
360
361 If parameter binding is supported, is the :1 placeholder style also
362 supported?
363
364 The :1 placeholder style is not (yet) supported.
365
366 Does the driver support the TYPE attribute do bind_param?
367 If so, which types are supported and how do they affect the bind?
368
369 No.
370
371 Do unsupported values of the TYPE attribute generate a warning?
372
373 N/A
374
375
376 =head2 Stored procedures
377
378 What syntax is used to call stored procedures and, where possible,
379 get results?
380
381 Stored procedures are not applicable in the XBase format.
382
383
384 =head2 Table Metadata
385
386 Does the driver support table_info method?
387
388 DBD::XBase supports the table_info method.
389
390 How can detailed information about the columns of a table be fetched?
391
392 There si no way to get that information (at the moment).
393
394 How can detailed information about the indexes of a table be fetched?
395
396 Indexes are not supported.
397
398 How can detailed information about the keys of a table be fetched?
399
400 Keys are not supported.
401
402
403 =head2 Driver-specific database handle attributes
404
405 xbase_ignorememo.
406
407 XXX expand description
408
409
410 =head2 Driver-specific statement handle attributes
411
412 xbase_ignorememo.
413
414
415 =head2 Default local row cache size and behaviour
416
417 Does the driver or database implement a local row cache when fetching
418 rows from a select statement? What is the default size?
419
420 DBD::XBase doesn't maintain a row cache (not applicable since the data
421 file is local to the driver).
422
423
424 =head2 Positioned updates and deletes
425
426 Does the driver support positioned updates and deletes (also called
427 updatable cursors)? If so, what syntax is used? E.g, "update ...
428 where current of $cursor_name".
429
430 DBD::XBase does not support positioned updates or deletes.
431
432
433 =head2 Differences from the DBI specification
434
435 List any significant differences in behaviour from the current DBI
436 specification.
437
438 DBD::XBase has no known significant differences in behaviour from the
439 current DBI specification.
440
441
442 =head2 URLs to more database/driver specific information
443
444 http://www.clicketyclick.dk/databases/xbase/format/
445
446
447 =head2 Multiple concurrent database connections
448
449 Does the driver allow multiple concurrent database connections
450 to the same database?
451
452 DBD::XBase supports an unlimited number of concurrent database
453 connections to one or more databases.
454
455
456 =head2 Concurrent use of multiple statement handles from same $dbh.
457
458 Does the driver allow a new statement handle to be prepared and used
459 while still fetching data from another statment handle associated
460 with the same database handle?
461
462 DBD::XBase supports the preparation and execution of a new statement
463 handle while still fetching data from another statment handle
464 associated with the same database handle.
465
466
467 =head2 Driver specific methods
468
469 What generally useful private ($h->func(...)) methods are provided?
470
471 DBD::XBase has no generally useful private methods.
472
473
474 =head2 Future Changes Planned for the Driver
475
476 Adding :1 style of placeholders; handling numbers outside of Perl's
477 numeric range.
478
479 =head2 How to get value of auto-increment field
480
481 If your database supports some kind of auto-increment key
482 then how can a script get the value of the key used for
483 the most recent insert statement?
484
485 DBD::XBase does not support auto-increment keys.
486
487 =head2 Auto conversion of numbers to strings and strings to numbers?
488
489 Does your database automatically convert strings to numbers
490 and number to strings as needed? E.g. does
491 INSERT INTO foo (num_field, str_field) VALUES ('42',42)
492 work? If not, what type conversion functions are needed.
493
494 Yes, DBD::XBase automatically converts between strings and numbers.
495
496 =head2 And strings to date and dates to strings?
497
498 Does your database automatically convert strings to dates
499 and dates to strings as needed? E.g. does
500 INSERT INTO foo (date_field) VALUES ('...date string...')
501 work? If not, what type conversion functions are needed.
502
503 Yes, DBD::XBase automatically converts between dates and strings.
504
505 =head2 Which character types can store embedded nulls?
506
507 Any? None?
508
509 Any.
510
511 =head2 Does commit close/finish all prepared statements?
512
513 Yes? No? Yes but the driver hides this by re-preparing as needed?
514
515 N/A, only AutoCommit mode is supported.
516
517 =head2 Are any emulations of other interfaces supplied?
518
519 Like Ingperl, Oraperl etc
520
521 No emulation layers are supported.
522
523 =head2 String concatenation operator
524
525 || or + or CONCAT() or what?
526
527 String concatenation is not supported.
528
529 =head2 NUM_OF_FIELDS set by prepare or execute?
530
531 Which?
532
533 NUM_OF_FIELDS is set by execute.
534
535 =head2 Other Significant Database Features
536
537 This is where you get a chance to 'sell' your database and driver.
538 What's most important to most potential perl DBI users?
539
540 It's a lovely piece of software, especially when you have to deal with
541 dbf files.
542
543 =cut
544