database_statements.rb in torque-postgresql-3.1.0

- old
+ new

@@ -10,20 +10,51 @@
         # Switch between dump mode or not
         def dump_mode!
           @_dump_mode = !!!@_dump_mode
         end
 
+        # List of schemas blocked by the application in the current connection
+        def schemas_blacklist
+          @schemas_blacklist ||= Torque::PostgreSQL.config.schemas.blacklist +
+            (@config.dig(:schemas, 'blacklist') || [])
+        end
+
+        # List of schemas used by the application in the current connection
+        def schemas_whitelist
+          @schemas_whitelist ||= Torque::PostgreSQL.config.schemas.whitelist +
+            (@config.dig(:schemas, 'whitelist') || [])
+        end
+
+        # A list of schemas on the search path sanitized
+        def schemas_search_path_sanitized
+          @schemas_search_path_sanitized ||= begin
+            db_user = @config[:username] || ENV['USER'] || ENV['USERNAME']
+            schema_search_path.split(',').map { |item| item.strip.sub('"$user"', db_user) }
+          end
+        end
+
         # Check if a given type is valid.
         def valid_type?(type)
           super || extended_types.include?(type)
         end
 
         # Get the list of extended types
         def extended_types
           EXTENDED_DATABASE_TYPES
         end
 
+        # Checks if a given schema exists in the database. If +filtered+ is
+        # given as false, then it will check regardless of whitelist and
+        # blacklist
+        def schema_exists?(name, filtered: true)
+          return user_defined_schemas.include?(name.to_s) if filtered
+
+          query_value(<<-SQL) == 1
+            SELECT 1 FROM pg_catalog.pg_namespace WHERE nspname = '#{name}'
+          SQL
+        end
+
         # Returns true if type exists.
         def type_exists?(name)
           user_defined_types.key? name.to_s
         end
         alias data_type_exists? type_exists?
@@ -122,21 +153,44 @@
         end
 
         # Get the list of inherited tables associated with their parent tables
         def inherited_tables
           tables = query(<<-SQL, 'SCHEMA')
-            SELECT child.relname             AS table_name,
-                   array_agg(parent.relname) AS inheritances
+            SELECT inhrelid::regclass  AS table_name,
+                   inhparent::regclass AS inheritances
             FROM pg_inherits
             JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
             JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
-            GROUP BY child.relname, pg_inherits.inhrelid
-            ORDER BY pg_inherits.inhrelid
+            ORDER BY inhrelid
           SQL
 
-          tables.map do |(table, refs)|
-            [table, PG::TextDecoder::Array.new.decode(refs)]
-          end.to_h
+          tables.each_with_object({}) do |(child, parent), result|
+            (result[child] ||= []) << parent
+          end
+        end
+
+        # Get the list of schemas that were created by the user
+        def user_defined_schemas
+          query_values(user_defined_schemas_sql, 'SCHEMA')
+        end
+
+        # Build the query for allowed schemas
+        def user_defined_schemas_sql
+          conditions = []
+          conditions << <<-SQL if schemas_blacklist.any?
+            nspname NOT LIKE ANY (ARRAY['#{schemas_blacklist.join("', '")}'])
+          SQL
+
+          conditions << <<-SQL if schemas_whitelist.any?
+            nspname LIKE ANY (ARRAY['#{schemas_whitelist.join("', '")}'])
+          SQL
+
+          <<-SQL.squish
+            SELECT nspname
+            FROM pg_catalog.pg_namespace
+            WHERE 1=1 AND #{conditions.join(' AND ')}
+            ORDER BY oid
+          SQL
         end
 
         # Get the list of columns, and their definition, but only from the
         # actual table, does not include columns that comes from inherited table
         def column_definitions(table_name) # :nodoc: