excalibur.mysql.py

# This file is part of Merlin.
# Merlin is the Copyright (C)2008,2009,2010 of Robin K. Hansen, Elliot Rosemarine, Andreas Jacobsen.

# Individual portions may be copyright by individual contributors, and
# are included in this collective work with permission of the copyright
# owners.

# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
 
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
 
import datetime, re, sys, time, traceback, urllib2, shutil,os
os.chdir("/var/www/vhosts/pa-rainbows.com/httpdocs/merlin");

from sqlalchemy.sql import text, bindparam
from Core.config import Config
from Core.paconf import PA
from Core.string import decode, excaliburlog
from Core.db import true, false, session
from Core.maps import Updates, galpenis, apenis
from Core.maps import galaxy_temp, planet_temp, alliance_temp, planet_new_id_search, planet_old_id_search
from ConfigParser import ConfigParser as CP



def skip_last(iterator):
    prev = next(iterator)
    for item in iterator:
	yield prev
	prev = item

# From http://www.diveintopython.net/http_web_services/etags.html
class DefaultErrorHandler(urllib2.HTTPDefaultErrorHandler):
    def http_error_default(self, req, fp, code, msg, headers):
        result = urllib2.HTTPError(req.get_full_url(), code, msg, headers, fp)
        result.status = code
        return result 




# Config files (absolute or relative paths) for all bots to be updated by this excalibur
configs = ['merlin.cfg']

bots = []
prefixes = []
for config in configs:
    cp = CP()
    cp.optionxform = str
    cp.read(config)
    bots += [cp]
    prefixes += [cp.get("DB", "prefix")]

if len(sys.argv) > 1:
    Config.set("URL", "dumps", sys.argv[1])
excaliburlog("Dumping from %s" %(Config.get("URL", "dumps"),))

# Get the previous tick number!
last_tick = Updates.current_tick()
midnight = Updates.midnight_tick() == last_tick
hour = bindparam("hour",datetime.datetime.utcnow().hour)
timestamp = bindparam("timestamp",datetime.datetime.utcnow() - datetime.timedelta(minutes=1))

t_start=time.time()
t1=t_start

while True:
    try:

        # How long has passed since starting?
        # If 55 mins, we're not likely getting dumps this tick, so quit
        if (time.time() - t_start) >= (55 * 60):
            excaliburlog("55 minutes without a successful dump, giving up!")
            session.close()
            sys.exit()

        # Build the request for planet data
        req = urllib2.Request(Config.get("URL", "planets"))
        if last_tick > 0:
            u = Updates.load()
     #       req.add_header('If-None-Match', u.etag)
     #       req.add_header('If-Modified-Since', u.modified)
        opener = urllib2.build_opener(DefaultErrorHandler())
        planets = opener.open(req)


        try:
            if planets.status == 304:
                excaliburlog("Dump files not modified. Waiting...")
                time.sleep(60)
                continue
            else:
                excaliburlog("Error: %s" % planets.status)
                time.sleep(120)
                continue
        except AttributeError:
            pass

        # Open the dump files
        try:
            galaxies = urllib2.urlopen(Config.get("URL", "galaxies"))
            alliances = urllib2.urlopen(Config.get("URL", "alliances"))
        except Exception, e:
            excaliburlog("Failed gathering dump files.\n%s" % (str(e),))
            time.sleep(300)
            continue

        # Skip first three lines of the dump, tick info is on fourth line
        planets.readline();planets.readline();planets.readline();
        # Parse the fourth line and check we have a number
        tick=planets.readline()
        m=re.search(r"tick:\s+(\d+)",tick,re.I)
        if not m:
            excaliburlog("Invalid tick: '%s'" % (tick,))
            time.sleep(120)
            continue
        planet_tick=int(m.group(1))
        excaliburlog("Planet dump for tick %s" % (planet_tick,))
        # Skip next three lines; two are junk, next is blank, data starts next
        planets.readline();planets.readline();planets.readline();planets.readline();

        # As above
        galaxies.readline();galaxies.readline();galaxies.readline();
        tick=galaxies.readline()
        m=re.search(r"tick:\s+(\d+)",tick,re.I)
        if not m:
            excaliburlog("Invalid tick: '%s'" % (tick,))
            time.sleep(120)
            continue
        galaxy_tick=int(m.group(1))
        excaliburlog("Galaxy dump for tick %s" % (galaxy_tick,))
        galaxies.readline();galaxies.readline();galaxies.readline();galaxies.readline();

        # As above
        alliances.readline();alliances.readline();alliances.readline();
        tick=alliances.readline()
        m=re.search(r"tick:\s+(\d+)",tick,re.I)
        if not m:
            excaliburlog("Invalid tick: '%s'" % (tick,))
            time.sleep(120)
            continue
        alliance_tick=int(m.group(1))
        excaliburlog("Alliance dump for tick %s" % (alliance_tick,))
        alliances.readline();alliances.readline();alliances.readline();alliances.readline();

        # Check the ticks of the dumps are all the same and that it's
        #  greater than the previous tick, i.e. a new tick
        if not (planet_tick == galaxy_tick  == alliance_tick):
            excaliburlog("Varying ticks found, sleeping\nPlanet: %s, Galaxy: %s, Alliance: %s" % (planet_tick,galaxy_tick,alliance_tick))
            time.sleep(30)
            continue
        if not planet_tick > last_tick:
            excaliburlog("Stale ticks found, sleeping")
            time.sleep(60)
            continue

        t2=time.time()-t1
        excaliburlog("Loaded dumps from webserver in %.3f seconds" % (t2,))
        t1=time.time()

        # Uncomment this line to allow ticking on the same data for debug
        # planet_tick = last_tick + 1
        tick = bindparam("tick",planet_tick)

        # Get header information
        etag = planets.headers.get("ETag")
        modified = planets.headers.get("Last-Modified")

        # Insert a record of the tick and a timestamp generated by SQLA
        session.execute(Updates.__table__.insert().values(id=planet_tick, etag=etag, modified=modified))

        # Empty out the temp tables
        session.execute(galaxy_temp.delete())
        session.execute(planet_temp.delete())
        session.execute(alliance_temp.delete())
	#session.execute(temp_1.delete())
	#session.execute(temp_2.delete())
        ##session.execute(text("DROP TABLE IF EXISTS temp_1; DROP TABLE IF EXISTS temp_2;"))

        # If the new tick is below the shuffle tick, empty out all the data
        #  and don't store anything from the dumps other than the tick itself
        if planet_tick <= PA.getint("numbers", "shuffle"):
            excaliburlog("Pre-shuffle dumps detected, emptying out the data")
            planets = None
            galaxies = None
            alliances = None

        # Insert the data to the temporary tables
        # Planets
        session.execute(planet_temp.insert(), [{
                                                "x": int(p[0]),
                                                "y": int(p[1]),
                                                "z": int(p[2]),
                                                "planetname": p[3].strip("\""),
                                                "rulername": p[4].strip("\""),
                                                "race": p[5],
                                                "size": int(p[6] or 0),
                                                "score": int(p[7] or 0),
                                                "value": int(p[8] or 0),
                                                "xp": int(p[9] or 0),
                                               } for p in [decode(line).strip().split("\t") for line in skip_last(planets)]]) if planets else None
        # Galaxies
        session.execute(galaxy_temp.insert(), [{
                                                "x": int(g[0]),
                                                "y": int(g[1]),
                                                "name": g[2].strip("\""),
                                                "size": int(g[3] or 0),
                                                "score": int(g[4] or 0),
                                                "value": int(g[5] or 0),
                                                "xp": int(g[6] or 0),
                                               } for g in [decode(line).strip().split("\t") for line in skip_last(galaxies)]]) if galaxies else None
        # Alliances
        session.execute(alliance_temp.insert(), [{
                                                "score_rank": int(a[0]),
                                                "name": a[1].strip("\""),
                                                "size": int(a[2] or 0),
                                                "members": int(a[3] or 1),
                                                "score": int(a[4] or 0),
                                                "points": int(a[5] or 0),
                                                "score_total": int(a[6] or 0),
                                                "value_total": int(a[7] or 0),
                                                "size_avg": int(a[2] or 0) / int(a[3] or 1),
                                                "score_avg": int(a[4] or 0) / min(int(a[3] or 1), PA.getint("numbers", "tag_count")),
                                                "points_avg": int(a[5] or 0) / int(a[3] or 1),
                                               } for a in [decode(line).strip().split("\t") for line in skip_last(alliances)]]) if alliances else None

        t2=time.time()-t1
        excaliburlog("Inserted dumps in %.3f seconds" % (t2,))
        t1=time.time()

