1

# coding: utf-8

     2

     3

"""

     4

    Database schema

     5

"""

     6

     7

from sqlalchemy import *

     8

     9

#

    15

# Object Mapping

    16

# (used externally)

    17

#

    18

    19

# for ORM definitions; outside of this module

    20

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

    21

from sqlalchemy.orm import eagerload, eagerload_all

    22

    23

# used by Application.session

    24

session_factory = sessionmaker()

    25

    26

#

    27

# Table Schema

    28

#

    29

    30

# our schema

    31

schema = MetaData()

    32

    33

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

    34

customers = Table('customers', schema,

    35

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

    36

    37

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

    38

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

    39

    40

        # free-form internal notes

    41

        Column('notes', Unicode),

    42

    43

        # raw CREATE/UPDATE timestamp

    44

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

    45

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

    46

)

    47

    48

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

    49

contacts = Table('contacts', schema,

    50

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

    51

    52

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

    53

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

    54

    55

        # person's natural name

    56

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

    57

    58

        # freeform telephone number

    59

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

    60

    61

        # freeform email address

    62

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

    63

    64

        # raw CREATE/UPDATE timestamp

    65

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

    66

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

    67

)

    68

    69

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

    70

# duration

    71

orders = Table('orders', schema,

    72

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

    73

    74

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

    75

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

    76

    77

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

    78

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

    79

    80

        # short description of customer event where these items are going

    81

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

    82

    83

        # longer description of what the event is

    84

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

    85

    86

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

    87

        Column('event_start', DateTime),

    88

        Column('event_end', DateTime),

    89

    90

        # raw CREATE/UPDATE timestamp

    91

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

    92

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

    93

)

    94

    95

# static inventory items

    96

items = Table('items', schema,

    97

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

    98

    99

        # item is part of a larger collection

   100

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

   101

   102

        # short name

   103

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

   104

   105

        # longer description

   106

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

   107

   108

        # total quantity, or NULL for uncounted items

   109

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

   110

)

   111

   112

# items included in order

   113

order_items = Table('order_items', schema,

   114

        # order we are part of

   115

        Column('order_id', None, ForeignKey('orders.id'), primary_key=True),

   116

   117

        # which item is selected

   118

        Column('item_id', None, ForeignKey('items.id'), primary_key=True),

   119

   120

        # how many out of total quantity

   121

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

   122

)

   123

   124

## editable contract terms for customer order

   125

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

   126

#contract_terms = Table('contract_terms', schema,

   127

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

   128

#

   129

#        # short description

   130

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

   131

#

   132

#        # full terms in formatted code

   133

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

   134

#)

   135