Example database queries

Activity.py

# vim:encoding=utf-8:ts=2:sw=2:expandtab

from AppStruct.Util import *
from AppStruct.WSGI.Util import ML

from HomeSchool import App
from datetime import date

from . import Student
from . import Subject
from . import Project
from . import Book

###############################################################################


class Activity:
  '''
  Public RO Attributes:
    Activity_MNID (int)
    IsNew (bool)

  Public RW Attributes:
    Date (date)
    Summary (str)
    Description (str)
    Student_MSID_Set (set of str)
    Subject_MSID_Set (set of str)
    Project_MSID_Set (set of str)
    Activity_Image_LNID_List (list of int)
    
  '''
  #============================================================================
  
  @property
  def Activity_MNID(self):
    return self._Activity_MNID

  @property
  def IsNew(self):
    return self._IsNew

  @property
  def Students(self):
    return (Student.Student(id) for id in self.Student_MSID_Set)

  @property
  def Subjects(self):
    return (Subject.Subject(id) for id in self.Subject_MSID_Set)
  
  @property
  def Projects(self):
    return (Project.Project(id) for id in self.Project_MSID_Set)

  @property
  def Books(self):
    return (Book.Book(id) for id in self.Book_MNID_List)

  @property
  def Images(self):
    return (Activity_Image(self.Activity_MNID, id) for id in self.Activity_Image_LNID_List)
  
  #============================================================================
  def __init__(self, Activity_MNID):
    self._Activity_MNID = Activity_MNID

    # Create a new, empty object
    if self.Activity_MNID == 0:
      self.Date = date.today()
      self.Summary = ''
      self.Description = ''
      self.Student_MSID_Set = {}
      self.Subject_MSID_Set = {}
      self.Project_MSID_Set = {}
      self._IsNew = True

    else:
      tmp = App.DB.Row('''
        SELECT
          "Date",
          "Summary",
          "Description"
        FROM 
          "Activity"
        WHERE True
          AND "Activity_MNID" = $Activity_MNID
        ''',
        Activity_MNID = Activity_MNID
        )

      self.Date = tmp.Date
      self.Summary = tmp.Summary
      self.Description = tmp.Description

      self.Student_MSID_Set = self.Get_Student_MSID_Set()
      self.Subject_MSID_Set = self.Get_Subject_MSID_Set()
      self.Project_MSID_Set = self.Get_Project_MSID_Set()
      self.Book_MNID_List   = self.Get_Book_MNID_List()

      self.Activity_Image_LNID_List = App.DB.ValueList('''
        SELECT 
          "Activity_Image_LNID"
        FROM 
          "Activity_Image" 
        WHERE True
          AND "Activity_MNID" = $Activity_MNID
        ''',
        Activity_MNID = self.Activity_MNID
        )

      self._IsNew = False
    pass#if

  
  #============================================================================
  def Save(self):
    
    self.Summary = self.Summary.strip()
    self.Description = self.Description.strip()

    EL = []

    if self.Date == None:
      EL.append(('Date', 'Date value could not be parsed.  Please use mm/dd/yyyy format.'))

    if len(self.Summary) < 1 or len(self.Summary) > 100:
      EL.append(('Summary', 'Summary must be between 1 and 100 characters in length.'))
      
    if EL:
      raise ValidationError(EL)


    with App.DB.Transaction():
      
      if self.Activity_MNID == 0:
        self._Activity_MNID = App.DB.Value('''
          INSERT INTO 
            "Activity"
            ([Field])
          VALUES
            ([Value])
          RETURNING
            "Activity_MNID"
          ''',
          ('Date'         , self.Date),
          ('Summary'      , self.Summary),
          ('Description'  , self.Description),
          )
      else:
        App.DB.Execute('''
          UPDATE 
            "Activity"
          SET
            [Field=Value]
          WHERE True
            AND "Activity_MNID" = $Activity_MNID
          ''',
          ('Date'         , self.Date),
          ('Summary'      , self.Summary),
          ('Description'  , self.Description),
          ('ChangeDate'   , '', 'NOW()'),
          Activity_MNID   = self.Activity_MNID,
          )
        
      pass#with
      
      
      # Here are the current values in the database
      curr = aadict()
      curr.Student_MSID_Set = self.Get_Student_MSID_Set()
      curr.Subject_MSID_Set = self.Get_Subject_MSID_Set()
      curr.Project_MSID_Set = self.Get_Project_MSID_Set()

      
      InsertList = list(set.difference(self.Student_MSID_Set, curr.Student_MSID_Set))
      RemoveList = list(set.difference(curr.Student_MSID_Set, self.Student_MSID_Set))

      #remove 
      App.DB.Execute('''
        DELETE FROM
          "Activity_Student"
        WHERE true
          AND "Activity_MNID" = $Activity_MNID
          AND "Student_MSID" = ANY ($RemoveList::text[])
        ''',
        Activity_MNID = self.Activity_MNID,
        RemoveList    = RemoveList,
        )

      #add
      for id in InsertList:
        App.DB.Execute('''
          INSERT INTO
            "Activity_Student"
            ([Field])
          VALUES
            ([Value])
          ''',
          ('Activity_MNID' , self.Activity_MNID),
          ('Student_MSID'  , id),
          )

      InsertList = list(set.difference(self.Subject_MSID_Set, curr.Subject_MSID_Set))
      RemoveList = list(set.difference(curr.Subject_MSID_Set, self.Subject_MSID_Set))

      #remove 
      App.DB.Execute('''
        DELETE FROM
          "Activity_Subject"
        WHERE true
          AND "Activity_MNID" = $Activity_MNID
          AND "Subject_MSID" = ANY ($RemoveList::text[])
        ''',
        Activity_MNID = self.Activity_MNID,
        RemoveList    = RemoveList,
        )

      #add
      for id in InsertList:
        App.DB.Execute('''
          INSERT INTO
            "Activity_Subject"
            ([Field])
          VALUES
            ([Value])
          ''',
          ('Activity_MNID' , self.Activity_MNID),
          ('Subject_MSID'  , id),
          )

      InsertList = list(set.difference(self.Project_MSID_Set, curr.Project_MSID_Set))
      RemoveList = list(set.difference(curr.Project_MSID_Set, self.Project_MSID_Set))

      #remove 
      App.DB.Execute('''
        DELETE FROM
          "Activity_Project"
        WHERE true
          AND "Activity_MNID" = $Activity_MNID
          AND "Project_MSID" = ANY ($RemoveList::text[])
        ''',
        Activity_MNID = self.Activity_MNID,
        RemoveList    = RemoveList,
        )

      #add
      for id in InsertList:
        App.DB.Execute('''
          INSERT INTO
            "Activity_Project"
            ([Field])
          VALUES
            ([Value])
          ''',
          ('Activity_MNID' , self.Activity_MNID),
          ('Project_MSID'  , id),
          )

      # Invalidate any book page renderings that reference this activity
      App.DB.Execute('''
        UPDATE
          "Book_Page_Render"
        SET 
          "ChangeDate" = now()
        WHERE true
          AND "Activity_MNID" = $Activity_MNID
        ''',
        Activity_MNID = self.Activity_MNID,
        )
      
      # Update the change date on any book pages that reference this activity
      App.DB.Execute('''
        UPDATE
          "Book_Page"
        SET
          "ChangeDate" = NOW()
        WHERE true
          AND "Activity_MNID" = $Activity_MNID
        ''',
        Activity_MNID = self.Activity_MNID,
        )

  #============================================================================
  def Delete(self):
    if self.Activity_MNID == 0:
      return

    App.DB.Execute('''
      DELETE FROM 
        "Activity"
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
      ''',
      Activity_MNID = self.Activity_MNID
      )

    self._Activity_MNID = 0

  
  #============================================================================
  def GetImage(self, Activity_Image_LNID, Size):
    
    if self.Activity_MNID == 0:
      raise InvalidOperation('Cannot get image from unsaved activity.')
    
    if Size == 'Max':
      sqlField = '"Image_Data"'
    elif Size in ('64x64', '128x128', '256x256', '512x512', '1024x1024'):
      sqlField = '"JPEG_' + Size + '"'
    else:
      raise ValueError('Invalid Size... ' + str(Size))
      
    return App.DB.Value('''
      SELECT 
        ''' + sqlField + '''
      FROM
        "Activity_Image"
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
        AND "Activity_Image_LNID" = $Activity_Image_LNID
      ''',
      Activity_MNID         = self.Activity_MNID,
      Activity_Image_LNID   = Activity_Image_LNID,
      )


  #============================================================================
  def AddImage(
    self, *, 
    File_Name, 
    File_ContentType, 
    File_Size, 
    File_Data, 
    JPEG_64x64,
    JPEG_128x128,
    JPEG_256x256,
    JPEG_512x512,
    JPEG_1024x1024,
    Image_Width,
    Image_Height,
    Image_Type,
    Image_Data
    ):

    if self.Activity_MNID == 0:
      raise InvalidOperation('Cannot add image to unsaved activity.')


    with App.DB.Transaction():
      Activity_Image_LNID = App.DB.Value('''
        SELECT 
          COALESCE(MAX("Activity_Image_LNID"), 0) + 1
        FROM
          "Activity_Image"
        WHERE True
          AND "Activity_MNID" = $Activity_MNID
        ''',
        Activity_MNID = self.Activity_MNID
        )

      App.DB.Execute('''
        INSERT INTO 
          "Activity_Image"
          ([Field])
        VALUES
          ([Value])
        ''',
        ('Activity_MNID'        , self.Activity_MNID),
        ('Activity_Image_LNID'  , Activity_Image_LNID),
        ('File_Name'            , File_Name),
        ('File_ContentType'     , File_ContentType),   
        ('File_Size'            , File_Size),
        ('File_Data'            , File_Data),
        ('JPEG_64x64'           , JPEG_64x64),
        ('JPEG_128x128'         , JPEG_128x128),
        ('JPEG_256x256'         , JPEG_256x256),
        ('JPEG_512x512'         , JPEG_512x512),
        ('JPEG_1024x1024'       , JPEG_1024x1024),
        ('Image_Width'          , Image_Width),
        ('Image_Height'         , Image_Height),
        ('Image_Type'           , Image_Type),
        ('Image_Data'           , Image_Data),
        )

    return Activity_Image_LNID

  #============================================================================
  def Get_Student_MSID_Set(self):
    return App.DB.ValueSet('''
      SELECT 
        "Student_MSID"
      FROM 
        "Activity_Student" 
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
      ''',
      Activity_MNID = self.Activity_MNID
      )

  #============================================================================
  def Get_Subject_MSID_Set(self):
    return App.DB.ValueSet('''
      SELECT 
        "Subject_MSID"
      FROM 
        "Activity_Subject" 
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
      ''',
      Activity_MNID = self.Activity_MNID
      )
      
  #============================================================================
  def Get_Project_MSID_Set(self):
    return App.DB.ValueSet('''
      SELECT 
        "Project_MSID"
      FROM 
        "Activity_Project" 
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
      ''',
      Activity_MNID = self.Activity_MNID
      )

  #============================================================================
  def Get_Book_MNID_List(self):
    return App.DB.ValueList('''
      SELECT 
        "Book_MNID"
      FROM 
        "Book_Page" 
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
      ORDER BY 
        "Book_MNID"
      ''',
      Activity_MNID = self.Activity_MNID
      )

