[Noalyss-commit] [noalyss] 02/04: Database : clean_orphan_lob removes unused lob

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit e5acc9e7d969f97f317ffb8a9a86c077f88b7d0d
Author: Dany De Bontridder <address@hidden>
Date:   Tue Mar 8 22:07:48 2016 +0100

    Database : clean_orphan_lob removes unused lob
---
 include/lib/class_database.php |   46 +++++++++++++++++++++++++++++++++++++++-
 1 files changed, 45 insertions(+), 1 deletions(-)

diff --git a/include/lib/class_database.php b/include/lib/class_database.php
index d3f915c..bd24ce9 100644
--- a/include/lib/class_database.php
+++ b/include/lib/class_database.php
@@ -1076,7 +1076,51 @@ class Database
             printf("\n\r");
         }
     }
+    /**
+     * @brief Find all lob and remove those which are not used by any tables
+     * 
+     */
+    function clean_orphan_lob()
+    {
+        // find all columns of type lob
+        $sql =" 
+                select table_schema,table_name,column_name 
+                from 
+                    information_schema.columns 
+                where table_schema not in ('information_schema','pg_catalog') 
+                and data_type='oid'";
+        $all_lob= "
+            select oid,'N' as used from pg_largeobject_metadata
+            ";
+        $a_table=  $this->get_array($sql);
+        $a_lob = $this->get_array($all_lob);
+        if ( $a_table == false || $a_lob == false ) return;
+        // for each lob
+        $nb_lob=count($a_lob);
+        $nb_table=count($a_table);
+        for ($i=0;$i  < $nb_lob;$i++)
+        {
+            $lob=$a_lob[$i]['oid'];
+            if ( $a_lob[$i]['used']=='Y')                    continue;
+            for ($j=0;$j  < $nb_table;$j++)
+            {
+                if ( $a_lob[$i]['used']=='Y')                    continue;
+                $check = $this->get_value(" select count(*) from ".
+                        
$a_table[$j]['table_schema'].".".$a_table[$j]['table_name'].
+                        " where ".
+                        $a_table[$j]['column_name']."=$1",array($lob));
+                if ( $check != 0 ) 
+                    $a_lob[$i]['used']='Y';
+                     
+            }
+        }
+        for ($i=0;$i  < $nb_lob;$i++)
+        {
+            if ( $a_lob[$i]['used']=='Y')                    continue;
+                $this->lo_unlink($a_lob[$i]['oid']);
+        }
+    }
 
 }
 
-/* test::test_me(); */
+/* test::test_me(); */
\ No newline at end of file