# ########################################################################### #
# ##############################    CLUSTERS    ############################# #
# ########################################################################### #

        # Make sure all the galaxies are active,
        #  some might have been deactivated previously
        session.execute(text("UPDATE cluster SET active = :true;", bindparams=[true]))

        # Any galaxies in the temp table without an id are new
        # Insert them to the current table and the id(serial/auto_increment)
        #  will be generated, and we can then copy it back to the temp table
        session.execute(text("INSERT INTO cluster (x, active) SELECT g.x, :true FROM galaxy_temp as g WHERE g.x NOT IN (SELECT x FROM cluster) GROUP BY g.x;", bindparams=[true]))

        # For galaxies that are no longer present in the new dump
        session.execute(text("UPDATE cluster SET active = :false WHERE x NOT IN (SELECT x FROM galaxy_temp);", bindparams=[false]))

        t2=time.time()-t1
        excaliburlog("Deactivate old clusters and generate new cluster ids in %.3f seconds" % (t2,))
        t1=time.time()

        # Update everything from the temp table and generate ranks
        # Deactivated items are untouched but NULLed earlier

        session.execute(text("""CREATE TABLE temp_1 (
                                  x INT, 
                                  count INT, 
                                  size INT, 
                                  value INT, 
                                  score INT, 
                                  xp INT, 
                                  totalroundroids INT, 
                                  totallostroids INT,
  				  score_total INT
                             );
                                 INSERT INTO temp_1 SELECT t.*,
                                   COALESCE(c.totalroundroids + (GREATEST(t.size - c.size, 0)), t.size) AS totalroundroids,
                                   COALESCE(c.totallostroids + (GREATEST(c.size - t.size, 0)), 0) AS totallostroids
                                 FROM cluster AS c, (SELECT x,
                                   count(*) as count,
                                   sum(size) as size,
                                   sum(value) as value,
                                   sum(score) as score,
				   sum(score) as score_total,
                                   sum(xp) as xp
                                 FROM planet_temp
                                   GROUP BY x) AS t
                                   WHERE c.x = t.x;
                             """, bindparams=[true]))

        session.execute(text("""UPDATE cluster AS c,
                                 (SELECT *,
                                   (SELECT COUNT(t2.totalroundroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totalroundroids < t2.totalroundroids OR (t1.totalroundroids=t2.totalroundroids AND t1.x=t2.x) WHERE t1.x=t.x
                                      GROUP BY t1.x) as totalroundroids_rank,
                                   (SELECT COUNT(t2.totallostroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totallostroids < t2.totallostroids OR (t1.totallostroids=t2.totallostroids AND t1.x=t2.x) WHERE t1.x=t.x
                                      GROUP BY t1.x) as totallostroids_rank,
                                   (SELECT COUNT(t2.size) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.size < t2.size OR (t1.size=t2.size AND t1.x=t2.x) WHERE t1.x=t.x
                                      GROUP BY t1.x) as size_rank,
                                   (SELECT COUNT(t2.score) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.score < t2.score OR (t1.score=t2.score AND t1.x=t2.x) WHERE t1.x=t.x
                                      GROUP BY t1.x) as score_rank,
                                   (SELECT COUNT(t2.value) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.value < t2.value OR (t1.value=t2.value AND t1.x=t2.x) WHERE t1.x=t.x
                                      GROUP BY t1.x) as value_rank,
                                   (SELECT COUNT(t2.xp) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.xp < t2.xp OR (t1.xp=t2.xp AND t1.x=t2.x) WHERE t1.x=t.x
                                      GROUP BY t1.x) as xp_rank
                                   """+
                                #    (SET @rownum := 0; SELECT @rownum := @rownum + 1 AS rank FROM (SELECT totalroundroids))
                                #     """+
                                 """
                                 FROM temp_1 AS t) AS t SET
                                  c.age = COALESCE(c.age, 0) + 1,
                                  c.size = t.size, c.score = t.score, c.value = t.value, c.xp = t.xp,
                                  c.ratio = CASE WHEN (t.value != 0) THEN 10000.0 * t.size / t.value ELSE 0 END,
                                  c.members = t.count,
                             """ + (
                             """
                                  c.size_growth = t.size - COALESCE(c.size - c.size_growth, 0),
                                  c.score_growth = t.score - COALESCE(c.score - c.score_growth, 0),
                                  c.value_growth = t.value - COALESCE(c.value - c.value_growth, 0),
                                  c.xp_growth = t.xp - COALESCE(c.xp - c.xp_growth, 0),
                                  c.member_growth = t.count - COALESCE(c.members - c.member_growth, 0),
                                  c.size_growth_pc = CASE WHEN (c.size - c.size_growth != 0) THEN COALESCE((t.size - (c.size - c.size_growth)) * 100.0 / (c.size - c.size_growth), 0) ELSE 0 END,
                                  c.score_growth_pc = CASE WHEN (c.score - c.score_growth != 0) THEN COALESCE((t.score - (c.score - c.score_growth)) * 100.0 / (c.score - c.score_growth), 0) ELSE 0 END,
                                  c.value_growth_pc = CASE WHEN (c.value - c.value_growth != 0) THEN COALESCE((t.value - (c.value - c.value_growth)) * 100.0 / (c.value - c.value_growth), 0) ELSE 0 END,
                                  c.xp_growth_pc = CASE WHEN (c.xp - c.xp_growth != 0) THEN COALESCE((t.xp - (c.xp - c.xp_growth)) * 100.0 / (c.xp - c.xp_growth), 0) ELSE 0 END,
                                  c.size_rank_change = t.size_rank - COALESCE(c.size_rank - c.size_rank_change, 0),
                                  c.score_rank_change = t.score_rank - COALESCE(c.score_rank - c.score_rank_change, 0),
                                  c.value_rank_change = t.value_rank - COALESCE(c.value_rank - c.value_rank_change, 0),
                                  c.xp_rank_change = t.xp_rank - COALESCE(c.xp_rank - c.xp_rank_change, 0),
                                  c.totalroundroids_rank_change = t.totalroundroids_rank - COALESCE(c.totalroundroids_rank - c.totalroundroids_rank_change, 0),
                                  c.totallostroids_rank_change = t.totallostroids_rank - COALESCE(c.totallostroids_rank - c.totallostroids_rank_change, 0),
                                  c.totalroundroids_growth = t.totalroundroids - COALESCE(c.totalroundroids - c.totalroundroids_growth, 0),
                                  c.totalroundroids_growth_pc = CASE WHEN (c.totalroundroids - c.totalroundroids_growth != 0) THEN COALESCE((t.totalroundroids - (c.totalroundroids - c.totalroundroids_growth)) * 100.0 / (c.totalroundroids - c.totalroundroids_growth), 0) ELSE 0 END,
                                  c.totallostroids_growth = t.totallostroids - COALESCE(c.totallostroids - c.totallostroids_growth, 0),
                                  c.totallostroids_growth_pc = CASE WHEN (c.totallostroids - c.totallostroids_growth != 0) THEN COALESCE((t.totallostroids - (c.totallostroids - c.totallostroids_growth)) * 100.0 / (c.totallostroids - c.totallostroids_growth), 0) ELSE 0 END,
                             """ if not midnight
                                 else
                             """
                                  c.size_growth = t.size - COALESCE(c.size, 0),
                                  c.score_growth = t.score - COALESCE(c.score, 0),
                                  c.value_growth = t.value - COALESCE(c.value, 0),
                                  c.xp_growth = t.xp - COALESCE(c.xp, 0),
                                  c.member_growth = t.count - COALESCE(c.members, 0),
                                  c.size_growth_pc = CASE WHEN (c.size != 0) THEN COALESCE((t.size - c.size) * 100.0 / c.size, 0) ELSE 0 END,
                                  c.score_growth_pc = CASE WHEN (c.score != 0) THEN COALESCE((t.score - c.score) * 100.0 / c.score * 100, 0) ELSE 0 END,
                                  c.value_growth_pc = CASE WHEN (c.value != 0) THEN COALESCE((t.value - c.value) * 100.0 / c.value, 0) ELSE 0 END,
                                  c.xp_growth_pc = CASE WHEN (c.xp != 0) THEN COALESCE((t.xp - c.xp) * 100.0 / c.xp, 0) ELSE 0 END,
                                  c.size_rank_change = t.size_rank - COALESCE(c.size_rank, 0),
                                  c.score_rank_change = t.score_rank - COALESCE(c.score_rank, 0),
                                  c.value_rank_change = t.value_rank - COALESCE(c.value_rank, 0),
                                  c.xp_rank_change = t.xp_rank - COALESCE(c.xp_rank, 0),
                                  c.totalroundroids_rank_change = t.totalroundroids_rank - COALESCE(c.totalroundroids_rank, 0),
                                  c.totallostroids_rank_change = t.totallostroids_rank - COALESCE(c.totallostroids_rank, 0),
                                  c.totalroundroids_growth = t.totalroundroids - COALESCE(c.totalroundroids, 0),
                                  c.totalroundroids_growth_pc = CASE WHEN (c.totalroundroids != 0) THEN COALESCE((t.totalroundroids - c.totalroundroids) * 100.0 / c.totalroundroids, 0) ELSE 0 END,
                                  c.totallostroids_growth = t.totallostroids - COALESCE(c.totallostroids, 0),
                                  c.totallostroids_growth_pc = CASE WHEN (c.totallostroids != 0) THEN COALESCE((t.totallostroids - c.totallostroids) * 100.0 / c.totallostroids, 0) ELSE 0 END,
                             """ ) +
                             """
                                  c.ticksroiding = COALESCE(c.ticksroiding, 0) + CASE WHEN (t.size > c.size AND (t.size - c.size) != (t.xp - c.xp)) THEN 1 ELSE 0 END,
                                  c.ticksroided = COALESCE(c.ticksroided, 0) + CASE WHEN (t.size < c.size) THEN 1 ELSE 0 END,
                                  c.tickroids = COALESCE(c.tickroids, 0) + t.size,
                                  c.avroids = COALESCE((c.tickroids + t.size) / (c.age + 1.0), t.size),
                                  c.roidxp = CASE WHEN (t.size != 0) THEN t.xp * 1.0 / t.size ELSE 0 END,
                             """ + ((
                             """
                                  c.%s_highest_rank = CASE WHEN (t.%s_rank <= COALESCE(c.%s_highest_rank, t.%s_rank)) THEN t.%s_rank ELSE c.%s_highest_rank END,
                                  c.%s_highest_rank_tick = CASE WHEN (t.%s_rank <= COALESCE(c.%s_highest_rank, t.%s_rank)) THEN :tick ELSE c.%s_highest_rank_tick END,
                                  c.%s_lowest_rank = CASE WHEN (t.%s_rank >= COALESCE(c.%s_lowest_rank, t.%s_rank)) THEN t.%s_rank ELSE c.%s_lowest_rank END,
                                  c.%s_lowest_rank_tick = CASE WHEN (t.%s_rank >= COALESCE(c.%s_lowest_rank, t.%s_rank)) THEN :tick ELSE c.%s_lowest_rank_tick END,
                             """ * 4) % (("size",)*22 + ("score",)*22 + ("value",)*22 + ("xp",)*22)) +
                             """
                                  c.totalroundroids = t.totalroundroids, c.totallostroids = t.totallostroids,
                                  c.totalroundroids_rank = t.totalroundroids_rank, c.totallostroids_rank = t.totallostroids_rank,
                                  c.size_rank = t.size_rank, c.score_rank = t.score_rank, c.value_rank = t.value_rank, c.xp_rank = t.xp_rank,
                                  c.vdiff = COALESCE(t.value - c.value, 0),
                                  c.sdiff = COALESCE(t.score - c.score, 0),
                                  c.xdiff = COALESCE(t.xp - c.xp, 0),
                                  c.rdiff = COALESCE(t.size - c.size, 0),
                                  c.mdiff = COALESCE(t.count - c.members, 0),
                                  c.vrankdiff = COALESCE(t.value_rank - c.value_rank, 0),
                                  c.srankdiff = COALESCE(t.score_rank - c.score_rank, 0),
                                  c.xrankdiff = COALESCE(t.xp_rank - c.xp_rank, 0),
                                  c.rrankdiff = COALESCE(t.size_rank - c.size_rank, 0),
                                  c.idle = CASE WHEN ((t.value-c.value) BETWEEN (c.vdiff-1) AND (c.vdiff+1) AND (c.xp-t.xp=0)) THEN 1 + COALESCE(c.idle, 0) ELSE 0 END
                                WHERE c.x = t.x
                                AND c.active = :true
                            ; DROP TABLE temp_1;""", bindparams=[tick, true]))

        t2=time.time()-t1
        excaliburlog("Update clusters from temp and generate ranks in %.3f seconds" % (t2,))
        t1=time.time()

# We do galaxies before planets now in order to satisfy the planet(x,y) FK

# ########################################################################### #
# ##############################    GALAXIES    ############################# #
# ########################################################################### #

        # Update the newly dumped data with IDs from the current data
        #  based on an x,y match in the two tables (and active=True)
