20120101000010_create_ticket.rb 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256
  1. class CreateTicket < ActiveRecord::Migration
  2. def up
  3. create_table :ticket_state_types do |t|
  4. t.column :name, :string, :limit => 250, :null => false
  5. t.column :note, :string, :limit => 250, :null => true
  6. t.column :updated_by_id, :integer, :null => false
  7. t.column :created_by_id, :integer, :null => false
  8. t.timestamps
  9. end
  10. add_index :ticket_state_types, [:name], :unique => true
  11. create_table :ticket_states do |t|
  12. t.references :ticket_state_type, :null => false
  13. t.column :name, :string, :limit => 250, :null => false
  14. t.column :note, :string, :limit => 250, :null => true
  15. t.column :active, :boolean, :null => false, :default => true
  16. t.column :updated_by_id, :integer, :null => false
  17. t.column :created_by_id, :integer, :null => false
  18. t.timestamps
  19. end
  20. add_index :ticket_states, [:name], :unique => true
  21. create_table :ticket_priorities do |t|
  22. t.column :name, :string, :limit => 250, :null => false
  23. t.column :note, :string, :limit => 250, :null => true
  24. t.column :active, :boolean, :null => false, :default => true
  25. t.column :updated_by_id, :integer, :null => false
  26. t.column :created_by_id, :integer, :null => false
  27. t.timestamps
  28. end
  29. add_index :ticket_priorities, [:name], :unique => true
  30. create_table :tickets do |t|
  31. t.references :group, :null => false
  32. t.references :ticket_priority, :null => false
  33. t.references :ticket_state, :null => false
  34. t.references :organization, :null => true
  35. t.column :number, :string, :limit => 60, :null => false
  36. t.column :title, :string, :limit => 250,:null => false
  37. t.column :owner_id, :integer, :null => false
  38. t.column :customer_id, :integer, :null => false
  39. t.column :note, :string, :limit => 250,:null => true
  40. t.column :first_response, :timestamp, :null => true
  41. t.column :first_response_escal_date, :timestamp, :null => true
  42. t.column :first_response_sla_time, :timestamp, :null => true
  43. t.column :close_time, :timestamp, :null => true
  44. t.column :close_time_escal_date, :timestamp, :null => true
  45. t.column :close_time_sla_time, :timestamp, :null => true
  46. t.column :last_contact, :timestamp, :null => true
  47. t.column :last_contact_agent, :timestamp, :null => true
  48. t.column :last_contact_customer, :timestamp, :null => true
  49. t.column :create_article_type_id, :integer, :null => true
  50. t.column :create_article_sender_id, :integer, :null => true
  51. t.column :article_count, :integer, :null => true
  52. t.column :updated_by_id, :integer, :null => false
  53. t.column :created_by_id, :integer, :null => false
  54. t.timestamps
  55. end
  56. add_index :tickets, [:ticket_state_id]
  57. add_index :tickets, [:ticket_priority_id]
  58. add_index :tickets, [:group_id]
  59. add_index :tickets, [:owner_id]
  60. add_index :tickets, [:customer_id]
  61. add_index :tickets, [:number], :unique => true
  62. add_index :tickets, [:title]
  63. add_index :tickets, [:created_at]
  64. add_index :tickets, [:first_response]
  65. add_index :tickets, [:first_response_escal_date]
  66. add_index :tickets, [:close_time]
  67. add_index :tickets, [:close_time_escal_date]
  68. add_index :tickets, [:last_contact]
  69. add_index :tickets, [:last_contact_agent]
  70. add_index :tickets, [:last_contact_customer]
  71. add_index :tickets, [:create_article_type_id]
  72. add_index :tickets, [:create_article_sender_id]
  73. add_index :tickets, [:created_by_id]
  74. create_table :ticket_flags do |t|
  75. t.references :tickets, :null => false
  76. t.column :key, :string, :limit => 50, :null => false
  77. t.column :value, :string, :limit => 50, :null => true
  78. t.column :created_by_id, :integer, :null => false
  79. t.timestamps
  80. end
  81. add_index :ticket_flags, [:tickets_id, :created_by_id]
  82. add_index :ticket_flags, [:tickets_id, :key]
  83. add_index :ticket_flags, [:tickets_id]
  84. add_index :ticket_flags, [:created_by_id]
  85. create_table :ticket_time_accounting do |t|
  86. t.references :tickets, :null => false
  87. t.references :ticket_articles, :null => true
  88. t.column :time_unit, :decimal, :precision => 6, :scale => 2, :null => false
  89. t.column :created_by_id, :integer, :null => false
  90. t.timestamps
  91. end
  92. add_index :ticket_time_accounting, [:tickets_id]
  93. add_index :ticket_time_accounting, [:ticket_articles_id]
  94. add_index :ticket_time_accounting, [:created_by_id]
  95. create_table :ticket_article_types do |t|
  96. t.column :name, :string, :limit => 250, :null => false
  97. t.column :note, :string, :limit => 250, :null => true
  98. t.column :communication, :boolean, :null => false
  99. t.column :active, :boolean, :null => false, :default => true
  100. t.column :updated_by_id, :integer, :null => false
  101. t.column :created_by_id, :integer, :null => false
  102. t.timestamps
  103. end
  104. add_index :ticket_article_types, [:name], :unique => true
  105. create_table :ticket_article_senders do |t|
  106. t.column :name, :string, :limit => 250, :null => false
  107. t.column :note, :string, :limit => 250, :null => true
  108. t.column :updated_by_id, :integer, :null => false
  109. t.column :created_by_id, :integer, :null => false
  110. t.timestamps
  111. end
  112. add_index :ticket_article_senders, [:name], :unique => true
  113. create_table :ticket_articles do |t|
  114. t.references :ticket, :null => false
  115. t.references :ticket_article_type, :null => false
  116. t.references :ticket_article_sender, :null => false
  117. t.column :from, :string, :limit => 3000, :null => true
  118. t.column :to, :string, :limit => 3000, :null => true
  119. t.column :cc, :string, :limit => 3000, :null => true
  120. t.column :subject, :string, :limit => 3000, :null => true
  121. # t.column :reply_to, :string, :limit => 3000, :null => true
  122. t.column :message_id, :string, :limit => 3000, :null => true
  123. t.column :message_id_md5, :string, :limit => 32, :null => true
  124. t.column :in_reply_to, :string, :limit => 3000, :null => true
  125. t.column :references, :string, :limit => 3200, :null => true
  126. t.column :body, :text, :null => true
  127. t.column :internal, :boolean, :null => false, :default => false
  128. t.column :updated_by_id, :integer, :null => false
  129. t.column :created_by_id, :integer, :null => false
  130. t.timestamps
  131. end
  132. add_index :ticket_articles, [:ticket_id]
  133. add_index :ticket_articles, [:message_id_md5]
  134. add_index :ticket_articles, [:message_id_md5, :ticket_article_type_id], :name => 'index_ticket_articles_message_id_md5_type_id'
  135. add_index :ticket_articles, [:created_by_id]
  136. add_index :ticket_articles, [:internal]
  137. add_index :ticket_articles, [:ticket_article_type_id]
  138. add_index :ticket_articles, [:ticket_article_sender_id]
  139. create_table :ticket_article_flags do |t|
  140. t.references :ticket_articles, :null => false
  141. t.column :key, :string, :limit => 50, :null => false
  142. t.column :value, :string, :limit => 50, :null => true
  143. t.column :created_by_id, :integer, :null => false
  144. t.timestamps
  145. end
  146. add_index :ticket_article_flags, [:ticket_articles_id, :created_by_id], :name => 'index_ticket_article_flags_on_articles_id_and_created_by_id'
  147. add_index :ticket_article_flags, [:ticket_articles_id, :key]
  148. add_index :ticket_article_flags, [:ticket_articles_id]
  149. add_index :ticket_article_flags, [:created_by_id]
  150. create_table :ticket_counters do |t|
  151. t.column :content, :string, :limit => 100, :null => false
  152. t.column :generator, :string, :limit => 100, :null => false
  153. end
  154. add_index :ticket_counters, [:generator], :unique => true
  155. create_table :overviews do |t|
  156. t.references :user, :null => true
  157. t.references :role, :null => false
  158. t.column :name, :string, :limit => 250, :null => false
  159. t.column :link, :string, :limit => 250, :null => false
  160. t.column :prio, :integer, :null => false
  161. t.column :condition, :string, :limit => 2500, :null => false
  162. t.column :order, :string, :limit => 2500, :null => false
  163. t.column :group_by, :string, :limit => 250, :null => true
  164. t.column :organization_shared, :boolean, :null => false, :default => false
  165. t.column :view, :string, :limit => 1000, :null => false
  166. t.column :active, :boolean, :null => false, :default => true
  167. t.column :updated_by_id, :integer, :null => false
  168. t.column :created_by_id, :integer, :null => false
  169. t.timestamps
  170. end
  171. add_index :overviews, [:user_id]
  172. add_index :overviews, [:name]
  173. create_table :overviews_groups, :id => false do |t|
  174. t.integer :overview_id
  175. t.integer :group_id
  176. end
  177. add_index :overviews_groups, [:overview_id]
  178. add_index :overviews_groups, [:group_id]
  179. create_table :triggers do |t|
  180. t.column :name, :string, :limit => 250, :null => false
  181. t.column :key, :string, :limit => 250, :null => false
  182. t.column :value, :string, :limit => 250, :null => false
  183. end
  184. add_index :triggers, [:name]
  185. add_index :triggers, [:key]
  186. add_index :triggers, [:value]
  187. create_table :notifications do |t|
  188. t.column :subject, :string, :limit => 250, :null => false
  189. t.column :body, :string, :limit => 8000, :null => false
  190. t.column :content_type, :string, :limit => 250, :null => false
  191. t.column :active, :boolean, :null => false, :default => true
  192. t.column :note, :string, :limit => 250, :null => true
  193. t.timestamps
  194. end
  195. create_table :link_types do |t|
  196. t.column :name, :string, :limit => 250, :null => false
  197. t.column :note, :string, :limit => 250, :null => true
  198. t.column :active, :boolean, :null => false, :default => true
  199. t.timestamps
  200. end
  201. add_index :link_types, [:name], :unique => true
  202. create_table :link_objects do |t|
  203. t.column :name, :string, :limit => 250, :null => false
  204. t.column :note, :string, :limit => 250, :null => true
  205. t.column :active, :boolean, :null => false, :default => true
  206. t.timestamps
  207. end
  208. add_index :link_objects, [:name], :unique => true
  209. create_table :links do |t|
  210. t.references :link_type, :null => false
  211. t.column :link_object_source_id, :integer, :null => false
  212. t.column :link_object_source_value, :integer, :null => false
  213. t.column :link_object_target_id, :integer, :null => false
  214. t.column :link_object_target_value, :integer, :null => false
  215. t.timestamps
  216. end
  217. add_index :links, [:link_object_source_id, :link_object_source_value, :link_object_target_id, :link_object_target_value, :link_type_id], :unique => true, :name => 'links_uniq_total'
  218. end
  219. def self.down
  220. drop_table :notifications
  221. drop_table :triggers
  222. drop_table :links
  223. drop_table :link_types
  224. drop_table :link_objects
  225. drop_table :overviews
  226. drop_table :ticket_counters
  227. drop_table :ticket_time_accounting
  228. drop_table :ticket_article_flags
  229. drop_table :ticket_articles
  230. drop_table :ticket_article_types
  231. drop_table :ticket_article_senders
  232. drop_table :ticket_flags
  233. drop_table :tickets
  234. drop_table :ticket_priorities
  235. drop_table :ticket_states
  236. drop_table :ticket_state_types
  237. end
  238. end