Module: Prick::Rdbms
- Extended by:
- Ensure
- Defined in:
- lib/prick/rdbms.rb
Class Method Summary collapse
- .copy_database(from, to, owner: ) ⇒ Object
-
.create_database(db, owner: , template: "template1") ⇒ Object
TODO: make ‘owner` an option.
- .create_user(user) ⇒ Object
- .drop_database(db, fail: true) ⇒ Object
- .drop_user(user, fail: true) ⇒ Object
-
.exec_file(db, file, user: ENV['USER']) ⇒ Object
Execute the given file and return stdout as an array of tuples.
-
.exec_sql(db, sql, user: ENV['USER']) ⇒ Object
Execute the SQL statement and return stdout as an array of tuples.
- .exist_database?(db) ⇒ Boolean
- .exist_record?(db, sql) ⇒ Boolean
-
.exist_schema?(db, schema) ⇒ Boolean
DETECT SCHEMAS, TABLES, AND RECORDS.
- .exist_table?(db, schema, table) ⇒ Boolean
-
.exist_user?(user) ⇒ Boolean
MAINTAIN USERS AND DATABASES.
- .list_databases(re = /.*/) ⇒ Object
- .load(db, file, user: ) ⇒ Object
- .save(db, file, data: true) ⇒ Object
-
.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.
-
.select_record(db, sql, user: ) ⇒ Object
Execute the SQL statement and return a single record as an array of values.
-
.select_value(db, sql, user: ) ⇒ Object
Execute the SQL statement and return a value.
-
.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.
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
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
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
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
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
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 |