0035_hydrated_connection_view_v3.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

    dependencies = [
        ('spigot', '0034_hydrated_connection_view_v2'),
    ]

    operations = [
        migrations.RunSQL("""
           drop view if exists spigot_hydratedconnection;
            create view spigot_hydratedconnection as
            select
              spigot_connection.id as connection_id,
              spigot_connection.time_occurred as connection_time_occurred,
              spigot_connection.time_of_checkout as connection_time_of_checkout,
              spigot_card.id as card_id,
              spigot_card.uid as card_uid,
              spigot_event.id as event_id,
              spigot_event.name as event_name,
              spigot_company.id as company_id,
              spigot_company.name as company_name,
              spigot_company.url as company_url,
              spigot_booth.id as booth_id,
              spigot_booth.number as booth_number,
              spigot_booth.name as booth_name,
              attendee_user.id as user_id,
              attendee_user.email as user_email,
              attendee_user.name as user_name,
              attendee_user.company as user_company,
              attendee_user.title as user_title,
              attendee_user.photo as user_photo,
              spigot_annotation.id as annotation_id,
              spigot_annotation.stars as annotation_stars,
              spigot_annotation.owner_id as annotation_owner
            from spigot_connection
              join spigot_event on spigot_connection.event_id = spigot_event.id
              join spigot_booth on cast(spigot_connection.reader_id as int) = spigot_booth.number and spigot_connection.event_id = spigot_booth.event_id
              join spigot_company on spigot_booth.company_id = spigot_company.id
              join spigot_card on spigot_connection.card_id = spigot_card.uid
              left join spigot_userprofile attendee_user on spigot_card.user_id = attendee_user.id
              left join spigot_annotation on spigot_connection.id = spigot_annotation.connection_id
              left join spigot_userprofile vendor_user on spigot_annotation.owner_id = vendor_user.id
            ;
        """)
    ]

models.py

class HydratedConnection(ExtendedModel):
    connection_id = models.IntegerField('connection id', primary_key=True)
    connection_time_occurred = models.DateTimeField("time the connection event occurred at the node", blank=False)
    connection_time_of_checkout = models.DateTimeField("time the connection ended event occurred at the node",
                                                       blank=True, null=True)
    card_id = models.IntegerField('card id', null=True)
    card_uid = models.CharField("unique identifier", max_length=64, null=True)
    card_spigot_id = models.CharField("spigot identifier", max_length=64, null=True)
    event_id = models.IntegerField('event id')
    event_name = models.CharField("event name", max_length=255, blank=False)
    company_id = models.IntegerField('company id')
    company_name = models.CharField(max_length=100, blank=False, unique=True)
    company_url = models.URLField("company web site", blank=True, null=True)
    booth_id = models.IntegerField('booth id')
    booth_reader_id = models.IntegerField('booth reader id')
    booth_name = models.CharField("name of session/booth", max_length=1024, default=None, null=True, blank=True)
    user_id = models.IntegerField('user id', null=True)
    user_email = models.EmailField("user's email address", blank=False, unique=True)
    user_name = models.CharField("user's full name", max_length=128, blank=False, default="Unregistered User")
    user_company = models.CharField("user's self-described company", max_length=255, blank=True, null=True)
    user_title = models.CharField("user's self-described title", max_length=255, blank=True, null=True)
    user_photo = models.URLField("user's profile picture", blank=True, null=True)
    vendor_id = models.IntegerField('vendor id')

    class Meta:
        managed = False