/server/zanata-war/src/main/resources/db/h2/h2_baseline.sql

https://github.com/hferentschik/zanata · SQL · 485 lines · 414 code · 66 blank · 5 comment · 0 complexity · 039c64ac9e34f8461c95026eb3946761 MD5 · raw file

  1. /*
  2. * Warning: Please do not change this file!
  3. * Any changes to the database schema should be added as
  4. * Liquibase changeSets in db.changelog.xml
  5. */
  6. create table HAccount (
  7. id bigint generated by default as identity,
  8. creationDate timestamp not null,
  9. lastChanged timestamp not null,
  10. versionNum integer not null,
  11. apiKey varchar(32),
  12. enabled bit not null,
  13. passwordHash varchar(255),
  14. username varchar(255),
  15. primary key (id),
  16. unique (username)
  17. );
  18. create table HAccountActivationKey (
  19. keyHash varchar(32) not null,
  20. accountId bigint not null,
  21. primary key (keyHash),
  22. unique (accountId)
  23. );
  24. create table HAccountMembership (
  25. accountId bigint not null,
  26. memberOf integer not null,
  27. primary key (accountId, memberOf)
  28. );
  29. create table HAccountResetPasswordKey (
  30. keyHash varchar(32) not null,
  31. accountId bigint not null,
  32. primary key (keyHash),
  33. unique (accountId)
  34. );
  35. create table HAccountRole (
  36. id integer generated by default as identity,
  37. conditional bit not null,
  38. name varchar(255),
  39. primary key (id)
  40. );
  41. create table HAccountRoleGroup (
  42. roleId integer not null,
  43. memberOf integer not null,
  44. primary key (roleId, memberOf)
  45. );
  46. create table HApplicationConfiguration (
  47. id bigint generated by default as identity,
  48. creationDate timestamp not null,
  49. lastChanged timestamp not null,
  50. versionNum integer not null,
  51. config_key varchar(255) not null,
  52. config_value clob not null,
  53. primary key (id),
  54. unique (config_key)
  55. );
  56. create table HCommunity (
  57. id bigint generated by default as identity,
  58. creationDate timestamp not null,
  59. lastChanged timestamp not null,
  60. versionNum integer not null,
  61. slug varchar(40) not null,
  62. description varchar(100),
  63. homeContent clob,
  64. name varchar(255) not null,
  65. ownerId bigint not null,
  66. primary key (id),
  67. unique (slug)
  68. );
  69. create table HCommunity_Member (
  70. personId bigint not null,
  71. communityId bigint not null,
  72. primary key (communityId, personId)
  73. );
  74. create table HCommunity_Officer (
  75. personId bigint not null,
  76. communityId bigint not null,
  77. primary key (communityId, personId)
  78. );
  79. create table HDocument (
  80. id bigint generated by default as identity,
  81. creationDate timestamp not null,
  82. lastChanged timestamp not null,
  83. versionNum integer not null,
  84. contentType varchar(255) not null,
  85. docId varchar(255) not null,
  86. name varchar(255) not null,
  87. obsolete bit not null,
  88. path varchar(255) not null,
  89. revision integer not null,
  90. last_modified_by_id bigint,
  91. locale bigint not null,
  92. poHeader_id bigint,
  93. project_iteration_id bigint not null,
  94. primary key (id),
  95. unique (docId, project_iteration_id)
  96. );
  97. create table HDocumentHistory (
  98. id bigint generated by default as identity,
  99. contentType varchar(255) not null,
  100. docId varchar(255) not null,
  101. lastChanged timestamp,
  102. name varchar(255),
  103. obsolete bit not null,
  104. path varchar(255),
  105. revision integer,
  106. document_id bigint,
  107. last_modified_by_id bigint,
  108. locale bigint not null,
  109. primary key (id),
  110. unique (document_id, revision)
  111. );
  112. create table HLocale (
  113. id bigint generated by default as identity,
  114. creationDate timestamp not null,
  115. lastChanged timestamp not null,
  116. versionNum integer not null,
  117. active bit not null,
  118. localeId varchar(255) not null,
  119. primary key (id),
  120. unique (localeId)
  121. );
  122. create table HLocale_Member (
  123. personId bigint not null,
  124. supportedLanguageId bigint not null,
  125. primary key (supportedLanguageId, personId)
  126. );
  127. create table HPerson (
  128. id bigint generated by default as identity,
  129. creationDate timestamp not null,
  130. lastChanged timestamp not null,
  131. versionNum integer not null,
  132. email varchar(255) not null,
  133. name varchar(80) not null,
  134. accountId bigint,
  135. primary key (id),
  136. unique (email)
  137. );
  138. create table HPoHeader (
  139. id bigint generated by default as identity,
  140. creationDate timestamp not null,
  141. lastChanged timestamp not null,
  142. versionNum integer not null,
  143. entries clob,
  144. comment_id bigint,
  145. primary key (id)
  146. );
  147. create table HPoTargetHeader (
  148. id bigint generated by default as identity,
  149. creationDate timestamp not null,
  150. lastChanged timestamp not null,
  151. versionNum integer not null,
  152. entries clob,
  153. comment_id bigint,
  154. document_id bigint,
  155. targetLanguage bigint not null,
  156. primary key (id),
  157. unique (document_id, targetLanguage)
  158. );
  159. create table HPotEntryData (
  160. id bigint generated by default as identity,
  161. context varchar(255),
  162. flags varchar(255),
  163. refs clob,
  164. comment_id bigint,
  165. tf_id bigint unique,
  166. primary key (id),
  167. unique (tf_id)
  168. );
  169. create table HProject (
  170. projecttype varchar(31) not null,
  171. id bigint generated by default as identity,
  172. creationDate timestamp not null,
  173. lastChanged timestamp not null,
  174. versionNum integer not null,
  175. slug varchar(40) not null,
  176. description varchar(100),
  177. homeContent clob,
  178. name varchar(80) not null,
  179. primary key (id),
  180. unique (slug)
  181. );
  182. create table HProjectIteration (
  183. id bigint generated by default as identity,
  184. creationDate timestamp not null,
  185. lastChanged timestamp not null,
  186. versionNum integer not null,
  187. slug varchar(40) not null,
  188. active bit not null,
  189. description varchar(255),
  190. name varchar(20),
  191. parentId bigint,
  192. project_id bigint not null,
  193. primary key (id),
  194. unique (slug, project_id)
  195. );
  196. create table HProject_Maintainer (
  197. personId bigint not null,
  198. projectId bigint not null,
  199. primary key (projectId, personId)
  200. );
  201. create table HSimpleComment (
  202. id bigint generated by default as identity,
  203. comment clob not null,
  204. primary key (id)
  205. );
  206. create table HTextFlow (
  207. id bigint generated by default as identity,
  208. content clob not null,
  209. obsolete bit not null,
  210. pos integer not null,
  211. resId varchar(255) not null,
  212. revision integer not null,
  213. comment_id bigint,
  214. document_id bigint not null,
  215. potEntryData_id bigint,
  216. primary key (id),
  217. unique (document_id, resId)
  218. );
  219. create table HTextFlowHistory (
  220. id bigint generated by default as identity,
  221. content clob,
  222. obsolete bit not null,
  223. pos integer,
  224. revision integer,
  225. tf_id bigint,
  226. primary key (id),
  227. unique (revision, tf_id)
  228. );
  229. create table HTextFlowTarget (
  230. id bigint generated by default as identity,
  231. creationDate timestamp not null,
  232. lastChanged timestamp not null,
  233. versionNum integer not null,
  234. content clob not null,
  235. state integer not null,
  236. tf_revision integer not null,
  237. comment_id bigint,
  238. last_modified_by_id bigint,
  239. locale bigint not null,
  240. tf_id bigint,
  241. primary key (id),
  242. unique (locale, tf_id)
  243. );
  244. create table HTextFlowTargetHistory (
  245. id bigint generated by default as identity,
  246. content clob,
  247. lastChanged timestamp,
  248. state integer,
  249. tf_revision integer,
  250. versionNum integer,
  251. last_modified_by_id bigint,
  252. target_id bigint,
  253. primary key (id),
  254. unique (target_id, versionNum)
  255. );
  256. alter table HAccountActivationKey
  257. add constraint FK86E79CA4FA68C45F
  258. foreign key (accountId)
  259. references HAccount;
  260. alter table HAccountMembership
  261. add constraint FK9D5DB27B3E684F5E
  262. foreign key (memberOf)
  263. references HAccountRole;
  264. alter table HAccountMembership
  265. add constraint FK9D5DB27BFA68C45F
  266. foreign key (accountId)
  267. references HAccount;
  268. alter table HAccountResetPasswordKey
  269. add constraint FK85C9EFDAFA68C45F
  270. foreign key (accountId)
  271. references HAccount;
  272. alter table HAccountRoleGroup
  273. add constraint FK3321CC643E684F5E
  274. foreign key (memberOf)
  275. references HAccountRole;
  276. alter table HAccountRoleGroup
  277. add constraint FK3321CC642DF53D7E
  278. foreign key (roleId)
  279. references HAccountRole;
  280. alter table HCommunity
  281. add constraint FKD3DF208177D52F9
  282. foreign key (ownerId)
  283. references HPerson;
  284. alter table HCommunity_Member
  285. add constraint FK8BEBF03860C55B1B
  286. foreign key (personId)
  287. references HPerson;
  288. alter table HCommunity_Member
  289. add constraint FK8BEBF038AF83AE57
  290. foreign key (communityId)
  291. references HCommunity;
  292. alter table HCommunity_Officer
  293. add constraint FK5CB3E75860C55B1B
  294. foreign key (personId)
  295. references HPerson;
  296. alter table HCommunity_Officer
  297. add constraint FK5CB3E758AF83AE57
  298. foreign key (communityId)
  299. references HCommunity;
  300. alter table HDocument
  301. add constraint FKEA766D836C9BADC1
  302. foreign key (last_modified_by_id)
  303. references HPerson;
  304. alter table HDocument
  305. add constraint FKEA766D83FEA3B54A
  306. foreign key (locale)
  307. references HLocale;
  308. alter table HDocument
  309. add constraint FKEA766D8351ED6DFD
  310. foreign key (project_iteration_id)
  311. references HProjectIteration;
  312. alter table HDocument
  313. add constraint FKEA766D83136CC025
  314. foreign key (poHeader_id)
  315. references HPoHeader;
  316. alter table HDocumentHistory
  317. add constraint FK279765915383E2F0
  318. foreign key (document_id)
  319. references HDocument;
  320. alter table HDocumentHistory
  321. add constraint FK279765916C9BADC1
  322. foreign key (last_modified_by_id)
  323. references HPerson;
  324. alter table HDocumentHistory
  325. add constraint FK27976591FEA3B54A
  326. foreign key (locale)
  327. references HLocale;
  328. alter table HLocale_Member
  329. add constraint FK82DF50D760C55B1B
  330. foreign key (personId)
  331. references HPerson;
  332. alter table HLocale_Member
  333. add constraint FK82DF50D73A932491
  334. foreign key (supportedLanguageId)
  335. references HLocale;
  336. alter table HPerson
  337. add constraint FK6F0931BDFA68C45F
  338. foreign key (accountId)
  339. references HAccount;
  340. alter table HPoHeader
  341. add constraint FK9A0ABDD4B7A40DF2
  342. foreign key (comment_id)
  343. references HSimpleComment;
  344. alter table HPoTargetHeader
  345. add constraint FK1BC719855383E2F0
  346. foreign key (document_id)
  347. references HDocument;
  348. alter table HPoTargetHeader
  349. add constraint FK1BC719857D208AD9
  350. foreign key (targetLanguage)
  351. references HLocale;
  352. alter table HPoTargetHeader
  353. add constraint FK1BC71985B7A40DF2
  354. foreign key (comment_id)
  355. references HSimpleComment;
  356. alter table HPotEntryData
  357. add constraint FK17A648CFB7A40DF2
  358. foreign key (comment_id)
  359. references HSimpleComment;
  360. alter table HPotEntryData
  361. add constraint FK17A648CFCCAD9D19
  362. foreign key (tf_id)
  363. references HTextFlow;
  364. alter table HProjectIteration
  365. add constraint FK31C1E42C4BCEEA93
  366. foreign key (project_id)
  367. references HProject;
  368. alter table HProjectIteration
  369. add constraint FK31C1E42C5B1D181F
  370. foreign key (parentId)
  371. references HProjectIteration;
  372. alter table HProject_Maintainer
  373. add constraint FK1491F2E660C55B1B
  374. foreign key (personId)
  375. references HPerson;
  376. alter table HProject_Maintainer
  377. add constraint FK1491F2E665B5BB37
  378. foreign key (projectId)
  379. references HProject;
  380. alter table HTextFlow
  381. add constraint FK7B40F8635383E2F0
  382. foreign key (document_id)
  383. references HDocument;
  384. alter table HTextFlow
  385. add constraint FK7B40F8638D8E70A5
  386. foreign key (potEntryData_id)
  387. references HPotEntryData;
  388. alter table HTextFlow
  389. add constraint FK7B40F863B7A40DF2
  390. foreign key (comment_id)
  391. references HSimpleComment;
  392. alter table HTextFlowHistory
  393. add constraint FK46C4DEB1CCAD9D19
  394. foreign key (tf_id)
  395. references HTextFlow;
  396. alter table HTextFlowTarget
  397. add constraint FK1E933FD46C9BADC1
  398. foreign key (last_modified_by_id)
  399. references HPerson;
  400. alter table HTextFlowTarget
  401. add constraint FK1E933FD4FEA3B54A
  402. foreign key (locale)
  403. references HLocale;
  404. alter table HTextFlowTarget
  405. add constraint FK1E933FD4B7A40DF2
  406. foreign key (comment_id)
  407. references HSimpleComment;
  408. alter table HTextFlowTarget
  409. add constraint FK1E933FD4CCAD9D19
  410. foreign key (tf_id)
  411. references HTextFlow;
  412. alter table HTextFlowTargetHistory
  413. add constraint FKF10986206C9BADC1
  414. foreign key (last_modified_by_id)
  415. references HPerson;
  416. alter table HTextFlowTargetHistory
  417. add constraint FKF109862080727E8B
  418. foreign key (target_id)
  419. references HTextFlowTarget;