1from __future__ import absolute_import, print_function
2
3from pony.orm.core import *
4from decimal import Decimal
5from datetime import date
6
7db = Database()
8
9class Faculty(db.Entity):
10    _table_ = 'Faculties'
11    number = PrimaryKey(int)
12    name = Required(str, unique=True)
13    departments = Set('Department')
14
15class Department(db.Entity):
16    _table_ = 'Departments'
17    number = PrimaryKey(int)
18    name = Required(str, unique=True)
19    faculty = Required(Faculty)
20    teachers = Set('Teacher')
21    majors = Set('Major')
22    groups = Set('Group')
23
24class Group(db.Entity):
25    _table_ = 'Groups'
26    number = PrimaryKey(int)
27    grad_year = Required(int)
28    department = Required(Department, column='dep')
29    lessons = Set('Lesson', columns=['day_of_week', 'meeting_time', 'classroom_number', 'building'])
30    students = Set('Student')
31
32class Student(db.Entity):
33    _table_ = 'Students'
34    name = Required(str)
35    scholarship = Required(Decimal, 10, 2, default=Decimal('0.0'))
36    group = Required(Group)
37    grades = Set('Grade')
38
39class Major(db.Entity):
40    _table_ = 'Majors'
41    name = PrimaryKey(str)
42    department = Required(Department)
43    courses = Set('Course')
44
45class Subject(db.Entity):
46    _table_ = 'Subjects'
47    name = PrimaryKey(str)
48    courses = Set('Course')
49    teachers = Set('Teacher')
50
51class Course(db.Entity):
52    _table_ = 'Courses'
53    major = Required(Major)
54    subject = Required(Subject)
55    semester = Required(int)
56    composite_key(major, subject, semester)
57    lect_hours = Required(int)
58    pract_hours = Required(int)
59    credit = Required(int)
60    lessons = Set('Lesson')
61    grades = Set('Grade')
62
63class Lesson(db.Entity):
64    _table_ = 'Lessons'
65    day_of_week = Required(int)
66    meeting_time = Required(int)
67    classroom = Required('Classroom')
68    PrimaryKey(day_of_week, meeting_time, classroom)
69    course = Required(Course)
70    teacher = Required('Teacher')
71    groups = Set(Group)
72
73class Grade(db.Entity):
74    _table_ = 'Grades'
75    student = Required(Student)
76    course = Required(Course)
77    PrimaryKey(student, course)
78    teacher = Required('Teacher')
79    date = Required(date)
80    value = Required(str)
81
82class Teacher(db.Entity):
83    _table_ = 'Teachers'
84    name = Required(str)
85    degree = Optional(str)
86    department = Required(Department)
87    subjects = Set(Subject)
88    lessons = Set(Lesson)
89    grades = Set(Grade)
90
91class Building(db.Entity):
92    _table_ = 'Buildings'
93    number = PrimaryKey(str)
94    description = Optional(str)
95    classrooms = Set('Classroom')
96
97class Classroom(db.Entity):
98    _table_ = 'Classrooms'
99    building = Required(Building)
100    number = Required(str)
101    PrimaryKey(building, number)
102    description = Optional(str)
103    lessons = Set(Lesson)
104
105db.bind('sqlite', 'university2.sqlite', create_db=True)
106#db.bind('mysql', host='localhost', user='pony', passwd='pony', db='university2')
107#db.bind('postgres', user='pony', password='pony', host='localhost', database='university2')
108#db.bind('oracle', 'university2/pony@localhost')
109
110db.generate_mapping(create_tables=True)
111
112sql_debug(True)
113
114def test_queries():
115    # very simple query
116    select(s for s in Student)[:]
117
118    # one condition
119    select(s for s in Student if s.scholarship > 0)[:]
120
121    # multiple conditions
122    select(s for s in Student if s.scholarship > 0 and s.group.number == 4142)[:]
123
124    # no join here - attribute can be found in table Students
125    select(s for s in Student if s.group.number == 4142)[:]
126
127    # automatic join of two tables because grad_year is stored in table Groups
128    select(s for s in Student if s.group.grad_year == 2011)[:]
129
130    # still two tables are joined
131    select(s for s in Student if s.group.department.number == 44)[:]
132
133    # automatic join of tree tables
134    select(s for s in Student if s.group.department.name == 'Ancient Philosophy')[:]
135
136    # manual join of tables will produce equivalent query
137    select(s for s in Student for g in Group if s.group == g and g.department.name == 'Ancient Philosophy')[:]
138
139    # join two tables by composite foreign key
140    select(c for c in Classroom for l in Lesson if l.classroom == c and l.course.subject.name == 'Physics')[:]
141
142    # Lessons  will be joined with Buildings directly without Classrooms
143    select(s for s in Subject for l in Lesson if s == l.course.subject and l.classroom.building.description == 'some description')[:]
144
145    # just another example of join of many tables
146    select(c for c in Course if c.major.department.faculty.number == 4)[:]
147