Class: PgConn::RdbmsMethods

Inherits:
Object
  • Object
show all
Defined in:
lib/pg_conn/rdbms_methods.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(conn) ⇒ RdbmsMethods

Returns a new instance of RdbmsMethods.



9
10
11
12
 
# File 'lib/pg_conn/rdbms_methods.rb', line 9

def initialize(conn)
  @conn = conn
  # TODO: Check if conn is a superuser connection
end

Instance Attribute Details

#conn ⇒ Object (readonly)

Returns the value of attribute conn.



7
8
9
 
# File 'lib/pg_conn/rdbms_methods.rb', line 7

def conn
  @conn
end

Instance Method Details

#copy(from_database, to_database, owner: ) ⇒ Object

Fast copy using templates. Note that no user may be logged in to the source database for this to work



102
103
104
 
# File 'lib/pg_conn/rdbms_methods.rb', line 102

def copy(from_database, to_database, owner: ENV['USER'])
  create(to_database, owner: owner, template: from_database)
end

#create(database, owner: ENV['USER'], template: "template1") ⇒ Object

Create a new database



24
25
26
27
28
29
30
 
# File 'lib/pg_conn/rdbms_methods.rb', line 24

def create(database, owner: ENV['USER'], template: "template1")
  owner_clause = owner ? "owner = \"#{owner}\"" : nil
  template_clause = template ? "template = \"#{template}\"" : nil
  stmt = ["create database \"#{database}\"", owner_clause, template_clause].compact.join(" ")
  conn.execute stmt # Note we're using #execute instead of #exec because
                    # create database can't run within a transaction
end

#drop(database, force: false) ⇒ Object

Drop a database



33
34
35
36
 
# File 'lib/pg_conn/rdbms_methods.rb', line 33

def drop(database, force: false)
  conn.execute "drop database if exists \"#{database}\"#{force ? ' with (force)' : ''}"
  true
end

#empty!(database = nil, public: true, exclude: []) ⇒ Object

Hollow-out a database by removing all schemas in the database. The public schema is recreated afterwards unless :public is false. Uses the current database if @database is nil

Note that the database can have active users logged in while the database is emptied. TODO Explain what happens if the users have active transactions. Should the be terminated?



79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/pg_conn/rdbms_methods.rb', line 79

def empty!(database = nil, public: true, exclude: [])
  local = !database.nil?
  begin
    conn = local ? PgConn.new(database) : self.conn
    schemas =
        conn
          .values("select nspname from pg_namespace where nspowner != 10 or nspname = 'public'")
          .select { |schema| !exclude.include?(schema) }
          .join(", ")
    conn.exec "drop schema #{schemas} cascade"

    # FIXME FIXME FIXME SECURITY Why grant 'create' to public?
    conn.exec %(
        create schema public authorization postgres;
        grant usage, create on schema public to public
    ) if public
  ensure
    conn&.terminate if local
  end
end

#exist?(database) ⇒ Boolean

Return true if the database exists

Returns:

  • (Boolean) 


15
16
17
18
19
20
21
 
# File 'lib/pg_conn/rdbms_methods.rb', line 15

def exist?(database)
  conn.exist? %(
      select 1
      from pg_database
      where datname = '#{database}'
  )
end

#list(all: false, exclude: []) ⇒ Object

List databases in the RDBMS



39
40
41
42
43
44
45
 
# File 'lib/pg_conn/rdbms_methods.rb', line 39

def list(all: false, exclude: [])
  exclude += POSTGRES_DATABASES if !all
  exclude_sql_list = "'" + exclude.join("', '") + "'"
  exclude_clause = exclude.empty? ? nil : "where datname not in (#{exclude_sql_list})"
  stmt = ["select datname from pg_database", exclude_clause].compact.join(" ")
  conn.values stmt
end

#load(database, file, role: , gzip: nil) ⇒ Object

TODO: This code is replicated across many projects. Should be moved to PgConn



107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/pg_conn/rdbms_methods.rb', line 107

def load(database, file, role: ENV['USER'], gzip: nil)
  command_opt = role ? "-c \"set role #{role}\";\n" : nil
  if gzip
    pipe_cmd = file ? "gunzip --to-stdout #{file} |" : "gunzip --to-stdout |"
    file_opt = nil
  else
    pipe_cmd = nil
    file_opt = file ? "-f #{file}" : nil
  end
  cmd = [pipe_cmd, "psql -v ON_ERROR_STOP=1", command_opt, file_opt, database].compact.join(" ")
  stdout, stderr, status = Open3.capture3(cmd)
  status == 0 or raise PsqlError.new(stderr)
end

#owner(database) ⇒ Object

Return the owner of a given database



48
49
50
51
52
53
54
55
 
# File 'lib/pg_conn/rdbms_methods.rb', line 48

def owner(database)
  conn.value %(
    select r.rolname
    from (values ('#{database}')) as v (database)
    left join pg_database d on d.datname = v.database
    left join pg_roles r on r.oid = d.datdba
  )
end

#save(database, file, data: true, schema: true, gzip: nil) ⇒ Object

TODO: This code is replicated across many projects. Should be moved to PgConn



122
123
124
125
126
127
128
129
130
131
132
133
134
135
 
# File 'lib/pg_conn/rdbms_methods.rb', line 122

def save(database, file, data: true, schema: true, gzip: nil)
  data_opt = data ? nil : "--schema-only"
  schema_opt = schema ? nil : "--data-only"
  if gzip
    pipe_cmd = file ? "| gzip >#{file}" : "| gzip"
    file_opt = nil
  else
    pipe_cmd = nil
    file_opt = file ? "-f #{file}" : nil
  end
  cmd = ["pg_dump --no-owner", data_opt, schema_opt, file_opt, database, pipe_cmd].compact.join(" ")
  stdout, stderr, status = Open3.capture3(cmd)
  status == 0 or raise PsqlError.new(stderr)
end

#users(database) ⇒ Object

Return list of users currently logged in to the given database or to any database if database is nil

FIXME: There is a possible race-condition here where some process (eg. auto-vacuum) is logged in to the database but has a nil username. The easy fix is to have ‘usename is not null’ but it would be nice to know what exactly is triggering this problem



65
66
67
68
69
 
# File 'lib/pg_conn/rdbms_methods.rb', line 65

def users(database)
  database_clause = database ? "datname = '#{database}'" : nil
  query = ["select usename from pg_stat_activity", database_clause].compact.join(" where ")
  conn.values query
end