-
Notifications
You must be signed in to change notification settings - Fork 1
SQL backend engine scripts
There might be some additional backend engine SQL specific that you might need for initializing fields from the SQL side. Something that returns a UTC timestamp and something that returns a GUID/UUID. I mostly work with PostgreSQL so I can show the ones that I use. You can use them as examples to build your own for your backend. The internet might have additional examples available.
Over time, more scripts should make it to these Wiki pages. If you want to contribute something to the scripts of the supported DCT2SQL backends, your name will be listed as the contributor. It will be appreciated, by the folks getting started on their particular SQL engine backend.
Created with Claude by Roberto Renz from PostgreSQL example. NOT TESTED!
CREATE FUNCTION ryb_sf_getelapsedtimeutc ()
RETURNS BIGINT
AS
BEGIN
RETURN SECS_BETWEEN(TIMESTAMP '1970-01-01 00:00:00', CURRENT_TIMESTAMP(0) AT TIME ZONE UTC) * 1000;
END;
-- Drop existing function if it exists
DROP FUNCTION IF EXISTS define.ryb_sf_makeguid;
-- Optionally, create the schema if needed (depending on your permissions)
-- CREATE SCHEMA define;
CREATE FUNCTION define.ryb_sf_makeguid()
RETURNS VARCHAR(16)
-- You may specify DETERMINISTIC or NOT DETERMINISTIC as needed
MODIFIES SQL DATA
BEGIN
DECLARE chars VARCHAR(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE result VARCHAR(16) := '';
DECLARE i INT := 1;
DECLARE pos INT;
REPEAT
-- Generate a random float between 0 and 1
-- Multiply by length (36), use CEILING to get a position 1..36
SET pos = CEILING(RAND() * 36);
SET result = result || SUBSTRING(chars FROM pos FOR 1);
SET i = i + 1;
UNTIL i > 16 END REPEAT;
RETURN result;
END;
Created with Claude by Roberto Renz from PostgreSQL example. NOT TESTED!
CREATE FUNCTION dbo.ryb_sf_getelapsedtimeutc()
RETURNS BIGINT
AS
BEGIN
RETURN DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', SYSUTCDATETIME());
END;
GO
-- Drop if exists
IF OBJECT_ID('define.ryb_sf_makeguid', 'FN') IS NOT NULL
DROP FUNCTION define.ryb_sf_makeguid;
GO
-- Create the schema if needed
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'define')
EXEC('CREATE SCHEMA define');
GO
-- Create the function
CREATE FUNCTION define.ryb_sf_makeguid()
RETURNS NVARCHAR(50)
AS
BEGIN
DECLARE @chars NVARCHAR(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE @result NVARCHAR(50) = '';
DECLARE @i INT = 1;
WHILE @i <= 16
BEGIN
-- Random number 1–36
DECLARE @pos INT = CAST(RAND(CHECKSUM(NEWID())) * 36 + 1 AS INT);
SET @result = @result + SUBSTRING(@chars, @pos, 1);
SET @i += 1;
END
RETURN @result;
END;
GO
Created with Claude by Roberto Renz from PostgreSQL example. NOT TESTED!
DELIMITER //
CREATE FUNCTION ryb_sf_getelapsedtimeutc()
RETURNS BIGINT
DETERMINISTIC
BEGIN
RETURN UNIX_TIMESTAMP(UTC_TIMESTAMP(6)) * 1000;
END//
DELIMITER ;
-- Drop if exists
DROP FUNCTION IF EXISTS ryb_sf_makeguid;
DELIMITER $$
CREATE FUNCTION ryb_sf_makeguid()
RETURNS VARCHAR(16)
DETERMINISTIC
BEGIN
DECLARE chars VARCHAR(36) DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE result VARCHAR(16) DEFAULT '';
DECLARE i INT DEFAULT 1;
DECLARE pos INT;
WHILE i <= 16 DO
-- RAND() returns 0 ≤ x < 1, multiply by 36 → 0–35
SET pos = FLOOR(RAND() * 36) + 1;
SET result = CONCAT(result, SUBSTRING(chars, pos, 1));
SET i = i + 1;
END WHILE;
RETURN result;
END$$
DELIMITER ;
These script examples are from one of my PostgreSQL application databases.
-- FUNCTION: define.ryb_sf_getelapsedtimeutc()
-- DROP FUNCTION define.ryb_sf_getelapsedtimeutc();
CREATE OR REPLACE FUNCTION define.ryb_sf_getelapsedtimeutc()
RETURNS bigint
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
begin RETURN ( SELECT (EXTRACT(EPOCH FROM (SELECT NOW() AT TIME ZONE 'UTC')) * 1000) );
end
$BODY$;
ALTER FUNCTION define.ryb_sf_getelapsedtimeutc() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO res_dba WITH GRANT OPTION;
-- FUNCTION: define.ryb_sf_makeguid()
-- DROP FUNCTION define.ryb_sf_makeguid();
CREATE OR REPLACE FUNCTION define.ryb_sf_makeguid()
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare
chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}';
result text := '';
i integer := 0;
begin
for i in 1..16 loop
result := result || chars[ceil(35 * random())];
end loop;
return result;
end;
$BODY$;
ALTER FUNCTION define.ryb_sf_makeguid() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO res_dba WITH GRANT OPTION;
-- FUNCTION: define.ryb_sf_random_string(integer)
-- DROP FUNCTION define.ryb_sf_random_string(integer);
CREATE OR REPLACE FUNCTION define.ryb_sf_random_string(integer)
RETURNS text
LANGUAGE 'sql'
COST 100
VOLATILE
AS $BODY$
SELECT array_to_string( ARRAY (
SELECT substring( '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
FROM ( ceil(random()*62))::int FOR 1 )
FROM generate_series(1, $1)
), '' )
$BODY$;
ALTER FUNCTION define.ryb_sf_random_string(integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO res_dba WITH GRANT OPTION;
These have been tested out in an SQLAnywhere database.
ALTER FUNCTION "define"."sql_sf_GetElapsedTimeUTC"()
returns real
not deterministic
begin
declare "RetVal" real;
set "RetVal" = (select "DATEDIFF"("millisecond","DATETIME"('1970-01-01 00:00:00.000'),current utc timestamp));
return "RetVal"
end
ALTER FUNCTION "define"."sql_sf_MakeGUID"()
returns char(16)
not deterministic
begin
declare "RetVal" varchar(16);
declare "i" integer;
declare @dow array(36) of char(1) =
"array"
('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','0','1','2','3','4','5','6','7','8','9');
select @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ]
|| @dow [ [ "FLOOR"("RAND"()*36)+1 ] ] into "RetVal";
return "RetVal"
end
Valhalla Calling - Miracle of Sound
[Verse 1]
Ships on vigor of the waves are skimming
Barren summits to the verdant plains
Each horizon is a new beginning
Rise and reign
Far from the Fjords and the ice cold currents
Ravens soar over new frontiers
Songs and sagas of a fate determined
Shields and spears
Vows of favour or the thrill of plunder
Pull together for the clan and kin
Clank of hammers and the crash of thunder
Pound within
[Chorus]
Oh-ho-oh
The echoes of eternity
Oh-ho-oh
Valhalla calling me
Oh-ho-oh
To pluck the strings of destiny
Oh-ho-oh
Valhalla calling me
Valhalla calling me
[Verse 2]
Sails a' swaying on the crimson rivers
Blood and glory in the fighting fields
Shields a' shatter into splintered timbers
Iron and steel
Fires are rising and the bells are ringing
Glory take us into Odin's halls
Golden glimmer and the sound of singing
Asgard's call
[Chorus]
Oh-ho-oh
The echoes of eternity
Oh-ho-oh
Valhalla calling me
Oh-ho-oh
To pluck the strings of destiny
Oh-ho-oh
Valhalla calling me
Valhalla calling me
[Bridge]
Wind and the waves will carry me
Wind and the waves will set me free
Wind and the waves will carry me
Wind and the waves will set me free
[Chorus]
Oh-ho-oh
The echoes of eternity
Oh-ho-oh
Valhalla calling me
Oh-ho-oh
To pluck the strings of destiny
Oh-ho-oh
Valhalla calling me
Valhalla calling me
Copyright (c) 2020, Roberto Artigas, All rights reserved world wide. - roberto.artigas.dev@gmail.com