Subversion Repositories computer_asset_manager_v1

<?php

   /*

    * This script populates the device_attrib table in CAMP v1 from a tab delimited text file.

    * 

    * 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. Put full path/filename in the $filename variable below

    * 2. From cli, run 'php ./bulkLoadAttribs.php > out.sql

    * 3. (optional) open out.sql in text editor to review

    * 4. 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 | 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 = '/home/rodolico/www/web/computer_asset_manager_v1/device_attributes.csv';

   // 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";

?>