def test(self):
user1 = self.create_user(username='******')
user2 = self.create_user(username='******')
cursor = connection.cursor()
cursor.execute('SELECT * FROM auth_user ORDER BY username')
results = dictfetchall(cursor)
self.assertEqual(len(results), 2)
self.assertIn('username', results[0])
self.assertEqual(results[0]['username'], user1.username)
self.assertIn('username', results[1])
self.assertEqual(results[1]['username'], user2.username)
from hotline.counties.models import County
from hotline.images.models import Image
from hotline.notifications.models import UserNotificationQuery
from hotline.reports.models import Report
from hotline.species.models import Species
from hotline.users.models import User
old = connections['old'].cursor()
# the severity ID and category IDs are assumed to be the same, so we aren't
# importing those
# import the species
old.execute("SELECT id, category_id, severity_id, name_sci, name_comm, remedy, resources FROM issues")
for row in dictfetchall(old):
species = Species.objects.filter(pk=row['id']).first()
if not species:
species = Species(pk=row['id'])
species.name = row['name_comm'] or ""
species.scientific_name = row['name_sci'] or ""
species.remedy = row['remedy'] or ""
species.resources = row['resources'] or ""
species.severity_id = row['severity_id']
species.category_id = row['category_id']
species.save()
with suspended_updates():
# create users
user_id_to_user_id = {}
def _copy_records(settings):
import pytz
from arcutils.db import dictfetchall
from django.apps import apps
from django.db import connections
from django.contrib.auth import get_user_model
old = connections['old'].cursor()
tz = pytz.timezone(settings.TIME_ZONE)
User = get_user_model()
Comment = apps.get_model('comments', 'Comment')
County = apps.get_model('counties', 'County')
Image = apps.get_model('images', 'Image')
Report = apps.get_model('reports', 'Report')
Species = apps.get_model('species', 'Species')
UserNotificationQuery = apps.get_model('notifications', 'UserNotificationQuery')
# Species
print('Copying species...', end='', flush=True)
old.execute("""
SELECT id, category_id, severity_id, name_sci, name_comm, remedy, resources
FROM issues
""")
for row in dictfetchall(old):
pk = row['id']
data = {
'category_id': row['category_id'],
'name': row['name_comm'] or '',
'remedy': row['remedy'] or '',
'resources': row['resources'] or '',
'scientific_name': row['name_sci'] or '',
'severity_id': row['severity_id'],
}
Species.objects.update_or_create(pk=pk, defaults=data)
print('Done')
# Users
print('Copying users...', end='', flush=True)
user_id_map = {}
report_submitter_user_id = {}
key_to_user_id = {}
old.execute("""
SELECT cardable_id, users.id AS user_id, affiliations, enabled, vcards.id, n_family,
n_given, n_prefix, n_suffix, cardable_type, email, hashed_password
FROM vcards
LEFT JOIN users ON vcards.cardable_id = users.id AND cardable_type = 'User'
ORDER BY cardable_type
""")
for row in dictfetchall(old):
email = row['email']
exists = User.objects.filter(email=email).exists()
data = {
'first_name': row['n_given'] or '',
'last_name': row['n_family'] or '',
'prefix': row['n_prefix'] or '',
'suffix': row['n_suffix'] or '',
}
if not exists:
# Only update these fields if the user wasn't already imported
data.update({
'affiliations': row['affiliations'] or '',
'is_active': bool(row['enabled']),
'password': '******'.format(**row),
})
user, created = User.objects.update_or_create(email=email, defaults=data)
user_id_map[row['user_id']] = user.pk
if row['cardable_type'] == 'Submitter':
report_submitter_user_id[row['cardable_id']] = user.pk
key_to_user_id[(row['cardable_type'], row['cardable_id'])] = user.pk
print('Done')
# Reports
print('Copying reports...', end='', flush=True)
staff_user = User.objects.filter(is_staff=True).first()
edrr_status_map = {2: 0, 3: 1, 4: 2, 5: 3, 6: 4, 7: 5}
category_map = {
5: 13, # Aquatic Vertebrates => Reptiles and Amphibians
}
old.execute("""
SELECT reports.id, category_id, issue_id, reported_category, reported_issue, has_sample,
issue_desc, private_note, location_desc, reports.created_at, reports.updated_at, closed,
user_id, edrr_status, lat, lng
FROM reports
LEFT JOIN locations ON locations.locateable_id = reports.id AND locateable_type = 'Report'
""")
for row in dictfetchall(old):
pk = row['id']
point = 'POINT({lng} {lat})'.format(**row)
actual_species_id = row['issue_id']
if not Species.objects.filter(pk=actual_species_id).exists():
actual_species_id = None
reported_species_id = row['reported_issue']
if not Species.objects.filter(pk=reported_species_id).exists():
reported_species_id = None
created_on = tz.localize(row['created_at'])
reported_category_id = row['reported_category']
reported_category_id = category_map.get(reported_category_id, reported_category_id)
data = {
'actual_species_id': actual_species_id,
'claimed_by_id': user_id_map[row['user_id']],
'county': County.objects.filter(the_geom__intersects=point).first(),
'created_by_id': report_submitter_user_id[row['id']],
'created_on': created_on,
'description': row['issue_desc'] or '',
'edrr_status': edrr_status_map.get(row['edrr_status'], None),
'has_specimen': row['has_sample'],
'is_archived': bool(row['closed'] and not row['issue_id']),
'is_public': bool(row['closed'] and row['issue_id']),
'location': row['location_desc'] or '',
'point': point,
'reported_category_id': reported_category_id,
'reported_species_id': reported_species_id,
}
report, created = Report.objects.update_or_create(pk=pk, defaults=data)
if created:
# Note: Setting created_on when creating Reports does
# nothing; now is *always* used on creation when a field is
# configured to use auto_now_add.
report.created_on = created_on
report.save()
# Create a private comment for the private_note field; the PK
# should be large to avoid colliding with the comments that are
# imported later.
if row['private_note']:
pk = report.pk + 100000
Comment.objects.update_or_create(pk=pk, defaults={
'body': row['private_note'],
'created_by': report.claimed_by or staff_user,
'created_on': tz.localize(row['created_at']),
'report_id': report.pk,
'visibility': Comment.PRIVATE,
})
print('Done')
# Comments
print('Copying comments...', end='', flush=True)
old.execute("""
SELECT private, id, report_id, annotator_id, annotator_type, body, created_at
FROM annotations
""")
for row in dictfetchall(old):
pk = row['id']
report_id = row['report_id']
report = Report.objects.get(pk=report_id)
if row['annotator_type'] == 'User':
created_by_id = user_id_map[row['annotator_id']]
elif row['annotator_type'] == 'Submitter':
created_by_id = report.created_by_id
elif row['annotator_type'] == 'Expert':
created_by_id = key_to_user_id[(row['annotator_type'], row['annotator_id'])]
else:
created_by_id = report.created_by_id
Comment.objects.update_or_create(pk=pk, defaults={
'body': row['body'] or '',
'created_by_id': created_by_id,
'created_on': tz.localize(row['created_at']),
'report_id': report_id,
'visibility': Comment.PRIVATE if row['private'] else Comment.PUBLIC,
})
print('Done')
# Images
print('Copying images...', end='', flush=True)
old.execute("""
SELECT id, imageable_id, imageable_type, filename, created_at, label
FROM images
WHERE imageable_type = 'Report'
""")
for row in dictfetchall(old):
pk = row['id']
image = posixpath.join('images', '{pk:04d}-{filename}'.format(pk=pk, **row))
report_id = row['imageable_id']
report = Report.objects.get(pk=report_id)
Image.objects.update_or_create(pk=pk, defaults={
'created_by_id': report.created_by_id,
'created_on': tz.localize(row['created_at']),
'image': image,
'name': row['label'],
'report': report,
'visibility': Image.PUBLIC,
})
print('Done')
# User notification queries
print('Creating user notification queries...', end='', flush=True)
UserNotificationQuery.objects.filter(name='Imported').delete()
old.execute("""
SELECT category_id, user_id
FROM categories_users
INNER JOIN categories ON categories.id = category_id
""")
user_to_query = defaultdict(lambda: defaultdict(list))
for row in dictfetchall(old):
user_id = user_id_map[row['user_id']]
category = 'category_id:{category_id}'.format(**row)
user_to_query[user_id]['categories'].append(category)
old.execute("""
SELECT user_id, label
FROM regions_users
INNER JOIN regions ON regions.id = region_id
""")
for row in dictfetchall(old):
user_id = user_id_map[row['user_id']]
if row['label'].lower() == 'hoodriver':
row['label'] = 'Hood River'
county = 'county:({label})'.format(**row)
user_to_query[user_id]['counties'].append(county)
for user_id, info in user_to_query.items():
categories_query = (' OR '.join(info['categories']))
counties_query = (' OR '.join(info['counties']))
if categories_query and counties_query:
query = ('(%s) AND (%s)' % (categories_query, counties_query))
elif categories_query:
query = categories_query
elif counties_query:
query = counties_query
UserNotificationQuery.objects.create(
name='Imported',
query=urlencode({'querystring': query}),
user_id=user_id,
)
print('Done')
