Skip to content

ncCode / freesn

Go to a project
Switch branch/tag
MySql_Function_insert.sql 16.8 KB
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 
/**
 *	@filesource MySQL_Function_insert.sql
 *
 * @category freeSN
 * @copyright Copyright by mensch.coop e.G. 2009
 * @mailto	dev [at] mensch.coop
 * @version 0.4.200901
 * @link http://mensch.coop
 */

-- --------------------------------------
select 'CREATE FUNCTION insertSubGroupProfile(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertSubGroupProfile;
-- DROP FUNCTION IF EXISTS createGroupProfile;
DELIMITER '$';
CREATE FUNCTION insertSubGroupProfile(param_NickName varchar(20), param_EMail varchar(256), param_Fingerprint varchar(42), param_Jabber varchar(256), param_Url varchar(2048), param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_Name varchar(20), param_private boolean, param_ProfileID int, param_Avatar varchar(2048), param_Signature text(2048))
	RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewProfileID INT;
		DECLARE var_NewMTextID INT;
		DECLARE var_NewAuthReadListID INT;
		DECLARE var_NewAuthWriteListID INT;

		SET var_NewProfileID=(SELECT insertProfile( 'Profile',param_NickName, param_EMail, param_Fingerprint, param_Jabber, param_Url, param_Subject, param_Text, param_LanguageCode, param_Name, param_private, param_Avatar, param_Signature));
		SET var_NewAuthReadListID=(select AuthReadListID from losp_BasisElement where ID=var_NewProfileID);
		SET var_NewAuthWriteListID=(select AuthWriteListID from losp_BasisElement where ID=var_NewProfileID);

		IF (param_private) THEN
			INSERT INTO losp_BasisElement_readauthorisation ( ListID, ProfileID) VALUES (var_NewAuthReadListID, param_ProfileID);
		END IF;
		INSERT INTO losp_BasisElement_writeauthorisation ( ListID, ProfileID) VALUES (var_NewAuthWriteListID, param_ProfileID);
		RETURN var_NewProfileID;
 	END;
$
DELIMITER  ;



-- --------------------------------------
select 'CREATE FUNCTION insertGroupProfileBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertGroupProfileBE;
-- DROP FUNCTION IF EXISTS createGroupProfile;
DELIMITER '$';
CREATE FUNCTION insertGroupProfileBE(param_NickName varchar(20), param_EMail varchar(256), param_Fingerprint varchar(42), param_Jabber varchar(256), param_Url varchar(2048), param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_Name varchar(20), param_private boolean, param_ProfileID int, param_Avatar varchar(2048), param_Signature text(2048))
	RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewProfileID INT;
		DECLARE var_NewMTextID INT;
		DECLARE var_NewAuthReadListID INT;
		DECLARE var_NewAuthWriteListID INT;

		SET var_NewProfileID=(SELECT insertProfile( 'GroupProfileBE',param_NickName, param_EMail, param_Fingerprint, param_Jabber, param_Url, param_Subject, param_Text, param_LanguageCode, param_Name, param_private, param_Avatar, param_Signature));
		SET var_NewAuthReadListID=(select AuthReadListID from losp_BasisElement where ID=var_NewProfileID);
		SET var_NewAuthWriteListID=(select AuthWriteListID from losp_BasisElement where ID=var_NewProfileID);

		IF (param_private) THEN
			INSERT INTO losp_BasisElement_readauthorisation ( ListID, ProfileID) VALUES (var_NewAuthReadListID, param_ProfileID);
		END IF;
		INSERT INTO losp_BasisElement_writeauthorisation ( ListID, ProfileID) VALUES (var_NewAuthWriteListID, param_ProfileID);
		RETURN var_NewProfileID;
 	END;
$
DELIMITER  ;

--	--------------------------------------------------------

