#!/usr/bin/perl
# Copyright Leslie Richardson 2001-2020
# This file is part of Open Admin for Schools
my %lex = ('Update Meta' => 'Update Meta Table',
'Name' => 'Name',
'Field' => 'Field',
'Index'=> 'Index',
'No Updates required' => 'No Updates required',
'Eoy' => 'Eoy',
'Main' => 'Main',
'Removing field' => 'Removing field',
'Error' => 'Error',
);
my @tables = ( staff, staff_multi, student, student_inac, announce );
# These are the tables that have meta support built.
use DBI;
use CGI;
my $viewsize = 20; # default value for viewsize.
my $formtype = 'text'; # default form type.
# Read config variables
eval require "../../etc/admin.conf";
if ( $@ ) {
print $lex{Error}. ": $@ \n";
die $lex{Error}. ": $@\n";
}
my $q = new CGI;
print $q->header( -charset, $charset );
my %arr = $q->Vars;
print qq{$doctype\n$lex{'Update Meta'}
\n};
print qq{[ $lex{Main} |\n};
print qq{ $lex{Eoy} ]\n};
print qq{
$lex{'Update Meta'}
\n};
my $dsn = "DBI:mysql:$dbase";
my $dbh = DBI->connect($dsn,$user,$password);
$dbh->{mysql_enable_utf8} = 1;
# Setup Checking for existence of a field record
$sth3 = $dbh->prepare("select id from meta
where tableid = ? and fieldid = ?");
# Loop through all tables
foreach my $table ( @tables ) {
print qq{
$table
\n};
my $index = 0;
my $first = 1;
my %fieldnames;
# Get fieldnames from meta table for this table
my $sth4 = $dbh->prepare("select fieldid from meta where tableid = ?");
$sth4->execute( $table );
while ( my $fieldname = $sth4->fetchrow ) {
$fieldnames{$fieldname} = 1;
}
$sth1 = $dbh->prepare("show columns from $table");
$sth1->execute;
if ($DBI::errstr){print $DBI::errstr; die "$DBI::errstr for table $table show\n"; }
print qq{
\n};
print qq{
$lex{Name}
$lex{Field}
};
print qq{
$lex{Index}
\n};
while ( my @cols = $sth1->fetchrow ) {
# Get size of the field
my $size = 0;
if ($cols[1] =~ /char\((.+)\)/ or $cols[1] =~ /varchar\((.+)\)/){
#print qq{The result is: $1 \n};
$size = $1;
}
# Check for presence in meta table...
$sth3->execute( $table, $cols[0] );
my $id = $sth3->fetchrow;
if ( $id ){ # field exists, just update index.
$sth2 = $dbh->prepare("update meta set arrayidx = '$index' where id = '$id'");
$sth2->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
delete $fieldnames{$cols[0]}; # remove this fieldname from the list
} else { # field doesn't exist in meta; add it.
print qq{
$cols[0]
$cols[1]
$index
\n};
$fieldname = "$cols[0]";
$sth2 = $dbh->prepare("insert into meta ( dbaseid, tableid, fieldid,
datatype, arrayidx, size, fieldname, formtype, viewsize )
values('school','$table','$cols[0]','$cols[1]','$index','$size',
'$fieldname','$formtype','$viewsize')");
$sth2->execute;
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
$first = 0;
}
$index++;
} # End of field loop.
# Test for any existing values left in %fieldnames. If any, delete from meta table.
foreach my $fieldname ( keys %fieldnames ) {
my $sth5 = $dbh->prepare("delete from meta where tableid = ? and fieldid = ?");
$sth5->execute( $table, $fieldname );
if ($DBI::errstr){ print $DBI::errstr; die $DBI::errstr; }
print qq{