Database Class Reference

contains the class for connecting to Noalyss More...

Inheritance diagram for Database:

Collaboration diagram for Database:

Public Member Functions
	__construct ($p_database_id=0, $p_type='dos')
	constructor
	__toString ()
	apply_patch ($p_name)
	loop to apply all the path to a folder or a template Upgrade check if the folder $p_name needs to be upgrade thanks the variable DBVERSION and run all the SQL script named upgradeX.sql from the folder noalyss/include/sql/patch until X equal DBVERSION-1
	format_name ($p_id, $p_type)
	return the name of the database with the domain name
	get_version ()
	Get version of a database, the content of the table version.
Public Member Functions inherited from DatabaseCore
	__construct ($p_user, $p_password, $p_dbname, $p_host, $p_port)
	!< $dbuser(string) Database user
	alter_seq ($p_name, $min)
	alter the sequence value
	clean_orphan_lob ()
	Find all lob and remove those which are not used by any tables.
	clear_all_prepare ()
	clear all prepare stmt
	clear_prepare ($sql_name)
	clear a prepare stmt
	close ()
	wrapper for the function pg_close
	commit ()
	Commit the transaction.
	count ($p_ret=null)
	synomym for size()
	count_sql ($p_sql, $p_array=null)
	Count the number of row returned by a sql statement.
	create_sequence ($p_name, $min=1)
	Create a sequence.
	exec_sql ($p_string, $p_array=null)
	send a sql string to the database
	execute ($p_string, $p_array)
	wrapper for the function pg_execute
	execute_script ($script)
	Execute a sql script.
	exist_blob ($p_oid)
	check if the large object exists
	exist_column ($col, $table, $schema)
	Check if a column exists in a table.
	exist_database ($p_name)
	Count the database name in a system view.
	exist_schema ($p_name)
	test if a schema exists
	exist_sequence ($p_name)
	test if a sequence exist
	exist_table ($p_name, $p_schema='public')
	test if a table exist
	exist_view ($p_name)
	fetch ($p_indice, $p_mode=PGSQL_ASSOC)
	fetch the $p_indice array from the last query
	get_affected ()
	return the number of rows affected by the previous query
	get_array ($p_sql, $p_array=null, $p_mode=PGSQL_ASSOC)
	purpose return the result of a sql statment in a array
	get_current_seq ($p_seq)
	get the current sequence value
	get_db ()
	get_dbhost ()
	get_dbname ()
	get_dbport ()
	get_dbuser ()
	get_encoding ()
	get_is_open ()
	get_name ()
	return the name of the current database
	get_next_seq ($p_seq)
	get the next sequence value
	get_ret ()
	get_row ($p_sql, $p_array=NULL)
	Returns only one row from a query.
	get_sql ()
	last SQL stmt executed
	get_value ($p_sql, $p_array=null)
	return the value of the sql, the sql will return only one value with the value
	is_prepare ($query_name)
	Check if a prepared statement already exists or not.
	lo_export ($p_oid, $tmp_file)
	wrapper for the function pg_lo_export
	lo_import ($p_filename)
	wrapper for the function pg_lo_export
	lo_read ($oid)
	read a Large object with data content in a binary
	lo_replace ($binary_data, $oid)
	replace a Large object with data content in a binary
	lo_unlink ($p_oid)
	wrapper for the function pg_lo_unlink
	lo_write ($binary_data)
	large_object writee: create a Large object if oid is not given with data content in a binaray
	make_array ($p_sql, $p_null=0, $p_array=null)
	make a array with the sql.
	make_list ($sql, $p_array=null)
	create a string containing the value separated by comma for use in a SQL in statement
	prepare ($p_string, $p_sql)
	wrapper for the function pg_prepare
	query_to_csv ($ret, $aheader)
	with the handle of a successull query, echo each row into CSV and send it directly to output.
	rollback ()
	rollback the current transaction
	search_sql_inject ($p_sql)
	FInd if a SQL Select has a SQL stmt to inject or damage Data When a SELECT SQL string is build, this string could contain a SQL attempt to damage data, so the statement DELETE TRUNCATE ... are forbidden.
	set_db ($db)
	set_encoding ($p_charset)
	set_is_open ($is_open)
	set_ret ($ret)
	set_sql ($sql)
	last SQL stmt executed
	size ($p_ret=null)
	return the number of rows found by the last query, or the number of rows from $p_ret
	start ()
	: start a transaction
	status ()
	get the transaction status : The status can be
	upload ($p_name, $only_oid=false)
	Save one or several documents into the database , it just puts the file in the database and returns the corresponding OID , the mimetype , size ... of the document must be set in the calling function.
	verify ()

