~aleteoryx/muditaos

ref: 5b0f2aa44a61bd2124fea48bdf1f9e2fc47c4eb6 muditaos/module-services/service-db/agents/quotes/QuotesQueries.hpp -rw-r--r-- 4.5 KiB
5b0f2aa4 — Mateusz Grzegorzek [EGD-6262] Refactor Quotes 4 years ago 
                                                                                
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

// Copyright (c) 2017-2021, Mudita Sp. z.o.o. All rights reserved.
// For licensing, see https://github.com/mudita/MuditaOS/LICENSE.md

#pragma once

namespace Quotes::Queries
{
    constexpr auto getAllCategories = R"sql(
                        SELECT category_id, category_name, enabled
                        FROM category_table;
                        )sql";

    constexpr auto getAllQuotes = R"sql(
                        SELECT quote_id, lang_id, quote, author, enabled
                        FROM quote_table;
                        )sql";

    constexpr auto getQuotesByCategoryId = R"sql(
                        SELECT QT.quote_id, QT.lang_id, QT.quote, QT.author, QT.enabled
                        FROM 
                            quote_table as QT,
                            quote_category_map as QCM,
                            category_table as CT
                        WHERE
                            QCM.quote_id = QT.quote_id
                            and 
                            QCM.category_id = CT.category_id
                            and
                            QCM.category_id = '%lu'
                            and
                            CT.enabled = TRUE
                        )sql";

    constexpr auto getQuotesFromCustomCategory = R"sql(
                        SELECT QT.quote_id, QT.lang_id, QT.quote, QT.author, QT.enabled
                        FROM
                            quote_table as QT,
                            quote_category_map as QCM,
                            category_table as CT
                        WHERE
                            QCM.quote_id = QT.quote_id
                            and 
                            QCM.category_id = CT.category_id
                            and
                            CT.category_name = 'Custom'
                            and
                            CT.enabled = TRUE
                        )sql";

    constexpr auto getCustomCategoryId = R"sql(
                        SELECT category_id, category_name, enabled
                        FROM
                            category_table
                        WHERE
                            category_name = 'Custom'
                        )sql";

    constexpr auto enableCategory = R"sql(
                        UPDATE category_table SET enabled = '%d'
                        WHERE category_id = '%lu';
                        )sql";

    constexpr auto enableQuote = R"sql(
                        UPDATE quote_table SET enabled = '%d'
                        WHERE quote_id = '%lu';
                        )sql";

    constexpr auto getEnabledQuotes = R"sql(
                        SELECT QT.quote_id, QT.lang_id, QT.quote, QT.author, QT.enabled
                        FROM
                            quote_table as QT,
                            quote_category_map as QCM,
                            category_table as CT
                        WHERE
                            QCM.quote_id = QT.quote_id
                            and
                            QCM.category_id = CT.category_id
                            and
                            QT.enabled = TRUE
                            and
                            CT.enabled = TRUE
                        )sql";

    constexpr auto addQuoteToQuoteTable = R"sql(
                        INSERT INTO quote_table (lang_id, quote, author, enabled)
                        VALUES ('%lu', '%q' , '%q', '%d');
                        )sql";

    constexpr auto addQuoteToQuoteCategoryMapTable = R"sql(
                        INSERT INTO quote_category_map (category_id, quote_id)
                        VALUES ('%lu', '%lu');
                        )sql";

    constexpr auto readQuote = R"sql(
                        SELECT quote_id, lang_id, quote, author, enabled
                        FROM quote_table
                        WHERE quote_id = '%lu';
                        )sql";

    constexpr auto writeQuote = R"sql(
                        UPDATE quote_table
                        SET lang_id = '%lu', quote = '%q', author = '%q', enabled = '%d'
                        WHERE quote_id = '%lu';
                        )sql";

    constexpr auto deleteQuoteFromQuoteCategoryMapTable = R"sql(
                        DELETE FROM quote_category_map
                        WHERE quote_id = '%lu';
                        )sql";

    constexpr auto deleteQuoteFromQuoteTable = R"sql(
                        DELETE FROM quote_table
                        WHERE quote_id = '%lu';
                        )sql";
} // namespace Quotes::Queries