#         session.execute(text("""UPDATE galaxy_temp AS t SET
#                                   id = g.id
#                                 FROM (SELECT id, x, y FROM galaxy) AS g
#                                   WHERE t.x = g.x AND t.y = g.y
#                             ;"""))


        session.execute(text("""UPDATE galaxy_temp AS t, galaxy as g SET
                                  t.id = g.id
                                  WHERE t.x = g.x AND t.y = g.y
                            ;"""))

        # Make sure all the galaxies are active,
        #  some might have been deactivated previously
        session.execute(text("UPDATE galaxy SET active = :true;", bindparams=[true]))

        t2=time.time()-t1
        excaliburlog("Copy galaxy ids to temp and activate in %.3f seconds" % (t2,))
        t1=time.time()

        # Any galaxies in the temp table without an id are new
        # Insert them to the current table and the id(serial/auto_increment)
        #  will be generated, and we can then copy it back to the temp table
        # Galaxies under a certain amount of planets are private
        session.execute(text("INSERT INTO galaxy (x, y, active) SELECT g.x, g.y, :true FROM galaxy_temp as g WHERE g.id IS NULL;", bindparams=[true]))
        session.execute(text("UPDATE galaxy_temp SET id = (SELECT id FROM galaxy WHERE galaxy.x = galaxy_temp.x AND galaxy.y = galaxy_temp.y AND galaxy.active = :true ORDER BY galaxy.id DESC) WHERE id IS NULL;", bindparams=[true]))

        # For galaxies that are no longer present in the new dump
        session.execute(text("UPDATE galaxy SET active = :false WHERE id NOT IN (SELECT id FROM galaxy_temp WHERE id IS NOT NULL);", bindparams=[false]))

        t2=time.time()-t1
        excaliburlog("Deactivate old galaxies and generate new galaxy ids in %.3f seconds" % (t2,))
        t1=time.time()

        # Update everything from the temp table and generate ranks
        # Deactivated items are untouched but NULLed earlier
        session.execute(text("""CREATE TABLE temp_1 (
                                  id INT, 
                                  x INT, 
                                  y INT, 
                                  name VARCHAR(255), 
                                  size INT, 
                                  score INT, 
                                  value INT, 
                                  xp INT, 
                                  totalroundroids INT, 
                                  totallostroids INT
                                );

                                CREATE TABLE temp_2 (
                                  x INT, 
                                  y INT, 
                                  count INT, 
                                  real_score INT 
                                );

                                 INSERT INTO temp_1 SELECT t.*,
                                   COALESCE(g.totalroundroids + (GREATEST(t.size - g.size, 0)), t.size) AS totalroundroids,
                                   COALESCE(g.totallostroids + (GREATEST(g.size - t.size, 0)), 0) AS totallostroids
                                 FROM galaxy AS g, galaxy_temp AS t
                                   WHERE g.id = t.id AND g.active = :true;

                                 INSERT INTO temp_2 SELECT x, y, count(*) AS count, SUM(score) AS real_score 
                                                              FROM planet_temp GROUP BY x, y;
                             """, bindparams=[true]))

        session.execute(text("""UPDATE galaxy AS g, 
                                (SELECT *,
                                   (SELECT COUNT(t2.totalroundroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totalroundroids < t2.totalroundroids OR (t1.totalroundroids=t2.totalroundroids AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as totalroundroids_rank,
                                   (SELECT COUNT(t2.totallostroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totallostroids < t2.totallostroids OR (t1.totallostroids=t2.totallostroids AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as totallostroids_rank,
                                   (SELECT COUNT(t2.size) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.size < t2.size OR (t1.size=t2.size AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as size_rank,
                                   (SELECT COUNT(t2.score) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.score < t2.score OR (t1.score=t2.score AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as score_rank,
                                   (SELECT COUNT(t2.value) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.value < t2.value OR (t1.value=t2.value AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as value_rank,
                                   (SELECT COUNT(t2.xp) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.xp < t2.xp OR (t1.xp=t2.xp AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as xp_rank
                                FROM temp_1 AS t) AS t,
                                (SELECT x, y, count, real_score,
                                  (SELECT COUNT(t2.real_score) FROM temp_2 t1 JOIN temp_2 t2 ON 
                                      t1.real_score < t2.real_score OR (t1.real_score=t2.real_score AND t1.x=t2.x AND t1.y=t2.y) WHERE t1.x=t.x AND t1.y=t.y
                                      GROUP BY t1.x,t1.y) AS real_score_rank
                                   FROM temp_2 AS t
                                ) AS p SET
                                  g.age = COALESCE(g.age, 0) + 1,
                                  g.x = t.x, g.y = t.y,
                                  g.name = t.name, g.size = t.size, g.score = t.score, g.value = t.value, g.xp = t.xp,
                                  g.ratio = CASE WHEN (t.value != 0) THEN 10000.0 * t.size / t.value ELSE 0 END,
                                  g.members = p.count,
                                  g.private = p.count <= :priv_gal OR (g.x = 1 AND g.y = 1),
                             """ + (
                             """
                                  g.size_growth = t.size - COALESCE(g.size - g.size_growth, 0),
                                  g.score_growth = t.score - COALESCE(g.score - g.score_growth, 0),
                                  g.value_growth = t.value - COALESCE(g.value - g.value_growth, 0),
                                  g.xp_growth = t.xp - COALESCE(g.xp - g.xp_growth, 0),
                                  g.member_growth = p.count - COALESCE(g.members - g.member_growth, 0),
                                  g.size_growth_pc = CASE WHEN (g.size - g.size_growth != 0) THEN COALESCE((t.size - (g.size - g.size_growth)) * 100.0 / (g.size - g.size_growth), 0) ELSE 0 END,
                                  g.score_growth_pc = CASE WHEN (g.score - g.score_growth != 0) THEN COALESCE((t.score - (g.score - g.score_growth)) * 100.0 / (g.score - g.score_growth), 0) ELSE 0 END,
                                  g.value_growth_pc = CASE WHEN (g.value - g.value_growth != 0) THEN COALESCE((t.value - (g.value - g.value_growth)) * 100.0 / (g.value - g.value_growth), 0) ELSE 0 END,
                                  g.xp_growth_pc = CASE WHEN (g.xp - g.xp_growth != 0) THEN COALESCE((t.xp - (g.xp - g.xp_growth)) * 100.0 / (g.xp - g.xp_growth), 0) ELSE 0 END,
                                  g.size_rank_change = t.size_rank - COALESCE(g.size_rank - g.size_rank_change, 0),
                                  g.score_rank_change = t.score_rank - COALESCE(g.score_rank - g.score_rank_change, 0),
                                  g.value_rank_change = t.value_rank - COALESCE(g.value_rank - g.value_rank_change, 0),
                                  g.xp_rank_change = t.xp_rank - COALESCE(g.xp_rank - g.xp_rank_change, 0),
                                  g.real_score_growth = p.real_score - COALESCE(g.real_score - g.real_score_growth, 0),
                                  g.real_score_growth_pc = CASE WHEN (g.real_score - g.real_score_growth != 0) THEN COALESCE((p.real_score - (g.real_score - g.real_score_growth)) * 100.0 / (g.real_score - g.real_score_growth), 0) ELSE 0 END,
                                  g.real_score_rank_change = p.real_score_rank - COALESCE(g.real_score_rank - g.real_score_rank_change, 0),
                                  g.totalroundroids_rank_change = t.totalroundroids_rank - COALESCE(g.totalroundroids_rank - g.totalroundroids_rank_change, 0),
                                  g.totallostroids_rank_change = t.totallostroids_rank - COALESCE(g.totallostroids_rank - g.totallostroids_rank_change, 0),
                                  g.totalroundroids_growth = t.totalroundroids - COALESCE(g.totalroundroids - g.totalroundroids_growth, 0),
                                  g.totalroundroids_growth_pc = CASE WHEN (g.totalroundroids - g.totalroundroids_growth != 0) THEN COALESCE((t.totalroundroids - (g.totalroundroids - g.totalroundroids_growth)) * 100.0 / (g.totalroundroids - g.totalroundroids_growth), 0) ELSE 0 END,
                                  g.totallostroids_growth = t.totallostroids - COALESCE(g.totallostroids - g.totallostroids_growth, 0),
                                  g.totallostroids_growth_pc = CASE WHEN (g.totallostroids - g.totallostroids_growth != 0) THEN COALESCE((t.totallostroids - (g.totallostroids - g.totallostroids_growth)) * 100.0 / (g.totallostroids - g.totallostroids_growth), 0) ELSE 0 END,
                             """ if not midnight
                                 else
                             """
                                  g.size_growth = t.size - COALESCE(g.size, 0),
                                  g.score_growth = t.score - COALESCE(g.score, 0),
                                  g.value_growth = t.value - COALESCE(g.value, 0),
                                  g.xp_growth = t.xp - COALESCE(g.xp, 0),
                                  g.member_growth = p.count - COALESCE(g.members, 0),
                                  g.size_growth_pc = CASE WHEN (g.size != 0) THEN COALESCE((t.size - g.size) * 100.0 / g.size, 0) ELSE 0 END,
                                  g.score_growth_pc = CASE WHEN (g.score != 0) THEN COALESCE((t.score - g.score) * 100.0 / g.score * 100, 0) ELSE 0 END,
                                  g.value_growth_pc = CASE WHEN (g.value != 0) THEN COALESCE((t.value - g.value) * 100.0 / g.value, 0) ELSE 0 END,
                                  g.xp_growth_pc = CASE WHEN (g.xp != 0) THEN COALESCE((t.xp - g.xp) * 100.0 / g.xp, 0) ELSE 0 END,
                                  g.size_rank_change = t.size_rank - COALESCE(g.size_rank, 0),
                                  g.score_rank_change = t.score_rank - COALESCE(g.score_rank, 0),
                                  g.value_rank_change = t.value_rank - COALESCE(g.value_rank, 0),
                                  g.xp_rank_change = t.xp_rank - COALESCE(g.xp_rank, 0),
                                  g.real_score_growth = p.real_score - COALESCE(g.real_score, 0),
                                  g.real_score_growth_pc = CASE WHEN (g.real_score != 0) THEN COALESCE((p.real_score - g.real_score) * 100.0 / g.real_score * 100, 0) ELSE 0 END,
                                  g.real_score_rank_change = p.real_score_rank - COALESCE(g.real_score_rank, 0),
                                  g.totalroundroids_rank_change = t.totalroundroids_rank - COALESCE(g.totalroundroids_rank, 0),
                                  g.totallostroids_rank_change = t.totallostroids_rank - COALESCE(g.totallostroids_rank, 0),
                                  g.totalroundroids_growth = t.totalroundroids - COALESCE(g.totalroundroids, 0),
                                  g.totalroundroids_growth_pc = CASE WHEN (g.totalroundroids != 0) THEN COALESCE((t.totalroundroids - g.totalroundroids) * 100.0 / g.totalroundroids, 0) ELSE 0 END,
                                  g.totallostroids_growth = t.totallostroids - COALESCE(g.totallostroids, 0),
                                  g.totallostroids_growth_pc = CASE WHEN (g.totallostroids != 0) THEN COALESCE((t.totallostroids - g.totallostroids) * 100.0 / g.totallostroids, 0) ELSE 0 END,
                             """ ) +
                             """
                                  g.ticksroiding = COALESCE(g.ticksroiding, 0) + CASE WHEN (t.size > g.size AND (t.size - g.size) != (t.xp - g.xp)) THEN 1 ELSE 0 END,
                                  g.ticksroided = COALESCE(g.ticksroided, 0) + CASE WHEN (t.size < g.size) THEN 1 ELSE 0 END,
                                  g.tickroids = COALESCE(g.tickroids, 0) + t.size,
                                  g.avroids = COALESCE((g.tickroids + t.size) / (g.age + 1.0), t.size),
                                  g.roidxp = CASE WHEN (t.size != 0) THEN t.xp * 1.0 / t.size ELSE 0 END,
                             """ + ((
                             """
                                  g.%s_highest_rank = CASE WHEN (t.%s_rank <= COALESCE(g.%s_highest_rank, t.%s_rank)) THEN t.%s_rank ELSE g.%s_highest_rank END,
                                  g.%s_highest_rank_tick = CASE WHEN (t.%s_rank <= COALESCE(g.%s_highest_rank, t.%s_rank)) THEN :tick ELSE g.%s_highest_rank_tick END,
                                  g.%s_lowest_rank = CASE WHEN (t.%s_rank >= COALESCE(g.%s_lowest_rank, t.%s_rank)) THEN t.%s_rank ELSE g.%s_lowest_rank END,
                                  g.%s_lowest_rank_tick = CASE WHEN (t.%s_rank >= COALESCE(g.%s_lowest_rank, t.%s_rank)) THEN :tick ELSE g.%s_lowest_rank_tick END,
                             """ * 4) % (("size",)*22 + ("score",)*22 + ("value",)*22 + ("xp",)*22)) +
                             """
                                  g.real_score_highest_rank = CASE WHEN (p.real_score_rank <= COALESCE(g.real_score_highest_rank, p.real_score_rank)) THEN p.real_score_rank ELSE g.real_score_highest_rank END,
                                  g.real_score_highest_rank_tick = CASE WHEN (p.real_score_rank <= COALESCE(g.real_score_highest_rank, p.real_score_rank)) THEN :tick ELSE g.real_score_highest_rank_tick END,
                                  g.real_score_lowest_rank = CASE WHEN (p.real_score_rank >= COALESCE(g.real_score_lowest_rank, p.real_score_rank)) THEN p.real_score_rank ELSE g.real_score_lowest_rank END,
                                  g.real_score_lowest_rank_tick = CASE WHEN (p.real_score_rank >= COALESCE(g.real_score_lowest_rank, p.real_score_rank)) THEN :tick ELSE g.real_score_lowest_rank_tick END,
                                  g.real_score = p.real_score, g.real_score_rank = p.real_score_rank,
                                  g.totalroundroids = t.totalroundroids, g.totallostroids = t.totallostroids,
                                  g.totalroundroids_rank = t.totalroundroids_rank, g.totallostroids_rank = t.totallostroids_rank,
                                  g.size_rank = t.size_rank, g.score_rank = t.score_rank, g.value_rank = t.value_rank, g.xp_rank = t.xp_rank,
                                  g.vdiff = COALESCE(t.value - g.value, 0),
                                  g.sdiff = COALESCE(t.score - g.score, 0),
                                  g.rsdiff = COALESCE(p.real_score - g.real_score, 0),
                                  g.xdiff = COALESCE(t.xp - g.xp, 0),
                                  g.rdiff = COALESCE(t.size - g.size, 0),
                                  g.mdiff = COALESCE(p.count - g.members, 0),
                                  g.vrankdiff = COALESCE(t.value_rank - g.value_rank, 0),
                                  g.srankdiff = COALESCE(t.score_rank - g.score_rank, 0),
                                  g.rsrankdiff = COALESCE(p.real_score_rank - g.real_score_rank, 0),
                                  g.xrankdiff = COALESCE(t.xp_rank - g.xp_rank, 0),
                                  g.rrankdiff = COALESCE(t.size_rank - g.size_rank, 0),
                                  g.idle = CASE WHEN ((t.value-g.value) BETWEEN (g.vdiff-1) AND (g.vdiff+1) AND (g.xp-t.xp=0)) THEN 1 + COALESCE(g.idle, 0) ELSE 0 END
                                WHERE g.id = t.id
                                   AND g.x = p.x AND g.y = p.y
                                AND g.active = :true
                            ;DROP TABLE temp_1; DROP TABLE temp_2;""", bindparams=[tick, true, bindparam("priv_gal",PA.getint("numbers", "priv_gal"))]))



        t2=time.time()-t1
        excaliburlog("Update galaxies from temp and generate ranks in %.3f seconds" % (t2,))
        t1=time.time()

