author | Tero Marttila <terom@fixme.fi> |
Fri, 07 Jan 2011 01:23:24 +0200 | |
changeset 31 | e1b63e4d10f4 |
parent 27 | 1cb8b78101f9 |
child 48 | 06fa83c8c0bb |
permissions | -rw-r--r-- |
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
1 |
# coding: utf-8 |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
2 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
3 |
""" |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
4 |
Database schema |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
5 |
""" |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
6 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
7 |
from sqlalchemy import * |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
8 |
|
27
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
9 |
# |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
10 |
# Object Mapping |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
11 |
# (used externally) |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
12 |
# |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
13 |
|
25
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
14 |
# for ORM definitions; outside of this module |
27
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
15 |
from sqlalchemy.orm import mapper, sessionmaker, relation |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
16 |
from sqlalchemy.orm import eagerload, eagerload_all |
25
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
17 |
|
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
18 |
# used by Application.session |
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
19 |
session_factory = sessionmaker() |
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
20 |
|
27
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
21 |
# |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
22 |
# Table Schema |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
23 |
# |
1cb8b78101f9
Use Order model for OrdersView table
Tero Marttila <terom@fixme.fi>
parents:
25
diff
changeset
|
24 |
|
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
25 |
# our schema |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
26 |
schema = MetaData() |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
27 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
28 |
# customers are organizations or natural persons who are serving as the renting side of an order |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
29 |
customers = Table('customers', schema, |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
30 |
Column('id', Integer, primary_key=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
31 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
32 |
# organization or person name (may match with contact name) |
7 | 33 |
Column('name', Unicode, nullable=False, unique=True), |
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
34 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
35 |
# free-form internal notes |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
36 |
Column('notes', Unicode), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
37 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
38 |
# raw CREATE/UPDATE timestamp |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
39 |
Column('created_at', DateTime, nullable=False, default=func.now()), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
40 |
Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
41 |
) |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
42 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
43 |
# contacts are phone/email contact details for named persons in organizations who are active in some order |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
44 |
contacts = Table('contacts', schema, |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
45 |
Column('id', Integer, primary_key=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
46 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
47 |
# reference to contact being primary for specified customer (organization or themselves) |
25
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
48 |
Column('customer_id', None, ForeignKey('customers.id'), nullable=True), |
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
49 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
50 |
# person's natural name |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
51 |
Column('name', Unicode, nullable=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
52 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
53 |
# freeform telephone number |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
54 |
Column('phone', String, nullable=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
55 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
56 |
# freeform email address |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
57 |
Column('email', String, nullable=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
58 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
59 |
# raw CREATE/UPDATE timestamp |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
60 |
Column('created_at', DateTime, nullable=False, default=func.now()), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
61 |
Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
62 |
) |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
63 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
64 |
# orders are the transactions that some customer/contact makes, and involve checking out some inventory for some |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
65 |
# duration |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
66 |
orders = Table('orders', schema, |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
67 |
Column('id', Integer, primary_key=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
68 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
69 |
# ordering customer, if entered (i.e. who to bill) |
25
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
70 |
Column('customer_id', None, ForeignKey('customers.id'), nullable=True), |
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
71 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
72 |
# active contact at customer side for order (i.e. who to call) |
25
cfb55708ee03
db: change ForeignKey columns to use _id suffix; start using SQLAlchemy ORM for Order model
Tero Marttila <terom@fixme.fi>
parents:
9
diff
changeset
|
73 |
Column('contact_id', None, ForeignKey('contacts.id'), nullable=True), |
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
74 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
75 |
# short description of customer event where these items are going |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
76 |
Column('event_name', Unicode, nullable=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
77 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
78 |
# longer description of what the event is |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
79 |
Column('event_description', Unicode, nullable=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
80 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
81 |
# rough duration of event; time interval during which items are reserved |
9
0327b83959e9
Implement working NewOrderView, and start restructuring it again right away...
Tero Marttila <terom@fixme.fi>
parents:
7
diff
changeset
|
82 |
Column('event_start', DateTime), |
0327b83959e9
Implement working NewOrderView, and start restructuring it again right away...
Tero Marttila <terom@fixme.fi>
parents:
7
diff
changeset
|
83 |
Column('event_end', DateTime), |
6
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
84 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
85 |
# raw CREATE/UPDATE timestamp |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
86 |
Column('created_at', DateTime, nullable=False, default=func.now()), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
87 |
Column('updated_at', DateTime, nullable=True, onupdate=func.current_timestamp()), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
88 |
) |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
89 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
90 |
# editable contract terms for customer order |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
91 |
# provision for standard terms, or more specific ones for certain customers |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
92 |
contract_terms = Table('contract_terms', schema, |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
93 |
Column('id', Integer, primary_key=True), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
94 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
95 |
# short description |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
96 |
Column('name', Unicode, nullable=False), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
97 |
|
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
98 |
# full terms in formatted code |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
99 |
Column('terms', Unicode, nullable=False), |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
100 |
) |
72c73df76db2
Split wsgi.py into controllers/customers/urls for now; start orders form
Tero Marttila <terom@fixme.fi>
parents:
diff
changeset
|
101 |