Class: Synthra::Export::Sql

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

Overview

Exports schema as SQL CREATE TABLE statements

Examples:

Export a schema

exporter = Sql.new(schema, registry: registry, dialect: :postgresql)
sql = exporter.export

Constant Summary collapse

DIALECTS =

Supported SQL dialects

%i[postgresql mysql sqlite].freeze
POSTGRESQL_TYPES =

Type mappings for PostgreSQL

{
  "uuid" => "UUID",
  "ulid" => "VARCHAR(26)",
  "email" => "VARCHAR(255)",
  "url" => "TEXT",
  "ip" => "INET",
  "ipv6" => "INET",
  "date" => "DATE",
  "date_between" => "DATE",
  "past_date" => "DATE",
  "future_date" => "DATE",
  "mobile_device_release_date" => "DATE",
  "now" => "DATE",
  "timestamp" => "TIMESTAMP WITH TIME ZONE",
  "datetime" => "TIMESTAMP WITH TIME ZONE",
  "text" => "TEXT",
  "name" => "VARCHAR(255)",
  "full_name" => "VARCHAR(255)",
  "first_name" => "VARCHAR(100)",
  "last_name" => "VARCHAR(100)",
  "phone" => "VARCHAR(50)",
  "city" => "VARCHAR(100)",
  "country" => "VARCHAR(100)",
  "country_code" => "CHAR(3)",
  "postal_code" => "VARCHAR(20)",
  "state" => "VARCHAR(100)",
  "street" => "TEXT",
  "address" => "TEXT",
  "username" => "VARCHAR(50)",
  "social_handle" => "VARCHAR(50)",
  "domain" => "VARCHAR(255)",
  "user_agent" => "TEXT",
  "mac_address" => "MACADDR",
  "iban" => "VARCHAR(34)",
  "credit_card" => "VARCHAR(20)",
  "currency" => "VARCHAR(10)",
  "currency_code" => "CHAR(3)",
  "paragraph" => "TEXT",
  "sentence" => "TEXT",
  "word" => "VARCHAR(100)",
  "message_text" => "TEXT",
  "hashtag_text" => "VARCHAR(100)",
  "snowflake_id" => "BIGINT",
  "numeric_string_id" => "BIGINT",
  "number" => "INTEGER",
  "integer" => "INTEGER",
  "age" => "INTEGER",
  "airport_elevation" => "INTEGER",
  "discount" => "INTEGER",
  "formula" => "INTEGER",
  "row_number" => "INTEGER",
  "sequence" => "INTEGER",
  "float" => "DECIMAL(15,2)",
  "money" => "DECIMAL(15,2)",
  "latitude" => "DECIMAL(10,8)",
  "longitude" => "DECIMAL(11,8)",
  "airport_latitude" => "DECIMAL(10,8)",
  "airport_longitude" => "DECIMAL(11,8)",
  "product_price" => "DECIMAL(15,2)",
  "tax_rate" => "DECIMAL(10,4)",
  "transaction_amount" => "DECIMAL(15,2)",
  "id_sequence" => "SERIAL",
  "boolean" => "BOOLEAN",
  "object" => "JSONB",
  "map_by_field" => "JSONB",
  "array" => "JSONB",
  "empty_array" => "JSONB",
  "json_array" => "JSONB",
  "indices_pair" => "INTEGER[]"
}.freeze
MYSQL_TYPES =

Type mappings for MySQL

