Module: CustomId::DbExtension

Defined in:
lib/custom_id/db_extension.rb

Overview

Note:

The shared-characters feature available in Concern is intentionally omitted here because cross-table lookups inside a trigger introduce concurrency risks and make schema evolution painful.

Optional database-side ID generation using PostgreSQL, MySQL or SQLite triggers.

This is an alternative to the Ruby-side Concern approach. Both achieve the same goal – prefixed, collision-resistant string IDs – but this module offloads the work to the database engine.

Trade-offs vs. the Ruby concern

Aspect Ruby concern DbExtension (DB trigger)
Portability Any AR adapter PG, MySQL, SQLite only
Bulk inserts Per-record callbacks Handled by the DB
Raw SQL inserts IDs not generated IDs always generated
Testability Easy (SQLite ok) Needs a real DB connection
Migration needed No Yes (install/uninstall)

Requirements

  • PostgreSQL: 9.6+ (uses gen_random_bytes from the pgcrypto extension).
  • MySQL: 5.7+ (uses RANDOM_BYTES).
  • SQLite: 3.0+ (uses randomblob). Non-PK columns use an AFTER INSERT trigger; NOT NULL primary key columns use a BEFORE INSERT trigger with RAISE(IGNORE) so the row is inserted with a generated ID before SQLite evaluates the NOT NULL constraint on the outer statement.

Usage in migrations (PostgreSQL example)

class CreateUsers < ActiveRecord::Migration[7.0]
def up
  enable_extension "pgcrypto"   # once per database

  create_table :users, id: :string do |t|
    t.string :name, null: false
    t.timestamps
  end

  CustomId::DbExtension.install_trigger!(connection, :users, prefix: "usr")
end

def down
  CustomId::DbExtension.uninstall_trigger!(connection, :users)
  drop_table :users
end
end

Constant Summary collapse

PG_ADAPTERS =

rubocop:disable Style/MutableConstant Adapters considered PostgreSQL-compatible.

%w[postgresql postgis].freeze
MYSQL_ADAPTERS = 
%w[mysql mysql2 trilogy].freeze
SQLITE_ADAPTERS = 
%w[sqlite sqlite3].freeze
SUPPORTED_ADAPTERS = 
(PG_ADAPTERS + MYSQL_ADAPTERS + SQLITE_ADAPTERS).freeze
ALPHABET =

Base58 characters

"123456789ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz"
PG_GENERATE_FUNCTION_SQL =

SQL that creates (or replaces) the reusable Base58 generator function. Depends on +pgcrypto+'s gen_random_bytes.

"CREATE OR REPLACE FUNCTION custom_id_base58(p_size INT DEFAULT 16)\nRETURNS TEXT AS $$\nDECLARE\n  chars      TEXT    := '\#{ALPHABET}';\n  result     TEXT    := '';\n  i          INT;\n  rand_bytes BYTEA;\nBEGIN\n  rand_bytes := gen_random_bytes(p_size);\n  FOR i IN 0..p_size - 1 LOOP\n    result := result || substr(chars, (get_byte(rand_bytes, i) % 58) + 1, 1);\n  END LOOP;\n  RETURN result;\nEND;\n$$ LANGUAGE plpgsql;\n"
PG_DROP_GENERATE_FUNCTION_SQL =

SQL that removes the shared Base58 generator function.

"DROP FUNCTION IF EXISTS custom_id_base58(INT);"
MYSQL_GENERATE_FUNCTION_SQL =

MySQL implementation of Base58 generator. Note: RANDOM_BYTES(N) returns binary data.

"CREATE FUNCTION IF NOT EXISTS custom_id_base58(p_size INT)\nRETURNS TEXT DETERMINISTIC\nBEGIN\n  DECLARE chars TEXT DEFAULT '\#{ALPHABET}';\n  DECLARE result TEXT DEFAULT '';\n  DECLARE i INT DEFAULT 0;\n  WHILE i < p_size DO\n    SET result = CONCAT(result, SUBSTR(chars, (ORD(RANDOM_BYTES(1)) % 58) + 1, 1));\n    SET i = i + 1;\n  END WHILE;\n  RETURN result;\nEND;\n"
MYSQL_DROP_GENERATE_FUNCTION_SQL =

rubocop:enable Style/MutableConstant

"DROP FUNCTION IF EXISTS custom_id_base58;"

Class Method Summary collapse

Class Method Details

.install_generate_function!(connection) ⇒ Object

Installs the shared Base58 generator function into the database. Safe to call multiple times (uses CREATE OR REPLACE or IF NOT EXISTS).

Parameters:

Raises:

  • when the adapter is not supported or (PG only) when the pgcrypto extension is not enabled.



126
127
128
129
130
131
132
133
134
135
136
137
138
139
 
