Skip to content

Instantly share code, notes, and snippets.

@svvitale
Created November 23, 2017 17:24
Show Gist options
  • Save svvitale/62e43d7b359792fc199523f0e611861e to your computer and use it in GitHub Desktop.
Save svvitale/62e43d7b359792fc199523f0e611861e to your computer and use it in GitHub Desktop.
# -*- 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
;
""")
]
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment