path: root/TODO_2005.txt

Change ideas for DBI
====================

--- 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.
MJE: I may misunderstand this but ODBC commits in this case because
 AutoCommit is turned back on again when the block completes and that
 causes any outstanding txn to be committed. Neither DBD::ODBC or ODBC
 turned AutoCommit back on themselves.

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)
  - MJE I believe this is effectively done now as PERL_POLLUTE was removed
    in 5.13.3

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 old and new approaches,
which means having both ::db::prepare and ::st::prepare
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 returning 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 <tom@kishel.net>):
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<may> 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 "<basename>@<linenum>;..." 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