// 7.11 15:37

/* create assert function
  example : assert( obj === null, 'object was not null!' );
  error message appears in javascript console, if any.
  credit to: Ayman Hourieh http://aymanh.com/
*/
function AssertException(message) { this.message = message; }
AssertException.prototype.toString = function () {
  return 'AssertException: ' + this.message;
};
function assert(exp, message) {
  if (!exp) {
    throw new AssertException(message);
  }
}

// function to handle error result of query submit
function errback(err,msg) {
	//alert(err.toString());
	this.rdbAdmin.showErrorMessage('<pre>'+err.toString()+': '+msg+'</pre>');
}

// dict of aliases of type names
//
var alias = { 'int2' : 'smallint',
			  'int4' : 'integer',
			  'int8' : 'bigint' };

// column name quoter
//
function quoteIdentifier(name)
{
	var nmparts = name.split('.');
	var outparts = [];
	for (var np in nmparts) {
		if (nmparts.hasOwnProperty(np)) {
			outparts.push('"'+nmparts[np].replace(/"/,'""')+'"');
		}
	}
	return outparts.join('.');
}

// split table name
//
function splitTableName(name)
{
	var sNtN, schemaName, tableName;
	if (name.indexOf('.')>=0) {
		sNtN = name.split('.');
		return [sNtN[0], sNtN[1]];
	}
	else {
		return ['public', name];
	}
}


// returns a value from Python DB API datatypes list
//
function apiType(colType)
{
	//'NONE','STRING','NUMBER','DATETIME','ROWID','BINARY','DATE','TIME'.
	var numtypes = ['int','bigint','smallint','integer','serial','bigserial',
					'real','float','double','decimal','double','decimal',
					'number','numeric'];
	var strtypes = ['text','character','char','varchar','character varying'];
	var datetimetypes = ['timestamp','timestamp with time zone',
						 'timestamp without time zone'];
	var datetypes = ['date'];
	var timetypes = ['time','time with time zone','time without time zone'];
	var binarytypes = ['bytea'];
	if ($.inArray(colType,numtypes)>-1) {
		return 'NUMBER';
	}
	if ($.inArray(colType,strtypes)>-1) {
		return 'STRING';
	}
	if ($.inArray(colType,datetimetypes)>-1) {
		return 'DATETIME'; 
	}
	if ($.inArray(colType,timetypes)>-1) {
		return 'TIME'; 
	}
	if ($.inArray(colType,datetypes)>-1) {
		return 'DATE'; 
	}
	if ($.inArray(colType,binarytypes)>-1) {
		return 'BINARY';
	}
	if (colType === 'boolean') {
		return 'STRING';
	}
	if (colType === 'ARRAY') {
		return 'STRING';
	}
	//alert('unrecognized coltype '+colType);
	return 'STRING';
}


// DatabaseManager that uses an SQLEngine object for database access
//
function DatabaseManager(sqlEngine)
{
	this.sqlEngine = sqlEngine;
	
	this.getTableNames = function(display) {
		var query = "SELECT tablename, schemaname FROM pg_tables " +
					"WHERE tablename !~* 'pg_*' AND tablename !~* 'sql_*'";
		this.sqlEngine.queryRows( {'callback' : display,
				                   'q' : query,
								   'errback' : errback });
	};
	
	this.getViewNames = function(display) {
		var query = "SELECT viewname, schemaname FROM pg_views " +
					"WHERE schemaname NOT IN('information_schema', 'pg_catalog');";
		this.sqlEngine.queryRows( {	'q' : query,
									'callback' : display,
									'errback' : errback  });
	};
	
	this.getSchemaNames = function(display) {
		var query = "SELECT nspname FROM pg_namespace " +
					"WHERE nspowner > 10 ";
		this.sqlEngine.queryRows( {'q' : query,
								   'callback' : display,
				                   'errback' : errback });
	};

	this.getFunctionNames = function(display) {
		var query = 'SELECT * FROM pg_proc ' +
					'WHERE proowner = ( ' +
					'    SELECT usesysid FROM "pg_user" ' +
					'      WHERE usename = (SELECT CURRENT_USER) );';
		function cBack(rows) {
			var result = [], nm;
			for (var i in rows) {
				if ( rows.hasOwnProperty(i) ) {
					result.push(rows[i][0]);
				}
			}
			display(result);
		}
		this.sqlEngine.queryRows( {'q' : query,
								   'callback' : cBack,
				                   'errback' : errback  });
	};

	this.getDatabaseSchema = function(display)
	{
		var $this = this;
		var query =
			"SELECT c.oid, s.nspname||'.'||c.relname, " +
			"       a.attnum, a.attname, t.typname, " +
			"       pg_get_expr(d.adbin,d.oid,true) AS deflt, " +
			"       (not a.attnotnull)::boolean AS is_nullable, " +
			"		information_schema._pg_char_max_length( " +
			"				 information_schema._pg_truetypid(a.*,t.*), " +
			"				 information_schema._pg_truetypmod(a.*,t.*) " +
			"			)::information_schema.cardinal_number AS character_max_length, " +
			"		information_schema._pg_numeric_precision(  " +
			"				 information_schema._pg_truetypid(a.*,t.*), " +
			"				 information_schema._pg_truetypmod(a.*,t.*) " +
			"			)::information_schema.cardinal_number AS numeric_precision, " +
			"		information_schema._pg_numeric_scale(  " +
			"				 information_schema._pg_truetypid(a.*,t.*), " +
			"				 information_schema._pg_truetypmod(a.*,t.*) " +
			"			)::information_schema.cardinal_number AS numeric_scale, " +
			"		a.attndims,  " +
			"       t.typelem    " +
			"  FROM pg_attribute a  " +
			"          JOIN pg_type t ON a.atttypid = t.oid " +
			"          JOIN pg_class c ON a.attrelid = c.oid " +
			"          JOIN pg_namespace s ON c.relnamespace = s.oid " +
			"          LEFT JOIN pg_attrdef d    " +
			"                   ON c.oid = d.adrelid AND a.attnum = d.adnum " +
			" WHERE s.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast') " +
			"   AND relkind IN ('r', 'v') " +
			"   AND position('_' in c.relname) <> 1 " +
			"   AND not a.attisdropped " +
			"   AND a.attnum >= 0  " +
			" ORDER BY 2, 4 "+
			" OFFSET %s ";
        var displayer = display;
		function processDatabaseSchema(columns) {
			var table, tableName, column, curTableName = "";
			var result = [];
			for (var i in columns) {
				if (columns.hasOwnProperty(i)) {
					tableName = columns[i][1];
					if (tableName !== curTableName) {
						table = new SQLTable($this.sqlEngine);
						table.nameTable(tableName);
						result.push(table);
						curTableName = tableName;
					}
					column = new SQLColumn();
					column.loadData(table, columns[i]);
					table.addColumn(column);
				}
			}
			displayer(result);
		}
		var SE = this;
		function getMultiple(callback, query, offset) {
			var q = query.replace('%s',offset.toString());
			var results = [];
			function getMore(res) {
				results = results.concat(res);
				if (res.length >= 100) {
					offset = offset+100;
					q = query.replace('%s',offset.toString());
					//SE.performQuery(getMore,'',q);
					SE.sqlEngine.queryRows( {'callback' : getMore,
										     'q' : q });
				}
				else {
					callback(results);
				}
			}
			//SE.performQuery(getMore,'',q);
			SE.sqlEngine.queryRows( {'callback' : getMore,
					                 'q' : q });
		}
		getMultiple(processDatabaseSchema, query, 0);
	};

	this.getView = function(schema,view,callback,errback)
	{
		function cback(json) {
			var result = [];
			for (var i in json.records.rows) {
				if ( json.records.rows.hasOwnProperty(i)) {
					result.push(json.records.rows[i]);
				}
			}
			callback(result);
		}
		var query = "SELECT * FROM pg_views " +
				    "WHERE schemaname = %s " +
				    "  AND viewname = %s ;";
		var args = [schema,view];
		this.sqlEngine.query( {'callback' : cback,
							   'errback' : errback,
				               'q' : query,
							   'args' : args });
	};
	
	/*
		gets array *element* type rows from pg_types, where the table column
		  query retrieved the *array* columns themselves, and we still
		  need the rows that describe the element type.
		a field 'int[]', for example, will get an _int type record with the
		  getTableDetails query below, and a non-zero typelem field.  This
		  query will get the int type record referenced by the typelem
		  field value.
	*/
	this.getArrayColumns = function(table, callback, errback)
	{
		/* callback iterates over list of array elem types,
		    matches up each rec with column in table, and copies
		    data over.
		    @param table : table object
		    @param callback : function to call with data
		    @param errback : function to call in case of error
		*/
		function cback(json) {
			var rec, rownum, typnam, charmax, numprec, numscale, col;
			for (var i in json.records.rows) {
				// iterate over retrieved records
				if (json.records.rows.hasOwnProperty(i)) {
					rec = json.records.rows[i];
					for (var j in table.fields) {
						// iterate over table columns
						if (table.fields.hasOwnProperty(j)) {
							col = table.fields[j];
							if (col.pos === rec[0]) {
								// if rec matches this column, copy data
								col.dataType = alias[rec[1]] || rec[1];
								col.maxLength = rec[3];
								col.precision = rec[5];
								col.scale = rec[6];
							}
						}
					}
				}
			}
			callback(table);				
		}
		var arQry =
			"SELECT a.attnum, at.typname, at.typelem, " +
			"  information_schema._pg_char_max_length(k.typelem,a.atttypmod " +
			"    )::information_schema.cardinal_number AS character_max_length, " + 
			"  information_schema._pg_numeric_precision(k.typelem,a.atttypmod " +
            "    )::information_schema.cardinal_number AS numeric_precision, " +
			"  information_schema._pg_numeric_scale(k.typelem,a.atttypmod " +
            "    )::information_schema.cardinal_number AS numeric_scale " +
			" FROM pg_attribute a  " +
			"	        JOIN pg_type k ON a.atttypid = k.oid  " +
			"			JOIN pg_type at ON k.typelem = at.oid " +
			"   WHERE a.attrelid =  " + table.tableOID;
		this.sqlEngine.query( {'q' : arQry,
							   'callback' : cback,
							   'errback' : errback });
	};
	
	this.getTableDetails = function(schemaName, tableName, callback, errback)
	{
		var $this = this;
		var table = new SQLTable(this.sqlEngine);
		table.nameTable(schemaName,tableName);
		function cback(json) {
			var column,
			    includesArray = false;
			for (var i in json.records.rows) {
				if (json.records.rows.hasOwnProperty(i)) {
					column = new SQLColumn();
					column.loadData(table, json.records.rows[i]);
					if (column.typelem > 0) {
						includesArray = true;
					}
					table.addColumn(column);
				}
			}
			if (includesArray) {
				$this.getArrayColumns(table, callback, errback);
			}
			else {
				callback(table);				
			}
		}
		var query =
			"SELECT c.oid, c.relname, a.attnum, a.attname, t.typname, " +
			"       pg_get_expr(d.adbin,d.oid,true) AS deflt, " +
			"       not a.attnotnull AS is_nullable, " +
			"		information_schema._pg_char_max_length( " +
			"				 information_schema._pg_truetypid(a.*,t.*), " +
			"				 information_schema._pg_truetypmod(a.*,t.*) " +
			"			   )::information_schema.cardinal_number AS character_max_length, " +
			"		information_schema._pg_numeric_precision(  " +
			"				 information_schema._pg_truetypid(a.*,t.*), " +
			"				 information_schema._pg_truetypmod(a.*,t.*) " +
			"			   )::information_schema.cardinal_number AS numeric_precision, " +
			"		information_schema._pg_numeric_scale(  " +
			"				 information_schema._pg_truetypid(a.*,t.*), " +
			"				 information_schema._pg_truetypmod(a.*,t.*) " +
			"			   )::information_schema.cardinal_number AS numeric_scale, " +
			"		a.attndims,  " +
			"       t.typelem    " +
			"  FROM pg_attribute a  " +
			"          JOIN pg_type t ON a.atttypid = t.oid " +
			"          JOIN pg_class c ON a.attrelid = c.oid " +
			"          JOIN pg_namespace s ON c.relnamespace = s.oid " +
			"          LEFT JOIN pg_attrdef d    " +
			"                   ON c.oid = d.adrelid AND a.attnum = d.adnum " +
			" WHERE c.relname = '" + tableName + "' " +
			"   AND s.nspname = '" + schemaName + "' " +
			"   AND not a.attisdropped " +
			"   AND a.attnum >= 0  " +
			"ORDER BY a.attnum ";
		this.sqlEngine.query( {'q' : query,
							   'callback' : cback,
							   'errback' : errback });
	};
	
	this.getIndexes = function(schemaName, tableName, callback, errback)
	{
		function cback(json) {
			var result = [];
			if (json.records.rows) {
				for (var i=0;i<json.records.rows.length;i+=1) {
					result.push(json.records.rows[i]);
				}
			}
			callback(result);
		}
		// select indexes and their column names
		var query1 =
		" SELECT pi.indnatts AS attr_num, pi.indisunique AS is_unique, " +
		"   pi.indisprimary AS is_primary," + 
		"   pc.relname AS index_name, pctn.relname AS table_name, " +
		"   pg_catalog.pg_get_indexdef(pi.indexrelid, 0, true) AS index_def, " +
		"   ARRAY(SELECT DISTINCT a.attname " +
		"         FROM pg_index c " +
		"               JOIN pg_class t ON c.indexrelid  = t.oid " +
		"                   LEFT JOIN pg_attribute a ON a.attrelid = t.oid " +
		"         WHERE t.relname = pc.relname )  " +
		"  FROM pg_index AS pi " +
		"   INNER JOIN pg_class AS pc ON pi.indexrelid=pc.oid " +
		"     INNER JOIN pg_class AS pctn ON pi.indrelid=pctn.oid " +
		"     JOIN pg_namespace ON pctn.relnamespace = pg_namespace.oid " +
		" WHERE pctn.relname = '" + tableName + "'" +
		"   AND pg_namespace.nspname = '" + schemaName + "'";
		
		this.sqlEngine.query({ 'q' : query1,
							   'callback' : cback,
							   'errback' : errback  });
	};
		
	this.getConstraints = function(schemaName, tableName, callback, errback)
	{
		function cback(json) {
			var result = [];
			if (json.records && json.records.rows) {
				for (var i=0;i<json.records.rows.length;i+=1) {
					result.push(json.records.rows[i]);
				}
			}
			callback(result);
		}
		// select indexes and their column names
		var query =
		" SELECT pc.conname, pc.contype, pc.conrelid, pc.confrelid, " +
		"   pc.confupdtype, pc.confdeltype, pc.confmatchtype, " +
		"   pc.conkey, pc.confkey, pc.conpfeqop, pc.conppeqop, " +
		"   pc.conffeqop, pg_get_constraintdef(pc.oid) as consrc, " +
		"   fc.relname as fortable, fs.nspname as forschema " +
		"  FROM pg_constraint AS pc " +
		"          JOIN pg_class c ON pc.conrelid = c.oid " +
		"          JOIN pg_namespace s ON c.relnamespace = s.oid " +
		"		   LEFT JOIN pg_class fc ON pc.confrelid = fc.oid " +
		"		   LEFT JOIN pg_namespace fs ON fc.relnamespace = fs.oid " +
		" WHERE c.relname = '" + tableName + "' " +
		"   AND s.nspname = '" + schemaName + "' ";
		this.sqlEngine.query({ 'q' : query,
							   'callback' : cback,
							   'errback' : errback  });
	};
		
	this.getFunctionsList = function(fname,callback,errback)
	{
		var query = "SELECT p.proname, n.nspname, p.proretset, p.provolatile, " +
		            "       p.pronargs, p.prorettype, p.proallargtypes, " +
					"       p.proargmodes, p.proargnames, p.prosrc, p.probin" +
		            "  FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid " +
		            " WHERE proowner = " +
					"    (SELECT usesysid FROM \"pg_user\" " +
					"      WHERE usename = (SELECT CURRENT_USER) ) " +
					"   AND proname='~fname~';".replace('~fname~',fname);
		/* proname, pronamespace, proowner, prolang, procost,
		   prorows, provariadic, proisagg, proiswindow, prosecdef,
		   proisstrict, proretset, provolatile, pronargs, pronargdefaults,
		   prorettype, proargtypes, proallargtypes, proargmodes, proargnames,
		   proargdefaults, prosrc, brobin, proconfig, proacl
		*/
		function cback(json) {
			var result = [];
			if (json.row_count[0] > 0) {
				for (var i in json.records.rows) {
					if (json.records.rows.hasOwnProperty(i)) {
						result.push(json.records.rows[i]);
					}
				}
			}
			callback(result);
		}
		this.sqlEngine.query( { 'q' : query,
							    'callback' : cback,
								'errback' : errback });
	};
        
	this.getTableOID = function(schemaName, tableName, callback, errback)
	{
		// get table id
		var query = "SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c " +
					"LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " +
					"WHERE c.relname ~ '^("+tableName+")$' "+
					" AND n.nspname ~ '^("+schemaName+")$' ORDER BY 2, 3;";
					//AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3;";
		function withOID(toid) {
			var table_id = toid.records.rows[0][0];
			callback(table_id);
		}
		this.sqlEngine.query( { 'q' : query,
							    'callback' : withOID,
								'errback' : errback    });
		//tablePanel = ....
	};

	this.getColumnComments = function(table_oid, columns, callback, errback)
	{
		var queryparts = [];
		for(var i=0;i<columns.length;i+=1) {
			queryparts.push("col_description("+table_oid+","+columns[i].pos+")");
		}
		var query = "SELECT " + queryparts.join(',') + ";";
		function cback(json) {
			var comments = [];
			for(var i in json.records.rows[0]) {
				if (json.records.rows.hasOwnProperty(i)) {
					comments.push(json.records.rows[0][i]);
				}
			}
			callback(comments);
		}
		this.sqlEngine.query( { 'q' : query,
								'callback' : cback,
								'errback' : errback });	
	};
	
	this.getTableComment = function(tableOID,callback,errback)
	{
		var query = "SELECT obj_description("+tableOID+",'pg_class');";
		function extractTableComment(json) {
			if (json.records.rows[0][0]) {
				callback(json.records.rows[0][0]);
			}
			else {
				callback("");
			}
		}
		this.sqlEngine.query( { 'q' : query,
							    'callback' : extractTableComment,
								'errback' : errback  });
	};
	
	this.renameTable = function(oldname, newname, callback, errback)
	{
		var query = 'ALTER TABLE '+quoteIdentifier(oldname)+
					' RENAME TO '+quoteIdentifier(newname)+';';
		this.sqlEngine.query( { 'q' : query,
							    'callback' : callback,
								'errback' : errback   });
	};
	
	this.dropTable = function(tableName, callback, errback)
	{
		var query = 'DROP TABLE '+quoteIdentifier(tableName)+';';
		this.sqlEngine.query( { 'q' : query,
							    'callback' : callback,
								'errback' : errback   });
	};
	
	this.truncate = function(tableName, callback, errback)
	{
		var query = 'TRUNCATE TABLE '+quoteIdentifier(tableName)+';';
		this.sqlEngine.query( { 'q' : query,
							    'callback' : callback,
								'errback' : errback   });
	};

	this.getTypeByOID = function(oid,callback,errback)
	{
		var query = [];
		for (var i in oid) {
			if (oid.hasOwnProperty(i)) {
				query.push("(SELECT ROW(oid, typname) FROM pg_type WHERE oid=" + oid[i]+') as c'+i);
			}
		}
		var queryTxt = 'SELECT ' + query.join(", \n");
		function cback(json) {
			var result = [];
			if (json.row_count[0] > 0) {
				for (var j in json.records.rows) {
					if ( json.records.rows.hasOwnProperty(j) ) {
						result.push(json.records.rows[j]);
					}
				}
			}
			callback(result);
		}
		this.sqlEngine.query( { 'q' : queryTxt,
							    'callback' : cback,
								'errback' : errback } );
	};

}


// All these are to be integrated into DatabaseManager above.
//
function SQLEngineXXX()
{
	
	this.getTriggers = function(tableName)
	{
		var query = "SELECT trigger_name,event_manipulation,action_orientation,condition_timing " + 
					"FROM information_schema.triggers WHERE event_object_table='" + tableName + 
					"' AND trigger_schema NOT IN ('pg_catalog', 'information_schema'); ";
		var json_result = this.performQuery(query);
		if (json_result.row_count[0] === 0) {
			return [];
		}
		return json_result.records.rows;
	};
	
	this.createTrigger = function(q)
	{
		var result = this.performQuery(q);
		return result; 
	};
	
}

// object to hold meta-data for table
//   contains SQLColumn elements
//
function SQLTable(sqlEngine)
{
	if ( !sqlEngine ) {
		alert('SQLTable created without slqEngine ref');
	}
	this.sqlEngine = sqlEngine;
	this.tableName = "";
	this.schemaName = "";
	this.comment = "";
	this.fields = [];

	this.nameTable = function(arg0,arg1)
	{
		if (arg1) {
			this.schemaName = arg0;
			this.tableName = arg1;
		}
		else {
			var sNtN = splitTableName(arg0);
			this.schemaName = sNtN[0];
			this.tableName = sNtN[1];
		}
	};

	this.qualTableName = function()
	{
		if (this.schemaName === "public") {
			return this.tableName;
		}
		else {
			return this.schemaName+'.'+this.tableName;
		}
	};

	this.addColumn = function(column)
	{
		this.fields.push(column);
	};
	
	this.addComment = function(comment)
	{
		this.comment = comment;
	};

	this.toStr = function()
	{
		var result = this.tableName + "\n\n";
		for (var i in this.fields) {
			if (this.fields.hasOwnProperty(i)) {
				var col = this.fields[i];
				result = result + col.columnName + " " + col.dataType + "\n";
			}
		}
		return result;
	};
	
}


// object to hold meta-data about Column
//
function SQLColumn()
{
	this.table = null;
	this.references = null;
	this.pos = 0;
	this.columnName = "";
	this.dataType = "";
	this.columnDefault = "";
	this.isNullable = "";
	this.maxLength = "";
	this.precision = "";
	this.numDims = "";
	this.typelem = '';
	this.comment = "";
	
	this.deleted = false;
	this.added = false;

	this.loadData = function(tab, data)
	{
		this.table = tab;
		this.table.tableOID = data[0];
		this.pos = data[2];
		this.columnName = data[3];
		this.dataType = alias[data[4]] || data[4];
		this.columnDefault = data[5];
		this.isNullable = data[6]; // ? 'YES' : 'NO';
		this.maxLength = data[7];
		this.precision = data[8];
		this.scale = data[9];
		this.numDims = data[10];  // 0 for nonarrays
		this.typelem = data[11];  // for array types, scalar type oid
		if (data[12]) {
			this.comment = data[12];
		}
	};
	
	this.sizeString = function() {
		var size = this.maxLength;
		if (this.dataType.toLowerCase() in {'numeric':1,'decimal':2}) {
			size = this.precision+','+this.scale;
		}
		return size;
	};

}