This library provides - a mechanism for automated (passwordless) and yet fairly secure access to the system database - connect to local database, hide authentication info - use remote db if available, local otherwise - translation between Perl and SQL structures, assuming that the database obeys certain rules - interface for most common db uses such as 'get' and 'put', the latter being a combination of update and insert - treat all input and output as utf-8

The following functions are stable and for general use

  - put
  - delete
  - get_record
  - get_records

See usage examples below.

The functions of the A2E::Prog library such as

  - vecho
  - decho
  - exek
  - exek_open3

are also available in the object.

A2E::Daba is intended to be the successor of A2E::db.

SYNOPSIS

use A2E::Daba;


my $db = new_ready A2E::Daba;
my ($var1, $var2, ...) = $db->get_record(qq{select var1, var2, .... from ...}) ;
$db->put('my_table', { key1=>'keyval1', key2=>'keyval2' }, val1=>'valval1', ... ;

BUGS, LIMITATIONS

The squoted function may have to be adapted to non-PostgreSQL databases. The documentation effort has just begun. The configuration mechanism needs clarification.

Further rationalisation could be achieved by introducing a database schema configuration mechanism similar to what has been done in some other applications such as mlht and php_users.

Related Systems

PREREQUISITES

    A2E::OpenPGP(3)
    DBI(3)
    Sys::Hostname(3)

OPTIONS

--daba|--db a2e: name of database to be used by default
--dabas|--dbs a2e=1: make a database available and activate/deactivate it by assigning it a 1/0 value
--maxrows 1: maximum number of rows that may be affected by an 'update' operation. If more than this is affected we die.
--daba_auto: Automatic/batch mode, use anonymous access, don't even try prompting for a password
--daba_anon_user|--dau a2e=apache: User to use as long as none is specified. This user should have at least minimal access rights to the database.
--daba_user|--du a2e=phm: User specific to database. This is usually specified in personal configuration files. By default it is the system user. If no daba_pass is specified, we fall back to daba_anon_user
--daba_anon_pass|--dap topsikret: Cleartext passwords for the given databases belonging to the user specified by --daba_anon_user. These should not be directly stored in any configuration file but could be useful as a commandline options. They can be supplied by environment variables such as A2E_ANON_PASS, assuming that daba is a2e, or via the --daba_anon_pass_fayl file. They can be cached in cleartext files whose names are derived using the value of the --daba_anon_pass_fayl option. The authoritative source information should reside in the --daba_anon_pass_pgp option. Edit only that option (in a config file) and then delete the derived files so they will be regenerated.
--daba_pass|--dp a2e=topsikret: Cleartext passwords of the current user for the given databases. They should never be stored in a configuration file. They can be specified via environment variables like A2E_PASS, where A2E is the concerned daba (internal database name) in upper case. They can be cached in cleartext in files whose names are derived using the sprintf format specified through the --daba_pass_fayl option with the daba (database identifier) as its only argument. The authoritative source information should reside in the --daba_pass_pgp option. Edit only that option (in a config file) and then delete the derived files so they will be regenerated.
--daba_anon_pass_pgp|--dapp a2e=pgp_message: Encrypted passwords for the anonymous user of each database. Store in sitewide config file using heredoc syntax. This should usually be the master source from which cached passwords, if any, are generated.a
--daba_pass_pgp|--dpp: Encrypted password for the individual user of each database. Store in individual config file using heredoc syntax. This should usually be the master source from which cached passwords, if any, are generated.
--daba_anon_pass_fayl|--dapf a2e=/var/tmp/.a2e_pass: group-readable file where to cache the anonymous user's cleartext password so that it needn't be read interactively or from memory next time; as long as this is not defined no cleartext storing is done. This file is created by the program if not present already. For this reason it should be in a directory where the program has write access. Enabling this option is the way to go if you want your applications to modify remote databases while working in the background, e.g. invoked by cronjobs, without any human initialisation (and without provoking your system's users to store even more precious cleartext passwords elsewhere).
--daba_pass_fayl|--dpf a2e=~/.a2e_pass: user-readable file where to store the individual user's cleartext password so that it needn't be read interactively or from memory next time; as long as this is not defined no cleartext storing is done. This file is created by the program if not present already. For this reason it should be in a directory where the program has write access. By setting this option you take an unnecessary security risk for the sake of making it easier for more automatic processes to use your database applications. The preferred way to meet such needs is by using the daba_anon_pass_fayl option.
--daba_hsys a2e=a2e: machine (hardware system) where the database is found. Each hsys has its own arbitrary symbol.
--daba_name a2e=a2e: the actual name of the database that is used when connecting to it. This may differ from the arbitrary daba symbol that we use here.; By default it is the same.
--hsys_mach a2e=bartu.bos.a2e.de: Fully qualified domain name of the hsys machine
--daba_lokal: Whether to limit queries to localhost.; Same effect was previously obtained by setting --host to 'localhost'.
--fldtyp tadatum=d: field type of a database field/column.; This is part of a quick & dirty sql-perl mapping syntax which is subject to change
--optyps: operator definitions. a means of extension that is still in development and not ready for public use yet.
--sqllog /var/opt/a2e/log/daba_%s%d%s.sql: filename template of file where to log sql commands that were used, so that these may be applied to another database later. template argument 1 (%s) is the name of the database, argument 2 (%d) is 1 for commands on the local database (daba_lokal true), 0 otherwise, 3 (%d) is the time of invocation (progtime) of the program. Normally this should be set in a configuration file, so that the $user variable or tilde expansion ~ can be used. TODO: possibly we should send this stuff to a list of concerned addresses where mail processors listen and react by updating their respective local databases. This could be done by a special call at the end of program execution which sends out the accumulated files.
--dpllog /var/opt/a2e/log/daba_%s%d%d.dpl: filename template of file where to log high-level deplate database modification instructions that were used, so that these may be applied to another database later. structure like --sqllog.

VERSION

This describes version 0.1.1

FILES

Configuration

    %{sysconfdir}/daba.konf
    ~/.daba_konf
    ./@daba

Applications

    A2E::Dokfs(3) -- built on A2E::Daba
    A2E::Mondeb::Daba(3)
  http://a2e.de/adv/perl/A2E

Related Tools

    daba_pass(1) -- generate statements to be source into a shell environment so as to cache passwords
    env_cache(1) -- invoke daba_pass in bash
    mktdir_bashrc(1) -- contains definition of shell function cdok that changes a directory to a document (identified by its symbol rather than its directory location) and, if needed, creates the password cache
    a2e_pgp(1) -- use public key encryption and optionally read password from environment
                                                                               
=head1 AUTHOR

    Hartmut Pilch

COPYRIGHT

    Copyright (c) 2007-8 Hartmut Pilch

    This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

IMPLEMENTATION

Construct the Object

Function defvars

Function daba_postkonfig

Some setup work that needs to be performed after all configuration options have been read and calculated: find the database and connect to it.

Normally done via setup, but some applications may need to access the functionality directly so as to build their own setup routine.

Function postkonfig

daba_postkonfig for overloading

Function daba_konfig

Parse database dependent configuration file. A convenience subroutine used in lastkonfig overloads e.g. in A2E::Privat::Db2auth(3)

Deprecated: to support simultaneous use of multiple databases, use hash variables instead.

Function dbi_konekt

Subroutine of daba_konekt

Function daba_aktiv

Function daba_konekt

Connect to the database using the collected identification+authentication info.

Quickly return non-null value if connection is already established.

Connect to the central server if possible, to a local copy otherwise.

Subroutines for translating Perl to SQL

Function squoted

enclose a string with single quotes for SQL, escape existing single quotes by reduplication (as required by PostgreSQL, other databases may differ)

Function squoted_or_null

Same as squoted but for update/insert statements where empty strings are replace with NULL.

Function deplate_quoted

enclose a string with double quotes, escape existing double quotes by backslash, as needed by deplate et al

Function fldtyp

type of a given field, as far as known from available database schema information.

Function sql_query

Send an SQL command and collect number of affected rows.

A subroutine for higher-level commands below.

The end-user also needs this for special cases that can not be expressed well with higher-level commands.

Edit database fields (update/insert, delete)

Function put

example:

    $db->put('users', { user=>'johndoe' }, mail=>'john@doe.com', sex=>'m' )

    $db->put('diary', { user=>'johndoe', day=>'2007-10-07' }, entry=>'great job done today')

arg 1 is the table name, arg 2 a reference to an array that identifies the range of searched records (normally consisting of key-value pairs of the indexed fields which together constitute a unique identifier), args 3 and onward specify values to write into the found records.

put tries to update an existing record, and, if that fails, inserts a new record. If the 'update' would result in change of more than one record, nothing is done.

This behaviour can be modified by setting a maximum number of rows that may be updated by one 'put' command through $m->{maxrows}. If maxrows is 0, there is no limit and you are on your own.

Function edit

An alias for 'put', deprecated, may be removed soon.

Function delete

Similar to 'put', but delete the found records.

Example

    $db->delete('users', { user=>'johndoe' });

    $db->delete('diary', { user=>'johndoe', day=>'2007-10-07' });

Build Select Queries

Function fldtyp_squoted

Function sql_kol_val

Build SQL column value comparison expression from a Perl key-value pair.

Takes a key and a more or less complex value.

    app => 'phpbb' ---> app = 'phpbb'
    app => ['=', 'phpbb'] --->  app = 'phpbb'
    app => ['like', '%phpbb'] ---> app like '%phpbb'
    app => ['in', 'a', 'b', 'c'] ---> app in ( 'a', 'b', 'c')
    app => ['~', '^phpbb'] ---> app ~ '^phpbb' 
    app => ['>', 'a'] --> app > 'a'
    app => ['<', 'z'] --> app < 'z'
    app => ['<=', 'z'] --> app <= 'z'

Function sql_kond

Build an SQL Where expression from a Perl hash as follows

Examples

{ person => ffii, date => ['<=..<', '2007-01-01', '2008-01-01' ] }

        --> person == 'ffii' and '2007-01-01' <= date and date < '2008-01-01'

        { 'a.taktg' => [ '=',  [ 'b' 'dokktg' ] ] }
 
        -->  a.taktg == b.dokktg

Complex comparison functions can be expressed as list references as explained in sql_kol_val

Function get_select_tables

Accept a simple or complex expression describing a table or joined set of tables, return the corresponding SQL string and the list of concerned tables.

Examples:

'taktg'

    --> 'taktg', 'taktg'

[ 'taktg', 'tadok' ]

    --> 'taktg natural join tadok', 'taktg', 'tadok'

[ [ 'taktg', 'k' ], [ [ 'tadok', 'd' ], 'join', 'k.ktg' => [ '=', 'd.dok' ] ] ]

    --> 'taktg as k join tadok as d on ( k.ktg == d.dok )', 'taktg', 'tadok'

Function get_select_query

A subroutine of get_record and get_records, builds an SQL select statement from perl variables of the following structure:

 arg 1: tab: a string or complex expression denoting the table(s) to search
 arg 2: keysref: a reference to a hash, specifically a set of key value records where the key is a field and the value either a simple string or a reference to a list expression that specifies a condition to be met by the field, e.g. ['~', '^phpbb'] or ['like', '%phpbb'] or ['in', 'a', 'b', 'c']
 arg 3: opts: further modifiers, such as group, order, having, limit etc

INTERFACE

Querying Functions

Function get_record

Return columns values of one found record.

Example:

    my ($mail, $sex) = $db->get_record('users', { user=>'johndoe' }, 'mail', 'sex');

Function get_record_tuples

Like get_record, but give back a hashref. Not tested yet.

Function get_records

Return a list of references to retrieved records.

Example:

    @daily_entries = $db->get_records('diary', { user=>'johndoe' }, 'day', 'entry');

    foreach $daily_entry (@daily_entries) {
        ($day, $entry) = @$daily_entry;
        print "entry of johndoe on day $day: $entry\n";
    };