select 'CREATE FUNCTION insertAccount(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertAccount;
DELIMITER '$';
	CREATE FUNCTION insertAccount(Password varchar(50), ProfileID int(11) UNSIGNED, FirstName varchar(50), SurName varchar(100), Street varchar(100), Streetnumber int(4), City varchar(50), ZipCode int(5), EMail varchar(256))
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewID INT;
		DECLARE var_ClassID INT;
		set var_NewID=(select max(ID) as ID from losp_be_account)+1;
		INSERT INTO losp_be_account (ID,Password,Firstname,SurName,ProfileID,EMail,Street,Streetnumber,City,ZipCode)
		VALUES (var_NewID, Password, FirstName, SurName, ProfileID	, EMail, Street, Streetnumber, City, ZipCode);
		return var_NewID;
 	END;
$
DELIMITER  ;

--	--------------------------------------------------------

select 'CREATE FUNCTION insertMultimediaText(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertMultimediaText;
DELIMITER '$';
	CREATE FUNCTION insertMultimediaText(param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int, param_ClassName  varchar(512))
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewID INT;
		DECLARE var_ClassID INT;
		set var_NewID=(select max(ID) as ID from losp_BasisElement)+1;
		set var_ClassID=(select ClassID from losp_class where ClassName=param_ClassName);
		INSERT INTO losp_BasisElement ( ID , Author_ProfileID, Subject, Text, LanguageCode, AuthReadListID, AuthWriteListID, PCAuthReadListID, PCAuthWriteListID, ClassID)
		VALUES (var_NewID, param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, var_ClassID);
		return var_NewID;
 	END;
$
DELIMITER  ;



-- --------------------------------------------------------

select 'CREATE FUNCTION insertProfile(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertProfile;
DELIMITER '$';
CREATE FUNCTION insertProfile(param_KindOfProfile varchar(512), param_NickName varchar(20), param_EMail varchar(256), param_Fingerprint varchar(42), param_Jabber varchar(256), param_Url varchar(2048), param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_Name varchar(20), param_private boolean, param_Avatar varchar(2048), param_Signature text(2048))
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewMTextID INT;
		DECLARE var_NewAuthorID INT;
		DECLARE var_NewAuthReadListID INT;
		DECLARE var_NewAuthWriteListID INT;
		DECLARE var_PCAuthListID INT;
		set var_NewAuthReadListID =0;
		set var_NewAuthWriteListID =0;
		set var_NewAuthorID=0;
		set var_PCAuthListID=0;
		set var_NewMTextID=(select insertMultimediaText(var_NewAuthorID, param_Subject, param_Text, param_LanguageCode, var_NewAuthReadListID, var_NewAuthWriteListID,var_PCAuthListID,var_PCAuthListID, param_KindOfProfile));

		INSERT INTO losp_BE_profile (MTextID, NickName, Url, EMail, Fingerprint, Jabber, OnlineState, Avatar, Signature)
			VALUES (var_NewMTextID, param_NickName, param_Url, param_EMail, param_Fingerprint, param_Jabber, '1', param_Avatar, param_Signature);

		set var_NewAuthWriteListID=(select max(ListID) as ID from losp_BasisElement_writeauthorisation)+1;
		INSERT INTO losp_BasisElement_writeauthorisation ( ListID, ProfileID) VALUES (var_NewAuthWriteListID, var_NewMTextID);

		IF (param_private) THEN
			set var_NewAuthReadListID=(select max(ListID) as ID from losp_BasisElement_readauthorisation)+1;
			INSERT INTO losp_BasisElement_readauthorisation ( ListID, ProfileID) VALUES (var_NewAuthReadListID, var_NewMTextID);
		END IF;

		update losp_BasisElement set Author_ProfileID=var_NewMTextID, AuthReadListID=var_NewAuthReadListID, AuthWriteListID=var_NewAuthWriteListID where ID=var_NewMTextID;

		return var_NewMTextID;
	END;
$
DELIMITER  ;

-- --------------------


select 'CREATE FUNCTION insertDateBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertDateBE;
DELIMITER '$';
	CREATE FUNCTION insertDateBE(param_State varchar(128), param_Start int, param_End int, param_Cycle int(2), param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewID INT;
		DECLARE var_StateID INT;
		set var_StateID=(select StateID from losp_BasisClipboard_state where State=param_State);
		set var_NewID=(select  insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'DateBE') );
		INSERT INTO losp_BE_date ( MTextID , Start, End, Cycle)
			VALUES (var_NewID, param_Start, param_End, param_Cycle);
		return var_NewID;
 	END;