# ########################################################################### #
# ##############################    PLANETS    ############################## #
# ########################################################################### #

        # Update the newly dumped data with IDs from the current data
        #  based on an ruler-,planet-name match in the two tables (and active=True)
#         session.execute(text("""UPDATE planet_temp AS t SET
#                                   id = p.id
#                                 FROM (SELECT id, rulername, planetname FROM planet WHERE active = :true) AS p
#                                   WHERE t.rulername = p.rulername AND t.planetname = p.planetname
#                             ;""", bindparams=[true]))


        session.execute(text("""UPDATE planet_temp AS t, planet as p SET
                                  t.id = p.id
                                  WHERE t.rulername = p.rulername AND t.planetname = p.planetname
                            ;""", bindparams=[true]))

        t2=time.time()-t1
        excaliburlog("Copy planet ids to temp in %.3f seconds" % (t2,))
        t1=time.time()

        while last_tick > PA.getint("numbers", "shuffle"): #looks are deceiving, this only runs once
            # This code is designed to match planets whose ruler/planet names
            #  change, by matching them with new planets using certain criteria

            def load_planet_id_search():
                # If we have any ids in the planet_new_id_search table,
                #  match them up with planet_temp using x,y,z
                session.execute(text("UPDATE planet_temp SET id = (SELECT id FROM planet_new_id_search WHERE planet_temp.x = planet_new_id_search.x AND planet_temp.y = planet_new_id_search.y AND planet_temp.z = planet_new_id_search.z) WHERE id IS NULL;"))
                # Empty out the two search tables
                session.execute(planet_new_id_search.delete())
                session.execute(planet_old_id_search.delete())
                # Insert from the new tick any planets without id
                if session.execute(text("INSERT INTO planet_new_id_search SELECT id, x, y, z, planetname, rulername, race, size, score, value, xp FROM planet_temp WHERE planet_temp.id IS NULL;")).rowcount < 1:
                    return None
                # Insert from the previous tick any planets without
                #  an equivalent planet from the new tick
                if session.execute(text("INSERT INTO planet_old_id_search SELECT id, x, y, z, planetname, rulername, race, size, score, value, xp, vdiff FROM planet WHERE planet.id NOT IN (SELECT id FROM planet_temp WHERE id IS NOT NULL) AND planet.active = :true;", bindparams=[true])).rowcount < 1:
                    return None
                # If either of the two search tables do not have any planets
                #  to match moved in (.rowcount() < 1) then return None, else:
                return 1

            # Load in the planets to match against and use the first set of match criterion
            if load_planet_id_search() is None: break
            session.execute(text("""UPDATE planet_new_id_search SET id = (
                                      SELECT id FROM planet_old_id_search WHERE
                                        planet_old_id_search.x = planet_new_id_search.x AND
                                        planet_old_id_search.y = planet_new_id_search.y AND
                                        planet_old_id_search.z = planet_new_id_search.z AND
                                        planet_old_id_search.race = planet_new_id_search.race AND
                                        planet_old_id_search.size > 500 AND
                                        planet_old_id_search.size = planet_new_id_search.size
                                      );"""))
            # As above, second set of criterion
            if load_planet_id_search() is None: break
            session.execute(text("""UPDATE planet_new_id_search SET id = (
                                      SELECT id FROM planet_old_id_search WHERE
                                        planet_old_id_search.x = planet_new_id_search.x AND
                                        planet_old_id_search.y = planet_new_id_search.y AND
                                        planet_old_id_search.z = planet_new_id_search.z AND
                                        planet_old_id_search.race = planet_new_id_search.race AND
                                        planet_old_id_search.value > 500000 AND
                                        planet_new_id_search.value BETWEEN
                                                planet_old_id_search.value - (2* planet_old_id_search.vdiff) AND 
                                                planet_old_id_search.value + (2* planet_old_id_search.vdiff)
                                      );"""))
            # Third set of criterion
            if load_planet_id_search() is None: break
            session.execute(text("""UPDATE planet_new_id_search SET id = (
                                      SELECT id FROM planet_old_id_search WHERE
                                        planet_old_id_search.race = planet_new_id_search.race AND
                                        planet_old_id_search.size > 500 AND
                                        planet_old_id_search.size = planet_new_id_search.size AND
                                        planet_old_id_search.value > 500000 AND
                                        planet_new_id_search.value BETWEEN
                                                planet_old_id_search.value - (2* planet_old_id_search.vdiff) AND
                                                planet_old_id_search.value + (2* planet_old_id_search.vdiff)
                                      );"""))
            # Fourth set of criterion for smaller planets
            if load_planet_id_search() is None: break
            session.execute(text("""UPDATE planet_new_id_search SET id = (
                                      SELECT id FROM planet_old_id_search WHERE
                                        planet_old_id_search.x = planet_new_id_search.x AND
                                        planet_old_id_search.y = planet_new_id_search.y AND
                                        planet_old_id_search.z = planet_new_id_search.z AND
                                        planet_old_id_search.race = planet_new_id_search.race AND
                                        planet_old_id_search.size = planet_new_id_search.size AND
                                        planet_new_id_search.value BETWEEN
                                                planet_old_id_search.value - (2* planet_old_id_search.vdiff) AND
                                                planet_old_id_search.value + (2* planet_old_id_search.vdiff)
                                      );"""))
            # Fifth set of criterion for planets that half-match
            if load_planet_id_search() is None: break
            session.execute(text("""UPDATE planet_new_id_search SET id = (
                                      SELECT id FROM planet_old_id_search WHERE
                                        planet_old_id_search.x = planet_new_id_search.x AND
                                        planet_old_id_search.y = planet_new_id_search.y AND
                                        planet_old_id_search.z = planet_new_id_search.z AND
                                        (planet_old_id_search.planetname = planet_new_id_search.planetname
                                         OR planet_old_id_search.rulername = planet_new_id_search.rulername)
                                      );"""))
            # Final update
            if load_planet_id_search() is None: break
            break

        t2=time.time()-t1
        excaliburlog("Lost planet ids match up in %.3f seconds" % (t2,))
        t1=time.time()

        # Any planets in the temp table without an id are new
        # Insert them to the current table and the id(serial/auto_increment)
        #  will be generated, and we can then copy it back to the temp table
        session.execute(text("INSERT INTO planet (rulername, planetname, active) SELECT rulername, planetname, :true FROM planet_temp WHERE id IS NULL;", bindparams=[true]))
        session.execute(text("UPDATE planet_temp SET id = (SELECT id FROM planet WHERE planet.rulername = planet_temp.rulername AND planet.planetname = planet_temp.planetname AND planet.active = :true ORDER BY planet.id DESC) WHERE id IS NULL;", bindparams=[true]))

        t2=time.time()-t1
        excaliburlog("Generate new planet ids in %.3f seconds" % (t2,))
        t1=time.time()

        # Create records of new planets,
        session.execute(text("""INSERT INTO planet_exiles (hour, tick, id, newx, newy, newz)
                                SELECT :hour, :tick, planet.id, planet_temp.x, planet_temp.y, planet_temp.z
                                FROM planet_temp, planet
                                WHERE
                                    planet.rulername = planet_temp.rulername AND
                                    planet.planetname = planet_temp.planetname AND
                                    planet.active = :true AND
                                    planet.age IS NULL
                            ;""", bindparams=[tick, hour, true]))
        # deleted plantes
        session.execute(text("""INSERT INTO planet_exiles (hour, tick, id, oldx, oldy, oldz)
                                SELECT :hour, :tick, planet.id, planet.x, planet.y, planet.z
                                FROM planet
                                WHERE
                                    planet.active = :true AND
                                    planet.age IS NOT NULL AND
                                    planet.id NOT IN (SELECT id FROM planet_temp WHERE id IS NOT NULL)
                            ;""", bindparams=[tick, hour, true]))
        # planet renames
        session.execute(text("""INSERT INTO planet_exiles (hour, tick, id, oldx, oldy, oldz, newx, newy, newz)
                                SELECT :hour, :tick, planet.id, planet.x, planet.y, planet.z, planet_temp.x, planet_temp.y, planet_temp.z
                                FROM planet_temp, planet
                                WHERE
                                    planet.id = planet_temp.id AND
                                    planet.active = :true AND
                                    planet.age IS NOT NULL AND
                                    (planet.rulername != planet_temp.rulername OR planet.planetname != planet_temp.planetname)
                            ;""", bindparams=[tick, hour, true]))
        # and planet movements
        session.execute(text("""INSERT INTO planet_exiles (hour, tick, id, oldx, oldy, oldz, newx, newy, newz)
                                SELECT :hour, :tick, planet.id, planet.x, planet.y, planet.z, planet_temp.x, planet_temp.y, planet_temp.z
                                FROM planet_temp, planet
                                WHERE
                                    planet.id = planet_temp.id AND
                                    planet.active = :true AND
                                    planet.age IS NOT NULL AND
                                    (planet.x != planet_temp.x OR planet.y != planet_temp.y OR planet.z != planet_temp.z)
                            ;""", bindparams=[tick, hour, true]))

        t2=time.time()-t1
        excaliburlog("Track new/deleted/moved planets in %.3f seconds" % (t2,))
        t1=time.time()

        # For planets that are no longer present in the new dump
        session.execute(text("UPDATE planet SET active = :false WHERE id NOT IN (SELECT id FROM planet_temp WHERE id IS NOT NULL);", bindparams=[false]))

        t2=time.time()-t1
        excaliburlog("Deactivate old planets in %.3f seconds" % (t2,))
        t1=time.time()

        # Update everything from the temp table and generate ranks
        # Deactivated items are untouched but NULLed earlier
        session.execute(text("""CREATE TABLE temp_1 (
                                  id INT, 
                                  x INT, 
                                  y INT, 
                                  z INT, 
                                  planetname VARCHAR(255), 
                                  rulername VARCHAR(255), 
                                  race VARCHAR(255), 
                                  size INT, 
                                  score INT, 
                                  value INT, 
                                  xp INT, 
                                  totalroundroids INT, 
                                  totallostroids INT,
  				  score_total INT
                                );

                                 INSERT INTO temp_1 SELECT t.*,
                                   COALESCE(p.totalroundroids + (GREATEST(t.size - p.size, 0)), t.size) AS totalroundroids,
                                   COALESCE(p.totallostroids + (GREATEST(p.size - t.size, 0)), 0) AS totallostroids,
					t.score as score_total
                                 FROM planet AS p, planet_temp AS t
                                   WHERE p.id = t.id AND p.active = :true;
                             """, bindparams=[true]))

        session.execute(text("""UPDATE planet AS p, (SELECT *,
                             """ + (( """
                                   (SELECT COUNT(t2.totalroundroids) FROM temp_1 t1 JOIN temp_1 t2 ON %s AND (
                                      t1.totalroundroids < t2.totalroundroids OR (t1.totalroundroids=t2.totalroundroids AND t1.id=t2.id)) WHERE t1.id=t.id
                                      GROUP BY t1.id) as %s_totalroundroids_rank,
                                   (SELECT COUNT(t2.totallostroids) FROM temp_1 t1 JOIN temp_1 t2 ON %s AND (
                                      t1.totallostroids < t2.totallostroids OR (t1.totallostroids=t2.totallostroids AND t1.id=t2.id)) WHERE t1.id=t.id
                                      GROUP BY t1.id) as %s_totallostroids_rank,
                                   (SELECT COUNT(t2.size) FROM temp_1 t1 JOIN temp_1 t2 ON %s AND (
                                      t1.size < t2.size OR (t1.size=t2.size AND t1.id=t2.id)) WHERE t1.id=t.id
                                      GROUP BY t1.id) as %s_size_rank,
                                   (SELECT COUNT(t2.score) FROM temp_1 t1 JOIN temp_1 t2 ON %s AND (
                                      t1.score < t2.score OR (t1.score=t2.score AND t1.id=t2.id)) WHERE t1.id=t.id
                                      GROUP BY t1.id) as %s_score_rank,
                                   (SELECT COUNT(t2.value) FROM temp_1 t1 JOIN temp_1 t2 ON %s AND (
                                      t1.value < t2.value OR (t1.value=t2.value AND t1.id=t2.id)) WHERE t1.id=t.id
                                      GROUP BY t1.id) as %s_value_rank,
                                   (SELECT COUNT(t2.xp) FROM temp_1 t1 JOIN temp_1 t2 ON %s AND (
                                      t1.xp < t2.xp OR (t1.xp=t2.xp AND t1.id=t2.id)) WHERE t1.id=t.id
                                      GROUP BY t1.id) as %s_xp_rank,
                             """ * 3) % (("t1.x=t2.x","cluster",)*6 + ("t1.x=t2.x AND t1.y=t2.y","galaxy",)*6
                                    + ("t1.race=t2.race","race",)*6)) +
#                              """ + ((
#                                   (SELECT COUNT(*) + 1 FROM temp_1 WHERE totallostroids > temp_1.totallostroids AND x=temp_1.x) as totallostroids_rank,
#                                   rank() OVER (PARTITION BY %s ORDER BY totalroundroids DESC) AS %s_totalroundroids_rank,
#                                   rank() OVER (PARTITION BY %s ORDER BY totallostroids DESC) AS %s_totallostroids_rank,
#                                   rank() OVER (PARTITION BY %s ORDER BY size DESC) AS %s_size_rank,
#                                   rank() OVER (PARTITION BY %s ORDER BY score DESC) AS %s_score_rank,
#                                   rank() OVER (PARTITION BY %s ORDER BY value DESC) AS %s_value_rank,
#                                   rank() OVER (PARTITION BY %s ORDER BY xp DESC) AS %s_xp_rank,
#                              """ * 3) % (("x","cluster",)*6 + ("x, y","galaxy",)*6 + ("race","race",)*6)) +
#                              """
#                                   rank() OVER (ORDER BY totalroundroids DESC) AS totalroundroids_rank,
#                                   rank() OVER (ORDER BY totallostroids DESC) AS totallostroids_rank,
#                                   rank() OVER (ORDER BY size DESC) AS size_rank,
#                                   rank() OVER (ORDER BY score DESC) AS score_rank,
#                                   rank() OVER (ORDER BY value DESC) AS value_rank,
#                                   rank() OVER (ORDER BY xp DESC) AS xp_rank
                             """
                                  (SELECT COUNT(t2.totalroundroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totalroundroids < t2.totalroundroids OR (t1.totalroundroids=t2.totalroundroids AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as totalroundroids_rank,
                                  (SELECT COUNT(t2.totallostroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totallostroids < t2.totallostroids OR (t1.totallostroids=t2.totallostroids AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as totallostroids_rank,
                                  (SELECT COUNT(t2.size) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.size < t2.size OR (t1.size=t2.size AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as size_rank,
                                  (SELECT COUNT(t2.score) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.score < t2.score OR (t1.score=t2.score AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as score_rank,
                                  (SELECT COUNT(t2.value) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.value < t2.value OR (t1.value=t2.value AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as value_rank,
                                  (SELECT COUNT(t2.xp) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.xp < t2.xp OR (t1.xp=t2.xp AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as xp_rank
                                FROM temp_1 AS t) AS t
                                SET
                                  p.age = COALESCE(p.age, 0) + 1,
                                  p.x = t.x, p.y = t.y, p.z = t.z,
                                  p.planetname = t.planetname, p.rulername = t.rulername, p.race = t.race,
                                  p.size = t.size, p.score = t.score, p.value = t.value, p.xp = t.xp,
                                  p.ratio = CASE WHEN (t.value != 0) THEN 10000.0 * t.size / t.value ELSE 0 END,
                             """ + ((
                             """
                                  p.size_growth = t.size - COALESCE(p.size - p.size_growth, 0),
                                  p.score_growth = t.score - COALESCE(p.score - p.score_growth, 0),
                                  p.value_growth = t.value - COALESCE(p.value - p.value_growth, 0),
                                  p.xp_growth = t.xp - COALESCE(p.xp - p.xp_growth, 0),
                                  p.size_growth_pc = CASE WHEN (p.size - p.size_growth != 0) THEN COALESCE((t.size - (p.size - p.size_growth)) * 100.0 / (p.size - p.size_growth), 0) ELSE 0 END,
                                  p.score_growth_pc = CASE WHEN (p.score - p.score_growth != 0) THEN COALESCE((t.score - (p.score - p.score_growth)) * 100.0 / (p.score - p.score_growth), 0) ELSE 0 END,
                                  p.value_growth_pc = CASE WHEN (p.value - p.value_growth != 0) THEN COALESCE((t.value - (p.value - p.value_growth)) * 100.0 / (p.value - p.value_growth), 0) ELSE 0 END,
                                  p.xp_growth_pc = CASE WHEN (p.xp - p.xp_growth != 0) THEN COALESCE((t.xp - (p.xp - p.xp_growth)) * 100.0 / (p.xp - p.xp_growth), 0) ELSE 0 END,
                             """ + ((
                             """
                                  p.%ssize_rank_change = t.%ssize_rank - COALESCE(p.%ssize_rank - p.%ssize_rank_change, 0),
                                  p.%sscore_rank_change = t.%sscore_rank - COALESCE(p.%sscore_rank - p.%sscore_rank_change, 0),
                                  p.%svalue_rank_change = t.%svalue_rank - COALESCE(p.%svalue_rank - p.%svalue_rank_change, 0),
                                  p.%sxp_rank_change = t.%sxp_rank - COALESCE(p.%sxp_rank - p.%sxp_rank_change, 0),
                                  p.%stotalroundroids_rank_change = t.%stotalroundroids_rank - COALESCE(p.%stotalroundroids_rank - p.%stotalroundroids_rank_change, 0),
                                  p.%stotallostroids_rank_change = t.%stotallostroids_rank - COALESCE(p.%stotallostroids_rank - p.%stotallostroids_rank_change, 0),
                             """ * 4) % (("",)*24 + ("cluster_",)*24 + ("galaxy_",)*24 + ("race_",)*24)) +
                             """
                                  p.totalroundroids_growth = t.totalroundroids - COALESCE(p.totalroundroids - p.totalroundroids_growth, 0),
                                  p.totalroundroids_growth_pc = CASE WHEN (p.totalroundroids - p.totalroundroids_growth != 0) THEN COALESCE((t.totalroundroids - (p.totalroundroids - p.totalroundroids_growth)) * 100.0 / (p.totalroundroids - p.totalroundroids_growth), 0) ELSE 0 END,
                                  p.totallostroids_growth = t.totallostroids - COALESCE(p.totallostroids - p.totallostroids_growth, 0),
                                  p.totallostroids_growth_pc = CASE WHEN (p.totallostroids - p.totallostroids_growth != 0) THEN COALESCE((t.totallostroids - (p.totallostroids - p.totallostroids_growth)) * 100.0 / (p.totallostroids - p.totallostroids_growth), 0) ELSE 0 END,
                             """ ) if not midnight
                                 else (
                             """
                                  p.size_growth = t.size - COALESCE(p.size, 0),
                                  p.score_growth = t.score - COALESCE(p.score, 0),
                                  p.value_growth = t.value - COALESCE(p.value, 0),
                                  p.xp_growth = t.xp - COALESCE(p.xp, 0),
                                  p.size_growth_pc = CASE WHEN (p.size != 0) THEN COALESCE((t.size - p.size) * 100.0 / p.size, 0) ELSE 0 END,
                                  p.score_growth_pc = CASE WHEN (p.score != 0) THEN COALESCE((t.score - p.score) * 100.0 / p.score, 0) ELSE 0 END,
                                  p.value_growth_pc = CASE WHEN (p.value != 0) THEN COALESCE((t.value - p.value) * 100.0 / p.value, 0) ELSE 0 END,
                                  p.xp_growth_pc = CASE WHEN (p.xp != 0) THEN COALESCE((t.xp - p.xp) * 100.0 / p.xp, 0) ELSE 0 END,
                             """ + ((
                             """
                                  p.%ssize_rank_change = t.%ssize_rank - COALESCE(p.%ssize_rank, 0),
                                  p.%sscore_rank_change = t.%sscore_rank - COALESCE(p.%sscore_rank, 0),
                                  p.%svalue_rank_change = t.%svalue_rank - COALESCE(p.%svalue_rank, 0),
                                  p.%sxp_rank_change = t.%sxp_rank - COALESCE(p.%sxp_rank, 0),
                                  p.%stotalroundroids_rank_change = t.%stotalroundroids_rank - COALESCE(p.%stotalroundroids_rank, 0),
                                  p.%stotallostroids_rank_change = t.%stotallostroids_rank - COALESCE(p.%stotallostroids_rank, 0),
                             """ * 4) % (("",)*18 + ("cluster_",)*18 + ("galaxy_",)*18 + ("race_",)*18)) +
                             """
                                  p.totalroundroids_growth = t.totalroundroids - COALESCE(p.totalroundroids, 0),
                                  p.totalroundroids_growth_pc = CASE WHEN (p.totalroundroids != 0) THEN COALESCE((t.totalroundroids - p.totalroundroids) * 100.0 / p.totalroundroids, 0) ELSE 0 END,
                                  p.totallostroids_growth = t.totallostroids - COALESCE(p.totallostroids, 0),
                                  p.totallostroids_growth_pc = CASE WHEN (p.totallostroids != 0) THEN COALESCE((t.totallostroids - p.totallostroids) * 100.0 / p.totallostroids, 0) ELSE 0 END,
                             """ )) +
                             """
                                  p.totalroundroids = t.totalroundroids, p.totallostroids = t.totallostroids,
                             """ + ((
                             """
                                  p.%ssize_rank = t.%ssize_rank, p.%sscore_rank = t.%sscore_rank, p.%svalue_rank = t.%svalue_rank, p.%sxp_rank = t.%sxp_rank,
                                  p.%stotalroundroids_rank = t.%stotalroundroids_rank, p.%stotallostroids_rank = t.%stotallostroids_rank,
                             """ * 4) % (("",)*12 + ("cluster_",)*12 + ("galaxy_",)*12 + ("race_",)*12)) +
                             """
                                  p.ticksroiding = COALESCE(p.ticksroiding, 0) + CASE WHEN (t.size > p.size AND (t.size - p.size) != (t.xp - p.xp)) THEN 1 ELSE 0 END,
                                  p.ticksroided = COALESCE(p.ticksroided, 0) + CASE WHEN (t.size < p.size) THEN 1 ELSE 0 END,
                                  p.tickroids = COALESCE(p.tickroids, 0) + t.size,
                                  p.avroids = COALESCE((p.tickroids + t.size) / (p.age + 1.0), t.size),
                                  p.roidxp = CASE WHEN (t.size != 0) THEN t.xp * 1.0 / t.size ELSE 0 END,
                             """ + ((
                             """
                                  p.%s_highest_rank = CASE WHEN (t.%s_rank <= COALESCE(p.%s_highest_rank, t.%s_rank)) THEN t.%s_rank ELSE p.%s_highest_rank END,
                                  p.%s_highest_rank_tick = CASE WHEN (t.%s_rank <= COALESCE(p.%s_highest_rank, t.%s_rank)) THEN :tick ELSE p.%s_highest_rank_tick END,
                                  p.%s_lowest_rank = CASE WHEN (t.%s_rank >= COALESCE(p.%s_lowest_rank, t.%s_rank)) THEN t.%s_rank ELSE p.%s_lowest_rank END,
                                  p.%s_lowest_rank_tick = CASE WHEN (t.%s_rank >= COALESCE(p.%s_lowest_rank, t.%s_rank)) THEN :tick ELSE p.%s_lowest_rank_tick END,
                             """ * 4) % (("size",)*22 + ("score",)*22 + ("value",)*22 + ("xp",)*22)) +
                             """
                                  p.vdiff = COALESCE(t.value - p.value, 0),
                                  p.sdiff = COALESCE(t.score - p.score, 0),
                                  p.xdiff = COALESCE(t.xp - p.xp, 0),
                                  p.rdiff = COALESCE(t.size - p.size, 0),
                                  p.vrankdiff = COALESCE(t.value_rank - p.value_rank, 0),
                                  p.srankdiff = COALESCE(t.score_rank - p.score_rank, 0),
                                  p.xrankdiff = COALESCE(t.xp_rank - p.xp_rank, 0),
                                  p.rrankdiff = COALESCE(t.size_rank - p.size_rank, 0),
                                  p.idle = CASE WHEN ((t.value-p.value) BETWEEN (p.vdiff-1) AND (p.vdiff+1) AND (p.xp-t.xp=0)) THEN 1 + COALESCE(p.idle, 0) ELSE 0 END
                                  WHERE p.id = t.id
                                AND p.active = :true
                            ; DROP TABLE temp_1;""", bindparams=[tick, true]))

        t2=time.time()-t1
        excaliburlog("Update planets from temp and generate ranks in %.3f seconds" % (t2,))
        t1=time.time()

        # Idle data
        session.execute(text("""INSERT INTO planet_idles (hour, tick, id, idle)
                                SELECT :hour, :tick, planet.id, planet.idle
                                FROM planet
                                WHERE
                                    planet.idle > 0 AND
                                    planet.active = :true
                            ;""", bindparams=[tick, hour, true]))
        # Value drops
        session.execute(text("""INSERT INTO planet_value_drops (hour, tick, id, vdiff)
                                SELECT :hour, :tick, planet.id, planet.vdiff
                                FROM planet
                                WHERE
                                    planet.vdiff < 0 AND
                                    planet.active = :true
                            ;""", bindparams=[tick, hour, true]))
        # Landings
        session.execute(text("""INSERT INTO planet_landings (hour, tick, id, rdiff)
                                SELECT :hour, :tick, planet.id, planet.rdiff
                                FROM planet
                                WHERE
                                    planet.rdiff > 0 AND
                                    planet.rdiff != planet.xdiff AND
                                    planet.active = :true
                            ;""", bindparams=[tick, hour, true]))
        # Landed on
        session.execute(text("""INSERT INTO planet_landed_on (hour, tick, id, rdiff)
                                SELECT :hour, :tick, planet.id, planet.rdiff
                                FROM planet
                                WHERE
                                    planet.rdiff < 0 AND
                                    planet.active = :true
                            ;""", bindparams=[tick, hour, true]))

        t2=time.time()-t1
        excaliburlog("Planet stats in in %.3f seconds" % (t2,))
        t1=time.time()