# File 'lib/custom_id/db_extension.rb', line 126

def self.install_generate_function!(connection)
  assert_supported!(connection)

  case connection.adapter_name.downcase
  when *PG_ADAPTERS
    pg_assert_pgcrypto!(connection)
    connection.execute(PG_GENERATE_FUNCTION_SQL)
  when *MYSQL_ADAPTERS
    connection.execute(MYSQL_GENERATE_FUNCTION_SQL)
  when *SQLITE_ADAPTERS
    # SQLite doesn't support stored functions in the same way.
    # We'll embed the logic directly in the trigger.
  end
end

.install_trigger!(connection, table_name, prefix:, column: :id, size: 16) ⇒ Object

Note:

MySQL + ActiveRecord string PKs: MySQL's protocol does not return a trigger-generated string PK to the caller (unlike PostgreSQL's RETURNING). After an AR create, Rails reads LAST_INSERT_ID() which returns 0 for non-AUTO_INCREMENT columns, leaving the in-memory record with id = "0" while the database row is correct. Fix: also declare cid on the model so that AR generates the ID in Ruby before the INSERT. The trigger then acts only as a safety net for raw-SQL inserts that bypass ActiveRecord.

Installs both the per-table trigger function and the BEFORE INSERT trigger on table_name, auto-generating a prefixed custom ID when the column is NULL.

Idempotent: uses CREATE OR REPLACE FUNCTION and DROP TRIGGER IF EXISTS before re-creating the trigger.

Parameters:

  • Target table.

  • ID prefix (e.g. "usr").

  • (defaults to: :id)

    Column to populate (default: :id).

  • (defaults to: 16)

    Length of the random portion (default: 16).

Raises:

  • when the adapter is not supported.



176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/custom_id/db_extension.rb', line 176

def self.install_trigger!(connection, table_name, prefix:, column: :id, size: 16)
  assert_supported!(connection)

  adapter = connection.adapter_name.downcase
  case adapter
  when *PG_ADAPTERS
    pg_assert_pgcrypto!(connection)
    connection.execute(PG_GENERATE_FUNCTION_SQL)
    connection.execute(pg_trigger_function_sql(table_name, prefix: prefix, column: column, size: size))
    connection.execute(pg_create_trigger_sql(table_name, column: column))
  when *MYSQL_ADAPTERS
    connection.execute(MYSQL_GENERATE_FUNCTION_SQL)
    # mysql2/trilogy execute only one statement per call, so DROP and CREATE
    # must be sent separately (unlike PG which accepts multi-statement strings).
    connection.execute(mysql_drop_trigger_sql(table_name, column: column))
    connection.execute(mysql_create_trigger_sql(table_name, prefix: prefix, column: column, size: size))
  when *SQLITE_ADAPTERS
    sqlite_install_trigger!(connection, table_name, prefix: prefix, column: column, size: size)
  end
end

.supported?(connection) ⇒ Boolean

Returns true when connection targets a supported adapter.

Parameters:

Returns:



116
117
118
 
# File 'lib/custom_id/db_extension.rb', line 116

def self.supported?(connection)
  SUPPORTED_ADAPTERS.include?(connection.adapter_name.downcase)
end

.uninstall_generate_function!(connection) ⇒ Object

Removes the shared Base58 generator function from the database.

Parameters:

Raises:

  • when the adapter is not supported.



145
146
147
148
149
150
151
152
153
154
 
# File 'lib/custom_id/db_extension.rb', line 145

def self.uninstall_generate_function!(connection)
  assert_supported!(connection)

  case connection.adapter_name.downcase
  when *PG_ADAPTERS
    connection.execute(PG_DROP_GENERATE_FUNCTION_SQL)
  when *MYSQL_ADAPTERS
    connection.execute(MYSQL_DROP_GENERATE_FUNCTION_SQL)
  end
end

.uninstall_trigger!(connection, table_name, column: :id) ⇒ Object

Drops the per-table trigger and its companion trigger function from table_name.

Parameters:

  • Target table.

  • (defaults to: :id)

    Column the trigger was installed on (default: :id).

Raises:

  • when the adapter is not supported.



203
204
205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/custom_id/db_extension.rb', line 203

def self.uninstall_trigger!(connection, table_name, column: :id)
  assert_supported!(connection)

  adapter = connection.adapter_name.downcase
  case adapter
  when *PG_ADAPTERS
    connection.execute(pg_drop_trigger_sql(table_name, column: column))
  when *MYSQL_ADAPTERS
    connection.execute(mysql_drop_trigger_sql(table_name, column: column))
  when *SQLITE_ADAPTERS
    connection.execute(sqlite_drop_trigger_sql(table_name, column: column))
  end
end