Pd0fmNbd

1/* Store procedure creation */
2
3-- This section is used to drop current versions of the proceedures --
4IF OBJECT_ID ( 'SC_AddEntityRequires', 'P' ) IS NOT NULL 
5    DROP PROCEDURE SC_AddEntityRequires;
6GO
7
8IF OBJECT_ID ( 'SC_CreateEntity', 'P' ) IS NOT NULL 
9    DROP PROCEDURE SC_CreateEntity;
10GO
11
12IF OBJECT_ID ( 'SC_GetInfo', 'P' ) IS NOT NULL 
13    DROP PROCEDURE SC_GetInfo;
14GO
15
16IF OBJECT_ID ( 'SC_GetBuildableEntities', 'P' ) IS NOT NULL 
17    DROP PROCEDURE SC_GetBuildableEntities;
18GO
19
20IF OBJECT_ID ( 'SC_GetDependencies', 'P' ) IS NOT NULL 
21    DROP PROCEDURE SC_GetDependencies;
22GO
23
24IF OBJECT_ID ( 'SC_GetBuildOrderRec', 'P' ) IS NOT NULL 
25    DROP PROCEDURE SC_GetBuildOrderRec;
26GO
27
28IF OBJECT_ID ( 'SC_GetBuildOrder', 'P' ) IS NOT NULL 
29    DROP PROCEDURE SC_GetBuildOrder;
30GO
31
32IF OBJECT_ID ( 'SC_GetBuildTime', 'P' ) IS NOT NULL 
33    DROP PROCEDURE SC_GetBuildTime;
34GO
35
36IF OBJECT_ID ( 'SC_InsertUnitMorphType', 'P' ) IS NOT NULL 
37    DROP PROCEDURE SC_InsertUnitMorphType;
38GO
39
40-- temporary global table for BuildOrder
41
42IF OBJECT_ID('tempdb..##BuildOrderTable') IS NOT NULL
43	DROP TABLE ##BuildOrderTable
44GO
45
46CREATE TABLE ##BuildOrderTable (
47Name nvarchar(255) UNIQUE
48)
49GO
50-----
51
52
53-----
54-- Inserts an entity's build dependency into the database --
55-----
56CREATE PROCEDURE SC_AddEntityRequires(
57@baseName nvarchar( 255 ),
58@dependName nvarchar( 255 )
59)
60AS
61SET NOCOUNT ON
62DECLARE @baseID int
63DECLARE @dependID int
64SET @baseID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@baseName)
65SET @dependID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@dependName)
66IF @baseID IS NULL
67 BEGIN
68	PRINT 'SC_AddEntityRequires failed. Entity: ' + @baseName + ' does not exist!'
69 END
70IF @dependID IS NULL
71 BEGIN
72	PRINT 'SC_AddEntityRequires failed. Entity: ' + @dependName + ' does not exist!'
73 END
74ELSE
75 BEGIN
76	DECLARE @baseIDCheck int = (SELECT EntityID FROM Starcraft2DB.dbo.EntityRequired WHERE RequiredID=@dependID)
77	IF @baseIDCheck = @baseID
78	 BEGIN
79		PRINT 'SC_AddEntityRequires FAILED!!! Entry (' + @baseName + ', ' + @dependName + ') already exists.'
80	 END
81	ELSE
82	 BEGIN
83		INSERT INTO Starcraft2DB.dbo.EntityRequired( EntityID, RequiredID )
84		VALUES (@baseID, @dependID)
85	 END
86 END
87GO
88
89
90--
91-- Inserts a new entity into the database --
92--
93CREATE PROCEDURE SC_CreateEntity(
94@name nvarchar( 255 ),
95@unitType nvarchar( 255 ),
96@buildTime float,
97@mineralCost int,
98@vespeneGas int,
99@attackType nvarchar( 255 )
100)
101AS
102SET NOCOUNT ON
103DECLARE @entityID int
104DECLARE @attackTypeID int
105SET @attackTypeID = (SELECT ID FROM Starcraft2DB.dbo.AttackType WHERE Name=@attackType)
106
107-- insert into Entities table
108SET @entityID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@name)
109IF @entityID IS NOT NULL
110	PRINT 'SC_CreateEntity FAILED!!! Entity: ' + @name + ' already exist! Why are you trying to create it again?!'
111
112INSERT INTO Starcraft2DB.dbo.Entities( Name, EntityTypeID )
113SELECT @name, ID FROM Starcraft2DB.dbo.EntityType WHERE Name='Unit'
114
115-- insert into secondary tables
116SET @entityID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@name)
117INSERT INTO Starcraft2DB.dbo.EntityBuildProps( BuildTime, MineralCost, VespeneGas, EntityID )
118SELECT @buildTime, @mineralCost, @vespeneGas, ID FROM Starcraft2DB.dbo.Entities WHERE Name=@name
119
120IF @entityID IS NULL 
121	PRINT 'SC_CreateEntity failed. Entity: ' + @name + ' does not exist!'
122ELSE
123	IF @attackTypeID IS NOT NULL
124	 BEGIN
125		INSERT INTO Starcraft2DB.dbo.EntityAttackTypes( EntityID, AttackTypeID )
126		SELECT @entityID, @attackTypeID
127	 END
128GO
129
130-----
131-- Gets an entity's basic info (build time and resource cost). Takes in the name of the entity that we are interested in --
132-----
133CREATE PROCEDURE SC_GetInfo(
134@unitName nvarchar( 255 )
135)
136AS
137SET NOCOUNT ON
138DECLARE @unitID int
139SET @unitID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@unitName) --find the ID of the unit--
140IF @unitID IS NOT NULL	--if we find the unit
141(
142	SELECT Name, BuildTime, MineralCost, VespeneGas FROM Starcraft2DB.dbo.Entities
143	JOIN Starcraft2DB.dbo.EntityBuildProps ON Starcraft2DB.dbo.EntityBuildProps.EntityID = Starcraft2DB.dbo.Entities.ID
144	WHERE ID = @unitID
145)
146ELSE
147	PRINT 'SC_GetInfo failed: ' + @unitName + ' does not exist!'
148GO
149
150
151-----
152-- Given an input entity name, outputs all the entities that it can produce (e.g. a barrack can produce marines and other stuff) --
153-----
154CREATE PROCEDURE SC_GetBuildableEntities(
155@name nvarchar( 255 )
156)
157AS
158SET NOCOUNT ON
159DECLARE @id int
160SET @id = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@name) --check if the entity exists--
161IF @id IS NOT NULL	--if we find the unit
162(
163	SELECT Name AS BuildableEntities FROM Starcraft2DB.dbo.Entities
164	JOIN Starcraft2DB.dbo.EntityBuildFrom ON Starcraft2DB.dbo.EntityBuildFrom.EntityID = Starcraft2DB.dbo.Entities.ID
165	WHERE BuildFromID = @id
166)
167ELSE
168	PRINT 'SC_GetBuildableEntities failed: ' + @name + ' does not exist!'
169GO
170
171
172-----
173-- Gets an entity's dependencies --
174-----
175CREATE PROCEDURE SC_GetDependencies(
176@name nvarchar( 255 )
177)
178AS
179SET NOCOUNT ON
180DECLARE @id int
181SET @id = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@name) --check if the entity exists--
182IF @id IS NOT NULL	--if we find the unit
183(
184	-- get the names of all entities in the "BuildFromID" column, whose corresponding "EntityID" entry is the input entity
185	SELECT Name AS Dependencies FROM Starcraft2DB.dbo.EntityRequired
186	JOIN Starcraft2DB.dbo.Entities ON Starcraft2DB.dbo.Entities.ID = Starcraft2DB.dbo.EntityRequired.RequiredID 
187	WHERE EntityID=@id
188)
189ELSE
190	PRINT 'SC_GetDependencies failed: ' + @name + ' does not exist!'
191GO
192
193
194-- Recursive procedure called by SC_GetBuildOrder. Should not be called directly! --
195CREATE PROCEDURE SC_GetBuildOrderRec(
196@name nvarchar( 255 )
197)
198AS
199SET NOCOUNT ON
200
201-- temp table --
202DECLARE @tempTable TABLE (
203RowNumber int IDENTITY,
204Name nvarchar(255)
205)
206
207-- get the dependencies for this entity and store it in a temporary table--
208INSERT INTO @tempTable
209EXECUTE SC_GetDependencies @name
210
211-- insert the values of the temp table into the global BuildOrder table
212DECLARE @tempName nvarchar(255)
213DECLARE @i int = 1
214DECLARE @maxRows int
215
216SET @maxRows = (SELECT COUNT(*) FROM @tempTable) --number of rows in the temp table
217WHILE @i <= @maxRows --go through each entry in the temp table
218	BEGIN
219
220	-- All entries of the temp table are the input entity's dependencies. Add each entry into the build order table only if it doesn't already exist--
221	SET @tempName = (SELECT Name FROM @tempTable WHERE RowNumber=@i) --get the current row--
222	IF @tempName NOT IN (SELECT Name FROM ##BuildOrderTable WHERE Name=@tempName) --if the current entry is not in the build order table--
223		BEGIN
224		--put it inside the build order table
225		INSERT INTO ##BuildOrderTable( Name ) 
226		VALUES( @tempName )
227
228		--check if it has its own dependencies--
229		EXECUTE SC_GetBuildOrderRec @tempName
230		END
231	SET @i = @i + 1 --increment the counter--
232	END
233GO
234
235
236-----
237-- Gets an entity's build order --
238-----
239CREATE PROCEDURE SC_GetBuildOrder(
240@name nvarchar( 255 )
241)
242AS
243SET NOCOUNT ON
244
245-- delete current entries of table --
246DELETE ##BuildOrderTable
247
248DECLARE @id int = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@name) --check if the entity exists--
249IF @id IS NULL	--if we find the unit
250	PRINT 'SC_GetBuildOrder failed: ' + @name + ' does not exist!'
251ELSE
252	BEGIN
253	EXECUTE SC_GetBuildOrderRec @name --call the recursive function--
254	SELECT Name FROM ##BuildOrderTable --display the table--
255	END	
256GO
257
258-----
259-- Gets an entity's build time --
260-----
261CREATE PROCEDURE SC_GetBuildTime(
262@name nvarchar( 255 )
263)
264AS
265/*SET NOCOUNT ON
266
267DECLARE @tempTable TABLE (
268Name nvarchar(255)
269)
270
271-- get the dependencies for this entity and store it in a temporary table--
272INSERT INTO @tempTable
273EXECUTE SC_GetBuildOrder @name
274
275-- insert the entity into the temp table (to add the build time later)
276INSERT INTO @tempTable( Name )
277VALUES( @name )
278
279-- add up the build times of the entity --
280SELECT SUM(BuildTime) As TotalBuildTime FROM Entities
281JOIN Starcraft2DB.dbo.Entities ON Starcraft2DB.dbo.Entities.Name = @tempTable.Name 
282JOIN Starcraft2DB.dbo.EntityBuildProps ON Starcraft2DB.dbo.EntityBuildProps.EntityID = ID */
283GO
284
285-----
286-- Inserts a units morph type --
287-----
288CREATE PROCEDURE SC_InsertUnitMorphType(
289@baseName nvarchar( 255 ),
290@morphName nvarchar( 255 )
291)
292AS
293
294DECLARE @baseID int
295DECLARE @morphID int
296SET @baseID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@baseName)
297SET @morphID = (SELECT ID FROM Starcraft2DB.dbo.Entities WHERE Name=@morphName)
298IF @baseID IS NULL 
299	PRINT 'SC_InsertUnitMorphType failed. Entity: ' + @baseName + ' does not exist!'
300ELSE IF @morphID IS NULL
301	PRINT 'SC_InsertUnitMorphType failed. Entity: ' + @morphName + ' does not exist!'
302ELSE
303 BEGIN
304	DECLARE @baseIDCheck int
305	SET @baseIDCheck = (SELECT BaseID FROM Starcraft2DB.dbo.EntityMorphs WHERE MorphedID=@morphID)
306	IF @baseIDCheck IS NOT NULL
307		PRINT 'SC_InsertUnitMorphType FAILED!!! Entity: ' + @morphName + ' does not exist!'
308
309	INSERT INTO Starcraft2DB.dbo.EntityMorphs( BaseID, MorphedID )
310	VALUES (@baseID, @morphID)
311 END
312GO
313
314
315/* @to_do
316	- "specify what unit a building can build"
317	- " show me the amount of time it will take for me to get my first unit/building of the desired type"
318*/
319
320
321-----
322-- mini-driver, should eventually be deleted forever --
323
324EXECUTE SC_CreateEntity "Command Center", "Building", 120, 400, 0, NULL
325EXECUTE SC_CreateEntity "Barracks", "Building", 20, 40, 40, NULL
326EXECUTE SC_CreateEntity "Armory", "Building", 20, 40, 40, NULL
327EXECUTE SC_CreateEntity "StarportDependency", "Building", 20, 40, 40, NULL
328EXECUTE SC_CreateEntity "Starport", "Building", 20, 40, 40, NULL
329EXECUTE SC_CreateEntity "SCV", "Unit", 40, 40, 40, "Air"
330EXECUTE SC_CreateEntity "Marine", "Unit", 40, 40, 40, "Ground"
331EXECUTE SC_AddEntityRequires "SCV", "Command Center"
332EXECUTE SC_AddEntityRequires "Marine", "Barracks"
333EXECUTE SC_AddEntityRequires "Barracks", "Armory"
334EXECUTE SC_AddEntityRequires "Barracks", "Starport"
335EXECUTE SC_AddEntityRequires "Starport", "Armory"
336EXECUTE SC_AddEntityRequires "Barracks", "StarportDependency"
337EXECUTE SC_InsertUnitMorphType "Marine", "Command Center"
338--EXECUTE SC_GetInfo "SCV" 
339--EXECUTE SC_GetInfo "Command Center" 
340--EXECUTE SC_GetInfo "NOTEXISTS" 
341--EXECUTE SC_GetBuildableEntities "Command Center" 
342--EXECUTE SC_GetDependencies "Command Center" 
343--GO
344
345EXECUTE SC_GetBuildOrder "Marine"
346/*SELECT * FROM Starcraft2DB.dbo.Entities
347SELECT * FROM Starcraft2DB.dbo.EntityAttackTypes
348SELECT * FROM Starcraft2DB.dbo.EntityBuildProps
349SELECT * FROM Starcraft2DB.dbo.EntityBuildFrom
350SELECT * FROM Starcraft2DB.dbo.EntityMorphs*/
351GO