Class: StructuredDataToSql::Json::SqlEmitter

Inherits:
Object
  • Object
show all
Defined in:
lib/structured_data_to_sql/json/sql_emitter.rb

Overview

Writes MySQL/MariaDB DDL and batched INSERT statements following the same conventions as the XML converter output.

Constant Summary collapse

META_ROOT = 
["_sid"].freeze
META_CHILD = 
%w[_sid _parent_sid _parent_id _ordinal].freeze
MAX_INLINE_ROW_BYTES = 
60_000
TEXT_POINTER_BYTES = 
20

Instance Method Summary collapse

Instance Method Details

#format_value(value, column_def) ⇒ Object 



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/structured_data_to_sql/json/sql_emitter.rb', line 116

def format_value(value, column_def)
  return format_null(column_def) if value.nil?
  return SqlText.escape_sql_string(value.text) if value.is_a?(JsonValue)

  case column_def&.kind
  when :boolean
    value == true ? "1" : "0"
  when :integer, :float
    if value.is_a?(Numeric)
      value.to_s
    else
      SqlText.escape_sql_string(value.to_s)
    end
  when :datetime
    format_datetime(value)
  else
    SqlText.escape_sql_string(value.to_s)
  end
end

#write_batch(out, name, columns, value_rows) ⇒ Object 



68
69
70
71
72
73
74
75
76
77
78
79
 
# File 'lib/structured_data_to_sql/json/sql_emitter.rb', line 68

def write_batch(out, name, columns, value_rows)
  return if value_rows.empty?

  identifiers = columns.map { |column| SqlText.escape_identifier(column) }
  out.write(
    "INSERT INTO #{SqlText.escape_identifier(name)} (#{identifiers.join(", ")}) VALUES\n"
  )
  out.write(
    value_rows.map { |values| "  (#{values.join(", ")})" }.join(",\n")
  )
  out.write(";\n")
end

#write_create_table(out, name, column_defs, child:) ⇒ Object 



31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'lib/structured_data_to_sql/json/sql_emitter.rb', line 31

def write_create_table(out, name, column_defs, child:)
  column_defs = row_safe_column_defs(column_defs, child:)
  lines = ["  `_sid` BIGINT NOT NULL"]
  if child
    lines << "  `_parent_sid` BIGINT NOT NULL"
    lines << "  `_parent_id` VARCHAR(255)"
    lines << "  `_ordinal` BIGINT NOT NULL"
  end
  column_defs.each do |col|
    definition =
      "  #{SqlText.escape_identifier(col.name)} #{col.sql_type}"
    definition += " NOT NULL" unless col.null
    definition +=
      " COMMENT #{SqlText.escape_sql_string(col.comment)}" if col.comment
    lines << definition
  end
  lines << "  PRIMARY KEY (`_sid`)"
  if child
    lines << "  KEY `idx_parent_sid` (`_parent_sid`)"
    lines << "  KEY `idx_parent_id` (`_parent_id`)"
  end
  id_column = column_defs.find { |col| col.name == "id" }
  # TEXT-family columns cannot carry a plain KEY without a prefix length.
  if id_column && !id_column.sql_type.end_with?("TEXT")
    lines << "  KEY `idx_id` (`id`)"
  end

  out.write("\n-- Table: #{name}\n")
  out.write("DROP TABLE IF EXISTS #{SqlText.escape_identifier(name)};\n")
  out.write(
    "CREATE TABLE #{SqlText.escape_identifier(name)} (\n#{lines.join(",\n")}\n"
  )
  out.write(
    ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;\n"
  )
end

#write_footer(out) ⇒ Object 



27
28
29
 
# File 'lib/structured_data_to_sql/json/sql_emitter.rb', line 27

def write_footer(out)
  out.write("\nSET FOREIGN_KEY_CHECKS = 1;\nSET UNIQUE_CHECKS = 1;\n")
end

#write_header(out, source) ⇒ Object 



16
17
18
19
20
21
22
23
24
25
 
# File 'lib/structured_data_to_sql/json/sql_emitter.rb', line 16

def write_header(out, source)
  out.write("-- Generated by json-to-sql converter\n")
  out.write("-- Source: #{source}\n")
  out.write(
    "-- Generated at: #{Time.now.strftime("%Y-%m-%d %H:%M:%S")}\n\n"
  )
  out.write(
    "SET NAMES utf8mb4;\nSET sql_mode = '';\nSET FOREIGN_KEY_CHECKS = 0;\nSET UNIQUE_CHECKS = 0;\n\n"
  )
end

#write_meta_table(out, rows) ⇒ Object

PII manifest derived from x-pii annotations in the JSON Schemas, so downstream redaction tooling can query column sensitivity in MariaDB.



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
 
# File 'lib/structured_data_to_sql/json/sql_emitter.rb', line 83

def write_meta_table(out, rows)
  out.write("\n-- Table: _json_meta\n")
  out.write("DROP TABLE IF EXISTS `_json_meta`;\n")
  out.write(<<~SQL)
    CREATE TABLE `_json_meta` (
      `table_name` VARCHAR(64) NOT NULL,
      `column_name` VARCHAR(64) NOT NULL,
      `json_path` VARCHAR(255) NOT NULL,
      `pii` TINYINT(1) NOT NULL DEFAULT 0,
      `pii_note` TEXT,
      `source_schema` VARCHAR(255),
      PRIMARY KEY (`table_name`, `column_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  SQL
  value_rows =
    rows.map do |table, column, json_path, pii, note, source|
      [
        SqlText.escape_sql_string(table),
        SqlText.escape_sql_string(column),
        SqlText.escape_sql_string(json_path),
        pii.to_s,
        note.nil? ? "NULL" : SqlText.escape_sql_string(note),
        SqlText.escape_sql_string(source)
      ]
    end
  write_batch(
    out,
    "_json_meta",
    %w[table_name column_name json_path pii pii_note source_schema],
    value_rows
  )
end