Module: ActiveRecord::ConnectionAdapters::Duckdb::SchemaStatements

Included in:
ActiveRecord::ConnectionAdapters::DuckdbAdapter
Defined in:
lib/active_record/connection_adapters/duckdb/schema_statements.rb

Overview

DuckDB-specific schema statement implementations Provides DuckDB-specific functionality for table, sequence, and index management

Instance Method Summary collapse

Instance Method Details

#create_sequence(sequence_name, start_with: 1, increment_by: 1, **options) ⇒ void

This method returns an undefined value.

Creates a sequence in DuckDB

Parameters:

  • sequence_name (String) —

    The name of the sequence to create

  • start_with (Integer) (defaults to: 1) —

    The starting value for the sequence (default: 1)

  • increment_by (Integer) (defaults to: 1) —

    The increment value for the sequence (default: 1)

  • options (Hash) —

    Additional sequence options



73
74
75
76
77
78
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 73

def create_sequence(sequence_name, start_with: 1, increment_by: 1, **options)
  sql = "CREATE SEQUENCE #{quote_table_name(sequence_name)}"
  sql << " START #{start_with}" if start_with != 1
  sql << " INCREMENT #{increment_by}" if increment_by != 1
  execute(sql, 'Create Sequence')
end

#create_table(table_name, id: :primary_key, primary_key: nil, **options) ⇒ void

This method returns an undefined value.

Creates a table with DuckDB-specific handling for sequences and primary keys

Parameters:

  • table_name (String, Symbol) —

    The name of the table to create

  • id (Symbol, Boolean) (defaults to: :primary_key) —

    The primary key type or false for no primary key

  • primary_key (String, Symbol, nil) (defaults to: nil) —

    Custom primary key column name

  • options (Hash) —

    Additional table creation options



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
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 38

def create_table(table_name, id: :primary_key, primary_key: nil, **options)
  # Handle sequence creation for integer primary keys BEFORE table creation
  sequence_name = nil
  pk_column_name = nil
  needs_sequence_default = false
  if id != false && id != :uuid && id != :string
    pk_column_name = primary_key || 'id'
    sequence_name = "#{table_name}_#{pk_column_name}_seq"
    needs_sequence_default = true
    # Extract sequence start value from options
    start_with = options.dig(:sequence, :start_with) || options[:start_with] || 1
    create_sequence_safely(sequence_name, table_name, start_with: start_with)
  end

  # Store sequence info for later use during table creation
  @pending_sequence_default = ({ table: table_name, column: pk_column_name, sequence: sequence_name } if needs_sequence_default && sequence_name && pk_column_name)

  begin
    # Now create the table with Rails handling the standard creation
    super do |td|
      # If block given, let user define columns
      yield td if block_given?
    end
  ensure
    # Clear the pending sequence default
    @pending_sequence_default = nil
  end
end

#create_table_definition(name) ⇒ ActiveRecord::ConnectionAdapters::Duckdb::TableDefinition

Creates a DuckDB-specific table definition instance

Parameters:

  • name (String, Symbol) —

    The table name

Returns:



28
29
30
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 28

def create_table_definition(name, **)
  TableDefinition.new(self, name, **)
end

#data_source_sql(name = nil, type: nil) ⇒ String

Generates SQL for querying data sources (tables/views) with optional filtering

Parameters:

  • name (String, nil) (defaults to: nil) —

    Optional table name to filter by

  • type (String, nil) (defaults to: nil) —

    Optional table type filter (‘BASE TABLE’, ‘VIEW’, etc.)

Returns:

  • (String) —

    SQL query string for retrieving table information



184
185
186
187
188
189
190
191
192
193
194
195
196
197
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 184

def data_source_sql(name = nil, type: nil)
  scope = quoted_scope(name, type: type)

  sql = 'SELECT table_name FROM information_schema.tables'

  conditions = []
  conditions << "table_schema = #{scope[:schema]}" if scope[:schema]
  conditions << "table_name = #{scope[:name]}" if scope[:name]
  conditions << scope[:type] if scope[:type] # This now contains the full condition

  sql += " WHERE #{conditions.join(" AND ")}" if conditions.any?
  sql += ' ORDER BY table_name'
  sql
end

#drop_sequence(sequence_name, if_exists: false) ⇒ void

