Gossamer Forum
Home : Products : DBMan : Customization :

getting another database's field-names: a mod

Quote Reply
getting another database's field-names: a mod
JP Deni once wrote a small mod on how to import field-names from another database (http://webmagic.hypermart.net/dbman/multi12.txt).

I happen to use this mod quite often, and found it to have one disadvantage: You have to hard-code the list of fields that the other database has.

I now wrote a subroutine which gets that list of fields on the fly from the database configuration file. That way, if the field list happens to change, you always get the current version.

You can call this subroutine by passing on the name of the database setup file without the extension. For example, if I am currently using database db1 and want to access field names (and their values) from db2, I'd do this:

@db_cols = &get_db_cols("db2"); # get db2 field names

In the above mod from JPDeni, this line replaces the line starting with "@db_cols = ('Name', 'URL'".

Add the following subroutine somewhere to html.pl or db.cgi (db.pl):
----------------------------------
sub get_db_cols {

$configfile = "$_[0].cfg";
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
undef $/;

while (<FILE>)
{ if (/(%db_def\s+=\s+\(\s+)('.*?)(\))/s) { $fields = $2;}}
close(FILE);

while ($fields =~ /(\n')(\w+)(')/g) {push (@db_cols, $2); }
return (@db_cols);
}
---------------------------
Explanation:

What the code does is basically to open the cfg-file, look for the field list and extract the field names. It contains pattern searches based on how the field name list is written in the cfg-file: the assumption is that the field name list follows the string "%db_def = (" and is in turn followed by a closing bracket ")". Another assumption is that field-names are placed at the beginning of a new line, enclosed by single quotes. This is how the cfg-file comes "out of the box", so unless you've changed that, the sub should work fine.

I'd be glad to have suggestions to improve upon the above code (I'm not very confident in my coding skills!). Perhaps it could then be added to JPDeni's mod in the unofficial FAQ.

Cheers,

kellner
Quote Reply
Re: getting another database's field-names: a mod In reply to
There is a much nicer way Smile:

Code:
my (%fields) = &get_defaults("default2.cfg");
Code:
sub get_defaults {

my ($def) = shift || "default.cfg";
-e $def or &cgierr();
my %fields;
Code:
require $def;
Code:
foreach (keys %db_def) {
$fields{$_} = ${$_};
}
return %fields;
}
Than can of course be extended to obtain the other %db_def fields.

Mods:http://wiredon.net/gt/download.shtml
Installs:http://wiredon.net/gt/


Quote Reply
Re: getting another database's field-names: a mod In reply to
It sure does look nicer - always inspiring to get a completely different approach, thanks! -, but I don't quite understand what it returns as values for the field keys. I mean, as keys of %fields I get all field names all right, but what are their values? (There weren't any when I tried to print them.)
What I would need is just the field names and then do a query on one particular name in the second database.
Also, by the way, is this sub related to the sub get_defaults which already exists in db.cgi or is the sameness of name purely incidental?

Regards,

kellner
Quote Reply
Re: getting another database's field-names: a mod In reply to
In Reply To:
Also, by the way, is this sub related to the sub get_defaults which already exists in db.cgi or is the sameness of name purely incidental?
Purely coincidental...That's my own code...just rename the sub if you need to..

Right ok, I'm a little confused....when you say you want the field names and their values what exactly do you mean. You want the db_def field names and their corresponding values from default.db?....

SO if you searched for "Category" you'd want all the categories to be shown?......Am I barking up the wrong tree....sorry for sounding stupid but it's midnight and my brain is slowly switching off.

Mods:http://wiredon.net/gt/download.shtml
Installs:http://wiredon.net/gt/


Quote Reply
Re: getting another database's field-names: a mod In reply to
no, what JPDeni's original mod does, and what I want, is to open up db2 which has one field with the same name and value as one in db1. example: db1 has "name", "age", "size", and I have a record where "name" is "Jones". In returning search results for db1, I want to add information for "Jones" which comes from db2. db2 has the fields "name", "hobbies", "dislikes".
So I need to (a) open db2 from within the code for search results for db1, (b) get a record for a particular field value ("name" eq "Jones").
It's in that context where I need the second database's field names, AND their values for a particular query match.
I hope this is somehow intelligible - it's now 3:30 am at this end, and my head might have been left somewhere else :-)

kellner
Quote Reply
Re: [Paul] getting another database's field-names: a mod In reply to
i'm trying to implement this but having weird results. i'm not sure what to do with $fields after i get them from second config file. also, do i have to shift or unshift again to get back to the definitions of default.cfg? any help would be appreciated because this is over my head.
Quote Reply
Re: [delicia] getting another database's field-names: a mod In reply to
i have kellner's code almost working. it is not pulling my first field which is number zero thus all the fields are off by one. then it's not going past field 36. i'm pretty sure it stops there because there's a valid_expr in that field which contains parentheses. i don't know enough about regexp to fix this. can anyone help?
Quote Reply
Re: [delicia] getting another database's field-names: a mod In reply to
thanks very much to andy @ ultranerds. this is working:

undef @db2_cols;
$configfile = $db_script_path . "/" . $configfile;
open(FILE, "<$configfile") || &cgierr("Cannot open $configfile.\n$!");
local $/;
my ($fields);
while (<FILE>) { if (/(%db_def\s+=\s+\(\s+)('.*?)(\);)/s) { $fields = $2;}}
close(FILE);

my @split = split /\n/, $fields;
foreach (@split) {
if ($_ =~ /'(\w+)'/) { push @db2_cols, $1; }
}