flaskBlog2/dbChecker.py at master · PyAcademy/flaskBlog2 · GitHub

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
from helpers import mkdir, exists, message, sqlite3


def dbFolder():
    match exists("db"):
        case True:
            message("6", 'Folder: "/db" FOUND')
        case False:
            message("1", 'Folder: "/db" NOT FOUND')
            mkdir("db")
            message("2", 'Folder: "/db" CREATED')


def usersTable():
    match exists("db/users.db"):
        case True:
            message("6", 'DATABASE: "users.db" FOUND')
        case False:
            message("1", 'DATABASE: "users.db" NOT FOUND')
            open("db/users.db", "x")
            message("2", 'DATABASE: "users.db" CREATED')
    connection = sqlite3.connect("db/users.db")
    cursor = connection.cursor()
    try:
        cursor.execute("""SELECT * FROM users; """).fetchall()
        message("6", 'TABLE: "Users" FOUND')
        connection.close()
    except:
        message("1", 'TABLE: "Users" NOT FOUND')
        usersTable = """
        CREATE TABLE IF NOT EXISTS Users(
	    "userID"	INTEGER NOT NULL UNIQUE,
	    "userName"	TEXT UNIQUE,
	    "email"	TEXT UNIQUE,
	    "password"	TEXT,
	    "role"	TEXT,
	    "points"	INTEGER,
	    "creationDate"	TEXT,
	    "creationTime"	TEXT,
	    PRIMARY KEY("userID" AUTOINCREMENT)
        );"""
        cursor.execute(usersTable)
        connection.commit()
        connection.close()
        message("2", 'TABLE: "Users" CREATED')


def postsTable():
    match exists("db/posts.db"):
        case True:
            message("6", 'DATABASE: "posts.db" FOUND')
        case False:
            message("1", 'DATABASE: "posts.db" NOT FOUND')
            open("db/posts.db", "x")
            message("2", 'DATABASE: "posts.db" CREATED')
    connection = sqlite3.connect("db/posts.db")
    cursor = connection.cursor()
    try:
        cursor.execute("""SELECT * FROM posts; """).fetchall()
        message("6", 'TABLE: "Posts" FOUND')
        connection.close()
    except:
        message("1", 'TABLE: "Posts" NOT FOUND')
        postsTable = """
        CREATE TABLE "posts" (
    	"id"	INTEGER NOT NULL UNIQUE,
    	"title"	TEXT NOT NULL,
    	"tags"	TEXT,
    	"content"	TEXT NOT NULL,
    	"author"	TEXT NOT NULL,
    	"date"	TEXT NOT NULL,
    	"time"	TEXT NOT NULL,
    	"views"	TEXT,
    	"lastEditDate"	TEXT,
        "lastEditTime"	TEXT,
    	PRIMARY KEY("id" AUTOINCREMENT)
        );"""
        cursor.execute(postsTable)
        connection.commit()
        connection.close()
        message("2", 'TABLE: "Posts" CREATED')


def commentsTable():
    match exists("db/comments.db"):
        case True:
            message("6", 'DATABASE: "comments.db" FOUND')
        case False:
            message("1", 'DATABASE: "comments.db" NOT FOUND')
            open("db/comments.db", "x")
            message("2", 'DATABASE: "comments.db" CREATED')
    connection = sqlite3.connect("db/comments.db")
    cursor = connection.cursor()
    try:
        cursor.execute("""SELECT * FROM comments; """).fetchall()
        message("6", 'TABLE: "Comments" FOUND')
        connection.close()
    except:
        message("1", 'TABLE: "Comments" NOT FOUND')
        commentsTable = """
        CREATE TABLE IF NOT EXISTS comments(
	    "id"	INTEGER NOT NULL,
	    "post"	INTEGER,
	    "comment"	TEXT,
	    "user"	TEXT,
	    "date"	TEXT,
	    "time"	TEXT,
	    PRIMARY KEY("id" AUTOINCREMENT)
        );"""
        cursor.execute(commentsTable)
        connection.commit()
        connection.close()
        message("2", 'TABLE: "Comments" CREATED')