cWGMLNLx

1#!/usr/bin/env python3
2# -*- coding: utf-8 -*-
3
4from sqlite3 import *
5from os import *
6
7DATABASE_FILE = "database_discretionary_model.db"
8
9def select_database():
10    print("""Хотите создать новую базу данных?
11
12    1. да
13    2. нет
14    """)
15    submenu_item = input(">>> ")
16    return submenu_item
17
18def database_exist():
19    return path.exists(DATABASE_FILE)
20
21def delete_database():
22    if database_exist():
23        remove(DATABASE_FILE)
24
25def create_new_database():
26    if database_exist():
27        open(DATABASE_FILE).close()
28
29def connect_to_database():
30    if not database_exist():
31        create_new_database()
32    con = connect(DATABASE_FILE) # Подключаемся к базе данных
33    cur = con.cursor() # Создаем объект-курсор
34    return con, cur
35
36def create_new_tables(con, cur):
37    sql = """
38    CREATE TABLE IF NOT EXISTS subject (
39        subject_number INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
40        name TEXT NOT NULL UNIQUE
41    );
42    CREATE TABLE IF NOT EXISTS object (
43        object_number INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
44        name TEXT NOT NULL UNIQUE
45    );
46    CREATE TABLE IF NOT EXISTS matrix_access_rights (
47        subject_number INTEGER NOT NULL,
48        object_number INTEGER NOT NULL,
49        access_right TEXT NOT NULL,
50        PRIMARY KEY (subject_number, object_number, access_right),
51        FOREIGN KEY (subject_number) REFERENCES subject(subject_number),
52        FOREIGN KEY (object_number) REFERENCES object(object_number)
53    );
54    """
55    cur.executescript(sql) # Выполняем SQL-запросы
56    con.commit() # Завершаем транзакцию
57
58def select_menu_option():
59    menu = """Выберите вариант
60
61    1. добавить право доступа в клетку матрицы
62    2. удалить право доступа из клетки матрицы
63    3. добавить субъекта в матрицу
64    4. удалить субъекта из матрицы
65    5. добавить объекта в матрицу
66    6. удалить объекта из матрицы
67    7. список объектов, доступ к которым не задан никакому субъекту (ни по чтению, ни по записи)
68    8. список субъектов, не обращающихся ни к каким объектам (ни по чтению, ни по записи)
69    9. список субъектов, имеющих полный доступ ко всем объектам
70    10. список субъектов, которые имеют право записи в некоторый один объект
71    11. список субъектов, каждый из которых имеет полный доступ только к одному объекту
72    12. вычислить процент заполненности матрицы
73    13. сформировать список субъектов, имеющих некоторый доступ к заданному объекту
74    14. выйти
75    """
76    print(menu)
77    menu_option = input(">>> ")
78    return menu_option
79
80def choose_right_access():
81    print("""Выберите право доступа
82
83    1. r
84    2. w
85    3. выйти
86    """)
87    submenu_item = input(">>> ")
88    return submenu_item
89
90def add_access_right(con, cur):
91    print("Добавление права доступа в клетку матрицы...")
92    subject_number = input("Номер субъекта ")
93    object_number = input("Номер объекта ")
94    while True:
95        submenu_item = choose_right_access()
96        if submenu_item == "1":
97            print("Добавление права доступа r в клетку матрицы...")
98            access_right = "r"
99        elif submenu_item == "2":
100            print("Добавление права доступа w в клетку матрицы...")
101            access_right = "w"
102        elif submenu_item == "3":
103            break
104        else:
105            print("Вы выбрали неверный вариант")
106        if access_right == "r" or access_right == "w":
107            d = {"subject_number": subject_number, "object_number": object_number, "access_right": access_right}
108            sql = """
109            INSERT OR IGNORE INTO matrix_access_rights
110            SELECT :subject_number, :object_number, :access_right 
111            WHERE EXISTS (
112                SELECT subject_number FROM subject
113                WHERE subject_number=:subject_number
114            ) AND EXISTS (
115                SELECT object_number FROM object
116                WHERE object_number=:object_number
117            )
118            """
119            cur.execute(sql, d)
120            con.commit() # Завершаем транзакцию
121
122def remove_access_right(con, cur):
123    print("Удаление права доступа из клетки матрицы...")
124    subject_number = input("Номер субъекта ")
125    object_number = input("Номер объекта ")
126    while True:
127        submenu_item = choose_right_access()
128        if submenu_item == "1":
129            print("Удаление права доступа r из клетки матрицы...")
130            access_right = "r"
131        elif submenu_item == "2":
132            print("Удаление права доступа w из клетки матрицы...")
133            access_right = "w"
134        elif submenu_item == "3":
135            break
136        else:
137            print("Вы выбрали неверный вариант")
138        t = (subject_number, object_number, access_right)
139        sql = """
140        DELETE FROM matrix_access_rights
141        WHERE subject_number=? AND object_number=? AND access_right=?
142        """
143        cur.execute(sql, t)
144        con.commit() # Завершаем транзакцию
145
146def add_subject(con, cur):
147    print("Добавление субъекта в матрицу...")
148    name = input("Добавить ")
149    t = (name,)
150    sql = """
151    INSERT OR IGNORE INTO subject(name)
152    VALUES (:name)
153    """
154    cur.execute(sql, t)
155    sql = """
156    SELECT subject_number, name
157    FROM subject
158    WHERE name=:name
159    """
160    cur.execute(sql, t)
161    con.commit() # Завершаем транзакцию
162    subject_number, name = cur.fetchone()
163    print(name, "зарегистрирован как", subject_number)
164
165def remove_subject(con, cur):
166    print("Удаление субъекта из матрицы...")
167    subject_number = input("Удалить ")
168    t = (subject_number,)
169    sql = """
170    DELETE FROM matrix_access_rights
171    WHERE subject_number=:subject_number
172    """
173    cur.execute(sql, t)
174    sql = """
175    DELETE FROM subject
176    WHERE subject_number=:subject_number
177    """
178    cur.execute(sql, t)
179    con.commit() # Завершаем транзакцию
180
181def add_object(con, cur):
182    print("Добавление объекта в матрицу...")
183    name = input("Добавить ")
184    t = (name,)
185    sql = """
186    INSERT OR IGNORE INTO object(name)
187    VALUES (:name)
188    """
189    cur.execute(sql, t)
190    sql = """
191    SELECT object_number, name
192    FROM object
193    WHERE name=:name
194    """
195    cur.execute(sql, t)
196    con.commit() # Завершаем транзакцию
197    subject_number, name = cur.fetchone()
198    print(name, "зарегистрирован как", subject_number)
199
200def remove_object(con, cur):
201    print("Удаление объекта из матрицы...")
202    object_number = input("Удалить ")
203    t = (object_number,)
204    sql = """
205    DELETE FROM matrix_access_rights
206    WHERE object_number=:object_number
207    """
208    cur.execute(sql, t)
209    sql = """
210    DELETE FROM object
211    WHERE object_number=:object_number
212    """
213    cur.execute(sql, t)
214    con.commit() # Завершаем транзакцию
215
216def print_list_objects_that_no_subject_can_access(con, cur):
217    sql = """
218    SELECT o.name
219    FROM object AS o
220    WHERE o.object_number NOT IN (
221        SELECT m.object_number
222        FROM matrix_access_rights AS m
223        WHERE o.object_number = m.object_number
224    )
225    """
226    cur.execute(sql)
227    con.commit() # Завершаем транзакцию
228    for (name,) in cur.fetchall():
229        print(name)
230
231def print_list_subjects_that_do_not_apply_to_any_objects(con, cur):
232    sql = """
233    SELECT s.name
234    FROM subject AS s
235    WHERE s.subject_number NOT IN (
236        SELECT m.subject_number
237        FROM matrix_access_rights AS m
238        WHERE s.subject_number = m.subject_number
239    )
240    """
241    cur.execute(sql)
242    con.commit() # Завершаем транзакцию
243    for (name,) in cur.fetchall():
244        print(name)
245
246def print_list_subjects_that_have_full_access_to_all_objects(con, cur):
247    sql = """
248    SELECT s2.name
249    FROM subject AS s2
250    WHERE s2.subject_number IN (
251        SELECT s1.subject_number
252        FROM (
253            SELECT *
254            FROM matrix_access_rights AS m
255            WHERE s2.subject_number = m.subject_number
256            GROUP BY m.subject_number, m.object_number
257            HAVING COUNT(*) = 2
258        ) AS s1
259        GROUP BY s1.subject_number
260        HAVING COUNT(*) = (
261            SELECT COUNT(*) FROM object
262        )
263    )
264    """
265    cur.execute(sql)
266    con.commit() # Завершаем транзакцию
267    for (name,) in cur.fetchall():
268        print(name)
269
270def print_list_subjects_that_allowed_to_write_to_some_single_object(con, cur):
271    object_number = input("Объект ")
272    t = (object_number,)
273    sql = """
274    SELECT s2.name
275    FROM subject AS s2
276    WHERE s2.subject_number IN (
277        SELECT m.subject_number
278        FROM matrix_access_rights AS m, subject AS s1
279        WHERE s1.subject_number = m.subject_number AND m.object_number=:object_number AND m.access_right = "w"
280    )
281    """
282    cur.execute(sql, t)
283    con.commit() # Завершаем транзакцию
284    for (name,) in cur.fetchall():
285        print(name)
286
287def print_list_subjects_each_which_has_full_access_to_only_one_object(con, cur):
288    sql = """
289    SELECT s2.name
290    FROM subject AS s2
291    WHERE s2.subject_number IN (
292        SELECT s1.subject_number
293        FROM (
294            SELECT *
295            FROM matrix_access_rights AS m
296            WHERE s2.subject_number = m.subject_number
297            GROUP BY m.subject_number, m.object_number
298            HAVING COUNT(*) = 2
299        ) AS s1
300        GROUP BY s1.subject_number
301        HAVING COUNT(*) = 1
302    ) AND s2.subject_number NOT IN (
303        SELECT s1.subject_number
304        FROM (
305            SELECT m.subject_number, GROUP_CONCAT(m.access_right) AS access_right
306            FROM matrix_access_rights AS m
307            WHERE m.subject_number = m.subject_number
308            GROUP BY m.subject_number, m.object_number
309        ) AS s1
310        WHERE s1.access_right = "w"
311    )
312    """
313    cur.execute(sql)
314    con.commit() # Завершаем транзакцию
315    for (name,) in cur.fetchall():
316        print(name)
317
318def calculate_percentage_occupancy(con, cur):
319    print("Вычисление процента заполненности матрицы...")
320    sql = """
321    SELECT COUNT(*) FROM (
322        SELECT *
323        FROM matrix_access_rights
324        GROUP BY subject_number, object_number
325    )
326    """
327    cur.execute(sql)
328    (number_standard_cells,) = cur.fetchone()
329    sql = """
330    SELECT COUNT(*) * (
331        SELECT COUNT(*)
332        FROM object
333    )
334    FROM subject
335    """
336    cur.execute(sql)
337    con.commit() # Завершаем транзакцию
338    (number_cells,) = cur.fetchone()
339    if number_cells == 0:
340        percentage_occupancy = 0
341    else:
342        percentage_occupancy = int(100 * round(number_standard_cells / number_cells, 2))
343    print(str(percentage_occupancy) + "%")
344
345def print_list_subjects(con, cur):
346    print("Формирование списка субъектов, имеющих некоторый доступ к заданному объекту...")
347    object_number = input("Номер объекта ")
348    t = (object_number,)
349    sql = """
350    SELECT s.name, GROUP_CONCAT(m.access_right)
351    FROM matrix_access_rights AS m, subject AS s
352    WHERE s.subject_number = m.subject_number
353    AND m.object_number=:object_number
354    GROUP BY m.subject_number, m.object_number
355    """
356    cur.execute(sql, t)
357    con.commit() # Завершаем транзакцию
358    for name, access_right in cur.fetchall():
359        print(name, access_right)
360
361def program_exit(con, cur):
362    print("Выход из программы...")
363    cur.close() # Закрываем объект-курсор
364    con.close() # Закрываем соединение
365
366print("Дискреционная модель...")
367while True:
368    submenu_item = select_database()
369    if submenu_item == "1":
370        delete_database()
371        create_new_database()
372        con, cur = connect_to_database()
373        create_new_tables(con, cur)
374        break
375    elif submenu_item == "2":
376        con, cur = connect_to_database()
377        break
378    else:
379        print("Вы выбрали неверный вариант")
380while True:
381    menu_option = select_menu_option()
382    if menu_option == "1":
383        add_access_right(con, cur)
384    elif menu_option == "2":
385        remove_access_right(con, cur)
386    elif menu_option == "3":
387        add_subject(con, cur)
388    elif menu_option == "4":
389        remove_subject(con, cur)
390    elif menu_option == "5":
391        add_object(con, cur)
392    elif menu_option == "6":
393        remove_object(con, cur)
394    elif menu_option == "7":
395        print_list_objects_that_no_subject_can_access(con, cur)
396    elif menu_option == "8":
397        print_list_subjects_that_do_not_apply_to_any_objects(con, cur)
398    elif menu_option == "9":
399        print_list_subjects_that_have_full_access_to_all_objects(con, cur)
400    elif menu_option == "10":
401        print_list_subjects_that_allowed_to_write_to_some_single_object(con, cur)
402    elif menu_option == "11":
403        print_list_subjects_each_which_has_full_access_to_only_one_object(con, cur)
404    elif menu_option == "12":
405        calculate_percentage_occupancy(con, cur)
406    elif menu_option == "13":
407        print_list_subjects(con, cur)
408    elif menu_option == "14":
409        program_exit(con, cur)
410        break
411    else:
412        print("Вы выбрали неверный вариант")