Class: Synthra::Export::SqlInsert

Inherits:
Base
  • Object
show all
Defined in:
lib/synthra/export/sql_insert.rb

Overview

Exports generated data as SQL INSERT statements

Examples:

Export schema data as SQL INSERTs

exporter = SqlInsert.new(schema, registry: registry, count: 100, dialect: :postgresql)
sql = exporter.export

Constant Summary collapse

DIALECTS =

Supported SQL dialects

%i[postgresql mysql sqlite].freeze

Instance Method Summary collapse

Constructor Details

This class inherits a constructor from Synthra::Export::Base

Instance Method Details

#build_batch_insert(table_name, records, dialect) ⇒ Object (private) 



54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
# File 'lib/synthra/export/sql_insert.rb', line 54

def build_batch_insert(table_name, records, dialect)
  return "" if records.empty?

  flat_records = records.map { |r| flatten_record(r) }
  columns = flat_records.first.keys.map { |k| quote_identifier(k, dialect) }

  lines = []
  lines << "INSERT INTO #{quote_identifier(table_name, dialect)} (#{columns.join(', ')}) VALUES"

  values_lines = flat_records.map do |flat_record|
    values = flat_record.values.map { |v| format_value(v, dialect) }
    "  (#{values.join(', ')})"
  end

  lines << values_lines.join(",\n") + ";"
  lines.join("\n")
end

#build_single_insert(table_name, record, dialect) ⇒ Object (private) 



46
47
48
49
50
51
52
 
# File 'lib/synthra/export/sql_insert.rb', line 46

def build_single_insert(table_name, record, dialect)
  flat_record = flatten_record(record)
  columns = flat_record.keys.map { |k| quote_identifier(k, dialect) }
  values = flat_record.values.map { |v| format_value(v, dialect) }

  "INSERT INTO #{quote_identifier(table_name, dialect)} (#{columns.join(', ')}) VALUES (#{values.join(', ')});"
end

#escape_string(str, dialect) ⇒ Object (private) 



120
121
122
123
124
125
126
127
128
 
# File 'lib/synthra/export/sql_insert.rb', line 120

def escape_string(str, dialect)
  escaped = str.gsub("'", "''")
  case dialect
  when :mysql
    "'#{escaped.gsub('\\', '\\\\\\\\')}'"
  else
    "'#{escaped}'"
  end
end

#export ⇒ Object 



16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
# File 'lib/synthra/export/sql_insert.rb', line 16

def export
  count = options[:count] || 10
  seed = options[:seed]
  mode = options[:mode] || :random
  dialect = options[:dialect] || :postgresql

  records = schema.generate_many(count, seed: seed, mode: mode, registry: registry)

  lines = []
  lines << "-- Generated from Synthra schema: #{schema.name}"
  lines << "-- Dialect: #{dialect.to_s.upcase}"
  lines << "-- Records: #{count}"
  lines << "-- Generated at: #{Time.now.iso8601}"
  lines << ""

  table_name = to_snake_case(schema.name)

  if options[:batch_insert] && dialect != :sqlite
    lines << build_batch_insert(table_name, records, dialect)
  else
    records.each do |record|
      lines << build_single_insert(table_name, record, dialect)
    end
  end

  lines.join("\n")
end

#flatten_record(record, prefix = "") ⇒ Object (private) 



72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
# File 'lib/synthra/export/sql_insert.rb', line 72

def flatten_record(record, prefix = "")
  result = {}

  record.each do |key, value|
    full_key = prefix.empty? ? key : "#{prefix}_#{key}"

    case value
    when Hash
      # For nested objects, either flatten or serialize as JSON
      if options[:flatten_nested]
        result.merge!(flatten_record(value, full_key))
      else
        result[full_key] = JSON.generate(value)
      end
    when Array
      # Arrays become JSON
      result[full_key] = JSON.generate(value)
    else
      result[full_key] = value
    end
  end

  result
end

#format_value(value, dialect) ⇒ Object (private) 



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
 
# File 'lib/synthra/export/sql_insert.rb', line 97

def format_value(value, dialect)
  case value
  when nil
    "NULL"
  when true
    dialect == :mysql ? "1" : "TRUE"
  when false
    dialect == :mysql ? "0" : "FALSE"
  when Integer
    value.to_s
  when Float
    value.to_s
  when String
    escape_string(value, dialect)
  when Time, DateTime
    escape_string(value.iso8601, dialect)
  when Date
    escape_string(value.to_s, dialect)
  else
    escape_string(value.to_s, dialect)
  end
end

#quote_identifier(name, dialect) ⇒ Object (private) 



130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/synthra/export/sql_insert.rb', line 130

def quote_identifier(name, dialect)
  snake_name = to_snake_case(name.to_s)
  # SECURITY: escape the quote char inside the identifier (double it) so a field name
  # containing a backtick / double-quote can't break out of the quoting into injected SQL.
  case dialect
  when :mysql
    "`#{snake_name.gsub('`', '``')}`"
  when :postgresql
    "\"#{snake_name.gsub('"', '""')}\""
  else
    snake_name
  end
end

#to_snake_case(str) ⇒ Object (private) 



144
145
146
147
148
 
# File 'lib/synthra/export/sql_insert.rb', line 144

def to_snake_case(str)
  str.gsub(/([A-Z]+)([A-Z][a-z])/, '\1_\2')
     .gsub(/([a-z\d])([A-Z])/, '\1_\2')
     .downcase
end