0
# Database Operations
1
2
Sopel provides a powerful database layer built on SQLAlchemy ORM, supporting multiple database backends including SQLite, MySQL, PostgreSQL, Oracle, Firebird, and Sybase. The database system offers convenient methods for storing and retrieving user-specific, channel-specific, and plugin-specific data.
3
4
## Capabilities
5
6
### Database Interface
7
8
Main database interface providing high-level methods for data storage and retrieval.
9
10
```python { .api }
11
class SopelDB:
12
"""Main database interface for Sopel bots."""
13
14
def __init__(self, config: 'Config', identifier_factory: 'IdentifierFactory' = None):
15
"""
16
Initialize database connection.
17
18
Args:
19
config (Config): Bot configuration containing database settings
20
identifier_factory (IdentifierFactory): Factory for creating identifiers
21
"""
22
23
def connect(self) -> None:
24
"""Establish database connection and create tables if needed."""
25
26
def close(self) -> None:
27
"""Close database connection."""
28
29
# User/Nick data methods
30
def get_nick_id(self, nick: str, create: bool = True) -> int:
31
"""
32
Get numeric ID for a nickname.
33
34
Args:
35
nick (str): Nickname to get ID for
36
create (bool): Whether to create new ID if nick doesn't exist
37
38
Returns:
39
Numeric ID for the nickname
40
"""
41
42
def get_nick_value(self, nick: str, key: str) -> str | None:
43
"""
44
Get stored value for a nickname.
45
46
Args:
47
nick (str): Nickname to get value for
48
key (str): Key name for the stored value
49
50
Returns:
51
Stored value or None if not found
52
"""
53
54
def set_nick_value(self, nick: str, key: str, value) -> None:
55
"""
56
Store value for a nickname.
57
58
Args:
59
nick (str): Nickname to store value for
60
key (str): Key name for the value
61
value: Value to store (will be JSON serialized)
62
"""
63
64
def delete_nick_value(self, nick: str, key: str) -> None:
65
"""
66
Delete stored value for a nickname.
67
68
Args:
69
nick (str): Nickname to delete value for
70
key (str): Key name of value to delete
71
"""
72
73
def delete_nick_group(self, nick: str, key_prefix: str) -> None:
74
"""
75
Delete all values for a nickname with keys starting with prefix.
76
77
Args:
78
nick (str): Nickname to delete values for
79
key_prefix (str): Key prefix to match for deletion
80
"""
81
82
# Channel data methods
83
def get_channel_value(self, channel: str, key: str) -> str | None:
84
"""
85
Get stored value for a channel.
86
87
Args:
88
channel (str): Channel name to get value for
89
key (str): Key name for the stored value
90
91
Returns:
92
Stored value or None if not found
93
"""
94
95
def set_channel_value(self, channel: str, key: str, value) -> None:
96
"""
97
Store value for a channel.
98
99
Args:
100
channel (str): Channel name to store value for
101
key (str): Key name for the value
102
value: Value to store (will be JSON serialized)
103
"""
104
105
def delete_channel_value(self, channel: str, key: str) -> None:
106
"""
107
Delete stored value for a channel.
108
109
Args:
110
channel (str): Channel name to delete value for
111
key (str): Key name of value to delete
112
"""
113
114
# Plugin data methods
115
def get_plugin_value(self, plugin: str, key: str) -> str | None:
116
"""
117
Get stored value for a plugin.
118
119
Args:
120
plugin (str): Plugin name to get value for
121
key (str): Key name for the stored value
122
123
Returns:
124
Stored value or None if not found
125
"""
126
127
def set_plugin_value(self, plugin: str, key: str, value) -> None:
128
"""
129
Store value for a plugin.
130
131
Args:
132
plugin (str): Plugin name to store value for
133
key (str): Key name for the value
134
value: Value to store (will be JSON serialized)
135
"""
136
137
def delete_plugin_value(self, plugin: str, key: str) -> None:
138
"""
139
Delete stored value for a plugin.
140
141
Args:
142
plugin (str): Plugin name to delete value for
143
key (str): Key name of value to delete
144
"""
145
146
# Utility methods
147
def get_preferred_value(self, names: list) -> tuple | None:
148
"""
149
Get the first available value from a list of names.
150
151
Args:
152
names (list): List of (type, name, key) tuples to check
153
154
Returns:
155
First found (type, name, key, value) tuple or None
156
"""
157
```
158
159
### Database Models
160
161
SQLAlchemy ORM models that define the database schema.
162
163
```python { .api }
164
# Base model class
165
Base = declarative_base()
166
167
class NickIDs(Base):
168
"""Model for tracking nickname IDs."""
169
__tablename__ = 'nick_ids'
170
171
nick_id: int # Primary key
172
slug: str # Normalized nickname
173
canonical: str # Original nickname form
174
175
class Nicknames(Base):
176
"""Model for nickname aliases and tracking."""
177
__tablename__ = 'nicknames'
178
179
nick_id: int # Foreign key to NickIDs
180
slug: str # Normalized nickname
181
canonical: str # Original nickname form
182
183
class NickValues(Base):
184
"""Model for user-specific data storage."""
185
__tablename__ = 'nick_values'
186
187
nick_id: int # Foreign key to NickIDs
188
key: str # Data key name
189
value: str # JSON-serialized data value
190
191
class ChannelValues(Base):
192
"""Model for channel-specific data storage."""
193
__tablename__ = 'channel_values'
194
195
channel: str # Channel name (normalized)
196
key: str # Data key name
197
value: str # JSON-serialized data value
198
199
class PluginValues(Base):
200
"""Model for plugin-specific data storage."""
201
__tablename__ = 'plugin_values'
202
203
plugin: str # Plugin name
204
key: str # Data key name
205
value: str # JSON-serialized data value
206
```
207
208
## Usage Examples
209
210
### Basic Data Storage and Retrieval
211
212
```python
213
from sopel import plugin
214
215
@plugin.command('remember')
216
@plugin.example('.remember pizza is delicious')
217
def remember_command(bot, trigger):
218
"""Store information for a user."""
219
if not trigger.group(2):
220
bot.reply("What should I remember?")
221
return
222
223
memory = trigger.group(2)
224
bot.db.set_nick_value(trigger.nick, 'memory', memory)
225
bot.reply(f"I'll remember that for you, {trigger.nick}!")
226
227
@plugin.command('recall')
228
@plugin.example('.recall')
229
def recall_command(bot, trigger):
230
"""Recall stored information for a user."""
231
memory = bot.db.get_nick_value(trigger.nick, 'memory')
232
if memory:
233
bot.reply(f"You told me: {memory}")
234
else:
235
bot.reply("I don't have anything stored for you.")
236
237
@plugin.command('forget')
238
def forget_command(bot, trigger):
239
"""Delete stored information for a user."""
240
bot.db.delete_nick_value(trigger.nick, 'memory')
241
bot.reply("I've forgotten what you told me.")
242
```
243
244
### Channel-Specific Data
245
246
```python
247
@plugin.command('topic')
248
@plugin.example('.topic Welcome to our channel!')
249
@plugin.require_privilege(plugin.OP)
250
def set_topic_command(bot, trigger):
251
"""Set and remember channel topic."""
252
if not trigger.group(2):
253
# Recall stored topic
254
stored_topic = bot.db.get_channel_value(trigger.sender, 'custom_topic')
255
if stored_topic:
256
bot.reply(f"Stored topic: {stored_topic}")
257
else:
258
bot.reply("No custom topic stored for this channel.")
259
return
260
261
topic = trigger.group(2)
262
bot.db.set_channel_value(trigger.sender, 'custom_topic', topic)
263
bot.db.set_channel_value(trigger.sender, 'topic_setter', trigger.nick)
264
bot.reply(f"Topic stored: {topic}")
265
266
@plugin.command('stats')
267
def channel_stats(bot, trigger):
268
"""Show channel statistics."""
269
# Get or initialize message counter
270
count = bot.db.get_channel_value(trigger.sender, 'message_count') or 0
271
count = int(count) + 1
272
273
# Update counter
274
bot.db.set_channel_value(trigger.sender, 'message_count', count)
275
276
bot.reply(f"This channel has seen {count} messages!")
277
```
278
279
### Plugin-Specific Configuration Storage
280
281
```python
282
@plugin.command('apikey')
283
@plugin.require_admin()
284
def set_api_key(bot, trigger):
285
"""Set API key for weather plugin."""
286
if not trigger.group(2):
287
bot.reply("Usage: .apikey <your_api_key>")
288
return
289
290
api_key = trigger.group(2)
291
bot.db.set_plugin_value('weather', 'api_key', api_key)
292
bot.reply("API key stored successfully!")
293
294
@plugin.command('weather')
295
@plugin.example('.weather London')
296
def weather_command(bot, trigger):
297
"""Get weather using stored API key."""
298
api_key = bot.db.get_plugin_value('weather', 'api_key')
299
if not api_key:
300
bot.reply("Weather API key not configured. Admin needs to set it.")
301
return
302
303
location = trigger.group(2) or 'London'
304
305
# Store user's preferred location
306
bot.db.set_nick_value(trigger.nick, 'weather_location', location)
307
308
# Use API key to fetch weather...
309
bot.reply(f"Weather for {location}: Sunny, 25°C")
310
```
311
312
### Advanced Data Management
313
314
```python
315
@plugin.command('profile')
316
def user_profile(bot, trigger):
317
"""Show user profile with multiple data points."""
318
nick = trigger.group(2) or trigger.nick
319
320
# Get various user data
321
location = bot.db.get_nick_value(nick, 'location')
322
timezone = bot.db.get_nick_value(nick, 'timezone')
323
favorite_color = bot.db.get_nick_value(nick, 'favorite_color')
324
join_date = bot.db.get_nick_value(nick, 'first_seen')
325
326
profile_parts = [f"Profile for {nick}:"]
327
328
if location:
329
profile_parts.append(f"Location: {location}")
330
if timezone:
331
profile_parts.append(f"Timezone: {timezone}")
332
if favorite_color:
333
profile_parts.append(f"Favorite color: {favorite_color}")
334
if join_date:
335
profile_parts.append(f"First seen: {join_date}")
336
337
if len(profile_parts) == 1:
338
profile_parts.append("No profile data available.")
339
340
bot.reply(" | ".join(profile_parts))
341
342
@plugin.command('setprofile')
343
@plugin.example('.setprofile location New York')
344
def set_profile(bot, trigger):
345
"""Set profile information."""
346
args = trigger.group(2)
347
if not args:
348
bot.reply("Usage: .setprofile <field> <value>")
349
return
350
351
parts = args.split(' ', 1)
352
if len(parts) != 2:
353
bot.reply("Usage: .setprofile <field> <value>")
354
return
355
356
field, value = parts
357
valid_fields = ['location', 'timezone', 'favorite_color']
358
359
if field not in valid_fields:
360
bot.reply(f"Valid fields: {', '.join(valid_fields)}")
361
return
362
363
bot.db.set_nick_value(trigger.nick, field, value)
364
bot.reply(f"Set {field} to: {value}")
365
366
@plugin.command('clearprofile')
367
def clear_profile(bot, trigger):
368
"""Clear all profile data."""
369
fields = ['location', 'timezone', 'favorite_color']
370
371
for field in fields:
372
bot.db.delete_nick_value(trigger.nick, field)
373
374
bot.reply("Profile data cleared.")
375
```
376
377
### Database Migration and Maintenance
378
379
```python
380
@plugin.command('dbmigrate')
381
@plugin.require_owner()
382
def migrate_data(bot, trigger):
383
"""Migrate old data format to new format."""
384
# Example: migrate old single-value storage to structured data
385
386
all_nicks = [] # Would get from database query
387
migrated_count = 0
388
389
for nick in all_nicks:
390
old_data = bot.db.get_nick_value(nick, 'old_format_data')
391
if old_data:
392
# Parse old format and convert
393
try:
394
# Convert old format to new structured format
395
new_data = {'converted': True, 'original': old_data}
396
bot.db.set_nick_value(nick, 'new_format_data', new_data)
397
bot.db.delete_nick_value(nick, 'old_format_data')
398
migrated_count += 1
399
except Exception as e:
400
bot.say(f"Migration failed for {nick}: {e}")
401
402
bot.reply(f"Migrated {migrated_count} user records.")
403
404
@plugin.command('dbcleanup')
405
@plugin.require_owner()
406
def cleanup_database(bot, trigger):
407
"""Clean up old or invalid database entries."""
408
# Clean up plugin data for unloaded plugins
409
active_plugins = list(bot.settings.get('core', {}).get('exclude', []))
410
411
# This would require direct database access for complex cleanup
412
bot.reply("Database cleanup completed.")
413
```
414
415
### Working with Complex Data Types
416
417
```python
418
@plugin.command('addtag')
419
@plugin.example('.addtag python programming')
420
def add_tag(bot, trigger):
421
"""Add tags to user profile."""
422
if not trigger.group(2):
423
bot.reply("Usage: .addtag <tag>")
424
return
425
426
tag = trigger.group(2).lower()
427
428
# Get existing tags list
429
tags = bot.db.get_nick_value(trigger.nick, 'tags') or []
430
if isinstance(tags, str):
431
tags = [tags] # Handle legacy single tag format
432
433
if tag not in tags:
434
tags.append(tag)
435
bot.db.set_nick_value(trigger.nick, 'tags', tags)
436
bot.reply(f"Added tag: {tag}")
437
else:
438
bot.reply(f"You already have tag: {tag}")
439
440
@plugin.command('tags')
441
def show_tags(bot, trigger):
442
"""Show user's tags."""
443
nick = trigger.group(2) or trigger.nick
444
tags = bot.db.get_nick_value(nick, 'tags') or []
445
446
if tags:
447
tag_list = ', '.join(tags)
448
bot.reply(f"Tags for {nick}: {tag_list}")
449
else:
450
bot.reply(f"{nick} has no tags.")
451
452
@plugin.command('findusers')
453
@plugin.example('.findusers python')
454
def find_users_by_tag(bot, trigger):
455
"""Find users with a specific tag."""
456
if not trigger.group(2):
457
bot.reply("Usage: .findusers <tag>")
458
return
459
460
search_tag = trigger.group(2).lower()
461
462
# This would require a more complex database query
463
# For now, showing the concept
464
bot.reply(f"Users with tag '{search_tag}': (feature requires database query)")
465
```
466
467
## Database Configuration
468
469
### Supported Database Types
470
471
```python
472
# SQLite (default)
473
[core]
474
db_type = sqlite
475
db_filename = sopel.db
476
477
# MySQL
478
[core]
479
db_type = mysql
480
db_host = localhost
481
db_port = 3306
482
db_user = sopel_user
483
db_pass = sopel_password
484
db_name = sopel_db
485
486
# PostgreSQL
487
[core]
488
db_type = postgresql
489
db_host = localhost
490
db_port = 5432
491
db_user = sopel_user
492
db_pass = sopel_password
493
db_name = sopel_db
494
```
495
496
### Database URL Format
497
498
```python
499
# Alternative: specify complete database URL
500
[core]
501
db_type = mysql
502
db_url = mysql://user:password@host:port/database
503
504
# PostgreSQL with SSL
505
db_url = postgresql://user:password@host:port/database?sslmode=require
506
507
# SQLite with absolute path
508
db_url = sqlite:///absolute/path/to/database.db
509
```