1. Alvis Mikovs
  2. Narita

Source

Narita / data.sql

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550

CREATE SCHEMA narita;

GO



CREATE -- DROP
TABLE narita.characters (
	characterID int PRIMARY KEY,
	keyID int NOT NULL,
	vCode char(64) NOT NULL,
	name varchar(100) NOT NULL,
	balance money NOT NULL,
	corporationID int NOT NULL,
	currentTime smalldatetime NOT NULL,
	cachedUntil smalldatetime NOT NULL
);

GO



CREATE -- DROP
TABLE narita.characterSkills (
	characterID int REFERENCES narita.characters,
	typeID int REFERENCES invTypes,
	skillpoints int NOT NULL,
	level tinyint NOT NULL,
	published bit NOT NULL,
	PRIMARY KEY (characterID, typeID)
);

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateCharacters @characterID int, @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (data xml);
	EXEC ('INSERT INTO #tmp SELECT * FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_BLOB) AS data;');
	DECLARE @xml xml = (SELECT data FROM #tmp);
	DROP TABLE #tmp;
	DECLARE @xmldoc int;
	EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xml;
	UPDATE narita.characters
	SET
		narita.characters.name = t.name,
		narita.characters.balance = t.balance,
		narita.characters.corporationID = t.corporationID,
		narita.characters.currentTime = t.currentTime,
		narita.characters.cachedUntil = t.cachedUntil
	FROM OPENXML (@xmldoc, '//result', 2)
	WITH (characterID int, name varchar(100), balance money, corporationID int, currentTime smalldatetime '/eveapi/currentTime', cachedUntil smalldatetime '/eveapi/cachedUntil') AS t
	WHERE narita.characters.characterID = t.characterID;
	MERGE narita.characterSkills AS t
	USING (
		SELECT t.* FROM (
			SELECT t.*
			FROM OPENXML (@xmldoc, '//rowset[@name="skills"]/row')
			WITH (characterID int '/eveapi/result/characterID', typeID int, skillpoints int, level tinyint, published bit) AS t
		) AS t
		INNER JOIN invTypes AS it ON it.typeID = t.typeID
	) AS s ON
		s.characterID = t.characterID AND
		s.typeID = t.typeID
	WHEN MATCHED THEN UPDATE SET t.skillpoints = s.skillpoints, t.level = s.level, t.published = s.published
	WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (s.characterID, s.typeID, s.skillpoints, s.level, s.published)
	WHEN NOT MATCHED BY SOURCE AND t.characterID = @characterID THEN DELETE;
	EXEC sp_xml_removedocument @xmldoc;
END;

GO



CREATE -- DROP
TABLE narita.standings (
	characterID int REFERENCES narita.characters,
	agentID int,
	corporationID int,
	factionID int,
	standing decimal(4,2) NOT NULL,
	currentTime smalldatetime NOT NULL,
	cachedUntil smalldatetime NOT NULL
	UNIQUE (characterID, agentID, corporationID, factionID)
);

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateStandings @characterID int, @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (data xml);
	EXEC ('INSERT INTO #tmp SELECT * FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_BLOB) AS data;');
	DECLARE @xml xml = (SELECT data FROM #tmp);
	DROP TABLE #tmp;
	DECLARE @xmldoc int;
	EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xml;
	DELETE FROM narita.standings
	WHERE characterID = @characterID;
	INSERT INTO narita.standings
	SELECT @characterID, a.agentID, c.corporationID, f.factionID, t.standing, t.currentTime, t.cachedUntil
	FROM (
		SELECT t.*
		FROM OPENXML (@xmldoc, '//rowset/row')
		WITH (fromID int, standing decimal(4,2), currentTime smalldatetime '/eveapi/currentTime', cachedUntil smalldatetime '/eveapi/cachedUntil') AS t
	) AS t
	LEFT JOIN agtAgents AS a ON a.agentID = t.fromID
	LEFT JOIN crpNPCCorporations AS c ON c.corporationID = t.fromID
	LEFT JOIN chrFactions AS f ON f.factionID = t.fromID;
	EXEC sp_xml_removedocument @xmldoc;
END;

GO



CREATE -- DROP
TABLE narita.assets (
	characterID int REFERENCES narita.characters,
	itemID bigint,
	locationID bigint,
	typeID int NOT NULL REFERENCES invTypes,
	quantity int NOT NULL,
	flag int NOT NULL,
	singleton bit NOT NULL,
	parentItemID bigint,
	currentTime smalldatetime NOT NULL,
	cachedUntil smalldatetime NOT NULL
	PRIMARY KEY (characterID, itemID)
);

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateAssets @characterID int, @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (data xml);
	EXEC ('INSERT INTO #tmp SELECT * FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_BLOB) AS data;');
	DECLARE @xml xml = (SELECT data FROM #tmp);
	DROP TABLE #tmp;
	DECLARE @xmldoc int;
	EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xml;
	DELETE FROM narita.assets
	WHERE characterID = @characterID;
	INSERT INTO narita.assets
	SELECT @characterID, t.*
	FROM OPENXML (@xmldoc, '//row')
	WITH (itemID bigint, locationID bigint, typeID int, quantity int, flag int, singleton bit, parentItemID bigint '../../@itemID', currentTime smalldatetime '/eveapi/currentTime', cachedUntil smalldatetime '/eveapi/cachedUntil') AS t
	INNER JOIN invTypes AS it ON it.typeID = t.typeID;
	EXEC sp_xml_removedocument @xmldoc;
END;

GO



CREATE -- DROP
TABLE narita.wallet (
	characterID int REFERENCES narita.characters,
	transactionDateTime smalldatetime NOT NULL,
	transactionID bigint NOT NULL,
	quantity int NOT NULL,
	typeID int NOT NULL REFERENCES invTypes,
	price money NOT NULL,
	clientID int NOT NULL,
	stationID int NOT NULL REFERENCES staStations,
	transactionType char(1) NOT NULL,
	transactionFor char(1) NOT NULL,
	journalTransactionID bigint NOT NULL,
	currentTime smalldatetime NOT NULL,
	cachedUntil smalldatetime NOT NULL,
	PRIMARY KEY (characterID, transactionID)
);


GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateWallet @characterID int, @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (data xml);
	EXEC ('INSERT INTO #tmp SELECT * FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_BLOB) AS data;');
	DECLARE @xml xml = (SELECT data FROM #tmp);
	DROP TABLE #tmp;
	DECLARE @xmldoc int;
	EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xml;
	WITH
		t AS (
			SELECT
				@characterID AS characterID,
				t.*
			FROM OPENXML (@xmldoc, '//row')
			WITH (transactionDateTime smalldatetime, transactionID bigint, quantity int, typeID int, price money, clientID int, stationID int, transactionType char(1), transactionFor char(1), journalTransactionID bigint, currentTime smalldatetime '/eveapi/currentTime', cachedUntil smalldatetime '/eveapi/cachedUntil') AS t
		)
	INSERT INTO narita.wallet
	SELECT t.* FROM t
	INNER JOIN invTypes AS it ON it.typeID = t.typeID
	LEFT JOIN narita.wallet AS w ON
		w.characterID = t.characterID AND
		w.transactionID = t.transactionID
	WHERE w.transactionID IS NULL;
	EXEC sp_xml_removedocument @xmldoc;
END;

GO



CREATE -- DROP
TABLE narita.orders (
	characterID int REFERENCES narita.characters,
	orderID bigint,
	charID int NOT NULL,
	stationID int NOT NULL REFERENCES staStations,
	volEntered int NOT NULL,
	volRemaining int NOT NULL,
	minVolume int NOT NULL,
	orderState bit NOT NULL,
	typeID int NOT NULL REFERENCES invTypes,
	range int NOT NULL,
	accountKey int NOT NULL,
	duration smallint NOT NULL,
	escrow money NOT NULL,
	price money NOT NULL,
	bid bit NOT NULL,
	issued smalldatetime NOT NULL,
	currentTime smalldatetime NOT NULL,
	cachedUntil smalldatetime
	PRIMARY KEY (characterID, orderID)
);

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateOrdersAPI @characterID int, @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (data xml);
	EXEC ('INSERT INTO #tmp SELECT * FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_BLOB) AS data;');
	DECLARE @xml xml = (SELECT data FROM #tmp);
	DROP TABLE #tmp;
	DECLARE @xmldoc int;
	EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xml;
	DELETE FROM narita.orders
	WHERE characterID = @characterID;
	INSERT INTO narita.orders
	SELECT @characterID, t.*
	FROM OPENXML (@xmldoc, '//row')
	WITH (orderID bigint, charID int, stationID int, volEntered int, volRemaining int, minVolume int, orderState bit, typeID int, range int, accountKey int, duration smallint, escrow money, price money, bid bit, issued smalldatetime, currentTime smalldatetime '/eveapi/currentTime', cachedUntil smalldatetime '/eveapi/cachedUntil') AS t;
	EXEC sp_xml_removedocument @xmldoc;
END;

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateOrdersFile @characterID int, @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (orderID varchar(100), typeID varchar(100), charID varchar(100), charName varchar(100), regionID varchar(100), regionName varchar(100), stationID varchar(100), stationName varchar(100), range varchar(100), bid varchar(100), price varchar(100), volEntered varchar(100), volRemaining varchar(100), issueDate varchar(100), orderState varchar(100), minVolume varchar(100), accountID varchar(100), duration varchar(100), isCorp varchar(100), solarSystemID varchar(100), solarSystemName varchar(100), escrow varchar(100));
	EXEC('BULK INSERT #tmp FROM ''' + @file + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR =	''\n'', FIRSTROW = 2);');
	DELETE FROM narita.orders
	WHERE characterID = @characterID;
	INSERT INTO narita.orders
	SELECT
		@characterID,
		CAST(t.orderID AS bigint) AS orderID,
		CAST(t.charID AS int) AS charID,
		CAST(t.stationID AS int) AS stationID,
		CAST(t.volEntered AS int) AS volEntered,
		CAST(CAST(t.volRemaining AS decimal) AS int) AS volRemaining,
		CAST(t.minVolume AS int) AS minVolume,
		CAST(LTRIM(t.orderState) AS bit) AS orderState,
		CAST(t.typeID AS int) AS typeID,
		CAST(t.range AS int) AS range,
		1000 AS accountKey,
		CAST(t.duration AS smallint) AS duration,
		CAST(t.escrow AS money) AS escrow,
		CAST(t.price AS money) AS price,
		CAST(t.bid AS bit) AS bid,
		CAST(t.issueDate AS smalldatetime) AS issued,
		GETUTCDATE(),
		COALESCE((SELECT MAX(cachedUntil) FROM narita.orders), GETUTCDATE()) AS cachedUntil
	FROM #tmp AS t;
	DROP TABLE #tmp;
END;

GO



CREATE -- DROP
TABLE narita.market (
	date date,
	orderID bigint,
	stationID int NOT NULL REFERENCES staStations,
	volEntered int NOT NULL,
	volRemaining int NOT NULL,
	minVolume int NOT NULL,
	typeID int NOT NULL REFERENCES invTypes,
	range int NOT NULL,
	duration smallint NOT NULL,
	price money NOT NULL,
	bid bit NOT NULL,
	issued smalldatetime NOT NULL,
	reportedBy int NOT NULL,
	reportedTime smalldatetime NOT NULL,
	PRIMARY KEY (date DESC, orderID)
);

CREATE INDEX IX_naritaMarket_marketLatestEffectiveOrders ON narita.market (stationID, bid, range) INCLUDE (typeID, reportedTime);
CREATE INDEX IX_naritaMarket_marketCustomMedianDayPrice ON narita.market (typeID, date DESC, bid, stationID) INCLUDE (price);

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateMarket @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (orderid varchar(100), regionid varchar(100), systemid varchar(100), stationid varchar(100), typeid varchar(100), bid varchar(100), price varchar(100), minvolume varchar(100), volremain varchar(100), volenter varchar(100), issued varchar(100), duration varchar(100), range varchar(100), reportedby varchar(100), reportedtime varchar(100));
	EXEC('BULK INSERT #tmp FROM ''' + @file + ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = ''","'', ROWTERMINATOR = ''\n'');');
	MERGE narita.market AS t
	USING (
		SELECT t.*
		FROM (
			SELECT
				t.*,
				ROW_NUMBER() OVER(PARTITION BY t.date, t.orderID ORDER BY t.reportedTime DESC) AS rn
			FROM (
				SELECT
					CAST(CASE WHEN LEN(t.reportedtime) = 20 THEN LEFT(t.reportedtime, 19) ELSE LEFT(t.reportedtime, CHARINDEX('.', t.reportedtime) - 1) END AS date) AS date,
					CAST(RIGHT(t.orderid, LEN(t.orderid) - 1) AS bigint) AS orderID,
					CAST(t.stationid AS int) AS stationID,
					CAST(t.volenter AS int) AS volEntered,
					CAST(t.volremain AS int) AS volRemaining,
					CAST(t.minvolume AS int) AS minVolume,
					CAST(t.typeid AS int) AS typeID,
					CAST(t.range AS int) AS range,
					CASE WHEN LEN(t.duration) = 7 THEN 0 ELSE CAST(REPLACE(REPLACE(t.duration, ' days, 0:00:00', ''), ' day, 0:00:00', '') AS smallint) END AS duration,
					CAST(t.price AS money) AS price,
					CAST(t.bid AS bit) AS bid,
					CAST(CASE WHEN CHARINDEX('.', t.issued) > 0 THEN LEFT(t.issued, CHARINDEX('.', t.issued) - 1) ELSE t.issued END AS smalldatetime) AS issued,
					CAST(t.reportedby AS int) AS reportedBy,
					CAST(CASE WHEN LEN(t.reportedtime) = 20 THEN LEFT(t.reportedtime, 19) ELSE LEFT(t.reportedtime, CHARINDEX('.', t.reportedtime) - 1) END AS smalldatetime) AS reportedTime
				FROM #tmp AS t
				WHERE t.price NOT LIKE '%e%'
			) AS t
			INNER JOIN staStations AS s ON s.stationID = t.stationID
			INNER JOIN invTypes AS ty ON ty.typeID = t.typeID
		) AS t
		WHERE t.rn = 1
	) AS s ON
		s.date = t.date AND
		s.orderID = t.orderID
	WHEN MATCHED AND t.reportedTime < s.reportedTime THEN UPDATE SET t.stationID = s.stationID, t.volEntered = s.volEntered, t.volRemaining = s.volRemaining, t.minVolume = s.minVolume, t.typeID = s.typeID, t.range = s.range, t.duration = s.duration, t.price = s.price, t.bid = s.bid, t.issued = s.issued, t.reportedBy = s.reportedBy, t.reportedTime = s.reportedTime
	WHEN NOT MATCHED THEN INSERT VALUES (date, orderID, stationID, volEntered, volRemaining, minVolume, typeID, range, duration, price, bid, issued, reportedBy, reportedTime);
	DROP TABLE #tmp;
	DELETE FROM narita.marketCache WHERE reportedTime < (SELECT DATEADD(DAY, 1, MAX(m.date)) FROM narita.market AS m);
	/*
	WITH
		t AS (
			SELECT
				m.date,
				m.orderID,
				m.volRemaining,
				ROW_NUMBER() OVER(PARTITION BY m.orderID ORDER BY m.date) AS rn
			FROM narita.market AS m
		)
	DELETE narita.market
	FROM narita.market
	INNER JOIN (
		SELECT
			t2.date,
			t2.orderID
		FROM t AS t1
		INNER JOIN t AS t2 ON
			t2.orderID = t1.orderID AND
			t2.rn = t1.rn + 1
		WHERE t1.volRemaining < t2.volRemaining
	) AS t ON
		t.date = narita.market.date AND
		t.orderID = narita.market.orderID;
	*/
END;

GO



CREATE -- DROP
TABLE narita.marketCache (
	orderID bigint PRIMARY KEY,
	stationID int NOT NULL REFERENCES staStations,
	volEntered int NOT NULL,
	volRemaining int NOT NULL,
	minVolume int NOT NULL,
	typeID int NOT NULL REFERENCES invTypes,
	range int NOT NULL,
	duration smallint NOT NULL,
	price money NOT NULL,
	bid bit NOT NULL,
	issued smalldatetime NOT NULL,
	reportedTime smalldatetime NOT NULL
);

GO



CREATE -- DROP
TABLE narita.marketCacheUpdates (
	stationID int NOT NULL REFERENCES staStations,
	typeID int NOT NULL REFERENCES invTypes,
	updateTime smalldatetime NOT NULL,
	PRIMARY KEY (stationID, typeID)
);

GO



CREATE -- ALTER -- DROP
PROCEDURE narita.updateMarketCache @file varchar(1000) AS
BEGIN;
	CREATE TABLE #tmp (price varchar(100), volremaining varchar(100), typeid varchar(100), range varchar(100), orderid varchar(100), volentered varchar(100), minvolume varchar(100), bid varchar(100), issued varchar(100), duration varchar(100), stationid varchar(100), regionid varchar(100), solarSystemid varchar(100), jumps varchar(100));
	EXEC('BULK INSERT #tmp FROM ''' + @file + ''' WITH (FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'');');
	MERGE narita.marketCache AS t
	USING (
		SELECT t.*
		FROM (
			SELECT
				t.*,
				ROW_NUMBER() OVER(PARTITION BY t.orderID ORDER BY t.reportedTime DESC) AS rn
			FROM (
				SELECT
					CAST(t.orderid AS bigint) AS orderID,
					CAST(t.stationid AS int) AS stationID,
					CAST(CAST(t.volentered AS decimal) AS int) AS volEntered,
					CAST(CAST(t.volremaining AS decimal) AS int) AS volRemaining,
					CAST(t.minvolume AS int) AS minVolume,
					CAST(t.typeid AS int) AS typeID,
					CAST(t.range AS int) AS range,
					CAST(t.duration AS smallint) AS duration,
					CAST(t.price AS money) AS price,
					CAST(t.bid AS bit) AS bid,
					CAST(CASE WHEN CHARINDEX('.', t.issued) > 0 THEN LEFT(t.issued, CHARINDEX('.', t.issued) - 1) ELSE t.issued END AS smalldatetime) AS issued,
					CAST(GETUTCDATE() AS smalldatetime) AS reportedTime
				FROM #tmp AS t
			) AS t
			INNER JOIN staStations AS s ON s.stationID = t.stationID
			INNER JOIN invTypes AS ty ON ty.typeID = t.typeID
		) AS t
		WHERE t.rn = 1
	) AS s ON s.orderID = t.orderID
	WHEN MATCHED AND t.reportedTime < s.reportedTime THEN UPDATE SET t.stationID = s.stationID, t.volEntered = s.volEntered, t.volRemaining = s.volRemaining, t.minVolume = s.minVolume, t.typeID = s.typeID, t.range = s.range, t.duration = s.duration, t.price = s.price, t.bid = s.bid, t.issued = s.issued, t.reportedTime = s.reportedTime
	WHEN NOT MATCHED THEN INSERT VALUES (orderID, stationID, volEntered, volRemaining, minVolume, typeID, range, duration, price, bid, issued, reportedTime);
	DROP TABLE #tmp;
	DELETE narita.marketCache
	FROM narita.marketCache AS mc
	INNER JOIN (
		SELECT
			mc.typeID,
			MAX(mc.reportedTime) AS maxReportedTime
		FROM narita.marketCache AS mc
		GROUP BY mc.typeID
	) AS t ON
		t.typeID = mc.typeID AND
		t.maxReportedTime > mc.reportedTime;
	UPDATE narita.orders
	SET narita.orders.volRemaining = mc.volRemaining, narita.orders.price = mc.price, narita.orders.issued = mc.issued
	FROM narita.marketCache AS mc
	WHERE mc.orderID = narita.orders.orderID AND mc.reportedTime > narita.orders.currentTime;
END;

GO



CREATE -- DROP
TABLE narita.marketSolarSystemRange (
	fromSolarSystemID int,
	toSolarSystemID int,
	jumps tinyint,
	PRIMARY KEY (fromSolarSystemID, toSolarSystemID)
);

GO

DECLARE @result TABLE (
	solarSystemID int PRIMARY KEY,
	jumps tinyint
);
DECLARE c CURSOR FOR
	SELECT ss.solarSystemID
	FROM mapSolarSystems AS ss
OPEN c;
DECLARE @fromSolarSystemID int;
FETCH NEXT FROM c INTO @fromSolarSystemID;
WHILE @@FETCH_STATUS = 0
BEGIN;
	DELETE FROM @result;
	DECLARE @jumps tinyint = 0;
	DECLARE @regionID int = (SELECT ss.regionID FROM mapSolarSystems AS ss WHERE ss.solarSystemID = @fromSolarSystemID);
	INSERT INTO @result VALUES (@fromSolarSystemID, NULL);
	WHILE EXISTS(
		SELECT TOP 1 r.solarSystemID
		FROM @result AS r
		WHERE r.jumps IS NULL
	)
	BEGIN;
		UPDATE @result
		SET jumps = @jumps
		WHERE jumps IS NULL;
		INSERT INTO @result
		SELECT DISTINCT
			ssj.toSolarSystemID,
			NULL
		FROM mapSolarSystemJumps AS ssj
		INNER JOIN @result AS rf ON
			rf.solarSystemID = ssj.fromSolarSystemID AND
			rf.jumps = @jumps
		LEFT JOIN @result AS rt ON rt.solarSystemID = ssj.toSolarSystemID
		WHERE
			ssj.toRegionID = @regionID AND
			rt.solarSystemID IS NULL;
		SET @jumps = @jumps + 1;
	END;
	INSERT INTO narita.marketSolarSystemRange
	SELECT
		@fromSolarSystemID,
		r.solarSystemID AS toSolarSystemID,
		r.jumps
	FROM @result AS r;
	FETCH NEXT FROM c INTO @fromSolarSystemID;
END;
CLOSE c;
DEALLOCATE c;

GO