$
DELIMITER  ;

-- -----------------

select 'CREATE FUNCTION insertBasisElementDesign(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertBasisElementDesign;
DELIMITER '$';
	CREATE FUNCTION insertBasisElementDesign(param_BEClassName varchar(64), param_Author_ProfileID int, param_CSS text)
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewID INT;
		set var_NewID=(select max(ID) as ID from losp_BasisElementDesign)+1;
		INSERT INTO losp_BasisElementDesign 		(ID, BEClassName, Author_ProfileID, CSS ) 	VALUES (var_NewID, param_BEClassName, param_Author_ProfileID, param_CSS);
		return var_NewID;
 	END;
$
DELIMITER  ;

--	--------------------------------------------------------
select 'CREATE FUNCTION insertTopicBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertTopicBE;
DELIMITER '$';
	CREATE FUNCTION insertTopicBE(param_Name varchar(20), param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewMTextID INT;
		set var_NewMTextID=(select insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'TopicBE'));
		INSERT INTO losp_BE_topic (Name, MTextID) VALUES (param_Name, var_NewMTextID);
		return var_NewMTextID;
 	END;
$
DELIMITER  ;

--	--------------------------------------------------------
select 'CREATE FUNCTION insertThreadMessageBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertThreadMessageBE;
DELIMITER '$';
	CREATE FUNCTION insertThreadMessageBE(param_LastInsertDate int, param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewMTextID INT;
		set var_NewMTextID=(select insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'ThreadMessageBE'));
		INSERT INTO losp_BE_threadmessage (LastInsertDate, ThreadBEQuantity, MTextID) VALUES (param_LastInsertDate, 0, var_NewMTextID);
		return var_NewMTextID;
 	END;
$
DELIMITER  ;
--	--------------------------------------------------------
select 'CREATE FUNCTION insertNewsfeedBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertNewsfeedBE;
DELIMITER '$';
	CREATE FUNCTION insertNewsfeedBE(param_url varchar(2048), param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewMTextID INT;
		set var_NewMTextID=(select insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'NewsfeedBE'));
		INSERT INTO losp_BE_newsfeed (Url, MTextID) VALUES (param_url, var_NewMTextID);
		return var_NewMTextID;
 	END;
$
DELIMITER  ;


select 'CREATE FUNCTION insertTag(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertTag;
DELIMITER '$';
	CREATE FUNCTION insertTag(param_Tagname varchar(64))
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewID INT;
		set var_NewID=(select max(TagID) as ID from losp_BasisElement_tagnamelist)+1;
		INSERT INTO losp_BasisElement_tagnamelist (TagID, TagName) VALUES (var_NewID, param_Tagname);
		return var_NewID;
 	END;
$
DELIMITER  ;

select 'CREATE FUNCTION insertFirstTagToBasisElement(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertFirstTagToBasisElement;
DELIMITER '$';
	CREATE FUNCTION insertFirstTagToBasisElement(param_RequestProfileID int, param_MTextID int, param_TagID int )
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewListID INT;
		call checkBEAuth(param_MTextID,param_RequestProfileID,@r,@var_Profile_w);
		IF (@var_Profile_w) THEN
			set var_NewListID=(select TagListID from losp_BasisElement where ID=param_MTextID);

			IF var_NewListID>=0 THEN
				DELETE FROM losp_BasisElement_taglist WHERE TagListID=var_NewListID;
			ELSE
				set var_NewListID=(select max(TagListID) as ID from losp_BasisElement_taglist)+1;
			END IF;

			INSERT INTO losp_BasisElement_taglist (TagListID, TagID) VALUES (var_NewListID, param_TagID);

			UPDATE losp_BasisElement SET  TagListID=var_NewListID where ID=param_MTextID;

			return var_NewListID;
		END IF;
		return 0;
 	END;
