Module: Prick::Rdbms

Extended by:
Ensure
Defined in:
lib/prick/rdbms.rb

Class Method Summary collapse

Methods included from Ensure

ensure_state, ensure_state_value, revoke_state

Class Method Details

.copy_database(from, to, owner: ) ⇒ Object



109
110
111
# File 'lib/prick/rdbms.rb', line 109

def self.copy_database(from, to, owner: ENV['USER'])
  create_database(to, owner: owner, template: from)
end

.create_database(db, owner: , template: "template1") ⇒ Object

TODO: make ‘owner` an option



104
105
106
107
# File 'lib/prick/rdbms.rb', line 104

def self.create_database(db, owner: ENV['USER'], template: "template1") 
  owner_option = (owner ? "-O #{owner}" : "")
  Command.command "createdb -T #{template} #{owner_option} #{db}" 
end

.create_user(user) ⇒ Object



91
92
93
# File 'lib/prick/rdbms.rb', line 91

def self.create_user(user)
  Command.command("createuser #{user}")
end

.drop_database(db, fail: true) ⇒ Object



113
114
115
# File 'lib/prick/rdbms.rb', line 113

def self.drop_database(db, fail: true) 
  Command.command "dropdb #{db}", fail: fail
end

.drop_user(user, fail: true) ⇒ Object



95
96
97
# File 'lib/prick/rdbms.rb', line 95

def self.drop_user(user, fail: true)
  Command.command "dropuser #{user}", fail: fail
end

.exec_file(db, file, user: ENV['USER']) ⇒ Object

Execute the given file and return stdout as an array of tuples



25
26
27
# File 'lib/prick/rdbms.rb', line 25

def self.exec_file(db, file, user: ENV['USER'])
  self.exec_sql(db, File.read(file), user: user)
end

.exec_sql(db, sql, user: ENV['USER']) ⇒ Object

Execute the SQL statement and return stdout as an array of tuples



13
14
15
16
17
18
19
20
21
22
# File 'lib/prick/rdbms.rb', line 13

def self.exec_sql(db, sql, user: ENV['USER'])
  stdout = Command.command %(
    {
      echo "set role #{user};"
      echo "set search_path to public;"
      echo "#{sql}"
    } | psql --csv --tuples-only --quiet -v ON_ERROR_STOP=1 -d #{db}
  )
  CSV.new(stdout.join("\n")).read
end

.exist_database?(db) ⇒ Boolean

Returns:

  • (Boolean)


99
100
101
# File 'lib/prick/rdbms.rb', line 99

def self.exist_database?(db)
  exist_record?("template1", "select 1 from pg_database where datname = '#{db}'")
end

.exist_record?(db, sql) ⇒ Boolean

Returns:

  • (Boolean)


81
82
83
# File 'lib/prick/rdbms.rb', line 81

def self.exist_record?(db, sql)
  !select(db, sql).empty?
end

.exist_schema?(db, schema) ⇒ Boolean

DETECT SCHEMAS, TABLES, AND RECORDS

Returns:

  • (Boolean)


63
64
65
66
67
68
69
70
# File 'lib/prick/rdbms.rb', line 63

def self.exist_schema?(db, schema)
  exist_record?(db, %(
      select 1
      from information_schema.schemata
      where catalog_name = '#{db}'
      and schema_name = '#{schema}'
  ))
end

.exist_table?(db, schema, table) ⇒ Boolean

Returns:

  • (Boolean)


72
73
74
75
76
77
78
79
# File 'lib/prick/rdbms.rb', line 72

def self.exist_table?(db, schema, table)
  exist_record?(db, %(
      select 1
      from information_schema.tables
      where table_schema = '#{schema}'
      and table_name = '#{table}'
  ))
end

.exist_user?(user) ⇒ Boolean

MAINTAIN USERS AND DATABASES

Returns:

  • (Boolean)


87
88
89
# File 'lib/prick/rdbms.rb', line 87

def self.exist_user?(user)
  exist_record?("template1", "select 1 from pg_roles where rolname = '#{user}'")
end

.list_databases(re = /.*/) ⇒ Object



117
118
119
# File 'lib/prick/rdbms.rb', line 117

def self.list_databases(re = /.*/)
  select_values("template1", "select datname from pg_database").select { |db| db =~ re }
end

.load(db, file, user: ) ⇒ Object



121
122
123
124
125
126
127
128
# File 'lib/prick/rdbms.rb', line 121

def self.load(db, file, user: ENV['USER'])
  Command.command %(
    {
        echo "set role #{user};"
        gunzip --to-stdout #{file}
    } | psql -v ON_ERROR_STOP=1 -d #{db}
  )
end

.save(db, file, data: true) ⇒ Object



130
131
132
133
# File 'lib/prick/rdbms.rb', line 130

def self.save(db, file, data: true)
  data_opt = (data ? "" : "--schema-only")
  Command.command "pg_dump --no-owner #{data_opt} #{db} | gzip --to-stdout >#{file}"
end

.select(db, sql, user: ENV['USER']) ⇒ Object

Execute the SQL statement and return the result as an array of record tuples Just an alias for ::exec_sql



31
# File 'lib/prick/rdbms.rb', line 31

def self.select(db, sql, user: ENV['USER']) exec_sql(db, sql, user: user) end

.select_record(db, sql, user: ) ⇒ Object

Execute the SQL statement and return a single record as an array of values. Raises an exception if the SQL statement doesn’t return exactly one record



46
47
48
49
50
# File 'lib/prick/rdbms.rb', line 46

def self.select_record(db, sql, user: ENV['USER'])
  records = exec_sql(db, sql, user: user)
  records.size == 1 or raise Prick::Fail, "Expected one row only"
  records.first
end

.select_value(db, sql, user: ) ⇒ Object

Execute the SQL statement and return a value. The value is the first field of the first record in the result. Raises an exception if the SQL statement doesn’t return exactly one record with one field



55
56
57
58
59
# File 'lib/prick/rdbms.rb', line 55

def self.select_value(db, sql, user: ENV['USER'])
  row = select_record(db, sql, user: user)
  row.size == 1 or raise Prick::Fail, "Expected one field only"
  row.first
end

.select_values(db, sql, user: ENV['USER']) ⇒ Object

Execute the SQL statement and return an array of values, one for each single-valued record in the result. Raises an exception if the SQL statement doesn’t return records with exactly one field



36
37
38
39
40
41
# File 'lib/prick/rdbms.rb', line 36

def self.select_values(db, sql, user: ENV['USER'])
  records = exec_sql(db, sql, user: user)
  return [] if records.empty?
  records.first.size == 1 or raise Prick::Fail, "Expected records with one field"
  records.flatten
end