Additional Inherited Members
Static Public Member Functions inherited from DatabaseCore
static	escape_string ($p_string)
	wrapper for the function pg_escape_string
static	fetch_all ($ret, $p_mode=PGSQL_ASSOC)
	wrapper for the function pg_fetch_all
static	fetch_array ($ret, $p_indice=0, $p_mode=PGSQL_ASSOC)
	wrapper for the function pg_fetch_array
static	fetch_result ($ret, $p_row=0, $p_col=0)
	wrapper for the function pg_fetch_all
static	fetch_row ($ret, $p_row)
	wrapper for the function pg_fetch_row
static	nb_column ($p_ret)
	Returns the number of columns in a ret.
static	num_row ($ret)
	wrapper for the function pg_num_rows
static	test_me ()
Data Fields inherited from DatabaseCore
	$array
	$sql
	last SQL stmt executed
Protected Attributes inherited from DatabaseCore
	$db
	database connection
	$dbhost
	!< $dbport (int) Database port
	$dbname
	$dbport
	!< $dbname (string) Database name
	$dbuser
	!< $dbhost(string) Database host
	$is_open
	$ret
	return value

Detailed Description

contains the class for connecting to Noalyss

Definition at line 32 of file database.class.php.

Constructor & Destructor Documentation

__construct()

Database::__construct	(		$p_database_id = 0,
			$p_type = 'dos' )

constructor

Parameters

$p_database_id	is the id of the dossier, or the modele following the p_type if = 0 then connect to the repository
$p_type	is 'DOS' (defaut) for dossier or 'MOD'

Definition at line 40 of file database.class.php.

    {
        if (IsNumber($p_database_id) == false || strlen($p_database_id??"") > 10)
            die("-->Dossier invalide [$p_database_id]");
        $noalyss_user = (defined("noalyss_user")) ? noalyss_user : phpcompta_user;
        $password = (defined("noalyss_password")) ? noalyss_password : phpcompta_password;
        $port = (defined("noalyss_psql_port")) ? noalyss_psql_port : phpcompta_psql_port;
        $host = (!defined("noalyss_psql_host")) ? '127.0.0.1' : noalyss_psql_host;
        if (defined("MULTI") && MULTI == "0") {
            $l_dossier = dbname;
        } else {
 
            if ($p_database_id == 0) { /* connect to the repository */
                $l_dossier = sprintf("%saccount_repository", strtolower(domaine));
            } else if ($p_type == 'dos') { /* connect to a folder (dossier) */
                $l_dossier = sprintf("%sdossier%d", strtolower(domaine), $p_database_id);
            } else if ($p_type == 'mod') { /* connect to a template (modele) */
                $l_dossier = sprintf("%smod%d", strtolower(domaine), $p_database_id);
            } else if ($p_type == 'template') {
                $l_dossier = 'template1';
            } else {
                throw new Exception('Connection invalide');
            }
        }
 
        parent::__construct($noalyss_user, $password, $l_dossier, $host, $port);
 
        $this->exec_sql('set search_path to public,pg_catalog;');
        if ($this->exist_schema('comptaproc')) {
            $this->exec_sql('set search_path to public,comptaproc,pg_catalog;');
        }
        $this->exec_sql('set DateStyle to ISO, MDY;');
        $this->ret=true;
        $this->is_open=true;
    }