This method returns an undefined value.

Drops a sequence from the database

Parameters:

  • sequence_name (String) —

    The name of the sequence to drop

  • if_exists (Boolean) (defaults to: false) —

    Whether to use IF EXISTS clause (default: false)



84
85
86
87
88
89
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 84

def drop_sequence(sequence_name, if_exists: false)
  sql = +'DROP SEQUENCE'
  sql << ' IF EXISTS' if if_exists
  sql << " #{quote_table_name(sequence_name)}"
  execute(sql, 'Drop Sequence')
end

#execute(sql, name = nil) ⇒ DuckDB::Result

Override execute to intercept CREATE TABLE statements and inject sequence defaults

Parameters:

  • sql (String) —

    The SQL statement to execute

  • name (String, nil) (defaults to: nil) —

    Optional name for logging purposes

Returns:

  • (DuckDB::Result) —

    The result of the query execution



351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 351

def execute(sql, name = nil)
  # Check if this is a CREATE TABLE statement and we have a pending sequence default
  if @pending_sequence_default && sql.match?(/\A\s*CREATE TABLE/i)
    pending = @pending_sequence_default
    table_pattern = /CREATE TABLE\s+"?#{Regexp.escape(pending[:table])}"?\s*\(/i

    if sql.match?(table_pattern)
      # Find the PRIMARY KEY column definition and inject the sequence default
      # This pattern specifically looks for the primary key column with PRIMARY KEY constraint
      pk_column_pattern = /"?#{Regexp.escape(pending[:column])}"?\s+\w+\s+PRIMARY\s+KEY(?!\s+DEFAULT)/i

      # Only replace the first occurrence (the actual primary key)
      sql = sql.sub(pk_column_pattern) do |match|
        # Inject the sequence default before PRIMARY KEY
        match.sub(/(\s+)PRIMARY\s+KEY/i, "\\1DEFAULT nextval('#{pending[:sequence]}') PRIMARY KEY")
      end
    end
  end

  super
end

#indexes(table_name) ⇒ Array<ActiveRecord::ConnectionAdapters::IndexDefinition>

Returns indexes for a specific table

Parameters:

  • table_name (String, Symbol) —

    The name of the table

Returns:

  • (Array<ActiveRecord::ConnectionAdapters::IndexDefinition>) —

    Array of index definitions



139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 139

def indexes(table_name)
  indexes = []
  begin
    result = execute("SELECT * FROM duckdb_indexes() WHERE table_name = #{quote(table_name.to_s)}", 'SCHEMA')
    # Store result as array immediately to avoid consumption issues
    result_array = result.to_a
    result_array.each_with_index do |index_row, _idx|
      # DuckDB duckdb_indexes() returns array with structure:
      # [database_name, database_oid, schema_name, schema_oid, index_name, index_oid, table_name, table_oid, nil, {}, is_unique, is_primary, column_names, sql]
      index_name = index_row[4]
      is_unique = index_row[10]
      is_primary = index_row[11]
      column_names_str = index_row[12]
      # Skip primary key indexes as they're handled separately
      next if is_primary

      # Skip if we don't have essential information
      next unless index_name && column_names_str

      # Parse column names from string format like "[name]" or "['name']"
      columns = parse_index_columns(column_names_str)
      next if columns.empty?

      # Clean up column names - remove extra quotes
      cleaned_columns = columns.map { |col| col.gsub(/^"|"$/, '') }

      # Create IndexDefinition with correct Rails 8.0 signature
      index_def = ActiveRecord::ConnectionAdapters::IndexDefinition.new(
        table_name.to_s,      # table
        index_name.to_s,      # name
        !is_unique.nil?, # unique
        cleaned_columns # columns
      )
      indexes << index_def
    end
  rescue StandardError => e
    Rails.logger&.warn("Could not retrieve indexes for table #{table_name}: #{e.message}") if defined?(Rails)
  end
  indexes
end

#lookup_cast_type_from_column(sql_type_metadata) ⇒ ActiveRecord::Type::Value

Looks up the appropriate cast type for a column based on SQL type metadata

Parameters:

  • sql_type_metadata (ActiveRecord::ConnectionAdapters::SqlTypeMetadata) —

    The SQL type metadata

Returns:

  • (ActiveRecord::Type::Value) —

    The appropriate cast type



267
268
269
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 267

def lookup_cast_type_from_column(sql_type_metadata)
  lookup_cast_type(sql_type_metadata.sql_type)
end

#new_column_from_field(table_name, field, definitions) ⇒ ActiveRecord::ConnectionAdapters::Duckdb::Column

Creates a new Column object from DuckDB field information

Parameters:

  • table_name (String) —

    The name of the table

  • field (Array) —

    Array containing column field information from PRAGMA table_info

  • definitions (Hash) —

    Additional column definitions (unused)

Returns:



204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 204

def new_column_from_field(table_name, field, definitions)
  column_name, formatted_type, column_default, not_null, _type_id, _type_modifier, collation_name, comment, _identity, _generated, pk = field

  # Ensure we have required values with proper defaults
  column_name = 'unknown_column' if column_name.nil? || column_name.empty?

  formatted_type = formatted_type.to_s if formatted_type
  formatted_type = 'VARCHAR' if formatted_type.nil? || formatted_type.empty?

  # Create proper SqlTypeMetadata object
  sql_type_metadata = fetch_type_metadata(formatted_type)

  # For primary keys with integer types, check if sequence exists and set default_function
  default_value = nil
  default_function = nil

  if pk && [true,
            1].include?(pk) && %w[INTEGER BIGINT].include?(formatted_type.to_s.upcase) && column_name == 'id'
    # This is an integer primary key named 'id' - assume sequence exists
    sequence_name = "#{table_name}_#{column_name}_seq"
    default_function = "nextval('#{sequence_name}')"
    default_value = nil
  elsif column_default&.to_s&.include?('nextval(')
    # This is a sequence - store it as default_function, not default_value
    default_function = column_default.to_s
    default_value = nil
  else
    default_value = extract_value_from_default(column_default)
    default_function = nil
  end

  # Ensure boolean values are properly converted for null constraint
  # In DuckDB PRAGMA: not_null=1 means NOT NULL, not_null=0 means NULL allowed
  is_null = case not_null
            when 1, true
              false  # Column does NOT allow NULL
            else
              true   # Default to allowing NULL for unknown values
            end

  # Clean up parameters for Column constructor
  clean_column_name = column_name.to_s
  clean_default_value = default_value
  clean_default_function = default_function&.to_s
  clean_collation = collation_name&.to_s
  clean_comment = comment&.to_s

  ActiveRecord::ConnectionAdapters::Duckdb::Column.new(
    clean_column_name,       # name
    clean_default_value,     # default (should be nil for sequences!)
    sql_type_metadata,       # sql_type_metadata
    is_null,                 # null (boolean - true if column allows NULL)
    clean_default_function,  # default_function (this is where nextval goes!)
    collation: clean_collation.presence,
    comment: clean_comment.presence,
    auto_increment: pk && %w[INTEGER BIGINT].include?(formatted_type.to_s.upcase),
    rowid: pk && column_name == 'id'
  )
end

#next_sequence_value(sequence_name) ⇒ String

Returns SQL expression to get the next value from a sequence

Parameters:

  • sequence_name (String) —

    The name of the sequence

Returns:

  • (String) —

    SQL expression for getting next sequence value



117
118
119
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 117

def next_sequence_value(sequence_name)
  "nextval('#{sequence_name}')"
end

#quoted_scope(name = nil, type: nil) ⇒ Hash

Creates a quoted scope hash for table/schema queries with type filtering

Parameters:

  • name (String, nil) (defaults to: nil) —

    Optional table name (may include schema prefix)

  • type (String, nil) (defaults to: nil) —

    Optional table type filter

Returns:

  • (Hash) —

    Hash containing quoted schema, name, and type condition



275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 275

def quoted_scope(name = nil, type: nil)
  schema, name = extract_schema_qualified_name(name)

  type_condition = case type
                   when 'BASE TABLE'
                     "table_type = 'BASE TABLE'"
                   when 'VIEW'
                     "table_type = 'VIEW'"
                   else
                     "table_type IN ('BASE TABLE', 'VIEW')"
                   end

  {
    schema: schema ? quote(schema) : nil,
    name: name ? quote(name) : nil,
    type: type_condition
  }
end

#reset_sequence!(sequence_name, value = 1) ⇒ void

This method returns an undefined value.

Resets a sequence to a specific value

Parameters:

  • sequence_name (String) —

    The name of the sequence to reset

  • value (Integer) (defaults to: 1) —

    The value to reset the sequence to (default: 1)



125
126
127
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 125

def reset_sequence!(sequence_name, value = 1)
  execute("ALTER SEQUENCE #{quote_table_name(sequence_name)} RESTART WITH #{value}", 'Reset Sequence')
end

#schema_creation ⇒ ActiveRecord::ConnectionAdapters::Duckdb::SchemaCreation

Returns a DuckDB-specific schema creation instance

Returns:



14
15
16
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 14

def schema_creation
  SchemaCreation.new(self)
end

#sequence_exists?(sequence_name) ⇒ Boolean

Checks if a sequence exists in the database

Parameters:

  • sequence_name (String) —

    The name of the sequence to check

Returns:

  • (Boolean) —

    true if the sequence exists, false otherwise



94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 94

def sequence_exists?(sequence_name)
  # Try to get next value from sequence in a way that doesn't consume it
  # Use a transaction that we can rollback to avoid side effects
  transaction do
    execute("SELECT nextval(#{quote(sequence_name)})", 'SCHEMA')
    raise ActiveRecord::Rollback # Rollback to avoid consuming the sequence value
  end
  true
rescue ActiveRecord::StatementInvalid, DuckDB::Error => e
  # If the sequence doesn't exist, nextval will fail with a specific error
  raise unless e.message.include?('does not exist') || e.message.include?('Catalog Error')

  false

# Re-raise other types of errors
rescue StandardError
  # For any other error, assume sequence doesn't exist
  false
end

#sequences ⇒ Array<String>

Returns a list of all sequences in the database

Returns:

  • (Array<String>) —

    Array of sequence names (currently returns empty array)



131
132
133
134
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 131

def sequences
  # For now, return empty array since DuckDB sequence introspection is limited
  []
end

#tables ⇒ Array<String>

Returns a list of all tables in the database

Returns:

  • (Array<String>) —

    Array of table names



20
21
22
23
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 20

def tables
  result = execute(data_source_sql(type: 'BASE TABLE'), 'SCHEMA')
  result.to_a.map { |row| row[0] }
end

#type_to_sql(type, limit: nil, precision: nil, scale: nil, **options) ⇒ String

Converts ActiveRecord type to DuckDB SQL type string

Parameters:

  • type (Symbol, String) —

    The ActiveRecord type to convert

  • limit (Integer, nil) (defaults to: nil) —

    Optional column size limit

  • precision (Integer, nil) (defaults to: nil) —

    Optional decimal precision

  • scale (Integer, nil) (defaults to: nil) —

    Optional decimal scale

  • options (Hash) —

    Additional type options

Returns:

  • (String) —

    The DuckDB SQL type string



301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
 
# File 'lib/active_record/connection_adapters/duckdb/schema_statements.rb', line 301

def type_to_sql(type, limit: nil, precision: nil, scale: nil, **options)
  case type.to_s
  when 'primary_key'
    # Use the configured primary key type
    primary_key_type_definition
  when 'string', 'text'
    if limit
      "VARCHAR(#{limit})"
    else
      'VARCHAR'
    end
  when 'integer'
    integer_to_sql(limit)
  when 'bigint'
    'BIGINT'
  when 'float'
    'REAL'
  when 'decimal', 'numeric'
    if precision && scale
      "DECIMAL(#{precision},#{scale})"
    elsif precision
      "DECIMAL(#{precision})"
    else
      'DECIMAL'
    end
  when 'datetime', 'timestamp'
    'TIMESTAMP'
  when 'time'
    'TIME'
  when 'date'
    'DATE'
  when 'boolean'
    'BOOLEAN'
  when 'binary', 'blob'
    # TODO: Add blob size limits
    # Postgres has limits set on blob sized
    # https://github.com/rails/rails/blob/82e9029bbf63a33b69f007927979c5564a6afe9e/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb#L855
    # Duckdb has a 4g size limit as well - https://duckdb.org/docs/stable/sql/data_types/blob
    'BLOB'
  when 'uuid'
    'UUID'
  else
    super
  end
end