Skip to content

armgit / Vvveb

Public Imported from GitHub
0

Powerful and easy to use cms to build websites, blogs or ecommerce stores.

3.8 KB
Raw Blame
-- admin_auth_token

	-- get all entries

	CREATE PROCEDURE getAll(
		IN start INT,
		IN limit INT,
		IN cursor INT, -- current cursor (admin_auth_token_id) for cursor pagination
		IN admin_id INT,
		IN updated_at CHAR,
		
		-- return array of admin_auth_token 
		OUT fetch_all,
		OUT fetch_one
	)
	BEGIN
        
        SELECT * FROM admin_auth_token WHERE 1 = 1 

        	@IF isset(:admin_id) AND !empty(:admin_id)
			THEN 
				AND admin_auth_token.admin_id = :admin_id 
        	END @IF	

			@IF isset(:updated_at) AND !empty(:updated_at)
			THEN 
				AND admin_auth_token.updated_at = :updated_at
        	END @IF	

			-- cursor pagination

			@IF isset(:cursor)

			THEN

			    AND admin_auth_token.admin_auth_token_id > :cursor

			END @IF

			ORDER BY admin_auth_token.admin_id, admin_auth_token.updated_at
			
			-- limit
			@IF isset(:limit)
			THEN
				LIMIT :limit OFFSET :start
			END @IF;		

		-- SELECT FOUND_ROWS() as count;
		SELECT count(*) FROM (
			
			@SQL_COUNT(admin_auth_token.admin_id, admin) -- this takes previous query removes limit and replaces select columns with parameter admin_id
			
		) as count;				
        
    END

	-- get admin information

	CREATE PROCEDURE get(
		IN admin_id INT,
		IN updated_at CHAR,
		IN username CHAR,
		IN email CHAR,
		IN status INT,
		IN role_id INT,
		
		OUT fetch_row
	)
	BEGIN
        
        SELECT _.* FROM admin_auth_token AS _ 
			LEFT JOIN admin ON (admin.admin_id = _.admin_id)
		
		WHERE 1 = 1


		@IF isset(:admin_id) AND !empty(:admin_id)
		THEN 
			AND _.admin_id = :admin_id 
		END @IF	

		@IF isset(:updated_at) AND !empty(:updated_at)
		THEN 
			AND _.updated_at = :updated_at
		END @IF	
		
		@IF isset(:username)
		THEN 
			AND admin.username = :username 
		END @IF	

		@IF isset(:email)
		THEN 
			AND admin.email = :email 
		END @IF			

		@IF isset(:status)
		THEN 
			AND admin.status = :status 
		END @IF	            
			
		@IF isset(:role_id)
		THEN 
			AND admin.role_id = :role_id 
		END @IF		
		
		LIMIT 1;
        
    END
	
	-- Add new admin_auth_token

	CREATE PROCEDURE add(
		IN admin_auth_token ROW,
		OUT fetch_one
	)
	BEGIN
		
		-- allow only table fields and set defaults for missing values
		@FILTER(:admin_auth_token, admin_auth_token)
		
		INSERT INTO admin_auth_token 
			
			( @KEYS(:admin_auth_token) )
			
	  	VALUES ( :admin_auth_token )
		RETURNING admin_auth_token_id;
	END    
    

	-- Update admin_auth_token 
	
	CREATE PROCEDURE edit(
		IN admin_id INT,
		IN admin_auth_token ROW,
		OUT affected_rows
	)
	BEGIN
		-- allow only table fields and set defaults for missing values
		@FILTER(:admin_auth_token, admin_auth_token)

		UPDATE admin_auth_token 
			
			SET @LIST(:admin_auth_token) 
			
		WHERE admin_id = :admin_id;

	END
	
	CREATE PROCEDURE update(
		IN admin_id INT,
		IN admin_auth_token ROWS,
		OUT affected_rows
	)
	BEGIN
		-- allow only table fields and set defaults for missing values (array mode: filter each row)
		:admin_auth_token = @FILTER(:admin_auth_token, admin_auth_token, false, true)

		@EACH(:admin_auth_token) 
			INSERT INTO admin_auth_token 
		
				( @KEYS(:each), admin_id )
			
			VALUES ( :each, :admin_id)
			ON CONFLICT ("admin_auth_token_id") DO UPDATE SET "token" = :each.token, "description" = :each.description;

	END

	-- delete admin_auth_token

	CREATE PROCEDURE delete(
		IN admin_id INT,
		IN admin_auth_token_id ARRAY,

		OUT affected_rows
	)
	BEGIN

		DELETE FROM admin_auth_token USING admin  
		WHERE 
		
			admin.admin_id = admin_auth_token.admin_id

		@IF isset(:admin_id) AND !empty(:admin_id)
		THEN 
			AND admin.admin_id = :admin_id 
		END @IF	

		@IF isset(:username)
		THEN 
			AND admin.username = :username 
		END @IF	

		@IF isset(:email)
		THEN 
			AND admin.email = :email 
		END @IF			

		@IF isset(:admin_auth_token_id)
		THEN 
			AND admin_auth_token.admin_auth_token_id IN (:admin_auth_token_id)
		END @IF	
		
	END