Home » Database » DB_DataObject » Bug #4136

[2005-04-13 09:21 UTC] krecik at e-wro dot net

Description:
------------
It would be nice if the links.ini file were automatically generated/augmented from FOREIGN KEY declarations.

[2005-09-03 15:14 UTC] wouter_hartog at hotmail dot com

I totally agree with this request, but I would go a step further; we should not need a links.ini file at all if the database has all the Foreign Keys defined properly. So not just a script to auto-generate the links.ini file, but change the code in the DB_DataObject package to create links based on Foreign Keys.

[2005-09-05 09:52 UTC] krecik at e-wro dot net

Well, alan pointed right thing: somebody has to write a patch. I'm not prepared to do it (lack of time and PHP experience) so I don't push matter further.

As a side note, I'd like to mention it would be not so easy to do properly. SQL allows to declare multi-field keys and trying to handle them would possibly give a whole new class of headaches for anyone doing it. At least gave to me when I tried to invent reasonable interface, not to mention an implementation.

[2005-09-08 16:20 UTC] rkgphp at accedo dot es

Check this out...

<?php

// +----------------------------------------------------------------------+
// | PHP Version 4 |
// +----------------------------------------------------------------------+
// | Copyright (c) Ruben Gomez Agudo <rkgphp@accedo.es> |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.02 of the PHP license, |
// | that is bundled with this package in the file LICENSE, and is |
// | available at through the world-wide-web at |
// | http://www.php.net/license/2_02.txt. |
// | If you did not receive a copy of the PHP license and are unable to |
// | obtain it through the world-wide-web, please send a note to |
// | license@php.net so we can mail you a copy immediately. |
// +----------------------------------------------------------------------+
// | Author: Ruben Gomez <rkgphp@accedo.es>
// +----------------------------------------------------------------------+

// This source automatically generates a links.ini file from a DB processed
// with DB_DataObjects, based in some criterias at the DB design, usually done
// by me and hope by you too

//require_once "debuglib.php";
/*
REQUIRED_VAR: $file_in
OPTIONAL_VAR: [$file_out]
*/

if(!is_string($file_in)) {
print "I need the file in!";
die();
}

if(file_exists($file_in)) {
$lines = file($file_in);
} else {
print "The file $file_in doesn't exists!";
die();
}

if(!is_string($file_out)) {
$file_out = preg_replace("/(.*)\.ini/", "$1.links.ini", $file_in);
}

foreach($lines as $line) {
if(preg_match("/\[(.*)\]/", $line, $res)) {
if(preg_match("/(.*)__keys/", $res[1], $res2)) {
$table = $res2[1];
$keys = true;

// print "<br>table=$table ; keys=$keys ; line='$line'";
continue;
} else {
$table = $res[1];
$keys = false;

// print "<br>table=$table ; keys=$keys ; line='$line'";
continue;
}
}

$table = trim($table, "\r\n ");
$line = trim($line, "\r\n ");

if(!is_string($table) || $table=="" ||
!is_string($line) || $line=="")
continue;

if($keys) {
if(preg_match("/^([A-z0-9_]+)[ ]*=.*$/", $line, $res3))
$k[$table][] = $res3[1];
} else {
if(preg_match("/^([A-z0-9_]+)[ ]*=[ ]*[0-9]+$/", $line, $res3))
$a[$table][] = $res3[1];
}

}

//print_a($a);
//print_a($k);

/*
Now we have the columns and the keys. Let's make the links...

...by searching every (not key) column in each table, comparing with all the
others key columns in the other tables

... Modify this to adapt the program to your needs. This is perfect for me.
*/

foreach($a as $table => $columns) {

foreach($columns as $column) {

// Skip key columns
$column_is_key = false;
foreach($k[$table] as $kcolumns) {
if($column == $kcolumns) {
$column_is_key = true;
break;
}
}
if($column_is_key)
continue;

foreach($k as $table2 => $keys2) {
// Skip himself
if($table2 == $table)
continue;

foreach($keys2 as $key2) {
if($column == $key2)
$l[$table][] = "$column = $table2:$key2";
continue;
}

}
}
}

//int_a($k);
//print_a($l);

foreach($l as $table => $links) {
$out .= "[$table]\n";
foreach($links as $link)
$out .= "$link\n";
$out .= "\n";
}

if (!$handle = fopen($file_out, 'w')) {
echo "Cannot open file ($file_out)";
exit;
}
if (fwrite($handle, $out) === FALSE) {
echo "Cannot write to file ($file_out)";
exit;
}
echo "Success, wrote links to the file $file_out";
fclose($handle);

?>

[2005-09-14 21:05 UTC] carlosjordao at gmail dot com

Hi,
I'm thinking about it. I have written a scrap of code that does it. Well it works (for postgres), but it has some details very specific to the postgres database.
I don't know how to do it. I would like some advices.

Here goes the SQL Query I use:
select ARRAY(select attname from pg_attribute where attnum = any (conkey) and attrelid=conrelid) as from_col, (select relname from pg_class where oid=conrelid) as from_table, a.relname as to_table, ARRAY(select attname from pg_attribute where attnum = any (confkey) AND attrelid=a.oid) as to_col from pg_constraint c, pg_class a where contype='f' and confrelid=a.oid order by from_table;

The code I have put into Generator.php is very simple.
It gets the data provided by this query and writes out to the {$base}/{$this->_database}.links.ini file.

The problems is: the query output is something specific to postgres; the first field comes like array, and is returned like a string instead an array. I've put it into pgsql.php, inside function getSpecificQuery(), but I don't know how make that a generic output to be processed inside Generator.php

Besides that, I need to consider the schema thing. This query doesn't output tables name with schema, what will not work if the .ini file has the tables with schema name.