# ########################################################################### #
# #############################    ALLIANCES    ############################# #
# ########################################################################### #

        # Update the newly dumped data with IDs from the current data
        #  based on a name match in the two tables (and active=True)
#         session.execute(text("""UPDATE alliance_temp AS t SET
#                                   id = a.id
#                                 FROM (SELECT id, name FROM alliance) AS a
#                                   WHERE t.name = a.name
#                             ;"""))


        session.execute(text("""UPDATE alliance_temp AS t, alliance AS a SET
                                  t.id = a.id
                                  WHERE t.name = a.name
                            ;"""))

        # Make sure all the alliances are active,
        #  some might have been deactivated previously
        session.execute(text("UPDATE alliance SET active = :true;", bindparams=[true]))

        t2=time.time()-t1
        excaliburlog("Copy alliance ids to temp and activate in %.3f seconds" % (t2,))
        t1=time.time()

        # Any alliances in the temp table without an id are new
        # Insert them to the current table and the id(serial/auto_increment)
        #  will be generated, and we can then copy it back to the temp table
        session.execute(text("INSERT INTO alliance (name, active) SELECT name, :true FROM alliance_temp WHERE id IS NULL;", bindparams=[true]))
        session.execute(text("UPDATE alliance_temp SET id = (SELECT id FROM alliance WHERE alliance.name = alliance_temp.name AND alliance.active = :true ORDER BY alliance.id DESC) WHERE id IS NULL;", bindparams=[true]))

        # For alliances that are no longer present in the new dump, we will
        #  NULL all the data, leaving only the name and id for FKs
        session.execute(text("UPDATE alliance SET active = :false WHERE id NOT IN (SELECT id FROM alliance_temp WHERE id IS NOT NULL);", bindparams=[false]))

        t2=time.time()-t1
        excaliburlog("Deactivate old alliances and generate new alliance ids in %.3f seconds" % (t2,))
        t1=time.time()

        # Update everything from the temp table and generate ranks
        # Deactivated items are untouched but NULLed earlier
        session.execute(text("""CREATE TABLE temp_1 (
                                  id INT, 
                                  name VARCHAR(255), 
                                  size INT, 
                                  members INT, 
                                  score INT, 
                                  points INT, 
                                  score_rank INT, 
                                  size_avg INT, 
                                  score_avg INT, 
                                  points_avg INT, 
                                  totalroundroids INT, 
                                  totallostroids INT,
				  score_total INT,
				  value_total INT
                                );

                                 INSERT INTO temp_1 SELECT t.*,
                                   COALESCE(a.totalroundroids + (GREATEST(t.size - a.size, 0)), t.size) AS totalroundroids,
                                   COALESCE(a.totallostroids + (GREATEST(a.size - t.size, 0)), 0) AS totallostroids
                                 FROM alliance AS a, alliance_temp AS t
                                   WHERE a.id = t.id AND a.active = :true;
                             """, bindparams=[true]))

        session.execute(text("""UPDATE alliance AS a,
                                (SELECT *,
                                   (SELECT COUNT(t2.totalroundroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totalroundroids < t2.totalroundroids OR (t1.totalroundroids=t2.totalroundroids AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as totalroundroids_rank,
                                   (SELECT COUNT(t2.totallostroids) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.totallostroids < t2.totallostroids OR (t1.totallostroids=t2.totallostroids AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as totallostroids_rank,
                                   (SELECT COUNT(t2.size) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.size < t2.size OR (t1.size=t2.size AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as size_rank,
                                   (SELECT COUNT(t2.points) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.points < t2.points OR (t1.points=t2.points AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as points_rank,
                                   (SELECT COUNT(t2.members) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.members < t2.members OR (t1.members=t2.members AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as members_rank,
                                   (SELECT COUNT(t2.size_avg) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.size_avg < t2.size_avg OR (t1.size_avg=t2.size_avg AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as size_avg_rank,
                                   (SELECT COUNT(t2.score_avg) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.score_avg < t2.score_avg OR (t1.score_avg=t2.score_avg AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as score_avg_rank,
                                   (SELECT COUNT(t2.points_avg) FROM temp_1 t1 JOIN temp_1 t2 ON 
                                      t1.points_avg < t2.points_avg OR (t1.points_avg=t2.points_avg AND t1.id=t2.id) WHERE t1.id=t.id
                                      GROUP BY t1.id) as points_avg_rank
                                FROM temp_1 AS t) AS t SET

                                  a.age = COALESCE(a.age, 0) + 1,
                                  a.size = t.size, a.members = t.members, a.score = t.score, a.points = t.points,
                                  a.score_total = t.score_total, a.value_total = t.value_total,
                                  a.size_avg = t.size_avg, a.score_avg = t.score_avg, a.points_avg = t.points_avg,
                                  a.ratio = CASE WHEN (t.score != 0) THEN 10000.0 * t.size / t.score ELSE 0 END,
                             """ + (
                             """
                                  a.size_growth = t.size - COALESCE(a.size - a.size_growth, 0),
                                  a.score_growth = t.score - COALESCE(a.score - a.score_growth, 0),
                                  a.points_growth = t.points - COALESCE(a.points - a.points_growth, 0),
                                  a.member_growth = t.members - COALESCE(a.members - a.member_growth, 0),
                                  a.size_growth_pc = CASE WHEN (a.size - a.size_growth != 0) THEN COALESCE((t.size - (a.size - a.size_growth)) * 100.0 / (a.size - a.size_growth), 0) ELSE 0 END,
                                  a.score_growth_pc = CASE WHEN (a.score - a.score_growth != 0) THEN COALESCE((t.score - (a.score - a.score_growth)) * 100.0 / (a.score - a.score_growth), 0) ELSE 0 END,
                                  a.points_growth_pc = CASE WHEN (a.points - a.points_growth != 0) THEN COALESCE((t.points - (a.points - a.points_growth)) * 100.0 / (a.points - a.points_growth), 0) ELSE 0 END,
                                  a.size_avg_growth = t.size_avg - COALESCE(a.size_avg - a.size_avg_growth, 0),
                                  a.score_avg_growth = t.score_avg - COALESCE(a.score_avg - a.score_avg_growth, 0),
                                  a.points_avg_growth = t.points_avg - COALESCE(a.points_avg - a.points_avg_growth, 0),
                                  a.size_avg_growth_pc = CASE WHEN (a.size_avg - a.size_avg_growth != 0) THEN COALESCE((t.size_avg - (a.size_avg - a.size_avg_growth)) * 100.0 / (a.size_avg - a.size_avg_growth), 0) ELSE 0 END,
                                  a.score_avg_growth_pc = CASE WHEN (a.score_avg - a.score_avg_growth != 0) THEN COALESCE((t.score_avg - (a.score_avg - a.score_avg_growth)) * 100.0 / (a.score_avg - a.score_avg_growth), 0) ELSE 0 END,
                                  a.points_avg_growth_pc = CASE WHEN (a.points_avg - a.points_avg_growth != 0) THEN COALESCE((t.points_avg - (a.points_avg - a.points_avg_growth)) * 100.0 / (a.points_avg - a.points_avg_growth), 0) ELSE 0 END,
                                  a.size_rank_change = t.size_rank - COALESCE(a.size_rank - a.size_rank_change, 0),
                                  a.members_rank_change = t.members_rank - COALESCE(a.members_rank - a.members_rank_change, 0),
                                  a.score_rank_change = t.score_rank - COALESCE(a.score_rank - a.score_rank_change, 0),
                                  a.points_rank_change = t.points_rank - COALESCE(a.points_rank - a.points_rank_change, 0),
                                  a.size_avg_rank_change = t.size_avg_rank - COALESCE(a.size_avg_rank - a.size_avg_rank_change, 0),
                                  a.score_avg_rank_change = t.score_avg_rank - COALESCE(a.score_avg_rank - a.score_avg_rank_change, 0),
                                  a.points_avg_rank_change = t.points_avg_rank - COALESCE(a.points_avg_rank - a.points_avg_rank_change, 0),
                                  a.totalroundroids_rank_change = t.totalroundroids_rank - COALESCE(a.totalroundroids_rank - a.totalroundroids_rank_change, 0),
                                  a.totallostroids_rank_change = t.totallostroids_rank - COALESCE(a.totallostroids_rank - a.totallostroids_rank_change, 0),
                                  a.totalroundroids_growth = t.totalroundroids - COALESCE(a.totalroundroids - a.totalroundroids_growth, 0),
                                  a.totalroundroids_growth_pc = CASE WHEN (a.totalroundroids - a.totalroundroids_growth != 0) THEN COALESCE((t.totalroundroids - (a.totalroundroids - a.totalroundroids_growth)) * 100.0 / (a.totalroundroids - a.totalroundroids_growth), 0) ELSE 0 END,
                                  a.totallostroids_growth = t.totallostroids - COALESCE(a.totallostroids - a.totallostroids_growth, 0),
                                  a.totallostroids_growth_pc = CASE WHEN (a.totallostroids - a.totallostroids_growth != 0) THEN COALESCE((t.totallostroids - (a.totallostroids - a.totallostroids_growth)) * 100.0 / (a.totallostroids - a.totallostroids_growth), 0) ELSE 0 END,
                             """ if not midnight
                                 else
                             """
                                  a.size_growth = t.size - COALESCE(a.size, 0),
                                  a.score_growth = t.score - COALESCE(a.score, 0),
                                  a.points_growth = t.points - COALESCE(a.points, 0),
                                  a.member_growth = t.members - COALESCE(a.members, 0),
                                  a.size_growth_pc = CASE WHEN (a.size != 0) THEN COALESCE((t.size - a.size) * 100.0 / a.size, 0) ELSE 0 END,
                                  a.score_growth_pc = CASE WHEN (a.score != 0) THEN COALESCE((t.score - a.score) * 100.0 / a.score, 0) ELSE 0 END,
                                  a.points_growth_pc = CASE WHEN (a.points != 0) THEN COALESCE((t.points - a.points) * 100.0 / a.points, 0) ELSE 0 END,
                                  a.size_avg_growth = t.size_avg - COALESCE(a.size_avg, 0),
                                  a.score_avg_growth = t.score_avg - COALESCE(a.score_avg, 0),
                                  a.points_avg_growth = t.points_avg - COALESCE(a.points_avg, 0),
                                  a.size_avg_growth_pc = CASE WHEN (a.size_avg != 0) THEN COALESCE((t.size_avg - a.size_avg) * 100.0 / a.size_avg, 0) ELSE 0 END,
                                  a.score_avg_growth_pc = CASE WHEN (a.score_avg != 0) THEN COALESCE((t.score_avg - a.score_avg) * 100.0 / a.score_avg, 0) ELSE 0 END,
                                  a.points_avg_growth_pc = CASE WHEN (a.points_avg != 0) THEN COALESCE((t.points_avg - a.points_avg) * 100.0 / a.points_avg, 0) ELSE 0 END,
                                  a.size_rank_change = t.size_rank - COALESCE(a.size_rank, 0),
                                  a.members_rank_change = t.members_rank - COALESCE(a.members_rank, 0),
                                  a.score_rank_change = t.score_rank - COALESCE(a.score_rank, 0),
                                  a.points_rank_change = t.points_rank - COALESCE(a.points_rank, 0),
                                  a.size_avg_rank_change = t.size_avg_rank - COALESCE(a.size_avg_rank, 0),
                                  a.score_avg_rank_change = t.score_avg_rank - COALESCE(a.score_avg_rank, 0),
                                  a.points_avg_rank_change = t.points_avg_rank - COALESCE(a.points_avg_rank, 0),
                                  a.totalroundroids_rank_change = t.totalroundroids_rank - COALESCE(a.totalroundroids_rank, 0),
                                  a.totallostroids_rank_change = t.totallostroids_rank - COALESCE(a.totallostroids_rank, 0),
                                  a.totalroundroids_growth = t.totalroundroids - COALESCE(a.totalroundroids, 0),
                                  a.totalroundroids_growth_pc = CASE WHEN (a.totalroundroids != 0) THEN COALESCE((t.totalroundroids - a.totalroundroids) * 100.0 / a.totalroundroids, 0) ELSE 0 END,
                                  a.totallostroids_growth = t.totallostroids - COALESCE(a.totallostroids, 0),
                                  a.totallostroids_growth_pc = CASE WHEN (a.totallostroids != 0) THEN COALESCE((t.totallostroids - a.totallostroids) * 100.0 / a.totallostroids, 0) ELSE 0 END,
                             """ ) +
                             """
                                  a.ticksroiding = COALESCE(a.ticksroiding, 0) + CASE WHEN (t.size > a.size) THEN 1 ELSE 0 END,
                                  a.ticksroided = COALESCE(a.ticksroided, 0) + CASE WHEN (t.size < a.size) THEN 1 ELSE 0 END,
                                  a.tickroids = COALESCE(a.tickroids, 0) + t.size,
                                  a.avroids = COALESCE((a.tickroids + t.size) / (a.age + 1.0), t.size),
                             """ + ((
                             """
                                  a.%s_highest_rank = CASE WHEN (t.%s_rank <= COALESCE(a.%s_highest_rank, t.%s_rank)) THEN t.%s_rank ELSE a.%s_highest_rank END,
                                  a.%s_highest_rank_tick = CASE WHEN (t.%s_rank <= COALESCE(a.%s_highest_rank, t.%s_rank)) THEN :tick ELSE a.%s_highest_rank_tick END,
                                  a.%s_lowest_rank = CASE WHEN (t.%s_rank >= COALESCE(a.%s_lowest_rank, t.%s_rank)) THEN t.%s_rank ELSE a.%s_lowest_rank END,
                                  a.%s_lowest_rank_tick = CASE WHEN (t.%s_rank >= COALESCE(a.%s_lowest_rank, t.%s_rank)) THEN :tick ELSE a.%s_lowest_rank_tick END,
                             """ * 7) % (("size",)*22 + ("members",)*22 + ("score",)*22 + ("points",)*22 + ("size_avg",)*22 + ("score_avg",)*22 + ("points_avg",)*22)) +
                             """
                                  a.totalroundroids = t.totalroundroids, a.totallostroids = t.totallostroids,
                                  a.totalroundroids_rank = t.totalroundroids_rank, a.totallostroids_rank = t.totallostroids_rank,
                                  a.size_rank = t.size_rank, a.members_rank = t.members_rank, a.score_rank = t.score_rank, a.points_rank = t.points_rank,
                                  a.size_avg_rank = t.size_avg_rank, a.score_avg_rank = t.score_avg_rank, a.points_avg_rank = t.points_avg_rank,
                                  a.sdiff = COALESCE(t.score - a.score, 0),
                                  a.pdiff = COALESCE(t.points - a.points, 0),
                                  a.rdiff = COALESCE(t.size - a.size, 0),
                                  a.mdiff = COALESCE(t.members - a.members, 0),
                                  a.srankdiff = COALESCE(t.score_rank - a.score_rank, 0),
                                  a.prankdiff = COALESCE(t.points_rank - a.points_rank, 0),
                                  a.rrankdiff = COALESCE(t.size_rank - a.size_rank, 0),
                                  a.mrankdiff = COALESCE(t.members_rank - a.members_rank, 0),
                                  a.savgdiff = COALESCE(t.score_avg - a.score_avg, 0),
                                  a.pavgdiff = COALESCE(t.points_avg - a.points_avg, 0),
                                  a.ravgdiff = COALESCE(t.size_avg - a.size_avg, 0),
                                  a.savgrankdiff = COALESCE(t.score_avg_rank - a.score_avg_rank, 0),
                                  a.pavgrankdiff = COALESCE(t.points_avg_rank - a.points_avg_rank, 0),
                                  a.ravgrankdiff = COALESCE(t.size_avg_rank - a.size_avg_rank, 0),
                                  a.idle = CASE WHEN ((t.score-a.score) BETWEEN (a.sdiff-1) AND (a.sdiff+1)) THEN 1 + COALESCE(a.idle, 0) ELSE 0 END
                                  WHERE a.id = t.id
                                AND a.active = :true
                            ; DROP TABLE temp_1;""", bindparams=[tick, true]))

        t2=time.time()-t1
        excaliburlog("Update alliances from temp and generate ranks in %.3f seconds" % (t2,))
        t1=time.time()