$
DELIMITER  ;


select 'CREATE FUNCTION insertCategory(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertCategory;
DELIMITER '$';
	CREATE FUNCTION insertCategory(param_Categoryname varchar(64))
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewID INT;
		set var_NewID=(select max(BECategoryID) as ID from losp_BasisElement_categorynamelist)+1;
		INSERT INTO losp_BasisElement_categorynamelist (BECategoryID, BECategoryName) VALUES (var_NewID, param_Categoryname);
		return var_NewID;
 	END;
$
DELIMITER  ;

select 'CREATE FUNCTION insertFirstCategoryToBasisElement(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertFirstCategoryToBasisElement;
DELIMITER '$';
	CREATE FUNCTION insertFirstCategoryToBasisElement(param_RequestProfileID int, param_MTextID int, param_CatID int )
	RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewListID INT;

		call checkBEAuth(param_MTextID,param_RequestProfileID,@r,@var_Profile_w);
		IF (@var_Profile_w) THEN
			set var_NewListID=(select BECategoryListID from losp_BasisElement where ID=param_MTextID);

			IF var_NewListID>=0 THEN
				DELETE FROM losp_BasisElement_categorylist WHERE BECategoryListID=var_NewListID;
			ELSE
				set var_NewListID=(select max(BECategoryListID) as ID from losp_BasisElement_categorylist)+1;
			END IF;

			INSERT INTO losp_BasisElement_categorylist (BECategoryListID, BECategoryID) VALUES (var_NewListID, param_CatID);

			UPDATE losp_BasisElement SET BECategoryListID=var_NewListID where ID=param_MTextID;

			return var_NewListID;
		END IF;
		return 0;
 	END;
$
DELIMITER  ;


select 'CREATE FUNCTION insertFileBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertFileBE;
DELIMITER '$';
	CREATE FUNCTION insertFileBE(param_link varchar(512), param_mime varchar(20), param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewMTextID INT;
		set var_NewMTextID=(select insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'FileBE'));
		INSERT INTO losp_BE_file (Link, Mime, MTextID) VALUES (param_link, param_mime, var_NewMTextID);
		return var_NewMTextID;
 	END;
$
DELIMITER  ;



select 'CREATE FUNCTION insertUrlBE(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertUrlBE;
DELIMITER '$';
	CREATE FUNCTION insertUrlBE(param_Url varchar(2048), param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
		RETURNS INT
	DETERMINISTIC
 	BEGIN
		DECLARE var_NewMTextID INT;
		set var_NewMTextID=(select insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'UrlBE'));

		INSERT INTO losp_BE_url (Url, MTextID) VALUES (param_Url, var_NewMTextID);

		return var_NewMTextID;
 	END;
$
DELIMITER  ;



--	--------------------------------------------------------
select 'CREATE FUNCTION insertTreepart(...) RETURNS INT';
DROP FUNCTION IF EXISTS insertTreepart;
DELIMITER '$';
CREATE FUNCTION insertTreepart(param_Name varchar(20), param_Author_ProfileID int, param_Subject varchar(512), param_Text text, param_LanguageCode varchar(3), param_BECategoryChoiceBEID int(11), param_BECategoryListID int(11), param_AuthReadListID int, param_AuthWriteListID int, param_PCAuthReadListID int, param_PCAuthWriteListID int)
		RETURNS INT
	DETERMINISTIC
	BEGIN
		DECLARE var_NewMTextID INT;
		set var_NewMTextID=(select insertMultimediaText(param_Author_ProfileID, param_Subject, param_Text, param_LanguageCode, param_AuthReadListID, param_AuthWriteListID, param_PCAuthReadListID, param_PCAuthWriteListID, 'Treepart'));

		INSERT INTO losp_BE_treepart (Name, MTextID) VALUES (param_Name, var_NewMTextID);

		update losp_BasisElement
			set
				BECategoryListID=param_BECategoryListID,
				BECategoryChoiceBEID=param_BECategoryChoiceBEID
			where ID=var_NewMTextID;

		return var_NewMTextID;
	END;
$
DELIMITER  ;