· 8 years ago · Feb 23, 2018, 06:04 PM
1namespace AozoraBunko
2{
3 public static class SqlStringStore
4 {
5 public const string Initialize = @"
6
7 DROP TABLE IF EXISTS raw_source;
8 DROP VIEW IF EXISTS book_summary;
9 DROP TABLE IF EXISTS person_role;
10 DROP TABLE IF EXISTS original_content;
11 DROP TABLE IF EXISTS bookshelf;
12 DROP TABLE IF EXISTS people;
13 DROP TABLE IF EXISTS role_types;
14 DROP TABLE IF EXISTS character_usage_types;
15 DROP TABLE IF EXISTS filtered_mode;
16 DROP TABLE IF EXISTS filtered_content;
17
18
19 CREATE TABLE raw_source(
20 id SERIAL PRIMARY KEY ,
21 book_id integer,
22 title text,
23 title_phonetic text,
24 sub_title text,
25 sub_title_phonetic text,
26 original_title text,
27 character_usage text,
28 book_is_protected text,
29 person_id integer,
30 sur_name text,
31 given_name text,
32 sur_name_phonetic text,
33 given_name_phonetic text,
34 role_flg text,
35 text_file_url text,
36 last_up_date date
37 );
38
39 CREATE INDEX idx_tmp_book_id ON raw_source USING HASH (book_id);
40 CREATE INDEX idx_tmp_person_id on raw_source USING HASH (person_id);
41
42 CREATE TABLE character_usage_types
43 (
44 id SERIAL NOT NULL
45 CONSTRAINT character_usage_types_pkey
46 PRIMARY KEY,
47 name VARCHAR NOT NULL
48 );
49
50 CREATE UNIQUE INDEX idx_character_usage_types_name ON character_usage_types(name);
51
52 CREATE TABLE role_types(
53 id SERIAL PRIMARY KEY ,
54 name VARCHAR NOT NULL UNIQUE
55 );
56
57 CREATE TABLE people
58 (
59 id INTEGER PRIMARY KEY ,
60 sur_name TEXT,
61 given_name TEXT,
62 sur_name_phonetic TEXT,
63 given_name_phonetic TEXT
64 );
65
66 CREATE TABLE bookshelf (
67 id INTEGER PRIMARY KEY ,
68 title text,
69 title_phonetic text,
70 subtitle text,
71 subtitle_phonetic text,
72 original_title text,
73 character_usage INTEGER REFERENCES character_usage_types(id),
74 text_file_url text NOT NULL ,
75 last_update date NOT null
76 );
77
78 CREATE TABLE person_role (
79 id SERIAL PRIMARY KEY ,
80 book_id INTEGER REFERENCES bookshelf(id),
81 person_id INTEGER REFERENCES people(id),
82 role INTEGER REFERENCES role_types(id)
83 );
84
85 CREATE INDEX idx_person_role_role ON person_role USING HASH(role);
86
87 CREATE TABLE original_content
88 (
89 id INTEGER NOT NULL
90 CONSTRAINT original_content_pkey PRIMARY KEY,
91 content TEXT
92 );
93
94 CREATE TABLE filtered_mode(
95 id INTEGER PRIMARY KEY ,
96 name text NOT NULL UNIQUE
97 );
98
99 INSERT INTO filtered_mode(id,name) VALUES (0,'NotFiltered'),(1,'Strict'),(2,'Loose');
100
101
102 CREATE TABLE filtered_content(
103 id INTEGER PRIMARY KEY ,
104 header_filtered_mode INTEGER,
105 footer_filtered_mode INTEGER,
106 content text
107 );
108
109";
110
111 public static string CopyRawSource(string sourcePath) =>
112 "COPY raw_source(book_id, title, title_phonetic, sub_title, " +
113 "sub_title_phonetic,original_title,character_usage,book_is_protected," +
114 " person_id, sur_name, given_name, sur_name_phonetic, given_name_phonetic, " +
115 $"role_flg, text_file_url, last_up_date) FROM \'{sourcePath.Replace("\\", "\\\\")}\' WITH CSV HEADER;";
116
117 public static string CopyBookshelf(string sourcePath) =>
118 $"COPY original_content(id,content) FROM \'{sourcePath.Replace("\\", "\\\\")}\' WITH CSV";
119
120
121
122 }
123}