Gossamer Forum
Home : Products : DBMan : Customization :

Pulling data form 1 db to another.

(Page 1 of 2)
> >
Quote Reply
Pulling data form 1 db to another.
Let me start off by telling you how my system is set up.

First I have 2 db one named "dbads" and the other is "master".
There are 3 dir's, "dbads", "master" and "db". The "dbads" and "master" each have the following files
"auth.pl, db.cgi, default.cfg, default.count, default.log and html.pl". The "db" dir has, "auth(dir)
dbads.db, master.db and default.pass".

the data base is setup for events, dbads hold the last four events. When we start the 5th event the oldest one will
be moved to the master db (by the way of Archive Mod). The master db will keep events 5-10 after that they will be deleted.
I separate the db to keep them from getting to big (dbads which is the main db hold about 2400 records and the master has about 9000 records).

Now for the fun part:-)
Admin is the only one that uses the master db. We use it mainly to lookup the history of donors. but the data is incomplete
because half of the information (events 1-4) is in the dbads db. What i would like to do is pull that data and display it with
the data in the master db so we can see the full history of each donor.

I found this code which is spose to do this, but it is vag on where it go's and how to use it. I put the "sub" in my master db.cgi
and i'm pretty sure the "|; print &get_all_records_from_dbads; print qq|" go's in my master html.pl but dont know here. i also think there is something missing.

Any ideals?

Thank you'
Ed-


call another html_pl from a other html_pl
---------------------------------------------
If you want to print out some records from the database dbads while logged into another database,
this is not quite easy, but can be done:

|; print &get_all_records_from_dbads; print qq|



sub get_all_records_from_dbads {
#-------------------------------------------------------
my $output;
# switch to dbads
undef(@db_cols);
do ("dbads.cfg");
my %old_in = %in;
undef(%in);
# end of switch

# define search terms and parameters
$in{'LfdNr'} = "*"; # you can also specify sort options: $in{'sb'} = "1"; and so on.

# do the actual search
my ($status, @hits) = query("view"); # now search dbads for all records

# output results
if ($status eq "ok") {

my $numhits = @hits / @db_cols;
for (0 .. $numhits-1) {# basically the same routine as in html_view_success
my %rec = &array_to_hash($_, @hits);
$output .= qq|$rec{'Company'} and $rec{'Contact'} and $rec{'Email'}
|; # copy text from html_record for dbads
}
}# end if status ok

# switch back to the original database
undef(@db_cols);
do("$db_setup.cfg");
undef(%in);
%in = %old_in;
return ($output);
}
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
your setup seems a lot more complicated than mine. i put all my related databases in one dir and give them descriptive names. each has its own html file which is defined in the cfg, but all use the same db.cgi.

anyway, i would put both of your subs in the html file for your master. be sure the data path in dbads.cfg makes sense for the master to be able to find the dbads db. the following line should go in the html at the place you want it to print the other records:
print &get_all_records_from_dbads;

