1

# coding: utf-8

     2

     3

"""

     4

    Database schema

     5

"""

     6

     7

from sqlalchemy import *

     8

     9

# our schema

    10

schema = MetaData()

    11

    12

#

    13

# tables

    14

#

    15

    16

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

    17

customers = Table('customers', schema,

    18

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

    19

    20

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

    22

    23

        # free-form internal notes

    24

        Column('notes', Unicode),

    25

    26

        # raw CREATE/UPDATE timestamp

    27

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

    28

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

    29

)

    30

    31

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

    32

contacts = Table('contacts', schema,

    33

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

    34

    35

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

    36

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

    37

    38

        # person's natural name

    39

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

    40

    41

        # freeform telephone number

    42

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

    43

    44

        # freeform email address

    45

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

    46

    47

        # raw CREATE/UPDATE timestamp

    48

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

    49

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

    50

)

    51

    52

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

    53

# duration

    54

orders = Table('orders', schema,

    55

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

    56

    57

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

    58

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

    59

    60

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

    61

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

    62

    63

        # short description of customer event where these items are going

    64

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

    65

    66

        # longer description of what the event is

    67

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

    68

    69

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

    70

        Column('start', DateTime),

    71

        Column('end', DateTime),

    72

    73

        # raw CREATE/UPDATE timestamp

    74

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

    75

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

    76

)

    77

    78

# editable contract terms for customer order

    79

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

    80

contract_terms = Table('contract_terms', schema,

    81

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

    82

    83

        # short description

    84

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

    85

    86

        # full terms in formatted code

    87

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

    88

)

    89