log = Logger::getLogger(__CLASS__);
if(class_exists("Predis\Client")){
try {
Predis\Autoloader::register();
$this->cache = new Predis\Client(array(
"scheme" => $redis_scheme,
"host" => $redis_host,
"port" => $redis_port,
"persistent" => 1,
//todo changed this from 0 to -1 to disable timeouts
"read_write_timeout" => -1));
$this->cache->connect();
$this->log->info("redis host: ".$redis_host);
$this->log->info("redis port: ".$redis_port);
} catch(Exception $e) {
$this->log->error("Error connecting to Redis server: ".$e->getMessage());
$this->cache = null;
}
} else {
$this->log->info("Class Predis does not exist\n");
}
}
// initialises the arrays containing the warehouse schema, via an xml that describes them
//TODO: insert checks on whether the data in the xml are correct (get schema from db)
function loadSchema($file = null) {
if(file_exists($file)) {
$json = file_get_contents($file);
//$this->log->info('schema of database: '.json_encode((array) simplexml_load_string($json)));
$this->schema = json_decode(json_encode((array) simplexml_load_string($json)),1);
}
else {
echo "loadSchema: could not find the xml";
$this->log->error('loadSchema: could not find the xml');
return -1;
}
foreach ($this->schema['table'] as $table) {
switch ($table['type']) {
case 'fact': array_push($this->facts,$table);
break;
//case 'm': array_push($this->metadata,$table);
// break;
default: array_push($this->dimensions,$table);
break;
}
}
$this->log->info("loaded schema");
}
function doConnect($file) {
try {
switch($GLOBALS['type']){
case 'postgres':
$this->db = new PDO('pgsql:host='.$GLOBALS['host'].';port=5432;dbname='.$GLOBALS['db_name'].';user='.$GLOBALS['username'].';password='.$GLOBALS['password']);
break;
case 'sqlite':
$this->db = new PDO('sqlite:'.$GLOBALS['head'].$GLOBALS['db_name']);
break;
default:
echo "not known database type\n";
break;
}
} catch(Exception $e){
echo "oops..".$e->getMessage()."\n";
$this->log->fatal($e->getMessage());
return false;
}
$this->schema_file = $file;
$this->loadSchema($file);
}
function doDisconnect($save = false) {
if($save) {
$fp = fopen($this->schema_file,"w");
$data = "\n".toxml($this->schema,"schema",1)."\n\n";
fwrite($fp,$data);
fclose($fp);
}
$this->db = null;
}
function beginTrans() {
$this->db->beginTransaction();
}
function doCommit() {
$this->db->commit();
}
//TODO here predis cache is checked and the query is submitted to the DB
function doQuery($query, $fetchMode=PDO::FETCH_BOTH,$persistent){
if($this->cache != null){
//TODO md5($query) -> REDIS hash set keys
$myKey = md5($query);
$this->log->debug("Searching for key: ".$myKey);
if($this->cache->exists($myKey)) {
$results = $this->cache->hget($myKey, "results");
return json_decode($results, true);
} else {
$this->log->debug("not found in cache");
$results = $this->doQueryNoCache($query, $fetchMode);
$this->log->info("adding in cache. key: ".$myKey);
if($persistent==null||$persistent=='1')
{$persistent = "true";}
if(($this->cache->hmset($myKey, array("query" => $query, "results" => json_encode($results), "persistent" => $persistent, "fetchMode" => $fetchMode))) == false) {
$this->log->info("adding key ".$myKey." in cache failed."); //predis
}
else {
$this->cache->save();
}
return $results;
}
} else {
return $this->doQueryNoCache($query, $fetchMode);
}
}
function doQueryNoCache($query, $fetchMode=PDO::FETCH_BOTH) {
$stmt = $this->db->query($query);
if(!$stmt){
$arr = $this->db->errorInfo();
$this->log->error($arr[2]);
return null;
}
$this->log->debug("***************FETCH MODE : ".$fetchMode);
return $stmt->fetchAll($fetchMode);
}
function doPrepare($query){
$stmt = $this->db->prepare($query);
if(!$stmt){
$arr = $this->db->errorInfo();
$this->log->error($arr[0]." ".$arr[1]." ".$arr[2]);
return false;
}
return $stmt;
}
function doExecute($stmt){
if(!$stmt->execute()){
$arr = $stmt->errorInfo();
$this->log->error($arr[2]);
return false;
}
return $stmt;
}
//-------------------------------------------------------------------------------------\\
//-------------------------------------------------------------------------------------\\
//------------------------------CHECKS, GETTERS, SETTERS-------------------------------\\
//-------------------------------------------------------------------------------------\\
//-------------------------------------------------------------------------------------\\
public function printSchema() {
print_r($this->schema);
}
public function getSchema() {
if(isset($this->schema))
return $this->schema;
return null;
}
public function getFacts() {
$list = $this->doQuery("select tableName from defaults order by tableorder",3,"false");
if($list != null){
$sorted = array();
if(isset($this->facts)) {
for($i=0;$ifacts as $fact){
if($fact['name'] == $list[$i][0]){
$sorted[] = $fact;
break;
}
}
}
return $sorted;
}
}
else
return $this->facts;
}
public function getDimensions() {
if(isset($this->dimensions)) {
return $this->dimensions;
}
return null;
}
public function getFactsNames() {
$names = array();
if(isset($this->facts)) {
foreach($this->facts as $fact) {
array_push($names,$fact['name']);
}
return $names;
}
return null;
}
function getType($oldType) {
switch ($oldType) {
case 'int':
case 'double':
return 'number';
break;
case 'varchar':
case 'datetime':
return 'string';
break;
case 'bool':
return 'boolean';
break;
default:
return false;
}
}
/*returns an array with the triplet (name,type,view) for each of the keys of the dimension table
or false on failure
type is either number, string or boolean //? need to add more ?*/
function findKeys($table) {
$myarray = array();
if($table == false) {
$this->log->error('findKeys: no table indicated');
return false;
}
if(is_array($table['key'])) {
foreach($table['key'] as $key) {
$keyInfo = $this->findOneField($table,$key,'attrib');
array_push($myarray,$keyInfo);
}
}
else {
$keyInfo = $this->findOneField($table,$table['key'],'attrib');
array_push($myarray,$keyInfo);
}
return $myarray;
}
//returns an array with the tetraplet (name,type,view,data) for each of the meas of the fact table or false on failure
//type is either number, string or boolean //? need to add more ?
function findMeas($table) {
$myarray = array();
if($table == false) {
$this->log->error('findMeas: no table indicated');
return false;
}
if(isset($table['meas'][0])) {
foreach($table['meas'] as $meas) {
if(isset($meas['view'])) {
$view = $meas['view'];
}
else
$view = $meas['name'];
array_push($myarray,array($meas['name'],$view,$meas['type'],$meas['data']));
}
}
else {
if(isset($table['meas']['view'])) {
$view = $meas['view'];
}
else
$view = $meas['name'];
array_push($myarray,array($meas['name'],$view,$meas['type'],$meas['data']));
}
return $myarray;
}
function getDims() {
return $this->dimensions;
}
function findOneField($table,$name,$ftype) {
$field = search($table[$ftype],'name',$name);
if(count($field)!=1) {
$this->log->error('error in xml');
return false;
}
$type = $this->getType($field[0]['type']);
if(isset($field[0]['view'])) {
$view = $field[0]['view'];
}
else
$view = $field[0]['name'];
return array($field[0]['name'],$type,$view,$field[0]['data']);
}
function getField($table,$name,$ftype) {
$field = search($table[$ftype],'name',$name);
if(count($field)!=1) {
$this->log->error('error in xml');
return false;
}
return $field[0];
}
function findTable($name,$type) {
if($type == 'dimension') {
$all_matching_files = search($this->dimensions,'name',$name);
}
elseif($type == 'fact') {
$all_matching_files = search($this->facts,'name',$name);
}
else {
$this->log->error('findTable: could not recognise type '.$type);
return false;
}
if(count($all_matching_files)==0) {
$this->log->error('xml file error: table: '.$name.' not found');
return false;
}
$matching_files = search($all_matching_files,'type',$type);
if(count($matching_files)!=1) {
$this->log->error('multiple '.$type.'s with name: '.$name);
return false;
}
return $matching_files[0];
}
//checks if a fact table with exactly the given dimensions exist
function searchWarehousebyDims($dims) {
$flag = false;
foreach($this->facts as $warehouse) {
//if the number of dims is the same as the given and every dim in the list exists in the given then return the warehouse name
if(count($warehouse['dim']) != count($dims)){
continue;
}
foreach($dims as $dim) {
$res = search($warehouse['dim'], 'name', $dim);
if(count($res) != 0) {
$flag = true;
}
else {
$flag = false;
break;
}
}
if($flag == true)
return $warehouse['name'];
}
//not found
return false;
}
/*check if a name is already in use for a fact table
if yes, return false, otherwise, return true*/
function checkName($name) {
if(findTable($name,"fact") != false) {
return true;
}
return false;
}
/*check if a name is already in use for a dimension table
if yes, return true, otherwise, return false*/
function checkDim($name) {
if(findTable($name,"dimension") != false) {
return true;
}
return false;
}
function findTableType($name) {
if($this->findTable($name,"dimension") != false) {
return "dimension";
}
if($this->findTable($name,"fact") != false) {
return "fact";
}
return false;
}
/*check if a list of dims all exist in the schema
returns a list with all dims that don't exist or true
*/
function checkListofDims($dims) {
$non_existent = array();
$flag = true;
foreach($dims as $dim) {
if(checkDim($dim) == true){
array_push($non_existent,$dim);
$flag = false;
}
}
if($flag == true)
return true;
else
return $non_existent;
}
function checkMeas($fact, $meas_name) {
//find the fact table
//if there is a meas with the same name
foreach($this->facts as $table)
if($table['name'] == $fact)
foreach($table['meas'] as $meas) {
if($meas== $meas_name)
{ return true;
}
}
return false;
}
function getAllDims() {
$dimnames = array();
foreach($this->dimensions as $dim) {
array_push($dimnames,$dim['name']);
}
return $dimnames;
}
function getDimsFieldsList($tableName) {
$fields = array();
//find the table
$table = $this->findTable($tableName,'dimension');
if($table == false) {
return false;
}
foreach($table['attrib'] as $field) {
if($field['name'] == 'id')
continue;
if(isset($field['analysed'])) {
$fields = array_merge($fields,$this->getDimsFieldsList($field['analysed']));
}
else {
if(isset($field['view']))
$view = $field['view'];
else
$view = $field['name'];
array_push($fields,array($tableName.".".$field['name'],$tableName.".".$view,$field['type'],$field['data']));
}
}
return $fields;
}
function getDimsList($tablename) {
$fields = array();
//find the factable
$table = $this->findTable($tablename,'fact');
if($table == false) {
return false;
}
foreach($table['dim'] as $dim) {
$temp = $this->getDimsFieldsList($dim['name']);
$fields = array_merge($fields, $temp);
}
return $fields;
}
function getDimsKeys($dimName) {
$table = $this->findTable($dimName,'dimension');
if($table == false) {
return false;
}
return $table['key'];
}
//-------------------------------------------------------------------------------------\\
//-------------------------------------------------------------------------------------\\
//----------------------------------DATA READING---------------------------------------\\
//-------------------------------------------------------------------------------------\\
//-------------------------------------------------------------------------------------\\
function getMeasMetadata($table) {
$query = 'select name, source, sourceUrl, definition, unit from Metadata where inTable="'.$table.'"';
$this->log->info("getMeasMetadata -> generated query: ".$query);
$res = $this->doQuery($query);
if($res == null)
return "false";
return $res;
}
/*
gets the name of a fact table and the name of a measurement and returns the minimun, maximum and count distinct values for it
*/
function measRangeData($facttable, $measurement) {
$query = "SELECT min($measurement), max($measurement), count(distinct $measurement) FROM $facttable WHERE $measurement != ''";
$this->log->info("generated query: ".$query);
$res = $this->doQuery($query);
$res=$res[0];
return array($res[0],$res[1],$res[2]);
}
//return the minimun, maximum
function getRangeData($table, $field) {
$query = 'SELECT min("'.$field.'"), max("'.$field.'") FROM "'.$table.'"';
if($GLOBALS['type'] == "sqlite")
$query .= ' WHERE "'.$field.'" != ""';
$this->log->info("generated query: ".$query);
$res = $this->doQuery($query);
$res = $res[0];
return array("min"=>$res[0],"max"=>$res[1]);
}
//return the distinct values
function getDistinctValues($table, $field) {
$query = 'select distinct "'.$field.'" from "'.$table.'" order by "'.$field.'"';
$this->log->info("generated query: ".$query);
$res = $this->doQuery($query, PDO::FETCH_NUM);
return $res;
}
function getFilterData($table,$field){
$flag = false;
$myDimTable = $this->findTable($table,'dimension');
$myFactTable = $this->findTable($table,'fact');
//is it a dim?
if($myDimTable != false){
//does it have the field?
if(!isAssoc($myDimTable['attrib'])){
foreach($myDimTable['attrib'] as $attrib){
if($attrib['name'] == $field){
$myField = $attrib;
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
}
}
}
else{
if($myDimTable['attrib']['name'] == $field){
$myField = $myDimTable['attrib'];
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
}
}
}
if($myFactTable != false){
//look in the dims
if(!isAssoc($myFactTable['dim'])){
foreach($myFactTable['dim'] as $dim) {
if($dim['name'] == $field){
$myField = $dim;
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
}
}
}
else{
if($myFactTable['dim']['name'] == $field){
$myField = $myFactTable['dim'];
//$flag = true;
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
}
}
if(!isAssoc($myFactTable['meas'])){
foreach($myFactTable['meas'] as $meas) {
if($meas['name'] == $field){
$myField = $meas;
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
}
}
}
else{
if($myFactTable['meas']['name'] == $field){
$myField = $myFactTable['meas'];
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
}
}
$this->log->error('cannot find field '.$field.' in table '.$table);
return "cannot find field ".$field;
}
$this->log->error("cannot find table ".$table);
return "cannot find table ".$table;
}
function getDefaultData($table) {
$this->log->info("DefaultData -> table:".$table);
if($table !=null && $table!='null') {
$query = 'select * from "defaults" where tablename='."'".$table."'";
}
else{
$query = 'select * from "defaults" where tableorder=(select min(tableorder) from "defaults")';
}
$this->log->info("DefaultData -> query created: ".$query);
$res = $this->doQuery($query,3,"false");
if($res == null)
return "empty";
$selectedData = array();
$selectedData['table'] = $res[0][0];
$selectedData['fields'] = array();
$selectedData['fields'][0] = array();
$selectedData['fields'][0]['fld'] = $res[0][2];
$selectedData['fields'][0]['agg'] = $res[0][3];
$selectedData['fields'][0]['id'] = 0;
$selectedData['fields'][0]['type'] = "";
$selectedData['fields'][0]['yaxis'] = 1;
$selectedData['xaxis']['name'] = $res[0][1];
$selectedData['xaxis']['agg'] = 'avg';
$selectedData['group'] = '';
$selectedData['color'] = '';
if($this->checkMeas($selectedData['table'], $selectedData['xaxis']['name'])){
$type='scatter';
$selectedData['type'] = 'scatter';
}
else{
$selectedData['type'] = 'chart';
$type = $res[0][5];
}
$selectedData['size'] = $GLOBALS['size'];
return array('selectedData'=>$selectedData,'type'=>$type);
//return array('selectedData'=>$selectedData, 'data'=>$this->getData($selectedData),'type'=>$type);
}
/*
domi tou selectedData:
['table'] = fact table name
['fields'] = [{'fld':field name,'agg':aggregator for the field,'id': de mas noiazei}, ...]
['series'] = [dim field name with the list of the dim tables names that lead to it (eg: Product-Supplier-sup_name), ...]
['group'] = dim field name with the list of the dim tables names that lead to it (eg: Product-Supplier-sup_name) <---will have to group by it and have a series for each value of it
['xaxis'] = {'name': to onoma tou dim h meas pou paei ston x axis, 'agg': o aggregator an einai meas}
['type'](optional)
stin apli periptwsi to series einai panta keno
sto telos exoume tosa series osa fields (to poly 3)
an sto xaxis exoume meas tote exoume scatter plot kai den bazoume to xaxis sto group by
an den exoume series tote den exoume group by kai agnooume ta aggs
*/
/*
domi tou query:
ok select: to xaxis me patera to akribws proigoumeno sto onoma AND ola ta fields me ta aggs tous AND ola ta series me patera to akribws proigoumeno sto onoma
ok from: to table AND ola osa emfanizontai sto series kai to xaxis
where: 1 zeygari gia kathe syndyasmo diplanwn pinakwn pou emfanizetai sto series kai twn arxikwn me to table
(eg: gia to Product-Supplier tha exoume ena zeygari to Product,Supplier kai ena to Facttable,Product)
ok group by: to xaxis kai ola ta series opws akribws kai sto select
ok order by: to xaxis
*/
function makeQueryGroup($sel,$persistent){//print_r($sel);
$this->log->info("makeQueryGroup");
/*
select agg(field), xaxisfield, groupfield
from facttable, xaxistables, grouptables
where facttable = xaxistables and xaxistables and facttable = groupstables and groupstables
group by groupfield, xaxisfield (ektos ki an einai meas)
order by groupfield, xaxisfield
*/
$isMeas = false;
$field = "";
$xaxisfield = "";
$groupfield = "";
$tables = array();
$tablesPairs = array();
$tables[] = $sel['table'];
/*yaxis*/ //<---- panta measurement
$field = '"'.$sel['fields'][0]['fld'].'"';
/*xaxis*/
$xaxislist = explode("-",$sel['xaxis']['name']);
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1])){
$isMeas = true;
}
if(!$isMeas){
//an den einai measurement tote einai dimension kai mporei na exei tables
if(count($xaxislist) == 1){ //einai dim alla den analyetai se pinaka
$xaxisfield = '"'.$sel['table'].'"."'.$xaxislist[count($xaxislist)-1].'"';
}
else{
$xaxisfield = '"'.$xaxislist[count($xaxislist)-2].'"."'.$xaxislist[count($xaxislist)-1].'"';
//briskw tous pinakes enan enan kai tous bazw sta pairs alla kai sto xaxistables
for($i=0;$i 0){
$query .= " and ";
for($i=0;$ilog->info("generated query: ".$query);
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
if($isMeas) $returnType = 'scatter';
else $returnType = 'chart';
return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
}
function getData($sel,$persistent){
$this->log->info("getting data");
$xaxislist = explode("-",$sel['xaxis']['name']);
$groups = array();
$brsels = array(); $queries = array();
$breakflag = false;
//check if we need to break the query to many (if there are filters that apply to only one field)
if(count($sel['fields']) > 1){
for($i=0;$ilog->info("NEED TO BREAK!");
//will break the query into as many as the different values in the filters and havings to attribute -> count($groups)
$brsels[] = $this->clearselections($sel,$groups,-1,0);
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
$queries[] = $this->scatterData($brsels[0],false);
else
$queries[] = $this->chartData($brsels[0],false,$persistent);
for($i=0;$iclearselections($sel,$groups,$groups[$i],$i+1);
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
$queries[] = $this->scatterData($brsels[$i+1],false);
else
$queries[] = $this->chartData($brsels[$i+1],false,$persistent);
}
$this->log->info("selections: ".print_r($brsels,true));
$this->log->info("user selections updated: ".print_r($sel,true));
$this->log->info("queries: ".print_r($queries,true));
//get all the queries and combine them to one
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
return $this->makeSuperQuery($queries,$sel,"scatter",$persistent);
else
return $this->makeSuperQuery($queries,$sel,"chart",$persistent);
}
else{
$this->log->info("NO NEED TO BREAK!");
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
return $this->scatterData($sel,$persistent);
else
return $this->chartData($sel,true,$persistent);
}
}
private function makeSuperQuery($queries,$sel,$type,$persistent){
$superquerySelects = "SELECT ";
$superquerySelecte = " ";
$superqueryFrom = " FROM ";
$superquerySort = "";
//ta pedia tou xaxis kai tou group tha einai ayta tou outer join
//sta select yparxei prwta to xaxis, meta i lista me ta fields onomatismena, kai meta an yparxei to group
//sto megalo select tha yparxei gia kathe query to xaxis, meta ola ta fields twn queries me ti seira pou einai sto sel
//kai meta gia kathe query to group field
//prepei na kseroume kathe pedio tou select se poio query anikei, kai to pedio tou order by
for($q=0;$q0) $superquerySelects .= ", ";
$superquerySelects .= "q".$q.".xfield, ";
if($q>0) $superquerySort .= ", ";
$superquerySort .= "q".$q.".xfield ";
if($sel['group']!='' && $sel['group'] != 'no'){//dimfields
/*if($q>0)*/ $superquerySelecte .= ", ";
$superquerySelecte .= "q".$q.".dimfield ";
$superquerySort .= ", q".$q.".dimfield ";
}
$superqueryFrom .= "(".$queries[$q].") as q".$q;
if($q>0){
$superqueryFrom .= " ON q".($q-1).".xfield = q".$q.".xfield ";
if($sel['group']!='' && $sel['group'] != 'no')
$superqueryFrom .= " AND q".($q-1).".dimfield = q".$q.".dimfield ";
}
if($qlog->info("superquery: ".$superQuery);
$res = $this->doQuery($superQuery, PDO::FETCH_NUM,$persistent);
return $this->combineData($res,count($queries),count($sel['fields']),$type);
}
private function combineData($data,$queries,$fields,$type){
$newdata = array();
if(count($data[0]) > $queries+$fields)
$dstart = $queries + $fields;
else
$dstart = -1;
$fstart = $queries;
for($d=0;$d 0)
for($i=$dstart;$ilog->info("superquerys result: ".print_r($newdata,true));
return array('type'=>$type,'data'=>$newdata);
}
private function clearselections(&$sel,$seperate,$field,$index){
$newsel = array();
$fields = array();
$newsel['table'] = $sel['table'];
$newsel['xaxis'] = $sel['xaxis'];
$newsel['group'] = $sel['group'];
$newsel['color'] = $sel['color'];
$newsel['size'] = $sel['size'];
$newsel['fields'] = array();
$newsel['filters'] = array();
$newsel['having'] = array();
if($field == -1 || $field == -2){ //things that apply to whole chart, will remove only the things that apply to one field and that field
for($i=0;$ichartDataGroup($sel, $doquery);
}
else{
$this->log->info("sel: ".print_r($sel,true));
$tree = new QueryTree($sel['table']);
$tree->updateXAxis($sel['xaxis']['name']);
$tree->updateYAxis($sel['fields']);
$tree->updateFilters($sel['filters']);
if (isset($sel['sort'])) {$tree->updateOrderBy($sel['sort']);}
if(isset($sel['order'])){$tree->updateOrder($sel['order']);}
if(isset($sel['size'])){$tree->updateLimit($sel['size']);}
if(isset($sel['nulls'])){$tree->excludeNulls($sel['nulls']);}
$this->log->info("tree: ".print_r($tree->tree, true));
$query = $tree->getQuery();
if(!$doquery){
$this->log->info('chartData generated query:'.$query);
$this->log->info('will not execute it');
return $query;
}
$this->log->info('chartData generated tree: '.print_r($tree->getTree(), true));
$this->log->info('chartData generated query: '.$query);
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
$this->log->info("result: ".print_r($res,true));
return array('type'=>'chart','data'=>$res);
}
}
function chartDataGroup($sel, $doquery=true,$persistent){
//exoume dyo dims, ena tou xaxis kai ena tou group
//kanoume tin idia diadikasia kai gia ta dyo
$xfield = '';
$dimfield = '';
$tables = array();
$tablePairs = array();
$filters = array();
//order by it
//may need to join
//gia to xaxis
$dimlist = explode("-",$sel['xaxis']['name']);
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
$xfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
}
else{
$xfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
//briskw tous pinakes enan enan kai tous bazw sta pairs
for($i=0;$i1){ //join needed
$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
for($i=0;$i1){ //join needed
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
for($i=0;$i0) $query .= ', "'.implode("\", \"",$tables).'" ';
/*$query .= ' WHERE "'.$sel['xaxis']['name'].'" !=""';
for($i=0;$i 0 || (isset($sel['filters']) && count($sel['filters']) > 0) || $sel['nulls']){
$query .= " WHERE ";
for($i=0;$i0){
if(count($tablePairs)>0)
$query .= " and ";
for($i=0;$i=".$sel['filters'][$i]['min'].")";
else if(strstr($sel['filters'][$i]['name'],"year") !== false && $sel['filters'][$i]['min']=='today')
$query .="(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">= extract(YEAR from now()))" ;
else
$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
}
else if(isset($sel['filters'][$i]['values'])){
$query .= "(";
for($j=0;$j0) || count($tablePairs) > 0){
$query .= " and ";
}
$query .= $xfield . " IS NOT NULL AND ". $dimfield . " IS NOT NULL ";
}
}
//--------------------------------------------------------//
$query .= " GROUP BY ".$dimfield.', '.$xfield;
if(count($sel['having'])>0){
$query .= " HAVING ";
for($i=0;$i0)
$query .= " and ";
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
}
}
if(!$doquery){
$this->log->info('chartDataGroup generated query:'.$query);
$this->log->info('will not execute it');
return $query;
}
//need to find the field to order the result set by
$query.= " ORDER BY ";
if(isset($sel['sort']) && $sel['sort']!='xaxis'){
$ex = explode("-", $sel['sort']);
$query .= $ex[0]."(".$sel['table'].".".$ex[1].")";
if($sel['order'] && $sel['order'] == 'd')
$query.= " DESC";
$query .= ", ".$xfield;
}
else{
$query.= $xfield;
if($sel['order'] && $sel['order'] == 'd')
$query.= " DESC";
}
$query.=', '.$dimfield;
$query.= " LIMIT ".$sel['size'];
//echo $query;
$this->log->info('chartDataGroup generated query:'.$query);
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
$this->log->info("result: ".print_r($res,true));
return array('type'=>'chart','data'=>$res);
}
function scatterData($sel, $doquery=true,$persistent){
//group, color, or nothing
//many fields or one
if($sel['group']!='' && $sel['group'] != 'no'){ //group
$this->log->info('scatterDataGroup called');
return $this->scatterDataGroup($sel, $doquery);
}
else if($sel['color']!='' && $sel['color'] != 'no'){ //color
$this->log->info('scatterDataColor called');
return $this->scatterDataColor($sel, $doquery);
}
else{ //nothing
$this->log->info('scatterData called');
//code here
//no dim to bother us, just the measurements
//TODO: get all other dims to show in tooltip
$tables = array();
$tablePairs = array();
$filters = array();
//filters
if(isset($sel['filters'])){
for($fc=0;$fc1){ //join needed
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
for($i=0;$i0) $query .= ', "'.implode("\", \"",$tables).'" ';
$query .= ' WHERE ';
for($i=0;$i0) $query .=" AND ";
$query .= "field".$i.'" IS NOT NULL';
}
//joins + filters
if(count($tablePairs) > 0 || (isset($sel['filters']) && count($sel['filters']) > 0) || $sel['nulls']){
$query .= " AND ";
for($i=0;$i0){
if(count($tablePairs)>0) $query .= " and ";
for($i=0;$i=".$sel['filters'][$i]['min'].")" ;
}
else{
$query .= "(";
for($j=0;$j0) || count($tablePairs) > 0){
$query .= " and ";
}
$query .= $xfield . " IS NOT NULL ";
}
}
if(count($sel['having'])>0){
$query .= " HAVING ";
for($i=0;$i0)
$query .= " and ";
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
}
}
//--------------------------------------------------------//
if(!$doquery){
$this->log->info('scatterData generated query:'.$query);
$this->log->info('will not execute it');
return $query;
}
$this->log->info('scatterData generated query:'.$query);
//echo "scatterplot, simple case:"; echo $query;
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
$this->log->info("result: ".print_r($res,true));
return array('type'=>'scatter','data'=>$res);
}
}
function scatterDataColor($sel, $doquery=true,$persistent){
//one dim, the one for the color
//TODO: get all other dims to show in tooltip
$dimfield = '';
$tables = array();
$tablePairs = array();
//order by it
//may need to join
$dimlist = explode("-",$sel['color']);
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
}
else{
$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
//briskw tous pinakes enan enan kai tous bazw sta pairs
for($i=0;$i1){ //join needed
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
for($i=0;$i0) $query .= ', "'.implode("\", \"",$tables).'" ';
$query .= " WHERE ";
for($i=0;$i0) $query .=" AND ";
$query .= "field".$i.'" IS NOT NULL';
}
if(count($tablePairs) > 0){
$query .= " AND ";
for($i=0;$i0){
if(count($tablePairs)>0) $query .= " AND ";
for($i=0;$i=".$sel['filters'][$i]['min'].")" ;
}
else{
$query .= "(";
for($j=0;$j0){
$query .= " HAVING ";
for($i=0;$i0)
$query .= " and ";
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
}
}
if(!$doquery){
$this->log->info('chartDataColor generated query:'.$query);
$this->log->info('will not execute it');
return $query;
}
//--------------------------------------------------------//
$query .= " ORDER BY ".$dimfield;
//echo "scatterplot, case color:"; echo $query;
$this->log->info('scatterDataColor generated query:'.$query);
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
$this->log->info("result: ".print_r($res,true));
return array('type'=>'scatter','data'=>$res);
}
function scatterDataGroup($sel, $doquery=true,$persistent){
//one dim, the one for the group
//aggrs for all fields
//TODO: get all other dims to show in tooltip
$dimfield = '';
$tables = array();
$tablePairs = array();
//order by it
//may need to join
$dimlist = explode("-",$sel['group']);
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
}
else{
$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
//briskw tous pinakes enan enan kai tous bazw sta pairs
for($i=0;$i1){ //join needed
$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
for($i=0;$i1){ //join needed
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
for($i=0;$i0) $query .= ', "'.implode("\", \"",$tables).'" ';
$query .= ' WHERE ';
for($i=0;$i0) $query .=" AND ";
$query .= 'field'.$i." IS NOT NULL";
}
if(count($tablePairs) > 0){
$query .= " AND ";
for($i=0;$i0){
$query .= " AND ";
for($i=0;$i=".$sel['filters'][$i]['min'].")" ;
}
else{
$query .= "(";
for($j=0;$j0){
$query .= " HAVING ";
for($i=0;$i0)
$query .= " and ";
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
}
}
if(!$doquery){
$this->log->info('chartDataGroup generated query:'.$query);
$this->log->info('will not execute it');
return $query;
}
//echo "scatterplot, case group:"; echo $query;
$this->log->info('scatterDataGroup generated query:'.$query);
$res = $this->doQuery($query, PDO::FETCH_NUM);
$this->log->info("result: ".print_r($res,true));
return array('type'=>'scatter','data'=>$res);
}
function performQuery($query,$persistent){
$this->log->info("perfoming query ".$query." for persistence : ".$persistent." \n");
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
$this->log->info("result: ".print_r($res,true));
return $res;
}
function makeQuery($selectedData){
$this->log->info("makeQuery");
$isMeas = false;
$hasGroups = true;
$selectList = array();
$groupbyList = array();
$whereList = array();
$groupby = "";
$orderby = "";
$lastSelect = array();
$tablesList = array();
$tablesPairs = array();
$xaxistables = array();
$dimsList = array();
$dimsList = $this->queryAllDims($selectedData['table']);
//create the tables list and the tables pairs list
$tablesList[] = $selectedData['table'];
foreach($selectedData['series'] as $series) {
$seriesexploded = explode("-",$series);
if(count($seriesexploded) == 1) {
$groupbyList[] = '"'.$selectedData['table'].'"."'.$seriesexploded[0].'"';
$lastSelect[] = $seriesexploded[0];
}
else{
$i=0;
if(!in_array($seriesexploded[$i],$tablesList))
$tablesList[] = $seriesexploded[$i];
if(!in_array_r(array($selectedData['table'],$seriesexploded[0]),$tablesPairs))
$tablesPairs[] = array($selectedData['table'],$seriesexploded[0]);
for($j=1;$jcheckMeas($selectedData['table'], $xaxistables[count($xaxistables)-1])){
$isMeas = true;
$hasGroups = false;
}
if(count($xaxistables) == 1){
if($isMeas && $selectedData['group'] && $selectedData['group'] != ''){
if($selectedData['xaxis']['agg'] == 'none')
$selectList[] = "avg('".$selectedData['table'].'"."'.$xaxistables[0].'")';
else
$selectList[] = $selectedData['xaxis']['agg']+'("'.$selectedData['table'].'"."'.$xaxistables[0].'")';
}
else{
$selectList[] = '"'.$selectedData['table'].'"."'.$xaxistables[0].'"';
}
if(!$isMeas){
$groupbyList[] = '"'.$selectedData['table'].'"."'.$xaxistables[0].'"';
}
}
else {
$selectList[] = '"'.$xaxistables[count($xaxistables)-2].'"."'.$xaxistables[count($xaxistables)-1].'"';
if(!$isMeas){
$groupbyList[] = '"'.$xaxistables[count($xaxistables)-2].'"."'.$xaxistables[count($xaxistables)-1].'"';
}
if(!in_array($xaxistables[count($xaxistables)-2],$tablesList))
$tablesList[] = $xaxistables[count($xaxistables)-2];
if(!in_array_r(array($selectedData['table'],$xaxistables[0]),$tablesPairs))
$tablesPairs[] = array($selectedData['table'],$xaxistables[0]);
for($i=0;$ilog->info("generated query: ".$query);
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
//print_r($res);
//$this->log->info('result set:'.json_encode($res));
if($isMeas) $returnType = 'scatter';
else $returnType = 'chart';
return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
}
function queryAllDims($tablename) {
//get all the dims of the fact table and make a list with all their keys
$fields = array();
$joins = array();
//find the factable
$table = $this->findTable($tablename,'fact');
if($table == false) {
return false;
}
foreach($table['dim'] as $dim) {
if(isset($dim['dimtable']) && $dim['dimtable'] == 'no') {//will need no join
$fields[] = $dim['name'];
}
else{
//find the key(s) put them in the fields list
$key = $this->getDimsKeys($dim['name']);
if(is_array($key)){
foreach($key as $k)
$k = "'".$k."'";
$fields[] = implode(" || ",$key);
}
else
$fields[] = $key;
//and put it in the join list
$joins[] = $dim['name'];
}
}
return array('fields'=>$fields,'joins'=>$joins);
}
}
class QueryTree {
public $tree = Array();
public function __construct($table) {
$this->tree['table'] = $table;
$this->tree['chains'] = Array();
$this->tree['order'] = 'ASC';
}
public function updateFilters($filters) {
for ($i = 0; $i < count($filters); $i++) {
$filterFields = explode("-", $filters[$i]['name']);
$rootField = $filterFields[0];
$field = $filterFields[count($filterFields) - 1];
$filter = Array();
$filter["column"] = $field;
if (isset($filters[$i]['values']))
$filter['values'] = $filters[$i]['values'];
if (isset($filters[$i]['max']))
$filter['max'] = $filters[$i]['max'];
if (isset($filters[$i]['min']))
$filter['min'] = $filters[$i]['min'];
if (isset($filters[$i]['exvalues']))
$filter['exvalues'] = $filters[$i]['exvalues'];
if (count($filterFields) > 1) {
$this->buildChain($filters[$i]['name']);
$this->tree['chains'][$rootField]['tables'][count($filterFields) - 2]['filters'][] = $filter;
} else {
$this->tree['filters'][] = $filter;
}
}
}
public function updateYAxis($yAxisFields) {
foreach ($yAxisFields as $field)
$this->tree['yAxis'][] = $field['agg'] . '("' . $this->tree['table'] . '"."' . $field['fld'] . '")';
}
public function updateXAxis($xAxisField) {
$fields = explode("-", $xAxisField);
if (count($fields) == 1) {
$this->tree['xAxis'] = '"'. $this->tree['table'] .'"."' . $fields[0] . '"';
} else {
if (!isset($this->tree['chains'][$fields[0]])) {
$this->buildChain($xAxisField);
}
$this->tree['xAxis'] = '"' . $fields[0] . '"."' . $fields[count($fields) - 1] . '"';
$this->tree['chains'][$fields[0]]['return'] = '"'.$fields[count($fields) - 2] . '"."' . $fields[count($fields) - 1] . '"';
}
}
public function excludeNulls($exclude = true) {
$this->tree['excludeNulls'] = $exclude;
}
public function updateOrderBy($orderBy) {
if ($orderBy == 'xaxis') {
$this->tree['orderBy'] = $this->tree['xAxis'];
} else {
$fields = explode('-', $orderBy);
$this->tree['orderBy'] = $fields[0] . '("' . $this->tree['table'] . '"."' . $fields[1] . '")';
}
}
public function updateOrder($order) {
if ($order == 'd') {
$this->tree['order'] = 'DESC';
}
}
public function updateLimit($limit) {
$this->tree['limit'] = $limit;
}
public function getQuery() {
$query = 'SELECT ' . $this->tree['xAxis'] . ' as xfield';
if (isset($this->tree['yAxis'])) {
for ($i = 0; $i < count($this->tree['yAxis']); $i++) {
$query .= ', ';
if ($i < count($this->tree['yAxis']) - 1) {
$query .= ' , ';
}
$query .= $this->tree['yAxis'][$i] . ' as field' . $i;
}
}
$query .= ' FROM ' . $this->tree['table'];
foreach ($this->tree['chains'] as $chainName => $chain) {
$query .= ' JOIN (' . $this->buildSubQuery($chain) . ') as ' .$chainName . ' ON "' . $this->tree['table'] . '"."' . $chainName . '" = "' . $chainName . '"."id"' ;
}
if (isset($this->tree['excludeNulls']) || isset($this->tree['filters'])) {
$query .= ' WHERE ';
}
if (isset($this->tree['excludeNulls'])) {
$query .= $this->tree['xAxis'] . ' IS NOT NULL';
}
if (isset($this->tree['filters'])) {
if (substr_compare($query, ' WHERE ', -strlen(' WHERE '), strlen(' WHERE ')) !== 0)
$query .= ' AND ';
for ($i = 0; $i < count($this->tree['filters']); $i++) {
$query .= '(' . $this->buildSubQueryFilter($this->tree['table'], $this->tree['filters'][$i]) .')';
if ($i < count($this->tree['filters']) - 1)
$query .= ' AND ';
}
}
$query .= ' GROUP BY ' . $this->tree['xAxis'];
if (isset($this->tree['orderBy'])) {
$query .= ' ORDER BY ' . $this->tree['orderBy'] . ' ' . $this->tree['order'];
}
if (isset($this->tree['limit'])) {
$query .= ' LIMIT ' . $this->tree['limit'];
}
return $query;
}
public function getTree() {
return $this->tree;
}
/* Helper functions */
private function buildChain($fieldsString) {
$fields = explode("-", $fieldsString);
$rootField = $fields[0];
if (!isset($this->tree['chains'][$rootField])) {
$this->tree['chains'][$rootField] = Array();
$this->tree['chains'][$rootField]['name'] = $rootField;
}
for ($fc = 0; $fc < count($fields) - 1; $fc++) {
$field = $fields[$fc];
if (!isset($this->tree['chains'][$rootField]['tables'][$fc]) ) {
$this->tree['chains'][$rootField]['tables'][] = Array("table" => $field);
}
}
}
private function buildSubQuery($chain) {
$subQuery = 'select distinct "' . $chain['tables'][0]['table'] . '"."id"';
if (isset($chain['return']))
$subQuery .= ', ' . $chain['return'];
$subQuery .= ' from "' . $chain['tables'][0]['table'] . '"';
for ($i = 1; $i < count($chain['tables']); $i++) {
$subQuery .= ' join "' . $chain['tables'][$i]['table'] .'" on "' . $chain['tables'][$i]['table'] . '"."id"="'.$chain['tables'][$i-1]['table'].'"."'.$chain['tables'][$i]['table'].'"';
if (isset($chain['tables'][$i]['filters'])) {
foreach ($chain['tables'][$i]['filters'] as $filter) {
$subQuery .= ' and (' . $this->buildSubQueryFilter($chain['tables'][$i]['table'], $filter) . ') ';
}
}
}
return $subQuery;
}
private function buildSubQueryFilter($tableName, $filter) {
$column = $filter['column'];
$filterText = "";
if (isset($filter['values'])) {
for ($fc = 0; $fc < count($filter['values']); $fc++) {
$value = $filter['values'][$fc];
if ($fc > 0)
$filterText .= ' or ';
$filterText .= '"' . $tableName . '"."'.$column.'" = \'' . $value . '\'';
}
} else if ( isset($filter['exvalues'])) {
for ($fc = 0; $fc < count($filter['exvalues']); $fc++) {
$value = $filter['exvalues'][$fc];
if ($fc > 0)
$filterText .= ' and ';
$filterText .= '"' . $tableName . '"."'.$column.'" != \'' . $value . '\'';
}
} else if (isset($filter['max'])) {
if (strstr($column, 'year') && $filter['max'] == 'today') {
$filterText = $column . ' <= extract(YEAR from now()) and ' . $column . ' >= ' . $filter['min'];
} else if (strstr($column, 'year') && $filter['min'] == 'today') {
$filterText = $column . ' >= extract(YEAR from now()) and ' . $column . ' <= ' . $filter['max'];
} else {
$filterText = $column . ' >= ' . $filter['min'] . ' and ' . $column . ' <= ' . $filter['max'];
}
}
return $filterText;
}
}
?>