{
  "uuid" => "CHAR(36)",
  "ulid" => "CHAR(26)",
  "email" => "VARCHAR(255)",
  "url" => "TEXT",
  "ip" => "VARCHAR(45)",
  "ipv6" => "VARCHAR(45)",
  "date" => "DATE",
  "date_between" => "DATE",
  "past_date" => "DATE",
  "future_date" => "DATE",
  "mobile_device_release_date" => "DATE",
  "now" => "DATE",
  "timestamp" => "DATETIME",
  "datetime" => "DATETIME",
  "text" => "TEXT",
  "name" => "VARCHAR(255)",
  "full_name" => "VARCHAR(255)",
  "first_name" => "VARCHAR(100)",
  "last_name" => "VARCHAR(100)",
  "phone" => "VARCHAR(50)",
  "city" => "VARCHAR(100)",
  "country" => "VARCHAR(100)",
  "country_code" => "CHAR(3)",
  "postal_code" => "VARCHAR(20)",
  "state" => "VARCHAR(100)",
  "street" => "TEXT",
  "address" => "TEXT",
  "username" => "VARCHAR(50)",
  "social_handle" => "VARCHAR(50)",
  "domain" => "VARCHAR(255)",
  "user_agent" => "TEXT",
  "mac_address" => "CHAR(17)",
  "iban" => "VARCHAR(34)",
  "credit_card" => "VARCHAR(20)",
  "currency" => "VARCHAR(10)",
  "currency_code" => "CHAR(3)",
  "paragraph" => "TEXT",
  "sentence" => "TEXT",
  "word" => "VARCHAR(100)",
  "message_text" => "TEXT",
  "hashtag_text" => "VARCHAR(100)",
  "snowflake_id" => "BIGINT",
  "numeric_string_id" => "BIGINT",
  "number" => "INT",
  "integer" => "INT",
  "age" => "INT",
  "airport_elevation" => "INT",
  "discount" => "INT",
  "formula" => "INT",
  "row_number" => "INT",
  "sequence" => "INT",
  "float" => "DECIMAL(15,2)",
  "money" => "DECIMAL(15,2)",
  "latitude" => "DECIMAL(10,8)",
  "longitude" => "DECIMAL(11,8)",
  "airport_latitude" => "DECIMAL(10,8)",
  "airport_longitude" => "DECIMAL(11,8)",
  "product_price" => "DECIMAL(15,2)",
  "tax_rate" => "DECIMAL(10,4)",
  "transaction_amount" => "DECIMAL(15,2)",
  "id_sequence" => "INT AUTO_INCREMENT",
  "boolean" => "TINYINT(1)",
  "object" => "JSON",
  "map_by_field" => "JSON",
  "array" => "JSON",
  "empty_array" => "JSON",
  "json_array" => "JSON",
  "indices_pair" => "JSON"
}.freeze
SQLITE_TYPES =

Type mappings for SQLite

{
  "uuid" => "TEXT",
  "ulid" => "TEXT",
  "email" => "TEXT",
  "url" => "TEXT",
  "ip" => "TEXT",
  "ipv6" => "TEXT",
  "date" => "TEXT",
  "date_between" => "TEXT",
  "past_date" => "TEXT",
  "future_date" => "TEXT",
  "mobile_device_release_date" => "TEXT",
  "now" => "TEXT",
  "timestamp" => "TEXT",
  "datetime" => "TEXT",
  "text" => "TEXT",
  "name" => "TEXT",
  "full_name" => "TEXT",
  "first_name" => "TEXT",
  "last_name" => "TEXT",
  "phone" => "TEXT",
  "city" => "TEXT",
  "country" => "TEXT",
  "country_code" => "TEXT",
  "postal_code" => "TEXT",
  "state" => "TEXT",
  "street" => "TEXT",
  "address" => "TEXT",
  "username" => "TEXT",
  "social_handle" => "TEXT",
  "domain" => "TEXT",
  "user_agent" => "TEXT",
  "mac_address" => "TEXT",
  "iban" => "TEXT",
  "credit_card" => "TEXT",
  "currency" => "TEXT",
  "currency_code" => "TEXT",
  "paragraph" => "TEXT",
  "sentence" => "TEXT",
  "word" => "TEXT",
  "message_text" => "TEXT",
  "hashtag_text" => "TEXT",
  "snowflake_id" => "INTEGER",
  "numeric_string_id" => "INTEGER",
  "number" => "INTEGER",
  "integer" => "INTEGER",
  "age" => "INTEGER",
  "airport_elevation" => "INTEGER",
  "discount" => "INTEGER",
  "formula" => "INTEGER",
  "row_number" => "INTEGER",
  "sequence" => "INTEGER",
  "float" => "REAL",
  "money" => "REAL",
  "latitude" => "REAL",
  "longitude" => "REAL",
  "airport_latitude" => "REAL",
  "airport_longitude" => "REAL",
  "product_price" => "REAL",
  "tax_rate" => "REAL",
  "transaction_amount" => "REAL",
  "id_sequence" => "INTEGER PRIMARY KEY AUTOINCREMENT",
  "boolean" => "INTEGER",
  "object" => "TEXT",
  "map_by_field" => "TEXT",
  "array" => "TEXT",
  "empty_array" => "TEXT",
  "json_array" => "TEXT",
  "indices_pair" => "TEXT"
}.freeze

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

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

Class Method Details

.export_all(registry, **options) ⇒ String

Export all schemas in a registry

Parameters:

  • registry (Registry) —

    the registry

  • options (Hash) —

    export options

Returns:

  • (String) —

    SQL statements



262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/synthra/export/sql.rb', line 262

