1

# coding: utf-8

     2

     3

"""

     4

    Database schema

     5

"""

     6

     7

from sqlalchemy import *

     8

     9

#

    10

# Object Mapping

    11

# (used externally)

    12

#

    13

    14

# for ORM definitions; outside of this module

    15

from sqlalchemy.orm import mapper, sessionmaker, relation, backref

    16

from sqlalchemy.orm import eagerload, eagerload_all

    17

    18

# used by Application.session

    19

session_factory = sessionmaker()

    20

    21

#

    22

# Table Schema

    23

#

    24

    25

# our schema

    26

schema = MetaData()

    27

    28

# customers are organizations or natural persons who are serving as the renting side of an order

    29

customers = Table('customers', schema,

    30

        Column('id', Integer, primary_key=True),

    31

    32

        # organization or person name (may match with contact name)

    33

        Column('name', Unicode, nullable=False, unique=True),

    34

    35

        # free-form internal notes

    36

        Column('notes', Unicode),

    37

    38

        # raw CREATE/UPDATE timestamp

    39

        Column('created_at', DateTime, nullable=False, default=func.now()),

    40

        Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()),

    41

)

    42

    43

# contacts are phone/email contact details for named persons in organizations who are active in some order

    44

contacts = Table('contacts', schema,

    45

        Column('id', Integer, primary_key=True),

    46

    47

        # reference to contact being primary for specified customer (organization or themselves)

    48

        Column('customer_id', None, ForeignKey('customers.id'), nullable=True),

    49

    50

        # person's natural name

    51

        Column('name', Unicode, nullable=True),

    52

    53

        # freeform telephone number

    54

        Column('phone', String, nullable=True),

    55

    56

        # freeform email address

    57

        Column('email', String, nullable=True),

    58

    59

        # raw CREATE/UPDATE timestamp

    60

        Column('created_at', DateTime, nullable=False, default=func.now()),

    61

        Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()),

    62

)

    63

    64

# orders are the transactions that some customer/contact makes, and involve checking out some inventory for some

    65

# duration

    66

orders = Table('orders', schema,

    67

        Column('id', Integer, primary_key=True),

    68

    69

        # ordering customer, if entered (i.e. who to bill)

    70

        Column('customer_id', None, ForeignKey('customers.id'), nullable=True),

    71

    72

        # active contact at customer side for order (i.e. who to call)

    73

        Column('contact_id', None, ForeignKey('contacts.id'), nullable=True),

    74

    75

        # short description of customer event where these items are going

    76

        Column('event_name', Unicode, nullable=True),

    77

    78

        # longer description of what the event is

    79

        Column('event_description', Unicode, nullable=True),

    80

    81

        # rough duration of event; time interval during which items are reserved

    82

        Column('event_start', DateTime),

    83

        Column('event_end', DateTime),

    84

    85

        # raw CREATE/UPDATE timestamp

    86

        Column('created_at', DateTime, nullable=False, default=func.now()),

    87

        Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()),

    88

)

    89

    90

# static inventory items

    91

items = Table('items', schema,

    92

        Column('id', Integer, primary_key=True),

    93

    94

        # item is part of a larger collection

    95

        Column('parent_id', None, ForeignKey('items.id'), nullable=True),

    96

    97

        # short name

    98

        Column('name', Unicode, nullable=False),

    99

   100

        # longer description

   101

        Column('detail', Unicode, nullable=True),

   102

   103

        # total quantity, or NULL for uncounted items

   104

        Column('quantity', Integer, nullable=True),

   105

)

   106

   118

   119

## editable contract terms for customer order

   120

## provision for standard terms, or more specific ones for certain customers

   121

#contract_terms = Table('contract_terms', schema,

   122

#        Column('id', Integer, primary_key=True),

   123

#

   124

#        # short description

   125

#        Column('name', Unicode, nullable=False),

   126

#

   127

#        # full terms in formatted code

   128

#        Column('terms', Unicode, nullable=False),

   129

#)

   130