20120101000001_create_base.rb 45 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936
  1. # Copyright (C) 2012-2024 Zammad Foundation, https://zammad-foundation.org/
  2. class CreateBase < ActiveRecord::Migration[4.2]
  3. def up
  4. # clear old caches to start from scratch
  5. Rails.cache.clear
  6. # This table is used by activerecord-session_store gem
  7. # Thus it's better to disable Rubocop rules rather than touch the code
  8. create_table :sessions do |t|
  9. t.string :session_id, null: false
  10. t.boolean :persistent, null: true # rubocop:disable Rails/ThreeStateBooleanColumn
  11. t.text :data
  12. t.timestamps limit: 3, null: false
  13. end
  14. add_index :sessions, :session_id
  15. add_index :sessions, :updated_at
  16. add_index :sessions, :persistent
  17. create_table :users do |t|
  18. t.references :organization, null: true
  19. t.string :login, limit: 255, null: false
  20. t.string :firstname, limit: 100, null: true, default: ''
  21. t.string :lastname, limit: 100, null: true, default: ''
  22. t.string :email, limit: 255, null: true, default: ''
  23. t.string :image, limit: 100, null: true
  24. t.string :image_source, limit: 200, null: true
  25. t.string :web, limit: 100, null: true, default: ''
  26. t.string :password, limit: 100, null: true
  27. t.string :phone, limit: 100, null: true, default: ''
  28. t.string :fax, limit: 100, null: true, default: ''
  29. t.string :mobile, limit: 100, null: true, default: ''
  30. t.string :department, limit: 200, null: true, default: ''
  31. t.string :street, limit: 120, null: true, default: ''
  32. t.string :zip, limit: 100, null: true, default: ''
  33. t.string :city, limit: 100, null: true, default: ''
  34. t.string :country, limit: 100, null: true, default: ''
  35. t.string :address, limit: 500, null: true, default: ''
  36. t.boolean :vip, null: false, default: false
  37. t.boolean :verified, null: false, default: false
  38. t.boolean :active, null: false, default: true
  39. t.string :note, limit: 5000, null: true, default: ''
  40. t.timestamp :last_login, limit: 3, null: true
  41. t.string :source, limit: 200, null: true
  42. t.integer :login_failed, null: false, default: 0
  43. t.boolean :out_of_office, null: false, default: false
  44. t.date :out_of_office_start_at, null: true
  45. t.date :out_of_office_end_at, null: true
  46. t.integer :out_of_office_replacement_id, null: true
  47. t.string :preferences, limit: 8000, null: true
  48. t.integer :updated_by_id, null: false
  49. t.integer :created_by_id, null: false
  50. t.timestamps limit: 3, null: false
  51. end
  52. add_index :users, [:login], unique: true
  53. add_index :users, [:email]
  54. # add_index :users, [:email], unique: => true
  55. add_index :users, [:organization_id]
  56. add_index :users, [:image]
  57. add_index :users, [:department]
  58. add_index :users, [:phone]
  59. add_index :users, [:fax]
  60. add_index :users, [:mobile]
  61. add_index :users, %i[out_of_office out_of_office_start_at out_of_office_end_at], name: 'index_out_of_office'
  62. add_index :users, [:out_of_office_replacement_id]
  63. add_index :users, [:source]
  64. add_index :users, [:created_by_id]
  65. add_foreign_key :users, :users, column: :created_by_id
  66. add_foreign_key :users, :users, column: :updated_by_id
  67. add_foreign_key :users, :users, column: :out_of_office_replacement_id
  68. create_table :user_overview_sortings do |t|
  69. t.column :user_id, :integer, null: false
  70. t.column :overview_id, :integer, null: false
  71. t.column :prio, :integer, null: false
  72. t.integer :updated_by_id, null: false
  73. t.integer :created_by_id, null: false
  74. t.timestamps limit: 3, null: false
  75. end
  76. add_index :user_overview_sortings, :user_id
  77. add_index :user_overview_sortings, :overview_id
  78. add_foreign_key :user_overview_sortings, :users, column: :created_by_id
  79. add_foreign_key :user_overview_sortings, :users, column: :updated_by_id
  80. add_foreign_key :user_overview_sortings, :users, column: :user_id
  81. create_table :signatures do |t|
  82. t.string :name, limit: 100, null: false
  83. t.text :body, limit: 10.megabytes + 1, null: true
  84. t.boolean :active, null: false, default: true
  85. t.string :note, limit: 250, null: true
  86. t.integer :updated_by_id, null: false
  87. t.integer :created_by_id, null: false
  88. t.timestamps limit: 3, null: false
  89. end
  90. add_index :signatures, [:name], unique: true
  91. add_foreign_key :signatures, :users, column: :created_by_id
  92. add_foreign_key :signatures, :users, column: :updated_by_id
  93. create_table :email_addresses do |t|
  94. t.integer :channel_id, null: true
  95. t.string :name, limit: 250, null: false
  96. t.string :email, limit: 255, null: false
  97. t.boolean :active, null: false, default: true
  98. t.string :note, limit: 250, null: true
  99. t.string :preferences, limit: 2000, null: true
  100. t.integer :updated_by_id, null: false
  101. t.integer :created_by_id, null: false
  102. t.timestamps limit: 3, null: false
  103. end
  104. add_index :email_addresses, [:email], unique: true
  105. add_foreign_key :email_addresses, :users, column: :created_by_id
  106. add_foreign_key :email_addresses, :users, column: :updated_by_id
  107. create_table :groups do |t|
  108. t.references :signature, null: true
  109. t.references :email_address, null: true
  110. if ActiveRecord::Base.connection_db_config.configuration_hash[:adapter] == 'mysql2'
  111. t.string :name, limit: (160 * 6) + (2 * 5), null: false # max depth of 6 and 5 delimiters in between
  112. else
  113. t.string :name, limit: (160 * 10) + (2 * 9), null: false # max depth of 10 and 9 delimiters in between
  114. end
  115. t.string :name_last, limit: 160, null: false
  116. t.integer :parent_id, null: true
  117. t.integer :assignment_timeout, null: true
  118. t.string :follow_up_possible, limit: 100, null: false, default: 'yes'
  119. t.integer :reopen_time_in_days, null: true
  120. t.boolean :follow_up_assignment, null: false, default: true
  121. t.boolean :active, null: false, default: true
  122. t.boolean :shared_drafts, null: false, default: true
  123. t.string :note, limit: 250, null: true
  124. t.integer :updated_by_id, null: false
  125. t.integer :created_by_id, null: false
  126. t.timestamps limit: 3, null: false
  127. end
  128. add_index :groups, [:name], unique: true
  129. add_foreign_key :groups, :signatures
  130. add_foreign_key :groups, :email_addresses
  131. add_foreign_key :groups, :users, column: :created_by_id
  132. add_foreign_key :groups, :users, column: :updated_by_id
  133. add_foreign_key :groups, :groups, column: :parent_id
  134. create_table :roles do |t|
  135. t.string :name, limit: 100, null: false
  136. t.text :preferences, limit: 500.kilobytes + 1, null: true
  137. t.boolean :default_at_signup, null: false, default: false
  138. t.boolean :active, null: false, default: true
  139. t.string :note, limit: 250, null: true
  140. t.integer :updated_by_id, null: false
  141. t.integer :created_by_id, null: false
  142. t.timestamps limit: 3, null: false
  143. end
  144. add_index :roles, [:name], unique: true
  145. add_foreign_key :roles, :users, column: :created_by_id
  146. add_foreign_key :roles, :users, column: :updated_by_id
  147. create_table :permissions do |t|
  148. t.string :name, limit: 255, null: false
  149. t.string :note, limit: 500, null: true
  150. t.string :preferences, limit: 10_000, null: true
  151. t.boolean :active, null: false, default: true
  152. t.boolean :allow_signup, null: false, default: false
  153. t.timestamps limit: 3, null: false
  154. end
  155. add_index :permissions, [:name], unique: true
  156. create_table :permissions_roles, id: false do |t|
  157. t.belongs_to :role, index: true
  158. t.belongs_to :permission, index: true
  159. end
  160. create_table :organizations do |t|
  161. t.string :name, limit: 150, null: false
  162. t.boolean :shared, null: false, default: true
  163. t.string :domain, limit: 250, null: true, default: ''
  164. t.boolean :domain_assignment, null: false, default: false
  165. t.boolean :active, null: false, default: true
  166. t.boolean :vip, null: false, default: false
  167. t.string :note, limit: 5000, null: true, default: ''
  168. t.integer :updated_by_id, null: false
  169. t.integer :created_by_id, null: false
  170. t.timestamps limit: 3, null: false
  171. end
  172. add_index :organizations, [:name], unique: true
  173. add_index :organizations, [:domain]
  174. add_foreign_key :users, :organizations
  175. add_foreign_key :organizations, :users, column: :created_by_id
  176. add_foreign_key :organizations, :users, column: :updated_by_id
  177. create_table :roles_users, id: false do |t|
  178. t.references :user
  179. t.references :role
  180. end
  181. add_index :roles_users, [:user_id]
  182. add_index :roles_users, [:role_id]
  183. add_foreign_key :roles_users, :users
  184. add_foreign_key :roles_users, :roles
  185. create_table :groups_users, id: false do |t|
  186. t.references :user, null: false
  187. t.references :group, null: false
  188. t.string :access, limit: 50, null: false, default: 'full'
  189. end
  190. add_index :groups_users, [:user_id]
  191. add_index :groups_users, [:group_id]
  192. add_index :groups_users, [:access]
  193. add_index :groups_users, %i[user_id group_id access]
  194. add_foreign_key :groups_users, :users
  195. add_foreign_key :groups_users, :groups
  196. create_table :roles_groups, id: false do |t|
  197. t.references :role, null: false
  198. t.references :group, null: false
  199. t.string :access, limit: 50, null: false, default: 'full'
  200. end
  201. add_index :roles_groups, [:role_id]
  202. add_index :roles_groups, [:group_id]
  203. add_index :roles_groups, [:access]
  204. add_foreign_key :roles_groups, :roles
  205. add_foreign_key :roles_groups, :groups
  206. create_table :organizations_users, id: false do |t|
  207. t.references :user
  208. t.references :organization
  209. end
  210. add_index :organizations_users, [:user_id]
  211. add_index :organizations_users, [:organization_id]
  212. add_foreign_key :organizations_users, :users
  213. add_foreign_key :organizations_users, :organizations
  214. create_table :authorizations do |t|
  215. t.string :provider, limit: 250, null: false
  216. t.string :uid, limit: 250, null: false
  217. t.string :token, limit: 2500, null: true
  218. t.string :secret, limit: 250, null: true
  219. t.string :username, limit: 250, null: true
  220. t.references :user, null: false
  221. t.timestamps limit: 3, null: false
  222. end
  223. add_index :authorizations, %i[uid provider], unique: true
  224. add_index :authorizations, [:user_id]
  225. add_index :authorizations, [:username]
  226. add_foreign_key :authorizations, :users
  227. create_table :locales do |t|
  228. t.string :locale, limit: 20, null: false
  229. t.string :alias, limit: 20, null: true
  230. t.string :name, limit: 255, null: false
  231. t.string :dir, limit: 9, null: false, default: 'ltr'
  232. t.boolean :active, null: false, default: true
  233. t.timestamps limit: 3, null: false
  234. end
  235. add_index :locales, [:locale], unique: true
  236. add_index :locales, [:name], unique: true
  237. create_table :translations do |t|
  238. t.string :locale, limit: 10, null: false
  239. t.string :source, limit: 3000, null: false
  240. t.string :target, limit: 3000, null: false
  241. t.string :target_initial, limit: 3000, null: false
  242. t.boolean :is_synchronized_from_codebase, null: false, default: false
  243. t.string :synchronized_from_translation_file, limit: 255
  244. t.integer :updated_by_id, null: false
  245. t.integer :created_by_id, null: false
  246. t.timestamps limit: 3, null: false
  247. end
  248. add_index :translations, [:source], length: 255
  249. add_index :translations, [:locale]
  250. add_foreign_key :translations, :users, column: :created_by_id
  251. add_foreign_key :translations, :users, column: :updated_by_id
  252. create_table :object_lookups do |t|
  253. t.string :name, limit: 250, null: false
  254. t.timestamps limit: 3, null: false
  255. end
  256. add_index :object_lookups, [:name], unique: true
  257. create_table :type_lookups do |t|
  258. t.string :name, limit: 250, null: false
  259. t.timestamps limit: 3, null: false
  260. end
  261. add_index :type_lookups, [:name], unique: true
  262. create_table :tokens do |t|
  263. t.references :user, null: false
  264. t.boolean :persistent, null: false, default: false
  265. t.string :name, limit: 255, null: true
  266. t.string :token, limit: 100, null: false
  267. t.string :action, limit: 40, null: false
  268. t.text :preferences, limit: 500.kilobytes + 1, null: true
  269. t.timestamp :last_used_at, limit: 3, null: true
  270. t.date :expires_at, null: true
  271. t.timestamps limit: 3, null: false
  272. end
  273. add_index :tokens, :user_id
  274. add_index :tokens, %i[token action], unique: true
  275. add_index :tokens, :created_at
  276. add_index :tokens, :persistent
  277. add_foreign_key :tokens, :users
  278. create_table :packages do |t|
  279. t.string :name, limit: 250, null: false
  280. t.string :version, limit: 50, null: false
  281. t.string :vendor, limit: 150, null: false
  282. t.string :state, limit: 50, null: false
  283. t.string :url, limit: 512, null: true
  284. t.integer :updated_by_id, null: false
  285. t.integer :created_by_id, null: false
  286. t.timestamps limit: 3, null: false
  287. end
  288. add_foreign_key :packages, :users, column: :created_by_id
  289. add_foreign_key :packages, :users, column: :updated_by_id
  290. create_table :package_migrations do |t|
  291. t.string :name, limit: 250, null: false
  292. t.string :version, limit: 250, null: false
  293. t.timestamps limit: 3, null: false
  294. end
  295. create_table :taskbars do |t|
  296. t.references :user, null: false
  297. t.datetime :last_contact, null: false, limit: 3
  298. t.string :key, limit: 100, null: false
  299. t.string :callback, limit: 100, null: false
  300. t.text :state, limit: 20.megabytes + 1, null: true
  301. t.text :preferences, limit: 5.megabytes + 1, null: true
  302. t.string :params, limit: 2000, null: true
  303. t.integer :prio, null: false
  304. t.boolean :notify, null: false, default: false
  305. t.boolean :active, null: false, default: false
  306. t.string :app, null: false, default: 'desktop'
  307. t.timestamps limit: 3, null: false
  308. end
  309. add_index :taskbars, [:user_id]
  310. add_index :taskbars, [:key]
  311. add_foreign_key :taskbars, :users
  312. create_table :tag_objects do |t|
  313. t.string :name, limit: 250, null: false
  314. t.timestamps limit: 3, null: false
  315. end
  316. add_index :tag_objects, [:name], unique: true
  317. create_table :tag_items do |t|
  318. t.string :name, limit: 250, null: false
  319. t.string :name_downcase, limit: 250, null: false
  320. t.timestamps limit: 3, null: false
  321. end
  322. add_index :tag_items, [:name_downcase]
  323. create_table :tags do |t|
  324. t.references :tag_item, null: false
  325. t.references :tag_object, null: false
  326. t.integer :o_id, null: false
  327. t.integer :created_by_id, null: false
  328. t.timestamps limit: 3, null: false
  329. end
  330. add_index :tags, [:o_id]
  331. add_index :tags, [:tag_object_id]
  332. add_foreign_key :tags, :tag_items
  333. add_foreign_key :tags, :tag_objects
  334. add_foreign_key :tags, :users, column: :created_by_id
  335. create_table :recent_views do |t|
  336. t.references :recent_view_object, null: false
  337. t.integer :o_id, null: false
  338. t.integer :created_by_id, null: false
  339. t.timestamps limit: 3, null: false
  340. end
  341. add_index :recent_views, [:o_id]
  342. add_index :recent_views, [:created_by_id]
  343. add_index :recent_views, [:created_at]
  344. add_index :recent_views, [:recent_view_object_id]
  345. add_foreign_key :recent_views, :object_lookups, column: :recent_view_object_id
  346. add_foreign_key :recent_views, :users, column: :created_by_id
  347. create_table :activity_streams do |t|
  348. t.references :activity_stream_type, null: false
  349. t.references :activity_stream_object, null: false
  350. t.references :permission, null: true
  351. t.references :group, null: true
  352. t.integer :o_id, null: false
  353. t.integer :created_by_id, null: false
  354. t.timestamps limit: 3, null: false
  355. end
  356. add_index :activity_streams, [:o_id]
  357. add_index :activity_streams, [:created_by_id]
  358. add_index :activity_streams, [:permission_id]
  359. add_index :activity_streams, %i[permission_id group_id]
  360. add_index :activity_streams, %i[permission_id group_id created_at], name: 'index_activity_streams_on_permission_id_group_id_created_at'
  361. add_index :activity_streams, [:group_id]
  362. add_index :activity_streams, [:created_at]
  363. add_index :activity_streams, [:activity_stream_object_id]
  364. add_index :activity_streams, [:activity_stream_type_id]
  365. add_foreign_key :activity_streams, :type_lookups, column: :activity_stream_type_id
  366. add_foreign_key :activity_streams, :object_lookups, column: :activity_stream_object_id
  367. add_foreign_key :activity_streams, :permissions
  368. add_foreign_key :activity_streams, :groups
  369. add_foreign_key :activity_streams, :users, column: :created_by_id
  370. create_table :history_types do |t|
  371. t.string :name, limit: 250, null: false
  372. t.timestamps limit: 3, null: false
  373. end
  374. add_index :history_types, [:name], unique: true
  375. create_table :history_objects do |t|
  376. t.string :name, limit: 250, null: false
  377. t.string :note, limit: 250, null: true
  378. t.timestamps limit: 3, null: false
  379. end
  380. add_index :history_objects, [:name], unique: true
  381. create_table :history_attributes do |t|
  382. t.string :name, limit: 250, null: false
  383. t.timestamps limit: 3, null: false
  384. end
  385. add_index :history_attributes, [:name], unique: true
  386. create_table :histories do |t|
  387. t.references :history_type, null: false
  388. t.references :history_object, null: false
  389. t.references :history_attribute, null: true
  390. t.references :sourceable, polymorphic: true, null: true
  391. t.string :sourceable_name, limit: 500, null: true
  392. t.integer :o_id, null: false
  393. t.integer :related_o_id, null: true
  394. t.integer :related_history_object_id, null: true
  395. t.integer :id_to, null: true
  396. t.integer :id_from, null: true
  397. t.string :value_from, limit: 500, null: true
  398. t.string :value_to, limit: 500, null: true
  399. t.integer :created_by_id, null: false
  400. t.timestamps limit: 3, null: false
  401. end
  402. add_index :histories, [:o_id]
  403. add_index :histories, [:created_by_id]
  404. add_index :histories, [:created_at]
  405. add_index :histories, [:history_object_id]
  406. add_index :histories, [:history_attribute_id]
  407. add_index :histories, [:history_type_id]
  408. add_index :histories, [:id_to]
  409. add_index :histories, [:id_from]
  410. add_index :histories, [:value_from], length: 255
  411. add_index :histories, [:value_to], length: 255
  412. add_index :histories, [:related_o_id]
  413. add_index :histories, [:related_history_object_id]
  414. add_index :histories, %i[o_id history_object_id related_o_id]
  415. add_foreign_key :histories, :history_types
  416. add_foreign_key :histories, :history_objects
  417. add_foreign_key :histories, :history_attributes
  418. add_foreign_key :histories, :users, column: :created_by_id
  419. create_table :settings do |t|
  420. t.string :title, limit: 200, null: false
  421. t.string :name, limit: 200, null: false
  422. t.string :area, limit: 100, null: false
  423. t.string :description, limit: 2000, null: false
  424. t.text :options, null: true
  425. t.text :state_current, limit: 200.kilobytes + 1, null: true
  426. t.string :state_initial, limit: 2000, null: true
  427. t.boolean :frontend, null: false, default: false
  428. t.text :preferences, limit: 200.kilobytes + 1, null: true
  429. t.timestamps limit: 3, null: false
  430. end
  431. add_index :settings, [:name], unique: true
  432. add_index :settings, [:area]
  433. add_index :settings, [:frontend]
  434. create_table :store_objects do |t|
  435. t.string :name, limit: 250, null: false
  436. t.string :note, limit: 250, null: true
  437. t.timestamps limit: 3, null: false
  438. end
  439. add_index :store_objects, [:name], unique: true
  440. create_table :store_files do |t|
  441. t.string :sha, limit: 128, null: false
  442. t.string :provider, limit: 20, null: true
  443. t.timestamps limit: 3, null: false
  444. end
  445. add_index :store_files, [:sha], unique: true
  446. add_index :store_files, [:provider]
  447. create_table :stores do |t|
  448. t.references :store_object, null: false
  449. t.references :store_file, null: false
  450. t.string :o_id, limit: 255, null: false
  451. t.string :preferences, limit: 2500, null: true
  452. t.string :size, limit: 50, null: true
  453. t.string :filename, limit: 250, null: false
  454. t.integer :created_by_id, null: false
  455. t.timestamps limit: 3, null: false
  456. end
  457. add_index :stores, %i[store_object_id o_id]
  458. add_index :stores, %i[store_file_id]
  459. add_foreign_key :stores, :store_objects
  460. add_foreign_key :stores, :store_files
  461. add_foreign_key :stores, :users, column: :created_by_id
  462. create_table :store_provider_dbs do |t|
  463. t.string :sha, limit: 128, null: false
  464. t.binary :data, limit: 200.megabytes, null: true
  465. t.timestamps limit: 3, null: false
  466. end
  467. add_index :store_provider_dbs, [:sha], unique: true
  468. create_table :avatars do |t|
  469. t.integer :o_id, null: false
  470. t.integer :object_lookup_id, null: false
  471. t.boolean :default, null: false, default: false
  472. t.boolean :deletable, null: false, default: true
  473. t.boolean :initial, null: false, default: false
  474. t.integer :store_full_id, null: true
  475. t.integer :store_resize_id, null: true
  476. t.string :store_hash, limit: 32, null: true
  477. t.string :source, limit: 100, null: false
  478. t.string :source_url, limit: 512, null: true
  479. t.integer :updated_by_id, null: false
  480. t.integer :created_by_id, null: false
  481. t.timestamps limit: 3, null: false
  482. end
  483. add_index :avatars, %i[o_id object_lookup_id]
  484. add_index :avatars, [:store_hash]
  485. add_index :avatars, [:source]
  486. add_index :avatars, [:default]
  487. add_foreign_key :avatars, :users, column: :created_by_id
  488. add_foreign_key :avatars, :users, column: :updated_by_id
  489. create_table :online_notifications do |t|
  490. t.integer :o_id, null: false
  491. t.integer :object_lookup_id, null: false
  492. t.integer :type_lookup_id, null: false
  493. t.integer :user_id, null: false
  494. t.boolean :seen, null: false, default: false
  495. t.integer :updated_by_id, null: false
  496. t.integer :created_by_id, null: false
  497. t.timestamps limit: 3, null: false
  498. end
  499. add_index :online_notifications, [:user_id]
  500. add_index :online_notifications, [:seen]
  501. add_index :online_notifications, [:created_at]
  502. add_index :online_notifications, [:updated_at]
  503. add_foreign_key :online_notifications, :users
  504. add_foreign_key :online_notifications, :users, column: :created_by_id
  505. add_foreign_key :online_notifications, :users, column: :updated_by_id
  506. create_table :schedulers do |t|
  507. t.string :name, limit: 250, null: false
  508. t.string :method, limit: 250, null: false
  509. t.integer :period, null: true
  510. t.integer :running, null: false, default: false
  511. t.timestamp :last_run, limit: 3, null: true
  512. t.integer :prio, null: false
  513. t.string :pid, limit: 250, null: true
  514. t.string :note, limit: 250, null: true
  515. t.string :error_message, null: true
  516. t.string :status, null: true
  517. t.boolean :active, null: false, default: false
  518. t.string :timeplan, limit: 2500, null: true
  519. t.integer :updated_by_id, null: false
  520. t.integer :created_by_id, null: false
  521. t.timestamps limit: 3, null: false
  522. end
  523. add_index :schedulers, [:name], unique: true
  524. add_foreign_key :schedulers, :users, column: :created_by_id
  525. add_foreign_key :schedulers, :users, column: :updated_by_id
  526. create_table :calendars do |t|
  527. t.string :name, limit: 250, null: true
  528. t.string :timezone, limit: 250, null: true
  529. t.string :business_hours, limit: 3000, null: true
  530. t.boolean :default, null: false, default: false
  531. t.string :ical_url, limit: 500, null: true
  532. t.text :public_holidays, limit: 500.kilobytes + 1, null: true
  533. t.text :last_log, limit: 500.kilobytes + 1, null: true
  534. t.timestamp :last_sync, limit: 3, null: true
  535. t.integer :updated_by_id, null: false
  536. t.integer :created_by_id, null: false
  537. t.timestamps limit: 3, null: false
  538. end
  539. add_index :calendars, [:name], unique: true
  540. add_foreign_key :calendars, :users, column: :created_by_id
  541. add_foreign_key :calendars, :users, column: :updated_by_id
  542. create_table :user_devices do |t|
  543. t.references :user, null: false
  544. t.string :name, limit: 250, null: false
  545. t.string :os, limit: 150, null: true
  546. t.string :browser, limit: 250, null: true
  547. t.string :location, limit: 150, null: true
  548. t.string :device_details, limit: 2500, null: true
  549. t.string :location_details, limit: 2500, null: true
  550. t.string :fingerprint, limit: 160, null: true
  551. t.string :user_agent, limit: 250, null: true
  552. t.string :ip, limit: 160, null: true
  553. t.timestamps limit: 3, null: false
  554. end
  555. add_index :user_devices, [:user_id]
  556. add_index :user_devices, %i[os browser location]
  557. add_index :user_devices, [:fingerprint]
  558. add_index :user_devices, [:updated_at]
  559. add_index :user_devices, [:created_at]
  560. add_foreign_key :user_devices, :users
  561. create_table :external_credentials do |t|
  562. t.string :name
  563. t.string :credentials, limit: 2500, null: false
  564. t.timestamps limit: 3, null: false
  565. end
  566. create_table :object_manager_attributes do |t|
  567. t.references :object_lookup, null: false
  568. t.string :name, limit: 200, null: false
  569. t.string :display, limit: 200, null: false
  570. t.string :data_type, limit: 100, null: false
  571. t.text :data_option, limit: 800.kilobytes + 1, null: true
  572. t.text :data_option_new, limit: 800.kilobytes + 1, null: true
  573. t.boolean :editable, null: false, default: true
  574. t.boolean :active, null: false, default: true
  575. t.string :screens, limit: 2000, null: true
  576. t.boolean :to_create, null: false, default: false
  577. t.boolean :to_migrate, null: false, default: false
  578. t.boolean :to_delete, null: false, default: false
  579. t.boolean :to_config, null: false, default: false
  580. t.integer :position, null: false
  581. t.integer :created_by_id, null: false
  582. t.integer :updated_by_id, null: false
  583. t.timestamps limit: 3, null: false
  584. end
  585. add_index :object_manager_attributes, %i[object_lookup_id name], unique: true
  586. add_index :object_manager_attributes, [:object_lookup_id]
  587. add_foreign_key :object_manager_attributes, :object_lookups
  588. add_foreign_key :object_manager_attributes, :users, column: :created_by_id
  589. add_foreign_key :object_manager_attributes, :users, column: :updated_by_id
  590. create_table :delayed_jobs, force: true do |t|
  591. t.integer :priority, default: 0 # Allows some jobs to jump to the front of the queue
  592. t.integer :attempts, default: 0 # Provides for retries, but still fail eventually.
  593. t.text :handler # YAML-encoded string of the object that will do work
  594. t.text :last_error # reason for last failure (See Note below)
  595. t.datetime :run_at, limit: 3 # When to run. Could be Time.zone.now for immediately, or sometime in the future.
  596. t.datetime :locked_at, limit: 3 # Set when a client is working on this object
  597. t.datetime :failed_at, limit: 3 # Set when all retries have failed (actually, by default, the record is deleted instead)
  598. t.string :locked_by # Who is working on this object (if locked)
  599. t.string :queue # The name of the queue this job is in
  600. t.timestamps limit: 3, null: false
  601. end
  602. add_index :delayed_jobs, %i[priority run_at], name: 'delayed_jobs_priority'
  603. create_table :external_syncs do |t|
  604. t.string :source, limit: 100, null: false
  605. t.string :source_id, limit: 200, null: false
  606. t.string :object, limit: 100, null: false
  607. t.integer :o_id, null: false
  608. t.text :last_payload, limit: 500.kilobytes + 1, null: true
  609. t.timestamps limit: 3, null: false
  610. end
  611. add_index :external_syncs, %i[source source_id], unique: true
  612. add_index :external_syncs, %i[source source_id object o_id], name: 'index_external_syncs_on_source_and_source_id_and_object_o_id'
  613. add_index :external_syncs, %i[object o_id]
  614. create_table :import_jobs do |t|
  615. t.string :name, limit: 250, null: false
  616. t.boolean :dry_run, null: false, default: false
  617. t.text :payload, limit: 80_000
  618. t.text :result, limit: 80_000
  619. t.datetime :started_at, limit: 3
  620. t.datetime :finished_at, limit: 3
  621. t.timestamps limit: 3, null: false
  622. end
  623. create_table :cti_logs do |t|
  624. t.string :direction, limit: 20, null: false
  625. t.string :state, limit: 20, null: false
  626. t.string :from, limit: 100, null: false
  627. t.string :from_comment, limit: 250, null: true
  628. t.string :to, limit: 100, null: false
  629. t.string :to_comment, limit: 250, null: true
  630. t.string :queue, limit: 250, null: true
  631. t.string :call_id, limit: 250, null: false
  632. t.string :comment, limit: 500, null: true
  633. t.timestamp :initialized_at, limit: 3, null: true
  634. t.timestamp :start_at, limit: 3, null: true
  635. t.timestamp :end_at, limit: 3, null: true
  636. t.integer :duration_waiting_time, null: true
  637. t.integer :duration_talking_time, null: true
  638. t.boolean :done, null: false, default: true
  639. t.text :preferences, limit: 500.kilobytes + 1, null: true
  640. t.timestamps limit: 3, null: false
  641. end
  642. add_index :cti_logs, [:call_id], unique: true
  643. add_index :cti_logs, [:direction]
  644. add_index :cti_logs, [:from]
  645. create_table :cti_caller_ids do |t|
  646. t.string :caller_id, limit: 100, null: false
  647. t.string :comment, limit: 500, null: true
  648. t.string :level, limit: 100, null: false
  649. t.string :object, limit: 100, null: false
  650. t.integer :o_id, null: false
  651. t.references :user, null: true
  652. t.text :preferences, limit: 500.kilobytes + 1, null: true
  653. t.timestamps limit: 3, null: false
  654. end
  655. add_index :cti_caller_ids, [:caller_id]
  656. add_index :cti_caller_ids, %i[caller_id level]
  657. add_index :cti_caller_ids, %i[caller_id user_id]
  658. add_index :cti_caller_ids, %i[object o_id]
  659. add_index :cti_caller_ids, %i[object o_id level user_id caller_id], name: 'index_cti_caller_ids_on_object_o_id_level_user_id_caller_id'
  660. add_foreign_key :cti_caller_ids, :users
  661. create_table :stats_stores do |t|
  662. t.references :stats_storable, polymorphic: true, index: true
  663. t.string :key, limit: 250, null: true
  664. t.string :data, limit: 5000, null: true
  665. t.integer :created_by_id, null: false
  666. t.timestamps limit: 3, null: false
  667. end
  668. add_index :stats_stores, [:key]
  669. add_index :stats_stores, [:created_by_id]
  670. add_index :stats_stores, [:created_at]
  671. add_foreign_key :stats_stores, :users, column: :created_by_id
  672. create_table :http_logs do |t|
  673. t.column :direction, :string, limit: 20, null: false
  674. t.column :facility, :string, limit: 100, null: false
  675. t.column :method, :string, limit: 100, null: false
  676. t.column :url, :text, null: false
  677. t.column :status, :string, limit: 20, null: true
  678. t.column :ip, :string, limit: 50, null: true
  679. t.column :request, :text, null: false
  680. t.column :response, :text, null: false
  681. t.column :updated_by_id, :integer, null: true
  682. t.column :created_by_id, :integer, null: true
  683. t.timestamps limit: 3, null: false
  684. end
  685. add_index :http_logs, [:facility]
  686. add_index :http_logs, [:created_by_id]
  687. add_index :http_logs, [:created_at]
  688. add_foreign_key :http_logs, :users, column: :created_by_id
  689. add_foreign_key :http_logs, :users, column: :updated_by_id
  690. create_table :active_job_locks do |t|
  691. t.string :lock_key
  692. t.string :active_job_id
  693. t.timestamps limit: 3
  694. end
  695. add_index :active_job_locks, :lock_key, unique: true
  696. add_index :active_job_locks, :active_job_id, unique: true
  697. create_table :smime_certificates do |t|
  698. t.string :fingerprint, limit: 250, null: false
  699. t.string :uid, limit: 1024, null: false
  700. if Rails.application.config.db_column_array
  701. t.string :email_addresses, null: true, array: true
  702. else
  703. t.json :email_addresses, null: true
  704. end
  705. t.binary :pem, limit: 10.megabytes, null: false
  706. t.binary :private_key, limit: 10.megabytes, null: true
  707. t.string :private_key_secret, limit: 500, null: true
  708. t.string :issuer_hash, limit: 128, null: true
  709. t.string :subject_hash, limit: 128, null: true
  710. t.timestamps limit: 3, null: false
  711. end
  712. add_index :smime_certificates, [:fingerprint], unique: true
  713. add_index :smime_certificates, [:uid]
  714. create_table :data_privacy_tasks do |t|
  715. t.column :state, :string, limit: 150, default: 'in process', null: true
  716. t.references :deletable, polymorphic: true
  717. t.text :preferences
  718. t.column :updated_by_id, :integer, null: false
  719. t.column :created_by_id, :integer, null: false
  720. t.timestamps limit: 3, null: false
  721. end
  722. add_index :data_privacy_tasks, [:state]
  723. create_table :mentions do |t|
  724. t.references :mentionable, polymorphic: true, null: false
  725. t.column :user_id, :integer, null: false
  726. t.column :updated_by_id, :integer, null: false
  727. t.column :created_by_id, :integer, null: false
  728. t.timestamps limit: 3, null: false
  729. end
  730. add_index :mentions, %i[mentionable_id mentionable_type user_id], unique: true, name: 'index_mentions_mentionable_user'
  731. add_foreign_key :mentions, :users, column: :created_by_id
  732. add_foreign_key :mentions, :users, column: :updated_by_id
  733. add_foreign_key :mentions, :users, column: :user_id
  734. create_table :jobs do |t|
  735. t.column :name, :string, limit: 250, null: false
  736. t.column :timeplan, :string, limit: 2500, null: false
  737. t.column :object, :string, limit: 100, null: false
  738. t.column :condition, :text, limit: 500.kilobytes + 1, null: false
  739. t.column :perform, :text, limit: 500.kilobytes + 1, null: false
  740. t.column :disable_notification, :boolean, null: false, default: true
  741. t.column :last_run_at, :timestamp, limit: 3, null: true
  742. t.column :next_run_at, :timestamp, limit: 3, null: true
  743. t.column :running, :boolean, null: false, default: false
  744. t.column :processed, :integer, null: false, default: 0
  745. t.column :matching, :integer, null: false
  746. t.column :pid, :string, limit: 250, null: true
  747. t.column :localization, :string, limit: 20, null: true # thx to ApplicationModel::CanCreatesAndUpdates ...
  748. t.column :timezone, :string, limit: 250, null: true
  749. t.column :note, :string, limit: 250, null: true
  750. t.column :active, :boolean, null: false, default: false
  751. t.column :updated_by_id, :integer, null: false
  752. t.column :created_by_id, :integer, null: false
  753. t.timestamps limit: 3, null: false
  754. end
  755. add_index :jobs, [:name], unique: true
  756. add_foreign_key :jobs, :users, column: :created_by_id
  757. add_foreign_key :jobs, :users, column: :updated_by_id
  758. create_table :core_workflows do |t|
  759. t.string :name, limit: 100, null: false
  760. t.string :object, limit: 100, null: true
  761. t.text :preferences, limit: 500.kilobytes + 1, null: true
  762. t.text :condition_saved, limit: 500.kilobytes + 1, null: true
  763. t.text :condition_selected, limit: 500.kilobytes + 1, null: true
  764. t.text :perform, limit: 500.kilobytes + 1, null: true
  765. t.boolean :active, null: false, default: true
  766. t.boolean :stop_after_match, null: false, default: false
  767. t.boolean :changeable, null: false, default: true
  768. t.integer :priority, null: false, default: 0
  769. t.integer :updated_by_id, null: false
  770. t.integer :created_by_id, null: false
  771. t.timestamps limit: 3, null: false
  772. end
  773. add_index :core_workflows, [:name], unique: true
  774. add_foreign_key :core_workflows, :users, column: :created_by_id
  775. add_foreign_key :core_workflows, :users, column: :updated_by_id
  776. create_table :ldap_sources do |t|
  777. t.string :name, limit: 100, null: false
  778. t.text :preferences, limit: 5.megabytes + 1, null: true
  779. t.integer :prio, null: false
  780. t.boolean :active, null: false, default: true
  781. t.integer :updated_by_id, null: false
  782. t.integer :created_by_id, null: false
  783. t.timestamps limit: 3, null: false
  784. end
  785. add_index :ldap_sources, [:name], unique: true
  786. add_foreign_key :ldap_sources, :users, column: :created_by_id
  787. add_foreign_key :ldap_sources, :users, column: :updated_by_id
  788. create_table :public_links do |t|
  789. t.string :link, limit: 500, null: false
  790. t.string :title, limit: 200, null: false
  791. t.string :description, limit: 200, null: true
  792. if Rails.application.config.db_column_array
  793. t.string :screen, null: false, array: true
  794. else
  795. t.json :screen, null: false
  796. end
  797. t.boolean :new_tab, null: false, default: true
  798. t.integer :prio, null: false
  799. t.column :updated_by_id, :integer, null: false
  800. t.column :created_by_id, :integer, null: false
  801. t.timestamps limit: 3, null: false
  802. end
  803. add_index :public_links, [:link], unique: true
  804. add_foreign_key :public_links, :users, column: :created_by_id
  805. add_foreign_key :public_links, :users, column: :updated_by_id
  806. create_table :user_two_factor_preferences do |t|
  807. t.string :method, limit: 100, null: false
  808. t.text :configuration, limit: 500.kilobytes + 1, null: true
  809. t.integer :user_id, null: false
  810. t.integer :updated_by_id, null: false
  811. t.integer :created_by_id, null: false
  812. t.timestamps limit: 3, null: false
  813. end
  814. add_index :user_two_factor_preferences, %i[method user_id], unique: true
  815. add_foreign_key :user_two_factor_preferences, :users, column: :user_id
  816. add_foreign_key :user_two_factor_preferences, :users, column: :created_by_id
  817. add_foreign_key :user_two_factor_preferences, :users, column: :updated_by_id
  818. create_table :pgp_keys do |t|
  819. t.string :name, limit: 3000, null: false
  820. t.string :fingerprint, limit: 40, null: false
  821. t.text :key, limit: 500.kilobytes + 1, null: false
  822. t.datetime :expires_at, null: true, limit: 3
  823. if Rails.application.config.db_column_array
  824. t.string :email_addresses, null: true, array: true
  825. else
  826. t.json :email_addresses, null: true
  827. end
  828. t.boolean :secret, null: false, default: false
  829. t.string :passphrase, limit: 500, null: true
  830. t.string :domain_alias, limit: 255, null: true, default: ''
  831. t.integer :updated_by_id, null: false
  832. t.integer :created_by_id, null: false
  833. t.timestamps limit: 3, null: false
  834. end
  835. add_index :pgp_keys, [:fingerprint], unique: true
  836. add_index :pgp_keys, [:domain_alias]
  837. add_foreign_key :pgp_keys, :users, column: :created_by_id
  838. add_foreign_key :pgp_keys, :users, column: :updated_by_id
  839. create_table :ssl_certificates do |t|
  840. t.string :fingerprint, limit: 250, null: false
  841. t.binary :certificate, limit: 10.megabytes, null: false
  842. t.string :subject, limit: 250, null: false
  843. t.datetime :not_before, limit: 3, null: false
  844. t.datetime :not_after, limit: 3, null: false
  845. t.boolean :ca, default: false, null: false
  846. t.timestamps limit: 3, null: false
  847. end
  848. create_table :failed_emails do |t|
  849. t.binary :data, null: false
  850. t.integer :retries, null: false, default: 1
  851. t.text :parsing_error
  852. t.timestamps limit: 3, null: false
  853. end
  854. end
  855. end