tyty16/sqldeadcolumnfinder

Finds columns that are null or only have one unique value

SQLDeadColumnFinder

A php class designed to find "dead" columns in a SQL database (Null or only 1 unique value)., _(*1)

Installation

This library requires PHP 5.1 or later, but it is recommended to use the latest version of PHP. It does not have any other dependencies., _(*2)

It can either be autoloaded and installed via Composer tyty16/sqldeadcolumnfinder, or can be downloaded on its own., _(*3)

Getting Started

Instantiation

Instantiate the SQLDeadColumnFinder class with the pdo connection along with the name of the database you would like to check. Optional parameters include:, _(*4)

-$all (boolean value indicating whether to check all tables, or just tables with a created_at column) default:false -$months (integer value for how many months prior to the created_at date to check. Used when all is set to false) default:6 -$file (string value for the desired output file name) default:'dead-columns', _(*5)

Example

Finding Dead Columns

Call the find() method, which will call the individual methods to gather the columns to check, check them, and then export the data to a csv file with either the default file name, or the given file path during instantiation., _(*6)

Example

find();

?>

Methods

Methods can be called individually, but must follow the expected array format for the input parameters., _(*7)

getTablesToCheck()

Gathers table names from INFORMATION_SCHEMA to be checked. This will only include tables with a "created_at" column if $all is set to true., _(*8)

Example

getTablesToCheck();

?>

Returned Array Format

array(2) {
  [0]=>
  array(2) {
    ["TABLE_NAME"]=>
    string(3) "foo"
    ["TABLE_SCHEMA"]=>
    string(4) "test"
  }
  [1]=>
  array(2) {
    ["TABLE_NAME"]=>
    string(3) "bar"
    ["TABLE_SCHEMA"]=>
    string(4) "test"
  }
}

getColumnsToCheck($tables)

Gathers column names from INFORMATION_SCHEMA to be checked using the $tables array parameter. $tables must be formatted in the same way as the returned array from getTablesToCheck(). This method also increments the $numColumns field., _(*9)

Example

getColumnsToCheck($tables);

?>

Returned Array Format

array(3) {
  [0]=>
  array(3) {
    ["TABLE_SCHEMA"]=>
    string(4) "test"
    ["TABLE_NAME"]=>
    string(3) "foo"
    ["COLUMN_NAME"]=>
    string(2) "id"
  }
  [1]=>
  array(2) {
    ["TABLE_SCHEMA"]=>
    string(4) "test"
    ["TABLE_NAME"]=>
    string(3) "foo"
    ["COLUMN_NAME"]=>
    string(2) "name"
  }
  [2]=>
  array(2) {
    ["TABLE_SCHEMA"]=>
    string(4) "test"
    ["TABLE_NAME"]=>
    string(3) "bar"
    ["COLUMN_NAME"]=>
    string(2) "address"
  }
}

formatTablesWithColumns($columnsByTable)

Formats columns and table names in a way where nested for loops can be used in findDeadColumns(). The format for $columnsByTable should be the returned array format from getColumnsToCheck()., _(*10)

Example

formatTablesWithColumns($unformattedColumns);
  
?>

Returned Array Format

array(1) {
  ["test"]=>
  array(2) {
    ["foo"]=>
    array(2) {
      [0]=>
      string(2) "id"
      [1]=>
      string(4) "name"
    }
    ["bar"]=>
    array(1) {
      [0]=>
      string(7) "address"
    }
  }
}

findDeadColumns($dbWithTablesWithColumns)

Searches the given list of columns parameter and finds columns with either one unique value, or is completely null. If the $all is set to false, then only rows that are recent within the number of $months will be included in the search. $dbWithTablesWithColumns must be formatted in the same way as the return array from formatTablesWithColumns(). An array will be returned with the following values for each column:, _(*11)

-distinct (The number of unique values for that column) -value (The distinct value itself) -is_null (Whether the column is completely null), _(*12)

Example

formatTablesWithColumns($unformattedColumns);
  
?>

Returned Array Format

array(1) {
  ["test"]=>
  array(1) {
    ["foo"]=>
    array(2) {
      ["id"]=>
        array(3) {
          ["distinct"]=>
          int(1)
          ["value"]=>
          string(1) "1"
          ["is_null"]=>
          int(0)
        }
      ["name"]=>
        array(3) {
          ["distinct"]=>
          int(0)
          ["value"]=>
          string(4) "NULL"
          ["is_null"]=>
          int(1)
        }
    }
  }
}

outputToFile($deadColumns)

Takes a list of dead columns, formats them, and outputs them to a .csv file. The format of $deadColumns must be in the same format as the return array from findDeadColumns(). File will be saved either to the default file path as "dead-columns.csv" or what was specified for $file Columns in the .csv are as follows:, _(*13)

-Database: The name of the database contanining the dead column -Table: The name of the table containing the dead column -Column: The name of the dead column -Distinct Values: The number of unique values for the dead column -Value: The distinct value itself -In the past x months: Records the $months value in the file, _(*14)

Example

outputToFile($deadColumns);
  
?>