Exemplo n.º 1
0
    def handle(self, *args, **kwargs):
        self.verbosity = kwargs['verbosity']

        if self.verbosity >= 1:
            print("Looking for releasers without their name as a Nick")

        releasers = Releaser.objects.raw('''
            SELECT demoscene_releaser.*
            FROM
                demoscene_releaser
                LEFT JOIN demoscene_nick ON (
                    demoscene_releaser.id = demoscene_nick.releaser_id
                    AND demoscene_releaser.name = demoscene_nick.name
                )
            WHERE
                demoscene_nick.id IS NULL
        ''')
        for releaser in releasers:
            if self.verbosity >= 1:
                print("creating nick for %s" % releaser)
            nick = Nick(releaser=releaser, name=releaser.name)
            nick.save()

        if self.verbosity >= 1:
            print("Looking for Nicks without their name as a NickVariant")

        nicks = Nick.objects.raw('''
            SELECT demoscene_nick.*
            FROM
                demoscene_nick
                LEFT JOIN demoscene_nickvariant ON (
                    demoscene_nick.id = demoscene_nickvariant.nick_id
                    AND demoscene_nick.name = demoscene_nickvariant.name
                )
            WHERE
                demoscene_nickvariant.id IS NULL
        ''')
        for nick in nicks:
            if self.verbosity >= 1:
                print("creating nick_variant for %s" % nick)
            nick_variant = NickVariant(nick=nick, name=nick.name)
            nick_variant.save()

        if self.verbosity >= 1:
            print("Removing releaser abbreviations that are the same as the actual name")
        cursor = connection.cursor()
        cursor.execute('''
            UPDATE demoscene_nick
            SET abbreviation = ''
            WHERE LOWER(name) = LOWER(abbreviation)
        ''')

        if self.verbosity >= 1:
            print("Looking for Nicks without their abbreviation as a NickVariant")

        nicks = Nick.objects.raw('''
            SELECT demoscene_nick.*
            FROM
                demoscene_nick
                LEFT JOIN demoscene_nickvariant ON (
                    demoscene_nick.id = demoscene_nickvariant.nick_id
                    AND demoscene_nick.abbreviation = demoscene_nickvariant.name
                )
            WHERE
                demoscene_nick.abbreviation <> ''
                AND demoscene_nickvariant.id IS NULL
        ''')
        for nick in nicks:
            if self.verbosity >= 1:
                print("creating nick_variant for %s" % nick.abbreviation)
            nick_variant = NickVariant(nick=nick, name=nick.abbreviation)
            nick_variant.save()

        if self.verbosity >= 1:
            print("Truncating fuzzy dates to first of the month / first of January")
        cursor = connection.cursor()
        cursor.execute('''
            UPDATE productions_production
            SET release_date_date = date_trunc('month', release_date_date)
            WHERE
                release_date_precision = 'm'
                AND date_part('day', release_date_date) <> 1
        ''')
        cursor.execute('''
            UPDATE productions_production
            SET release_date_date = date_trunc('year', release_date_date)
            WHERE
                release_date_precision = 'y'
                AND (date_part('day', release_date_date) <> 1 or date_part('month', release_date_date) <> 1)
        ''')

        if self.verbosity >= 1:
            print("Removing abbreviations on scener nicks")
        nicks = Nick.objects.exclude(abbreviation='').filter(releaser__is_group=False)
        for nick in nicks:
            if self.verbosity >= 1:
                print("Removing abbreviation %s of %s" % (nick.abbreviation, nick.name))
            nick.abbreviation = ''
            nick.save()

        if self.verbosity >= 1:
            print("Stripping leading / trailing spaces from names and titles")
        cursor.execute('''
            UPDATE productions_production
            SET title = REGEXP_REPLACE(title, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
            WHERE title LIKE ' %%' OR title LIKE '%% '
        ''')
        cursor.execute('''
            UPDATE demoscene_releaser
            SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
            WHERE name LIKE ' %%' OR name LIKE '%% '
        ''')
        cursor.execute('''
            UPDATE demoscene_nick
            SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
            WHERE name LIKE ' %%' OR name LIKE '%% '
        ''')
        cursor.execute('''
            UPDATE demoscene_nickvariant
            SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
            WHERE name LIKE ' %%' OR name LIKE '%% '
        ''')
        cursor.execute('''
            UPDATE parties_party
            SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
            WHERE name LIKE ' %%' OR name LIKE '%% '
        ''')
        cursor.execute('''
            UPDATE parties_partyseries
            SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
            WHERE name LIKE ' %%' OR name LIKE '%% '
        ''')

        # skip this. it takes ages.
        # print("Recursively marking children of deleted scene.org dirs as deleted")
        # for dir in Directory.objects.filter(is_deleted=True):
        #    dir.mark_deleted()

        if self.verbosity >= 1:
            print("Converting invitation competitions to party invitation relations")
        invitation_compos = Competition.objects.filter(name__istartswith='invitation').select_related('party')
        for compo in invitation_compos:
            placings = compo.placings.select_related('production')

            is_real_compo = False
            for placing in placings:
                if placing.ranking != '' or placing.score != '':
                    is_real_compo = True
                compo.party.invitations.add(placing.production)

            if not is_real_compo:
                compo.delete()

        if self.verbosity >= 1:
            print("Checking encodings on results files")
        results_files = ResultsFile.objects.all()
        for results_file in results_files:
            try:
                results_file.text
            except UnicodeDecodeError:
                if self.verbosity >= 1:
                    print(
                        "Error on /parties/%d/results_file/%d/ - cannot decode as %r"
                        % (results_file.party_id, results_file.id, results_file.encoding)
                    )
            except IOError:
                # ignore files that aren't on disk (which probably means this is a local dev instance with a live db)
                pass

        if self.verbosity >= 1:
            print("Deleting unused tags")
        Tag.objects.annotate(num_prods=Count('taggit_taggeditem_items')).filter(num_prods=0).delete()

        print("Fixing has_screenshots flag on productions that claim not to have screenshots but do")
        cursor.execute('''
            UPDATE productions_production SET has_screenshot = 't'
            WHERE productions_production.id IN (
                SELECT productions_production.id
                FROM productions_production
                INNER JOIN productions_screenshot on (
                    productions_production.id = productions_screenshot.production_id
                    and thumbnail_url <> ''
                )
                WHERE has_screenshot = 'f'
            )
        ''')
        print("Fixing has_screenshots flag on productions that claim to have screenshots but don't")
        cursor.execute('''
            UPDATE productions_production SET has_screenshot = 'f'
            WHERE productions_production.id IN (
                SELECT productions_production.id
                FROM productions_production
                LEFT JOIN productions_screenshot on (
                    productions_production.id = productions_screenshot.production_id
                    and thumbnail_url <> ''
                )
                WHERE productions_screenshot.production_id is null and has_screenshot = 't'
            )
        ''')

        if self.verbosity >= 1:
            print("Deleting duplicate soundtrack links")
        cursor.execute('''
            select p1.production_id, p1.soundtrack_id, min(p1.position) AS position
            from productions_soundtracklink as p1
            inner join productions_soundtracklink as p2 on (
                p1.production_id = p2.production_id and p1.soundtrack_id = p2.soundtrack_id
                and p1.id <> p2.id
            )
            group by p1.production_id, p1.soundtrack_id
        ''')
        for (prod_id, soundtrack_id, position) in cursor.fetchall():
            SoundtrackLink.objects.filter(
                production_id=prod_id, soundtrack_id=soundtrack_id, position__gt=position
            ).delete()

        if self.verbosity >= 1:
            print("Deleting duplicate pack members")
        cursor.execute('''
            select p1.pack_id, p1.member_id, min(p1.position) AS position
            from productions_packmember as p1
            inner join productions_packmember as p2 on (
                p1.pack_id = p2.pack_id and p1.member_id = p2.member_id
                and p1.id <> p2.id
            )
            group by p1.pack_id, p1.member_id
        ''')
        for (pack_id, member_id, position) in cursor.fetchall():
            PackMember.objects.filter(pack_id=pack_id, member_id=member_id, position__gt=position).delete()

        if self.verbosity >= 1:
            print("Closing gaps in pack member sequences")
        for k, pms in groupby(PackMember.objects.order_by('pack_id', 'position'), lambda pm: pm.pack_id):
            for i, pm in enumerate(pms):
                if i + 1 != pm.position:
                    pm.position = i + 1
                    pm.save()

        if self.verbosity >= 1:
            print("Closing gaps in soundtrack sequences")
        for k, stls in groupby(
            SoundtrackLink.objects.order_by('production_id', 'position'),
            lambda stl: stl.production_id
        ):
            for i, stl in enumerate(stls):
                if i + 1 != stl.position:
                    stl.position = i + 1
                    stl.save()

        if self.verbosity >= 1:
            print("Marking diskmags with pack contents as packs")
        diskmag_id = ProductionType.objects.get(name='Diskmag').id
        artpack_id = ProductionType.objects.get(internal_name='artpack').id
        pack = ProductionType.objects.get(internal_name='pack')
        for prod in Production.objects.raw('''
            SELECT distinct pack_id AS id
            FROM productions_packmember
            INNER JOIN productions_production AS packmember ON (member_id = packmember.id)
            WHERE
            pack_id NOT IN (
                select production_id from productions_production_types
                where productiontype_id in (%(pack)s, %(artpack)s)
            )
            AND pack_id IN (
                select production_id from productions_production_types where productiontype_id = %(diskmag)s
            )
            AND packmember.supertype <> 'music'
        ''', {'pack': pack.id, 'artpack': artpack_id, 'diskmag': diskmag_id}):
            prod.types.add(pack)

        if self.verbosity >= 1:
            print("Reassigning music in pack contents that looks like it should be soundtrack links")
        # get the most common prodtypes that have soundtracks mis-categorised as pack contents
        non_pack_type_ids = ProductionType.objects.filter(
            name__in=['Musicdisk', 'Chip Music Pack', 'Demo', 'Diskmag']
        ).values_list('id', flat=True)
        pack_type_id = ProductionType.objects.get(name='Pack').id
        actual_pack_ids = Production.objects.filter(types__id=pack_type_id).values_list('id', flat=True)
        # get packmembers which are music, inside productions that are one of our target types, but not actual packs
        pack_contents = PackMember.objects.filter(
            pack__types__in=non_pack_type_ids, member__supertype='music'
        ).exclude(
            pack_id__in=actual_pack_ids
        ).order_by('pack_id', 'position')
        for pack_id, pack_members in groupby(pack_contents, lambda pm: pm.pack_id):
            soundtrack_ids = list(
                SoundtrackLink.objects.filter(production_id=pack_id).order_by('position')
                .values_list('soundtrack_id', flat=True)
            )
            for pack_member in pack_members:
                if pack_member.member_id not in soundtrack_ids:
                    SoundtrackLink.objects.create(
                        production_id=pack_id, soundtrack_id=pack_member.member_id,
                        position=len(soundtrack_ids) + 1, data_source=pack_member.data_source
                    )
                    soundtrack_ids.append(pack_member.member_id)
                pack_member.delete()

        if self.verbosity >= 1:
            print("done.")
