#!/opt/nsperl/bin/perl

# my use 
use File::Basename;
use IO::File;
use Cwd qw(realpath);

=pod
ObjectMeta
ObjectMeta_UserAttributes
=cut

$tables_to_insert_data = ",objectmeta_userattributes,versionmeta," ;
##$tables_to_insert_data = ",versionmeta," ;

$FILEFILE = "/tmp/filefile" ;
$fho = new IO::File;
if ($fho->open("> $FILEFILE")) {

=pod
create tables for test
=cut

    print $fho "

DROP TABLE IF EXISTS `objectmeta_userattributes`;
CREATE TABLE `objectmeta_userattributes` (
  `gid` binary(18) default NULL,
  `oid` binary(18) default NULL,
  `objId` bigint(20) default NULL,
  `PROPERTY` varchar(100) default NULL,
  `NAME` varchar(100) default NULL,
  `VALUE` text,
  `DATATYPE` tinyint(2) default NULL,
  KEY `SK_ObjectMeta_userAttributes1` (`oid`),
  KEY `SK_ObjectMeta_userAttributes2` (`objId`),
  KEY `SK_oidname_1` (`oid`,`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `objectmeta_userattributes_test`;
CREATE TABLE `objectmeta_userattributes_test` (
  `gid` binary(18) default NULL,
  `oid` binary(18) default NULL,
  `objId` bigint(20) default NULL,
  `PROPERTY` varchar(100) default NULL,
  `NAME` varchar(100) default NULL,
  `VALUE` text,
  `DATATYPE` tinyint(2) default NULL,
  KEY `SK_ObjectMeta_userAttributes1` (`oid`),
  KEY `SK_ObjectMeta_userAttributes2` (`objId`),
  KEY `SK_oidname_1` (`oid`,`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



DROP TABLE IF EXISTS `objectmeta`;
CREATE TABLE `objectmeta` (
  `oid` binary(18) NOT NULL default '                  ',
  `classType` varchar(100) NOT NULL default '',
  `name` text,
  `objId` bigint(20) default NULL,
  `creationTime` bigint(20) default NULL,
  `createdBy` text,
  `lastModificationTime` bigint(20) default NULL,
  `lastModifiedBy` text,
  `description` text,
  `objectType` text,
  `objectState` smallint(6) default NULL,
  `objDirty` tinyint(1) default NULL,
  `nextVersionId` bigint(20) default NULL,
  `ObjectMeta_cchkout_VersionMeta` binary(18) default NULL,
  `ObjectMeta_lchkin_VersionMeta` binary(18) default NULL,
  `ObjectMeta_VersionMeta_Array` longblob,
  PRIMARY KEY  (`oid`),
  UNIQUE KEY `uk_ObjectMeta` (`objId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `versionmeta`;
CREATE TABLE `versionmeta` (
  `oid` binary(18) NOT NULL default '                  ',
  `classType` varchar(100) NOT NULL default '',
  `versionId` bigint(20) default NULL,
  `creationTime` bigint(20) default NULL,
  `createdBy` text,
  `checkedInBy` text,
  `latestModificationTime` bigint(20) default NULL,
  `description` text,
  `objectURL` text,
  `versionState` smallint(6) default NULL,
  `VersionMeta_ObjectMeta` binary(18) default NULL,
  `childversion_parentversion` binary(18) default NULL,
  `ownerRead` tinyint(1) default '0',
  `ownerWrite` tinyint(1) default '0',
  `adminRead` tinyint(1) default '0',
  `adminWrite` tinyint(1) default '0',
  `otherRead` tinyint(1) default '0',
  `otherWrite` tinyint(1) default '0',
  `groupRead` tinyint(1) default '0',
  `groupWrite` tinyint(1) default '0',
  `isAutoSaved` tinyint(1) default '0',
  `object_reference` bigint(20) default '0',
  PRIMARY KEY  (`oid`),
  UNIQUE KEY `uk_VersionMeta` (`versionId`,`VersionMeta_ObjectMeta`),
  KEY `sk_versionmeta_1` (`versionState`),
  KEY `sk_versionmeta_2` (`VersionMeta_ObjectMeta`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



" ;

=pod
query to test
=cut

    print $fho "

SELECT
ObjectMeta.name AS objname
, IFNULL(NV1.value,'NULL')  AS \"projectName\"
, ObjectMeta.objId
, IFNULL(NV2.value,'NULL')  AS \"NEType\"
, ObjectMeta.objectState
, ObjectMeta.lastModifiedBy
, ObjectMeta.lastModificationTime
, ObjectMeta.description
, IFNULL(NV3.value,'NULL')  AS \"projectName2\"
, IFNULL(NV4.value,'NULL')  AS \"NEType2\"
, IFNULL(NV5.value,'NULL')  AS \"NEType3\"
, IFNULL(NV6.value,'NULL')  AS \"NEType4\"
, IFNULL(NV7.value,'NULL')  AS \"NEType5\"
, IFNULL(NV8.value,'NULL')  AS \"NEType6\"
, IFNULL(NV9.value,'NULL')  AS \"NEType7\"
, IFNULL(NV10.value,'NULL')  AS \"NEType8\"
, IFNULL(NV11.value,'NULL')  AS \"NEType9\"
, ObjectMeta.creationTime , ObjectMeta.objectType , ObjectMeta.createdBy

FROM ObjectMeta

LEFT JOIN ObjectMeta_UserAttributes AS NV1 ON ObjectMeta.oid=NV1.oid and NV1.name = 'projectName'
LEFT JOIN ObjectMeta_UserAttributes AS NV2 ON ObjectMeta.oid=NV2.oid and NV2.name = 'NEType'
LEFT JOIN ObjectMeta_UserAttributes AS NV3 ON ObjectMeta.oid=NV3.oid and NV3.name = 'projectName2'
LEFT JOIN ObjectMeta_UserAttributes AS NV4 ON ObjectMeta.oid=NV4.oid and NV4.name = 'projectName3'
LEFT JOIN ObjectMeta_UserAttributes AS NV5 ON ObjectMeta.oid=NV5.oid and NV5.name = 'projectName4'
LEFT JOIN ObjectMeta_UserAttributes AS NV6 ON ObjectMeta.oid=NV6.oid and NV6.name = 'projectName5'
LEFT JOIN ObjectMeta_UserAttributes AS NV7 ON ObjectMeta.oid=NV7.oid and NV7.name = 'projectName6'
LEFT JOIN ObjectMeta_UserAttributes AS NV8 ON ObjectMeta.oid=NV8.oid and NV8.name = 'projectName7'
LEFT JOIN ObjectMeta_UserAttributes AS NV9 ON ObjectMeta.oid=NV9.oid and NV9.name = 'projectName8'
LEFT JOIN ObjectMeta_UserAttributes AS NV10 ON ObjectMeta.oid=NV10.oid and NV10.name = 'projectName9'
LEFT JOIN ObjectMeta_UserAttributes AS NV11 ON ObjectMeta.oid=NV11.oid and NV11.name = 'projectName10'

ORDER by 7 desc;

" ;

=pod
,
VersionMeta
WHERE ObjectMeta.oid = VersionMeta.versionmeta_objectmeta and VersionMeta.createdby = 'netsmart'
                                                          and VersionMeta.versionState = '1'
=cut

    $fho->close;
    system ("cat $FILEFILE") ;
} else {
    print "\n Error opening file $FILEFILE ... \n" ;
}


=pod
insert data in `objectmeta_userattributes`; table
=cut

if ($tables_to_insert_data =~ /,objectmeta_userattributes,/i)
{

$FILEFILE = "/tmp/file_omua_insert" ;
$fho = new IO::File;
if ($fho->open("> $FILEFILE")) {


$count = 0 ;
$num_rec_for_insert = 2000 ;
print $fho "INSERT INTO `objectmeta_userattributes` VALUES ";
foreach $i1 ('1' .. '9','A' .. 'F') {
foreach $i2 ('1' .. '9','A' .. 'F') {
foreach $i3 ('1' .. '9','A' .. 'F') {
foreach $i4 ('1' .. '9','A' .. 'F') {
    $count++;
    if ( $count % $num_rec_for_insert == 0 ) {
        $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."FFFFFFFFFFFFFF'" ;
        print $fho "($oid,$oid,NULL,'','projectName','NDPT',0) ;
INSERT INTO `objectmeta_userattributes` VALUES ";
    }
    $i5='1' ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."226710AAF59999'" ;
    print $fho "($oid,$oid,NULL,'','projectName','NDPT',0),";

    $i5='2' ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."226710AAF59999'" ;
    print $fho "($oid,$oid,NULL,'','projectName2','NDPT',0),";

    $i5='3' ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."226710AAF59999'" ;
    print $fho "($oid,$oid,NULL,'','projectName3','NDPT',0),";

    $i5='4' ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."226710AAF59999'" ;
    print $fho "($oid,$oid,NULL,'','projectName4','NDPT',0),";

    $i5='5' ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."226710AAF59999'" ;
    print $fho "($oid,$oid,NULL,'','projectName5','NDPT',0),";

    $i5='6' ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."226710AAF59999'" ;
    print $fho "($oid,$oid,NULL,'','projectName6','NDPT',0),";
}
}
}
}
print $fho " ('FFFFFFFFFFFFFFFFFF','FFFFFFFFFFFFFFFFFF',NULL,'','projectName','NDPT',0) ; " ;



    $fho->close;
    system ("more $FILEFILE") ;
} else {
    print "\n Error opening file $FILEFILE ... \n" ;
}

}



if ($tables_to_insert_data =~ /,objectmeta,/i)
{

$FILEFILE = "/tmp/file_om_insert" ;
$fho = new IO::File;
if ($fho->open("> $FILEFILE")) {



$count = 0 ;
$num_rec_for_insert = 25 ;
print $fho "INSERT INTO `objectmeta` VALUES ";
foreach $i1 ('1' .. '9','A' .. 'F') {
foreach $i2 ('1' .. '9','A' .. 'F') {
foreach $i3 ('1' .. '9','A' .. 'F') {
foreach $i4 ('1' .. '9','A' .. 'F') {
foreach $i5 ('1' .. '9','A' .. 'F') {
    $count++;
    $oid = "'$i1"."$i2"."$i3"."$i4"."226710AAF59999'" ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."26710AAF59999'" ;

    print $fho "($oid,'framework.versioning.ObjectMetaDBImpl','pathring009',$count,1194977782456,'netsmart',1194977782456,'netsmart','','XML',1,0,0,'hhhhhhhhhhhhhhhhhh','jjjjjjjjjjjjjjjjjj','83A003EB01163A39EAECDF15000100000000,')"  ;

    if ( $count % $num_rec_for_insert == 0 ) {
        print $fho " ;
INSERT INTO `objectmeta` VALUES " ;
    } else {
        print $fho "," ;
    }
}
}
}
}
}
print $fho "('FFFFFFFFFFFFFFFFFF','framework.versioning.ObjectMetaDBImpl','pathring009',11509,1194977782456,'netsmart',1194977782456,'netsmart','','XML',1,0,0,'','','83A003EB01163A39EAECDF15000100000000,') ; " ;

    $fho->close;
    system ("more $FILEFILE") ;
} else {
    print "\n Error opening file $FILEFILE ... \n" ;
}

}


if ($tables_to_insert_data =~ /versionmeta/i)
{

$FILEFILE = "/tmp/file_vm_insert" ;
$fho = new IO::File;
if ($fho->open("> $FILEFILE")) {



$count = 0 ;
$num_rec_for_insert = 25 ;
print $fho "INSERT INTO `versionmeta` VALUES ";
foreach $i1 ('1' .. '9','A' .. 'F') {
foreach $i2 ('1' .. '9','A' .. 'F') {
foreach $i3 ('1' .. '9','A' .. 'F') {
foreach $i4 ('1' .. '9','A' .. 'F') {
foreach $i5 ('1' .. '9','A' .. 'F') {
    $count++;
    $oid = "'$i1"."$i2"."$i3"."$i4"."226710AAF59999'" ;
    $oid = "'$i1"."$i2"."$i3"."$i4"."$i5"."26710AAF59999'" ;


    print $fho "($oid,'framework.versioning.VersionMetaDBImpl',1,1194977794468,'netsmart','netsmart',1194977794468,'','',1,$oid,$oid,0,0,0,0,0,0,0,0,0,0)" ;

    if ( $count % $num_rec_for_insert == 0 ) {
        print $fho " ;
INSERT INTO `versionmeta` VALUES " ;
    } else {
        print $fho "," ;
    }
}
}
}
}
}
print $fho "('FFFFFFFFFFFFFFFFFF','framework.versioning.VersionMetaDBImpl',1,1194977794468,'netsmart','netsmart',1194977794468,'','',1,FFFFFFFFFFFFFFFFFF,FFFFFFFFFFFFFFFFFF,0,0,0,0,0,0,0,0,0,0) ; " ;

    $fho->close;
    system ("more $FILEFILE") ;
} else {
    print "\n Error opening file $FILEFILE ... \n" ;
}

}