Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

PostgreSQL Support

Locked
matt
Posts: 2
Joined: Sat Oct 05, 2013 3:53 am

PostgreSQL Support

Unread post by 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
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: PostgreSQL Support

Unread post by 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
Posts: 2
Joined: Sat Oct 05, 2013 3:53 am

Re: PostgreSQL Support

Unread post by 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
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: PostgreSQL Support

Unread post by massiws »

matt, your questions need answer from nuBuilder team.
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: PostgreSQL Support

Unread post by 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
Posts: 1
Joined: Thu Dec 04, 2014 7:59 am

Re: PostgreSQL Support

Unread post by Gohar »

This is very nice post thanks a lot..!!
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: PostgreSQL Support

Unread post by admin »

.
Locked