File size: 12,179 Bytes
bb7f1f4 | 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 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 | import os
import sqlite3
import threading
from typing import List
from modules import shared
from scripts.mo.data.storage import Storage
from scripts.mo.environment import env, logger
from scripts.mo.models import Record, ModelType
_DB_FILE = 'database.sqlite'
_DB_VERSION = 6
_DB_TIMEOUT = 30
def map_row_to_record(row) -> Record:
return Record(
id_=row[0],
name=row[1],
model_type=ModelType.by_value(row[2]),
download_url=row[3],
url=row[4],
download_path=row[5],
download_filename=row[6],
preview_url=row[7],
description=row[8],
positive_prompts=row[9],
negative_prompts=row[10],
sha256_hash=row[11],
md5_hash=row[12],
created_at=row[13],
groups=row[14].split(',') if row[14] else [],
subdir=row[15],
location=row[16],
weight=row[17]
)
class SQLiteStorage(Storage):
def __init__(self):
self.local = threading.local()
self._initialize()
def _connection(self):
if not hasattr(self.local, "connection"):
mo_database_dir = getattr(shared.cmd_opts, "mo_database_dir")
database_dir = mo_database_dir if mo_database_dir is not None else env.script_dir
db_file_path = os.path.join(database_dir, _DB_FILE)
self.local.connection = sqlite3.connect(db_file_path, _DB_TIMEOUT)
return self.local.connection
def _initialize(self):
cursor = self._connection().cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Record
(id INTEGER PRIMARY KEY,
_name TEXT,
model_type TEXT,
download_url TEXT,
url TEXT DEFAULT '',
download_path TEXT DEFAULT '',
download_filename TEXT DEFAULT '',
preview_url TEXT DEFAULT '',
description TEXT DEFAULT '',
positive_prompts TEXT DEFAULT '',
negative_prompts TEXT DEFAULT '',
sha256_hash TEXT DEFAULT '',
md5_hash TEXT DEFAULT '',
created_at INTEGER DEFAULT 0,
groups TEXT DEFAULT '',
subdir TEXT DEFAULT '',
location TEXT DEFAULT '',
weight REAL DEFAULT 1)
''')
cursor.execute(f'''CREATE TABLE IF NOT EXISTS Version
(version INTEGER DEFAULT {_DB_VERSION})''')
self._connection().commit()
self._check_database_version()
def _check_database_version(self):
cursor = self._connection().cursor()
cursor.execute('SELECT * FROM Version ', )
row = cursor.fetchone()
if row is None:
cursor.execute(f'INSERT INTO Version VALUES ({_DB_VERSION})')
self._connection().commit()
version = _DB_VERSION if row is None else row[0]
if version != _DB_VERSION:
self._run_migration(version)
def _run_migration(self, current_version):
for ver in range(current_version, _DB_VERSION):
if ver == 1:
self._migrate_1_to_2()
elif ver == 2:
self._migrate_2_to_3()
elif ver == 3:
self._migrate_3_to_4()
elif ver == 4:
self._migrate_4_to_5()
elif ver == 5:
self._migrage_5_to_6()
else:
raise Exception(f'Missing SQLite migration from {ver} to {_DB_VERSION}')
def _migrate_1_to_2(self):
cursor = self._connection().cursor()
cursor.execute('ALTER TABLE Record ADD COLUMN created_at INTEGER DEFAULT 0;')
cursor.execute("DELETE FROM Version")
cursor.execute('INSERT INTO Version VALUES (2)')
self._connection().commit()
def _migrate_2_to_3(self):
cursor = self._connection().cursor()
cursor.execute("ALTER TABLE Record ADD COLUMN groups TEXT DEFAULT '';")
cursor.execute("DELETE FROM Version")
cursor.execute('INSERT INTO Version VALUES (3)')
self._connection().commit()
def _migrate_3_to_4(self):
cursor = self._connection().cursor()
cursor.execute("ALTER TABLE Record RENAME COLUMN model_hash TO sha256_hash;")
cursor.execute("ALTER TABLE Record ADD COLUMN subdir TEXT DEFAULT '';")
cursor.execute("DELETE FROM Version")
cursor.execute('INSERT INTO Version VALUES (4)')
self._connection().commit()
def _migrate_4_to_5(self):
cursor = self._connection().cursor()
cursor.execute("ALTER TABLE Record ADD COLUMN location TEXT DEFAULT '';")
cursor.execute("DELETE FROM Version")
cursor.execute('INSERT INTO Version VALUES (5)')
self._connection().commit()
def _migrage_5_to_6(self):
cursor = self._connection().cursor()
cursor.execute("ALTER TABLE Record ADD COLUMN weight REAL DEFAULT 1;")
cursor.execute("DELETE FROM Version")
cursor.execute('INSERT INTO Version VALUES (6)')
self._connection().commit()
def get_all_records(self) -> List:
cursor = self._connection().cursor()
cursor.execute('SELECT * FROM Record')
rows = cursor.fetchall()
result = []
for row in rows:
result.append(map_row_to_record(row))
return result
def query_records(self, name_query: str = None, groups=None, model_types=None, show_downloaded=True,
show_not_downloaded=True) -> List:
query = 'SELECT * FROM Record'
is_where_appended = False
append_and = False
if name_query is not None and name_query:
if not is_where_appended:
query += ' WHERE'
is_where_appended = True
query += f" LOWER(_name) LIKE '%{name_query}%'"
append_and = True
if model_types is not None and len(model_types) > 0:
if not is_where_appended:
query += ' WHERE'
is_where_appended = True
if append_and:
query += ' AND'
query += ' ('
append_or = False
for model_type in model_types:
if append_or:
query += ' OR'
query += f" model_type='{model_type}'"
append_or = True
query += ')'
append_and = True
pass
if groups is not None and len(groups) > 0:
if not is_where_appended:
query += ' WHERE'
for group in groups:
if append_and:
query += ' AND'
query += f" LOWER(groups) LIKE '%{group}%'"
append_and = True
logger.debug(f'query: {query}')
cursor = self._connection().cursor()
cursor.execute(query)
rows = cursor.fetchall()
result = []
for row in rows:
record = map_row_to_record(row)
is_downloaded = bool(record.location) and os.path.exists(record.location)
if show_downloaded and is_downloaded:
result.append(record)
elif show_not_downloaded and not is_downloaded:
result.append(record)
return result
def get_record_by_id(self, id_) -> Record:
cursor = self._connection().cursor()
cursor.execute('SELECT * FROM Record WHERE id=?', (id_,))
row = cursor.fetchone()
return None if row is None else map_row_to_record(row)
def get_records_by_group(self, group: str) -> List:
cursor = self._connection().cursor()
cursor.execute(f"SELECT * FROM Record WHERE LOWER(groups) LIKE '%{group}%'")
rows = cursor.fetchall()
result = []
for row in rows:
result.append(map_row_to_record(row))
return result
def get_records_by_query(self, query: str) -> List:
cursor = self._connection().cursor()
cursor.execute(query)
rows = cursor.fetchall()
result = []
for row in rows:
result.append(map_row_to_record(row))
return result
def add_record(self, record: Record):
cursor = self._connection().cursor()
data = (
record.name,
record.model_type.value,
record.download_url,
record.url,
record.download_path,
record.download_filename,
record.preview_url,
record.description,
record.positive_prompts,
record.negative_prompts,
record.sha256_hash,
record.md5_hash,
record.created_at,
",".join(record.groups),
record.subdir,
record.location,
record.weight
)
cursor.execute(
"""INSERT INTO Record(
_name,
model_type,
download_url,
url,
download_path,
download_filename,
preview_url,
description,
positive_prompts,
negative_prompts,
sha256_hash,
md5_hash,
created_at,
groups,
subdir,
location,
weight) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
data)
self._connection().commit()
def update_record(self, record: Record):
cursor = self._connection().cursor()
data = (
record.name,
record.model_type.value,
record.download_url,
record.url,
record.download_path,
record.download_filename,
record.preview_url,
record.description,
record.positive_prompts,
record.negative_prompts,
record.sha256_hash,
record.md5_hash,
",".join(record.groups),
record.subdir,
record.location,
record.weight,
record.id_
)
cursor.execute(
"""UPDATE Record SET
_name=?,
model_type=?,
download_url=?,
url=?,
download_path=?,
download_filename=?,
preview_url=?,
description=?,
positive_prompts=?,
negative_prompts=?,
sha256_hash=?,
md5_hash=?,
groups=?,
subdir=?,
location=?,
weight=?
WHERE id=?
""", data
)
self._connection().commit()
def remove_record(self, _id):
cursor = self._connection().cursor()
cursor.execute("DELETE FROM Record WHERE id=?", (_id,))
self._connection().commit()
def get_available_groups(self) -> List:
cursor = self._connection().cursor()
cursor.execute('SELECT groups FROM Record')
rows = cursor.fetchall()
result = []
for row in rows:
if row[0]:
result.extend(row[0].split(","))
result = list(set(result))
return list(filter(None, result))
def get_all_records_locations(self) -> List:
cursor = self._connection().cursor()
cursor.execute('SELECT location FROM Record')
rows = cursor.fetchall()
result = []
for row in rows:
if row[0]:
result.append(row[0])
return result
|