~aleteoryx/muditaos

ref: c8c4f82080c1fd95c54c4fc28d64b9eed2255f92 muditaos/module-db/Tables/NotesTable.cpp -rw-r--r-- 4.0 KiB
c8c4f820 — Piotr Tanski [EGD-4487] Application notes implemented. (#1094) 5 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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136

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

#include "NotesTable.hpp"

NotesTable::NotesTable(Database *db) : Table(db)
{}

bool NotesTable::create()
{
    return true;
}

bool NotesTable::add(NotesTableRow entry)
{
    return db->execute(
        "INSERT or ignore INTO notes ( date, snippet ) VALUES ( %lu, '%q' );", entry.date, entry.snippet.c_str());
}

bool NotesTable::removeAll()
{
    return db->execute("DELETE FROM notes;");
}

bool NotesTable::removeById(std::uint32_t id)
{
    return db->execute("DELETE FROM notes where _id = %lu;", id);
}

bool NotesTable::removeByField(NotesTableFields field, const char *str)
{
    std::string fieldName;
    switch (field) {
    case NotesTableFields::Date:
        fieldName = "date";
        break;
    case NotesTableFields::Snippet:
        fieldName = "snippet";
        break;
    default:
        return false;
    }
    return db->execute("DELETE FROM note where %q = '%q';", fieldName.c_str(), str);
}

bool NotesTable::update(NotesTableRow entry)
{
    return db->execute(
        "UPDATE notes SET date = %lu, snippet = '%q' WHERE _id = %lu;", entry.date, entry.snippet.c_str(), entry.ID);
}

NotesTableRow NotesTable::getById(std::uint32_t id)
{
    auto retQuery = db->query("SELECT * FROM notes WHERE _id = %lu;", id);
    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return NotesTableRow();
    }
    return NotesTableRow{
        (*retQuery)[0].getUInt32(), // ID
        (*retQuery)[1].getUInt32(), // date
        (*retQuery)[2].getString()  // snippet
    };
}

std::vector<NotesTableRow> NotesTable::getLimitOffset(std::uint32_t offset, std::uint32_t limit)
{
    auto retQuery = db->query("SELECT * FROM notes ORDER BY date DESC LIMIT %lu OFFSET %lu;", limit, offset);
    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<NotesTableRow>();
    }

    std::vector<NotesTableRow> ret;
    do {
        ret.push_back(NotesTableRow{
            (*retQuery)[0].getUInt32(), // ID
            (*retQuery)[1].getUInt32(), // date
            (*retQuery)[2].getString()  // snippet
        });
    } while (retQuery->nextRow());
    return ret;
}

std::vector<NotesTableRow> NotesTable::getLimitOffsetByField(std::uint32_t offset,
                                                             std::uint32_t limit,
                                                             NotesTableFields field,
                                                             const char *str)
{
    std::string fieldName;
    switch (field) {
    case NotesTableFields::Date:
        fieldName = "date";
        break;
    case NotesTableFields ::Snippet:
        fieldName = "snippet";
        break;
    default:
        return std::vector<NotesTableRow>();
    }

    auto retQuery = db->query("SELECT * FROM notes WHERE %q='%q' ORDER BY date DESC LIMIT %lu OFFSET %lu;",
                              fieldName.c_str(),
                              str,
                              limit,
                              offset);
    if ((retQuery == nullptr) || (retQuery->getRowCount() == 0)) {
        return std::vector<NotesTableRow>();
    }

    std::vector<NotesTableRow> ret;
    do {
        ret.push_back(NotesTableRow{
            (*retQuery)[0].getUInt32(), // ID
            (*retQuery)[1].getUInt32(), // date
            (*retQuery)[2].getString()  // snippet
        });
    } while (retQuery->nextRow());
    return ret;
}

std::uint32_t NotesTable::count()
{
    auto queryRet = db->query("SELECT COUNT(*) FROM notes;");
    if (queryRet->getRowCount() == 0) {
        return 0;
    }
    return (*queryRet)[0].getUInt32();
}

std::uint32_t NotesTable::countByFieldId(const char *field, std::uint32_t id)
{
    auto queryRet = db->query("SELECT COUNT(*) FROM notes WHERE %q = %lu;", field, id);
    if ((queryRet == nullptr) || (queryRet->getRowCount() == 0)) {
        return 0;
    }
    return (*queryRet)[0].getUInt32();
}