Module: Export::Project
- Defined in:
- lib/export/project.rb
Constant Summary collapse
- HIERARCHIES =
When adding a new table be sure to check there is nothing different compared to existing ones.
[ ["container_item_hierarchies", "container_items"], ["geographic_area_hierarchies", "geographic_areas"], ["taxon_name_hierarchies", "taxon_names"] ]
- SPECIAL_TABLES =
['spatial_ref_sys', 'project', 'users', 'versions', 'delayed_jobs', 'imports']
Class Method Summary collapse
- .copy_table(table, io, cols, select_query) ⇒ Object private
- .download(project) ⇒ Object
- .dump_hierarchy_table(table_pair, io, project_id) ⇒ Object private
- .dump_table(table, io, project_id) ⇒ Object private
- .export_users(io, project) ⇒ Object private
-
.generate_dump(project, file) ⇒ Object
Restorable with psql -U :username -d :database -f dump.sql.
- .get_connection ⇒ Object private
- .get_table_cols(table) ⇒ Object private
- .setup_pk_sequence(table, io) ⇒ Object private
Class Method Details
.copy_table(table, io, cols, select_query) ⇒ Object (private)
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
# File 'lib/export/project.rb', line 101 def self.copy_table(table, io, cols, select_query) conn = get_connection io.puts("COPY public.#{table} (#{cols.join(', ')}) FROM stdin;") # TODO: Consider "WITH CSV HEADER" if dumping to a set of CSV files gets implemented conn.copy_data("COPY (#{select_query}) TO STDOUT") do while row = conn.get_copy_data io.write(row) end end io.puts('\.') io.puts end |
.download(project) ⇒ Object
41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
# File 'lib/export/project.rb', line 41 def self.download(project) Tempfile.create do |file| buffer = ::Zip::OutputStream.write_buffer(file) do |zipfile| zipfile.put_next_entry('dump.sql') generate_dump(project, zipfile) end buffer.flush Download::SqlProjectDump.create!( name: "#{project.name} export on #{Time.now}.", description: 'A zip file containing SQL dump of community data + project-specific data', filename: Zaru::sanitize!("#{project.name}.zip").gsub(' ', '_').downcase, source_file_path: file.path, expires: 2.days.from_now, is_public: false ) end end |
.dump_hierarchy_table(table_pair, io, project_id) ⇒ Object (private)
86 87 88 89 90 91 92 93 94 95 96 97 98 99 |
# File 'lib/export/project.rb', line 86 def self.dump_hierarchy_table(table_pair, io, project_id) cols_model = get_table_cols(table_pair.second) return dump_table(table_pair.first, io, project_id) unless cols_model.include?('"project_id"') cols_hierarchy = get_table_cols(table_pair.first) select_query = "SELECT #{cols_hierarchy.map { |c| "#{table_pair.first}.#{c}" }.join(', ')} "\ "FROM #{table_pair.second} INNER JOIN "\ "#{table_pair.first} ON #{table_pair.second}.id IN ("\ "#{table_pair.first}.ancestor_id, #{table_pair.first}.descendant_id"\ ") "\ "WHERE project_id = #{project_id}" copy_table(table_pair.first, io, cols_hierarchy, select_query) end |
.dump_table(table, io, project_id) ⇒ Object (private)
73 74 75 76 77 78 79 80 81 82 83 84 |
# File 'lib/export/project.rb', line 73 def self.dump_table(table, io, project_id) cols = get_table_cols(table) if cols.include?('"project_id"') where_clause = "WHERE project_id IN (#{project_id}, NULL)" elsif table == 'projects' where_clause = "WHERE id = #{project_id}" else where_clause = '' end copy_table(table, io, cols, "SELECT #{cols.join(', ')} FROM #{table} #{where_clause}") end |
.export_users(io, project) ⇒ Object (private)
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
# File 'lib/export/project.rb', line 117 def self.export_users(io, project) members = project.users.all conn = get_connection User.all.each do |user| attributes = { id: user.id, password_digest: "'#{conn.escape_string(User.new(password: 'taxonworks').password_digest)}'", created_at: "'#{user.created_at}'", updated_at: "'#{user.updated_at}'", created_by_id: user.created_by_id || 'NULL', updated_by_id: user.updated_by_id || 'NULL', is_administrator: user.is_administrator || 'NULL', hub_tab_order: "'{#{conn.escape_string(user.hub_tab_order.join(','))}}'" }.merge!( if members.include?(user) { email: "'#{conn.escape_string(user.email)}'", name: "'#{conn.escape_string(user.name)}'" } else { email: "'unknown_#{user.id}@example.com'", name: "'User outside project (#{user.id})'" } end ) io.puts("INSERT INTO public.users(#{attributes.keys.join(', ')})\nVALUES (#{attributes.values.join(', ')});") end io.puts setup_pk_sequence('users', io) end |
.generate_dump(project, file) ⇒ Object
Restorable with psql -U :username -d :database -f dump.sql. Requires database to be created without tables (rails db:create)
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
# File 'lib/export/project.rb', line 13 def self.generate_dump(project, file) config = ActiveRecord::Base.connection_config args = [ ['-h', config[:host]], ['-s', config[:database]], ['-U', config[:username]], ['-p', config[:port]] ].reject! { |(a, v)| v.nil? }.flatten! # Retrieve schema schema = Open3.capture3({'PGPASSWORD' => config[:password]}, 'pg_dump', '-w', '-O', *args).first # Open gap to insert COPY statements (between tables creation and contraints & indices setup) split_point = schema.index(/\n(--[^\n]*\n)*\s*ALTER\s+TABLE/) schema_head, schema_tail = schema[0..split_point-1], schema[split_point..-1] tables = (ActiveRecord::Base.connection.tables - SPECIAL_TABLES - HIERARCHIES.map(&:first)).sort file.puts schema_head file.write "\n-- DATA RESTORE\n\n" export_users(file, project) tables.each { |t| dump_table(t, file, project.id) } tables.each { |t| setup_pk_sequence(t, file) if get_table_cols(t).include?('"id"') } HIERARCHIES.each { |p| dump_hierarchy_table(p, file, project.id) } file.puts schema_tail end |
.get_connection ⇒ Object (private)
65 66 67 |
# File 'lib/export/project.rb', line 65 def self.get_connection ActiveRecord::Base.connection.raw_connection end |
.get_table_cols(table) ⇒ Object (private)
69 70 71 |
# File 'lib/export/project.rb', line 69 def self.get_table_cols(table) ActiveRecord::Base.connection.columns(table).map { |c| "\"#{c.name}\"" } end |
.setup_pk_sequence(table, io) ⇒ Object (private)
61 62 63 |
# File 'lib/export/project.rb', line 61 def self.setup_pk_sequence(table, io) io.write("SELECT setval('public.#{table}_id_seq', (SELECT COALESCE(MAX(id), 0)+1 FROM public.#{table}));\n\n") end |