# ########################################################################### #
# ##################   HISTORY: EVERYTHING BECOMES FINAL   ################## #
# ########################################################################### #

        # Update stats
        session.execute(text("""UPDATE updates SET
                                  clusters  = (SELECT count(*) FROM cluster  WHERE cluster.active  = :true),
                                  galaxies  = (SELECT count(*) FROM galaxy   WHERE galaxy.active   = :true),
                                  planets   = (SELECT count(*) FROM planet   WHERE planet.active   = :true),
                                  alliances = (SELECT count(*) FROM alliance WHERE alliance.active = :true),
                                  c200     = (SELECT count(*) FROM planet WHERE planet.active = :true AND x = 200),
                                  ter      = (SELECT count(*) FROM planet WHERE planet.active = :true AND race LIKE 'ter%'),
                                  cat      = (SELECT count(*) FROM planet WHERE planet.active = :true AND race LIKE 'cat%'),
                                  xan      = (SELECT count(*) FROM planet WHERE planet.active = :true AND race LIKE 'xan%'),
                                  zik      = (SELECT count(*) FROM planet WHERE planet.active = :true AND race LIKE 'zik%'),
                                  etd      = (SELECT count(*) FROM planet WHERE planet.active = :true AND race LIKE 'etd%')
                                WHERE updates.id = :tick
                            ;""", bindparams=[tick, true]))

        t2=time.time()-t1
        excaliburlog("Update stats in: %.3f seconds" % (t2,))
        t1=time.time()

        # Copy the dumps to their respective history tables
        session.execute(text("INSERT INTO cluster_history SELECT :tick, :hour, :timestamp, cluster.* FROM cluster ORDER BY x ASC;", bindparams=[tick, hour, timestamp]))
        session.execute(text("INSERT INTO galaxy_history SELECT :tick, :hour, :timestamp, galaxy.* FROM galaxy ORDER BY id ASC;", bindparams=[tick, hour, timestamp]))
        session.execute(text("INSERT INTO planet_history SELECT :tick, :hour, :timestamp, planet.* FROM planet ORDER BY id ASC;", bindparams=[tick, hour, timestamp]))
        session.execute(text("INSERT INTO alliance_history SELECT :tick, :hour, :timestamp, alliance.* FROM alliance ORDER BY id ASC;", bindparams=[tick, hour, timestamp]))

        t2=time.time()-t1
        excaliburlog("History in %.3f seconds" % (t2,))
        t1=time.time()

        # Finally we can commit!
        session.commit()

        t2=time.time()-t1
        excaliburlog("Final update in %.3f seconds" % (t2,))
        t1=time.time()

        break
    except Exception, e:
        excaliburlog("Something random went wrong, sleeping for 15 seconds to hope it improves: %s" % (str(e),), traceback=True)
        session.rollback()
        time.sleep(15)
        continue

