From 07eb5eccfd5260722ca74162e189c2bd5e8750a4 Mon Sep 17 00:00:00 2001
From: Ian Gilfillan <ian@greenman.co.za>
Date: Thu, 14 Jul 2016 21:25:35 +0000
Subject: Modify script to allow clean updates, verbose and safe options,
 update usb and pci vendors

---
 scripts/vendorid/insert_vendors.php | 104 ++++++++++++++++++++++++++++++------
 1 file changed, 89 insertions(+), 15 deletions(-)

(limited to 'scripts/vendorid/insert_vendors.php')

diff --git a/scripts/vendorid/insert_vendors.php b/scripts/vendorid/insert_vendors.php
index f2d6eeb..630a88d 100644
--- a/scripts/vendorid/insert_vendors.php
+++ b/scripts/vendorid/insert_vendors.php
@@ -2,8 +2,28 @@
 <?php
 
 //created by Antonio Gallo (tonicucoz@yahoo.com) and P. J. McDermott
+//Additional changes by Ian Gilfillan
 // this script is in the Public Domain
 
+
+//Accepts on argument, either -v or -s
+//-v for verbose, which shows all the inserts/updates that are being made
+//-s for safe, which shows all the inserts/updates to be made without actually making them
+if (isset($argv[1])) {
+	if ($argv[1]=='-v') {
+		$verbose=1;
+		$change=1;
+	}
+	elseif ($argv[1]=='-s'){
+		$change=0;
+		$verbose=1;
+	}
+}
+else {
+	$verbose=0;
+	$change=1;
+}
+
 include("connect.php");
 include("functions.php");
 
@@ -16,13 +36,40 @@ while (($line = fgets($ids_fp)) !== false) {
 		$vendorid = $matches[1];
 		$full_name = sanitizeDb(decode_soft($matches[2]));
 		$clean_name = sanitizeDb(decode($matches[2]));
-// 		echo "$vendorid $full_name $clean_name".PHP_EOL;
-		//insert
-		$query = "INSERT INTO vendors (bus,vendorid,clean_name,full_name) VALUES ('PCI','$vendorid','$clean_name','$full_name');";
-		DB::$instance->query($query);
-		//update
-		$query = "UPDATE vendors SET clean_name='$clean_name', full_name='$full_name' WHERE vendorid='$vendorid' AND bus='PCI';";
-		DB::$instance->query($query);
+
+		// Check if record exists, insert if not
+		$query = "SELECT vendorid, clean_name FROM vendors WHERE vendorid='$vendorid' AND bus='PCI'";
+		if ($result = DB::$instance->query($query)) {
+			$found = $result->num_rows;
+		}
+		if ($found == 0) {	
+			//insert
+			$query = "INSERT INTO vendors (bus,vendorid,clean_name,full_name) VALUES ('PCI','$vendorid','$clean_name','$full_name');";
+			if ($verbose) {
+				echo "\n Adding record: PCI $vendorid $clean_name $full_name";
+			}
+			if ($change) {
+				DB::$instance->query($query);
+			}
+		}
+
+		// Check if record has changed, update if so
+		$query = "SELECT vendorid, clean_name, full_name FROM vendors WHERE vendorid='$vendorid' AND (BINARY clean_name!='$clean_name' OR BINARY full_name!='$full_name') AND bus='PCI'";
+		if ($result = DB::$instance->query($query)) {
+			$found = $result->num_rows;
+		}
+		if ($found > 0) {
+			$row = $result->fetch_assoc();
+			//update
+			$query = "UPDATE vendors SET clean_name='$clean_name', full_name='$full_name' WHERE vendorid='$vendorid' AND bus='PCI';";
+			if ($verbose) {
+				echo "\n Updating record - old value: PCI " . $row['clean_name'] . " " . $row['full_name'];
+				echo "\n Updating record - new value: PCI $clean_name $full_name";
+			}
+			if ($change) {
+				DB::$instance->query($query);
+			}
+		}
 	}
 }
 
@@ -37,14 +84,41 @@ while (($line = fgets($ids_fp)) !== false) {
 		$vendorid = $matches[1];
 		$full_name = sanitizeDb(decode_soft($matches[2]));
 		$clean_name = sanitizeDb(decode($matches[2]));
-// 		echo "$vendorid $full_name $clean_name".PHP_EOL;
-		//insert
-		$query = "INSERT INTO vendors (bus,vendorid,clean_name,full_name) VALUES ('USB','$vendorid','$clean_name','$full_name');";
-		DB::$instance->query($query);
-		//update
-		$query = "UPDATE vendors SET clean_name='$clean_name', full_name='$full_name' WHERE vendorid='$vendorid' AND bus='USB';";
-		DB::$instance->query($query);
+
+                $query = "SELECT vendorid FROM vendors WHERE vendorid='$vendorid' AND bus='USB'";
+                if ($result = DB::$instance->query($query)) {
+                        $found = $result->num_rows;
+                }
+                if ($found == 0) {
+                        //insert
+			$query = "INSERT INTO vendors (bus,vendorid,clean_name,full_name) VALUES ('USB','$vendorid','$clean_name','$full_name');";
+			if ($verbose) {
+				echo "\n Adding record: USB $vendorid $clean_name $full_name";
+                        }
+			if ($change) {
+				DB::$instance->query($query);
+			}
+                }
+
+
+                // Check if record has changed, update if so
+                $query = "SELECT vendorid, clean_name, full_name FROM vendors WHERE vendorid='$vendorid' AND (BINARY clean_name!='$clean_name' OR BINARY full_name!='$full_name') AND bus='USB'";
+                if ($result = DB::$instance->query($query)) {
+                        $found = $result->num_rows;
+                }
+                if ($found >0) {
+			$row = $result->fetch_assoc();
+			$query = "UPDATE vendors SET clean_name='$clean_name', full_name='$full_name' WHERE vendorid='$vendorid' AND bus='USB';";
+			if ($verbose) {
+				echo "\n Updating record - old value: USB " . $row['clean_name'] . " " . $row['full_name'];
+                        	echo "\n Updating record - new value: USB $clean_name $full_name";
+                        }
+			if ($change) {
+				DB::$instance->query($query);
+			}
+                }
+
 	}
 }
 
-fclose($ids_fp);
\ No newline at end of file
+fclose($ids_fp);
-- 
cgit v1.2.3