DBI Version 2 ============= v2.0 - infrastructure changes, mainly relevant for driver authors v2.x - incremental features Change plan for DBI v2.0 ======================== --- Changes that may impact applications: Turning AutoCommit on, such as when { local $dbh->{AutoCommit} = 0; ... } goes out of scope, should trigger rollback not commit. (ODBC does a commit) RISK: This will break code that assumes a commit. REMEDY: Explicitly $dbh->commit where required. Always taint check the $sql for do() and prepare() if perl is in taint mode (can't be disabled). RISK: May impact code running with taint enabled but not DBI TaintIn/Out Also consider other changes to TaintIn/TaintOut attribute semantics. Alter tables() to default $schema to $dbh->current_schema. So tables() will default to returning tables in the current schema. (Should include public synonyms) RISK: This will impact code requiring tables from multiple schema. REMEDY: specify $schema parameter ("%" for all?) Add $dbh->current_schema (default to $dbh->{Username}) Remove old informix fudge in tables() (would only impact people using very old DBD::Informix versions as it now has it's own). Remove "old-style" connect syntax (where driver name is 4th parameter). Change undocumented DBI->err and DBI->errstr methods to warn. Bundle enhanced DBD::Multiplex RISK: may break apps using old DBD::Multiplex disconnect() implies rollback() unless AutoCommit (Driver.xst + drivers) --- Internal Changes Move DBI::xx classes to DBI::xx_base and sanction use of DBI::xx classes for extensions via mixins. Increase size of DBIS (dbistate) structure and imp_xxh.com structures and improve size/version sanity checks. Make ShowErrorStatement=>1 the default when handle is created Mandate use of dbivport.h and related macros. Drivers to alter trace level behaviour (no output at low levels and use named trace topics). Mandate that NUM_OF_FIELDS must be set by execute() and can't be deferred till $sth->{NUM_OF_FIELDS} or fetch*_*() called. Add PERL_NO_GET_CONTEXT for multiplicity/threads? Remove DBIS global and related macros. Add dDBIS to be used in functions (eg like dTHR) that can't access it via a imp_xxh Remove PERL_POLLUTE (so some names will require PL_ or Perl_ prefixes) Update dbipport.h from latest Devel::PPPort. Add function pointers for setting fetched field values into DBIS. IV, UV, NV, PV and SV? Drivers to use this instead of calling sv_setpv (etc) themselves. Use internally for set_fbav(). Add function pointer to indicate 'all fields set'. Use for both per-field and per-row OnFetch hooks. New reset() method: $dbh->reset - disconnects + discards all state related to the particular connection $sth->reset - finish + discards all state related to the particular statement Effectively think of a handle as having two parts: attributes related to a particular connection/statement (CachedKids/NUM_OF_PARAMS) and attribute not-related (AutoCommit/RaiseError). The reset method resets the first set but not the second. The reset method would call uncache(). Rework handle creation to use methods: Maybe $h->new_child(\%handle_attr) dr::connect => $dbh = $drh->new_child(\%attr); $dbh->connect(...) - calls $dbh->reset() & db::prepare => sub ...::db::prepare { my ($dbh, $sql, $attr) = @_; $sth = $dbh->new_child($attr) my @statements = $dbh->preparse($sql); $sth->{PendingStatements} = \@statements if @statements > 1; $sth->prepare( shift @statements ) or return; return $sth; } sub prepare_cached - no change, calls $dbh->prepare. sub ...::st::prepare { $sth->reset; ... } Also need to consider $sth->more_results and its need for reset()-like behaviour. Need to enable drivers to work with DBI v1 or v2: means having both ::db::prepare and ::st::prepare In DBI v2 when a driver is loaded the ::db::prepare() method will be deleted if a ::st::reset method exists. Make $DBI::err etc plain (untied) variables. Set them in set_err() and when returing from dispatch. Clear them, if appropriate, when entering dispatch dispatch(). Enable drivers to provide a hash to map err codes into state values. Unified test suite infrastructure to be reused by all drivers. A big project. -- others -- Add (auto-maintained) #define macro giving the version number of the DBI as an integer in a form that can be used by #if statements (eg 1043000) e.g. Have Makefile.PL write a .h file that contains the value and have that #included by DBIXS.h Fixup @DBD::Foo::ISA and ?->setup_driver issues Add "imp_xxh_t* imp_xxh;" element to com struct that points back at itself so macros can be written to work with imp_??h without needing casts. ALso make it cheap to get h from imp_xxh so only imp_xxh needs to be passed around. Add utility function that does SvUTF8_on(sv) if the sv contains valid-looking utf8. To be used (perhaps via OnFetch hook) where utf8 data is being stored in a non-utf8 aware database. Add DBIS->carp(varargs) to simplify access to Carp::carp so warnings like "execute called with 1 bind variables when 0 are needed" fr do() get reported against caller's file and line number and not a line in DBI.pm pre and post call hooks via ima structure? Remove _not_impl. Alias debug to trace in DBI::(dr/db/st) and remove debug() method from internals. DBD::Multiplex enhancements (Thomas Kishel ): Enable DBIx::HA (http://search.cpan.org/~hasseily/DBIx-HA/HA.pm) features. SQL translation hooks: mx_translate_sql_parent - called by prepare() to translate sql from app mx_translate_sql_child - called for each child handle so each can have different dialect (note that mx_translate_sql_parent could parse into internal tree from which mx_translate_sql_child then 'regenerates' custom sql for the child handle) See also http://c-jdbc.objectweb.org/ Use subversion mechanism for $VERSION in source files. ====== LATER ====== Define expected uft8 behaviour. Basically drivers need to set the uft8 flag on returned strings themselves when appropriate. The DBI I define a way for an application to indicate that a particular column should be flagged as uft8 to help drivers that are not able to determine that themselves. The DBI won't support automatic character set conversions. Define "topic bits" for TraceLevel. %DBI::TraceTopics & %DBD::Foo::TraceTopics "Lint" topic for extra checking, eg warn on $sth DESTROY if still Active "Verbose" topic adds verbosity to any other enabled topics "Connect" topic to log connect/disconnect/reconnect/failed-ping Add topic flags to ima struct and log when bits match? Use one bit for logging just the SQL statement executed (with no extra text) ideally in a way that lets the text file be parsed again later. Perhaps append ";\n\n\n" to each. Add parameter values and row count as comments afterwards? Use one bit for logging just Errors. Ability to remove a handle from the parents cache: $sth->uncache; and $dbh->uncache; for connect_cached Add discard_pending_rows() as an alias for finish() - which will be deprecated. $sth->{ParamAttr} eg { "1" => SQL_VARCHAR, "2" => { TYPE=>SQL_VARCHAR, ora_type=>99 }}; $h->{KidsHandles} = ref to cache (array or hash?) of weakrefs to child handles (bugs pre 5.8.5 with CLONE and weakrefs, see Perl changes 21936 and 22106) DESTROY could automatically disconnect/finish children Document DbTypeSubclass (ala DBIx::AnyDBD) Polish up and document _dbtype_names with an external interface and using get_info. FetchHashReuse attrib (=1 or ={}) copy from dbh to sth and use to optimise fetchrow_hash --- Changes that may affect driver authors Add PERL_NO_GET_CONTEXT for multiplicity/threads? force it for drivers? And enable xsbypass in dispatch if possible. Add log_where() to "trace level set to" log message. Add bind_col($n, \$foo, { OnFetch => sub { ... } }); Add way to specify default bind_col attributes for each TYPE e.g. $dbh->{DefaultBindTypeArgs} = { SQL_DATE => { TYPE => SQL_DATE }, SQL_DATETIME => { TYPE => SQL_DATETIME, OnFetch => \&foo }, }; # effectively automatically adds these as defaults: $sth->bind_col(1, \$foo, { %{ $dbh->{DefaultBindTypeArgs}{$sth->{TYPE}->[1]}, # <== OnFetch => sub { ... } }); # YYYY-MM-DD Method call for drivers to get (or indicate they've got) the sth metadata which can then be used to trigger default bind_cols. Add a handle flag to say that the driver has a hash that maps error codes into SQLSTATE values. The error event mechanism could check for the flag and lookup the SQLSTATE value for the error from the hash. Allow code hook as well. Maybe $dbh->{SQLSTATE_map} = code or hash ref Add minimum subset of ODBC3 SQLSTATE values that should be supported (and corresponding ODBC2 values?) Add more macro hooks to Driver.xst: ping, quote etc. Add dbh active checks to some more sth methods where reasonable. Define consise DBI<>DBD interface with view towards parrot. note that parrot will use more method calls instead of 'sideways' hooks into DBIS and the driver C code. DBI::DBD::Base module? Update DBI::DBD with overview and (at least) recommend Driver.xst strongly. Find XS drivers that don't use it and talk to authors. #define a large negative number to mean 'error' from st_execute and change *.xst to treat either that or -2 as an error. (The -2 is a transition for old drivers.) --- Other changes Simplify layering/subclassing of DBD's Reconsider clone() API See comment under $drh->$connect_meth in DBI.pm about $drh->errstr Ensure child $h has err reset after connect_cached() or prepare_cached() or else document that $DBI:err may be true after those methods even though they haven't failed. Umm. Fixed if $DBI::err isn't tied. Change t/zz_*_pp.t to be t/zXX_*.t where XX is a combination of: - 'pp' (for DBI_PUREPERL=2) - 'mx' (for DBI_AUTOPROXY=dbi:Multiplex:) - 'pr' (for DBI_AUTOPROXY=dbi:Proxy:) mx and pr wouldn't both apply to the same test Add data structure describing attributes Use the data structure to replace similar data in Proxy, Multiplex, PurePerl and other places. Add OnConnect attribute to connect() esp. for connect_cached() Macro to get new statement handle for XS code Trace to tied file handle. Add method to try to make the connection (session) read-only. preparse() - incl ability to split statements on semicolon Hooks for method entry and exit. $dbh->{Statement} can be wrong because fetch doesn't update value maybe imp_dbh holds imp_sth (or inner handle) of last sth method called (if not DESTROY) and sth outer DESTROY clears it (to reduce ref count) Then $dbh->{LastSth} would work (returning outer handle if valid). Then $dbh->{Statement} would be the same as $dbh->{LastSth}->{Statement} Also $dbh->{ParamValues} would be the same as $dbh->{LastSth}->{ParamValues}. Remove dummy 'Switch' driver. Sponge behave_like - generalize into new_child() copy RaiseError, PrintError, HandleError etc from the specified handle but which attributes? LongReadLen, LongTruncOk etc? Presumably all as we're acting as a proxy behind the scenes. Should behave_like handle be dbh or sth or either or same as parent? Add per-handle debug file pointer: NULL default => h->dbis->tracefp if not NULL then dup() via PerlIO for child handles close(h->tracefp) at end of DESTROY macro to do (h->tracefp || h->dbis->tracefp) $h->{TraceFileHandle} ? (enable "local $h->{TraceFileHandle} = ..."?) Move TIEHASH etc to XS (and to PurePerl) Change CachedKids to be a simple attribute cached in the handle hash to remove FETCH method call overhead in prepare_cached(). --- Other things to consider Add $h->err_errstr_state method that returns all three in one go. Support async (non-blocking) mode Add $sql = $dbh->show_create($schema_object_name) to return statement that would create that schema object, where possible. Add $id = $dbh->get_session_id() and $dbh->kill_session_id($id). Study alternate DBI's: ruby python php others? ADO object model identify any features we could usefully support and any incompatibilities etc Add DB version (major.minor ISA major) to DbSubType ISA tree. Add API to get table create statement (ala SHOW CREATE TABLE foo in MySQL). Consider closer mapping to SQL3 CLI API for driver API. Phalanx - test coverage =cut *** Small/quick/simple changes/checks *** fetchall_hashref for multiple keys - pending my $hash_key_name = $sth->{FetchHashKeyName} || 'NAME'; my $names_hash = $sth->FETCH("${hash_key_name}_hash"); my @key_fields = (ref $key_field) ? @$key_field : ($key_field); my @key_values; foreach (@key_fields) { my $index = $names_hash->{$_}; # perl index not column ++$index if defined $index; # convert to column number $index ||= $key_field if DBI::looks_like_number($key_field) && $key_field>=1; push @key_values, undef; $sth->bind_col($index, \$key_value[-1]) or return; } my $rows = {}; my $NAME = $sth->{$hash_key_name}; while (my $row = $sth->fetchrow_arrayref($hash_key_name)) { my $ref = $rows; $ref = $ref->{$_} ||= {} for @key_values; @{$ref}{@$NAME} = @$row; } return \%rows; *** Assorted to-do items and random thoughts *** IN NO PARTICULAR ORDER *** DBIx::DWIW make lasth return outer handle? update lasth on return from method so handles used by the implementation of the called method don't affect it? document dbi_fetchall_arrayref_attr attr of selectall_arrayref(). ODBC 3.5 date and intervals types and subtypes (from unixODBC?) http://www.vpservices.com/jeff/programs/SQL/docs/odbc-getinfo-msdn.html Proxy: allow config to specify SQL to allow/deny via regexen Docs for connect_cached and test with proxy. Attribute to prepare() to prefer lazy-prepare, e.g., don't talk to server till first execute or a statement handle attribute is accessed. How to report error from attribute FETCH as fetch method is marked keep_error? Perhaps some way to make the current keep_error value in the dispatch code available to change (via pointer in DBIS?) so a method can change the value of keep_error that's used when the method returns. Fixed since 1.43? BINDING: Add to docs & tutorial re wrong bind type on a param may cause index to not be used! (Find real examples first) check using EXPLAIN SELECT * WHERE int_indexed_col='42' vs =42. also WHERE int_column = '01' relies on db to convert '01' to an int rather than convert int_colum values to strings (which wouldn't match). > And note that if you are using bind_param_inout as 'bind_param_by_ref', > then the $maxlen parameter is redundant. I suspect all drivers could > implement bind_param_by_ref; most drivers, and specifically the Informix > driver, has no need for bind_param_inout as a mechanism for getting data > back from the database as there are no methods in the database which > work like that. With Informix, values are passed to the database for > placeholders, and values are returned through a cursor, and that's all. Okay. I'll take that as a vote for bind_param_by_ref as an alias for bind_param_inout. >>todo. bind_param_by_ref (or bind_param_byref) could be provided as a fallback method using a BeforeExecute hook to call bind_param with the 'current value' from the reference. Should ParamValues hold the value or the ref? Use ParamAttr to indicate byref? ------ OTHERS: Add method like sub perform_transaction { my ($dbh, $attr, $coderef, @args) = @_; my $wantarray = wantarray; my $use_transaction = 1; my $orig_AutoCommit = $dbh->{AutoCommit}; if ($orig_AutoCommit) { unless (eval { $dbh->{AutoCommit} = 0; 1 }) { die unless $allow_non_transaction; $use_transaction = 0; } } local $dbh->{RaiseError} = 1; eval { @result = ($wantarray) ? $coderef->(@args) : scalar $coderef->(@args); $dbh->commit if $use_transaction; $attr->{OnCommit}->() if $attr->{OnCommit}->(); }; if ($@) { local $@; protect original error my $rv = eval { ($use_transaction) ? $dbh->rollback : 0 }; $attr->{OnRollback}->($rv) if $attr->{OnRollback}; } die if $@; # propagate original error $dbh->{AutoCommit} = 1 if $orig_AutoCommit; return $result[0] unless $wantarray; return @result; } Change bind_column to save the info for get_fbav to use when first called. Thus making bind before execute work for all drivers. ODBC attribute defining if transactions are supported http://www.vpservices.com/jeff/programs/SQL/docs/odbc-getinfo-msdn.html Informix inspired changes? Add hook to DBI::DBD to write a myconfig.txt file into the source directory containing key driver and config info. dbish - state AutoCommit status clearly at connect time. (And try to set AutoCommit off in eval?) test shell "/connect user pass" etc check out http://tegan.deltanet.com/~phlip/DBUIframe.html Check DBD::Proxy connect&fetch latency (e.g. CGI use). ****** Less urgent changes ****** $dbh->ping($skip_seconds) - skip the ping if ping'd less than $skip_seconds ago and $h->err is false Change connect_cached() to use ping($skip_seconds || 1); $dbh->get_inner_handle / set_inner_handle use to make $dbh->connect return same handle Hook to call code ref on each fetch, pass fbav ref datarow_array(), datarow_arrayref(), datarow_hashref() remove sth from prepare_cached cache. Give handles names: $h->{Id} ? Useful for reporting, Multiplex, DBD::AnyData etc etc May become useful for weakrefs etc --- Fetch scroll and row set fetch_scroll() handling via get_fbav. Also add: row_array(offset) row_arrayref(offset) row_hashref(offset) get_fbav has three modes: single row - return cached RV to same cached AV alternate rows - return RV to AV[row % 2] row set - return RV to AV[++row] Enable fetchall_arrayref() to reuse a cached rowset so the overhead of allocating and freeing the individual row arrays and the rowset array can be avoided. fetchall_arrayref would then return the same arrayref each time. Most useful when combined with $maxrows. Bless row into DBI::Row ? Bless row set into DBI::Rowset ? Give get/set access to entire rowset via method calls? want to be able to plug in pre-loaded data row cache to new sth so it'll return the same data. Add 'break handling' when field values change? Use two fbav's so 'previous record' is available. Define break fields and handlers. Call them via an alternate fetch_with_break method. Jan 2002: Also now see DBIx::FetchLoop (Brendan Fagan) Alternatively, and perferably, add sufficient hooks for this to be done efficiently externally. Devel::Leak integration? XA transaction interface. References: http://xapool.experlog.com/ http://www.opengroup.org/publications/catalog/s423.htm http://www-106.ibm.com/developerworks/websphere/library/techarticles/0407_woolf/0407_woolf.html?ca=dnp-327 Consider issues affecting OSMM score. Add relevant notes to docs. --- DBI::Profile Add %time to per-node DBI::Profile dump Add 'executer' and 'fetcher' method attributes and increment corresponding counters in DBIS when method with those attributes are called. When profiling record in the profile data the amount they have incremented. Add DBI_PROFILE option so count is executions and avg time can be totaltime/executions not totaltime/methodcalls. DBI::Profile: add simple way to normalise the sql (convert constants to placeholders) so profiling is more effective for drivers/applications which don't use placeholders. Requires preparse()? DBI::Profile: Add calc of approx XS method call and timing overhead by calling perl_call("DBI::dbi_time") x100 at boot time for profile, and add 1/100 (x2) to each sample. Beware Win32 where resolution is too small and overhead will be 0 normally but may be eg 100ms if overhead probe is on cusp of time unit. Add mechanism so "call path" can be included in the Path of the profile data. Something like "@;..." or optionally just the basename part. (See log_where()) Allow code ref in Path and use result as string for that element of the Path. Fix dbi_time for Windows by using or linking-to Time::HiRes code. --- Add a C call to return boolean for is a number' for a given SV. Needs to do the right thing for a non-numeric string SV that's been tested in a numeric context (eg $a='S23'; foo() if $a==-1; $sth->execute($a)) So if SvNVOK is true but the value is 0 then should also do looks_like_number() to be sure. [Does perl's looks_like_number() do this already, if not what code do callers of looks_like_number() use?] Record attrib STOREs so can be replayed/copied to new or cloned handle. --- Test suite (random thoughts beyond the basic architecture in my head) one test file = one scenario setup (fixture) cleanup (destroy all data, disconnect etc) repeat tests with different data types (CHAR vs NCHAR) (implies changing fixtures?) repeat tests with contextual changes (pureperl/proxy/multiplex etc) test with overloaded and other kinds of 'magical' values Good to have 'behavior' tests were the outcome is noted but doesn't trigger failure e.g. limitation tests: data values out of range, eg truncation, may or may not cause an error depending on the database. random order of subtests leak detection after cleanup