session.close()

t1=time.time()-t_start
excaliburlog("Total time taken: %.3f seconds" % (t1,))

# Measure some dicks
t_start=time.time()
last_tick = Updates.current_tick()
history_tick = bindparam("tick",max(last_tick-72, 1))
t1=time.time()
session.execute(galpenis.__table__.delete())
#session.execute(text("SELECT setval('galpenis_rank_seq', 1, :false);", bindparams=[false]))
session.execute(text("INSERT INTO galpenis (galaxy_id, penis) SELECT galaxy.id, galaxy.score - galaxy_history.score FROM galaxy, galaxy_history WHERE galaxy.active = :true AND galaxy.x != 200 AND galaxy.id = galaxy_history.id AND galaxy_history.tick = :tick ORDER BY galaxy.score - galaxy_history.score DESC;", bindparams=[history_tick, true]))
t2=time.time()-t1
excaliburlog("galpenis in %.3f seconds" % (t2,))
t1=time.time()
session.execute(apenis.__table__.delete())
#session.execute(text("SELECT setval('apenis_rank_seq', 1, :false);", bindparams=[false]))
session.execute(text("INSERT INTO apenis (alliance_id, penis) SELECT alliance.id, alliance.score - alliance_history.score FROM alliance, alliance_history WHERE alliance.active = :true AND alliance.id = alliance_history.id AND alliance_history.tick = :tick ORDER BY alliance.score - alliance_history.score DESC;", bindparams=[history_tick, true,]))
t2=time.time()-t1
excaliburlog("apenis in %.3f seconds" % (t2,))
t1=time.time()
for i in range(len(bots)):
    t2=time.time()
    session.execute("DELETE FROM %sepenis;" % (prefixes[i]))
    #session.execute(text("SELECT setval('epenis_rank_seq', 1, :false);", bindparams=[false]))
    if bots[i].getboolean("Misc", "acl"):
        session.execute(text("INSERT INTO %sepenis (user_id, penis) SELECT %susers.id, planet.score - planet_history.score FROM %susers, planet, planet_history WHERE %susers.active = :true AND %susers.group_id != 2 AND planet.active = :true AND %susers.planet_id = planet.id AND planet.id = planet_history.id AND planet_history.tick = :tick ORDER BY planet.score - planet_history.score DESC;" % (prefixes[i], prefixes[i], prefixes[i], prefixes[i], prefixes[i], prefixes[i]), bindparams=[history_tick, true]))
    else:
        session.execute(text("INSERT INTO %sepenis (user_id, penis) SELECT %susers.id, planet.score - planet_history.score FROM %susers, planet, planet_history WHERE %susers.active = :true AND %susers.access >= :member AND planet.active = :true AND %susers.planet_id = planet.id AND planet.id = planet_history.id AND planet_history.tick = :tick ORDER BY planet.score - planet_history.score DESC;" % (prefixes[i], prefixes[i], prefixes[i], prefixes[i], prefixes[i], prefixes[i]), bindparams=[bindparam("member",bots[i].getint("Access","member")), history_tick, true]))
    t3=time.time()-t2
    excaliburlog("epenis for %s in %.3f seconds" % (prefixes[i],t3,))
