Identity MySQL DML User

CRUD stored procedures voor de User tabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een basiseenheid kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de gebruikersnaam en van het emailadres.

Design

Naam	Beschrijving
CategoryInsert	bevat 1 OUTPUT parameter om de nieuw Id te retourneren naar het calling programma
CategoryUpdate	deze stored procedure updatet alle kolommen van de rij met de opgegeven Id
CategorySelectOne	lees 1 rij in uit de tabel op basis van de Id, neem alle kolommen mee voor het detail venster
CategorySelectAll	lees alle rijen in uit de tabel maar alleen de kolommen die we in de lijst willen laten zien (waarvoor YES in de List kolom staat)
CategorySelectByUserName	lees 1 rij in uit de tabel op basis van de gebruikersnaam maar alleen de kolommen die we in de lijst willen laten zien (waarvoor YES in de List kolom staat)
CategorySelectByEmail	ees 1 rij in uit de tabel op basis van het emailadres maar alleen de kolommen die we in de lijst willen laten zien (waarvoor YES in de List kolom staat)

Oplossing

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML Insert Stored Procedure for User 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserInsert;
DELIMITER //
CREATE PROCEDURE `UserInsert`
(
	OUT pId INT ,
	IN pUserName NVARCHAR (50) ,
	IN pEmail VARCHAR (80) ,
	IN pPassword CHAR (128) ,
	IN pLastActivity DATETIME ,
	IN pFirstLogin DATETIME ,
	IN pAuthenticated BIT ,
	IN pLockedOut BIT 
)
BEGIN
INSERT INTO `User`
	(
		`User`.`UserName`,
		`User`.`Email`,
		`User`.`Password`,
		`User`.`LastActivity`,
		`User`.`FirstLogin`,
		`User`.`Authenticated`,
		`User`.`LockedOut`
	)
	VALUES
	(
		pUserName,
		pEmail,
		pPassword,
		pLastActivity,
		pFirstLogin,
		pAuthenticated,
		pLockedOut
	);
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML Update Stored Procedure for User
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserUpdate;
DELIMITER //
CREATE PROCEDURE `UserUpdate`
(
	pId INT ,
	pUserName NVARCHAR (50) ,
	pEmail VARCHAR (80) ,
	pPassword CHAR (128) ,
	pLastActivity DATETIME ,
	pFirstLogin DATETIME ,
	pAuthenticated BIT ,
	pLockedOut BIT 
)
BEGIN
UPDATE `User`
	SET
		`UserName` = pUserName,
		`Email` = pEmail,
		`Password` = pPassword,
		`LastActivity` = pLastActivity,
		`FirstLogin` = pFirstLogin,
		`Authenticated` = pAuthenticated,
		`LockedOut` = pLockedOut
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML Delete Stored Procedure for User 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserDelete;
DELIMITER //
CREATE PROCEDURE `UserDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `User`
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML SelectOne Stored Procedure for User 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserSelectOne;
DELIMITER //
CREATE PROCEDURE `UserSelectOne`
(
	 pId INT 
)
BEGIN
SELECT * FROM `User`
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML SelectAll Stored Procedure for table User 
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserSelectAll;
DELIMITER //
CREATE PROCEDURE `UserSelectAll`
(
)
BEGIN
SELECT `User`.`Id`,
	`User`.`UserName`,
	`User`.`Email`,
	`User`.`Password`,
	`User`.`Authenticated`,
	`User`.`LockedOut`
	FROM `User`
	ORDER BY `UserName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML SelectById Stored Procedure for table User
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserSelectById;
DELIMITER //
CREATE PROCEDURE `UserSelectById`
(
	 pId INT 
)
BEGIN
SELECT `User`.`Id`,
	`User`.`UserName`,
	`User`.`Email`,
	`User`.`Password`,
	`User`.`Authenticated`,
	`User`.`LockedOut`

	FROM `User`
	WHERE `User`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML SelectByUserName Stored Procedure for table User
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserSelectByUserName;
DELIMITER //
CREATE PROCEDURE `UserSelectByUserName`
(
	 pUserName NVARCHAR (50) 
)
BEGIN
SELECT `User`.`Id`,
	`User`.`UserName`,
	`User`.`Email`,
	`User`.`Password`,
	`User`.`Authenticated`,
	`User`.`LockedOut`

	FROM `User`
	WHERE `User`.`UserName` = pUserName
	ORDER BY `User`.`UserName`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Monday 1st of February 2016 11:37:52 AM
-- DML SelectByEmail Stored Procedure for table User
-- 
USE Webshop;
DROP PROCEDURE IF EXISTS UserSelectByEmail;
DELIMITER //
CREATE PROCEDURE `UserSelectByEmail`
(
	 pEmail VARCHAR (80) 
)
BEGIN
SELECT `User`.`Id`,
	`User`.`UserName`,
	`User`.`Email`,
	`User`.`Password`,
	`User`.`Authenticated`,
	`User`.`LockedOut`

	FROM `User`
	WHERE `User`.`Email` = pEmail
	ORDER BY `User`.`Email`;
END //
DELIMITER ;

2016-02-01 11:40:04