def self.export_all(registry, **options)
  dialect = options[:dialect] || :postgresql
  lines = []
  lines << "-- Generated from Synthra schemas"
  lines << "-- Dialect: #{dialect.to_s.upcase}"
  lines << "-- Schemas: #{registry.names.join(', ')}"
  lines << ""

  registry.schemas.each do |name, schema|
    exporter = new(schema, registry: registry, **options)
    lines << exporter.build_table(schema, dialect)
    lines << ""
  end

  lines.join("\n")
end

Instance Method Details

#build_column(field, type_mappings, dialect) ⇒ Object (private) 



332
333
334
335
336
337
338
339
340
341
 
# File 'lib/synthra/export/sql.rb', line 332

def build_column(field, type_mappings, dialect)
  column_name = to_snake_case(field.name)
  sql_type = field_to_sql_type(field, type_mappings)

  parts = [column_name, sql_type]
  parts << "NOT NULL" unless field.optional? || field.nullable?
  parts << "DEFAULT NULL" if field.nullable? && !field.optional?

  parts.join(" ")
end

#build_enum_type(args, type_mappings) ⇒ Object (private) 



366
367
368
369
370
371
 
# File 'lib/synthra/export/sql.rb', line 366

def build_enum_type(args, type_mappings)
  values = args[:values]&.map { |v| v.respond_to?(:value) ? v.value : v } || []
  # For PostgreSQL, could create an ENUM type, but VARCHAR is more portable
  max_length = values.map(&:to_s).map(&:length).max || 50
  "VARCHAR(#{[max_length, 50].max})"
end

#build_table(target_schema, dialect) ⇒ Object 



279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/synthra/export/sql.rb', line 279

def build_table(target_schema, dialect)
  type_mappings = case dialect
                  when :mysql then MYSQL_TYPES
                  when :sqlite then SQLITE_TYPES
                  else POSTGRESQL_TYPES
                  end

  table_name = to_snake_case(target_schema.name)
  lines = []
  
  lines << "CREATE TABLE #{table_name} ("

  columns = target_schema.fields.map do |field|
    build_column(field, type_mappings, dialect)
  end

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

  lines.join("\n")
end

#collect_field_schemas(field, schemas) ⇒ Object (private) 



311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
 
# File 'lib/synthra/export/sql.rb', line 311

def collect_field_schemas(field, schemas)
  type_name = field.type_name

  if schema_reference?(type_name)
    ref_schema = get_schema(type_name)
    if ref_schema && !schemas.any? { |s| s.name == ref_schema.name }
      schemas << ref_schema
    end
  end

  if type_name == "array"
    element = field.type_args[:element]
    if element && schema_reference?(element.to_s)
      ref_schema = get_schema(element.to_s)
      if ref_schema && !schemas.any? { |s| s.name == ref_schema.name }
        schemas << ref_schema
      end
    end
  end
end

#collect_referenced_schemas ⇒ Object (private) 



303
304
305
306
307
308
309
 
# File 'lib/synthra/export/sql.rb', line 303

def collect_referenced_schemas
  schemas = []
  schema.fields.each do |field|
    collect_field_schemas(field, schemas)
  end
  schemas.uniq(&:name)
end

#export ⇒ Object 



232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/synthra/export/sql.rb', line 232

def export
  dialect = options[:dialect] || :postgresql
  lines = []

  # Add header comment
  lines << "-- Generated from Synthra schema: #{schema.name}"
  lines << "-- Dialect: #{dialect.to_s.upcase}"
  lines << "-- Version: #{schema.version}" if schema.version
  lines << ""

  # Export referenced schemas first
  exported = Set.new
  collect_referenced_schemas.each do |ref_schema|
    lines << build_table(ref_schema, dialect)
    lines << ""
    exported << ref_schema.name
  end

  # Export main schema
  lines << build_table(schema, dialect) unless exported.include?(schema.name)

  lines.join("\n")
end

#field_to_sql_type(field, type_mappings) ⇒ Object (private) 



343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
 
# File 'lib/synthra/export/sql.rb', line 343

def field_to_sql_type(field, type_mappings)
  type_name = field.type_name
  type_args = field.type_args

  case type_name
  when "enum"
    build_enum_type(type_args, type_mappings)
  when "const"
    type_mappings["text"]
  when "array", "one_of", "map_by_field"
    type_mappings["object"] || "TEXT"
  else
    if type_mappings[type_name]
      type_mappings[type_name]
    elsif schema_reference?(type_name)
      # Foreign key - use UUID or appropriate ID type
      type_mappings["uuid"] || "TEXT"
    else
      type_mappings["text"] || "TEXT"
    end
  end
end

#to_snake_case(str) ⇒ Object (private) 



373
374
375
376
377
 
# File 'lib/synthra/export/sql.rb', line 373

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