0
# Models and Database
1
2
SQLAlchemy-based data models and database interface for managing dashboards, charts, datasets, users, and application metadata.
3
4
## Capabilities
5
6
### Core Models
7
8
Essential data models for Superset's primary entities.
9
10
```python { .api }
11
class Dashboard(Model, AuditMixinNullable, ImportExportMixin):
12
"""Dashboard model for organizing charts and layouts."""
13
14
__tablename__ = 'dashboards'
15
16
id: int
17
dashboard_title: str
18
position_json: str # JSON-encoded dashboard layout
19
description: str
20
css: str # Custom CSS styling
21
slug: str # URL-friendly identifier
22
published: bool
23
24
# Relationships
25
slices: List[Slice] # Charts in this dashboard
26
owners: List[User]
27
roles: List[Role]
28
29
def __init__(self, dashboard_title: str = None,
30
position_json: str = None, **kwargs):
31
"""
32
Initialize dashboard.
33
34
Args:
35
dashboard_title: Display name for dashboard
36
position_json: JSON layout configuration
37
"""
38
39
@property
40
def table_names(self) -> Set[str]:
41
"""Get all table names used by charts in this dashboard."""
42
43
@property
44
def datasources(self) -> Set[BaseDatasource]:
45
"""Get all datasources used by charts in this dashboard."""
46
47
def clone(self) -> 'Dashboard':
48
"""Create a copy of this dashboard."""
49
50
class Slice(Model, AuditMixinNullable, ImportExportMixin):
51
"""Chart/visualization model."""
52
53
__tablename__ = 'slices'
54
55
id: int
56
slice_name: str # Chart display name
57
datasource_id: int
58
datasource_type: str # 'table' or 'druid'
59
datasource_name: str
60
viz_type: str # Chart type (table, bar, line, etc.)
61
params: str # JSON-encoded chart configuration
62
description: str
63
cache_timeout: int
64
65
# Relationships
66
datasource: BaseDatasource
67
owners: List[User]
68
dashboards: List[Dashboard]
69
70
def __init__(self, slice_name: str = None, viz_type: str = None,
71
datasource_id: int = None, **kwargs):
72
"""
73
Initialize chart.
74
75
Args:
76
slice_name: Display name for chart
77
viz_type: Visualization type
78
datasource_id: ID of source dataset
79
"""
80
81
@property
82
def form_data(self) -> Dict[str, Any]:
83
"""Get chart configuration as dictionary."""
84
85
def get_query_context(self) -> QueryContext:
86
"""Build query context for data retrieval."""
87
88
class Database(Model, AuditMixinNullable, ImportExportMixin):
89
"""Database connection model."""
90
91
__tablename__ = 'dbs'
92
93
id: int
94
database_name: str
95
sqlalchemy_uri: str # Connection string
96
password: str # Encrypted password
97
cache_timeout: int
98
extra: str # JSON extra configuration
99
expose_in_sqllab: bool
100
allow_run_async: bool
101
allow_csv_upload: bool
102
allow_ctas: bool
103
allow_cvas: bool
104
allow_dml: bool
105
106
def __init__(self, database_name: str = None,
107
sqlalchemy_uri: str = None, **kwargs):
108
"""
109
Initialize database connection.
110
111
Args:
112
database_name: Display name for connection
113
sqlalchemy_uri: SQLAlchemy connection URI
114
"""
115
116
@property
117
def db_engine_spec(self) -> Type[BaseEngineSpec]:
118
"""Get database engine specification."""
119
120
def get_sqla_engine(self, schema: str = None) -> Engine:
121
"""Get SQLAlchemy engine for this database."""
122
123
def test_connection(self) -> Tuple[bool, str]:
124
"""Test database connectivity."""
125
```
126
127
**Usage Examples:**
128
129
```python
130
# Create dashboard
131
dashboard = Dashboard(
132
dashboard_title="Sales Dashboard",
133
position_json='{"CHART-1": {"x": 0, "y": 0, "w": 6, "h": 4}}'
134
)
135
db.session.add(dashboard)
136
db.session.commit()
137
138
# Create chart
139
chart = Slice(
140
slice_name="Sales by Region",
141
viz_type="bar",
142
datasource_id=1,
143
params='{"metrics": ["sales"], "groupby": ["region"]}'
144
)
145
db.session.add(chart)
146
db.session.commit()
147
```
148
149
### Dataset Models
150
151
Models for managing data sources and table metadata.
152
153
```python { .api }
154
class SqlaTable(Model, BaseDatasource):
155
"""SQL table dataset model."""
156
157
__tablename__ = 'tables'
158
159
id: int
160
table_name: str
161
main_dttm_col: str # Primary datetime column
162
default_endpoint: str
163
database_id: int
164
fetch_values_predicate: str
165
is_sqllab_view: bool
166
template_params: str # JSON template parameters
167
schema: str
168
sql: str # Custom SQL for virtual datasets
169
170
# Relationships
171
database: Database
172
columns: List[TableColumn]
173
metrics: List[SqlMetric]
174
owners: List[User]
175
176
def __init__(self, table_name: str = None, database: Database = None,
177
schema: str = None, **kwargs):
178
"""
179
Initialize SQL table dataset.
180
181
Args:
182
table_name: Name of database table
183
database: Database connection
184
schema: Schema name (optional)
185
"""
186
187
@property
188
def datasource_name(self) -> str:
189
"""Get display name for datasource."""
190
191
def get_query_str(self, query_obj: QueryObject) -> str:
192
"""Generate SQL query from query object."""
193
194
def get_df(self, query_obj: QueryObject) -> pd.DataFrame:
195
"""Execute query and return DataFrame."""
196
197
class TableColumn(Model, BaseColumn):
198
"""Table column metadata model."""
199
200
__tablename__ = 'table_columns'
201
202
id: int
203
column_name: str
204
type: str # Data type
205
groupby: bool # Can be used for grouping
206
filterable: bool # Can be used for filtering
207
expression: str # SQL expression for computed columns
208
description: str
209
table_id: int
210
211
# Relationships
212
table: SqlaTable
213
214
def __init__(self, column_name: str = None, type: str = None,
215
table: SqlaTable = None, **kwargs):
216
"""
217
Initialize table column.
218
219
Args:
220
column_name: Database column name
221
type: Data type
222
table: Parent table
223
"""
224
225
class SqlMetric(Model, BaseMetric):
226
"""SQL metric definition model."""
227
228
__tablename__ = 'sql_metrics'
229
230
id: int
231
metric_name: str
232
expression: str # SQL expression
233
metric_type: str
234
description: str
235
table_id: int
236
d3format: str # D3 number formatting
237
238
# Relationships
239
table: SqlaTable
240
241
def __init__(self, metric_name: str = None, expression: str = None,
242
table: SqlaTable = None, **kwargs):
243
"""
244
Initialize SQL metric.
245
246
Args:
247
metric_name: Display name for metric
248
expression: SQL expression to calculate metric
249
table: Parent table
250
"""
251
```
252
253
### User and Security Models
254
255
Models for user management, roles, and permissions.
256
257
```python { .api }
258
class User(Model):
259
"""User model for authentication and authorization."""
260
261
__tablename__ = 'ab_user'
262
263
id: int
264
first_name: str
265
last_name: str
266
username: str
267
password: str # Hashed password
268
active: bool
269
email: str
270
last_login: datetime
271
login_count: int
272
fail_login_count: int
273
274
# Relationships
275
roles: List[Role]
276
277
def __init__(self, first_name: str = None, last_name: str = None,
278
username: str = None, email: str = None, **kwargs):
279
"""
280
Initialize user.
281
282
Args:
283
first_name: User's first name
284
last_name: User's last name
285
username: Login username
286
email: Email address
287
"""
288
289
@property
290
def full_name(self) -> str:
291
"""Get user's full name."""
292
293
class Role(Model):
294
"""Role model for grouping permissions."""
295
296
__tablename__ = 'ab_role'
297
298
id: int
299
name: str
300
301
# Relationships
302
permissions: List[Permission]
303
users: List[User]
304
305
def __init__(self, name: str = None, **kwargs):
306
"""
307
Initialize role.
308
309
Args:
310
name: Role name
311
"""
312
313
class Permission(Model):
314
"""Permission model for access control."""
315
316
__tablename__ = 'ab_permission'
317
318
id: int
319
name: str
320
321
# Relationships
322
view_menu: ViewMenu
323
roles: List[Role]
324
325
def __init__(self, name: str = None, **kwargs):
326
"""
327
Initialize permission.
328
329
Args:
330
name: Permission name
331
"""
332
```
333
334
### Query and Execution Models
335
336
Models for SQL Lab and query execution tracking.
337
338
```python { .api }
339
class Query(Model):
340
"""SQL query execution model."""
341
342
__tablename__ = 'query'
343
344
id: int
345
client_id: str
346
database_id: int
347
tmp_table_name: str
348
tab_name: str
349
sql_editor_id: str
350
user_id: int
351
status: str # running, success, failed, etc.
352
schema: str
353
sql: str # SQL query text
354
select_sql: str # Processed SELECT statement
355
executed_sql: str # Actually executed SQL
356
limit: int
357
select_as_cta: bool
358
select_as_cta_used: bool
359
progress: int # Query progress percentage
360
rows: int # Number of rows returned
361
error_message: str
362
start_time: datetime
363
end_time: datetime
364
365
# Relationships
366
database: Database
367
user: User
368
369
def __init__(self, database_id: int = None, sql: str = None,
370
user_id: int = None, **kwargs):
371
"""
372
Initialize query.
373
374
Args:
375
database_id: Target database ID
376
sql: SQL query to execute
377
user_id: Executing user ID
378
"""
379
380
@property
381
def duration(self) -> timedelta:
382
"""Get query execution duration."""
383
384
class SavedQuery(Model, AuditMixinNullable):
385
"""Saved SQL query model."""
386
387
__tablename__ = 'saved_query'
388
389
id: int
390
user_id: int
391
db_id: int
392
label: str # Query name
393
schema: str
394
sql: str
395
description: str
396
397
# Relationships
398
database: Database
399
user: User
400
401
def __init__(self, user_id: int = None, db_id: int = None,
402
label: str = None, sql: str = None, **kwargs):
403
"""
404
Initialize saved query.
405
406
Args:
407
user_id: Query owner ID
408
db_id: Target database ID
409
label: Display name
410
sql: SQL query text
411
"""
412
```
413
414
### Utility and Metadata Models
415
416
Supporting models for application metadata and configuration.
417
418
```python { .api }
419
class KeyValue(Model):
420
"""Key-value store for application metadata."""
421
422
__tablename__ = 'keyvalue'
423
424
id: int
425
value: str # JSON-encoded value
426
427
def __init__(self, value: str = None, **kwargs):
428
"""
429
Initialize key-value pair.
430
431
Args:
432
value: JSON-encoded value
433
"""
434
435
class CssTemplate(Model, AuditMixinNullable):
436
"""CSS template for dashboard styling."""
437
438
__tablename__ = 'css_templates'
439
440
id: int
441
template_name: str
442
css: str # CSS content
443
444
def __init__(self, template_name: str = None, css: str = None, **kwargs):
445
"""
446
Initialize CSS template.
447
448
Args:
449
template_name: Template display name
450
css: CSS content
451
"""
452
453
class Log(Model):
454
"""Activity logging model."""
455
456
__tablename__ = 'logs'
457
458
id: int
459
action: str
460
user_id: int
461
json: str # JSON-encoded log data
462
dttm: datetime
463
dashboard_id: int
464
slice_id: int
465
466
# Relationships
467
user: User
468
dashboard: Dashboard
469
slice: Slice
470
471
def __init__(self, action: str = None, user_id: int = None,
472
json: str = None, **kwargs):
473
"""
474
Initialize log entry.
475
476
Args:
477
action: Action type
478
user_id: User performing action
479
json: Additional log data
480
"""
481
```
482
483
## Database Operations
484
485
### Session Management
486
487
```python { .api }
488
from superset import db
489
490
# Database session operations
491
def create_record(model_instance: Model) -> None:
492
"""Add new record to database."""
493
db.session.add(model_instance)
494
db.session.commit()
495
496
def update_record(model_instance: Model) -> None:
497
"""Update existing record."""
498
db.session.merge(model_instance)
499
db.session.commit()
500
501
def delete_record(model_instance: Model) -> None:
502
"""Delete record from database."""
503
db.session.delete(model_instance)
504
db.session.commit()
505
506
# Query operations
507
def get_by_id(model_class: Type[Model], id: int) -> Model:
508
"""Get record by primary key."""
509
return db.session.query(model_class).get(id)
510
511
def get_all(model_class: Type[Model]) -> List[Model]:
512
"""Get all records of a model type."""
513
return db.session.query(model_class).all()
514
```
515
516
### Model Mixins
517
518
```python { .api }
519
class AuditMixinNullable:
520
"""Mixin for audit fields that can be null."""
521
522
created_on: datetime
523
changed_on: datetime
524
created_by_fk: int
525
changed_by_fk: int
526
527
# Relationships
528
created_by: User
529
changed_by: User
530
531
class ImportExportMixin:
532
"""Mixin for import/export functionality."""
533
534
export_parent: str = None
535
export_children: List[str] = []
536
537
def export_schema(self) -> Dict[str, Any]:
538
"""Export model as dictionary."""
539
540
@classmethod
541
def import_from_dict(cls, session: Session, dict_rep: Dict[str, Any],
542
recursive: bool = True) -> 'ImportExportMixin':
543
"""Import model from dictionary."""
544
```