MET Configuration

Contents

  1. MET Configuration
  2. MET Project Protocol
  3. Updating Aspect Data
  4. Uploading files for Matrics
    1. Actualize the MDL to Metadata Upload File Converter
    2. Creating MetaData Upload File from Master Document List of Matrics
    3. Move Document Files to the Correct Place

MET Project Protocol

  1. MetProtocol - Calendar of activities and planning notes for the MET project. Please keep this page up to date.

Updating Aspect Data

Make backup before this procedure, because not anly the AspetRef and AspectValues but the Classifications table will be changed!

  1. Export Matrics's Excel file cfg/met/site/data/Management_Aspects_Tree.xls as a tab separated CSV file. See 

    cfg/met/site/data/Management_Aspects_Tree.csv
    It is VERY IMPORTANT, that
    • the aspects must have the same sequence as before (sorting can be changed in the column sortOrder)
    • the code values for existing aspect values must remain unchanged, otherwise the classification information will be destroyed with this procedure.
  2. Create the SQL file which creates the data and run it through an other SQL script: 
    cd cfg/met/site/data
../../../../app/etc/genAspectData.pl -t Management_Aspects_Tree.csv > genAspectData.sql
mysql -uroot -psqladmin met121prod
    source update_aspects.sql; -- it reads genAspectData.sql also

cd ../../../../app/etc
./genMeta.pl -c -j
  3. Delete cache in your browser if the aspect data appears strange in Document/New.

Uploading files for Matrics

Actualize the MDL to Metadata Upload File Converter

The Master Document List (MDL) to Metadata Upload File (MUF) converter is: 

cfg/met/site/data/convertMetaData.pl

Suppose that the Drawings record of a new revision is already in the database. If the contractIds in the Metadata Upload File (MUF) and in the database differ, the Metadata Uploader returns an error. Therefore the correct contractId must be set in MUF for all existing documents.

Change the function getContract in the converter program. Replace the body of the switch command with the actual list. See the function header, how to select the list form the database.

Creating MetaData Upload File from Master Document List of Matrics

  1. Remove all unnecessary columns behind column 'Original File Name if Different' (Excel column AQ)
  2. Perform some check and changes to avoid unnecessary iterations
    • Change 'METS-' to 'METS' in column 'SOURCE' (Excel column Q)
    • Remove 'Rev ' in column 'Appendix Revision' (Excel column AK)
    • Check that the header is the first line
    • Change date fields into ISO format (YYYY-MM-DD)
  3. Export the MDL into a TAB separated CSV file: "cfg/met/site/data/MDLDoclist_YYMMDD.csv". Use character coding UTF-8.
  4. Remove unnecessary trailing lines
  5. Convert MDL to MUF: 
    MUF_FILE=MUF_YYMMDD
cd cfg/met/site/data
./convertMetaData.pl < MDLDoclist_YYMMDD.csv > $MUF_FILE.txt
  6. Check that at least the drawingCodes are correct: 
    awk '{ printf "%s\t%s\n", NR, $1 }' $MUF_FILE.txt
  7. Split the new upload file into separate files based on contract code ("A", "B", "C", "D"), and determine the maximum number of lines in a file. 
    awk 'BEGIN { FS="\t" } { if ( $6 == "A" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_A.txt
awk 'BEGIN { FS="\t" } { if ( $6 == "B" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_B.txt
awk 'BEGIN { FS="\t" } { if ( $6 == "C" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_C.txt
awk 'BEGIN { FS="\t" } { if ( $6 == "D" || NR == 1 ) print $0 }' $MUF_FILE.txt > ${MUF_FILE}_D.txt
wc -l ${MUF_FILE}_?.txt
  8. Split every file which have more then 100 lines into separate upload files, e.g.: 
    split100lines.sh MUF_091221_A.txt

  9. Load up all files into the DrawMGT system with the menu Documents/Upload Metadata

Move Document Files to the Correct Place

Note that this procedure is for mysql version 5.x.

In the case of mysql version 4.x the SQL command "load data local" functions only without "local", i.e. the file is read by the server, not by the client. It means, that UTF8 files can not be loaded correctly. The files '/tmp/filelist.txt' and '/tmp/MDLFileList.csv' must be converted to SQL files and source should be used instead of load.

  1. Create a list of all available files. The file is a TAB separated CSV fájl, which can be loaded into the database for further processing. The file contains the directory (relative), the filename, the filename without extension and the size of the file. 
    BASEDIR=/home/met/www/html/prod                          # SET THE APPROPRIATE DIRECTORY
SITE=met                                                 # SET THE APPROPRIATE SITE
INSTANCE=lu.softxs.ch-prod                               # SET THE APPROPRIATE INSTANCE
DATABASE=met120prod                                      # SET THE APPROPRIATE database

