DPTW/MariaDB/DPTW.05.scores.my.sql

create database if not exists DPTW character set utf8mb4 collate utf8mb4_general_ci;
use DPTW;

delimiter ;^

	drop function if exists scores_set_validate;^
	create function scores_set_validate(
		$mode varchar(32), 
		$nick varchar(32), 
		$score integer
	) returns bigint begin 
		return (
			((case 
				when $mode is null then 1 << 0 
				when $mode = '' then 1 << 1 
			/*else ifnull((
				select 0 from Modes where date_out is null && name = $mode limit 1 
			), 1 << 2) end) << 0) | */
			else 0 end) << 0) | 
			((case 
				when $nick is null then 1 << 0 
				when $nick = '' then 1 << 1 
			else 0 end) << 3) | 
			((case 
				when $score is null then 1 << 0 
				when $score < 0 then 1 << 1 
			else 0 end) << 5) | 
		0);
	end;^
	
	drop procedure if exists modes_get;^
	create procedure modes_get(
		in $mode varchar(32), 
		out $id integer 
	) begin 
		
		set $id := (select id from Modes where date_out is null && name = $mode limit 1);
	
		if $id is null then begin 
			insert into Modes(name) values($mode);
			set $id := last_insert_id();
		end;end if;
	
	end;^
	
	drop procedure if exists nicks_get;^
	create procedure nicks_get(
		in $nick varchar(32), 
		out $id integer 
	) begin 
		
		set $id := (select id from Nicks where date_out is null && name = $nick limit 1);
	
		if $id is null then begin 
			insert into Nicks(name) values($nick);
			set $id := last_insert_id();
		end;end if;
	
	end;^

	drop procedure if exists scores_set;^
	create procedure scores_set(
		in `$session` text, 
		in $mode varchar(32), 
		in $nick varchar(32), 
		in $score integer, 
		out $error bigint, 
		out $id integer 
	) begin 
		
		declare `$database` varchar(64) default 'DPTW';
		declare `$procedure` varchar(64) default 'scores_set';
		declare $parameters text default json_set('{}', 
			'$.mode', $mode, 
			'$.nick', $nick, 
			'$.score', $score, 
			'$.id', 0
		);
		declare $session_id integer;
		declare $ip_id integer;
		declare $user_agent_id integer;
		declare exit handler for sqlexception begin
			get diagnostics condition 1
				@_status = returned_sqlstate,
				@_code = mysql_errno,
				@message = message_text;
			set $error := $error | 1 << 0;
			call exceptions_set($session_id, `$database`, `$procedure`, $ip_id, $user_agent_id, $parameters, $error, @message, @_status, @_code);
		end;
		
		set $id := 0;
		call sessions_full_validate(`$session`, $error, $session_id, $ip_id, $user_agent_id);
		set $error := $error | (scores_set_validate($mode, $nick, $score) << 13);
	
		if !$error then begin 
			
			declare $mode_id integer;
			declare $nick_id integer;
			declare $procedure_id integer;
		
			call modes_get($mode, $mode_id);
			call nicks_get($nick, $nick_id);
			call procedures_get(`$database`, `$procedure`, $procedure_id);
		
			insert into Scores(`session`, `procedure`, mode, nick, score) values
				($session_id, $procedure_id, $mode_id, $nick_id, $score);
			set $id := last_insert_id();
		
			set $parameters := json_set($parameters, '$.id', $id);
			
		end;end if;
	
		call logs_set($session_id, `$database`, `$procedure`, $parameters, $error, true);
		
	end;^

	drop function if exists scores_list_validate;^
	create function scores_list_validate(
		$mode varchar(32), 
		$page integer, 
		$items_per_page integer, 
		$nicks varchar(32)
	) returns bigint begin 
		return (
			((case 
				when $mode is null then 1 << 0 
				when $mode = '' then 1 << 1 
			else ifnull((
				select 0 from Modes where date_out is null && name = $mode limit 1 
			), 1 << 2) end) << 0) | 
			((case 
				when $page is null then 1 << 0 
				when $page < 1 then 1 << 1 
			else 0 end) << 3) | 
			((case 
				when $items_per_page is null then 1 << 0 
				when $items_per_page < 1 then 1 << 1 
			else 0 end) << 5) | 
			(if($nicks is null, 1 << 0, 0) << 7) | 
		0);
	end;^
	
	drop procedure if exists scores_list;^
	create procedure scores_list(
		in `$session` text, 
		in $mode varchar(32), 
		in $page integer, 
		in $items_per_page integer, 
		in $nicks varchar(32), 
		out $error bigint, 
		out $pages integer, 
		out $items integer
	) begin 
		
		declare `$database` varchar(64) default 'DPTW';
		declare `$procedure` varchar(64) default 'scores_list';
		declare $parameters text default json_set('{}', 
			'$.page', $page, 
			'$.items_per_page', $items_per_page, 
			'$.nicks', $nicks,  
			'$.pages', 0, 
			'$.items', 0
		);
		declare $session_id integer;
		declare $ip_id integer;
		declare $user_agent_id integer;
		declare exit handler for sqlexception begin
			get diagnostics condition 1
				@_status = returned_sqlstate,
				@_code = mysql_errno,
				@message = message_text;
			set $error := $error | 1 << 0;
			call exceptions_set($session_id, `$database`, `$procedure`, $parameters, $error, @message, @_status, @_code);
		end;
		
		set $pages := 0;
		set $items := 0;
		call sessions_full_validate(`$session`, $error, $session_id, $ip_id, $user_agent_id);
		set $error := $error | (scores_list_validate($mode, $page, $items_per_page, $nicks) << 13);
	
		if !$error then begin 
			
			declare $item_from integer;
			declare $mode_id integer default (select id from Modes where date_out is null && name = $mode limit 1);
			
			drop temporary table if exists DPTW_SCORES_LIST_TEMPORARY;
			create temporary table DPTW_SCORES_LIST_TEMPORARY as select * from (
				select *, rownum() as `position` from (select * from DPTW.ScoresView where mode_id = $mode_id) sublevel
			) subtable where ifnull($nicks, '') = '' || nick like concat('%', $nicks, '%');
		
			set $items := (select count(1) from DPTW_SCORES_LIST_TEMPORARY limit 1);
			set $pages := ceil($items / $items_per_page);
		
			if $page > $pages then 
				set $page := $pages;
			end if;
		
			set $item_from := ($page - 1) * $items_per_page;
		
			select * from DPTW_SCORES_LIST_TEMPORARY limit $item_from, $items_per_page;
		
			set $parameters := json_Set($parameters, 
				'$.pages', $pages, 
				'$.items', $items
			);
		
		end;end if;
	
		call logs_set($session_id, `$database`, `$procedure`, $parameters, $error, true);
		
	end;^

delimiter ;