t2=time.time()-t1
excaliburlog("epenis in %.3f seconds" % (t2,))
session.commit()
t1=time.time()-t_start
excaliburlog("Total penis time: %.3f seconds" % (t1,))
session.close()

# Close old scan requests
t_start = time.time()
for i in range(len(bots)):
    if bots[i].getboolean("Misc", "acl"):
        session.execute(text("UPDATE %srequest SET active=:false WHERE active=:true AND tick < %s;" % (prefixes[i], planet_tick-bots[i].getint("Misc", "reqexpire")), bindparams=[false, true]))
    else:
        session.execute(text("UPDATE %srequest SET active=:false WHERE active=:true AND tick < %s;" % (prefixes[i], planet_tick-bots[i].getint("Misc", "reqexpire")), bindparams=[false, true]))
session.commit()
excaliburlog("Expired requests removed in %.3f seconds" % (time.time() - t_start))
session.close()

# Clean tick dependant graph cache
try:
    t_start=time.time()
    if Config.get("Misc", "graphing") != "cached":
        raise OSError
    shutil.rmtree("Arthur/graphs/values/")
    shutil.rmtree("Arthur/graphs/ranks/")
except OSError:
    pass
finally:
    t1=time.time()-t_start
    excaliburlog("Clean tick dependant graph cache in %.3f seconds" % (t1,))
    
# Add a newline at the end
excaliburlog("\n")