7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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
|
# File 'lib/uuid_v7/patches/mysql/fx_migration.rb', line 7
def populate_uuid_field(table_name:, column_name:)
table_name = table_name.to_s.to_sym
column_name = column_name.to_s.to_sym
raise ArgumentError, "Column #{column_name} does not exist on table #{table_name}" unless column_exists?(table_name, column_name)
connection.execute <<~SQL
DROP FUNCTION IF EXISTS uuid_v7;
SQL
connection.exec_query <<~SQL.squish
CREATE FUNCTION uuid_v7 (time_of_creation DATETIME)
RETURNS BINARY(16)
LANGUAGE SQL
NOT DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
BEGIN
DECLARE uuid CHAR(36);
DECLARE undashed_uuid CHAR(32);
DECLARE currentTime BIGINT;
DECLARE buuid BINARY(16);
SET currentTime = UNIX_TIMESTAMP(time_of_creation) * 1000 + FLOOR(MICROSECOND(NOW(6)) / 1000);
SET uuid = LOWER(
CONCAT(
LPAD(HEX(currentTime), 12, '0'), '-7',
LPAD(HEX(FLOOR(RAND() * 0x1000)), 3, '0'), '-',
HEX(0x8000 | FLOOR(RAND() * 0x4000)), '-',
LPAD(HEX(FLOOR(RAND() * 0x1000000000000)), 12, '0')
)
);
SET undashed_uuid = REPLACE(uuid, '-', '');
SET buuid = UNHEX(undashed_uuid);
RETURN buuid;
END
SQL
result = connection.exec_query(<<~SQL)
SELECT COUNT(*) AS count FROM #{table_name} WHERE created_at IS NULL;
SQL
raise ActiveRecord::RecordInvalid, "There are records with NULL created_at in #{table_name}" if (result.rows[0][0]).positive?
connection.execute <<~SQL
UPDATE #{table_name} SET #{column_name} = uuid_v7(created_at) WHERE #{column_name} IS NULL;
SQL
connection.execute <<~SQL
DROP FUNCTION IF EXISTS uuid_v7;
SQL
end
|