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.

Engine: Mimer

Engine: MSSQL

Engine: MYSQL

Engine: PostgreSQL

These script examples are from one of my PostgreSQL application databases.

define.ryb_sf_getelapsedtimeutc()

-- 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;

define.ryb_sf_makeguid()

-- 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;

define.ryb_sf_random_string(integer)

-- 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;

Engine: SQLAnywhere

These have been tested out in an SQLAnywhere database.

define.sql_sf_GetElapsedTimeUTC

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

define.sql_sf_MakeGUID

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

Code Wiki Main Repositories

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