this is the way the relational db was intended to work. however, i think your situation is different and will require more custom programming. you will need to read all the records from one db, then read all the records from the other db and add them to the hash/variable (whatever it's called) so that you can sort them all together. in other words, the way i understand it, the records in your dbads are not related to a specific record in the master but are independent and equal.

it would be much easier if you could copy all the records into one file! there are some hacks that tell how to add a record to two databases.

this is rambling so probably isn't much help!

Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Thanks Delicia, I think this will help, i'll play with your ideals and see what happens. i know ill have more question for you.

Ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
delicia,
I tried your suggestion but no luck. So found the hack you mention, about coping to the db.
This works well but it has one problem Smile instead of coping the one file to the 2nd
db, it over write the hole db Frown. This is the sub I used. I put this in the db.cgi
file, and used this tag to call it" &create_copy_file; ". Any ideals how to make change
just the one record? or is this not the mod your talking about?




sub create_copy_file {
#-------------------------------------------------------------
$output = '';
open (DB, "<$db_file_name") or &cgierr("unable to open $db_file_name. Reason: $!");
if ($db_use_flock) { flock(DB, 1); }
@lines = ;
close DB;
foreach $line (@lines) {
$output .= "$line";
}
open (COPY, ">$db_copy_name") or &cgierr("unable to open $db_copy_name. Reason: $!");
if ($db_use_flock) {
flock(COPY, 2) or &cgierr("unable to get exclusive lock on $db_copy_name.\nReason: $!");
}
print COPY $output;
close COPY;
}

Thanks,Ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
look at this thread:

http://redundantcartridge.com/dbman/text/dbfaq108.txt

this appends one record to second db. one note, i don't think it would take care of future modification/delete to original record; i.e. i don't think it modifies or deletes record in db2.

hope you had a backup!!!

Last edited by:

delicia: Oct 5, 2015, 8:12 AM
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Yes, i learned along time ago to make backups and then back it up again Angelic.

do you do PM?

Ed.
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
no i don't PM
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Do you know what lines of code do the actual writhing to the data base? my thinking is to copy those lines of code and have them point to the other DB, so when the record is written to one DB, the other set of code will write to the other DB.

ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
ok, if you're not concerned with a record being changed or deleted and want the originals maintained in the second database, here's what i would do.

i'm referring to the databases as db1(the one into which the record is added) and db2 (the one into which the record is copied).

in cfg of db1 add the following line anywhere:

$db_after_add_record = 1;



in db.cgi, near end of sub add_record
change

&html_add_success();
}

to
if ($db_after_add_record) {
$after = &after_add_record;
}

&html_add_success($after); # $after is optional
}

i use $after to return a message that can be passed to html_add_success but you can omit it above and the following change to sub html_add_success if you want.

in html at the beginning of sub html_add_success, add the following line

my ($after) = $_[0];

then somewhere in the print code, insert $after to print the message (if applicable)

now the part that does the work! in the html file for db1, create a new sub:

