Class: Rails::Nl2sql::SchemaBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/rails/nl2sql/schema_builder.rb

Constant Summary collapse

@@schema_cache = 
nil

Class Method Summary collapse

Class Method Details

.build_hash_schema(options = {}) ⇒ Object

Legacy method for backward compatibility



214
215
216
217
218
219
220
221
222
223
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 214

def self.build_hash_schema(options = {})
  tables = get_filtered_tables(options)
  
  schema = {}
  tables.each do |table|
    schema[table] = ActiveRecord::Base.connection.columns(table).map(&:name)
  end

  schema
end

.build_schema(options = {}) ⇒ Object 



6
7
8
9
10
11
12
13
14
15
16
17
18
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 6

def self.build_schema(options = {})
  if options.empty? && @@schema_cache
    return @@schema_cache
  end

  tables = get_filtered_tables(options)

  schema_text = build_schema_text(tables)

  @@schema_cache = schema_text if options.empty?

  schema_text
end

.build_schema_text(tables) ⇒ Object 



60
61
62
63
64
65
66
67
68
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 60

def self.build_schema_text(tables)
  schema_parts = []
  
  tables.each do |table|
    schema_parts << build_table_schema(table)
  end
  
  schema_parts.join("\n\n")
end

.build_table_schema(table) ⇒ Object 



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 70

def self.build_table_schema(table)
  begin
    columns = ActiveRecord::Base.connection.columns(table)
  rescue => e
    # Skip tables that can't be introspected (e.g., PostGIS system tables)
    Rails.logger.debug "Skipping table #{table} due to introspection error: #{e.message}" if defined?(Rails)
    return "-- Table #{table} skipped due to introspection error"
  end
  
  schema = "CREATE TABLE #{table} (\n"
  
  column_definitions = columns.map do |column|
    type_info = get_column_type_info(column)
    nullable = column.null ? "" : " NOT NULL"
    default = column.default ? " DEFAULT #{column.default}" : ""
    
    "  #{column.name} #{type_info}#{nullable}#{default}"
  end
  
  schema += column_definitions.join(",\n")
  schema += "\n);"
  
  # Add indexes and foreign keys if available
  indexes = get_table_indexes(table)
  if indexes.any?
    schema += "\n\n-- Indexes for #{table}:"
    indexes.each do |index|
      schema += "\n-- #{index[:type]}: #{index[:columns].join(', ')}"
    end
  end
  
  schema
end

.clear_cache! ⇒ Object 



20
21
22
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 20

def self.clear_cache!
  @@schema_cache = nil
end

.get_column_type_info(column) ⇒ Object 



104
105
106
107
108
109
110
111
112
113
114
115
116
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/rails/nl2sql/schema_builder.rb', line 104

def self.get_column_type_info(column)
  case column.type
  when :string
    "VARCHAR(#{column.limit || 255})"
  when :text
    "TEXT"
  when :integer
    "INTEGER"
  when :bigint
    "BIGINT"
  when :float
    "FLOAT"
  when :decimal
    precision = column.precision || 10
    scale = column.scale || 0
    "DECIMAL(#{precision},#{scale})"
  when :datetime
    "TIMESTAMP"
  when :date
    "DATE"
  when :time
    "TIME"
  when :boolean
    "BOOLEAN"
  when :json
    "JSON"
  else
    # Handle PostGIS geometry types and other spatial types
    sql_type = column.sql_type || "TEXT"
    case sql_type.downcase
    when /geometry/
      "GEOMETRY"
    when /geography/
      "GEOGRAPHY"
    when /point/
      "POINT"
    when /polygon/
      "POLYGON"
    when /linestring/
      "LINESTRING"
    else
      sql_type
    end
  end
end

.get_database_type ⇒ Object 



24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 24

def self.get_database_type
  adapter = ActiveRecord::Base.connection.adapter_name.downcase
  case adapter
  when 'postgresql'
    'PostgreSQL'
  when 'mysql', 'mysql2'
    'MySQL'
  when 'sqlite3'
    'SQLite'
  when 'oracle', 'oracleenhanced'
    'Oracle'
  when 'sqlserver'
    'SQL Server'
  else
    'PostgreSQL' # Default fallback
  end
end

.get_filtered_tables(options = {}) ⇒ Object 



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 42

def self.get_filtered_tables(options = {})
  all_tables = ActiveRecord::Base.connection.tables
  
  # Remove system tables
  all_tables.reject! { |table| system_table?(table) }
  
  # Apply filtering options
  if options[:exclude]
    all_tables -= options[:exclude]
  end
  
  if options[:include]
    all_tables = options[:include] & all_tables
  end
  
  all_tables
end

.get_table_indexes(table) ⇒ Object 



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 150

def self.get_table_indexes(table)
  indexes = []
  
  begin
    connection = ActiveRecord::Base.connection
    if connection.respond_to?(:indexes)
      table_indexes = connection.indexes(table)
      table_indexes.each do |index|
        indexes << {
          type: index.unique? ? "UNIQUE INDEX" : "INDEX",
          columns: index.columns,
          name: index.name
        }
      end
    end
  rescue => e
    # Skip if indexes can't be retrieved
  end
  
  indexes
end

.system_table?(table) ⇒ Boolean

Returns:

  • (Boolean) 


172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
 
# File 'lib/rails/nl2sql/schema_builder.rb', line 172

def self.system_table?(table)
  system_tables = [
    'schema_migrations',
    'ar_internal_metadata',
    'sqlite_sequence',
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
  ]
  
  # PostGIS system tables
  postgis_system_tables = [
    'geometry_columns',
    'geography_columns',
    'spatial_ref_sys',
    'raster_columns',
    'raster_overviews',
    'topology',
    'layer',
    'topology_layer'
  ]
  
  # PostgreSQL system schemas
  pg_system_schemas = [
    'pg_',
    'information_schema'
  ]
  
  # Check regular system tables
  return true if system_tables.any? { |sys_table| table.include?(sys_table) }
  
  # Check PostGIS system tables
  return true if postgis_system_tables.any? { |sys_table| table == sys_table }
  
  # Check PostgreSQL system schemas
  return true if pg_system_schemas.any? { |schema| table.start_with?(schema) }
  
  false
end