References $p_type, domaine, DatabaseCore\exec_sql(), and DatabaseCore\exist_schema().

Here is the call graph for this function:

Member Function Documentation

__toString()

Database::__toString

(

)

Parameters

return

Note

See also

Reimplemented from DatabaseCore.

Definition at line 75 of file database.class.php.

                         : string {
        return "DatabaseCore[db=" . var_export($this->db,true)
            . ", ret=" .var_export( $this->ret,true)
            . ", is_open=" . $this->is_open
            . ", sql=" . $this->sql
            . ", array=" . var_export($this->array,true)
            . ",dbname = ".$this->get_dbname()
            . "]";
}

References db, DatabaseCore\get_dbname(), and return.

Here is the call graph for this function:

apply_patch()

Database::apply_patch

(

$p_name

)

loop to apply all the path to a folder or a template Upgrade check if the folder $p_name needs to be upgrade thanks the variable DBVERSION and run all the SQL script named upgradeX.sql from the folder noalyss/include/sql/patch until X equal DBVERSION-1

Parameters

$p_name

database name

Bug in parm_code for FRANCE

Definition at line 109 of file database.class.php.

    {
        if (!$this->exist_table('version')) {
            echo _('Base de donnée vide');
            return;
        }
        $MaxVersion = DBVERSION - 1;
        $succeed = "<span style=\"font-size:18px;color:green\">&#x2713;</span>";
        echo '<ul style="list-type-style:square">';
        for ($i = 4; $i <= $MaxVersion; $i++) {
            $to = $i + 1;
            if ($this->get_version() <= $i) {
                if ($this->get_version() == 97) {
                    if ($this->exist_schema("amortissement")) {
                        $this->exec_sql('ALTER TABLE amortissement.amortissement_histo
                                                        ADD CONSTRAINT internal_fk FOREIGN KEY (jr_internal) REFERENCES jrn (jr_internal)
                                                        ON UPDATE CASCADE ON DELETE SET NULL');
                    }
                }
                echo "<li>Patching " . $p_name .
                    " from the version " . $this->get_version() . " to $to ";
 
               $this->execute_script(NOALYSS_INCLUDE . '/sql/patch/upgrade' . $i . '.sql');
               echo $succeed;
 
                if ( DEBUGNOALYSS == 0)
                    ob_start();
                // specific for version 4
                if ($i == 4) {
                    $sql = "select jrn_def_id from jrn_def ";
                    $Res = $this->exec_sql($sql);
                    $Max = $this->size();
                    for ($seq = 0; $seq < $Max; $seq++) {
                        $row = pg_fetch_array($Res, $seq);
                        $sql = sprintf("create sequence s_jrn_%d", $row['jrn_def_id']);
                        $this->exec_sql($sql);
                    }
                }
                // specific to version 7
                if ($i == 7) {
                    // now we use sequence instead of computing a max
                    //
                    $Res2 = $this->exec_sql('select coalesce(max(jr_grpt_id),1) as l from jrn');
                    $Max2 = pg_num_rows($Res2);
                    if ($Max2 == 1) {
                        $Row = pg_fetch_array($Res2, 0);
                        var_dump($Row);
                        $M = $Row['l'];
                        $this->exec_sql("select setval('s_grpt',$M,true)");
                    }
                }
                // specific to version 17
                if ($i == 17) {
                    $this->execute_script(NOALYSS_INCLUDE . '/sql/patch/upgrade17.sql');
                    $max = $this->get_value('select last_value from s_jnt_fic_att_value');
                    $this->alter_seq('s_jnt_fic_att_value', $max + 1);
                } // version
                // reset sequence in the modele
                //--
                if ($i == 30 && $p_name == "mod") {
                    $a_seq = array('s_jrn', 's_jrn_op', 's_centralized',
                        's_stock_goods', 'c_order', 's_central');
                    foreach ($a_seq as $seq) {
                        $sql = sprintf("select setval('%s',1,false)", $seq);
                        $Res = $this->exec_sql($sql);
                    }
                    $sql = "select jrn_def_id from jrn_def ";
                    $Res = $this->exec_sql($sql);
                    $Max = pg_num_rows($Res);
                    for ($seq = 0; $seq < $Max; $seq++) {
                        $row = pg_fetch_array($Res, $seq);
                        $sql = sprintf("select setval('s_jrn_%d',1,false)", $row['jrn_def_id']);
                        $this->exec_sql($sql);
                    }
                }
                if ($i == 36) {
                    /* check the country and apply the path */
                    $res = $this->exec_sql("select pr_value from parameter where pr_id='MY_COUNTRY'");
                    $country = pg_fetch_result($res, 0, 0);
                    $this->execute_script(NOALYSS_INCLUDE . "/sql/patch/upgrade36." . $country . ".sql");
                    $this->exec_sql('update tmp_pcmn set pcm_type=find_pcm_type(pcm_val)');
                }
                if ($i == 59) {
                    $res = $this->exec_sql("select pr_value from parameter where pr_id='MY_COUNTRY'");
                    $country = pg_fetch_result($res, 0, 0);
                    if ($country == 'BE')
                        $this->exec_sql("insert into parm_code values ('SUPPLIER',440,'Poste par défaut pour les fournisseurs')");
                    if ($country == 'FR')
                        $this->exec_sql("insert into parm_code values ('SUPPLIER',400,'Poste par défaut pour les fournisseurs')");
                }
                if ($i == 61) {
                    $country = $this->get_value("select pr_value from parameter where pr_id='MY_COUNTRY'");
                    $this->execute_script(NOALYSS_INCLUDE . "/sql/patch/upgrade61." . $country . ".sql");
                }
                /**
                 * Bug in parm_code for FRANCE
                 */
                if ($i == 141 ) {
                     $country = $this->get_value("select pr_value from parameter where pr_id='MY_COUNTRY'");
                     $this->execute_script(NOALYSS_INCLUDE . "/sql/patch/upgrade141." . $country . ".sql");
                }
 
                if (DEBUGNOALYSS == 0 )
                    ob_end_clean();
 
            }
        }
        echo '</ul>';
    }

References $country, $i, $Max, $max, $Res, $res, $row, $seq, DatabaseCore\$sql, $succeed, $to, _, DatabaseCore\alter_seq(), DBVERSION, DatabaseCore\exec_sql(), DatabaseCore\execute_script(), DatabaseCore\exist_schema(), DatabaseCore\exist_table(), DatabaseCore\get_value(), get_version(), and size.

Here is the call graph for this function:

format_name()

Database::format_name	(		$p_id,
			$p_type )

return the name of the database with the domain name

Parameters

$p_id	of the folder WITHOUT the domain name
$p_type	dos for folder mod for template

Returns

formatted name

Definition at line 225 of file database.class.php.

    {
        switch ($p_type) {
            case 'dos':
                $sys_name = sprintf("%sdossier%d", strtolower(domaine), $p_id);
                break;
            case 'mod':
                $sys_name = sprintf("%smod%d", strtolower(domaine), $p_id);
                break;
            default:
                echo_error(__FILE__ . " format_name invalid type " . $p_type, __LINE__);
                throw new Exception(__FILE__ . " format_name invalid type " . $p_type . __LINE__);
        }
        return $sys_name;
    }

References $p_id, $p_type, domaine, and echo_error().

Here is the call graph for this function:

get_version()

Database::get_version

(

)

Get version of a database, the content of the table version.

Returns

version number

Definition at line 93 of file database.class.php.

    {
        $Res = $this->get_value("select max(val) from version");
        return $Res;
    }

References $Res, and DatabaseCore\get_value().

Referenced by apply_patch().

Here is the call graph for this function:

---

The documentation for this class was generated from the following file:

• include/class/database.class.php