DATADIR=$BASEDIR/cfg/met/site/data                       # set your site/data directory
cd /var/www/html/dev/met120prod                          # go to the directory where the files are
$DATADIR/genFileList.sh data_* > /tmp/filelist.txt       # list all directories as argument
  2. Load the file into a temp table in the database. Note that do not leave mysql until the end of the process, because temp table is used and it is lost on exit from mysql. 
    mysql -uroot -psqladmin $DATABASE
 set names utf8;
 create temporary table FileList ( n serial, dir char(255), fileWithExt char(128),
  fileWithoutExt char(128), size int ) collate utf8_general_ci;
 load data local infile '/tmp/filelist.txt' into table FileList(dir, fileWithExt, fileWithoutExt, size);
  3. Create a CSV file from the MDL, containing the following columns: 'Doc. No.', 'Doc. Part', 'Appendix Revision', 'Coded File Name', 'Original File Name if Different'. Remove the header line(s) also. Load this file also into a temp table in the database. 
     create temporary table MDLFileList ( docNo char(5), docPart char(5), rev char(1),
   codedFileName char(128), origFileName char(172) ) collate utf8_general_ci;

 load data local infile 'MDLFileList.csv' into table MDLFileList;

 update MDLFileList set rev = "1" where rev is null or length(rev)=0;
 update MDLFileList set docPart=null where length(docPart)=0;
  4. Filter out all revisionId from filenames in directory 'drawings'. Use an other terminal, do not exit from mysql. 
    cd var/${SITE}/${INSTANCE}/data/drawings
find . -type f | awk -F- '{ print $6 }' | sort -n > /tmp/revId.txt
  5. Load the revisionId list into a temp table in mysql 
     create temporary table exclRevId( revId int ) collate utf8_general_ci;

 load data local infile '/tmp/revId.txt' into table exclRevId;
  6. Create list of revisionIds and corresponding filenumber 
     create temporary table revFile( revId int, fileNo int, primary key( revId ) ) collate utf8_general_ci;

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithExt = m.origFileName;   -- Original filename with extension

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithoutExt = m.origFileName;   -- Original filename without extension

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithExt = m.codedFileName;   -- Coded filename with extension

 insert ignore into revFile select r.revisionId, f.n 
  from Drawings d, Revisions r, MDLFileList m, FileList f
  where d.drawingId = r.drawingId
  and d.drawingCode like concat( "%-METS-00", coalesce(concat(m.docNo, "-", upper(m.docPart)),m.docNo))
  and r.revisionCode = concat( "Rev-", m.rev )
  and r.revisionId not in (select revId
  from exclRevId) and f.fileWithoutExt = m.codedFileName;   -- Coded filename without extension
  7. Update file name, file size, file typ in table Revisions 
     update Revisions, FileList, revFile set publishFilename = fileWithExt, publishFilesize = FileList.size 
  where Revisions.revisionId = revFile.revId
  and FileList.n = revFile.fileNo;
 update Revisions, FileTypeRef set publishFileTypeId = fileTypeId
  where publishFileTypeId is null
  and publishFilename like concat( '%', FileTypeRef.fileExtension );

  8. Create a bunch of cp commands into the file /tmp/cp.sh 

     select
   -- drawingCode,
   concat(
     'cp ',
     -- From original name
       '"$SRC_DIR"/\'', FileList.dir, '/', replace(publishFileName,"'", "'\"'\"'"), '\' ',
     -- to internal filename $ROOT_DIR/contractId/{internalName}
       '"$DST_DIR"/', contractId, '/',
       17,       '-',    -- site ID (MET)
       0,  '-',          -- projectId
       contractId, '-',  -- contractId
       2,  '-',          -- objectTypeId (Revision)
       4,  '-',          -- objectSubTypeId (PublishFile)
       revisionId, '-',  -- objectId
       publishFileTypeId -- fileTypeId
   ) 'Internal Filename'
 from
   Drawings, Revisions, revFile, FileList
 where
   Drawings.drawingId = Revisions.drawingId and
   Revisions.revisionId = revFile.revId and
   revFile.fileNo = FileList.n and
   Revisions.revisionId = revFile.revId
 into outfile '/tmp/cp.sh';
  9. Set environment variables SRC_DIR and DST_DIR. Note that SRCDIR must be the same as your work directoy above in step 1 at creating the /tmp/filelist.txt. 
    SRC_DIR=/home/met                     # set it appropriately
DST_DIR=$BASEDIR/var/${SITE}/${INSTANCE}/data/drawings

. /tmp/cp.sh

MetConfiguration (last edited 2010-09-20 14:10:27 by 195)

Copyright 2008-2014, SoftXS GmbH, Switzerland