-
Notifications
You must be signed in to change notification settings - Fork 0
/
Program.cs
325 lines (273 loc) · 10.4 KB
/
Program.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Devart.Data.SQLite;
using System.Text.RegularExpressions;
using System.IO;
namespace books
{
class BooksStorage
{
private SQLiteConnection dbConnect;
public BooksStorage(String file)
{
dbConnect = new SQLiteConnection();
dbConnect.ConnectionString = $"Data Source={file};";
dbConnect.Open();
ModifyRequest("PRAGMA foreign_keys = ON", null);
}
private String SelectOne(String sql, Dictionary<String, String> param)
{
SQLiteCommand command = dbConnect.CreateCommand();
command.CommandText = sql;
if (param != null)
{
foreach (var pair in param)
{
command.Parameters.Add(pair.Key, pair.Value);
}
}
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read()) return reader.GetString(0);
else return "";
}
}
private int ModifyRequest(String sql, Dictionary<String, String> param)
{
SQLiteCommand command = dbConnect.CreateCommand();
command.CommandText = sql;
if (param != null)
{
foreach (var pair in param)
{
command.Parameters.Add(pair.Key, pair.Value);
}
}
try
{
return command.ExecuteNonQuery();
}
catch
{
return 0;
}
}
public void ShowBooks(String title)
{
String sql =
$@"select b.id, b.title, GROUP_CONCAT(a.name, ', ') au from books b
left join lnk_books_authors l on b.id = l.book_id
left join authors a on l.author_id = a.id
where b.title like :title
group by b.id, b.title";
SQLiteCommand command = dbConnect.CreateCommand();
command.CommandText = sql;
command.Parameters.Add("title", $"{title.Trim()}%");
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"{reader.GetString(0)}\t{reader.GetString(1)} - {reader.GetString(2)}");
}
}
}
public int DeleteBook(String id)
{
return ModifyRequest("DELETE FROM books WHERE id=:id",
new Dictionary<string, string> { { "id", id } });
}
public int UpdateBook(String id, String title)
{
title = title.Trim();
if (title == "") return 0;
return ModifyRequest("UPDATE books SET title=:title WHERE id=:id",
new Dictionary<string, string> { { "title", title }, { "id", id } });
}
public String GetTitle(String book_id)
{
return SelectOne("SELECT title FROM books WHERE id=:id",
new Dictionary<string, string> { { "id", book_id } });
}
public String GetAuthors(String book_id)
{
String sql =
$@"select GROUP_CONCAT(a.name, ', ') au from books b
left join lnk_books_authors l on b.id = l.book_id
left join authors a on l.author_id = a.id
where b.id = :id";
return SelectOne(sql, new Dictionary<string, string> { { "id", book_id } });
}
private String GetAuthorId(String name)
{
return SelectOne("SELECT id FROM authors WHERE name=:name",
new Dictionary<string, string> { { "name", name } });
}
private String AuthorId(String name) // Returns id for existing author or create new author
{
String personId = SelectOne("SELECT id FROM authors WHERE name=:name",
new Dictionary<string, string> { { "name", name } });
if (Regex.IsMatch(personId, @"^\d+$")) return personId; // exists
String maxId = SelectOne("SELECT MAX(id)+1 FROM authors", null);
ModifyRequest("INSERT INTO authors(id, name) VALUES (:id, :name)",
new Dictionary<string, string> { { "id", maxId }, { "name", name } });
return maxId; // new author
}
private int CreateLink(String book_id, String author_id)
{
return ModifyRequest("INSERT INTO lnk_books_authors VALUES (:book_id, :author_id)",
new Dictionary<string, string> { { "book_id", book_id }, { "author_id", author_id } });
}
public int InsertBook(String title, String authosr)
{
title = title.Trim();
if (title == "") return 0;
String maxId = SelectOne("SELECT MAX(id)+1 FROM books", null);
int booksCount = ModifyRequest("INSERT INTO books(id, title) VALUES (:id, :title)",
new Dictionary<string, string> { { "id", maxId }, { "title", title } });
if (booksCount != 1) return booksCount;
foreach (String au in Regex.Split(authosr, @"\s*,\s*"))
{
String person = CleanName(au);
if (person == "") continue;
CreateLink(maxId, AuthorId(person));
}
return booksCount;
}
public void UpdateAuthors(String book_id, String authorsList)
{
int add = 0;
int del = 0;
foreach (String au in Regex.Split(authorsList, @"\s*,\s*"))
{
String person = CleanName(au);
if (person == "") continue;
if (Regex.IsMatch(au, @"^-")) // author for delete
{
del += ModifyRequest("DELETE FROM lnk_books_authors WHERE book_id = :book_id AND author_id = :author_id",
new Dictionary<string, string> { { "book_id", book_id }, { "author_id", GetAuthorId(person) } });
}
else
{
add += CreateLink(book_id, AuthorId(person));
}
}
Console.WriteLine($"Authors add: {add}; delete: {del}");
}
public static String CleanName(String name)
{
// removes [+-;=,.] at the beginning and duplicate spaces between words
return String.Join(" ", Regex.Split(Regex.Replace(name.Trim(), @"^[+-;=,.]+", "").Trim(), @"\s+"));
}
}
class UserInterface
{
private BooksStorage db;
public UserInterface(BooksStorage database)
{
this.db = database;
}
public void MainMenu()
{
Boolean actionLoop = true;
while (actionLoop)
{
Console.WriteLine("1 - SELECT, 2 - INSERT, 3 - UPDATE, 4 - DELETE, 5 - EXIT");
switch (Console.ReadKey(true).KeyChar)
{
case '1':
ShowBooks();
break;
case '2':
AddBook();
break;
case '3':
UpdateBook();
break;
case '4':
DeleteBook();
break;
case '5':
actionLoop = false;
break;
default:
Console.WriteLine("Incorrect choice");
break;
}
Console.WriteLine();
}
}
public void ShowBooks()
{
String filter = Prompt("SELECT:\nEnter the first letters of book's title, or nothing (all books)");
db.ShowBooks(filter);
}
public void AddBook()
{
String title = Prompt("INSERT:\nEnter book title");
if (title == "") return;
String authors = Prompt("Coma separated list of authors");
Console.WriteLine($"{db.InsertBook(title, authors)} was added");
}
public void UpdateBook()
{
String updId = Prompt("UPDATE:\nEnter book number for update");
String title = db.GetTitle(updId);
if (title == "")
{
Console.WriteLine("Book not found");
return;
}
Console.WriteLine($"Title: {title}");
String updName = Prompt("Enter new book's title");
Console.WriteLine($"{db.UpdateBook(updId, updName)} Book name was updated");
Console.WriteLine($"Authors: {db.GetAuthors(updId)}");
String updAuthors = Prompt("Coma separater lisn for add (<name>) or delete ( -<name> )");
db.UpdateAuthors(updId, updAuthors);
}
public void DeleteBook()
{
String del_id = Prompt("DELETE:\nEnter book's number to delete");
Console.WriteLine($"{db.DeleteBook(del_id)} was deleted");
}
public static String Prompt(String msg)
{
Console.WriteLine(msg);
Console.Write("> ");
return Console.ReadLine();
}
}
class Program
{
static String GetFullDbName(String dbName)
{
String dir = Directory.GetCurrentDirectory();
while(true)
{
String fullName = $@"{dir}\{dbName}";
if (File.Exists(fullName)) return fullName;
DirectoryInfo parent = Directory.GetParent(dir);
if (parent == null) return null;
dir = parent.FullName;
}
}
static void Main(string[] args)
{
Console.WriteLine("Library management system (C) Gmyrak Dmitry\n");
String dbName = "books.db";
String fullDbName = GetFullDbName(dbName);
if (fullDbName == null)
{
Console.WriteLine($"Database file {dbName} not found");
Environment.Exit(1);
}
Console.WriteLine($"Using database file: {fullDbName}\n");
BooksStorage bs = new BooksStorage(fullDbName);
UserInterface ui = new UserInterface(bs);
ui.MainMenu();
Console.WriteLine("Bye!");
}
}
}