File size: 7,227 Bytes
27867f1 |
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 |
"""
MIT License
Copyright (C) 2023 ROCKY4546
https://github.com/rocky4546
This file is part of Cabernet
Permission is hereby granted, free of charge, to any person obtaining a copy of this software
and associated documentation files (the "Software"), to deal in the Software without restriction,
including without limitation the rights to use, copy, modify, merge, publish, distribute,
sublicense, and/or sell copies of the Software, and to permit persons to whom the Software
is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or
substantial portions of the Software.
"""
import json
from lib.db.db import DB
from lib.common.decorators import Backup
from lib.common.decorators import Restore
DB_AREA_TABLE = 'area'
DB_SECTION_TABLE = 'section'
DB_INSTANCE_TABLE = 'instance'
DB_CONFIG_TABLE = 'config'
DB_CONFIG_NAME = 'db_files-defn_db'
sqlcmds = {
'ct': [
"""
CREATE TABLE IF NOT EXISTS area (
name VARCHAR(255) NOT NULL,
icon VARCHAR(255) NOT NULL,
label VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
PRIMARY KEY(name)
)
""",
"""
CREATE TABLE IF NOT EXISTS section (
area VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
sort VARCHAR(255) NOT NULL,
icon VARCHAR(255) NOT NULL,
label VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
settings TEXT NOT NULL,
FOREIGN KEY(area) REFERENCES area(name),
UNIQUE(area, name)
)
""",
"""
CREATE TABLE IF NOT EXISTS config (
key VARCHAR(255) NOT NULL,
settings TEXT NOT NULL,
PRIMARY KEY(key)
)
""",
"""
CREATE TABLE IF NOT EXISTS instance (
area VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
icon VARCHAR(255) NOT NULL,
label VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
settings TEXT NOT NULL,
FOREIGN KEY(area) REFERENCES area(name),
UNIQUE(area, name)
)
"""
],
'dt': [
"""
DROP TABLE IF EXISTS area
""",
"""
DROP TABLE IF EXISTS section
""",
"""
DROP TABLE IF EXISTS config
""",
"""
DROP TABLE IF EXISTS instance
"""
],
'area_add':
"""
INSERT OR REPLACE INTO area (
name, icon, label, description
) VALUES ( ?, ?, ?, ? )
""",
'area_get':
"""
SELECT * from area WHERE name LIKE ? ORDER BY rowid
""",
'area_keys_get':
"""
SELECT name from area ORDER BY rowid
""",
'section_add':
"""
INSERT OR REPLACE INTO section (
area, name, sort, icon, label, description, settings
) VALUES ( ?, ?, ?, ?, ?, ?, ? )
""",
'section_get':
"""
SELECT * from section WHERE area = ? ORDER BY sort
""",
'section_one_get':
"""
SELECT * from section WHERE area = ? AND name = ? ORDER BY sort
""",
'section_name_get':
"""
SELECT area from section WHERE name = ? ORDER BY sort
""",
'instance_add':
"""
INSERT OR REPLACE INTO instance (
area, name, icon, label, description, settings
) VALUES ( ?, ?, ?, ?, ?, ? )
""",
'instance_get':
"""
SELECT * from instance WHERE area = ? ORDER BY rowid
""",
'config_add':
"""
INSERT OR REPLACE INTO config (
key, settings
) VALUES ( 'main', ? )
""",
'config_get':
"""
SELECT settings from config
"""
}
class DBConfigDefn(DB):
def __init__(self, _config):
super().__init__(_config, _config['datamgmt'][DB_CONFIG_NAME], sqlcmds)
def get_area_dict(self, _where=None):
if not _where:
_where = '%'
return self.get_dict(DB_AREA_TABLE, (_where,))
def get_area_json(self, _where=None):
if not _where:
_where = '%'
return json.dumps(self.get_dict(DB_AREA_TABLE, (_where,)))
def get_areas(self):
""" returns an array of the area names in id order
"""
area_tuple = self.get('area_keys')
areas = [area[0] for area in area_tuple]
return areas
def add_area(self, _area, _area_data):
self.add(DB_AREA_TABLE, (
_area,
_area_data['icon'],
_area_data['label'],
_area_data['description']
))
def get_sections_dict(self, _where):
rows_dict = {}
rows = self.get_dict(DB_SECTION_TABLE, (_where,))
for row in rows:
settings = json.loads(row['settings'])
row['settings'] = settings
rows_dict[row['name']] = row
return rows_dict
def get_one_section_dict(self, _area, _section):
rows_dict = {}
rows = self.get_dict(DB_SECTION_TABLE+'_one', (_area, _section,))
for row in rows:
settings = json.loads(row['settings'])
row['settings'] = settings
rows_dict[row['name']] = row
return rows_dict
def get_area_by_section(self, _where):
""" returns an array of the area names that match the section
"""
area_tuple = self.get('section_name', (_where,))
areas = [area[0] for area in area_tuple]
return areas
def add_section(self, _area, _section, _section_data):
self.add(DB_SECTION_TABLE, (
_area,
_section,
_section_data['sort'],
_section_data['icon'],
_section_data['label'],
_section_data['description'],
json.dumps(_section_data['settings'])
))
def get_instance_dict(self, _where):
rows_dict = {}
rows = self.get_dict(DB_INSTANCE_TABLE, (_where,))
for row in rows:
settings = json.loads(row['settings'])
row['settings'] = settings
rows_dict[row['name']] = row
return rows_dict
def add_instance(self, _area, _section, _section_data):
self.add(DB_INSTANCE_TABLE, (
_area,
_section,
_section_data['icon'],
_section_data['label'],
_section_data['description'],
json.dumps(_section_data['settings'])
))
def add_config(self, _config):
self.add(DB_CONFIG_TABLE, (
json.dumps(_config),
))
def get_config(self):
return json.loads(self.get_dict(DB_CONFIG_TABLE)[0]['settings'])
@Backup(DB_CONFIG_NAME)
def backup(self, backup_folder):
self.export_sql(backup_folder)
@Restore(DB_CONFIG_NAME)
def restore(self, backup_folder):
msg = self.import_sql(backup_folder)
if msg is None:
return 'Config Database Restored'
else:
return msg
|