]> ruin.nu Git - ndwebbie.git/commit
Change database structure for planets and alliances + PG 8.4 changes
authorMichael Andreen <harv@ruin.nu>
Tue, 28 Jul 2009 00:51:51 +0000 (02:51 +0200)
committerMichael Andreen <harv@ruin.nu>
Sat, 15 Aug 2009 14:40:26 +0000 (16:40 +0200)
commitb6d8351387cf06b88e362a458aad1e0982e575dd
treee28855a57666776514dca6d51f5726e9f4bc12f7
parent6c73f6ddd47b3274ee7fa784f74823591d19dbc6
Change database structure for planets and alliances + PG 8.4 changes

Change the id for planets from id to pid. Similar change for alliances where
id is changed to aid. This makes them unique in the database, which
makes it possible to replace several 'JOIN .. ON' with 'JOIN .. USING'
or 'NATURAL JOIN'.

Also, use the real alliance name as foreign key instead of the
surrogate key.

Some bigger queries are moved into views, mainly defense calls and
alliance resource estimation.

Together with this, use some new PostgreSQL 8.4 features.

* CTE and Window function for alliance resource estimate, so only the
  top60 planets are picked.

* Use the new array_agg() instead of the custom array_accum()

Also, since 0000 is not a valid year anymore, raid.js is changed to use
0001 instead.
24 files changed:
database/noperl.sql
database/planets.sql [new file with mode: 0644]
lib/NDWeb/Auth/User.pm
lib/NDWeb/Controller/Alliances.pm
lib/NDWeb/Controller/Calls.pm
lib/NDWeb/Controller/CovOp.pm
lib/NDWeb/Controller/Graphs.pm
lib/NDWeb/Controller/Intel.pm
lib/NDWeb/Controller/JSRPC.pm
lib/NDWeb/Controller/Members.pm
lib/NDWeb/Controller/Raids.pm
lib/NDWeb/Controller/Rankings.pm
lib/NDWeb/Controller/Root.pm
lib/NDWeb/Controller/Stats.pm
lib/NDWeb/Controller/TextExport.pm
lib/NDWeb/Controller/Users.pm
lib/NDWeb/Include.pm
root/src/intel/planet.tt2
root/static/js/raid.js
scripts/createretal.pl
scripts/ndrank.pl
scripts/parsealliances.pl
scripts/parseplanets.pl
scripts/scans.pl