sub after_add_record {
#---------------------------------------------
# update db2

# first get the new record
%rec = &get_record($in{$db_key});
my (%rec2) = %rec;
my ($db2_file_name) = "path and filename to db2";


#make any changes you want to the rec2, especially if record layout is not identical

#next, append the rec2 to db2
# use the code in sub add_record as a guide:
open (DB, ">>$db2_file_name") or &cgierr("error in add_record. unable to open database: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DB, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DB &join_encode(%rec2); # NOTE: I HAVEN'T TESTED THIS SO BE SURE TO BACKUP FIRST
close DB; # automatically removes file lock

}.

hopefully the above will help you get started!
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
deltaic, this is working great. below is a copy of the code as i used it.

# in the cfg file i put this:

# Full path to db2 databace.
$db_after_add_db = $db_script_path . "/../db/db2.db";
# Call for copy to db2.
$db_after_add_record = 1;


# in the db.cgi file i put this:

# right after this:
&auth_logging("added record: $in{$db_key}") if ($auth_logging);

# i put this:
if ($db_after_add_record) {
$after = &after_add_record;
}

&html_add_success;
}
else {

# but before this:

&html_add_failure($status);

# in the html.pl i put this:

sub after_add_record {
#---------------------------------------------
# update db2

# first get the new record
%rec = &get_record($in{$db_key});
my (%rec2) = %rec;
my ($db2_file_name) = "$db_after_add_db";


#make any changes you want to the rec2, especially if record layout is not identical

#next, append the rec2 to db2
# use the code in sub add_record as a guide:
open (DB, ">>$db2_file_name") or &cgierr("error in add_record. unable to open database: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DB, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DB &join_encode(%rec2); # NOTE: This dose work!.
close DB; # automatically removes file lock

}

# at the top of sub html_add_success i put this:

my ($after) = $_[0];



Can this concept work for modifying a record?

Thank you,
Ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
glad it's working! ok, assuming both databases have same key and no records are entered directly into db2 (because that would mess up automatic key numbering), you can certainly use similar code to modify record in db2.

set up the flag in cfg:
$db_after_modify_record = 1;

add similar code in sub modify record like you did in add

then:
sub after_modify_record {

# first get the new record
%rec = &get_record($in{$db_key});

my ($db2_file_name) = "$db_after_add_db";


#make any changes you want to the rec2, especially if record layout is not identical

#next, slurp db2
open (DBX, "<$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) { flock(DBX, 1); }
@lines = <DBX>; # Slurp the database into @lines..
close DBX;
# now look for matching record
$found = 0; # Make sure the record is in here!
LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; } # Skip and Remove blank lines
if ($line =~ /^#/) { $output .= $line; next LINE; } # Comment Line
chomp ($line);
@data = &split_decode($line);
# put your key position below
if ($data[$db2_key_pos] eq $in{$db_key}) {

%rec2 = %rec;
$output .= &join_encode(%rec2);
$found = 1;
}
else {
$output .= $line . "\n"; # else print regular line.
}
}

if (!$found) {
# if the record was not found, you might want to just add it here
$output .= &join_encode(%rec2);
}

open (DBX, ">$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DBX, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DBX $output;
close DBX; # automatically removes file lock

}

i didn't check syntax but i think this will work. BACKUP first!!!
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Hello deltaic,

The add feature works great. And the modify works as well with the exception of one thing.
Modify will update the record with no problem, but it will also add the entire 1st db. to the top of
db2. Below are the codes I added.

Any ideals as to what i did worng?

Thanks,
Ed-


***************************************************************************************************************


I added this to the cfg file.

# Call for modify to master.
$db_after_modify_record = 1;


***************************************************************************************************************

I added 3 line at the bottom of this sub routine.

sub modify_record {
# --------------------------------------------------------
# This routine does the actual modification of a record. It expects
# to find in %in a record that is already in the database, and will
# rewrite the database with the new entry. First it checks to make
# sure that the modified record is ok with validate record.
# It then goes through the database looking for the right record to
# modify, if found, it prints out the modified record, and returns
# the user to a success page. Otherwise the user is returned to an error
# page with a reason why.

my ($status, $line, @lines, @data, $output, $found, $restricted);
my (%rec) = @_;
$status = &validate_record(%in); # Check to make sure the modifications are ok!

if ($status eq "ok") {
open (DB, "<$db_file_name") or &cgierr("error in modify_records. unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
@lines = <DB>; # Slurp the database into @lines..
close DB;

($restricted = 1) if ($auth_modify_own and !$per_admin);

$found = 0; # Make sure the record is in here!
LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; } # Skip and Remove blank lines
if ($line =~ /^#/) { $output .= $line; next LINE; } # Comment Line
chomp ($line);
@data = &split_decode($line);
($output .= "$line\n" and next LINE) if ($restricted and ($db_userid ne $data[$auth_user_field]));
if ($data[$db_key_pos] eq $in{$db_key}) {
# If we have userid's and this is not an admin, then we force the record to keep it's own
# userid.
if ($auth_user_field >= 0 and (!$per_admin or !$in{$db_cols[$auth_user_field]})) {
$in{$db_cols[$auth_user_field]} = $data[$auth_user_field];
}
$output .= &join_encode(%in);
$found = 1;
}
else {
$output .= $line . "\n"; # else print regular line.
}
}
if ($found) {
open (DB, ">$db_file_name") or &cgierr("error in modify_records. unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DB, 2) or &cgierr("unable to get exclusive lock on $db_file_name.\nReason: $!");
}
print DB $output;
close DB; # automatically removes file lock

&auth_logging("modified record: $in{$db_key}") if ($auth_logging);
&create_copy_file;
if ($db_after_modify_record) { #Added this line
$after = &after_modify_record; #Added this line
} #Added this line

&html_modify_success;
}
else {
&html_modify_failure("$in{$db_key} (can't find requested record)");
}
}
else {
&html_modify_failure($status); # Validation Error
}
}


***************************************************************************************************************

I added this sub routine to my html.pl


sub after_modify_record {

# first get the new record
%rec = &get_record($in{$db_key});

my ($db2_file_name) = "$db_after_add_db";


#make any changes you want to the rec2, especially if record layout is not identical

#next, slurp db2
open (DBX, "<$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) { flock(DBX, 1); }
@lines = <DBX>; # Slurp the database into @lines..
close DBX;
# now look for matching record
$found = 0; # Make sure the record is in here!
LINE: foreach $line (@lines) {
if ($line =~ /^$/) { next LINE; } # Skip and Remove blank lines
if ($line =~ /^#/) { $output .= $line; next LINE; } # Comment Line
chomp ($line);
@data = &split_decode($line);
# put your key position below
if ($data[$db2_key_pos] eq $in{$db_key}) {

%rec2 = %rec;
$output .= &join_encode(%rec2);
$found = 1;
}
else {
$output .= $line . "\n"; # else print regular line.
}
}

if (!$found) {
# if the record was not found, you might want to just add it here
$output .= &join_encode(%rec2);
}

open (DBX, ">$db2_file_name") or &cgierr("error in modify_records. unable to open db file: $db2_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DBX, 2) or &cgierr("unable to get exclusive lock on $db2_file_name.\nReason: $!");
}
print DBX $output;
close DBX; # automatically removes file lock

}

***************************************************************************************************************


sub html_modify_success {
# --------------------------------------------------------
# The user has successfully modified a record, and this page will
# display the modified record as a confirmation.
my ($after) = $_[0]; #Added this line
&html_print_headers;
print qq|
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
Hello deltaic,

Follow up to my last posting:

I found the problem, it was in a code i added for making backup of the db. i removed it from the modify sub in the db.cgi
and your code worked perfectly!

Thank you,
Ed


&auth_logging("modified record: $in{$db_key}") if ($auth_logging);
&create_copy_file; #removed this line
if ($db_after_modify_record) { #Added this line
$after = &after_modify_record; #Added this line
} #Added this line

&html_modify_success;
}
else {
&html_modify_failure("$in{$db_key} (can't find requested record)");
}
}
else {
&html_modify_failure($status); # Validation Error
}
}
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
great! that was an easy fix, wasn't it?
just curious -- with so many records in your db2, is there a noticeable delay when you add or modify records in db1?

Last edited by:

delicia: Oct 17, 2015, 6:58 AM
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Not at all, here are the stats for my data bases:
DB1 (main) as of today has 907 records.
DB2 (master) as of today has 10350 plus the 907 (copies) from the main giving me a total of 11257 records.
I also have the main db making a backup every time a new record is added. with all this going on there is no
delay in the transaction.

Ed-
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Hello delicia, do you know of a way to make this work with the multi modify mod?

Ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
as each individual record is modified, you would modify the related record so you would need to modify the following sub:

Code:
sub modify_mult_record {
# --------------------------------------------------------
# This routine will update multiple records at once. It expects
# to find in %in a series of records to update. They will be of the
# form field_name-key.
#
my ($key, %modify_list, %modify_rec, $rec_to_modify, @data, $key,
$errstr, $succstr, $output, %errors);

# First let's pick which records to modify and then separate them and store
# them in their own hashes.
$rec_to_modify = 0;
foreach $key (keys %in) { # Build a hash of keys to modify.
if ($in{$key} eq "modify") {
$modify_list{$key} = 1;
$rec_to_modify = 1;
}
($key =~ /^(.*)-(.+)$/) and (${$modify_rec{$2}}{$1} = $in{$key});
}
# Choke if we don't have anything to do.
$rec_to_modify or (&html_modify_failure("no records specified.") and return);

open (DB, "<$db_file_name") or &cgierr("error in modify_records. unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) { flock(DB, 1); }
LINE: while (<DB>) {
(/^#/) and ($output .= $_ and next LINE);
(/^\s*$/) and next LINE;
chomp;
@data = &split_decode($_);
$key = $data[$db_key_pos];

# Now we check if this record is something we want to modify. If so, then
# we make sure the new record is ok, if so we replace it.
if ($modify_list{$key}) {
$status = &validate_multiple_records(%{$modify_rec{$key}});
if ($status eq "ok") {
$output .= &join_encode(%{$modify_rec{$key}});
$modify_list{$key} = 0;
### ok here's where we have a record that was successfully modified
### so you need to put the call to modify the record in the other database
### $key is the key to pass
&after_modify_record($key); # you already have this sub
# (to modify a single record), right?
# look at it and see if it makes sense
# if not, create a new similar sub

}
else {
$errors{$key} = $status;
$output .= "$_\n";
}
}
else {
$output .= "$_\n";
}
}
close DB;

# Reprint out the database.
open (DB, ">$db_file_name") or &cgierr("error in modify_records. unable to open db file: $db_file_name.\nReason: $!");
if ($db_use_flock) {
flock(DB, 2) or &cgierr("unable to get exclusive lock on $db_file_name.\nReason: $!");
}
print DB $output;
close DB; # automatically removes file lock

# Let's display an error message if we were unable to modify a record
# for some reason.
foreach $key (keys %modify_list) {
if ($modify_list{$key}) {
($errors{$key}) ?
($errstr .= "$key: $errors{$key}") :
($errstr .= "$key: not found");
}
else {
$succstr .= qq~<a href="$db_script_link_url&view_records=1&$db_key=$key&ww=1">$key</a>,~;
}
}
chop($succstr); # Remove trailing delimeter

&html_modify_mult_results($succstr, $errstr);
}
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
note that &after_modify_record is not returning a status, so it's important that you're not doing anything that could cause an error
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
just looked at your sub after_modify. you may need a new sub after_modify_multi because of the different way the modified record key is referenced. after_modify uses $in. before creating a new sub, try this instead of what i wrote earlier:

Code:
my (%save_in) = %in;
$in{$db_key} = $key;
&after_modify_record($in{$db_key});
%in = %save_in;
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Sweet, we are moving in the right direction. I tried to do 2 records at the same time the first the changes were made to both db, but the 2nd one it deleted the record from the first db and no change to the main db.

Ed-
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
not seeing all the code at once makes it difficult for me to figure out what's happening. looking back over the modify_multiple_records, i see that it isn't slurping the database but is reading it one record at a time. i'm wondering if we're using some of the same variable names such as line and DB and getting things mixed up. i would try changing all the variables in your sub that writes changes to the other db and just append a 2 to the name, like DB2, @lines2, $line2, etc.
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
I wondering if we could take another look at this? I got side tracked but would like to finish it. Attached is a file that has the difference sub routines we made changes to. You should have the complete html.pl, db.cgi and config file for "adsdb" that I sent you earlier in another post.

What works:

I can add a record and it will write to the "adsdb.db and the master.db
I can modify a record and it will modify to the "adsdb.db and the master.db



Problem:

When I modify mutable records, the adsdb.db changes are made, but the master.db changes have not.
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
i'm enduring major renovations to my house right now. i can barely think for all the sanders, drills, nail guns, etc! probably won't have a chance to look at this before the weekend.

suggestion: in modify multiple it's looking at keys-in or something like that. set up a keys2_in that's a copy of it. then after you do all the processing in the main db, test for if ($db_after_modify) or whatever we called that flag. then sort of duplicate all the processing for the main db in the second db. hope this makes sense and gives you some direction till i can think!

let me know your progress.
Quote Reply
Re: [delicia] Pulling data form 1 db to another. In reply to
Thanks delicia, if you don't mind I'll wait till you can help, this is a little more than i can do.
Quote Reply
Re: [knue] Pulling data form 1 db to another. In reply to
i updated code for sub modify_mult_record. see attachment. i am not validating records in db2 therefore not showing success or error for db2. backup before testing!

Last edited by:

delicia: Dec 19, 2015, 7:15 AM
> >