Source code of manned.org https://manned.org/
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

schema.sql 12KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277
  1. CREATE TABLE systems (
  2. id integer PRIMARY KEY, -- hardcoded ID.
  3. name varchar NOT NULL,
  4. release varchar,
  5. relorder integer NOT NULL DEFAULT 0, -- simple way of ordering different releases for the same system
  6. short varchar NOT NULL
  7. );
  8. CREATE TABLE contents (
  9. hash bytea PRIMARY KEY,
  10. content varchar NOT NULL
  11. );
  12. CREATE TABLE packages (
  13. id SERIAL PRIMARY KEY,
  14. system integer NOT NULL REFERENCES systems(id) ON DELETE CASCADE,
  15. category varchar,
  16. name varchar NOT NULL,
  17. UNIQUE(system, name, category) -- Note the order, lookups on (system,name) are common
  18. );
  19. CREATE TABLE package_versions (
  20. id SERIAL PRIMARY KEY,
  21. package integer NOT NULL REFERENCES packages(id) ON DELETE CASCADE,
  22. version varchar NOT NULL,
  23. released date NOT NULL,
  24. arch varchar,
  25. UNIQUE(package, version)
  26. );
  27. CREATE TABLE man (
  28. package integer NOT NULL REFERENCES package_versions(id) ON DELETE CASCADE,
  29. name varchar NOT NULL,
  30. filename varchar NOT NULL,
  31. locale varchar,
  32. hash bytea NOT NULL REFERENCES contents(hash),
  33. section varchar NOT NULL,
  34. encoding varchar,
  35. UNIQUE(package, filename)
  36. );
  37. CREATE INDEX ON man (hash);
  38. CREATE INDEX ON man (name);
  39. CREATE TABLE man_index AS SELECT DISTINCT name, section FROM man;
  40. CREATE INDEX ON man_index USING btree(lower(name) text_pattern_ops);
  41. CREATE TABLE stats_cache AS SELECT count(distinct hash) AS hashes, count(distinct name) AS mans, count(*) AS files, count(distinct package) AS packages FROM man;
  42. INSERT INTO systems (id, name, release, short, relorder) VALUES
  43. (1, 'Arch Linux', NULL, 'arch', 0),
  44. (2, 'Ubuntu', '4.10', 'ubuntu-warty', 0),
  45. (3, 'Ubuntu', '5.04', 'ubuntu-hoary', 1),
  46. (4, 'Ubuntu', '5.10', 'ubuntu-breezy', 2),
  47. (5, 'Ubuntu', '6.06', 'ubuntu-dapper', 3),
  48. (6, 'Ubuntu', '6.10', 'ubuntu-edgy', 4),
  49. (7, 'Ubuntu', '7.04', 'ubuntu-feisty', 5),
  50. (8, 'Ubuntu', '7.10', 'ubuntu-gutsy', 6),
  51. (9, 'Ubuntu', '8.04', 'ubuntu-hardy', 7),
  52. (10, 'Ubuntu', '8.10', 'ubuntu-intrepid', 8),
  53. (11, 'Ubuntu', '9.04', 'ubuntu-jaunty', 9),
  54. (12, 'Ubuntu', '9.10', 'ubuntu-karmic', 10),
  55. (13, 'Ubuntu', '10.04', 'ubuntu-lucid', 11),
  56. (14, 'Ubuntu', '10.10', 'ubuntu-maverick', 12),
  57. (15, 'Ubuntu', '11.04', 'ubuntu-natty', 13),
  58. (16, 'Ubuntu', '11.10', 'ubuntu-oneiric', 14),
  59. (17, 'Ubuntu', '12.04', 'ubuntu-precise', 15),
  60. (18, 'Debian', '1.1', 'debian-buzz', 0),
  61. (19, 'Debian', '1.2', 'debian-rex', 1),
  62. (20, 'Debian', '1.3', 'debian-bo', 2),
  63. (21, 'Debian', '2.0', 'debian-hamm', 3),
  64. (22, 'Debian', '2.1', 'debian-slink', 4),
  65. (23, 'Debian', '2.2', 'debian-potato', 5),
  66. (24, 'Debian', '3.0', 'debian-woody', 6),
  67. (25, 'Debian', '3.1', 'debian-sarge', 7),
  68. (26, 'Debian', '4.0', 'debian-etch', 8),
  69. (27, 'Debian', '5.0', 'debian-lenny', 9),
  70. (28, 'Debian', '6.0', 'debian-squeeze', 10),
  71. (29, 'FreeBSD', '1.0', 'freebsd-1.0', 0),
  72. (30, 'FreeBSD', '2.0.5', 'freebsd-2.0.5', 1),
  73. (31, 'FreeBSD', '2.1.5', 'freebsd-2.1.5', 2),
  74. (32, 'FreeBSD', '2.1.7', 'freebsd-2.1.7', 3),
  75. (33, 'FreeBSD', '2.2.2', 'freebsd-2.2.2', 4),
  76. (34, 'FreeBSD', '2.2.5', 'freebsd-2.2.5', 5),
  77. (35, 'FreeBSD', '2.2.6', 'freebsd-2.2.6', 6),
  78. (36, 'FreeBSD', '2.2.7', 'freebsd-2.2.7', 7),
  79. (37, 'FreeBSD', '2.2.8', 'freebsd-2.2.8', 8),
  80. (38, 'FreeBSD', '3.0', 'freebsd-3.0', 9),
  81. (39, 'FreeBSD', '3.1', 'freebsd-3.1', 10),
  82. (40, 'FreeBSD', '3.2', 'freebsd-3.2', 11),
  83. (41, 'FreeBSD', '3.3', 'freebsd-3.3', 12),
  84. (42, 'FreeBSD', '3.4', 'freebsd-3.4', 13),
  85. (43, 'FreeBSD', '3.5', 'freebsd-3.5', 14),
  86. (44, 'FreeBSD', '3.5.1', 'freebsd-3.5.1', 15),
  87. (45, 'FreeBSD', '4.0', 'freebsd-4.0', 16),
  88. (46, 'FreeBSD', '4.1', 'freebsd-4.1', 17),
  89. (47, 'FreeBSD', '4.1.1', 'freebsd-4.1.1', 18),
  90. (48, 'FreeBSD', '4.2', 'freebsd-4.2', 19),
  91. (49, 'FreeBSD', '4.3', 'freebsd-4.3', 20),
  92. (50, 'FreeBSD', '4.4', 'freebsd-4.4', 21),
  93. (51, 'FreeBSD', '4.5', 'freebsd-4.5', 22),
  94. (52, 'FreeBSD', '4.6', 'freebsd-4.6', 23),
  95. (53, 'FreeBSD', '4.6.2', 'freebsd-4.6.2', 24),
  96. (54, 'FreeBSD', '4.7', 'freebsd-4.7', 25),
  97. (55, 'FreeBSD', '4.8', 'freebsd-4.8', 26),
  98. (56, 'FreeBSD', '4.9', 'freebsd-4.9', 27),
  99. (57, 'FreeBSD', '4.10', 'freebsd-4.10', 28),
  100. (58, 'FreeBSD', '4.11', 'freebsd-4.11', 29),
  101. (59, 'FreeBSD', '5.0', 'freebsd-5.0', 30),
  102. (60, 'FreeBSD', '5.1', 'freebsd-5.1', 31),
  103. (61, 'FreeBSD', '5.2', 'freebsd-5.2', 32),
  104. (62, 'FreeBSD', '5.2.1', 'freebsd-5.2.1', 33),
  105. (63, 'FreeBSD', '5.3', 'freebsd-5.3', 34),
  106. (64, 'FreeBSD', '5.4', 'freebsd-5.4', 35),
  107. (65, 'FreeBSD', '5.5', 'freebsd-5.5', 36),
  108. (66, 'FreeBSD', '6.0', 'freebsd-6.0', 37),
  109. (67, 'FreeBSD', '6.1', 'freebsd-6.1', 38),
  110. (68, 'FreeBSD', '6.2', 'freebsd-6.2', 39),
  111. (69, 'FreeBSD', '6.3', 'freebsd-6.3', 40),
  112. (70, 'FreeBSD', '6.4', 'freebsd-6.4', 41),
  113. (71, 'FreeBSD', '7.0', 'freebsd-7.0', 42),
  114. (72, 'FreeBSD', '7.1', 'freebsd-7.1', 43),
  115. (73, 'FreeBSD', '7.2', 'freebsd-7.2', 44),
  116. (74, 'FreeBSD', '7.3', 'freebsd-7.3', 45),
  117. (75, 'FreeBSD', '7.4', 'freebsd-7.4', 46),
  118. (76, 'FreeBSD', '8.0', 'freebsd-8.0', 47),
  119. (77, 'FreeBSD', '8.1', 'freebsd-8.1', 48),
  120. (78, 'FreeBSD', '8.2', 'freebsd-8.2', 49),
  121. (79, 'FreeBSD', '8.3', 'freebsd-8.3', 50),
  122. (80, 'FreeBSD', '9.0', 'freebsd-9.0', 52),
  123. (81, 'Ubuntu', '12.10', 'ubuntu-quantal', 16),
  124. (82, 'Ubuntu', '13.04', 'ubuntu-raring', 17),
  125. (83, 'Debian', '7.0', 'debian-wheezy', 11),
  126. (84, 'FreeBSD', '8.4', 'freebsd-8.4', 51),
  127. (85, 'FreeBSD', '9.1', 'freebsd-9.1', 53),
  128. (86, 'FreeBSD', '9.2', 'freebsd-9.2', 54),
  129. (87, 'Ubuntu', '13.10', 'ubuntu-saucy', 18),
  130. (88, 'Ubuntu', '14.04', 'ubuntu-trusty', 19),
  131. (89, 'Ubuntu', '14.10', 'ubuntu-utopic', 20),
  132. (90, 'Ubuntu', '15.04', 'ubuntu-vivid', 21),
  133. (91, 'Debian', '8.0', 'debian-jessie', 12),
  134. (92, 'Ubuntu', '15.10', 'ubuntu-wily', 22),
  135. (93, 'Ubuntu', '16.04', 'ubuntu-xenial', 23),
  136. (94, 'FreeBSD', '9.3', 'freebsd-9.3', 55),
  137. (95, 'FreeBSD', '10.0', 'freebsd-10.0', 56),
  138. (96, 'FreeBSD', '10.1', 'freebsd-10.1', 57),
  139. (97, 'FreeBSD', '10.2', 'freebsd-10.2', 58),
  140. (98, 'FreeBSD', '10.3', 'freebsd-10.3', 59),
  141. (99, 'FreeBSD', '11.0', 'freebsd-11.0', 61),
  142. (100,'Ubuntu', '16.10', 'ubuntu-yakkety', 24),
  143. (101,'Fedora', '1', 'fedora-1', 0),
  144. (102,'Fedora', '2', 'fedora-2', 1),
  145. (103,'Fedora', '3', 'fedora-3', 2),
  146. (104,'Fedora', '4', 'fedora-4', 3),
  147. (105,'Fedora', '5', 'fedora-5', 4),
  148. (106,'Fedora', '6', 'fedora-6', 5),
  149. (107,'Fedora', '7', 'fedora-7', 6),
  150. (108,'Fedora', '8', 'fedora-8', 7),
  151. (109,'Fedora', '9', 'fedora-9', 8),
  152. (110,'Fedora', '10', 'fedora-10', 9),
  153. (111,'Fedora', '11', 'fedora-11', 10),
  154. (112,'Fedora', '12', 'fedora-12', 11),
  155. (113,'Fedora', '13', 'fedora-13', 12),
  156. (114,'Fedora', '14', 'fedora-14', 13),
  157. (115,'Fedora', '15', 'fedora-15', 14),
  158. (116,'Fedora', '16', 'fedora-16', 15),
  159. (117,'Fedora', '17', 'fedora-17', 16),
  160. (118,'Fedora', '18', 'fedora-18', 17),
  161. (119,'Fedora', '19', 'fedora-19', 18),
  162. (120,'Fedora', '20', 'fedora-20', 19),
  163. (121,'Fedora', '21', 'fedora-21', 20),
  164. (122,'Fedora', '22', 'fedora-22', 21),
  165. (123,'Fedora', '23', 'fedora-23', 22),
  166. (124,'Fedora', '24', 'fedora-24', 23),
  167. (125,'Fedora', '25', 'fedora-25', 24),
  168. (126,'Ubuntu', '17.04', 'ubuntu-zesty', 25),
  169. (127,'Debian', '9.0', 'debian-stretch', 13),
  170. (128,'Fedora', '26', 'fedora-26', 25),
  171. (129,'FreeBSD', '11.1', 'freebsd-11.1', 62),
  172. (130,'Ubuntu', '17.10', 'ubuntu-artful', 26),
  173. (131,'Fedora', '27', 'fedora-27', 26),
  174. (132,'FreeBSD', '10.4', 'freebsd-10.4', 60),
  175. (133,'CentOS', '2.1', 'centos-2.1', 1),
  176. (134,'CentOS', '3.1', 'centos-3.1', 2),
  177. (136,'CentOS', '3.3', 'centos-3.3', 4),
  178. (137,'CentOS', '3.4', 'centos-3.4', 5),
  179. (138,'CentOS', '3.5', 'centos-3.5', 6),
  180. (139,'CentOS', '3.6', 'centos-3.6', 7),
  181. (140,'CentOS', '3.7', 'centos-3.7', 8),
  182. (141,'CentOS', '3.8', 'centos-3.8', 9),
  183. (142,'CentOS', '3.9', 'centos-3.9', 10),
  184. (143,'CentOS', '4.0', 'centos-4.0', 11),
  185. (144,'CentOS', '4.1', 'centos-4.1', 12),
  186. (145,'CentOS', '4.2', 'centos-4.2', 13),
  187. (146,'CentOS', '4.3', 'centos-4.3', 14),
  188. (147,'CentOS', '4.4', 'centos-4.4', 15),
  189. (148,'CentOS', '4.5', 'centos-4.5', 16),
  190. (149,'CentOS', '4.6', 'centos-4.6', 17),
  191. (150,'CentOS', '4.7', 'centos-4.7', 18),
  192. (151,'CentOS', '4.8', 'centos-4.8', 19),
  193. (152,'CentOS', '4.9', 'centos-4.9', 20),
  194. (153,'CentOS', '5.0', 'centos-5.0', 21),
  195. (154,'CentOS', '5.1', 'centos-5.1', 22),
  196. (155,'CentOS', '5.2', 'centos-5.2', 23),
  197. (156,'CentOS', '5.3', 'centos-5.3', 24),
  198. (157,'CentOS', '5.4', 'centos-5.4', 25),
  199. (158,'CentOS', '5.5', 'centos-5.5', 26),
  200. (159,'CentOS', '5.6', 'centos-5.6', 27),
  201. (160,'CentOS', '5.7', 'centos-5.7', 28),
  202. (161,'CentOS', '5.8', 'centos-5.8', 29),
  203. (162,'CentOS', '5.9', 'centos-5.9', 30),
  204. (163,'CentOS', '5.10', 'centos-5.10', 31),
  205. (164,'CentOS', '5.11', 'centos-5.11', 32),
  206. (165,'CentOS', '6.0', 'centos-6.0', 33),
  207. (166,'CentOS', '6.1', 'centos-6.1', 34),
  208. (167,'CentOS', '6.2', 'centos-6.2', 35),
  209. (168,'CentOS', '6.3', 'centos-6.3', 36),
  210. (169,'CentOS', '6.4', 'centos-6.4', 37),
  211. (170,'CentOS', '6.5', 'centos-6.5', 38),
  212. (171,'CentOS', '6.6', 'centos-6.6', 39),
  213. (172,'CentOS', '6.7', 'centos-6.7', 40),
  214. (173,'CentOS', '6.8', 'centos-6.8', 41),
  215. (174,'CentOS', '6.9', 'centos-6.9', 42),
  216. (175,'CentOS', '7.0', 'centos-7.0', 44),
  217. (176,'CentOS', '7.1', 'centos-7.1', 45),
  218. (177,'CentOS', '7.2', 'centos-7.2', 46),
  219. (178,'CentOS', '7.3', 'centos-7.3', 47),
  220. (179,'CentOS', '7.4', 'centos-7.4', 48),
  221. (180,'Ubuntu', '18.04', 'ubuntu-bionic', 27),
  222. (181,'Fedora', '28', 'fedora-28', 27),
  223. (182,'CentOS', '7.5', 'centos-7.5', 49),
  224. (183,'CentOS', '6.10', 'centos-6.10', 43),
  225. (184,'FreeBSD', '11.2', 'freebsd-11.2', 63),
  226. (185,'Ubuntu', '18.10', 'ubuntu-cosmic', 28),
  227. (186,'Fedora', '29', 'fedora-29', 28),
  228. (187,'CentOS', '7.6', 'centos-7.6', 50);
  229. -- Removes any path components and compression extensions from the filename.
  230. CREATE OR REPLACE FUNCTION basename_from_filename(fn text) RETURNS text AS $$
  231. DECLARE
  232. ret text;
  233. tmp text;
  234. BEGIN
  235. ret := regexp_replace(fn, '^.+/([^/]+)', E'\\1');
  236. LOOP
  237. tmp := regexp_replace(regexp_replace(regexp_replace(ret, E'\\.gz$', ''), E'\\.lzma$', ''), E'\\.bz2$', '');
  238. EXIT WHEN tmp = ret;
  239. ret := tmp;
  240. END LOOP;
  241. RETURN ret;
  242. END;
  243. $$ LANGUAGE plpgsql;
  244. CREATE OR REPLACE FUNCTION section_from_filename(text) RETURNS text AS $$
  245. SELECT regexp_replace(basename_from_filename($1), E'^.+\\.([^.]+)$', E'\\1');
  246. $$ LANGUAGE SQL;
  247. CREATE OR REPLACE FUNCTION name_from_filename(text) RETURNS text AS $$
  248. SELECT regexp_replace(basename_from_filename($1), E'^(.+)\\.[^.]+$', E'\\1');
  249. $$ LANGUAGE SQL;
  250. CREATE OR REPLACE FUNCTION is_english_locale(locale text) RETURNS bool AS $$
  251. SELECT locale IS NULL OR locale LIKE 'en%';
  252. $$ IMMUTABLE LANGUAGE SQL;
  253. CREATE OR REPLACE FUNCTION is_standard_man_location(path text) RETURNS bool AS $$
  254. SELECT path LIKE '/usr/share/man/man%' OR path LIKE '/usr/local/man/man%';
  255. $$ IMMUTABLE LANGUAGE sql;