q87qq8xd

1-- --------------------------------------------------------
2-- Hôte :                        127.0.0.1
3-- Version du serveur:           10.1.19-MariaDB - mariadb.org binary distribution
4-- SE du serveur:                Win64
5-- HeidiSQL Version:             9.3.0.4984
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES utf8mb4 */;
10/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
11/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
12
13-- Export de la structure de la base pour lif_1
14CREATE DATABASE IF NOT EXISTS `lif_1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
15USE `lif_1`;
16
17
18-- Export de la structure de table lif_1. account
19CREATE TABLE IF NOT EXISTS `account` (
20  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
21  `IsActive` tinyint(1) unsigned NOT NULL DEFAULT '1',
22  `IsGM` tinyint(1) unsigned NOT NULL DEFAULT '0',
23  `SteamID` bigint(20) unsigned NOT NULL,
24  PRIMARY KEY (`ID`),
25  UNIQUE KEY `UNQ_SteamID` (`SteamID`)
26) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
27
28-- L'exportation de données n'était pas sélectionnée.
29
30
31-- Export de la structure de table lif_1. blueprints
32CREATE TABLE IF NOT EXISTS `blueprints` (
33  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
34  `RecipeID` int(10) unsigned NOT NULL,
35  PRIMARY KEY (`ID`),
36  KEY `FK_blueprints_recipe` (`RecipeID`),
37  CONSTRAINT `FK_blueprints_recipe` FOREIGN KEY (`RecipeID`) REFERENCES `recipe` (`ID`)
38) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
39
40-- L'exportation de données n'était pas sélectionnée.
41
42
43-- Export de la structure de table lif_1. blueprint_requirements
44CREATE TABLE IF NOT EXISTS `blueprint_requirements` (
45  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
46  `BlueprintID` int(10) unsigned NOT NULL,
47  `RecipeRequirementID` int(10) unsigned NOT NULL,
48  `RegionID` int(10) unsigned NOT NULL,
49  PRIMARY KEY (`ID`),
50  KEY `FK_blueprint_requirements_blueprints` (`BlueprintID`),
51  KEY `FK_blueprint_requirements_recipe_requirement` (`RecipeRequirementID`),
52  KEY `FK_blueprint_requirements_regions` (`RegionID`),
53  CONSTRAINT `FK_blueprint_requirements_blueprints` FOREIGN KEY (`BlueprintID`) REFERENCES `blueprints` (`ID`),
54  CONSTRAINT `FK_blueprint_requirements_recipe_requirement` FOREIGN KEY (`RecipeRequirementID`) REFERENCES `recipe_requirement` (`ID`),
55  CONSTRAINT `FK_blueprint_requirements_regions` FOREIGN KEY (`RegionID`) REFERENCES `regions` (`ID`)
56) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
57
58-- L'exportation de données n'était pas sélectionnée.
59
60
61-- Export de la structure de table lif_1. building_items
62CREATE TABLE IF NOT EXISTS `building_items` (
63  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
64  `ContainerID` int(10) unsigned NOT NULL,
65  `ObjectTypeID` int(10) unsigned NOT NULL,
66  `Quality` tinyint(6) unsigned NOT NULL DEFAULT '0' COMMENT '0-100',
67  `Quantity` int(6) unsigned NOT NULL DEFAULT '0',
68  `Durability` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point',
69  PRIMARY KEY (`ID`),
70  KEY `FK_BuildingItemsContainerID` (`ContainerID`),
71  KEY `FK_BuildingItemTypeID` (`ObjectTypeID`) USING BTREE,
72  CONSTRAINT `FK_BuildingItemTypeID` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`),
73  CONSTRAINT `FK_BuildingItemsContainerID` FOREIGN KEY (`ContainerID`) REFERENCES `containers` (`ID`)
74) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Used for holding building materials in a construction site.';
75
76-- L'exportation de données n'était pas sélectionnée.
77
78
79-- Export de la structure de table lif_1. character
80CREATE TABLE IF NOT EXISTS `character` (
81  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
82  `IsActive` tinyint(3) unsigned NOT NULL DEFAULT '1',
83  `Name` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
84  `LastName` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
85  `GeoID` int(10) unsigned DEFAULT '0',
86  `AccountID` int(10) unsigned NOT NULL,
87  `GeoAlt` smallint(5) unsigned DEFAULT '0',
88  `OffsetMmX` smallint(6) NOT NULL DEFAULT '0' COMMENT 'in millimeters from the center of GeoID',
89  `OffsetMmY` smallint(6) NOT NULL DEFAULT '0' COMMENT 'in millimeters from the center of GeoID',
90  `OffsetMmZ` tinyint(3) NOT NULL DEFAULT '0' COMMENT 'in millimeters from the GeoAlt',
91  `RaceID` tinyint(3) unsigned NOT NULL,
92  `Alignment` int(10) NOT NULL COMMENT '6 digits after point',
93  `CriminalSecondsLeft` int(10) unsigned NOT NULL DEFAULT '0',
94  `Strength` int(10) unsigned NOT NULL COMMENT '6 digits after point',
95  `StrengthLock` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'up 1 lock 0 down -1',
96  `Agility` int(10) unsigned NOT NULL COMMENT '6 digits after point',
97  `AgilityLock` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'up 1 lock 0 down -1',
98  `Intellect` int(10) unsigned NOT NULL COMMENT '6 digits after point',
99  `IntellectLock` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'up 1 lock 0 down -1',
100  `Willpower` int(10) unsigned NOT NULL COMMENT '6 digits after point',
101  `WillpowerLock` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'up 1 lock 0 down -1',
102  `Constitution` int(10) unsigned NOT NULL COMMENT '6 digits after point',
103  `ConstitutionLock` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'up 1 lock 0 down -1',
104  `RootContainerID` int(10) unsigned NOT NULL,
105  `EquipmentContainerID` int(10) unsigned NOT NULL,
106  `HardHP` int(10) NOT NULL DEFAULT '1000000' COMMENT '6 digits after point',
107  `HardStam` int(10) NOT NULL DEFAULT '1000000' COMMENT '6 digits after point',
108  `SoftHP` int(10) NOT NULL DEFAULT '1000000' COMMENT '6 digits after point',
109  `SoftStam` int(10) NOT NULL DEFAULT '1000000' COMMENT '6 digits after point',
110  `Luck` int(10) unsigned NOT NULL COMMENT '6 digits after point',
111  `HungerRate` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point',
112  `AlchemyHash` binary(90) NOT NULL COMMENT 'Hash of effects sets IDs',
113  `VisibilityHash` binary(90) NOT NULL COMMENT 'Hash of effects visibility for player',
114  `appearance` blob,
115  `GuildID` int(10) unsigned DEFAULT NULL,
116  `GuildRoleID` tinyint(3) unsigned DEFAULT NULL,
117  `TitleMessageID` int(10) unsigned DEFAULT NULL,
118  `BindedObjectID` int(10) unsigned DEFAULT NULL,
119  `RallyObjectID` int(10) unsigned DEFAULT NULL,
120  `LastTimeUsedPraiseYourGodAbility` int(10) unsigned NOT NULL DEFAULT '0',
121  `LastTimeUsedTransmuteIntoGold` int(10) unsigned NOT NULL DEFAULT '0',
122  `CreateTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
123  `DeleteTimestamp` timestamp NULL DEFAULT NULL,
124  PRIMARY KEY (`ID`),
125  UNIQUE KEY `UNQ_Name` (`Name`),
126  UNIQUE KEY `UNQ_FK_RootContainerID` (`RootContainerID`),
127  UNIQUE KEY `UNQ_FK_character_EquipmentContainerID` (`EquipmentContainerID`),
128  KEY `FK_AccountID` (`AccountID`),
129  KEY `FK_character_unmovable_objects` (`BindedObjectID`),
130  KEY `IDX_IsActive` (`IsActive`),
131  KEY `FK_character_race` (`RaceID`),
132  KEY `FK_GuildID` (`GuildID`),
133  KEY `FK_character_guild_roles` (`GuildRoleID`),
134  KEY `FK_character_unmovable_objects2` (`RallyObjectID`),
135  CONSTRAINT `FK_AccountID` FOREIGN KEY (`AccountID`) REFERENCES `account` (`ID`),
136  CONSTRAINT `FK_GuildID` FOREIGN KEY (`GuildID`) REFERENCES `guilds` (`ID`),
137  CONSTRAINT `FK_RootContainerID` FOREIGN KEY (`RootContainerID`) REFERENCES `containers` (`ID`),
138  CONSTRAINT `FK_character_EquipmentContainerID` FOREIGN KEY (`EquipmentContainerID`) REFERENCES `containers` (`ID`),
139  CONSTRAINT `FK_character_guild_roles` FOREIGN KEY (`GuildRoleID`) REFERENCES `guild_roles` (`ID`),
140  CONSTRAINT `FK_character_race` FOREIGN KEY (`RaceID`) REFERENCES `race` (`ID`),
141  CONSTRAINT `FK_character_unmovable_objects` FOREIGN KEY (`BindedObjectID`) REFERENCES `unmovable_objects` (`ID`),
142  CONSTRAINT `FK_character_unmovable_objects2` FOREIGN KEY (`RallyObjectID`) REFERENCES `unmovable_objects` (`ID`)
143) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
144
145-- L'exportation de données n'était pas sélectionnée.
146
147
148-- Export de la structure de table lif_1. character_blueprints
149CREATE TABLE IF NOT EXISTS `character_blueprints` (
150  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
151  `CharID` int(10) unsigned NOT NULL,
152  `BlueprintID` int(10) unsigned NOT NULL,
153  PRIMARY KEY (`ID`),
154  UNIQUE KEY `FK_character_blueprints_blueprints` (`BlueprintID`),
155  KEY `FK_character_blueprints_character` (`CharID`),
156  CONSTRAINT `FK_character_blueprints_blueprints` FOREIGN KEY (`BlueprintID`) REFERENCES `blueprints` (`ID`),
157  CONSTRAINT `FK_character_blueprints_character` FOREIGN KEY (`CharID`) REFERENCES `character` (`ID`)
158) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
159
160-- L'exportation de données n'était pas sélectionnée.
161
162
163-- Export de la structure de table lif_1. character_effects
164CREATE TABLE IF NOT EXISTS `character_effects` (
165  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
166  `PlayerEffectID` tinyint(3) unsigned NOT NULL,
167  `CharacterID` int(10) unsigned NOT NULL,
168  `Magnitude` int(10) unsigned NOT NULL COMMENT '6 digits after point',
169  `DurationLeft` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'In seconds',
170  PRIMARY KEY (`ID`),
171  KEY `FK_character_effects_character` (`CharacterID`),
172  CONSTRAINT `FK_character_effects_character` FOREIGN KEY (`CharacterID`) REFERENCES `character` (`ID`)
173) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Stores player''s effects';
174
175-- L'exportation de données n'était pas sélectionnée.
176
177
178-- Export de la structure de table lif_1. character_titles
179CREATE TABLE IF NOT EXISTS `character_titles` (
180  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
181  `CharacterID` int(10) unsigned NOT NULL,
182  `TitleID` int(10) unsigned NOT NULL,
183  PRIMARY KEY (`ID`),
184  KEY `FK_character_titles_character` (`CharacterID`),
185  KEY `FK_character_titles_titles` (`TitleID`),
186  CONSTRAINT `FK_character_titles_character` FOREIGN KEY (`CharacterID`) REFERENCES `character` (`ID`),
187  CONSTRAINT `FK_character_titles_titles` FOREIGN KEY (`TitleID`) REFERENCES `titles` (`ID`)
188) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
189
190-- L'exportation de données n'était pas sélectionnée.
191
192
193-- Export de la structure de table lif_1. character_wounds
194CREATE TABLE IF NOT EXISTS `character_wounds` (
195  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
196  `CharacterID` int(10) unsigned NOT NULL,
197  `BodyPart` tinyint(3) unsigned NOT NULL COMMENT 'Head = 0, LHand = 1, RHand = 2, Torso=3,	LLeg=4, RLeg=5 enum PartOfBodyType in cpp',
198  `WoundType` tinyint(3) unsigned NOT NULL COMMENT 'Wound = 0, BigWound = 1 FractureHead = 3 enum WoundsType in cpp',
199  `DurationLeft` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'In seconds',
200  PRIMARY KEY (`ID`),
201  UNIQUE KEY `UNQ_character_wounds_CharacterID_BodyPart_WoundType` (`CharacterID`,`BodyPart`,`WoundType`),
202  CONSTRAINT `FK_character_wound_character` FOREIGN KEY (`CharacterID`) REFERENCES `character` (`ID`)
203) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
204
205-- L'exportation de données n'était pas sélectionnée.
206
207
208-- Export de la structure de table lif_1. chars_deathlog
209CREATE TABLE IF NOT EXISTS `chars_deathlog` (
210  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
211  `Time` int(10) unsigned NOT NULL,
212  `CharID` int(10) unsigned NOT NULL,
213  `KillerID` int(10) unsigned NOT NULL,
214  `IsKnockout` tinyint(3) unsigned DEFAULT '0',
215  PRIMARY KEY (`ID`)
216) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
217
218-- L'exportation de données n'était pas sélectionnée.
219
220
221-- Export de la structure de table lif_1. claims
222CREATE TABLE IF NOT EXISTS `claims` (
223  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
224  `GuildLandID` int(10) unsigned DEFAULT NULL,
225  `PersonalLandID` int(10) unsigned DEFAULT NULL,
226  `SupportPoints` int(11) NOT NULL,
227  PRIMARY KEY (`ID`),
228  UNIQUE KEY `UNQ_GuildLandID` (`GuildLandID`),
229  UNIQUE KEY `UNQ_PersonalLandID` (`PersonalLandID`),
230  CONSTRAINT `FK_claims_guild_lands` FOREIGN KEY (`GuildLandID`) REFERENCES `guild_lands` (`ID`),
231  CONSTRAINT `FK_claims_personal_lands` FOREIGN KEY (`PersonalLandID`) REFERENCES `personal_lands` (`ID`)
232) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
233
234-- L'exportation de données n'était pas sélectionnée.
235
236
237-- Export de la structure de table lif_1. claim_rules
238CREATE TABLE IF NOT EXISTS `claim_rules` (
239  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
240  `ClaimID` int(10) unsigned NOT NULL,
241  `ClaimSubjectID` int(10) unsigned NOT NULL,
242  `CanEnter` tinyint(3) unsigned NOT NULL DEFAULT '0',
243  `CanBuild` tinyint(3) unsigned NOT NULL DEFAULT '0',
244  `CanClaim` tinyint(3) unsigned NOT NULL DEFAULT '0',
245  `CanUse` tinyint(3) unsigned NOT NULL DEFAULT '0',
246  `CanDestroy` tinyint(3) unsigned NOT NULL DEFAULT '0',
247  PRIMARY KEY (`ID`),
248  UNIQUE KEY `UNQ_ClaimID_ClaimSubjectID` (`ClaimID`,`ClaimSubjectID`),
249  KEY `FK_claim_rules_claims` (`ClaimID`),
250  KEY `FK_claim_rules_claim_subjects` (`ClaimSubjectID`),
251  CONSTRAINT `FK_claim_rules_claim_subjects` FOREIGN KEY (`ClaimSubjectID`) REFERENCES `claim_subjects` (`ID`),
252  CONSTRAINT `FK_claim_rules_claims` FOREIGN KEY (`ClaimID`) REFERENCES `claims` (`ID`)
253) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
254
255-- L'exportation de données n'était pas sélectionnée.
256
257
258-- Export de la structure de table lif_1. claim_rules_unmovable
259CREATE TABLE IF NOT EXISTS `claim_rules_unmovable` (
260  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
261  `UnmovableClaimID` int(10) unsigned NOT NULL,
262  `ClaimSubjectID` int(10) unsigned NOT NULL,
263  `CanUse` tinyint(3) unsigned NOT NULL DEFAULT '0',
264  `CanDestroy` tinyint(3) unsigned NOT NULL DEFAULT '0',
265  PRIMARY KEY (`ID`),
266  KEY `FK_claim_rules_unmovable_unmovable_objects_claims` (`UnmovableClaimID`),
267  KEY `FK_claim_rules_unmovable_claim_subjects` (`ClaimSubjectID`),
268  CONSTRAINT `FK_claim_rules_unmovable_claim_subjects` FOREIGN KEY (`ClaimSubjectID`) REFERENCES `claim_subjects` (`ID`),
269  CONSTRAINT `FK_claim_rules_unmovable_unmovable_objects_claims` FOREIGN KEY (`UnmovableClaimID`) REFERENCES `unmovable_objects_claims` (`ID`)
270) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
271
272-- L'exportation de données n'était pas sélectionnée.
273
274
275-- Export de la structure de table lif_1. claim_subjects
276CREATE TABLE IF NOT EXISTS `claim_subjects` (
277  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
278  `CharID` int(10) unsigned DEFAULT NULL,
279  `GuildRoleID` tinyint(3) unsigned DEFAULT NULL,
280  `GuildID` int(10) unsigned DEFAULT NULL,
281  `StandingTypeID` tinyint(3) unsigned DEFAULT NULL,
282  PRIMARY KEY (`ID`),
283  UNIQUE KEY `UNQ_FK_claim_subjects_character` (`CharID`),
284  UNIQUE KEY `UNQ_FK_claim_subjects_guild_roles` (`GuildRoleID`),
285  UNIQUE KEY `UNQ_FK_claim_subjects_guilds` (`GuildID`),
286  UNIQUE KEY `UNQ_FK_claim_subjects_standing_types` (`StandingTypeID`),
287  CONSTRAINT `FK_claim_subjects_character` FOREIGN KEY (`CharID`) REFERENCES `character` (`ID`),
288  CONSTRAINT `FK_claim_subjects_guild_roles` FOREIGN KEY (`GuildRoleID`) REFERENCES `guild_roles` (`ID`),
289  CONSTRAINT `FK_claim_subjects_guilds` FOREIGN KEY (`GuildID`) REFERENCES `guilds` (`ID`),
290  CONSTRAINT `FK_claim_subjects_standing_types` FOREIGN KEY (`StandingTypeID`) REFERENCES `guild_standing_types` (`ID`)
291) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
292
293-- L'exportation de données n'était pas sélectionnée.
294
295
296-- Export de la structure de table lif_1. containers
297CREATE TABLE IF NOT EXISTS `containers` (
298  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
299  `ParentID` int(10) unsigned DEFAULT NULL,
300  `ObjectTypeID` int(10) unsigned NOT NULL,
301  `Quality` tinyint(6) unsigned NOT NULL DEFAULT '0' COMMENT '0-100',
302  `FeatureID` int(10) unsigned DEFAULT NULL,
303  PRIMARY KEY (`ID`),
304  KEY `FK_ParentID` (`ParentID`),
305  KEY `FK_ContainerTypeID` (`ObjectTypeID`) USING BTREE,
306  KEY `FK_ContanersFeaturesID` (`FeatureID`),
307  CONSTRAINT `FK_ContainersObjectTypeID` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`),
308  CONSTRAINT `FK_ContanersFeaturesID` FOREIGN KEY (`FeatureID`) REFERENCES `features` (`ID`),
309  CONSTRAINT `FK_ParentID` FOREIGN KEY (`ParentID`) REFERENCES `containers` (`ID`)
310) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
311
312-- L'exportation de données n'était pas sélectionnée.
313
314
315-- Export de la structure de table lif_1. custom_texts
316CREATE TABLE IF NOT EXISTS `custom_texts` (
317  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
318  `Custom_text` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
319  PRIMARY KEY (`ID`),
320  KEY `BYTEXT` (`Custom_text`)
321) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
322
323-- L'exportation de données n'était pas sélectionnée.
324
325
326-- Export de la structure de table lif_1. deleted_character_info
327CREATE TABLE IF NOT EXISTS `deleted_character_info` (
328  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
329  `ExCharID` int(10) unsigned NOT NULL,
330  `CharName` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
331  `CharLastName` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
332  `DeletedTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
333  PRIMARY KEY (`ID`)
334) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
335
336-- L'exportation de données n'était pas sélectionnée.
337
338
339-- Export de la structure de table lif_1. deleted_guild_info
340CREATE TABLE IF NOT EXISTS `deleted_guild_info` (
341  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
342  `ExGuildID` int(10) unsigned NOT NULL,
343  `GuildName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
344  `GuildTag` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
345  `DeletedTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
346  PRIMARY KEY (`ID`)
347) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
348
349-- L'exportation de données n'était pas sélectionnée.
350
351
352-- Export de la structure de table lif_1. effects
353CREATE TABLE IF NOT EXISTS `effects` (
354  `ID` tinyint(10) unsigned NOT NULL AUTO_INCREMENT,
355  `Effect_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
356  `ResultPreparationID` int(10) unsigned DEFAULT NULL,
357  `ResultPotionID` int(10) unsigned DEFAULT NULL,
358  `PlayerEffectID` tinyint(3) unsigned DEFAULT NULL,
359  PRIMARY KEY (`ID`),
360  KEY `FK_effects_result_preparation_objects_types` (`ResultPreparationID`),
361  KEY `FK_effects_result_potion_objects_types` (`ResultPotionID`),
362  CONSTRAINT `FK_effects_result_potion_objects_types` FOREIGN KEY (`ResultPotionID`) REFERENCES `objects_types` (`ID`),
363  CONSTRAINT `FK_effects_result_preparation_objects_types` FOREIGN KEY (`ResultPreparationID`) REFERENCES `objects_types` (`ID`)
364) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
365
366-- L'exportation de données n'était pas sélectionnée.
367
368
369-- Export de la structure de table lif_1. effects_sets
370CREATE TABLE IF NOT EXISTS `effects_sets` (
371  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
372  `EffectID1` tinyint(10) unsigned NOT NULL,
373  `EffectID2` tinyint(10) unsigned DEFAULT NULL,
374  `EffectID3` tinyint(10) unsigned NOT NULL,
375  PRIMARY KEY (`ID`),
376  KEY `FK_effects_sets_effects_2` (`EffectID2`),
377  KEY `FK_effects_sets_effects_3` (`EffectID3`),
378  KEY `FK_effects_sets_effects_1` (`EffectID1`),
379  CONSTRAINT `FK_effects_sets_effects_1` FOREIGN KEY (`EffectID1`) REFERENCES `effects` (`ID`),
380  CONSTRAINT `FK_effects_sets_effects_2` FOREIGN KEY (`EffectID2`) REFERENCES `effects` (`ID`),
381  CONSTRAINT `FK_effects_sets_effects_3` FOREIGN KEY (`EffectID3`) REFERENCES `effects` (`ID`)
382) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
383
384-- L'exportation de données n'était pas sélectionnée.
385
386
387-- Export de la structure de table lif_1. equipment_slots
388CREATE TABLE IF NOT EXISTS `equipment_slots` (
389  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
390  `CharacterID` int(10) unsigned NOT NULL,
391  `Slot` tinyint(3) unsigned NOT NULL COMMENT 'Valid slots: 1-16',
392  `ItemID` int(10) unsigned DEFAULT NULL,
393  PRIMARY KEY (`ID`),
394  UNIQUE KEY `UNQ_equipment_slots_CharacterID_Slot` (`CharacterID`,`Slot`),
395  KEY `FK_equipment_slots_items` (`ItemID`),
396  CONSTRAINT `FK_equipment_slots_character` FOREIGN KEY (`CharacterID`) REFERENCES `character` (`ID`),
397  CONSTRAINT `FK_equipment_slots_items` FOREIGN KEY (`ItemID`) REFERENCES `items` (`ID`)
398) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
399
400-- L'exportation de données n'était pas sélectionnée.
401
402
403-- Export de la structure de table lif_1. features
404CREATE TABLE IF NOT EXISTS `features` (
405  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
406  `CustomtextID` int(10) unsigned DEFAULT NULL,
407  `CreatedRegionID` int(10) unsigned DEFAULT NULL COMMENT 'Region of item''s origin',
408  `BlueprintID` int(10) unsigned DEFAULT NULL COMMENT 'ID of actual blueprint for blueprint items',
409  `HorseHP` int(11) DEFAULT NULL COMMENT '6 digits after point',
410  `has_effects` tinyint(1) unsigned NOT NULL DEFAULT '0',
411  PRIMARY KEY (`ID`),
412  UNIQUE KEY `FK_features_blueprints` (`BlueprintID`),
413  KEY `FK_FeauturesCustomTextID` (`CustomtextID`),
414  KEY `FK_features_regions` (`CreatedRegionID`),
415  CONSTRAINT `FK_FeauturesCustomTextID` FOREIGN KEY (`CustomtextID`) REFERENCES `custom_texts` (`ID`),
416  CONSTRAINT `FK_features_blueprints` FOREIGN KEY (`BlueprintID`) REFERENCES `blueprints` (`ID`),
417  CONSTRAINT `FK_features_regions` FOREIGN KEY (`CreatedRegionID`) REFERENCES `regions` (`ID`)
418) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
419
420-- L'exportation de données n'était pas sélectionnée.
421
422
423-- Export de la structure de table lif_1. food_eaten
424CREATE TABLE IF NOT EXISTS `food_eaten` (
425  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
426  `CharID` int(10) unsigned NOT NULL,
427  `Time` int(10) unsigned NOT NULL,
428  `FoodTypeID` int(10) unsigned NOT NULL,
429  `Complexity` int(10) unsigned NOT NULL,
430  PRIMARY KEY (`ID`),
431  KEY `FK_food_eaten_objects_types` (`FoodTypeID`),
432  KEY `IDX_food_eaten_time` (`Time`),
433  KEY `FK_food_eaten_character` (`CharID`),
434  CONSTRAINT `FK_food_eaten_character` FOREIGN KEY (`CharID`) REFERENCES `character` (`ID`),
435  CONSTRAINT `FK_food_eaten_objects_types` FOREIGN KEY (`FoodTypeID`) REFERENCES `objects_types` (`ID`)
436) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
437
438-- L'exportation de données n'était pas sélectionnée.
439
440
441-- Export de la structure de table lif_1. forest
442CREATE TABLE IF NOT EXISTS `forest` (
443  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
444  `GeoDataID` int(10) unsigned NOT NULL,
445  `TreeType` tinyint(3) unsigned NOT NULL,
446  `SubcellMask` tinyint(3) unsigned NOT NULL,
447  `AgeTime` int(10) unsigned NOT NULL,
448  `Quality` float NOT NULL,
449  PRIMARY KEY (`ID`),
450  UNIQUE KEY `GeoDataID_unique` (`GeoDataID`)
451) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
452
453-- L'exportation de données n'était pas sélectionnée.
454
455
456-- Export de la structure de table lif_1. forest_patch
457CREATE TABLE IF NOT EXISTS `forest_patch` (
458  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
459  `TerID` int(10) unsigned NOT NULL,
460  `Version` int(10) unsigned NOT NULL,
461  `Action` tinyint(3) unsigned NOT NULL COMMENT '1=Add; 2=UpdateHealth; 3=Remove; 4=Cut; 5=GrowAll',
462  `GeoDataID` int(10) unsigned DEFAULT NULL COMMENT 'NULL for GrowAll operation, which needs only TerID',
463  `SubcellMask` tinyint(3) unsigned DEFAULT NULL COMMENT '4x4 offset within game cell. Stores as "yyxx" bits',
464  `TreeType` tinyint(3) unsigned DEFAULT NULL COMMENT '0=Apple; 1=Birch; 2=Elm; 3=Spruce; 4=Pine; 5=Maple; 6=Mulberry; 7=Oak; 8=Willow;',
465  `TreeHealth` tinyint(3) unsigned DEFAULT NULL COMMENT '0=Ill; 1=Normal; 2=Great; 3=Stump',
466  `AddTime` int(10) unsigned DEFAULT NULL COMMENT 'Used in Add and GrowAll operations',
467  PRIMARY KEY (`ID`),
468  KEY `FK_forest_patch_terrain_blocks` (`TerID`),
469  CONSTRAINT `FK_forest_patch_terrain_blocks` FOREIGN KEY (`TerID`) REFERENCES `terrain_blocks` (`ID`)
470) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
471
472-- L'exportation de données n'était pas sélectionnée.
473
474
475-- Export de la structure de fonction lif_1. f_addForestPatch
476DELIMITER //
477CREATE DEFINER=`root`@`localhost` FUNCTION `f_addForestPatch`(
478	`in_TerID` INT UNSIGNED,
479	`in_Action` TINYINT UNSIGNED,
480	`in_GeoDataID` INT UNSIGNED,
481	`in_SubcellMask` TINYINT UNSIGNED,
482	`in_TreeType` TINYINT UNSIGNED,
483	`in_TreeHealth` TINYINT UNSIGNED,
484	`in_AddTime` INT UNSIGNED
485) RETURNS int(10) unsigned
486    MODIFIES SQL DATA
487BEGIN
488
489	declare versionID, dummyTerID INT UNSIGNED default NULL;
490
491	/* get current objects version for this terrain */
492	select ForestVersion, ID from terrain_blocks
493		where ID = in_TerID limit 1
494		into versionID, dummyTerID
495		for update;
496
497	/* check terrain */
498	if(dummyTerID is null) then
499		return NULL;
500	end if;
501
502	/* increment version */
503	set versionID = (ifnull(versionID, 0) + 1);
504
505	/* make changes */
506	insert into forest_patch
507		(     `TerID`,  `Version`, `Action`,  `GeoDataID`,  `SubcellMask`,   `TreeType`, `TreeHealth`,  `AddTime`)
508		values(in_TerID, versionID, in_Action, in_GeoDataID, in_SubcellMask, in_TreeType, in_TreeHealth, in_AddTime);
509
510	update terrain_blocks
511		set ForestVersion = versionID
512		where ID = in_TerID;
513
514	return versionID;
515
516END//
517DELIMITER ;
518
519
520-- Export de la structure de fonction lif_1. f_addGeoPatch
521DELIMITER //
522CREATE DEFINER=`root`@`localhost` FUNCTION `f_addGeoPatch`(
523	`in_VersionID` INT UNSIGNED, -- 0 for first change
524	`in_ChangeIdx` INT UNSIGNED, -- starts from 1
525	`in_IsServerOnly` TINYINT UNSIGNED,
526	`in_TerID` INT UNSIGNED,
527	`in_Action` TINYINT UNSIGNED,
528	`in_GeoDataID` INT UNSIGNED,
529	`in_Altitude` SMALLINT UNSIGNED,
530	`in_Substance` TINYINT UNSIGNED,
531	`in_LevelFlags` TINYINT UNSIGNED,
532	`in_Quantity` SMALLINT UNSIGNED,
533	`in_Quality` TINYINT UNSIGNED
534) RETURNS int(10) unsigned
535    MODIFIES SQL DATA
536BEGIN
537
538	declare versionID, dummyTerID INT UNSIGNED default NULL;
539
540	if(in_ChangeIdx = 1) then
541		/* get current objects version for this terrain (only for first change) */
542		select GeoVersion, ID from terrain_blocks
543			where ID = in_TerID limit 1
544			into versionID, dummyTerID
545			for update;
546
547		/* check terrain */
548		if(dummyTerID is null) then
549			return NULL;
550		end if;
551
552		/* increment version if this is initial change */
553		set versionID = (ifnull(versionID, 0) + 1);
554
555		/* update version for first change */
556		update terrain_blocks
557			set GeoVersion = versionID
558			where ID = in_TerID;
559	else
560		set versionID = in_VersionID;
561	end if;
562
563	/* insert geo data */
564	insert into geo_patch
565		(     `TerID`,  `Version`, `ChangeIndex`, `IsServerOnly`,  `Action`,  `GeoDataID`,  `Altitude`,  `Substance`,  `LevelFlags`,  `Quantity`,  `Quality`)
566		values(in_TerID, versionID, in_ChangeIdx,  in_IsServerOnly, in_Action, in_GeoDataID, in_Altitude, in_Substance, in_LevelFlags, in_Quantity, in_Quality);
567
568	return versionID;
569
570END//
571DELIMITER ;
572
573
574-- Export de la structure de fonction lif_1. f_addSkill
575DELIMITER //
576CREATE DEFINER=`root`@`localhost` FUNCTION `f_addSkill`(
577	`CharacterID` INT UNSIGNED,
578	`SkillTypeID` INT UNSIGNED,
579	`SkillAmount` INT UNSIGNED,
580	`LockStatus` TINYINT
581) RETURNS int(10) unsigned
582BEGIN
583  INSERT INTO skills(`CharacterID`, `SkillTypeID`, `SkillAmount`, `LockStatus`)
584  VALUES(CharacterID, SkillTypeID, SkillAmount, LockStatus);
585
586  return LAST_INSERT_ID();
587END//
588DELIMITER ;
589
590
591-- Export de la structure de fonction lif_1. f_createContainer
592DELIMITER //
593CREATE DEFINER=`root`@`localhost` FUNCTION `f_createContainer`(
594	ParentContainerID INT UNSIGNED,
595	ItemTypeID INT UNSIGNED,
596	Quality TINYINT UNSIGNED
597) RETURNS int(11)
598BEGIN
599	DECLARE containerID INT UNSIGNED default 0;
600	
601	INSERT INTO containers(`ParentID`, `ObjectTypeID`, `Quality`)
602	VALUES(ParentContainerID, ItemTypeID, Quality);
603	SET containerID = LAST_INSERT_ID();
604	
605	return containerID;
606END//
607DELIMITER ;
608
609
610-- Export de la structure de fonction lif_1. f_createEquipment
611DELIMITER //
612CREATE DEFINER=`root`@`localhost` FUNCTION `f_createEquipment`() RETURNS int(10)
613    MODIFIES SQL DATA
614BEGIN
615	return f_createRootContainer('equipment');
616
617END//
618DELIMITER ;
619
620
621-- Export de la structure de fonction lif_1. f_createForestItem
622DELIMITER //
623CREATE DEFINER=`root`@`localhost` FUNCTION `f_createForestItem`(
624	in_geoID INT UNSIGNED,
625	in_treeType TINYINT UNSIGNED,
626	in_subcellMask TINYINT UNSIGNED,
627	in_ageTime INT UNSIGNED,
628	in_quality FLOAT
629) RETURNS int(10) unsigned
630    MODIFIES SQL DATA
631BEGIN
632
633/*
634	declare geoSubCellsObjectMask int unsigned default NULL;
635	
636	-- geo data
637	select SubCellsObjectMask
638		from geo_data
639		where ID = in_geoID
640		limit 1
641		into geoSubCellsObjectMask
642		for update;
643
644	if(geoSubCellsObjectMask is null) then
645		-- geo coordinates unknown (need to generate geo_data?). Report error
646		return NULL;
647	elseif((geoSubCellsObjectMask & 1) > 0) then
648		-- alerady have tree at this place. Report error
649		return NULL;
650	else
651		-- set tree lock
652		update geo_data
653			set SubCellsObjectMask = (SubCellsObjectMask | 1)
654			where ID = in_geoID;
655	end if;
656*/
657
658	/* forest */
659	insert into forest
660		(GeoDataID,       TreeType,    SubcellMask,    AgeTime,    Quality)
661		values (in_geoID, in_treeType, in_subcellMask, in_ageTime, in_quality);
662	
663	return in_geoID;
664	
665END//
666DELIMITER ;
667
668
669-- Export de la structure de fonction lif_1. f_createGuild
670DELIMITER //
671CREATE DEFINER=`root`@`localhost` FUNCTION `f_createGuild`(
672	`inGuildTypeID` TINYINT UNSIGNED
673) RETURNS int(10) unsigned
674BEGIN
675	DECLARE guildID INT UNSIGNED default NULL;
676
677	INSERT INTO guilds (GuildTypeID) values (inGuildTypeID);
678	SET guildID =LAST_INSERT_ID();
679
680	RETURN guildID;
681END//
682DELIMITER ;
683
684
685-- Export de la structure de fonction lif_1. f_createGuildClaim
686DELIMITER //
687CREATE DEFINER=`root`@`localhost` FUNCTION `f_createGuildClaim`(
688	`inGuildID` INT UNSIGNED,
689	`inCenterGeoID` INT UNSIGNED,
690	`inRadius` INT UNSIGNED
691) RETURNS int(10) unsigned
692BEGIN
693	DECLARE newGuildLandID, newClaimID INT UNSIGNED default NULL;
694
695	INSERT INTO guild_lands(`GuildID`, `CenterGeoID`, `Radius`, `LandType`)
696	VALUES                 (inGuildID, inCenterGeoID, inRadius, 1/*core*/);
697
698	SET newGuildLandID =LAST_INSERT_ID();
699
700	INSERT INTO claims(`GuildLandID`, `SupportPoints`)
701	VALUES            (newGuildLandID,   0);
702
703	SET newClaimID =LAST_INSERT_ID();
704
705	RETURN newClaimID;
706END//
707DELIMITER ;
708
709
710-- Export de la structure de fonction lif_1. f_createInventory
711DELIMITER //
712CREATE DEFINER=`root`@`localhost` FUNCTION `f_createInventory`() RETURNS int(10)
713    MODIFIES SQL DATA
714BEGIN
715	return f_createRootContainer('inventory');
716
717END//
718DELIMITER ;
719
720
721-- Export de la structure de fonction lif_1. f_createRootContainer
722DELIMITER //
723CREATE DEFINER=`root`@`localhost` FUNCTION `f_createRootContainer`(
724	containerName varchar(64)
725) RETURNS int(10)
726    MODIFIES SQL DATA
727BEGIN
728	declare containerID, typeID int default NULL;
729	SELECT ID FROM objects_types
730		WHERE Name = containerName COLLATE utf8_unicode_ci and IsContainer = 1
731		LIMIT 1 INTO typeID;
732	/* container */
733	if(typeID is not null) then
734		SET containerID =f_createContainer( NULL, typeID, 0);
735	end if;
736	return containerID;
737END//
738DELIMITER ;
739
740
741-- Export de la structure de procédure lif_1. f_deleteContainer
742DELIMITER //
743CREATE DEFINER=`root`@`localhost` PROCEDURE `f_deleteContainer`(IN `InContainerID` INT UNSIGNED)
744BEGIN
745	DECLARE _id INT UNSIGNED default NULL;
746	DECLARE _featureID INT UNSIGNED default NULL;
747	DECLARE _customtextID INT UNSIGNED default NULL;
748	DECLARE done INT DEFAULT FALSE;
749	DECLARE cur CURSOR FOR SELECT ID FROM items WHERE ContainerID =InContainerID;
750	DECLARE cur2 CURSOR FOR SELECT ID FROM containers WHERE ParentID =InContainerID;
751	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =TRUE;
752
753	SELECT FeatureID FROM containers WHERE ID =InContainerID INTO _featureID;
754	DELETE FROM building_items WHERE ContainerID =InContainerID;
755
756	OPEN cur;
757	read_loop: LOOP
758		FETCH cur INTO _id;
759		if done then
760			LEAVE read_loop;
761	  end if;
762		CALL f_deleteItem( _id);
763	END LOOP;
764	CLOSE cur;
765
766	SET done = FALSE;
767	OPEN cur2;
768	read_loop2: LOOP
769		FETCH cur2 INTO _id;
770		if done then
771			LEAVE read_loop2;
772	  end if;
773		CALL f_deleteContainer( _id);
774	END LOOP;
775	CLOSE cur2;
776
777	DELETE FROM containers WHERE ID =InContainerID;
778
779	if( _featureID is not NULL) then
780		SELECT CustomtextID FROM features WHERE ID =_featureID INTO _customtextID;
781		DELETE FROM features WHERE `ID`=_featureID;
782		if( _customtextID is not NULL) then
783			CALL f_removeCustomText( _customtextID);
784		end if;
785	end if;
786END//
787DELIMITER ;
788
789
790-- Export de la structure de procédure lif_1. f_deleteItem
791DELIMITER //
792CREATE DEFINER=`root`@`localhost` PROCEDURE `f_deleteItem`(inItemID INT UNSIGNED)
793BEGIN
794
795	DECLARE _featureID INT UNSIGNED default NULL;
796	DECLARE _customtextID INT UNSIGNED default NULL;
797	DECLARE _blueprintID INT UNSIGNED default NULL;
798
799	SELECT FeatureID FROM items WHERE ID = inItemID INTO _featureID;
800	-- DELETE FROM equipment_slots WHERE ItemID = inItemID;
801	UPDATE equipment_slots set ItemID = NULL WHERE ItemID = inItemID;
802	DELETE FROM item_effects WHERE ItemID = inItemID;
803	DELETE FROM stables_pens WHERE ItemID = inItemID;
804	DELETE FROM items WHERE ID = inItemID;
805
806	if( _featureID is not NULL) then
807		SELECT CustomtextID, BlueprintID FROM features WHERE ID =_featureID INTO _customtextID, _blueprintID;
808		DELETE FROM features WHERE `ID`=_featureID;
809		if(_customtextID is not NULL) then
810			CALL f_removeCustomText( _customtextID);
811		end if;
812		if(_blueprintID is not NULL) then
813			DELETE FROM `character_blueprints` WHERE BlueprintID=_blueprintID;
814			DELETE FROM `blueprint_requirements` WHERE BlueprintID=_blueprintID;
815			DELETE FROM `blueprints` WHERE ID=_blueprintID;
816		end if;
817	end if;
818
819END//
820DELIMITER ;
821
822
823-- Export de la structure de procédure lif_1. f_fromGeoID
824DELIMITER //
825CREATE DEFINER=`root`@`localhost` PROCEDURE `f_fromGeoID`(
826	`geoID` INT UNSIGNED
827)
828    DETERMINISTIC
829BEGIN
830	select
831		(geoID >> 18) as terID,
832		(geoID & ((1 << 9) - 1)) as `x`,
833		((geoID >> 9) & ((1 << (9)) - 1)) as `y`;
834END//
835DELIMITER ;
836
837
838-- Export de la structure de fonction lif_1. f_getServerUUID
839DELIMITER //
840CREATE DEFINER=`root`@`localhost` FUNCTION `f_getServerUUID`() RETURNS char(36) CHARSET utf8 COLLATE utf8_unicode_ci
841    DETERMINISTIC
842BEGIN
843 DECLARE s_uuid char(36) DEFAULT NULL;
844 SELECT `Uuid` FROM server_uuid WHERE ID = 1 into s_uuid;
845 RETURN s_uuid;
846END//
847DELIMITER ;
848
849
850-- Export de la structure de fonction lif_1. f_insertCustomText
851DELIMITER //
852CREATE DEFINER=`root`@`localhost` FUNCTION `f_insertCustomText`(
853	inText VARCHAR(255)
854) RETURNS int(10) unsigned
855BEGIN
856	DECLARE _id INT UNSIGNED DEFAULT NULL;
857	SELECT ID FROM custom_texts WHERE Custom_text =inText COLLATE utf8_unicode_ci INTO _id;
858	if( _id is NULL) then
859		INSERT INTO custom_texts (`Custom_text`) VALUES (inText);
860		SET _id =LAST_INSERT_ID();
861	end if;
862	RETURN _id;
863END//
864DELIMITER ;
865
866
867-- Export de la structure de fonction lif_1. f_insertNewItemInventory
868DELIMITER //
869CREATE DEFINER=`root`@`localhost` FUNCTION `f_insertNewItemInventory`(
870	`inContainerID` INT UNSIGNED,
871	`inItemTypeID` INT UNSIGNED,
872	`inQuality` TINYINT UNSIGNED,
873	`inQuantity` INT UNSIGNED,
874	`inCreatedDurability` SMALLINT UNSIGNED,
875	`inDurability` SMALLINT UNSIGNED,
876	`inFeatureText` VARCHAR(255),
877	`inHaveEffects` TINYINT UNSIGNED,
878	`inBlueprintID` INT UNSIGNED,
879	`inCreatedRegionID` INT UNSIGNED,
880	`inHorseHP` INT UNSIGNED
881) RETURNS int(10) unsigned
882BEGIN
883
884	DECLARE customTextID INT UNSIGNED default NULL;
885	DECLARE featureID INT UNSIGNED default NULL;
886
887	/* use this code wnen items.ID field close to its upper limit
888
889	start transaction;
890
891	select (i1.id + 1) as free_id
892	from items as i1
893	left join items as i2 on (i1.id + 1) = i2.id
894	where i2.id is null
895	limit 1
896	into itemID
897	for update;
898
899	use itemID for insert below
900	*/
901
902	if(LENGTH(IFNULL(inFeatureText, '')) > 0) then
903		SET customTextID = f_insertCustomText(inFeatureText);
904	end if;
905
906	if(customTextID is not NULL OR inHaveEffects > 0 OR inBlueprintID is not NULL OR inCreatedRegionID is not NULL OR inHorseHP is not NULL) then
907		INSERT INTO features (`CustomtextID`,`has_effects`,`CreatedRegionID`,`BlueprintID`,`HorseHP`)
908			VALUES (customTextID, inHaveEffects,inCreatedRegionID,inBlueprintID,inHorseHP);
909		SET featureID = LAST_INSERT_ID();
910	end if;
911
912	INSERT INTO items(`ContainerID`, `ObjectTypeID`, `Quality`, `Quantity`, `Durability`, `CreatedDurability`, `FeatureID`)
913		VALUES(inContainerID, inItemTypeID, inQuality, inQuantity, inDurability, inCreatedDurability, featureID);
914
915	return LAST_INSERT_ID();
916
917	/*commit;*/
918END//
919DELIMITER ;
920
921
922-- Export de la structure de fonction lif_1. f_lastUsedDayNumber
923DELIMITER //
924CREATE DEFINER=`root`@`localhost` FUNCTION `f_lastUsedDayNumber`() RETURNS int(10) unsigned
925BEGIN
926	declare lastDayNum int unsigned default 0;
927	select ifnull(max(ID), 0) from day_version into lastDayNum;
928
929	return lastDayNum;
930END//
931DELIMITER ;
932
933
934-- Export de la structure de procédure lif_1. f_removeCustomText
935DELIMITER //
936CREATE DEFINER=`root`@`localhost` PROCEDURE `f_removeCustomText`(
937	inID INT UNSIGNED
938)
939BEGIN
940	if(NOT EXISTS(SELECT * FROM features WHERE CustomtextID = inID)) then
941		DELETE FROM custom_texts WHERE ID = inID;
942	end if;
943END//
944DELIMITER ;
945
946
947-- Export de la structure de procédure lif_1. f_renameContainer
948DELIMITER //
949CREATE DEFINER=`root`@`localhost` PROCEDURE `f_renameContainer`(
950	IN `inContainerID` INT UNSIGNED,
951	IN `inText` VARCHAR(255)
952)
953BEGIN
954	DECLARE var_oldCustomTextID, var_newCustomTextID INT UNSIGNED default NULL;
955	DECLARE var_featureID INT UNSIGNED default NULL;
956
957	if( LENGTH( IFNULL( inText, '')) > 0) then
958		SET var_newCustomTextID =f_insertCustomText( inText);
959	end if;
960
961	SELECT FeatureID FROM `containers` WHERE ID =inContainerID INTO var_featureID;
962	if( var_featureID is not NULL) then
963
964		SELECT CustomtextID FROM features WHERE ID =var_featureID INTO var_oldCustomTextID;
965		if( var_newCustomTextID is not NULL) then
966			-- update feature's text
967			UPDATE features set `CustomtextID` = var_newCustomTextID;
968		else
969			-- remove custom text and feature
970			UPDATE containers SET `FeatureID` =NULL WHERE ID =inContainerID;
971			DELETE FROM features WHERE ID=var_featureID;
972		end if;
973
974		if( var_oldCustomTextID is not NULL) then
975			CALL f_removeCustomText( var_oldCustomTextID);
976		end if;
977
978	elseif( var_newCustomTextID is not NULL) then
979		-- create new feature
980		INSERT INTO features (`CustomtextID`) VALUES (var_newCustomTextID);
981		SET var_featureID =LAST_INSERT_ID();
982		UPDATE containers SET `FeatureID` =var_featureID WHERE ID =inContainerID;
983	end if;
984END//
985DELIMITER ;
986
987
988-- Export de la structure de procédure lif_1. f_setFilesCrc
989DELIMITER //
990CREATE DEFINER=`root`@`localhost` PROCEDURE `f_setFilesCrc`(
991	in_terID      int unsigned,
992	in_TerCRC     int unsigned,
993	in_GeoIdxCRC  int unsigned,
994	in_GeoDatCRC  int unsigned,
995	in_ObjectsCRC int unsigned,
996	in_ForestCRC  int unsigned
997)
998BEGIN
999	update terrain_blocks set
1000		`TerCRC`     = in_TerCRC,
1001		`GeoIdxCRC`  = in_GeoIdxCRC,
1002		`GeoDatCRC`  = in_GeoDatCRC,
1003		`ObjectsCRC` = in_ObjectsCRC,
1004		`ForestCRC`  = in_ForestCRC
1005		where ID=in_terID;
1006END//
1007DELIMITER ;
1008
1009
1010-- Export de la structure de fonction lif_1. f_terIDFromGeoID
1011DELIMITER //
1012CREATE DEFINER=`root`@`localhost` FUNCTION `f_terIDFromGeoID`(
1013	`geoID` INT UNSIGNED
1014) RETURNS int(10) unsigned
1015    NO SQL
1016    DETERMINISTIC
1017BEGIN
1018	return (geoID >> 18);
1019END//
1020DELIMITER ;
1021
1022
1023-- Export de la structure de fonction lif_1. f_toGeoID
1024DELIMITER //
1025CREATE DEFINER=`root`@`localhost` FUNCTION `f_toGeoID`(
1026	`terID` INT UNSIGNED,
1027	`x` INT UNSIGNED,
1028	`y` INT UNSIGNED
1029) RETURNS int(10) unsigned
1030    NO SQL
1031    DETERMINISTIC
1032BEGIN
1033	return ((terID << 18) | (y << 9) | x);
1034END//
1035DELIMITER ;
1036
1037
1038-- Export de la structure de fonction lif_1. f_treeQualityToTreeHealth
1039DELIMITER //
1040CREATE DEFINER=`root`@`localhost` FUNCTION `f_treeQualityToTreeHealth`(
1041	`quality` FLOAT
1042) RETURNS int(10) unsigned
1043    DETERMINISTIC
1044BEGIN
1045	if (quality = 0) then
1046		return 3; -- Stump
1047	elseif (quality < 33) then
1048		return 0; -- Ill
1049	elseif (quality < 66) then
1050		return 1; -- Normal
1051	else
1052		return 2; -- Great
1053	end if;
1054END//
1055DELIMITER ;
1056
1057
1058-- Export de la structure de table lif_1. geo_patch
1059CREATE TABLE IF NOT EXISTS `geo_patch` (
1060  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1061  `TerID` int(10) unsigned NOT NULL,
1062  `Version` int(10) unsigned NOT NULL,
1063  `ChangeIndex` int(10) unsigned NOT NULL COMMENT 'Internal index of change within one version (starts from 1)',
1064  `IsServerOnly` tinyint(3) unsigned NOT NULL,
1065  `Action` tinyint(3) unsigned NOT NULL COMMENT '1=Add; 2=Delete;',
1066  `GeoDataID` int(10) unsigned NOT NULL,
1067  `Altitude` smallint(5) unsigned NOT NULL,
1068  `Substance` tinyint(3) unsigned DEFAULT NULL COMMENT 'GeoSubstanceID',
1069  `LevelFlags` tinyint(3) unsigned DEFAULT NULL COMMENT 'Level flags from geo-file',
1070  `Quantity` smallint(5) unsigned DEFAULT NULL COMMENT 'substance quantity (using as decay counter for air)',
1071  `Quality` tinyint(3) unsigned DEFAULT NULL COMMENT 'substance quality',
1072  PRIMARY KEY (`ID`),
1073  KEY `FK_geo_patch_terrain_blocks` (`TerID`),
1074  CONSTRAINT `FK_geo_patch_terrain_blocks` FOREIGN KEY (`TerID`) REFERENCES `terrain_blocks` (`ID`)
1075) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1076
1077-- L'exportation de données n'était pas sélectionnée.
1078
1079
1080-- Export de la structure de table lif_1. guilds
1081CREATE TABLE IF NOT EXISTS `guilds` (
1082  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1083  `Name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
1084  `GuildTypeID` tinyint(3) unsigned NOT NULL,
1085  `IsActive` tinyint(3) unsigned NOT NULL DEFAULT '1',
1086  `GuildCharter` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
1087  `GuildTag` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
1088  `HeraldryID` int(10) unsigned DEFAULT NULL,
1089  `CreateTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1090  `DeleteTimestamp` timestamp NULL DEFAULT NULL,
1091  PRIMARY KEY (`ID`),
1092  UNIQUE KEY `UNQ_Name` (`Name`),
1093  UNIQUE KEY `UNQ_GuildTag` (`GuildTag`),
1094  UNIQUE KEY `FK_guilds_heraldries` (`HeraldryID`),
1095  KEY `IDX_IsActive` (`IsActive`),
1096  KEY `FK_guilds_guild_types` (`GuildTypeID`),
1097  CONSTRAINT `FK_guilds_guild_types` FOREIGN KEY (`GuildTypeID`) REFERENCES `guild_types` (`ID`),
1098  CONSTRAINT `FK_guilds_heraldries` FOREIGN KEY (`HeraldryID`) REFERENCES `heraldries` (`ID`)
1099) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1100
1101-- L'exportation de données n'était pas sélectionnée.
1102
1103
1104-- Export de la structure de table lif_1. guild_actions_processed
1105CREATE TABLE IF NOT EXISTS `guild_actions_processed` (
1106  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1107  `TicketID` bigint(20) unsigned NOT NULL COMMENT 'Unique number for identifying query',
1108  `ActionType` enum('guild_create','guild_destroy','guild_change_level','player_invite_to_guild','player_joined_guild','player_left_guild','player_new_guild_role','guild_change_standing') COLLATE utf8_unicode_ci NOT NULL COMMENT '"guild_create" - Create a guild, "guild_destroy" - Destroy a guild, "guild_change_level" - Change guild level, "player_invite_to_guild" - Player invited to guild, "player_joined_guild" - Player joined a guild, "player_left_guild" - Player left or kicked from guild, "player_new_guild_role" - Player has been promoted or demoted, "guild_change_standing" - Guild has changed its standing',
1109  `ProcessedStatus` enum('failed','processed','user_accepted','user_declined') COLLATE utf8_unicode_ci NOT NULL,
1110  `ProducerCharID` int(10) unsigned DEFAULT NULL COMMENT 'character.ID',
1111  `ProducerCharDeletedID` int(10) unsigned DEFAULT NULL COMMENT 'deleted_character_info.ID',
1112  `GuildID` int(10) unsigned DEFAULT NULL COMMENT 'guilds.ID',
1113  `GuildDeletedID` int(10) unsigned DEFAULT NULL COMMENT 'deleted_guild_info.ID',
1114  `CharID` int(10) unsigned DEFAULT NULL COMMENT 'character.ID',
1115  `CharDeletedID` int(10) unsigned DEFAULT NULL COMMENT 'deleted_character_info.ID',
1116  `GuildName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
1117  `GuildTag` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
1118  `GuildCharter` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
1119  `GuildTypeID` tinyint(3) unsigned DEFAULT NULL COMMENT 'guild_types.ID',
1120  `CharIsKicked` tinyint(3) unsigned NOT NULL DEFAULT '0',
1121  `CharGuildRoleID` tinyint(3) unsigned DEFAULT NULL COMMENT 'guild_roles.ID',
1122  `OtherGuildID` int(10) unsigned DEFAULT NULL COMMENT 'guilds.ID',
1123  `OtherGuildDeletedID` int(10) unsigned DEFAULT NULL COMMENT 'deleted_guild_info.ID',
1124  `StandingTypeID` tinyint(3) unsigned DEFAULT NULL COMMENT 'guild_standing_types',
1125  `ProcessedTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1126  PRIMARY KEY (`ID`),
1127  UNIQUE KEY `UNQ_TicketID` (`TicketID`),
1128  KEY `FK_guild_actions_processed_character1` (`ProducerCharID`),
1129  CONSTRAINT `FK_guild_actions_processed_character1` FOREIGN KEY (`ProducerCharID`) REFERENCES `character` (`ID`)
1130) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1131
1132-- L'exportation de données n'était pas sélectionnée.
1133
1134
1135-- Export de la structure de table lif_1. guild_actions_queue
1136CREATE TABLE IF NOT EXISTS `guild_actions_queue` (
1137  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1138  `TicketID` bigint(20) unsigned NOT NULL COMMENT 'Unique number for identifying query',
1139  `ActionType` enum('guild_create','guild_destroy','guild_change_level','player_invite_to_guild','player_joined_guild','player_left_guild','player_new_guild_role','guild_change_standing') COLLATE utf8_unicode_ci NOT NULL COMMENT '"guild_create" - Create a guild, "guild_destroy" - Destroy a guild, "guild_change_level" - Change guild level, "player_invite_to_guild" - Player invited to guild, "player_joined_guild" - Player joined a guild, "player_left_guild" - Player left or kicked from guild, "player_new_guild_role" - Player has been promoted or demoted, "guild_change_standing" - Guild has changed its standing',
1140  `ProducerCharID` int(10) unsigned NOT NULL,
1141  `GuildID` int(10) unsigned DEFAULT NULL,
1142  `CharID` int(10) unsigned DEFAULT NULL,
1143  `GuildName` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
1144  `GuildTag` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
1145  `GuildCharter` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
1146  `GuildTypeID` tinyint(3) unsigned DEFAULT NULL,
1147  `CharIsKicked` tinyint(3) unsigned NOT NULL DEFAULT '0',
1148  `CharGuildRoleID` tinyint(3) unsigned DEFAULT NULL,
1149  `OtherGuildID` int(10) unsigned DEFAULT NULL,
1150  `StandingTypeID` tinyint(3) unsigned DEFAULT NULL,
1151  `AddedTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
1152  `OwnerConnectionID` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'For internal processing',
1153  `OwnedTimestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'For internal processing. Updates with OwnerConnectionID',
1154  PRIMARY KEY (`ID`),
1155  KEY `IDX_OwnerConnectionID_OwnedTime` (`OwnerConnectionID`,`OwnedTimestamp`),
1156  KEY `FK_guild_actions_queue_character1` (`ProducerCharID`),
1157  KEY `FK_guild_actions_queue_character2` (`CharID`),
1158  KEY `FK_guild_actions_queue_guilds1` (`GuildID`),
1159  KEY `FK_guild_actions_queue_guilds2` (`OtherGuildID`),
1160  KEY `FK_guild_actions_queue_guild_types` (`GuildTypeID`),
1161  KEY `FK_guild_actions_queue_guild_roles` (`CharGuildRoleID`),
1162  KEY `FK_guild_actions_queue_guild_standing_types` (`StandingTypeID`),
1163  CONSTRAINT `FK_guild_actions_queue_character1` FOREIGN KEY (`ProducerCharID`) REFERENCES `character` (`ID`),
1164  CONSTRAINT `FK_guild_actions_queue_character2` FOREIGN KEY (`CharID`) REFERENCES `character` (`ID`),
1165  CONSTRAINT `FK_guild_actions_queue_guild_roles` FOREIGN KEY (`CharGuildRoleID`) REFERENCES `guild_roles` (`ID`),
1166  CONSTRAINT `FK_guild_actions_queue_guild_standing_types` FOREIGN KEY (`StandingTypeID`) REFERENCES `guild_standing_types` (`ID`),
1167  CONSTRAINT `FK_guild_actions_queue_guild_types` FOREIGN KEY (`GuildTypeID`) REFERENCES `guild_types` (`ID`),
1168  CONSTRAINT `FK_guild_actions_queue_guilds1` FOREIGN KEY (`GuildID`) REFERENCES `guilds` (`ID`),
1169  CONSTRAINT `FK_guild_actions_queue_guilds2` FOREIGN KEY (`OtherGuildID`) REFERENCES `guilds` (`ID`)
1170) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1171
1172-- L'exportation de données n'était pas sélectionnée.
1173
1174
1175-- Export de la structure de table lif_1. guild_lands
1176CREATE TABLE IF NOT EXISTS `guild_lands` (
1177  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1178  `GuildID` int(10) unsigned NOT NULL,
1179  `Name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
1180  `CenterGeoID` int(10) unsigned NOT NULL,
1181  `Radius` int(10) unsigned NOT NULL,
1182  `LandType` int(10) unsigned NOT NULL COMMENT '1=Core, 2=Suburbia',
1183  PRIMARY KEY (`ID`),
1184  KEY `FK_guild_lands_guilds` (`GuildID`),
1185  CONSTRAINT `FK_guild_lands_guilds` FOREIGN KEY (`GuildID`) REFERENCES `guilds` (`ID`)
1186) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1187
1188-- L'exportation de données n'était pas sélectionnée.
1189
1190
1191-- Export de la structure de table lif_1. guild_roles
1192CREATE TABLE IF NOT EXISTS `guild_roles` (
1193  `ID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
1194  `Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
1195  PRIMARY KEY (`ID`)
1196) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1197
1198-- L'exportation de données n'était pas sélectionnée.
1199
1200
1201-- Export de la structure de table lif_1. guild_standings
1202CREATE TABLE IF NOT EXISTS `guild_standings` (
1203  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1204  `GuildID1` int(10) unsigned NOT NULL,
1205  `GuildID2` int(10) unsigned NOT NULL,
1206  `StandingTypeID` tinyint(3) unsigned NOT NULL,
1207  PRIMARY KEY (`ID`),
1208  UNIQUE KEY `UNQ_GuildID1_GuildID2` (`GuildID1`,`GuildID2`),
1209  KEY `FK_guild_standings_guild_standing_types` (`StandingTypeID`),
1210  KEY `IDX_GuildID2_GuildID1` (`GuildID2`,`GuildID1`),
1211  CONSTRAINT `FK_guild_standings_guild_standing_types` FOREIGN KEY (`StandingTypeID`) REFERENCES `guild_standing_types` (`ID`),
1212  CONSTRAINT `FK_guild_standings_guilds1` FOREIGN KEY (`GuildID1`) REFERENCES `guilds` (`ID`),
1213  CONSTRAINT `FK_guild_standings_guilds2` FOREIGN KEY (`GuildID2`) REFERENCES `guilds` (`ID`)
1214) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1215
1216-- L'exportation de données n'était pas sélectionnée.
1217
1218
1219-- Export de la structure de table lif_1. guild_standing_types
1220CREATE TABLE IF NOT EXISTS `guild_standing_types` (
1221  `ID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
1222  `Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
1223  PRIMARY KEY (`ID`)
1224) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1225
1226-- L'exportation de données n'était pas sélectionnée.
1227
1228
1229-- Export de la structure de table lif_1. guild_types
1230CREATE TABLE IF NOT EXISTS `guild_types` (
1231  `ID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
1232  `GuildLevel` int(11) NOT NULL,
1233  `Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
1234  `MessageID` int(10) unsigned NOT NULL,
1235  PRIMARY KEY (`ID`)
1236) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1237
1238-- L'exportation de données n'était pas sélectionnée.
1239
1240
1241-- Export de la structure de table lif_1. guild_type_role_msgs
1242CREATE TABLE IF NOT EXISTS `guild_type_role_msgs` (
1243  `ID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
1244  `GuildTypeID` tinyint(3) unsigned NOT NULL,
1245  `GuildRoleID` tinyint(3) unsigned NOT NULL,
1246  `MessageID` int(10) unsigned NOT NULL,
1247  PRIMARY KEY (`ID`),
1248  KEY `FK_guild_type_role_msgs_guild_roles` (`GuildRoleID`),
1249  KEY `FK_guild_type_role_msgs_guild_types` (`GuildTypeID`),
1250  CONSTRAINT `FK_guild_type_role_msgs_guild_roles` FOREIGN KEY (`GuildRoleID`) REFERENCES `guild_roles` (`ID`),
1251  CONSTRAINT `FK_guild_type_role_msgs_guild_types` FOREIGN KEY (`GuildTypeID`) REFERENCES `guild_types` (`ID`)
1252) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1253
1254-- L'exportation de données n'était pas sélectionnée.
1255
1256
1257-- Export de la structure de table lif_1. heraldic_charges
1258CREATE TABLE IF NOT EXISTS `heraldic_charges` (
1259  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1260  `SymbolIndex` tinyint(3) unsigned NOT NULL,
1261  `ColorIndex` tinyint(3) unsigned NOT NULL,
1262  `Position` enum('top_left','top_center','top_right','middle_left','true_center','middle_right','bottom_left','bottom_center','bottom_right') COLLATE utf8_unicode_ci NOT NULL,
1263  `Size` enum('small','medium','large') COLLATE utf8_unicode_ci NOT NULL,
1264  PRIMARY KEY (`ID`),
1265  UNIQUE KEY `UNQ_SymbolIndex_ColorIndex_Position_Size` (`SymbolIndex`,`ColorIndex`,`Position`,`Size`)
1266) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1267
1268-- L'exportation de données n'était pas sélectionnée.
1269
1270
1271-- Export de la structure de table lif_1. heraldries
1272CREATE TABLE IF NOT EXISTS `heraldries` (
1273  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1274  `BackgroundIndex` tinyint(3) unsigned NOT NULL,
1275  `BackgroundColorIndex1` tinyint(3) unsigned NOT NULL,
1276  `BackgroundColorIndex2` tinyint(3) unsigned NOT NULL,
1277  `ChargeID1` int(10) unsigned DEFAULT NULL,
1278  `ChargeID2` int(10) unsigned DEFAULT NULL,
1279  PRIMARY KEY (`ID`),
1280  KEY `FK_heraldries_heraldic_charges1` (`ChargeID1`),
1281  KEY `FK_heraldries_heraldic_charges2` (`ChargeID2`),
1282  CONSTRAINT `FK_heraldries_heraldic_charges1` FOREIGN KEY (`ChargeID1`) REFERENCES `heraldic_charges` (`ID`),
1283  CONSTRAINT `FK_heraldries_heraldic_charges2` FOREIGN KEY (`ChargeID2`) REFERENCES `heraldic_charges` (`ID`)
1284) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1285
1286-- L'exportation de données n'était pas sélectionnée.
1287
1288
1289-- Export de la structure de table lif_1. horses
1290CREATE TABLE IF NOT EXISTS `horses` (
1291  `ID` int(10) unsigned NOT NULL,
1292  `ObjectTypeID` int(10) unsigned NOT NULL,
1293  `Quality` tinyint(3) unsigned NOT NULL DEFAULT '0',
1294  `HP` int(11) NOT NULL DEFAULT '1000000' COMMENT '6 digits after point',
1295  `GeoID` int(10) unsigned NOT NULL,
1296  `GeoAlt` smallint(5) unsigned NOT NULL,
1297  `OffsetX` smallint(6) NOT NULL COMMENT 'ingame millimeters',
1298  `OffsetY` smallint(6) NOT NULL COMMENT 'ingame millimeters',
1299  `OffsetZ` int(10) NOT NULL COMMENT 'ingame millimeters',
1300  `TurnAngle` smallint(6) NOT NULL COMMENT 'rotation angle',
1301  `MountedCharacterID` int(10) unsigned DEFAULT NULL,
1302  `OwnerID` int(10) unsigned DEFAULT NULL,
1303  `DroppedTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
1304  `Durability` smallint(10) unsigned NOT NULL DEFAULT '100' COMMENT '2 digits after point',
1305  `CreatedDurability` smallint(10) unsigned NOT NULL DEFAULT '100' COMMENT '2 digits after point',
1306  PRIMARY KEY (`ID`),
1307  UNIQUE KEY `UNQ_MountedCharacterID` (`MountedCharacterID`),
1308  KEY `FK_horses_character` (`ObjectTypeID`),
1309  KEY `FK_horses_character2` (`OwnerID`),
1310  CONSTRAINT `FK_horses_character` FOREIGN KEY (`MountedCharacterID`) REFERENCES `character` (`ID`),
1311  CONSTRAINT `FK_horses_character2` FOREIGN KEY (`OwnerID`) REFERENCES `character` (`ID`),
1312  CONSTRAINT `FK_horses_objects_types` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`)
1313) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1314
1315-- L'exportation de données n'était pas sélectionnée.
1316
1317
1318-- Export de la structure de table lif_1. horses_server_id_ranges
1319CREATE TABLE IF NOT EXISTS `horses_server_id_ranges` (
1320  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1321  `ServerID` int(10) unsigned NOT NULL,
1322  `RangeStartID` int(10) unsigned NOT NULL,
1323  `RangeEndID` int(10) unsigned NOT NULL,
1324  PRIMARY KEY (`ID`),
1325  KEY `IDX_RangeEndID` (`RangeEndID`),
1326  KEY `IDX_ServerID` (`ServerID`)
1327) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='horses ID ranges assigned to servers. Should be accessed only by using p_issueIdRange_horses';
1328
1329-- L'exportation de données n'était pas sélectionnée.
1330
1331
1332-- Export de la structure de table lif_1. horses_server_id_ranges_lock
1333CREATE TABLE IF NOT EXISTS `horses_server_id_ranges_lock` (
1334  `ID` tinyint(3) unsigned NOT NULL,
1335  `IsLocked` tinyint(3) unsigned NOT NULL,
1336  PRIMARY KEY (`ID`)
1337) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Dummy table for locking from p_issueIdRange_horses. Do not store actual data, using only for internal needs';
1338
1339-- L'exportation de données n'était pas sélectionnée.
1340
1341
1342-- Export de la structure de table lif_1. items
1343CREATE TABLE IF NOT EXISTS `items` (
1344  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1345  `ContainerID` int(10) unsigned NOT NULL,
1346  `ObjectTypeID` int(10) unsigned NOT NULL,
1347  `Quality` tinyint(6) unsigned NOT NULL DEFAULT '0',
1348  `Quantity` int(6) unsigned NOT NULL DEFAULT '0',
1349  `Durability` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point',
1350  `CreatedDurability` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point, also acts as MaxDurability',
1351  `FeatureID` int(10) unsigned DEFAULT NULL,
1352  PRIMARY KEY (`ID`),
1353  KEY `FK_ItemsContainerID` (`ContainerID`),
1354  KEY `FK_ItemTypeID` (`ObjectTypeID`) USING BTREE,
1355  KEY `FK_ItemsFeaturesID` (`FeatureID`),
1356  CONSTRAINT `FK_ItemsContainerID` FOREIGN KEY (`ContainerID`) REFERENCES `containers` (`ID`),
1357  CONSTRAINT `FK_ItemsFeaturesID` FOREIGN KEY (`FeatureID`) REFERENCES `features` (`ID`),
1358  CONSTRAINT `FK_ItemsObjectTypeID` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`)
1359) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1360
1361-- L'exportation de données n'était pas sélectionnée.
1362
1363
1364-- Export de la structure de table lif_1. item_effects
1365CREATE TABLE IF NOT EXISTS `item_effects` (
1366  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1367  `ItemID` int(10) unsigned NOT NULL,
1368  `EffectID` tinyint(10) unsigned NOT NULL,
1369  `Magnitude` smallint(10) unsigned NOT NULL COMMENT '3 digits after point',
1370  PRIMARY KEY (`ID`),
1371  KEY `FK_ItemEffectsItemID` (`ItemID`),
1372  KEY `FK_item_effects_effects` (`EffectID`),
1373  CONSTRAINT `FK_ItemEffectsItemID` FOREIGN KEY (`ItemID`) REFERENCES `items` (`ID`),
1374  CONSTRAINT `FK_item_effects_effects` FOREIGN KEY (`EffectID`) REFERENCES `effects` (`ID`)
1375) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1376
1377-- L'exportation de données n'était pas sélectionnée.
1378
1379
1380-- Export de la structure de table lif_1. movable_objects
1381CREATE TABLE IF NOT EXISTS `movable_objects` (
1382  `ID` int(10) unsigned NOT NULL,
1383  `ObjectTypeID` int(10) unsigned NOT NULL,
1384  `RootContainerID` int(10) unsigned DEFAULT NULL,
1385  `TurnAngle` smallint(11) NOT NULL,
1386  `Durability` smallint(10) unsigned NOT NULL COMMENT 'Less then 10 - object is damaged',
1387  `CreatedDurability` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point, also acts as MaxDurability',
1388  `GeoDataID` int(10) unsigned NOT NULL,
1389  `Altitude` smallint(5) unsigned NOT NULL COMMENT 'Altitude in ingame decimeters',
1390  `IsComplete` tinyint(3) unsigned NOT NULL,
1391  `CarrierCharacterID` int(10) unsigned DEFAULT NULL,
1392  `OwnerID` int(10) unsigned DEFAULT NULL,
1393  `DroppedTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
1394  `OffsetMmX` smallint(6) NOT NULL COMMENT 'ingame millimeters',
1395  `OffsetMmY` smallint(6) NOT NULL COMMENT 'ingame millimeters',
1396  `OffsetMmZ` int(11) NOT NULL COMMENT 'ingame millimeters',
1397  PRIMARY KEY (`ID`),
1398  UNIQUE KEY `UNQ_CarrierCharacterID` (`CarrierCharacterID`),
1399  KEY `FK_MovableObjectsContainerID` (`RootContainerID`),
1400  KEY `FK_MovableObjectTypeID` (`ObjectTypeID`) USING BTREE,
1401  KEY `FK_ownerID` (`OwnerID`),
1402  CONSTRAINT `FK_MovableObjectsContainerID` FOREIGN KEY (`RootContainerID`) REFERENCES `containers` (`ID`),
1403  CONSTRAINT `FK_MovableObjectsObjectTypeID` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`),
1404  CONSTRAINT `FK_movable_objects_character` FOREIGN KEY (`CarrierCharacterID`) REFERENCES `character` (`ID`),
1405  CONSTRAINT `FK_ownerID` FOREIGN KEY (`OwnerID`) REFERENCES `character` (`ID`)
1406) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1407
1408-- L'exportation de données n'était pas sélectionnée.
1409
1410
1411-- Export de la structure de table lif_1. movable_objects_server_id_ranges
1412CREATE TABLE IF NOT EXISTS `movable_objects_server_id_ranges` (
1413  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1414  `ServerID` int(10) unsigned NOT NULL,
1415  `RangeStartID` int(10) unsigned NOT NULL,
1416  `RangeEndID` int(10) unsigned NOT NULL,
1417  PRIMARY KEY (`ID`),
1418  KEY `IDX_RangeEndID` (`RangeEndID`),
1419  KEY `FK_movable_objects_server_id_ranges_servers` (`ServerID`)
1420) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='movable_objects ID ranges assigned to servers. Should be accessed only by using p_issueIdRange_movable_objects';
1421
1422-- L'exportation de données n'était pas sélectionnée.
1423
1424
1425-- Export de la structure de table lif_1. movable_objects_server_id_ranges_lock
1426CREATE TABLE IF NOT EXISTS `movable_objects_server_id_ranges_lock` (
1427  `ID` tinyint(3) unsigned NOT NULL,
1428  `IsLocked` tinyint(3) unsigned NOT NULL,
1429  PRIMARY KEY (`ID`)
1430) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Dummy table for locking from p_issueIdRange_movable_objects. Do not store actual data, using only for internal needs';
1431
1432-- L'exportation de données n'était pas sélectionnée.
1433
1434
1435-- Export de la structure de table lif_1. nav_mesh_cache
1436CREATE TABLE IF NOT EXISTS `nav_mesh_cache` (
1437  `ServerID` smallint(5) unsigned NOT NULL,
1438  `FileCRC` int(10) unsigned NOT NULL,
1439  `FileSize` int(10) unsigned NOT NULL,
1440  `FileTimestamp` int(10) unsigned NOT NULL,
1441  PRIMARY KEY (`ServerID`)
1442) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1443
1444-- L'exportation de données n'était pas sélectionnée.
1445
1446
1447-- Export de la structure de table lif_1. nav_mesh_cache_ter_versions
1448CREATE TABLE IF NOT EXISTS `nav_mesh_cache_ter_versions` (
1449  `ServerID` smallint(5) unsigned NOT NULL,
1450  `TerID` int(10) unsigned NOT NULL,
1451  `ObjectsVersion` int(10) unsigned NOT NULL,
1452  `ForestVersion` int(10) unsigned NOT NULL,
1453  `GeoVersion` int(10) unsigned NOT NULL,
1454  PRIMARY KEY (`ServerID`,`TerID`),
1455  UNIQUE KEY `UNQ_TerID` (`TerID`),
1456  CONSTRAINT `FK_nav_mesh_cache_ter_versions_terrain_blocks` FOREIGN KEY (`TerID`) REFERENCES `terrain_blocks` (`ID`)
1457) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1458
1459-- L'exportation de données n'était pas sélectionnée.
1460
1461
1462-- Export de la structure de table lif_1. objects_patch
1463CREATE TABLE IF NOT EXISTS `objects_patch` (
1464  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1465  `TerID` int(10) unsigned NOT NULL,
1466  `Version` int(10) unsigned NOT NULL,
1467  `Action` tinyint(3) unsigned NOT NULL COMMENT '1=Create; 2=Delete; 3=CompleteChange; 4=RotateChange',
1468  `ObjectSuperType` tinyint(3) unsigned NOT NULL COMMENT '1=Movable; 2=Unmovable',
1469  `ObjectID` int(10) unsigned NOT NULL,
1470  `GeoDataID` int(10) unsigned NOT NULL,
1471  `ObjectTypeID` int(10) unsigned DEFAULT NULL,
1472  `TurnAngle` smallint(6) DEFAULT NULL,
1473  `Altitude` smallint(5) unsigned DEFAULT NULL,
1474  `OffsetX` smallint(6) DEFAULT NULL,
1475  `OffsetY` smallint(6) DEFAULT NULL,
1476  `OffsetZ` int(11) DEFAULT NULL,
1477  `IsComplete` tinyint(3) unsigned DEFAULT NULL,
1478  `Slope` tinyint(3) unsigned DEFAULT NULL COMMENT '0=No slope; 1=Left; 2=Right',
1479  PRIMARY KEY (`ID`),
1480  UNIQUE KEY `UNQ_TerID_Version` (`TerID`,`Version`),
1481  KEY `FK_objects_patch_terrain_blocks` (`TerID`),
1482  KEY `FK_objects_patch_objects_types` (`ObjectTypeID`),
1483  CONSTRAINT `FK_objects_patch_objects_types` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`),
1484  CONSTRAINT `FK_objects_patch_terrain_blocks` FOREIGN KEY (`TerID`) REFERENCES `terrain_blocks` (`ID`)
1485) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1486
1487-- L'exportation de données n'était pas sélectionnée.
1488
1489
1490-- Export de la structure de table lif_1. objects_types
1491CREATE TABLE IF NOT EXISTS `objects_types` (
1492  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1493  `ParentID` int(10) unsigned DEFAULT NULL,
1494  `Name` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
1495  `IsContainer` tinyint(1) NOT NULL DEFAULT '0',
1496  `IsMovableObject` tinyint(1) NOT NULL DEFAULT '0',
1497  `IsUnmovableobject` tinyint(1) NOT NULL DEFAULT '0',
1498  `IsTool` tinyint(1) NOT NULL DEFAULT '0',
1499  `IsDevice` tinyint(1) NOT NULL DEFAULT '0',
1500  `IsDoor` tinyint(1) NOT NULL DEFAULT '0',
1501  `MaxContSize` int(10) unsigned DEFAULT NULL COMMENT '3 digits after point (gramm)',
1502  `Length` tinyint(10) unsigned DEFAULT NULL,
1503  `MaxStackSize` int(10) unsigned DEFAULT NULL COMMENT 'For unmovable objects stores max amount of bind slots for players',
1504  `UnitWeight` int(10) unsigned DEFAULT NULL COMMENT '3 digits after point (gramm)',
1505  `BackgndImage` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
1506  `WorkAreaTop` smallint(5) unsigned DEFAULT NULL COMMENT 'obsolete',
1507  `WorkAreaLeft` smallint(5) unsigned DEFAULT NULL COMMENT 'obsolete',
1508  `WorkAreaWidth` smallint(5) unsigned DEFAULT NULL COMMENT 'obsolete',
1509  `WorkAreaHeight` smallint(5) unsigned DEFAULT NULL COMMENT 'obsolete',
1510  `BtnCloseTop` smallint(5) unsigned DEFAULT NULL COMMENT 'obsolete',
1511  `BtnCloseLeft` smallint(5) unsigned DEFAULT NULL COMMENT 'obsolete',
1512  `FaceImage` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
1513  `Description` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
1514  `BasePrice` int(10) unsigned DEFAULT NULL COMMENT 'BasePrice for Q=50 item in 0.01*copper coins',
1515  `OwnerTimeout` int(10) unsigned DEFAULT NULL COMMENT 'Timeout in seconds, when ownership keeping to dropper',
1516  PRIMARY KEY (`ID`),
1517  KEY `FKObjectsTypesParentID` (`ParentID`),
1518  CONSTRAINT `FKObjectsTypesParentID` FOREIGN KEY (`ParentID`) REFERENCES `objects_types` (`ID`)
1519) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1520
1521-- L'exportation de données n'était pas sélectionnée.
1522
1523
1524-- Export de la structure de table lif_1. personal_lands
1525CREATE TABLE IF NOT EXISTS `personal_lands` (
1526  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
1527  `CharID` int(10) unsigned NOT NULL,
1528  `Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
1529  `GeoID1` int(10) unsigned NOT NULL,
1530  `GeoID2` int(10) unsigned NOT NULL,
1531  `IsTemp` tinyint(3) unsigned NOT NULL DEFAULT '0',
1532  PRIMARY KEY (`ID`),
1533  KEY `FK_guild_lands_character` (`CharID`),
1534  CONSTRAINT `FK_guild_lands_character` FOREIGN KEY (`CharID`) REFERENCES `character` (`ID`)
1535) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1536
1537-- L'exportation de données n'était pas sélectionnée.
1538
1539
1540-- Export de la structure de procédure lif_1. p_addClaimSubject_char
1541DELIMITER //
1542CREATE DEFINER=`root`@`localhost` PROCEDURE `p_addClaimSubject_char`(
1543	IN `inClaimID` INT UNSIGNED,
1544	IN `inCharID` INT UNSIGNED,
1545	IN `inCanEnter` TINYINT UNSIGNED,
1546	IN `inCanBuild` TINYINT UNSIGNED,
1547	IN `inCanClaim` TINYINT UNSIGNED,
1548	IN `inCanUse` TINYINT UNSIGNED,
1549	IN `inCanDestroy` TINYINT UNSIGNED
1550)
1551BEGIN
1552	declare subjID INT UNSIGNED default NULL;
1553
1554	select ID from claim_subjects where CharID = inCharID into subjID;
1555	if(subjID is NULL) then
1556		insert into claim_subjects set CharID = inCharID;
1557		set subjID = LAST_INSERT_ID();
1558	end if;
1559
1560	insert into claim_rules
1561		(`ClaimID`, `ClaimSubjectID`, `CanEnter`, `CanBuild`, `CanClaim`, `CanUse`, `CanDestroy`)
1562		values
1563		(inClaimID, subjID,           inCanEnter, inCanBuild, inCanClaim, inCanUse, inCanDestroy);
1564END//
1565DELIMITER ;
1566
1567
1568-- Export de la structure de procédure lif_1. p_addHeraldry
1569DELIMITER //
1570CREATE DEFINER=`root`@`localhost` PROCEDURE `p_addHeraldry`(
1571	`in_BackgroundIndex` TINYINT UNSIGNED,
1572	`in_BackgroundColorIndex1` TINYINT UNSIGNED,
1573	`in_BackgroundColorIndex2` TINYINT UNSIGNED,
1574
1575	`inCharge1_isValid` TINYINT UNSIGNED,
1576	`inCharge1_SymbolIndex` TINYINT UNSIGNED,
1577	`inCharge1_ColorIndex` TINYINT UNSIGNED,
1578	`inCharge1_Position` VARCHAR(64),
1579	`inCharge1_Size` VARCHAR(64),
1580
1581	`inCharge2_isValid` TINYINT UNSIGNED,
1582	`inCharge2_SymbolIndex` TINYINT UNSIGNED,
1583	`inCharge2_ColorIndex` TINYINT UNSIGNED,
1584	`inCharge2_Position` VARCHAR(64),
1585	`inCharge2_Size` VARCHAR(64)
1586)
1587    COMMENT 'Return NULL when error occured, 0 when heraldry duplicate found, new heraldries.ID otherwise.'
1588BEGIN
1589	declare newChargeID1, newChargeID2 int unsigned default null;
1590	declare newHeraldryID int unsigned default 0;
1591	declare duplicateHeraldryID int unsigned default null;
1592
1593	declare exit handler for sqlexception
1594	begin
1595		rollback;
1596		resignal;
1597	end;
1598
1599	start transaction;
1600
1601	-- try to find existing charges at first
1602	if(inCharge1_isValid > 0) then
1603		set newChargeID1 = (select ID
1604			from heraldic_charges
1605			where SymbolIndex = inCharge1_SymbolIndex
1606				and ColorIndex = inCharge1_ColorIndex
1607				and Position = inCharge1_Position
1608				and Size = inCharge1_Size
1609			limit 1);
1610
1611		if(newChargeID1 is null) then
1612			-- insert new charge1
1613			insert into heraldic_charges
1614				(SymbolIndex, ColorIndex, Position, Size)
1615				values (inCharge1_SymbolIndex, inCharge1_ColorIndex, inCharge1_Position, inCharge1_Size);
1616			set newChargeID1 = LAST_INSERT_ID();
1617		end if;
1618	end if;
1619
1620	if(inCharge2_isValid > 0) then
1621		set newChargeID2 = (select ID
1622			from heraldic_charges
1623			where SymbolIndex = inCharge2_SymbolIndex
1624				and ColorIndex = inCharge2_ColorIndex
1625				and Position = inCharge2_Position
1626				and Size = inCharge2_Size);
1627
1628		if(newChargeID2 is null) then
1629			-- insert new charge2
1630			insert into heraldic_charges
1631				(SymbolIndex, ColorIndex, Position, Size)
1632				values (inCharge2_SymbolIndex, inCharge2_ColorIndex, inCharge2_Position, inCharge2_Size);
1633			set newChargeID2 = LAST_INSERT_ID();
1634		end if;
1635	end if;
1636
1637	-- check if charges are not the same
1638	if((newChargeID1 != newChargeID2) or (newChargeID1 is null or newChargeID2 is null)) then
1639		-- check heraldry uniqueness with all charges variations
1640		if(newChargeID1 is null and newChargeID2 is null) then
1641			-- when both charges is not set
1642			set duplicateHeraldryID = (
1643				select ID from heraldries
1644				where BackgroundIndex = in_BackgroundIndex
1645					and BackgroundColorIndex1 = in_BackgroundColorIndex1
1646					and BackgroundColorIndex2 = in_BackgroundColorIndex2
1647					and ChargeID1 is null
1648					and ChargeID2 is null
1649				limit 1
1650			);
1651		elseif(newChargeID1 is null or newChargeID2 is null) then
1652			-- when one charge is not set
1653			set duplicateHeraldryID = (
1654				select ID from heraldries
1655				where BackgroundIndex = in_BackgroundIndex
1656					and BackgroundColorIndex1 = in_BackgroundColorIndex1
1657					and BackgroundColorIndex2 = in_BackgroundColorIndex2
1658					and
1659					(
1660						(ChargeID1 is null and (ChargeID2 = newChargeID1 or ChargeID2 = newChargeID2))
1661						or
1662						(ChargeID2 is null and (ChargeID1 = newChargeID1 or ChargeID1 = newChargeID2))
1663					)
1664				limit 1
1665			);
1666		else
1667			-- when both charges are set
1668			set duplicateHeraldryID = (
1669				select ID from heraldries
1670				where BackgroundIndex = in_BackgroundIndex
1671					and BackgroundColorIndex1 = in_BackgroundColorIndex1
1672					and BackgroundColorIndex2 = in_BackgroundColorIndex2
1673					and
1674					(
1675						(ChargeID1 = newChargeID1 and ChargeID2 = newChargeID2)
1676						or
1677						(ChargeID1 = newChargeID2 and ChargeID2 = newChargeID1)
1678					)
1679				limit 1
1680			);
1681		end if;
1682
1683		if(duplicateHeraldryID is null) then
1684			insert into heraldries
1685				(BackgroundIndex, BackgroundColorIndex1, BackgroundColorIndex2, ChargeID1, ChargeID2)
1686				values (in_BackgroundIndex, in_BackgroundColorIndex1, in_BackgroundColorIndex2, newChargeID1, newChargeID2);
1687
1688			set newHeraldryID = LAST_INSERT_ID();
1689		end if;
1690	end if;
1691
1692	if(newHeraldryID > 0) then
1693		commit;
1694	else
1695		rollback;
1696	end if;
1697
1698  select newHeraldryID as `ID`, ifnull(duplicateHeraldryID, 0) as `DuplicateHeraldryID`;
1699END//
1700DELIMITER ;
1701
1702
1703-- Export de la structure de procédure lif_1. p_addObjectPatch
1704DELIMITER //
1705CREATE DEFINER=`root`@`localhost` PROCEDURE `p_addObjectPatch`(
1706	`in_ChangeID` INT UNSIGNED,
1707	`in_TerID` INT UNSIGNED,
1708	`in_Action` TINYINT UNSIGNED,
1709	`in_ObjectSuperType` TINYINT UNSIGNED,
1710	`in_ObjectID` INT UNSIGNED,
1711	`in_GeoDataID` INT UNSIGNED,
1712	`in_ObjectTypeID` INT UNSIGNED,
1713	`in_TurnAngle` SMALLINT,
1714	`in_Altitude` SMALLINT UNSIGNED,
1715	`in_OffsetX` SMALLINT,
1716	`in_OffsetY` SMALLINT,
1717	`in_OffsetZ` INT,
1718	`in_IsComplete` TINYINT(1) UNSIGNED,
1719	`in_Slope` TINYINT UNSIGNED
1720)
1721    MODIFIES SQL DATA
1722BEGIN
1723	
1724/*
1725	declare versionID, dummyTerID INT UNSIGNED default NULL;
1726
1727	-- get current objects version for this terrain
1728	select ObjectsVersion, ID from terrain_blocks
1729		where ID = in_TerID limit 1
1730		into versionID, dummyTerID
1731		for update;
1732	
1733	-- check terrain
1734	if(dummyTerID is null) then
1735		return NULL;
1736	end if;
1737	
1738	-- increment version
1739	set versionID = (ifnull(versionID, 0) + 1);
1740*/
1741
1742	-- make changes
1743	insert into objects_patch
1744		(       `TerID`,   `Version`,  `Action`,  `ObjectSuperType`,  `ObjectID`,  `GeoDataID`,  `ObjectTypeID`,  `TurnAngle`,  `Altitude`,  `OffsetX`,  `OffsetY`,  `OffsetZ`,  `IsComplete`,  `Slope`)
1745		values(in_TerID, in_ChangeID, in_Action, in_ObjectSuperType, in_ObjectID, in_GeoDataID, in_ObjectTypeID, in_TurnAngle, in_Altitude, in_OffsetX, in_OffsetY, in_OffsetZ, in_IsComplete, in_Slope);
1746	
1747	update terrain_blocks
1748		set ObjectsVersion = in_ChangeID
1749		where ID = in_TerID and ObjectsVersion < in_ChangeID; -- do not downgrade version
1750
1751/*
1752	return versionID;
1753*/
1754END//
1755DELIMITER ;
1756
1757
1758-- Export de la structure de procédure lif_1. p_allocate_character_wounds
1759DELIMITER //
1760CREATE DEFINER=`root`@`localhost` PROCEDURE `p_allocate_character_wounds`(
1761	`in_charID` INT UNSIGNED
1762)
1763BEGIN
1764
1765	insert ignore character_wounds (CharacterID, BodyPart, WoundType)
1766		select in_charID, t.BodyPart, t.WoundType
1767		from (
1768			select 0 as BodyPart, 0 as WoundType union all
1769			select 0, 1 union all
1770			select 0, 2 union all
1771			select 0, 3 union all
1772			select 0, 5 union all
1773			select 1, 0 union all
1774			select 1, 1 union all
1775			select 1, 2 union all
1776			select 1, 3 union all
1777			select 1, 5 union all
1778			select 2, 0 union all
1779			select 2, 1 union all
1780			select 2, 2 union all
1781			select 2, 3 union all
1782			select 2, 5 union all
1783			select 3, 0 union all
1784			select 3, 1 union all
1785			select 3, 2 union all
1786			select 3, 3 union all
1787			select 3, 5 union all
1788			select 4, 0 union all
1789			select 4, 1 union all
1790			select 4, 2 union all
1791			select 4, 3 union all
1792			select 4, 5 union all
1793			select 5, 0 union all
1794			select 5, 1 union all
1795			select 5, 2 union all
1796			select 5, 3 union all
1797			select 5, 5 union all
1798			select 6, 0
1799		) as t;
1800
1801END//
1802DELIMITER ;
1803
1804
1805-- Export de la structure de procédure lif_1. p_allocate_equipment_slots
1806DELIMITER //
1807CREATE DEFINER=`root`@`localhost` PROCEDURE `p_allocate_equipment_slots`(
1808	`in_charID` INT UNSIGNED
1809)
1810BEGIN
1811
1812	insert ignore equipment_slots (CharacterID, Slot)
1813		select in_charID, t.slot
1814		from (
1815			select 1 as slot union all
1816			select 2 union all
1817			select 3 union all
1818			select 4 union all
1819			select 5 union all
1820			select 6 union all
1821			select 7 union all
1822			select 8 union all
1823			select 9 union all
1824			select 10 union all
1825			select 11 union all
1826			select 12 union all
1827			select 13 union all
1828			select 14 union all
1829			select 15 union all
1830			select 16
1831		) as t;
1832
1833END//
1834DELIMITER ;
1835
1836
1837-- Export de la structure de procédure lif_1. p_changeGuildLeader
1838DELIMITER //
1839CREATE DEFINER=`root`@`localhost` PROCEDURE `p_changeGuildLeader`(
1840	IN `inGuildID` INT UNSIGNED,
1841	IN `inNewLeaderID` INT UNSIGNED
1842)
1843BEGIN
1844	update `character` set GuildID = inGuildID, GuildRoleID = 1/*Leader*/ where ID = inNewLeaderID;
1845	update `character` set GuildRoleID = 6/*Normal member*/ where GuildID = inGuildID and ID != inNewLeaderID;
1846END//
1847DELIMITER ;
1848
1849
1850-- Export de la structure de procédure lif_1. p_compactForestPatches
1851DELIMITER //
1852CREATE DEFINER=`root`@`localhost` PROCEDURE `p_compactForestPatches`()
1853BEGIN
1854	delete from forest_patch;
1855	update terrain_blocks set ForestVersion = default;
1856
1857	-- insert each tree as 'Add' change
1858	insert into forest_patch
1859		(`TerID`, `Version`, `Action`, `GeoDataID`, `SubcellMask`, `TreeType`, `TreeHealth`, `AddTime`)
1860		select f_terIDFromGeoID(f.GeoDataID) as terID,
1861			0, -- temporary version. We will update it later
1862			1, -- Action == Add
1863			f.GeoDataID,
1864			f.SubcellMask,
1865			f.TreeType,
1866			f_treeQualityToTreeHealth(f.Quality),
1867			f.AgeTime
1868		from forest f
1869		order by TerID, ID;
1870
1871	-- use user-defined variables to ranking forest versions grouped by TerID
1872	set @currTerID = 0;
1873	set @forestVer = 1;
1874
1875	update forest_patch fp
1876		join
1877		(
1878			-- use 'least()' workaround to reset both @currTerID and @forestVer variables when we moved to next terID while iterating over ordered patches
1879			select ID, (@forestVer := if(@currTerID != TerID, least(@currTerID := TerID, 1), @forestVer) + 1) as ver
1880			from forest_patch
1881			order by TerID, ID
1882		) as fp_ver on fp_ver.ID = fp.ID
1883		set Version = fp_ver.ver;
1884
1885	set @currTerID = 0;
1886	set @forestVer = 0;
1887
1888	update terrain_blocks tb set tb.ForestVersion = (select ifnull(max(fp.`Version`), default(tb.ForestVersion)) from forest_patch fp where TerID = tb.ID);
1889END//
1890DELIMITER ;
1891
1892
1893-- Export de la structure de procédure lif_1. p_completeBuilding
1894DELIMITER //
1895CREATE DEFINER=`root`@`localhost` PROCEDURE `p_completeBuilding`(
1896	IN `in_isMovable` TINYINT UNSIGNED,
1897	IN `in_objectID` INT UNSIGNED,
1898	IN `in_conteinerID` INT UNSIGNED,
1899	IN `in_CreatedDurability` SMALLINT UNSIGNED,
1900	IN `in_Durability` SMALLINT UNSIGNED,
1901	IN `in_isRecreateContainer` TINYINT UNSIGNED
1902)
1903BEGIN
1904	declare is_container, type_id, new_container_id INT UNSIGNED default NULL;
1905	declare container_name varchar(45) default NULL;
1906
1907	if(in_isMovable = 1) then
1908		SELECT ObjectTypeID FROM movable_objects WHERE ID = in_objectID LIMIT 1 INTO type_id FOR UPDATE;
1909	else
1910		SELECT ObjectTypeID FROM unmovable_objects WHERE ID = in_objectID LIMIT 1 INTO type_id FOR UPDATE;
1911	end if;
1912
1913	SELECT IsContainer, Name FROM objects_types WHERE ID = type_id LIMIT 1 INTO is_container, container_name;
1914
1915	if(is_container > 0 AND in_isRecreateContainer = 1) then
1916		SET new_container_id = f_createRootContainer(container_name);
1917	end if;
1918
1919	if(in_isMovable = 1) then
1920		if (in_isRecreateContainer = 1) then
1921			UPDATE movable_objects SET
1922				IsComplete =1,
1923				RootContainerID = new_container_id,
1924				Durability =in_Durability,
1925				CreatedDurability =in_CreatedDurability
1926			WHERE ID = in_objectID;
1927		else
1928			UPDATE movable_objects SET
1929				IsComplete =1,
1930				Durability =in_Durability,
1931				CreatedDurability =in_CreatedDurability
1932			WHERE ID = in_objectID;
1933		end if;
1934	else
1935		if (in_isRecreateContainer = 1) then
1936			UPDATE unmovable_objects SET
1937				IsComplete =1,
1938				RootContainerID = new_container_id,
1939				Durability =in_Durability,
1940				CreatedDurability =in_CreatedDurability
1941			WHERE ID = in_objectID;
1942		else
1943			UPDATE unmovable_objects SET
1944				IsComplete =1,
1945				Durability =in_Durability,
1946				CreatedDurability =in_CreatedDurability
1947			WHERE ID = in_objectID;
1948		end if;
1949	end if;
1950
1951	if(in_conteinerID > 0 AND in_isRecreateContainer = 1) then
1952  	   CALL f_deleteContainer(in_conteinerID);
1953	end if;
1954END//
1955DELIMITER ;
1956
1957
1958-- Export de la structure de procédure lif_1. p_createHorseObject
1959DELIMITER //
1960CREATE DEFINER=`root`@`localhost` PROCEDURE `p_createHorseObject`(
1961	`in_ID` INT UNSIGNED,
1962	`in_TypeID` INT UNSIGNED,
1963	`in_Quality` TINYINT UNSIGNED,
1964	`in_HP` INT,
1965	`in_GeoID` INT UNSIGNED,
1966	`in_Altitude` SMALLINT UNSIGNED,
1967	`in_OffsetX` SMALLINT,
1968	`in_OffsetY` SMALLINT,
1969	`in_OffsetZ` INT,
1970	`in_TurnAngle` SMALLINT,
1971	`in_MountedCharacterID` INT UNSIGNED,
1972	`in_OwnerID` INT UNSIGNED,
1973	`inCreatedDurability` SMALLINT UNSIGNED,
1974	`inDurability` SMALLINT UNSIGNED
1975)
1976    MODIFIES SQL DATA
1977BEGIN
1978	insert into horses
1979		(          ID, ObjectTypeID, Quality,    HP,    GeoID,    GeoAlt,      OffsetX,    OffsetY,    OffsetZ,    TurnAngle,    MountedCharacterID,    OwnerID,    CreatedDurability,   Durability  )
1980		values (in_ID,    in_TypeID, in_Quality, in_HP, in_GeoID, in_Altitude, in_OffsetX, in_OffsetY, in_OffsetZ, in_TurnAngle, in_MountedCharacterID, in_OwnerID, inCreatedDurability, inDurability);
1981END//
1982DELIMITER ;
1983
1984
1985-- Export de la structure de procédure lif_1. p_createMovableObject
1986DELIMITER //
1987CREATE DEFINER=`root`@`localhost` PROCEDURE `p_createMovableObject`(
1988	`in_ID` INT UNSIGNED,
1989	`in_TypeID` INT UNSIGNED,
1990	`in_RotateAngle` SMALLINT,
1991	`in_GeoID` INT UNSIGNED,
1992	`in_Altitude` SMALLINT UNSIGNED,
1993	`in_OffsetX` SMALLINT,
1994	`in_OffsetY` SMALLINT,
1995	`in_OffsetZ` INT,
1996	`in_CreatedDurability` SMALLINT UNSIGNED,
1997	`in_Durability` SMALLINT UNSIGNED,
1998	`in_IsComplete` TINYINT(1),
1999	`in_OwnerID` INT UNSIGNED,
2000	`in_isAlwaysCreateCompleteContainer` TINYINT
2001)
2002    MODIFIES SQL DATA
2003BEGIN
2004	declare is_container, containerObjectID, containerTypeID INT UNSIGNED default NULL;
2005	declare containerName varchar(45) default NULL;
2006
2007	if( in_IsComplete || in_isAlwaysCreateCompleteContainer) then
2008		select IsContainer, Name, ID from objects_types where ID = in_TypeID limit 1
2009			into is_container, containerName, containerTypeID;
2010		if( is_container) then
2011			set containerObjectID =f_createRootContainer( containerName);
2012		end if;
2013	else
2014		set containerObjectID =f_createRootContainer( 'object_inventory');
2015	end if;
2016
2017	insert into movable_objects
2018		(          ID, ObjectTypeID,   RootContainerID,      TurnAngle, GeoDataID,    Altitude,  OffsetMmX,  OffsetMmY,  OffsetMmZ,    CreatedDurability,    Durability,    IsComplete,    OwnerID)
2019		values (in_ID,    in_TypeID, containerObjectID, in_RotateAngle,  in_GeoID, in_Altitude, in_OffsetX, in_OffsetY, in_OffsetZ, in_CreatedDurability, in_Durability, in_IsComplete, in_OwnerID);
2020
2021	-- set objID = LAST_INSERT_ID();
2022	-- update geo_data set MovableObjectsCount = MovableObjectsCount + 1 where ID =in_GeoID;
2023	-- return objID;
2024END//
2025DELIMITER ;
2026
2027
2028-- Export de la structure de procédure lif_1. p_createUnmovableObject
2029DELIMITER //
2030CREATE DEFINER=`root`@`localhost` PROCEDURE `p_createUnmovableObject`(
2031	in_ID INT UNSIGNED,
2032	in_typeID INT UNSIGNED,
2033	in_variant TINYINT UNSIGNED,
2034	in_rotateAngle SMALLINT,
2035	in_geoID INT UNSIGNED,
2036	in_durability SMALLINT UNSIGNED,
2037	in_isComplete TINYINT UNSIGNED
2038)
2039    MODIFIES SQL DATA
2040BEGIN
2041
2042	declare is_container, containerObjectID, containerTypeID INT UNSIGNED default NULL;
2043	declare containerName varchar(45) default NULL;
2044
2045	if(in_isComplete) then
2046		select IsContainer, Name, ID from objects_types where ID = in_TypeID limit 1
2047			into is_container, containerName, containerTypeID;
2048		if( is_container) then
2049			set containerObjectID = f_createRootContainer( containerName);
2050		end if;
2051	else
2052		set containerObjectID = f_createRootContainer('object_inventory');
2053	end if;
2054
2055	insert into unmovable_objects
2056		(ID, ObjectTypeID,     Slope,      GeoDataID, TurnAngle,      RootContainerID,   Durability,    IsComplete)
2057		values (in_ID, in_typeID, in_variant, in_geoID,  in_rotateAngle, containerObjectID, in_durability, in_isComplete);
2058
2059END//
2060DELIMITER ;
2061
2062
2063-- Export de la structure de procédure lif_1. p_dbg_checkIdRangeConsistant_unmovable_objects
2064DELIMITER //
2065CREATE DEFINER=`root`@`localhost` PROCEDURE `p_dbg_checkIdRangeConsistant_unmovable_objects`()
2066    COMMENT 'Check consistant of content of unmovable_objects_server_id_ranges table. OK when no rows returned, else shows range intersections'
2067BEGIN
2068
2069	select * from `unmovable_objects_server_id_ranges` r1
2070	where exists (
2071		select * from `unmovable_objects_server_id_ranges` r2 where r2.ID != r1.ID
2072		and (
2073			(r1.RangeEndID between r2.RangeStartID and r2.RangeEndID) or
2074			(r1.RangeStartID between r2.RangeStartID and r2.RangeEndID) or
2075			(r1.RangeStartID <= r2.RangeStartID and r1.RangeEndID >= r2.RangeEndID)
2076		)
2077	);
2078
2079END//
2080DELIMITER ;
2081
2082
2083-- Export de la structure de procédure lif_1. p_dbg_checkIdRangeConsistent_horses
2084DELIMITER //
2085CREATE DEFINER=`root`@`localhost` PROCEDURE `p_dbg_checkIdRangeConsistent_horses`()
2086    COMMENT 'Check consistent of content of horses_server_id_ranges table. OK when no rows returned, else shows range intersections'
2087BEGIN
2088
2089select * from `horses_server_id_ranges` r1
2090where exists (
2091	select * from `horses_server_id_ranges` r2 where r2.ID != r1.ID
2092	and (
2093		(r1.RangeEndID between r2.RangeStartID and r2.RangeEndID) or
2094		(r1.RangeStartID between r2.RangeStartID and r2.RangeEndID) or
2095		(r1.RangeStartID <= r2.RangeStartID and r1.RangeEndID >= r2.RangeEndID)
2096	)
2097);
2098
2099END//
2100DELIMITER ;
2101
2102
2103-- Export de la structure de procédure lif_1. p_dbg_checkIdRangeConsistent_movable_objects
2104DELIMITER //
2105CREATE DEFINER=`root`@`localhost` PROCEDURE `p_dbg_checkIdRangeConsistent_movable_objects`()
2106    COMMENT 'Check consistent of content of movable_objects_server_id_ranges table. OK when no rows returned, else shows range intersections'
2107BEGIN
2108
2109	select * from `movable_objects_server_id_ranges` r1
2110	where exists (
2111		select * from `movable_objects_server_id_ranges` r2 where r2.ID != r1.ID
2112		and (
2113			(r1.RangeEndID between r2.RangeStartID and r2.RangeEndID) or
2114			(r1.RangeStartID between r2.RangeStartID and r2.RangeEndID) or
2115			(r1.RangeStartID <= r2.RangeStartID and r1.RangeEndID >= r2.RangeEndID)
2116		)
2117	);
2118
2119END//
2120DELIMITER ;
2121
2122
2123-- Export de la structure de procédure lif_1. p_deleteBlueprint
2124DELIMITER //
2125CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteBlueprint`(
2126	`in_blueprintID` INT UNSIGNED
2127)
2128    MODIFIES SQL DATA
2129BEGIN
2130	UPDATE `features` SET `BlueprintID`=NULL WHERE `BlueprintID`=in_blueprintID;
2131	DELETE FROM `character_blueprints` WHERE BlueprintID=in_blueprintID;
2132	DELETE FROM `blueprint_requirements` WHERE BlueprintID=in_blueprintID;
2133	DELETE FROM `blueprints` WHERE ID=in_blueprintID;
2134END//
2135DELIMITER ;
2136
2137
2138-- Export de la structure de procédure lif_1. p_deleteCharacter
2139DELIMITER //
2140CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteCharacter`(
2141	IN `inCharID` INT UNSIGNED,
2142	IN `inAccID` INT UNSIGNED
2143)
2144BEGIN
2145	declare eqContID, invContID, delCharacterID int unsigned default null;
2146	declare isCharFound tinyint unsigned default 0; -- not found by default
2147
2148	declare exit handler for sqlexception
2149	begin
2150		rollback;
2151		resignal;
2152	end;
2153
2154  select EquipmentContainerID, RootContainerID from `character` where id = inCharID and AccountID = inAccID
2155		into eqContID, invContID;
2156
2157	if(eqContID is not null and invContID is not null) then
2158		set isCharFound = 1; -- found
2159
2160	  START TRANSACTION;
2161
2162		DELETE FROM skills WHERE CharacterID = inCharID;
2163		DELETE FROM equipment_slots WHERE CharacterID = inCharID;
2164		DELETE FROM character_wounds WHERE CharacterID = inCharID;
2165		DELETE FROM character_titles WHERE CharacterID = inCharID;
2166		DELETE FROM character_effects WHERE CharacterID = inCharID;
2167		-- DELETE FROM guest_links WHERE characterId = inCharID;
2168
2169		-- personal_lands -> claims -> (claim_rules, unmovable_objects_claims)
2170		DELETE FROM claim_rules              WHERE ClaimID IN (SELECT ID FROM claims WHERE PersonalLandID IN (SELECT ID FROM personal_lands WHERE CharID = inCharID));
2171		DELETE FROM unmovable_objects_claims WHERE ClaimID IN (SELECT ID FROM claims WHERE PersonalLandID IN (SELECT ID FROM personal_lands WHERE CharID = inCharID));
2172		DELETE FROM claims WHERE PersonalLandID IN (SELECT ID FROM personal_lands WHERE CharID = inCharID);
2173		DELETE FROM personal_lands WHERE CharID = inCharID;
2174
2175		-- claim_subjects -> (claim_rules, claim_rules_unmovable)
2176		DELETE FROM claim_rules           WHERE ClaimSubjectID IN (SELECT ID FROM claim_subjects WHERE CharID = inCharID);
2177		DELETE FROM claim_rules_unmovable WHERE ClaimSubjectID IN (SELECT ID FROM claim_subjects WHERE CharID = inCharID);
2178		DELETE FROM claim_subjects WHERE CharID = inCharID;
2179
2180		-- DELETE FROM minigame_results WHERE characterID = inCharID;
2181		DELETE FROM food_eaten WHERE CharID = inCharID;
2182		-- DELETE FROM guild_actions WHERE CharID = inCharID;
2183		-- DELETE FROM guild_actions WHERE ProducerCharID = inCharID;
2184		DELETE FROM chars_deathlog WHERE CharID = inCharID;
2185		DELETE FROM chars_deathlog WHERE KillerID = inCharID;
2186		DELETE FROM skill_raise_logs WHERE PlayerID = inCharID;
2187
2188		UPDATE movable_objects SET CarrierCharacterID = NULL WHERE CarrierCharacterID = inCharID;
2189		UPDATE movable_objects SET OwnerID = NULL, DroppedTime = 0 WHERE OwnerID = inCharID;
2190		UPDATE unmovable_objects SET OwnerID = NULL, DroppedTime = 0 WHERE OwnerID = inCharID;
2191		-- _cm_old_cmLocks
2192		-- UPDATE geo_data SET PrivatePropertyPlayerID=0 WHERE PrivatePropertyPlayerID = inCharID;
2193		-- /_cm_old_cmLocks
2194
2195		-- clear guild_actions_queue data
2196		insert into deleted_character_info
2197			(ExCharID, CharName, CharLastName)
2198			select ID, Name, LastName from `character` WHERE ID = inCharID;
2199		set delCharacterID = LAST_INSERT_ID();
2200
2201		DELETE FROM guild_actions_queue where ProducerCharID = inCharID or CharID = inCharID;
2202		UPDATE guild_actions_processed set ProducerCharDeletedID = delCharacterID, ProducerCharID = NULL where ProducerCharID = inCharID;
2203		UPDATE guild_actions_processed set CharDeletedID = delCharacterID, CharID = NULL where CharID = inCharID;
2204		
2205		-- blueprints (use temporary table to hold all blueprints related to inCharID)
2206		drop temporary table if exists `tmp_character_blueprints`;
2207		create temporary table `tmp_character_blueprints` (
2208			BlueprintID int unsigned not null
2209		) engine=memory;
2210
2211		UPDATE `features` SET `BlueprintID` = NULL WHERE BlueprintID IN (SELECT BlueprintID FROM `character_blueprints` WHERE CharID = inCharID);
2212		DELETE FROM `blueprint_requirements` WHERE BlueprintID IN (SELECT BlueprintID FROM `character_blueprints` WHERE CharID = inCharID);
2213		INSERT INTO `tmp_character_blueprints` (BlueprintID)
2214			SELECT BlueprintID FROM `character_blueprints` WHERE CharID = inCharID;
2215		DELETE FROM `character_blueprints` WHERE CharID = inCharID;
2216		DELETE FROM `blueprints` WHERE ID IN (SELECT BlueprintID FROM `tmp_character_blueprints`);
2217		
2218		drop temporary table `tmp_character_blueprints`;
2219
2220		-- char itself
2221		DELETE FROM `character` WHERE ID = inCharID;
2222
2223		CALL f_deleteContainer(eqContID);
2224		CALL f_deleteContainer(invContID);
2225
2226		COMMIT;
2227	end if;
2228
2229	select isCharFound as `found`;
2230
2231END//
2232DELIMITER ;
2233
2234
2235-- Export de la structure de procédure lif_1. p_deleteClaim
2236DELIMITER //
2237CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteClaim`(
2238	IN `inClaimID` INT UNSIGNED
2239)
2240BEGIN
2241	declare delGuildLandID, delPersonalLandID int unsigned default NULL;
2242	declare exit handler for sqlexception
2243	begin
2244		rollback;
2245		resignal;
2246	end;
2247
2248	start transaction;
2249
2250	delete from claim_rules where ClaimID in (select ID from claims where ID = inClaimID);
2251	delete from claim_rules_unmovable where UnmovableClaimID in (select ID from unmovable_objects_claims where ClaimID in (select ID from claims where ID = inClaimID));
2252	delete from unmovable_objects_claims where ClaimID in (select ID from claims where ID = inClaimID);
2253
2254	select GuildLandID, PersonalLandID from claims where ID = inClaimID
2255		into delGuildLandID, delPersonalLandID;
2256	delete from claims where ID = inClaimID;
2257
2258	if(delGuildLandID is not NULL) then
2259		delete from guild_lands where ID = delGuildLandID;
2260	end if;
2261	if(delPersonalLandID is not NULL) then
2262		delete from personal_lands where ID = delPersonalLandID;
2263	end if;
2264
2265	-- cleanup claim_subjects
2266	delete from claim_subjects where !exists(select * from claim_rules where ClaimSubjectID = claim_subjects.ID) and
2267		!exists(select * from claim_rules_unmovable where ClaimSubjectID = claim_subjects.ID);
2268
2269	commit;
2270END//
2271DELIMITER ;
2272
2273
2274-- Export de la structure de procédure lif_1. p_deleteClaimSubject_char
2275DELIMITER //
2276CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteClaimSubject_char`(
2277	`inClaimID` INT UNSIGNED,
2278	`inCharID` INT UNSIGNED
2279)
2280BEGIN
2281	declare subjID INT UNSIGNED default NULL;
2282	select ID from claim_subjects where CharID = inCharID into subjID;
2283
2284	if(subjID is not NULL) then
2285		delete from claim_rules where ClaimID = inClaimID and ClaimSubjectID = subjID;
2286
2287		if(!exists(select * from claim_rules where ClaimSubjectID = subjID) and
2288			!exists(select * from claim_rules_unmovable where ClaimSubjectID = subjID)
2289		) then
2290			delete from claim_subjects where CharID = inCharID;
2291		end if;
2292	end if;
2293END//
2294DELIMITER ;
2295
2296
2297-- Export de la structure de procédure lif_1. p_deleteForestItem
2298DELIMITER //
2299CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteForestItem`(
2300	in_geoID INT UNSIGNED
2301)
2302    MODIFIES SQL DATA
2303BEGIN
2304
2305	delete from forest where GeoDataID = in_geoID;
2306
2307/*
2308	-- remove tree lock
2309	update geo_data
2310		set SubCellsObjectMask = (SubCellsObjectMask & ~1)
2311		where ID = in_geoID;
2312*/
2313END//
2314DELIMITER ;
2315
2316
2317-- Export de la structure de procédure lif_1. p_deleteGuild
2318DELIMITER //
2319CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteGuild`(
2320	IN `inGuildID` INT UNSIGNED
2321)
2322BEGIN
2323	declare delGuildID int unsigned default NULL;
2324
2325	declare exit handler for sqlexception
2326	begin
2327		rollback;
2328		resignal;
2329	end;
2330
2331	start transaction;
2332
2333	update `character` set GuildID = NULL, GuildRoleID = NULL where GuildID = inGuildID;
2334
2335	delete from claim_rules where ClaimSubjectID in (select ID from claim_subjects where GuildID = inGuildID)
2336		or ClaimID in (select ID from claims where GuildLandID in (select ID from guild_lands where GuildID = inGuildID));
2337	delete from claim_rules_unmovable where ClaimSubjectID in (select ID from claim_subjects where GuildID = inGuildID)
2338		or UnmovableClaimID in (select ID from unmovable_objects_claims where ClaimID in (select ID from claims where GuildLandID in (select ID from guild_lands where GuildID = inGuildID)));
2339	delete from unmovable_objects_claims where ClaimID in (select ID from claims where GuildLandID in (select ID from guild_lands where GuildID = inGuildID));
2340	delete from claim_subjects where GuildID = inGuildID;
2341	delete from claims where GuildLandID in (select ID from guild_lands where GuildID = inGuildID);
2342	delete from guild_lands where GuildID = inGuildID;
2343	delete from guild_standings where GuildID1 = inGuildID or GuildID2 = inGuildID;
2344
2345	insert into deleted_guild_info
2346		(ExGuildID, GuildName, GuildTag)
2347		select ID, Name, GuildTag from guilds WHERE ID = inGuildID;
2348	set delGuildID = LAST_INSERT_ID();
2349
2350	delete from guild_actions_queue where GuildID = inGuildID or OtherGuildID = inGuildID;
2351	update guild_actions_processed set GuildDeletedID = delGuildID, GuildID = NULL where GuildID = inGuildID;
2352	update guild_actions_processed set OtherGuildDeletedID = delGuildID, OtherGuildID = NULL where OtherGuildID = inGuildID;
2353
2354	delete from guilds where ID = inGuildID;
2355
2356	-- cleanup claim_subjects
2357	delete from claim_subjects where !exists(select * from claim_rules where ClaimSubjectID = claim_subjects.ID) and
2358		!exists(select * from claim_rules_unmovable where ClaimSubjectID = claim_subjects.ID);
2359
2360	commit;
2361
2362END//
2363DELIMITER ;
2364
2365
2366-- Export de la structure de procédure lif_1. p_deleteHorseObject
2367DELIMITER //
2368CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteHorseObject`(
2369	IN `inID` INT UNSIGNED
2370)
2371BEGIN
2372	DELETE FROM horses WHERE ID = inID;
2373END//
2374DELIMITER ;
2375
2376
2377-- Export de la structure de procédure lif_1. p_deleteMovableObject
2378DELIMITER //
2379CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteMovableObject`(
2380	IN `inID` INT UNSIGNED
2381)
2382BEGIN
2383	declare cID INT UNSIGNED DEFAULT NULL;
2384	-- SELECT RootContainerID, GeoDataID FROM movable_objects WHERE ID = inID INTO cID, geoID FOR UPDATE;
2385
2386	DECLARE EXIT HANDLER FOR SQLEXCEPTION
2387  BEGIN
2388    ROLLBACK;
2389    RESIGNAL;
2390  END;
2391
2392  START TRANSACTION;
2393
2394	SELECT RootContainerID FROM movable_objects WHERE ID = inID INTO cID FOR UPDATE;
2395
2396	DELETE FROM `working_containers` WHERE MovableObjectID = inID;
2397
2398	DELETE FROM movable_objects WHERE ID = inID;
2399
2400	if(cID is not null) then
2401		CALL f_deleteContainer( cID);
2402	end if;
2403
2404/*
2405	if(geoID is not null) then
2406		UPDATE geo_data SET MovableObjectsCount = MovableObjectsCount -1
2407			WHERE ID = geoID AND MovableObjectsCount >0;
2408	end if;
2409*/
2410
2411	COMMIT;
2412
2413END//
2414DELIMITER ;
2415
2416
2417-- Export de la structure de procédure lif_1. p_deleteUnmovableObject
2418DELIMITER //
2419CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteUnmovableObject`(
2420	in_ID INT UNSIGNED
2421	-- in_sideObjectMask TINYINT UNSIGNED
2422)
2423BEGIN
2424	declare cID int unsigned default null;
2425
2426	DECLARE EXIT HANDLER FOR SQLEXCEPTION
2427  BEGIN
2428    ROLLBACK;
2429    RESIGNAL;
2430  END;
2431
2432  START TRANSACTION;
2433
2434	select RootContainerID
2435		from unmovable_objects
2436		where ID = in_ID
2437		into cID
2438		for update;
2439
2440/*
2441	if(in_sideObjectMask = 0) then
2442		-- truly unmovable object
2443
2444		-- side object have no records in geo_data.HaveUnmovableObject, so update below is redundant. Rewrite it if you really need it
2445		-- reset HaveUnmovableObject where our object was single one
2446		update geo_data geo
2447		join
2448		(
2449			select t.GeoDataID
2450			from
2451			(
2452				select ug.GeoDataID, ug.UnmovableObjectID
2453				from unmovable_geodata ug
2454				left join geo_data geo on geo.ID = ug.GeoDataID
2455				where ug.UnmovableObjectID = in_ID
2456			)
2457			as t
2458			where not exists(select * from unmovable_geodata where GeoDataID = t.GeoDataID and UnmovableObjectID != t.UnmovableObjectID)
2459		) as t
2460			on t.GeoDataID = geo.ID
2461		set HaveUnmovableObject = 0;
2462
2463	else
2464		-- remove side object mask from SubCellsObjectMask
2465		update geo_data
2466			set SubCellsObjectMask = (SubCellsObjectMask & ~in_sideObjectMask)
2467			where ID = geoID;
2468	end if;
2469*/
2470
2471	-- delete object
2472	-- DELETE FROM unmovable_geodata WHERE UnmovableObjectID = in_ID;
2473	DELETE FROM stables_logs WHERE UnmovableObjectID = in_ID;
2474	DELETE FROM stables_pens WHERE UnmovableObjectID = in_ID;
2475	DELETE FROM stables_data WHERE UnmovableObjectID = in_ID;
2476	UPDATE `character` SET BindedObjectID=null WHERE BindedObjectID = in_ID;
2477	UPDATE `character` SET RallyObjectID=null WHERE RallyObjectID = in_ID;
2478	DELETE FROM `working_containers` WHERE UnmovableObjectID = in_ID;
2479	DELETE FROM unmovable_objects WHERE ID = in_ID;
2480
2481	-- delete container
2482	if(cID is not null) then
2483		CALL f_deleteContainer( cID);
2484	end if;
2485
2486/*	if(geoID is not null) then
2487		UPDATE geo_data
2488			SET HaveUnmovableObject = (CASE WHEN EXISTS(SELECT * FROM unmovable_objects WHERE GeoDataID = geoID) THEN 1 ELSE 0 END)
2489			WHERE ID = geoID;
2490	end if;
2491*/
2492
2493	COMMIT;
2494
2495END//
2496DELIMITER ;
2497
2498
2499-- Export de la structure de procédure lif_1. p_dropMovableObject
2500DELIMITER //
2501CREATE DEFINER=`root`@`localhost` PROCEDURE `p_dropMovableObject`(
2502	`objID` INT UNSIGNED,
2503	`newRotate` SMALLINT,
2504	`newGeoID` INT UNSIGNED,
2505	`newAltitude` SMALLINT UNSIGNED,
2506	`newOffsetX` SMALLINT,
2507	`newOffsetY` SMALLINT,
2508	`newOffsetZ` INT
2509)
2510    MODIFIES SQL DATA
2511BEGIN
2512
2513	/* now we update old geo pos when we lift an object */
2514	/*
2515	DECLARE oldGeoID INT UNSIGNED DEFAULT NULL;
2516	SELECT GeoDataID FROM movable_objects WHERE ID =objID
2517		LIMIT 1
2518		INTO oldGeoID
2519		FOR UPDATE;
2520
2521	IF( oldGeoID is not null) THEN
2522		UPDATE geo_data
2523			SET MovableObjectsCount =MovableObjectsCount -1
2524		WHERE ID =oldGeoID AND MovableObjectsCount >0;
2525	END IF;
2526	*/
2527
2528/*
2529	UPDATE geo_data
2530		SET MovableObjectsCount =MovableObjectsCount +1
2531	WHERE ID =newGeoID;
2532*/
2533
2534	UPDATE movable_objects
2535	SET
2536		OffsetMmX =newOffsetX,
2537		OffsetMmY =newOffsetY,
2538		OffsetMmZ =newOffsetZ,
2539		TurnAngle =newRotate,
2540		GeoDataID =newGeoID,
2541		Altitude =newAltitude,
2542		CarrierCharacterID =NULL
2543	WHERE ID=objID;
2544END//
2545DELIMITER ;
2546
2547
2548-- Export de la structure de procédure lif_1. p_issueIdRange_horses
2549DELIMITER //
2550CREATE DEFINER=`root`@`localhost` PROCEDURE `p_issueIdRange_horses`(
2551	in_serverID INT UNSIGNED,
2552	in_idCount INT UNSIGNED,
2553	in_isForce TINYINT UNSIGNED
2554)
2555    MODIFIES SQL DATA
2556    COMMENT 'Return horses IDs which available for concrete server to insert'
2557BEGIN
2558
2559declare rangeID, startID, endID, currFreeIdCount, currMaxUsedId, maxUsedId int unsigned default 0;
2560declare maxIssuedId int unsigned default NULL;
2561declare errorFlag, cursorDone tinyint unsigned default FALSE;
2562
2563declare rangeCursor cursor for
2564(
2565	select ID, RangeStartID, RangeEndID
2566	from `horses_server_id_ranges`
2567	where ServerID = in_serverID
2568	order by RangeStartID -- ordering for consecutive ID using
2569	for update
2570);
2571declare continue handler for not found set cursorDone = TRUE;
2572
2573-- mysql does not stop procedure execution on errors, so do it manually
2574declare continue handler for sqlexception
2575begin
2576	-- "leave this_sp;" can't be called here, so use flag
2577	set errorFlag = TRUE;
2578end;
2579
2580start transaction;
2581
2582-- We using the horses_server_id_ranges_lock table like a mutex - lock it at transaction start,
2583-- and release it on commit/rollback. This ugly solution provides us a 100% deadlock protection when
2584-- this procedure runs simultaneously from several sessions.
2585-- If you don't care about deadlocks, you can simply skip this insert - all locking logic below
2586-- still provide data consistent and prevent any range intersections (but don't save you from deadlocks)
2587insert into `horses_server_id_ranges_lock` (ID, IsLocked) values (1, 1)
2588	on duplicate key update IsLocked=1;
2589
2590-- Get max issued ID before we delete any range.
2591-- This operation give us currently maximum claimed ID with blocking from same queries and
2592-- from inserting into the gap just before max(RangeEndID) and after max(RangeEndID)
2593select max(RangeEndID)
2594	into maxIssuedId
2595	from `horses_server_id_ranges`
2596	for update;
2597
2598-- parse existing ranges when we are not forced to insert new range
2599if(in_isForce = 0) then
2600	open rangeCursor;
2601
2602	-- get max used id from all ranges and block these ranges from inserts
2603	range_loop: loop
2604		fetch rangeCursor into rangeID, startID, endID;
2605		if(cursorDone) then
2606			leave range_loop;
2607		end if;
2608
2609		select max(ID)
2610			into currMaxUsedId
2611			from `horses`
2612			where ID >= startID and ID <= endID
2613			for update;
2614
2615		if(ifnull(currMaxUsedId, 0) > maxUsedId) then
2616			set maxUsedId = currMaxUsedId;
2617		end if;
2618	end loop;
2619	close rangeCursor;
2620
2621	-- re-use cursor for iterate thru all exists server ranges again and check it to exceed maxUsedId
2622	set cursorDone = FALSE;
2623	open rangeCursor;
2624	range_loop: loop
2625		fetch rangeCursor into rangeID, startID, endID;
2626		if(cursorDone) then
2627			leave range_loop;
2628		end if;
2629
2630		-- at first validate range
2631		if(startID > endID) then
2632			-- delete invalid range
2633			delete from `horses_server_id_ranges`
2634				where ID = rangeID;
2635		else
2636			-- range is valid
2637			if(maxUsedId < startID) then
2638				-- range have no any used ids
2639				set currFreeIdCount = (currFreeIdCount + (endID - startID) + 1);
2640			elseif(maxUsedId >= startID and maxUsedId < endID) then
2641				-- range have some used ids
2642				set currFreeIdCount = (currFreeIdCount + (endID - maxUsedId));
2643				update `horses_server_id_ranges`
2644					set RangeStartID = (maxUsedId + 1)
2645						-- , RangeEndID = endID
2646					where ID = rangeID;
2647			else -- if(maxUsedId >= endID) then
2648				-- range is full or maxUsedId is out of range bounds
2649				delete from `horses_server_id_ranges`
2650					where ID = rangeID;
2651			end if;
2652		end if;
2653
2654	end loop;
2655
2656	close rangeCursor;
2657end if;
2658
2659if(currFreeIdCount < in_idCount or in_isForce > 0) then
2660	-- reserve new ID range
2661
2662	-- compatibility with old auto_increment IDs
2663	if(maxIssuedId is null) then
2664		select max(ID)
2665			into maxIssuedId
2666			from `horses`
2667			for update;
2668	end if;
2669
2670	-- We reserve new range with full size of in_idCount. This save us from inserting small ranges
2671	-- each time this procedure called
2672	set startID = (ifnull(maxIssuedId, 0) + 1);
2673	set endID = (startID + in_idCount - 1);
2674
2675	-- remember new range
2676	insert into `horses_server_id_ranges`
2677		(ServerID, RangeStartID, RangeEndID)
2678		values (in_serverID, startID, endID);
2679end if;
2680
2681-- return result
2682if(!errorFlag) then
2683	select RangeStartID, RangeEndID
2684		from `horses_server_id_ranges`
2685		where ServerID = in_serverID
2686		order by RangeStartID;
2687
2688	commit;
2689else -- in case of error
2690	rollback;
2691end if;
2692
2693END//
2694DELIMITER ;
2695
2696
2697-- Export de la structure de procédure lif_1. p_issueIdRange_movable_objects
2698DELIMITER //
2699CREATE DEFINER=`root`@`localhost` PROCEDURE `p_issueIdRange_movable_objects`(
2700	in_serverID INT UNSIGNED,
2701	in_idCount INT UNSIGNED,
2702	in_isForce TINYINT UNSIGNED
2703)
2704    MODIFIES SQL DATA
2705    COMMENT 'Return movable_objects IDs which available for concrete server to insert'
2706BEGIN
2707
2708declare rangeID, startID, endID, currFreeIdCount, currMaxUsedId, maxUsedId int unsigned default 0;
2709declare maxIssuedId int unsigned default NULL;
2710declare errorFlag, cursorDone tinyint unsigned default FALSE;
2711
2712declare rangeCursor cursor for
2713(
2714	select ID, RangeStartID, RangeEndID
2715	from `movable_objects_server_id_ranges`
2716	where ServerID = in_serverID
2717	order by RangeStartID -- ordering for consecutive ID using
2718	for update
2719);
2720declare continue handler for not found set cursorDone = TRUE;
2721
2722-- mysql does not stop procedure execution on errors, so do it manually
2723declare continue handler for sqlexception
2724begin
2725	-- "leave this_sp;" can't be called here, so use flag
2726	set errorFlag = TRUE;
2727end;
2728
2729start transaction;
2730
2731-- We using the movable_objects_server_id_ranges_lock table like a mutex - lock it at transaction start,
2732-- and release it on commit/rollback. This ugly solution provides us a 100% deadlock protection when
2733-- this procedure runs simultaneously from several sessions.
2734-- If you don't care about deadlocks, you can simply skip this insert - all locking logic below
2735-- still provide data consistent and prevent any range intersections (but don't save you from deadlocks)
2736insert into `movable_objects_server_id_ranges_lock` (ID, IsLocked) values (1, 1)
2737	on duplicate key update IsLocked=1;
2738
2739-- Get max issued ID before we delete any range.
2740-- This operation give us currently maximum claimed ID with blocking from same queries and
2741-- from inserting into the gap just before max(RangeEndID) and after max(RangeEndID)
2742select max(RangeEndID)
2743	into maxIssuedId
2744	from `movable_objects_server_id_ranges`
2745	for update;
2746
2747-- parse existing ranges when we are not forced to insert new range
2748if(in_isForce = 0) then
2749	open rangeCursor;
2750
2751	-- get max used id from all ranges and block these ranges from inserts
2752	range_loop: loop
2753		fetch rangeCursor into rangeID, startID, endID;
2754		if(cursorDone) then
2755			leave range_loop;
2756		end if;
2757
2758		select max(ID)
2759			into currMaxUsedId
2760			from `movable_objects`
2761			where ID >= startID and ID <= endID
2762			for update;
2763
2764		if(ifnull(currMaxUsedId, 0) > maxUsedId) then
2765			set maxUsedId = currMaxUsedId;
2766		end if;
2767	end loop;
2768	close rangeCursor;
2769
2770	-- re-use cursor for iterate thru all exists server ranges again and check it to exceed maxUsedId
2771	set cursorDone = FALSE;
2772	open rangeCursor;
2773	range_loop: loop
2774		fetch rangeCursor into rangeID, startID, endID;
2775		if(cursorDone) then
2776			leave range_loop;
2777		end if;
2778
2779		-- at first validate range
2780		if(startID > endID) then
2781			-- delete invalid range
2782			delete from `movable_objects_server_id_ranges`
2783				where ID = rangeID;
2784		else
2785			-- range is valid
2786			if(maxUsedId < startID) then
2787				-- range have no any used ids
2788				set currFreeIdCount = (currFreeIdCount + (endID - startID) + 1);
2789			elseif(maxUsedId >= startID and maxUsedId < endID) then
2790				-- range have some used ids
2791				set currFreeIdCount = (currFreeIdCount + (endID - maxUsedId));
2792				update `movable_objects_server_id_ranges`
2793					set RangeStartID = (maxUsedId + 1)
2794						-- , RangeEndID = endID
2795					where ID = rangeID;
2796			else -- if(maxUsedId >= endID) then
2797				-- range is full or maxUsedId is out of range bounds
2798				delete from `movable_objects_server_id_ranges`
2799					where ID = rangeID;
2800			end if;
2801		end if;
2802
2803	end loop;
2804
2805	close rangeCursor;
2806end if;
2807
2808if(currFreeIdCount < in_idCount or in_isForce > 0) then
2809	-- reserve new ID range
2810
2811	-- compatibility with old auto_increment IDs
2812	if(maxIssuedId is null) then
2813		select max(ID)
2814			into maxIssuedId
2815			from `movable_objects`
2816			for update;
2817	end if;
2818
2819	-- We reserve new range with full size of in_idCount. This save us from inserting small ranges
2820	-- each time this procedure called
2821	set startID = (ifnull(maxIssuedId, 0) + 1);
2822	set endID = (startID + in_idCount - 1);
2823
2824	-- remember new range
2825	insert into `movable_objects_server_id_ranges`
2826		(ServerID, RangeStartID, RangeEndID)
2827		values (in_serverID, startID, endID);
2828end if;
2829
2830-- return result
2831if(!errorFlag) then
2832	select RangeStartID, RangeEndID
2833		from `movable_objects_server_id_ranges`
2834		where ServerID = in_serverID
2835		order by RangeStartID;
2836
2837	commit;
2838else -- in case of error
2839	rollback;
2840end if;
2841
2842END//
2843DELIMITER ;
2844
2845
2846-- Export de la structure de procédure lif_1. p_issueIdRange_unmovable_objects
2847DELIMITER //
2848CREATE DEFINER=`root`@`localhost` PROCEDURE `p_issueIdRange_unmovable_objects`(
2849	in_serverID INT UNSIGNED,
2850	in_idCount INT UNSIGNED,
2851	in_isForce TINYINT UNSIGNED
2852)
2853    MODIFIES SQL DATA
2854    COMMENT 'Return unmovable_objects IDs which available for concrete server to insert'
2855BEGIN
2856
2857declare rangeID, startID, endID, currFreeIdCount, currMaxUsedId, maxUsedId int unsigned default 0;
2858declare maxIssuedId int unsigned default NULL;
2859declare errorFlag, cursorDone tinyint unsigned default FALSE;
2860
2861declare rangeCursor cursor for
2862(
2863	select ID, RangeStartID, RangeEndID
2864	from `unmovable_objects_server_id_ranges`
2865	where ServerID = in_serverID
2866	order by RangeStartID -- ordering for consecutive ID using
2867	for update
2868);
2869declare continue handler for not found set cursorDone = TRUE;
2870
2871-- mysql does not stop procedure execution on errors, so do it manually
2872declare continue handler for sqlexception
2873begin
2874	-- "leave this_sp;" can't be called here, so use flag
2875	set errorFlag = TRUE;
2876end;
2877
2878start transaction;
2879
2880-- We using the unmovable_objects_server_id_ranges_lock table like a mutex - lock it at transaction start,
2881-- and release it on commit/rollback. This ugly solution provides us a 100% deadlock protection when
2882-- this procedure runs simultaneously from several sessions.
2883-- If you don't care about deadlocks, you can simply skip this insert - all locking logic below
2884-- still provide data consistent and prevent any range intersections (but don't save you from deadlocks)
2885insert into `unmovable_objects_server_id_ranges_lock` (ID, IsLocked) values (1, 1)
2886	on duplicate key update IsLocked=1;
2887
2888-- Get max issued ID before we delete any range.
2889-- This operation give us currently maximum claimed ID with blocking from same queries and
2890-- from inserting into the gap just before max(RangeEndID) and after max(RangeEndID)
2891select max(RangeEndID)
2892	into maxIssuedId
2893	from `unmovable_objects_server_id_ranges`
2894	for update;
2895
2896-- parse existing ranges when we are not forced to insert new range
2897if(in_isForce = 0) then
2898	open rangeCursor;
2899
2900	-- get max used id from all ranges and block these ranges from inserts
2901	range_loop: loop
2902		fetch rangeCursor into rangeID, startID, endID;
2903		if(cursorDone) then
2904			leave range_loop;
2905		end if;
2906
2907		select max(ID)
2908			into currMaxUsedId
2909			from `unmovable_objects`
2910			where ID >= startID and ID <= endID
2911			for update;
2912
2913		if(ifnull(currMaxUsedId, 0) > maxUsedId) then
2914			set maxUsedId = currMaxUsedId;
2915		end if;
2916	end loop;
2917	close rangeCursor;
2918
2919	-- re-use cursor for iterate thru all exists server ranges again and check it to exceed maxUsedId
2920	set cursorDone = FALSE;
2921	open rangeCursor;
2922	range_loop: loop
2923		fetch rangeCursor into rangeID, startID, endID;
2924		if(cursorDone) then
2925			leave range_loop;
2926		end if;
2927
2928		-- at first validate range
2929		if(startID > endID) then
2930			-- delete invalid range
2931			delete from `unmovable_objects_server_id_ranges`
2932				where ID = rangeID;
2933		else
2934			-- range is valid
2935			if(maxUsedId < startID) then
2936				-- range have no any used ids
2937				set currFreeIdCount = (currFreeIdCount + (endID - startID) + 1);
2938			elseif(maxUsedId >= startID and maxUsedId < endID) then
2939				-- range have some used ids
2940				set currFreeIdCount = (currFreeIdCount + (endID - maxUsedId));
2941				update `unmovable_objects_server_id_ranges`
2942					set RangeStartID = (maxUsedId + 1)
2943						-- , RangeEndID = endID
2944					where ID = rangeID;
2945			else -- if(maxUsedId >= endID) then
2946				-- range is full or maxUsedId is out of range bounds
2947				delete from `unmovable_objects_server_id_ranges`
2948					where ID = rangeID;
2949			end if;
2950		end if;
2951
2952	end loop;
2953
2954	close rangeCursor;
2955end if;
2956
2957if(currFreeIdCount < in_idCount or in_isForce > 0) then
2958	-- reserve new ID range
2959
2960	-- compatibility with old auto_increment IDs
2961	if(maxIssuedId is null) then
2962		select max(ID)
2963			into maxIssuedId
2964			from `unmovable_objects`
2965			for update;
2966	end if;
2967
2968	-- We reserve new range with full size of in_idCount. This save us from inserting small ranges
2969	-- each time this procedure called
2970	set startID = (ifnull(maxIssuedId, 0) + 1);
2971	set endID = (startID + in_idCount - 1);
2972
2973	-- remember new range
2974	insert into `unmovable_objects_server_id_ranges`
2975		(ServerID, RangeStartID, RangeEndID)
2976		values (in_serverID, startID, endID);
2977end if;
2978
2979-- return result
2980if(!errorFlag) then
2981	select RangeStartID, RangeEndID
2982		from `unmovable_objects_server_id_ranges`
2983		where ServerID = in_serverID
2984		order by RangeStartID;
2985
2986	commit;
2987else -- in case of error
2988	rollback;
2989end if;
2990
2991END//
2992DELIMITER ;
2993
2994
2995-- Export de la structure de procédure lif_1. p_liftMovableObject
2996DELIMITER //
2997CREATE DEFINER=`root`@`localhost` PROCEDURE `p_liftMovableObject`(
2998	`objID` INT UNSIGNED,
2999	`carrierCharID` INT UNSIGNED
3000)
3001    MODIFIES SQL DATA
3002BEGIN
3003/*
3004	DECLARE geoID INT UNSIGNED DEFAULT NULL;
3005	SELECT GeoDataID FROM movable_objects WHERE ID =objID
3006		LIMIT 1
3007		INTO geoID
3008		FOR UPDATE;
3009		
3010	IF(geoID is not null) THEN
3011		UPDATE geo_data
3012			SET MovableObjectsCount =MovableObjectsCount -1	
3013		WHERE ID =geoID AND MovableObjectsCount >0;
3014	END IF;
3015*/
3016
3017	UPDATE movable_objects
3018		SET CarrierCharacterID =carrierCharID
3019	WHERE ID=objID;
3020END//
3021DELIMITER ;
3022
3023
3024-- Export de la structure de procédure lif_1. p_linkBlueprintToCharacter
3025DELIMITER //
3026CREATE DEFINER=`root`@`localhost` PROCEDURE `p_linkBlueprintToCharacter`(
3027	`in_blueprintID` INT UNSIGNED,
3028	`in_charID` INT UNSIGNED
3029)
3030    MODIFIES SQL DATA
3031    COMMENT 'Learns blueprint'
3032BEGIN
3033	-- we can't delete item here due to de-sync between `items` table and GS inventory info
3034	UPDATE `features` SET `BlueprintID`=NULL WHERE `BlueprintID`=in_blueprintID;
3035	INSERT INTO `character_blueprints` (`CharID`,`BlueprintID`) VALUES (in_charID,in_blueprintID);
3036END//
3037DELIMITER ;
3038
3039
3040-- Export de la structure de procédure lif_1. p_occupyId_horses
3041DELIMITER //
3042CREATE DEFINER=`root`@`localhost` PROCEDURE `p_occupyId_horses`(
3043	in_serverID INT UNSIGNED,
3044	in_id INT UNSIGNED
3045)
3046    MODIFIES SQL DATA
3047    COMMENT 'Modifies horses ID ranges for concrete server so given ID will never be used again'
3048BEGIN
3049
3050update horses_server_id_ranges
3051set RangeStartID = (in_id + 1)
3052where ServerID = in_serverID and
3053	in_id >= RangeStartID and in_id <= RangeEndID;
3054
3055END//
3056DELIMITER ;
3057
3058
3059-- Export de la structure de procédure lif_1. p_occupyId_movable_objects
3060DELIMITER //
3061CREATE DEFINER=`root`@`localhost` PROCEDURE `p_occupyId_movable_objects`(
3062	in_serverID INT UNSIGNED,
3063	in_id INT UNSIGNED
3064)
3065    MODIFIES SQL DATA
3066    COMMENT 'Modifies movable_objects ID ranges for concrete server so given ID will never be used again'
3067BEGIN
3068
3069update movable_objects_server_id_ranges
3070set RangeStartID = (in_id + 1)
3071where ServerID = in_serverID and
3072	in_id >= RangeStartID and in_id <= RangeEndID;
3073
3074END//
3075DELIMITER ;
3076
3077
3078-- Export de la structure de procédure lif_1. p_occupyId_unmovable_objects
3079DELIMITER //
3080CREATE DEFINER=`root`@`localhost` PROCEDURE `p_occupyId_unmovable_objects`(
3081	in_serverID INT UNSIGNED,
3082	in_id INT UNSIGNED
3083)
3084    MODIFIES SQL DATA
3085    COMMENT 'Modifies unmovable_objects ID ranges for concrete server so given ID will never be used again'
3086BEGIN
3087
3088update unmovable_objects_server_id_ranges
3089set RangeStartID = (in_id + 1)
3090where ServerID = in_serverID and
3091	in_id >= RangeStartID and in_id <= RangeEndID;
3092
3093END//
3094DELIMITER ;
3095
3096
3097-- Export de la structure de procédure lif_1. p_recreateUnmovableObject
3098DELIMITER //
3099CREATE DEFINER=`root`@`localhost` PROCEDURE `p_recreateUnmovableObject`(
3100	IN `inOldObjID` INT UNSIGNED,
3101	IN `inNewObjID` INT UNSIGNED,
3102	IN `inNewTypeID` INT UNSIGNED,
3103	IN `inCreatedDurability` SMALLINT UNSIGNED,
3104	IN `inDurability` SMALLINT UNSIGNED,
3105	IN `inCompleted` TINYINT UNSIGNED
3106)
3107BEGIN
3108	DECLARE oldGeoDataID, newContainerID INT UNSIGNED default NULL;
3109	DECLARE oldSlope TINYINT UNSIGNED default NULL;
3110	DECLARE oldTurnAngle SMALLINT default NULL;
3111	DECLARE newContainerName VARCHAR(45) default NULL;
3112	declare newObjIsContainer TINYINT default NULL;
3113
3114	SELECT IsContainer, Name FROM objects_types WHERE ID = inNewTypeID
3115		INTO newObjIsContainer, newContainerName;
3116
3117	SELECT Slope, GeoDataID, TurnAngle FROM unmovable_objects WHERE ID = inOldObjID
3118		INTO oldSlope, oldGeoDataID, oldTurnAngle
3119		FOR UPDATE;
3120
3121	CALL p_deleteUnmovableObject( inOldObjID);
3122	CALL p_createUnmovableObject(
3123		inNewObjID,
3124		inNewTypeID,
3125		oldSlope,
3126		oldTurnAngle,
3127		oldGeoDataID,
3128		inCreatedDurability,
3129		inCompleted);
3130
3131	-- container for inclomplete objects already created in p_createUnmovableObject. For completed object we duplicate container-related logic from p_completeBuilding here
3132	if(newObjIsContainer > 0 and inCompleted > 0) then
3133		SET newContainerID = f_createRootContainer(newContainerName);
3134
3135		UPDATE `unmovable_objects` SET
3136			CreatedDurability =inCreatedDurability,
3137			Durability =inDurability,
3138			RootContainerID =newContainerID
3139			WHERE ID=inNewObjID;
3140	else
3141		UPDATE `unmovable_objects` SET
3142			CreatedDurability =inCreatedDurability,
3143			Durability =inDurability
3144			WHERE ID=inNewObjID;
3145	end if;
3146END//
3147DELIMITER ;
3148
3149
3150-- Export de la structure de procédure lif_1. p_transferMovableObjItems
3151DELIMITER //
3152CREATE DEFINER=`root`@`localhost` PROCEDURE `p_transferMovableObjItems`(
3153	IN `in_sourceMovableObjID` INT UNSIGNED,
3154	IN `in_targetUnmovableObjID` INT UNSIGNED
3155)
3156    MODIFIES SQL DATA
3157    COMMENT 'Moves inventory items from movable object to unmovable object'
3158BEGIN
3159
3160	declare var_sourceContID, var_targetContID INT UNSIGNED default NULL;
3161
3162	select RootContainerID into var_sourceContID from movable_objects where ID = in_sourceMovableObjID;
3163	select RootContainerID into var_targetContID from unmovable_objects where ID = in_targetUnmovableObjID;
3164
3165	if(var_sourceContID is not null and var_targetContID is not null) then
3166		-- move non-stackable items with 80% probability
3167		update items i
3168		join objects_types ot on ot.ID = i.ObjectTypeID and ot.MaxStackSize <= 1
3169		set i.ContainerID = var_targetContID
3170		where i.ContainerID = var_sourceContID
3171			and rand() <= 0.8;
3172
3173		-- move stackable items with loss of 0.2 quantity
3174		update items i
3175		join objects_types ot on ot.ID = i.ObjectTypeID and ot.MaxStackSize > 1
3176		set i.ContainerID = var_targetContID,
3177			i.Quantity = floor(i.Quantity * 0.8)
3178		where i.ContainerID = var_sourceContID
3179			and floor(i.Quantity * 0.8) > 0;
3180
3181		-- move containers with 80% probability
3182		update containers c
3183		set c.ParentID = var_targetContID
3184		where c.ParentID = var_sourceContID
3185			and rand() <= 0.8;
3186	end if;
3187
3188END//
3189DELIMITER ;
3190
3191
3192-- Export de la structure de procédure lif_1. p_yo_check_steam_accout_exists
3193DELIMITER //
3194CREATE DEFINER=`root`@`localhost` PROCEDURE `p_yo_check_steam_accout_exists`(
3195	`in_steamID` BIGINT UNSIGNED
3196)
3197BEGIN
3198
3199	insert ignore account (SteamID)
3200	values (in_steamID);
3201
3202	select ID, IsActive, IsGM
3203	from account
3204	where SteamID = in_steamID;
3205	
3206END//
3207DELIMITER ;
3208
3209
3210-- Export de la structure de table lif_1. race
3211CREATE TABLE IF NOT EXISTS `race` (
3212  `ID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
3213  `Name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
3214  PRIMARY KEY (`ID`)
3215) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3216
3217-- L'exportation de données n'était pas sélectionnée.
3218
3219
3220-- Export de la structure de table lif_1. recipe
3221CREATE TABLE IF NOT EXISTS `recipe` (
3222  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3223  `Name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
3224  `Description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
3225  `StartingToolsID` int(10) unsigned DEFAULT NULL,
3226  `SkillTypeID` int(10) unsigned DEFAULT NULL,
3227  `SkillLvl` tinyint(3) unsigned DEFAULT NULL COMMENT 'Minimum skill required',
3228  `ResultObjectTypeID` int(10) unsigned DEFAULT NULL,
3229  `SkillDepends` tinyint(3) unsigned NOT NULL DEFAULT '0',
3230  `Quantity` smallint(5) unsigned NOT NULL DEFAULT '0',
3231  `Autorepeat` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'bool',
3232  `IsBlueprint` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'bool',
3233  PRIMARY KEY (`ID`),
3234  KEY `FK_recipe_starting_objects_types` (`StartingToolsID`),
3235  KEY `FK_recipe_skill_type` (`SkillTypeID`),
3236  KEY `FK_recipe_result_objects_types` (`ResultObjectTypeID`),
3237  CONSTRAINT `FK_recipe_result_objects_types` FOREIGN KEY (`ResultObjectTypeID`) REFERENCES `objects_types` (`ID`),
3238  CONSTRAINT `FK_recipe_skill_type` FOREIGN KEY (`SkillTypeID`) REFERENCES `skill_type` (`ID`),
3239  CONSTRAINT `FK_recipe_starting_objects_types` FOREIGN KEY (`StartingToolsID`) REFERENCES `objects_types` (`ID`)
3240) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3241
3242-- L'exportation de données n'était pas sélectionnée.
3243
3244
3245-- Export de la structure de table lif_1. recipe_possible_blueprints
3246CREATE TABLE IF NOT EXISTS `recipe_possible_blueprints` (
3247  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3248  `RecipeID` int(10) unsigned NOT NULL,
3249  `BaseRecipeID` int(10) unsigned NOT NULL,
3250  PRIMARY KEY (`ID`),
3251  KEY `FK_recipe_possible_blueprints_recipe` (`RecipeID`),
3252  KEY `FK_recipe_possible_blueprints_baserecipe` (`BaseRecipeID`),
3253  CONSTRAINT `FK_recipe_possible_blueprints_baserecipe` FOREIGN KEY (`BaseRecipeID`) REFERENCES `recipe` (`ID`),
3254  CONSTRAINT `FK_recipe_possible_blueprints_recipe` FOREIGN KEY (`RecipeID`) REFERENCES `recipe` (`ID`)
3255) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3256
3257-- L'exportation de données n'était pas sélectionnée.
3258
3259
3260-- Export de la structure de table lif_1. recipe_requirement
3261CREATE TABLE IF NOT EXISTS `recipe_requirement` (
3262  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3263  `RecipeID` int(10) unsigned DEFAULT NULL,
3264  `MaterialObjectTypeID` int(10) unsigned DEFAULT NULL,
3265  `Quality` tinyint(6) unsigned DEFAULT NULL,
3266  `Influence` tinyint(6) unsigned DEFAULT NULL,
3267  `Quantity` smallint(6) unsigned DEFAULT NULL COMMENT 'also amount of durability loss of a tool.',
3268  `IsRegionItemRequired` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'bool',
3269  PRIMARY KEY (`ID`),
3270  KEY `FK_recipe_requirement_recipe` (`RecipeID`),
3271  KEY `FK_recipe_requirement_objects_types` (`MaterialObjectTypeID`),
3272  CONSTRAINT `FK_recipe_requirement_objects_types` FOREIGN KEY (`MaterialObjectTypeID`) REFERENCES `objects_types` (`ID`),
3273  CONSTRAINT `FK_recipe_requirement_recipe` FOREIGN KEY (`RecipeID`) REFERENCES `recipe` (`ID`)
3274) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3275
3276-- L'exportation de données n'était pas sélectionnée.
3277
3278
3279-- Export de la structure de table lif_1. regions
3280CREATE TABLE IF NOT EXISTS `regions` (
3281  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3282  `NameMessageID` int(10) unsigned NOT NULL,
3283  PRIMARY KEY (`ID`)
3284) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3285
3286-- L'exportation de données n'était pas sélectionnée.
3287
3288
3289-- Export de la structure de table lif_1. server_uuid
3290CREATE TABLE IF NOT EXISTS `server_uuid` (
3291  `ID` tinyint(3) unsigned NOT NULL,
3292  `Uuid` char(36) COLLATE utf8_unicode_ci NOT NULL,
3293  PRIMARY KEY (`ID`)
3294) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3295
3296-- L'exportation de données n'était pas sélectionnée.
3297
3298
3299-- Export de la structure de fonction lif_1. sf_getColumnMaxCharLen
3300DELIMITER //
3301CREATE DEFINER=`root`@`localhost` FUNCTION `sf_getColumnMaxCharLen`(
3302	`TableName` VARCHAR(128),
3303	`ColumnName` VARCHAR(128)
3304) RETURNS bigint(20) unsigned
3305BEGIN
3306	declare ret_val bigint unsigned default 0;
3307	
3308	SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
3309		AND TABLE_NAME=`TableName` COLLATE utf8_unicode_ci
3310		AND COLUMN_NAME=`ColumnName` COLLATE utf8_unicode_ci
3311		INTO ret_val;
3312	
3313	return ret_val;
3314END//
3315DELIMITER ;
3316
3317
3318-- Export de la structure de fonction lif_1. sf_isColumnExists
3319DELIMITER //
3320CREATE DEFINER=`root`@`localhost` FUNCTION `sf_isColumnExists`(`TableName` VARCHAR(128), `ColumnName` VARCHAR(128)) RETURNS tinyint(1)
3321    MODIFIES SQL DATA
3322BEGIN
3323	declare ret_val tinyint(1) default 0;
3324	
3325	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
3326		AND TABLE_NAME=`TableName` COLLATE utf8_unicode_ci
3327		AND column_name=`ColumnName` COLLATE utf8_unicode_ci) then
3328		set ret_val = 1;
3329	end if;
3330	
3331	return ret_val;
3332
3333END//
3334DELIMITER ;
3335
3336
3337-- Export de la structure de fonction lif_1. sf_isColumnTypeMatch
3338DELIMITER //
3339CREATE DEFINER=`root`@`localhost` FUNCTION `sf_isColumnTypeMatch`(
3340	`TableName` VARCHAR(128),
3341	`ColumnName` VARCHAR(128),
3342	`TypeName` VARCHAR(64)
3343) RETURNS tinyint(3) unsigned
3344BEGIN
3345	
3346	IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
3347		AND TABLE_NAME=`TableName` COLLATE utf8_unicode_ci
3348		AND COLUMN_NAME=`ColumnName` COLLATE utf8_unicode_ci
3349		AND DATA_TYPE=`TypeName` COLLATE utf8_unicode_ci
3350	) then
3351		return 1;
3352	end if;
3353	
3354	return 0;
3355END//
3356DELIMITER ;
3357
3358
3359-- Export de la structure de fonction lif_1. sf_isConstraintExists
3360DELIMITER //
3361CREATE DEFINER=`root`@`localhost` FUNCTION `sf_isConstraintExists`(
3362	`TableName` VARCHAR(128),
3363	`ConstraintName` VARCHAR(128)
3364) RETURNS tinyint(3) unsigned
3365BEGIN
3366
3367	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=DATABASE()
3368		AND TABLE_NAME=`TableName` COLLATE utf8_unicode_ci
3369		AND CONSTRAINT_NAME=`ConstraintName` COLLATE utf8_unicode_ci
3370	) then
3371		return 1;
3372	end if;
3373	
3374	return 0;
3375END//
3376DELIMITER ;
3377
3378
3379-- Export de la structure de fonction lif_1. sf_isIndexExists
3380DELIMITER //
3381CREATE DEFINER=`root`@`localhost` FUNCTION `sf_isIndexExists`(
3382	`TableName` VARCHAR(128),
3383	`IndexName` VARCHAR(128)
3384) RETURNS tinyint(3) unsigned
3385BEGIN
3386	
3387	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE()
3388		AND TABLE_NAME=`TableName` COLLATE utf8_unicode_ci
3389		AND INDEX_NAME=`IndexName` COLLATE utf8_unicode_ci
3390	) then
3391		return 1;
3392	end if;
3393	
3394	return 0;
3395END//
3396DELIMITER ;
3397
3398
3399-- Export de la structure de fonction lif_1. sf_isTableExists
3400DELIMITER //
3401CREATE DEFINER=`root`@`localhost` FUNCTION `sf_isTableExists`(
3402	`TableName` VARCHAR(128)
3403) RETURNS tinyint(3) unsigned
3404BEGIN
3405
3406	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
3407		AND TABLE_NAME=`TableName` COLLATE utf8_unicode_ci
3408	) then
3409		return 1;
3410	end if;
3411	
3412	return 0;
3413END//
3414DELIMITER ;
3415
3416
3417-- Export de la structure de fonction lif_1. sf_isUserExists
3418DELIMITER //
3419CREATE DEFINER=`root`@`localhost` FUNCTION `sf_isUserExists`(
3420	`UserName` VARCHAR(16),
3421	`Host` VARCHAR(60)
3422) RETURNS tinyint(3) unsigned
3423BEGIN
3424	
3425	IF EXISTS (SELECT * FROM mysql.user
3426		WHERE `User` = `UserName` COLLATE utf8_unicode_ci
3427		AND `Host` = `Host` COLLATE utf8_unicode_ci
3428	) then
3429		return 1;
3430	end if;
3431	
3432	return 0;
3433END//
3434DELIMITER ;
3435
3436
3437-- Export de la structure de table lif_1. skills
3438CREATE TABLE IF NOT EXISTS `skills` (
3439  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3440  `CharacterID` int(10) unsigned NOT NULL,
3441  `SkillTypeID` int(10) unsigned NOT NULL,
3442  `SkillAmount` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '7 digits after point',
3443  `LockStatus` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'up 1 lock 0 down -1',
3444  PRIMARY KEY (`ID`),
3445  UNIQUE KEY `UNQ_skills_CharacterID_SkillTypeID` (`CharacterID`,`SkillTypeID`),
3446  KEY `FK_skills_skill_type` (`SkillTypeID`),
3447  CONSTRAINT `FK_skills_character` FOREIGN KEY (`CharacterID`) REFERENCES `character` (`ID`),
3448  CONSTRAINT `FK_skills_skill_type` FOREIGN KEY (`SkillTypeID`) REFERENCES `skill_type` (`ID`)
3449) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3450
3451-- L'exportation de données n'était pas sélectionnée.
3452
3453
3454-- Export de la structure de table lif_1. skill_raise_logs
3455CREATE TABLE IF NOT EXISTS `skill_raise_logs` (
3456  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3457  `SkillID` int(10) unsigned NOT NULL,
3458  `AbilityID` int(10) unsigned DEFAULT NULL,
3459  `PlayerID` int(10) unsigned NOT NULL,
3460  `Time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3461  `RaiseAmount` int(10) unsigned NOT NULL COMMENT '7 digits after point',
3462  `SkillMult` int(10) unsigned NOT NULL,
3463  `FedRate` int(10) unsigned NOT NULL COMMENT '3 digits after point',
3464  PRIMARY KEY (`ID`)
3465) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3466
3467-- L'exportation de données n'était pas sélectionnée.
3468
3469
3470-- Export de la structure de table lif_1. skill_type
3471CREATE TABLE IF NOT EXISTS `skill_type` (
3472  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3473  `Name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
3474  `Description` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
3475  `Parent` int(10) unsigned DEFAULT NULL,
3476  `Group` tinyint(3) unsigned DEFAULT NULL,
3477  `PrimaryStat` char(4) COLLATE utf8_unicode_ci NOT NULL,
3478  `SecondaryStat` char(4) COLLATE utf8_unicode_ci NOT NULL,
3479  `MasterMessageID` int(10) unsigned DEFAULT '0',
3480  `GMMessageID` int(10) unsigned DEFAULT '0',
3481  PRIMARY KEY (`ID`),
3482  KEY `FK_skill_type_skill_type` (`Parent`),
3483  CONSTRAINT `FK_skill_type_skill_type` FOREIGN KEY (`Parent`) REFERENCES `skill_type` (`ID`)
3484) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3485
3486-- L'exportation de données n'était pas sélectionnée.
3487
3488
3489-- Export de la structure de procédure lif_1. sp_checkForeignKeys
3490DELIMITER //
3491CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_checkForeignKeys`(
3492	`in_TableName` varchar(128),
3493	`in_StoreDetailedResult` tinyint unsigned, -- 1/0
3494	`in_ClearOldResults` tinyint unsigned -- 1/0
3495)
3496    COMMENT 'Check consistency of foreign keys which references to/from in_TableName table. Return 1 when data is consistent. If check failed, return 0 and write mistatch rows info into _invalid_foreign_keys table (when in_StoreDetailedResult parameter is 1)'
3497BEGIN
3498
3499	declare table_name_var varchar(64) default NULL;
3500	declare column_name_var varchar(64) default NULL;
3501	declare constraint_name_var varchar(128) default NULL;
3502	declare referenced_table_name_var varchar(64) default NULL;
3503	declare referenced_column_name_var varchar(64) default NULL;
3504
3505	declare checkResult tinyint unsigned default TRUE;
3506	declare cursorDone tinyint unsigned default FALSE;
3507
3508	declare fk_cur cursor for
3509	(
3510		select `TABLE_NAME`, `COLUMN_NAME`, `CONSTRAINT_NAME`, `REFERENCED_TABLE_NAME`, `REFERENCED_COLUMN_NAME`
3511		from `information_schema`.`KEY_COLUMN_USAGE`
3512		where `TABLE_SCHEMA` = database()
3513			-- and `CONSTRAINT_SCHEMA` = database()
3514			and `REFERENCED_TABLE_SCHEMA` = database()
3515			and (`TABLE_NAME` = in_TableName collate utf8_unicode_ci or `REFERENCED_TABLE_NAME` = in_TableName collate utf8_unicode_ci) -- check both: references to our table, and references of our table to other ones
3516	);
3517	declare continue handler for not found set cursorDone = TRUE;
3518
3519	-- recreate table if we need to store results
3520	if(in_StoreDetailedResult > 0) then
3521		if(!sf_isTableExists('_invalid_foreign_keys')) then
3522			create table _invalid_foreign_keys(
3523				`ID` int unsigned NOT NULL AUTO_INCREMENT,
3524				`TableName` varchar(64) NOT NULL,
3525				`ColumnName` varchar(64) NOT NULL,
3526				`ConstraintName` varchar(128) NOT NULL,
3527				`ReferencedTableName` varchar(64) NOT NULL,
3528				`ReferencedColumnName` varchar(64) NOT NULL,
3529				`InvalidKeyCount` int unsigned NOT NULL,
3530				`InvalidKeySql` varchar(2048) NOT NULL,
3531				PRIMARY KEY (`ID`)
3532			) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3533		elseif(in_ClearOldResults > 0) then
3534			truncate table _invalid_foreign_keys;
3535		end if;
3536	end if;
3537
3538	-- iterate thru all FKs
3539	open fk_cur;
3540	fk_loop: loop
3541		fetch fk_cur into
3542			table_name_var,
3543			column_name_var,
3544			constraint_name_var,
3545			referenced_table_name_var,
3546			referenced_column_name_var;
3547
3548		if(cursorDone) then
3549			leave fk_loop;
3550		end if;
3551
3552		-- check if referred table have all values form referring table
3553		set @from_qs = concat('FROM `', table_name_var, '` AS REFERRING ',
3554			'LEFT JOIN `', referenced_table_name_var, '` AS REFERRED ',
3555			'ON (REFERRING.`', column_name_var, '` = REFERRED.`', referenced_column_name_var, '`) ',
3556			'WHERE REFERRING.`', column_name_var, '` IS NOT NULL ',
3557			'AND REFERRED.`', referenced_column_name_var, '` IS NULL');
3558
3559		set @qs = concat('SELECT COUNT(*) ', @from_qs, ' INTO @invalid_key_count;');
3560
3561		prepare fk_stmt from @qs;
3562		execute fk_stmt;
3563
3564		if(@invalid_key_count > 0) then
3565			-- if FK mismatches found, change result status
3566			set checkResult = FALSE;
3567
3568			-- remember failed FK
3569			if(in_StoreDetailedResult > 0) then
3570				insert into _invalid_foreign_keys
3571				set
3572					TableName = table_name_var,
3573					ColumnName = column_name_var,
3574					ConstraintName = constraint_name_var,
3575					ReferencedTableName = referenced_table_name_var,
3576					ReferencedColumnName = referenced_column_name_var,
3577					InvalidKeyCount = @invalid_key_count,
3578					InvalidKeySql = CONCAT('SELECT REFERRING.`', column_name_var, '` AS "Invalid: ', column_name_var, '", REFERRING.* ', @from_qs, ';');
3579			end if;
3580		end if;
3581
3582		deallocate prepare fk_stmt;
3583	end loop;
3584
3585	select checkResult as `FK_Valid`;
3586END//
3587DELIMITER ;
3588
3589
3590-- Export de la structure de table lif_1. stables_data
3591CREATE TABLE IF NOT EXISTS `stables_data` (
3592  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3593  `UnmovableObjectID` int(10) unsigned NOT NULL,
3594  `FoodConsumeRatio` float NOT NULL DEFAULT '0',
3595  `DungMeter` float NOT NULL DEFAULT '0',
3596  `HarvestAmount` float NOT NULL DEFAULT '0',
3597  `HarvestQuality` float NOT NULL DEFAULT '0',
3598  `FoodLeft` float NOT NULL DEFAULT '0',
3599  `FoodQuality` float NOT NULL DEFAULT '0',
3600  `DungQuantity` float NOT NULL DEFAULT '0',
3601  `DungQuality` float NOT NULL DEFAULT '0',
3602  `Starving` tinyint(4) NOT NULL DEFAULT '0',
3603  `Dirty` tinyint(4) NOT NULL DEFAULT '0',
3604  PRIMARY KEY (`ID`),
3605  UNIQUE KEY `FK_stables_data_UnmovableObjectID` (`UnmovableObjectID`),
3606  CONSTRAINT `FK_stables_data_UnmovableObjectID` FOREIGN KEY (`UnmovableObjectID`) REFERENCES `unmovable_objects` (`ID`)
3607) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3608
3609-- L'exportation de données n'était pas sélectionnée.
3610
3611
3612-- Export de la structure de table lif_1. stables_logs
3613CREATE TABLE IF NOT EXISTS `stables_logs` (
3614  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3615  `UnmovableObjectID` int(10) unsigned NOT NULL,
3616  `EventTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3617  `MsgID` int(10) unsigned NOT NULL,
3618  `Param1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
3619  `Param2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
3620  `Param3` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
3621  PRIMARY KEY (`ID`),
3622  KEY `FK_stables_logs_UnmovableObjectID` (`UnmovableObjectID`),
3623  CONSTRAINT `FK_stables_logs_UnmovableObjectID` FOREIGN KEY (`UnmovableObjectID`) REFERENCES `unmovable_objects` (`ID`)
3624) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3625
3626-- L'exportation de données n'était pas sélectionnée.
3627
3628
3629-- Export de la structure de table lif_1. stables_pens
3630CREATE TABLE IF NOT EXISTS `stables_pens` (
3631  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3632  `UnmovableObjectID` int(10) unsigned NOT NULL,
3633  `ItemID` int(10) unsigned NOT NULL,
3634  `Slot` tinyint(3) unsigned NOT NULL,
3635  PRIMARY KEY (`ID`),
3636  KEY `FK_stables_pens_UnmovableObjectID` (`UnmovableObjectID`),
3637  KEY `FK_stables_pens_ItemID` (`ItemID`),
3638  CONSTRAINT `FK_stables_pens_ItemID` FOREIGN KEY (`ItemID`) REFERENCES `items` (`ID`),
3639  CONSTRAINT `FK_stables_pens_UnmovableObjectID` FOREIGN KEY (`UnmovableObjectID`) REFERENCES `unmovable_objects` (`ID`)
3640) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3641
3642-- L'exportation de données n'était pas sélectionnée.
3643
3644
3645-- Export de la structure de table lif_1. terrain_blocks
3646CREATE TABLE IF NOT EXISTS `terrain_blocks` (
3647  `ID` int(10) unsigned NOT NULL,
3648  `RegionID` int(10) unsigned NOT NULL,
3649  `ObjectsVersion` int(10) unsigned NOT NULL DEFAULT '1',
3650  `ForestVersion` int(10) unsigned NOT NULL DEFAULT '1',
3651  `GeoVersion` int(10) unsigned NOT NULL DEFAULT '1',
3652  `TerCRC` int(10) unsigned DEFAULT NULL,
3653  `GeoIdxCRC` int(10) unsigned DEFAULT NULL,
3654  `GeoDatCRC` int(10) unsigned DEFAULT NULL,
3655  `ObjectsCRC` int(10) unsigned DEFAULT NULL,
3656  `ForestCRC` int(10) unsigned DEFAULT NULL,
3657  `CachedGeoVersion` int(10) unsigned DEFAULT NULL,
3658  `CachedTerCRC` int(10) unsigned DEFAULT NULL,
3659  `CachedServerGeoIdxCRC` int(10) unsigned DEFAULT NULL,
3660  `CachedServerGeoDatCRC` int(10) unsigned DEFAULT NULL,
3661  `CachedClientGeoIdxCRC` int(10) unsigned DEFAULT NULL,
3662  `CachedClientGeoDatCRC` int(10) unsigned DEFAULT NULL,
3663  `PackedTerCRC` int(10) unsigned DEFAULT NULL,
3664  `PackedClientGeoIdxCRC` int(10) unsigned DEFAULT NULL,
3665  `PackedClientGeoDatCRC` int(10) unsigned DEFAULT NULL,
3666  `CachedClientGeoIdxSize` int(10) unsigned DEFAULT NULL,
3667  `CachedClientGeoDatSize` int(10) unsigned DEFAULT NULL,
3668  PRIMARY KEY (`ID`),
3669  KEY `FK_terrain_blocks_regions` (`RegionID`),
3670  CONSTRAINT `FK_terrain_blocks_regions` FOREIGN KEY (`RegionID`) REFERENCES `regions` (`ID`)
3671) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3672
3673-- L'exportation de données n'était pas sélectionnée.
3674
3675
3676-- Export de la structure de table lif_1. titles
3677CREATE TABLE IF NOT EXISTS `titles` (
3678  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3679  `Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
3680  `TitleMessageID` int(10) unsigned NOT NULL,
3681  PRIMARY KEY (`ID`)
3682) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3683
3684-- L'exportation de données n'était pas sélectionnée.
3685
3686
3687-- Export de la structure de table lif_1. unmovable_objects
3688CREATE TABLE IF NOT EXISTS `unmovable_objects` (
3689  `ID` int(10) unsigned NOT NULL,
3690  `ObjectTypeID` int(10) unsigned NOT NULL,
3691  `TurnAngle` smallint(11) NOT NULL,
3692  `RootContainerID` int(10) unsigned DEFAULT NULL,
3693  `Durability` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point Less then 10 = object is damaged',
3694  `CreatedDurability` smallint(6) unsigned NOT NULL DEFAULT '0' COMMENT '2 digits after point, also acts as MaxDurability',
3695  `IsComplete` tinyint(1) unsigned NOT NULL,
3696  `GeoDataID` int(10) unsigned NOT NULL,
3697  `Slope` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'none or left or right',
3698  `OwnerID` int(10) unsigned DEFAULT NULL,
3699  `DroppedTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
3700  PRIMARY KEY (`ID`),
3701  KEY `FK_UnmovableTypeID` (`ObjectTypeID`) USING BTREE,
3702  KEY `FK_unmovable_objects_RootContainerID` (`RootContainerID`),
3703  KEY `byGeoID` (`GeoDataID`),
3704  KEY `FK_uownerID` (`OwnerID`),
3705  CONSTRAINT `FK_UnmovableObjectsObjectTypeID` FOREIGN KEY (`ObjectTypeID`) REFERENCES `objects_types` (`ID`),
3706  CONSTRAINT `FK_unmovable_objects_RootContainerID` FOREIGN KEY (`RootContainerID`) REFERENCES `containers` (`ID`),
3707  CONSTRAINT `FK_uownerID` FOREIGN KEY (`OwnerID`) REFERENCES `character` (`ID`)
3708) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3709
3710-- L'exportation de données n'était pas sélectionnée.
3711
3712
3713-- Export de la structure de table lif_1. unmovable_objects_claims
3714CREATE TABLE IF NOT EXISTS `unmovable_objects_claims` (
3715  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3716  `UnmovableObjectID` int(10) unsigned NOT NULL,
3717  `ClaimID` int(10) unsigned NOT NULL,
3718  PRIMARY KEY (`ID`),
3719  UNIQUE KEY `UNQ_UnmovableObjectID` (`UnmovableObjectID`),
3720  KEY `FK_unmovable_objects_claims_claims` (`ClaimID`),
3721  CONSTRAINT `FK_unmovable_objects_claims_claims` FOREIGN KEY (`ClaimID`) REFERENCES `claims` (`ID`),
3722  CONSTRAINT `FK_unmovable_objects_claims_unmovable_objects` FOREIGN KEY (`UnmovableObjectID`) REFERENCES `unmovable_objects` (`ID`)
3723) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3724
3725-- L'exportation de données n'était pas sélectionnée.
3726
3727
3728-- Export de la structure de table lif_1. unmovable_objects_server_id_ranges
3729CREATE TABLE IF NOT EXISTS `unmovable_objects_server_id_ranges` (
3730  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3731  `ServerID` int(10) unsigned NOT NULL,
3732  `RangeStartID` int(10) unsigned NOT NULL,
3733  `RangeEndID` int(10) unsigned NOT NULL,
3734  PRIMARY KEY (`ID`),
3735  KEY `IDX_RangeEndID` (`RangeEndID`),
3736  KEY `IDX_ServerID` (`ServerID`)
3737) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='unmovable_objects ID ranges assigned to servers. Should be accessed only by using p_issueIdRange_unmovable_objects';
3738
3739-- L'exportation de données n'était pas sélectionnée.
3740
3741
3742-- Export de la structure de table lif_1. unmovable_objects_server_id_ranges_lock
3743CREATE TABLE IF NOT EXISTS `unmovable_objects_server_id_ranges_lock` (
3744  `ID` tinyint(3) unsigned NOT NULL,
3745  `IsLocked` tinyint(3) unsigned NOT NULL,
3746  PRIMARY KEY (`ID`)
3747) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Dummy table for locking from p_issueIdRange_unmovable_objects. Do not store actual data, using only for internal needs';
3748
3749-- L'exportation de données n'était pas sélectionnée.
3750
3751
3752-- Export de la structure de table lif_1. working_containers
3753CREATE TABLE IF NOT EXISTS `working_containers` (
3754  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3755  `MovableObjectID` int(10) unsigned DEFAULT NULL,
3756  `UnmovableObjectID` int(10) unsigned DEFAULT NULL,
3757  `FinishTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3758  `ResultItemID` int(10) unsigned DEFAULT NULL,
3759  `InputSkillValue` float DEFAULT NULL,
3760  PRIMARY KEY (`ID`),
3761  UNIQUE KEY `UNQ_MovableObjectID` (`MovableObjectID`),
3762  UNIQUE KEY `UNQ_UnmovableObjectID` (`UnmovableObjectID`),
3763  KEY `FK_working_containers_objects_types` (`ResultItemID`),
3764  CONSTRAINT `FK_working_containers_movable_objects` FOREIGN KEY (`MovableObjectID`) REFERENCES `movable_objects` (`ID`),
3765  CONSTRAINT `FK_working_containers_objects_types` FOREIGN KEY (`ResultItemID`) REFERENCES `objects_types` (`ID`),
3766  CONSTRAINT `FK_working_containers_unmovable_objects` FOREIGN KEY (`UnmovableObjectID`) REFERENCES `unmovable_objects` (`ID`)
3767) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3768
3769-- L'exportation de données n'était pas sélectionnée.
3770
3771
3772-- Export de la structure de table lif_1. _data_version
3773CREATE TABLE IF NOT EXISTS `_data_version` (
3774  `TableName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
3775  `Value` int(10) unsigned NOT NULL,
3776  PRIMARY KEY (`TableName`)
3777) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3778
3779-- L'exportation de données n'était pas sélectionnée.
3780
3781
3782-- Export de la structure de table lif_1. _invalid_foreign_keys
3783CREATE TABLE IF NOT EXISTS `_invalid_foreign_keys` (
3784  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
3785  `TableName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
3786  `ColumnName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
3787  `ConstraintName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
3788  `ReferencedTableName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
3789  `ReferencedColumnName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
3790  `InvalidKeyCount` int(10) unsigned NOT NULL,
3791  `InvalidKeySql` varchar(2048) COLLATE utf8_unicode_ci NOT NULL,
3792  PRIMARY KEY (`ID`)
3793) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
3794
3795-- L'exportation de données n'était pas sélectionnée.
3796
3797
3798-- Export de la structure de procédure lif_1. _p_convertForestToNewAgeSettings
3799DELIMITER //
3800CREATE DEFINER=`root`@`localhost` PROCEDURE `_p_convertForestToNewAgeSettings`()
3801BEGIN
3802	UPDATE `forest` SET `AgeTime` = _countNewTreeAge(TreeType, AgeTime);
3803	call p_compactForestPatches();
3804
3805	TRUNCATE table tmp_old_tree_ages;
3806	insert into tmp_old_tree_ages
3807	(TreeType, YoungAge, MatureAge)
3808	select TreeType, YoungAge, MatureAge from tmp_new_tree_ages;
3809
3810	TRUNCATE table tmp_new_tree_ages;
3811END//
3812DELIMITER ;
3813/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
3814/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
3815/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;