Exemplo n.º 2
0
	def handle_noargs(self, **options):
		print "Looking for releasers without their name as a Nick"

		releasers = Releaser.objects.raw('''
			SELECT demoscene_releaser.*
			FROM
				demoscene_releaser
				LEFT JOIN demoscene_nick ON (
					demoscene_releaser.id = demoscene_nick.releaser_id
					AND demoscene_releaser.name = demoscene_nick.name
				)
			WHERE
				demoscene_nick.id IS NULL
		''')
		for releaser in releasers:
			print "creating nick for %s" % releaser
			nick = Nick(releaser=releaser, name=releaser.name)
			nick.save()

		print "Looking for Nicks without their name as a NickVariant"

		nicks = Nick.objects.raw('''
			SELECT demoscene_nick.*
			FROM
				demoscene_nick
				LEFT JOIN demoscene_nickvariant ON (
					demoscene_nick.id = demoscene_nickvariant.nick_id
					AND demoscene_nick.name = demoscene_nickvariant.name
				)
			WHERE
				demoscene_nickvariant.id IS NULL
		''')
		for nick in nicks:
			print "creating nick_variant for %s" % nick
			nick_variant = NickVariant(nick=nick, name=nick.name)
			nick_variant.save()

		print "Removing releaser abbreviations that are the same as the actual name"
		cursor = connection.cursor()
		cursor.execute('''
			UPDATE demoscene_nick
			SET abbreviation = ''
			WHERE LOWER(name) = LOWER(abbreviation)
		''')

		print "Looking for Nicks without their abbreviation as a NickVariant"

		nicks = Nick.objects.raw('''
			SELECT demoscene_nick.*
			FROM
				demoscene_nick
				LEFT JOIN demoscene_nickvariant ON (
					demoscene_nick.id = demoscene_nickvariant.nick_id
					AND demoscene_nick.abbreviation = demoscene_nickvariant.name
				)
			WHERE
				demoscene_nick.abbreviation <> ''
				AND demoscene_nickvariant.id IS NULL
		''')
		for nick in nicks:
			print "creating nick_variant for %s" % nick.abbreviation
			nick_variant = NickVariant(nick=nick, name=nick.abbreviation)
			nick_variant.save()

		print "Truncating fuzzy dates to first of the month / first of January"
		cursor = connection.cursor()
		cursor.execute('''
			UPDATE productions_production
			SET release_date_date = date_trunc('month', release_date_date)
			WHERE release_date_precision = 'm'
		''')
		cursor.execute('''
			UPDATE productions_production
			SET release_date_date = date_trunc('year', release_date_date)
			WHERE release_date_precision = 'y'
		''')

		print "Removing abbreviations on scener nicks"
		nicks = Nick.objects.exclude(abbreviation='').filter(releaser__is_group=False)
		for nick in nicks:
			print "Removing abbreviation %s of %s" % (nick.abbreviation, nick.name)
			nick.abbreviation = ''
			nick.save()

		print "Stripping leading / trailing spaces from names and titles"
		cursor.execute('''
			UPDATE productions_production
			SET title = REGEXP_REPLACE(title, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
			WHERE title LIKE ' %%' OR title LIKE '%% '
		''')
		cursor.execute('''
			UPDATE demoscene_releaser
			SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
			WHERE name LIKE ' %%' OR name LIKE '%% '
		''')
		cursor.execute('''
			UPDATE demoscene_nick
			SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
			WHERE name LIKE ' %%' OR name LIKE '%% '
		''')
		cursor.execute('''
			UPDATE demoscene_nickvariant
			SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
			WHERE name LIKE ' %%' OR name LIKE '%% '
		''')
		cursor.execute('''
			UPDATE parties_party
			SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
			WHERE name LIKE ' %%' OR name LIKE '%% '
		''')
		cursor.execute('''
			UPDATE parties_partyseries
			SET name = REGEXP_REPLACE(name, E'^\\\\s*(.*?)\\\\s*$', E'\\\\1', 'g')
			WHERE name LIKE ' %%' OR name LIKE '%% '
		''')

		# skip this. it takes ages.
		# print "Recursively marking children of deleted scene.org dirs as deleted"
		# for dir in Directory.objects.filter(is_deleted=True):
		#	dir.mark_deleted()

		print "Converting invitation competitions to party invitation relations"
		invitation_compos = Competition.objects.filter(name__istartswith='invitation').select_related('party')
		for compo in invitation_compos:
			placings = compo.placings.select_related('production')

			is_real_compo = False
			for placing in placings:
				if placing.ranking != '' or placing.score != '':
					is_real_compo = True
				compo.party.invitations.add(placing.production)

			if not is_real_compo:
				compo.delete()

		print "Checking encodings on results files"
		results_files = ResultsFile.objects.all()
		for results_file in results_files:
			try:
				results_file.text
			except UnicodeDecodeError:
				print "Error on /parties/%d/results_file/%d/ - cannot decode as %r" % (results_file.party_id, results_file.id, results_file.encoding)
			except IOError:
				pass  # ignore files that aren't on disk (which probably means this is a local dev instance with a live db)

		print "Deleting unused tags"
		Tag.objects.annotate(num_prods=Count('taggit_taggeditem_items')).filter(num_prods=0).delete()

		print "Setting has_screenshots flag on productions"
		cursor.execute('''
			UPDATE productions_production SET has_screenshot = (
				id IN (
					SELECT DISTINCT production_id
					FROM productions_screenshot
					WHERE thumbnail_url <> ''
				)
			)
		''')

		print "done."