1 # Copyright 2007 Sam Hart
3 # This program is free software; you can redistribute it and/or modify
4 # it under the terms of the GNU General Public License as published by
5 # the Free Software Foundation; either version 2 of the License, or
6 # (at your option) any later version.
8 # This program is distributed in the hope that it will be useful,
9 # but WITHOUT ANY WARRANTY; without even the implied warranty of
10 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 # GNU General Public License for more details.
13 # You should have received a copy of the GNU General Public License
14 # along with this program; if not, write to the Free Software
15 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20 Swarm DB Schema Package
22 This package defines the database schema used in a Swarm Hive.
25 from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, \
26 Float, PickleType, Text, Binary, Boolean
28 #from sqlalchemy.orm import mapper, relation, backref
29 #import sqlalchemy.types as types
31 from swarmlib.db.db_bits import metadata
32 import swarmlib.db.data_objects as dobj
34 ###################################
35 # Define the issue and node tables
36 ###################################
38 __HASH_ID_LENGTH__ = 40
39 __USER_ID_LENGTH__ = 60
40 __SUMMARY_LENGTH__ = 255
42 issues_table = Table('issues', metadata,
43 Column('hash_id', String(__HASH_ID_LENGTH__),
44 primary_key=True, unique=True, nullable=False),
45 Column('issue_id', String(__HASH_ID_LENGTH__),
46 unique=True, nullable=False),
48 Column('block_id', String(__HASH_ID_LENGTH__),
49 ForeignKey('issues.hash_id')),
51 # Status will no longer be a user definable list
52 # This is to better refine searches.
53 Column('status', Integer, nullable=False),
55 # XXX: The following all need to have default values. The best way
56 # will probably be to set up a mapping in sqlalchemy orm, but I don't
57 # yet know how best to do this, so I'm marking it as a FIXME
58 Column('component', Integer, nullable=False),
59 Column('version', Integer, nullable=False),
60 Column('milestone', Integer, nullable=False),
61 Column('severity', Integer, nullable=False),
62 Column('priority', Integer, nullable=False),
63 Column('resolution', Integer, nullable=False),
65 Column('owner', String(__USER_ID_LENGTH__)),
66 Column('reporter', String(__USER_ID_LENGTH__), nullable=False),
68 # FIXME XXX: More mappings will be needed here
69 Column('cc_id', Integer),
70 Column('subscribers_id', Integer),
72 Column('time', Float),
75 nodes_table = Table('nodes', metadata,
76 Column('hash_id', String(__HASH_ID_LENGTH__),
77 primary_key=True, unique=True, nullable=False),
78 Column('issue_id', String(__HASH_ID_LENGTH__),
79 ForeignKey('issues.hash_id')),
80 Column('parent_node_id', String(__HASH_ID_LENGTH__),
81 ForeignKey('nodes.hash_id')),
83 Column('time', Float),
85 Column('poster', String(__USER_ID_LENGTH__)),
87 Column('summary', String(__SUMMARY_LENGTH__)),
88 Column('details', Text),
90 Column('attachment', Binary),
93 mapper(dobj.Issue, issues_table, properties={
94 'root_nodes':relation(Node), #, backref='issue')
95 'depends':relation(Issue, backref=backref('blocks',
96 remote_side=[issues_table.c.hash_id])),
98 mapper(dobj.Node, nodes_table, properties={
99 'children':relation(Node, backref=backref('parent',
100 remote_side=[nodes_table.c.hash_id])),
103 ###################################
104 # Define the Transaction log table
105 ###################################
107 transaction_log_table = Table('transaction_log', metadata,
108 Column('id', Integer, primary_key=True, auto_increment=True),
109 Column('root', String(__HASH_ID_LENGTH__)),
110 Column('time', Float),
111 Column('transaction', Integer),
112 Column('transaction_data', PickleType),
115 mapper(dobj.TransactionEntry, transaction_log_table)
117 ############################################
118 # Define the user-definable Taxonomy tables
119 ############################################
120 __TAX_NAME_LENGTH__ = 25
122 component_table = Table('components', metadata
123 Column('id', Integer, primary_key=True, unique=True, auto_increment=True),
124 Column('name', String(__TAX_NAME_LENGTH__)),
125 Column('isdefault', Boolean),
128 mapper(dobj.ComponentEntry, component_table)
130 version_table = Table('versions', metadata
131 column('id',Integer, primary_key=True, unique=True,
132 auto_increment=True),
133 column('name', String(__TAX_NAME_LENGTH__)),
134 column('isdefault', Boolean),
137 mapper(dobj.VersionEntry, version_table)
139 milestone_table = Table('milestones', metadata
140 column('id',Integer, primary_key=True, unique=True,
141 auto_increment=True),
142 column('name', String(__TAX_NAME_LENGTH__)),
143 column('isdefault', Boolean),
146 mapper(dobj.MilestoneEntry, milestone_table)
148 severity_table = Table('severities', metadata
149 column('id',Integer, primary_key=True, unique=True,
150 auto_increment=True),
151 column('name', String(__TAX_NAME_LENGTH__)),
152 column('isdefault', Boolean),
155 mapper(dobj.SeverityEntry, severity_table)
157 priority_table = Table('priorities', metadata
158 column('id',Integer, primary_key=True, unique=True,
159 auto_increment=True),
160 column('name', String(__TAX_NAME_LENGTH__)),
161 column('isdefault', Boolean),
164 mapper(dobj.PriorityEntry, priority_table)
166 status_table = Table('status', metadata
167 column('id',Integer, primary_key=True, unique=True,
168 auto_increment=True),
169 column('name', String(__TAX_NAME_LENGTH__)),
170 column('isdefault', Boolean),
173 mapper(dobj.StatusEntry, status_table)
175 resolution_table = Table('resolutions', metadata
176 column('id',Integer, primary_key=True, unique=True,
177 auto_increment=True),
178 column('name', String(__TAX_NAME_LENGTH__)),
179 column('isdefault', Boolean),
182 mapper(dobj.ResolutionEntry, resolution_table)
184 ##############################
185 # Upstream tracker definition
186 ##############################
188 upstream_table = Table('upstream', metadata
189 column('id', Integer, primary_key=True, unique=True,
190 auto_increment=True),
191 column('uri', String(__URI_LENGTH__),
192 column('type', Integer),
193 column('authentication', PickleType), # blob containing auth info
194 column('transport', PickleType), # blob containing transport info
197 mapper(dobj.Upstream, upstream_table)