Skip to content

armgit / Vvveb

Public Imported from GitHub
0

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

2.5 KB
Raw Blame
-- Languages

	-- get all languages

	CREATE PROCEDURE getAll(
		IN status INT,
		IN start INT,
		IN limit INT,
		IN cursor INT, -- current cursor (language_id) for cursor pagination
		OUT fetch_all, 
		OUT fetch_one 
	)
	BEGIN
		-- language
		SELECT *, slug as array_key
			FROM language WHERE 1 = 1
			
			@IF isset(:status) 
			THEN
				AND status = :status
			END @IF
			
			-- cursor pagination
			
			@IF isset(:cursor)
			
			THEN
			
				AND language.language_id > :cursor
			
			END @IF
			
			ORDER BY "default" DESC, status DESC, sort_order
			
			-- limit
			@IF isset(:limit)
			THEN
				LIMIT :limit OFFSET :start 
			END @IF;
			
			SELECT count(*) FROM (
					
					@SQL_COUNT(language.language_id, language) -- this takes previous query removes limit and replaces select columns with parameter product_id
					
			) as count;			
	END	
	
	-- get language

	CREATE PROCEDURE get(
		IN language_id INT,
		IN code CHAR,
		IN default INT,
		IN status INT,
		OUT fetch_row 
	)
	BEGIN
		-- language
		SELECT *, language_id
			FROM language as _ 
		WHERE 1 = 1 
			
			@IF isset(:language_id) 
			THEN
				AND language_id = :language_id
			END @IF
			
			@IF isset(:code) 
			THEN
				AND code = :code
			END @IF

			@IF isset(:default) 
			THEN
				AND "default" = :default
			END @IF
			
			@IF isset(:status) 
			THEN
				AND status = :status
			END @IF

		LIMIT 1;
	END
	
	-- add language

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

	END

	-- delete language

	CREATE PROCEDURE delete(
		IN  language_id ARRAY,
		OUT affected_rows
	)
	BEGIN
		
		DELETE FROM language WHERE language_id IN (:language_id);
		
	END

	-- edit language
	
	CREATE PROCEDURE edit(
		IN language ROW,
		IN language_id INT,
		IN code CHAR,
		IN default INT,
		IN status INT,
		OUT affected_rows
	)
	BEGIN

		-- allow only table fields and set defaults for missing values
		@FILTER(:language, language)
		
		
		UPDATE language 
			
			SET @LIST(:language) 
			
		WHERE 1 = 1 

			@IF isset(:language_id) 
			THEN
				AND language_id = :language_id
			END @IF
			
			@IF isset(:code) 
			THEN
				AND code = :code
			END @IF
			
			@IF isset(:default) 
			THEN
				AND "default" = :default
			END @IF
			
			@IF isset(:status) 
			THEN
				AND status = :status
			END @IF
	END