###############################################################################

def List():
  
  tmp = App.DB.ValueList('''
    SELECT
      "Activity_MNID"
    FROM
      "Activity"
    ORDER BY
      "Date"
    ''')

  return [Activity(id) for id in tmp]
      
      
        
        


###############################################################################
class Activity_Image:
  '''
  self (object):
    Activity_MNID (int)
    Activity_Image_LNID (str)
    Name (str)
    ContentType (str)
    Size (int)
    
    GetURL(Size) => (str)
  '''
  #============================================================================

  @property
  def Activity_MNID(self):
    return self._Activity_MNID

  @property
  def Activity_Image_LNID(self):
    return self._Activity_Image_LNID

  @property
  def Name(self):
    return self._File_Name

  @property
  def ContentType(self):
    return self._File_ContentType

  @property
  def Size(self):
    return self._File_Size

  @property
  def Image_Width(self):
    return self._Image_Width

  @property
  def Image_Height(self):
    return self._Image_Height

  @property
  def Image_Type(self):
    return self._Image_Type

  @property
  def Image_Size(self):
    return self._Image_Size

  @property
  def Image_Data(self):
    return App.DB.Value('''
      SELECT
        "Image_Data"
      FROM 
        "Activity_Image"
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
        AND "Activity_Image_LNID" = $Activity_Image_LNID
      ''',
      Activity_MNID         = self.Activity_MNID,
      Activity_Image_LNID   = self.Activity_Image_LNID,
      )

  #============================================================================
  def __init__(self, Activity_MNID, Activity_Image_LNID):
    self._Activity_MNID = Activity_MNID
    self._Activity_Image_LNID = Activity_Image_LNID

    tmp = App.DB.Row('''
      SELECT
        "File_Name",
        "File_ContentType",
        "File_Size",
        "Image_Width",
        "Image_Height",
        "Image_Type",
        LENGTH("Image_Data") AS "Image_Size"
      FROM 
        "Activity_Image"
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
        AND "Activity_Image_LNID" = $Activity_Image_LNID
      ''',
      Activity_MNID         = self.Activity_MNID,
      Activity_Image_LNID   = self.Activity_Image_LNID,
      )

    self._File_Name         = tmp.File_Name
    self._File_ContentType  = tmp.File_ContentType
    self._File_Size         = tmp.File_Size
    self._Image_Width       = tmp.Image_Width
    self._Image_Height      = tmp.Image_Height
    self._Image_Type        = tmp.Image_Type
    self._Image_Size        = tmp.Image_Size
    

  #============================================================================
  def GetURL(self, Size):
    return ML(
      App.URL_Standard + '/Core/Activity/GetImage', 
      Activity_MNID = self.Activity_MNID,
      Activity_Image_LNID = self.Activity_Image_LNID,
      Size = Size,
      )
  
  #============================================================================
  def Delete(self):
    App.DB.Execute('''
      DELETE FROM 
        "Activity_Image"
      WHERE True
        AND "Activity_MNID" = $Activity_MNID
        AND "Activity_Image_LNID" = $Activity_Image_LNID
      ''',
      Activity_MNID         = self.Activity_MNID,
      Activity_Image_LNID   = self.Activity_Image_LNID,
      )

    self._Activity_Image_LNID = 0