What is the proper way of creating objects in 30.create-db.sql and specifically generating UUID for new ones so that later collision won’t appear during Cuba platform’s operation?
Say I want to add something like
INSERT INTO public.sys_config (id, create_ts, created_by, version, update_ts, updated_by, name, value_) VALUES ('c06faf75-3f92-3a32-07d5-3567c9139728', '2018-09-14 18:32:16.088000', 'admin', 1, '2018-09-14 18:32:16.088000', null, 'callbook.initialFTSReindexAllDone', 'true');
What UUID is safe to use next? Yes, I may create most objects via GUI and then copy insert scripts for them. It it the only way? Does this guarantee no collision during further operation?
You can use the newid()
function. It is native in Microsoft SQL Server and defined by CUBA init scripts for other databases. For example:
INSERT INTO public.sys_config (id, create_ts, created_by, version, update_ts, updated_by, name, value_)
VALUES (newid(), '2018-09-14 18:32:16.088000', 'admin', 1, '2018-09-14 18:32:16.088000', null, 'callbook.initialFTSReindexAllDone', 'true');
And how do I use the result of certain newid()
call?
Say:
insert into SEC_ROLE
(ID, VERSION, CREATE_TS, CREATED_BY, UPDATE_TS, UPDATED_BY, DELETE_TS, DELETED_BY, NAME, LOC_NAME, DESCRIPTION, ROLE_TYPE, IS_DEFAULT_ROLE)
values ('3ec31528-dc0e-c341-7727-7b46771ae9ff', 3, '2016-11-20 23:11:35', 'u1', '2018-08-22 16:15:12', 'admin', null, null, 'Пользователи', null, null, 0, true);
And then
insert into SEC_PERMISSION
(ID, VERSION, CREATE_TS, CREATED_BY, UPDATE_TS, UPDATED_BY, DELETE_TS, DELETED_BY, PERMISSION_TYPE, TARGET, VALUE_, ROLE_ID)
values ('41b120ca-2d15-7198-50d5-f4949ba12bf6', 1, '2018-08-21 17:26:24', 'm1a', '2018-08-21 17:26:24', null, null, null, 20, 'sec$Role:read', 0, '3ec31528-dc0e-c341-7727-7b46771ae9ff');
See that 3ec31528-dc0e-c341-7727-7b46771ae9ff
that’d be generated by newid()
call must be used in another statement…
(If I used values (newid(), 3, '2016-11-20 23:11:35', 'u1', '2018-08-22 16:15:12', 'admin', null, null, 'Пользователи', null, null, 0, true);
)
I see it’s db dependent…
Solved it for postgres 9.0+
sql - How to declare a variable in a PostgreSQL query - Stack Overflow
DO $$
DECLARE userRoleID uuid =newid();
BEGIN
INSERT INTO public.sys_config (id, create_ts, created_by, version, update_ts, updated_by, name, value_) VALUES ('399dde3c-0193-0c15-5fa8-87cfa9117203', '2018-09-14 19:16:19.322000', 'admin', 2, '2018-09-14 19:16:52.439000', 'admin', 'callbook.userRoleID', userRoleID);
insert into SEC_ROLE
(ID, VERSION, CREATE_TS, CREATED_BY, UPDATE_TS, UPDATED_BY, DELETE_TS, DELETED_BY, NAME, LOC_NAME, DESCRIPTION,
ROLE_TYPE, IS_DEFAULT_ROLE)
values (userRoleID, 3, '2016-11-20 23:11:35', 'u1', '2018-08-22 16:15:12', 'admin', null, null, 'Пользователи', null,
null, 0, true);
--other things using variable go here
END $$;