PostgreSQL Support

[matt]

Hello.

I am a software developer. I think that this project might be useful to me, however, I only use PostgreSQL. PostgreSQL, in my usage of it, functions more as an application framework as opposed to a mere database, so that is my only option. Should I fork nuBuilder2 and replace all of the MySQL PHP calls with PostgreSQL calls and update the syntax, or would you like for me to help with PostgreSQL support in nuBuilder3?

Thanks.

[massiws]

matt,
nuBuilder v.3 will support much more database, using PDO: here and here you can find more informations.

If you want to fork nuBuilder v.2 you should rewrite some files (not so many, in my opinion).

Max

[matt]

Thanks. I saw those as I was doing my research on this project/product. I added PostgreSQL support to SugarCRM over a weekend many years ago; long before their new abstraction layer was in. So I have done this before. While the port was successful, SugarCRM never got around to giving me the ability to contribute my code back to them. The issue was that they required copyright assignment of all contributed code to them so that they can relicense that code in conjunction with their paid non-open source enhancements. I had called and sent e-mails, but one of the persons who I was forwarded to never got back to me. So I prefer to avoid this type of hassle.

I would love to help make PostgreSQL support for version 3 stable by the time that they are ready to do a formal release, but if that development process is closed to me, then I cannot help. Forking version 2 should not take much longer than what I did for SugarCRM in the past, but I prefer not to maintain a fork all by myself. I hope to have one of these two versions stable before the end of the year. Since my work inherently involves testing a lot, I could potentially help speed up the testing process too. I plan on adding PostgreSQL specific features, such as: Full text search, bitemporal tables, foreign data wrappers, and database level timezone awareness.

[massiws]

matt, your questions need answer from nuBuilder team.

[admin]

matt,

The best way for me to show you what will need to change for postgress (in version 3) is by showing you the only database function calls used, they will be in a file called dbfunctions.php..

Code: Select all

<?php require_once('nucommon.php'); 

mb_internal_encoding('UTF-8');

$DBHost                      = $_SESSION['DBHost'];
$DBName                      = $_SESSION['DBName'];
$DBUser                      = $_SESSION['DBUser'];
$DBPassword                  = $_SESSION['DBPassword'];

$nuDB = new PDO("mysql:host=$DBHost;dbname=$DBName;charset=utf8", $DBUser, $DBPassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$nuDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$GLOBALS['nuSetup']          = db_setup();

function db_setup(){
    
	static $setup;
	
	//check if setup has already be called
    if (empty($setup)) {
		//get setup info from db
		$rs 	= nuRunQuery("Select * From zzsys_setup");
		$setup 	= db_fetch_object($rs);
	}
	
	//setup garbage collect timeouts
	$gcLifetime  = 60 * $setup->set_time_out_minutes;
	ini_set("session.gc_maxlifetime", $gcLifetime);
		
	//return result		
    return $setup;
}



function nuRunQuery($s, $a = array(), $isInsert = false){

	global $DBHost;
	global $DBName;
	global $DBUser;
	global $DBPassword;
	global $nuDB;

	if($s == ''){
		$a           = array();
		$a[0]        = $DBHost;
		$a[1]        = $DBName;
		$a[2]        = $DBUser;
		$a[3]        = $DBPassword;
		return $a;
	}

	$object = $nuDB->prepare($s);

	try {
		$object->execute($a);
	}catch(PDOException $ex){
	
		$user        = nuV('nu_user_name');
		$message     = $ex->getMessage();
		$array       = debug_backtrace();
                $trace       = '';
                
                for($i = 0 ; $i < count($array) ; $i ++){
                    $trace  .= $array[$i]['file'] . ' - line ' . $array[$i]['line'] . ' (' . $array[$i]['function'] . ")\n\n";
                }

		$debug       = "
===USER==========

$user

===PDO MESSAGE=== 

$message

===SQL=========== 

$s

===BACK TRACE====

$trace

";
                
	nuDebug($debug);
        $id                      = $nuDB->lastInsertId();

	if(nuV('nu_user_name') == 'globeadmin'){
            $GLOBALS['ERRORS'][] = $debug;
        }else{
            $GLOBALS['ERRORS'][] = "There has been an error on this page.\n Please contact your system administrator and quote the following number: $id ";
        }
        
        return -1;
		
	}

        if($isInsert){
            
            return $nuDB->lastInsertId();
            
        }else{
            
            return $object;
        
        }
	
}


function db_is_auto_id($t, $p){

	$t       = nuRunQuery("SHOW COLUMNS FROM $t WHERE `Field` = '$p'");   //-- mysql's way of checking if its an auto-incrementing id primary key
	$r       = db_fetch_object($t);
	return $r->Extra == 'auto_increment';

}


function db_fetch_array($o){
	return $o->fetch(PDO::FETCH_BOTH);
}

function db_fetch_object($o){
	return $o->fetch(PDO::FETCH_OBJ);
}
	
function db_fetch_row($o){
	return $o->fetch(PDO::FETCH_NUM);
}

function db_field_array($o){
	
	$r       = db_fetch_object($o);
	$a       = array();
	
	foreach ($r as $key => $value){
		$a[] = $key;
	}
}


function db_columns($n){
    
    $a       = array();
    $d       = $_SESSION['DBName'];
    $s       = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$d' AND TABLE_NAME = '$n'";
    $t       = nuRunQuery($s);

    while($r = db_fetch_object($t)){
        $a[] = $r->COLUMN_NAME;
    }
    
    return $a;
    
}

function db_num_rows($o) {
	return $o->rowCount();
}

?>

There are less than 10 and probably only 1 or 2 spots that will need changing with

$nuDB = new PDO("mysql:host=$DBHost;dbname=$DBName;charset=utf8", $DBUser, $DBPassword, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

being the main one.

I hope this helps.

steven

[Gohar]

This is very nice post thanks a lot..!!

[admin]

.