database.php

Go to the documentation of this file.

<?php
class Database {
    private $USERINFOID = 0;            
    private $db;                        
    private $connected = FALSE;         
    private $dberrormsg;                
    function __construct() {
        /* try to connect to the database using defined parameters */
        $this->db = @mysql_connect(DB_HOST, DB_USER, DB_PWD);

        /* select database and save connection status */
        $this->connected = ($this->db && @mysql_select_db(DB_NAME));

        /* if not connected, save error message */
        if (!$this->connected) { $this->dberrormsg = mysql_error(); }
    }

    private function createExtensionTable($id_type) {
        /* get the field list */
        $sql =  "SELECT c.field_name AS field_name, f.type_specs AS type_specs " .
                "FROM contenttype_defs AS c " .
                "LEFT JOIN fieldtypes AS f ON c.field_type = f.id " .
                "WHERE c.id = " . $id_type . " " .
                "ORDER BY c.id_field";
        $res = mysql_query($sql);

        if ($res === FALSE) { return FALSE; }

        $sql = "";
        while ($line = mysql_fetch_assoc($res)) {
            $sql .= "`" . $line['field_name'] . "` " . $line['type_specs'] . ", ";
        }
        mysql_free_result($res);

        $sql =  "CREATE TABLE IF NOT EXISTS `_cnt_" . $id_type .
                "` (`id` bigint unsigned NOT NULL, " . $sql .
                "CONSTRAINT fk_cnt_" . $id_type . " FOREIGN KEY (`id`) REFERENCES content (`id`) ON DELETE CASCADE) " .
                "TYPE=InnoDB DEFAULT CHARSET=utf8";
        return mysql_query($sql);   
    }

    private function getExtendedFieldList($id_type) {
        $sql =  "SELECT field_name FROM contenttype_defs " .
                "WHERE id = " . $id_type . " " .
                "ORDER BY id_field";
        $res = mysql_query($sql);

        if ($res === FALSE) { return ""; }      // not critical, go on

        while ($line = mysql_fetch_assoc($res)) {
            $fieldlist .= ", cnt." . $line['field_name'] . " AS " . $line['field_name'];
        }
        mysql_free_result($res);
        return $fieldlist;      
    }

    private function createContentView($id_type) {
        $viewname = (($id_type > 0) ? "_cntview_" . $id_type : "_usrview");
        $sql =  "CREATE OR REPLACE VIEW `" . $viewname . "` AS " .
                "SELECT c.id AS id, " .
                "c.id_area AS id_area, " .
                "c.id_parent AS id_parent, " .
                "c.id_type AS id_type, " .
                "c.id_owner AS id_owner, " .
                "u.userid as userid, " .
                "c.visible AS visible, " .
                "c.position AS position, " .
                "c.readperm AS readperm, " .
                "c.writeperm AS writeperm, " .
                "c.group_readperm AS group_readperm, " .
                "c.group_writeperm AS group_writeperm, " .
                "c.counter AS counter, " .
                "c.date_created AS date_created, " .
                "c.date_lastmodified AS date_lastmodified, " .
                "c.date_expiration AS date_expiration, " .
                "((date_expiration > 0) AND (UNIX_TIMESTAMP(NOW()) > date_expiration)) AS expired" .
                $this->getExtendedFieldList($id_type) .
                " FROM content AS c " .
                "JOIN _cnt_" . $id_type . " AS cnt ON c.id = cnt.id " .
                "LEFT JOIN users AS u ON c.id_owner = u.id";
        return mysql_query($sql);
    }

    private function createUserInfoView() {
        $sql =  "CREATE OR REPLACE VIEW `_usrview` AS " .
                "SELECT u.id AS id, " .
                "u.id_area AS id_area, " .
                "u.pin AS pin, " .
                "u.userid AS userid, " .
                "u.pwd AS pwd, " .
                "u.email AS email, " .
                "u.admin AS admin, " .
                "u.enabled AS enabled, " .
                "u.groups AS groups, " .
                "u.datecreated AS datecreated" .
                $this->getExtendedFieldList($this->USERINFOID) .
                " FROM users AS u " .
                "LEFT JOIN _cnt_" . $this->USERINFOID . " AS cnt " .
                "ON u.id = cnt.id";
        return mysql_query($sql);
    }

    public function disconnect() {
        @mysql_close($this->db);
        $this->connected = FALSE;
    }

    public function isConnected() {
        return $this->connected;
    }

    public function errorMsg() {
        return $this->dberrormsg;
    }

    public function checkTables() {
        mysql_query("BEGIN");
        $ok = (($res = mysql_query("SELECT id FROM contenttypes")) !== FALSE);
        while ($ok && ($line = mysql_fetch_assoc($res))) {
            if ($ok = $this->createExtensionTable($line['id'])) {
                if ($line['id'] > 0) {
                    $ok = $this->createContentView($line['id']);
                } else {
                    $ok = $this->createUserInfoView();
                }
            }
        }
        if ($ok) {
            mysql_query("COMMIT");
        } else {
            mysql_query("ROLLBACK");
        }
        mysql_free_result($res);
        return $ok;
    }
}
?>

---