Subversion Repositories computer_asset_manager_v1

<?php

   /*
    * This script populates the device_attrib table in CAMP v1 from a tab delimited text file. Filename may be passed as the
    * only parameter on the cli, or the system can be used as a filter, ie read from STDIN
    * 
    * File should have a minimum of four columns and the first line should be headers as follows (case sensitive):
    * device    - should have case sensitive full name of something in the device table
    * attribute - should have case sensitive full name of something in the attrib table
    * value     - What should be placed in the value column of device_attrib
    * All other columns are ignored.
    *
    * If an attribute name is not found, it is added to the attrib table. If device is not found, no entry is made.
    *
    * Script designed to run from cli.
    * 1. From cli, run 'php ./bulkLoadAttribs.php tabdelimfile.csv > out.sql
    * 2. (optional) open out.sql in text editor to review
    * 3. From cli, run 'mysql -u root -p camp < out.sql'
    *
    * If you're positive about the data you are importing, you can combine steps 2-4 as
    * php ./bulkLoadAttribs.php tabdelimfile.csv | mysql -u root -p camp
    *
    * Information should be loaded in database.
    */


   function trimArray( $array ) {
      for( $i = 0; $i < count( $array ); $i++ ) {
         $array[$i] = trim( $array[$i] );
      }
      return $array;
   }

   /*
    * given a csv file with the first line as headers, creates an array of hash where each hash entry has the header
    * as the key and the value as the value
    */
   function loadCSV( $filename, $delimiter = "\t", $maxLineLength = 1024 ) {
      $allRows = array();
      if ( ( $fh = fopen( $filename, 'r' ) ) !== false ) {
         $header = trimArray( fgetcsv( $fh, $maxLineLength, $delimiter ) );
         while ( $row = fgetcsv( $fh, $maxLineLength, $delimiter ) ) {
            //print "Untrimmed\n" . print_r( $row, true ) . "\nTrimmed\n" . print_r( trimArray( $row ), true ) . "\n" ; die;
            $allRows[] = array_combine( $header, trimArray( $row ) );
         } // while
      }
      return $allRows;
   }

   // don't trust this function for any data not well controlled
   function escapeforDB( $value ) {
      return "'" . mysql_escape_string( $value ) . "'";
   }

   // Name of file to import
   $filename = 'php://stdin';
   if ( isset( $argv[1] ) ) {
      $filename = $argv[1];
   }
   
   // get data from import file and load it into $data. Read loadCSV
   $data = loadCSV( $filename );

   // we'll put our SQL into an array, then dump it.
   $sql = array();
   $attributes = array(); // uniquely store our attributes here
   for ( $i = 0; $i < count( $data ); $i++ ) {  // go through each line and grab fields we need
      $device = escapeforDB( $data[$i]['device'] );
      $attrib = escapeforDB( $data[$i]['attribute'] );
      $value = escapeforDB( $data[$i]['value'] );

      // track attributes we have used so we can make sure they're in there
      $attributes[$attrib] = true;
      // standard SQL that does an insert if the value doesn't already exist.
      $sql[] =
         "insert into device_attrib ( device_id, attrib_id,value, added_date )
            select device_id,attrib_id, $value, now()
            from device join attrib
            where
               device.name = $device
               and attrib.name = $attrib
               and not exists (
                  select * from device_attrib join device using (device_id) join attrib using (attrib_id) where device.name = $device and attrib.name = $attrib
                  );";
   }

   // we have our distinct attributes, so let's insert anything which does not already exist
   // we'll just print them to STDOUT (first, so $sql can use any new values)
   foreach ( $attributes as $key => $value ) {
      print "insert into attrib( name,added_date ) select $key, now() from dual where not exists (select * from attrib where name = $key);\n";
   }
   // our actual load
   print implode( "\n", $sql ) . "\n";
   print "select
   attrib.name attrib_name,
   device_attrib.value value,
   device_attrib.added_date,
   device.device_id,
   attrib.attrib_id,
   device_attrib_id
from
   device_attrib
   join attrib using (attrib_id)
   join device using (device_id)
where
   device.name = $device
order by
   attrib.name;
"


?>