mattercontrol/research/08-data-storage.md

# Data Storage & Persistence

## Summary

MatterControl uses SQLite for persistent data storage with a simple ORM pattern. The `Datastore` singleton manages database connections, schema validation, and session tracking. Entity classes provide CRUD operations through the `ISQLite` interface.

## Technical Description

### Architecture

```
Datastore (singleton)
├── ISQLite interface (database operations)
├── Entity tables
│   ├── PrintItemCollection
│   ├── PrintItem
│   ├── PrintTask
│   ├── Printer
│   ├── PrinterSetting
│   ├── CustomCommands
│   ├── SliceSetting
│   ├── SliceSettingsCollection
│   ├── SystemSetting
│   ├── UserSetting
│   └── ApplicationSession
└── ApplicationDataStorage (paths)
```

### Datastore Class

The `Datastore` singleton manages database lifecycle:

```csharp
public class Datastore
{
    public ISQLite dbSQLite;
    private string datastoreLocation;
    private ApplicationSession activeSession;

    public void Initialize(ISQLite dbSQLite);
    public void Exit();
    public int RecordCount(string tableName);
}
```

**Initialization:**
1. Check if database file exists (first run detection)
2. Connect to SQLite database
3. Validate/create schema tables
4. Initialize root library collection
5. Start application session

### Entity Base Class

All database models inherit from `Entity`:

```csharp
public class Entity
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    public virtual void Commit();  // Insert or Update
    public void Delete();
}
```

**Commit Logic:**
- `Id == 0`: Insert new record
- `Id != 0`: Update existing record
- Automatic retry on database lock

### Database Tables

| Table | Purpose | Key Fields |
|-------|---------|------------|
| `PrintItemCollection` | Library folders | Name, ParentCollectionId |
| `PrintItem` | Library items | Name, FileLocation, PrintItemCollectionId |
| `PrintTask` | Print job history | PrintName, PrintStart, PrintEnd, PercentDone |
| `Printer` | Printer definitions | Name (deprecated - use profiles) |
| `PrinterSetting` | Printer settings | Name, Value, PrinterId |
| `CustomCommands` | User macros | Name, Value, PrinterId |
| `SliceSetting` | Slice presets | Name, Value |
| `SliceSettingsCollection` | Setting groups | Name, Tag |
| `SystemSetting` | System config | Name, Value |
| `UserSetting` | User preferences | Name, Value |
| `ApplicationSession` | Session tracking | SessionStart, SessionEnd, PrintCount |

### ApplicationDataStorage

Manages file system paths:

| Path | Purpose |
|------|---------|
| `ApplicationDataPath` | Root data directory |
| `DatastorePath` | SQLite database file |
| `GCodeOutputPath` | Generated G-code files |
| `LibraryAssetsPath` | Library asset files |
| `ApplicationLibraryDataPath` | Library data |
| `ApplicationFontsDataPath` | Custom fonts |
| `CustomLibraryFoldersPath` | User library paths |
| `ProfilesPath` | Printer profiles |

**Default Location:**
- Windows: `%LOCALAPPDATA%\MatterControl`
- macOS: `~/Library/Application Support/MatterControl`
- Linux: `~/.local/share/MatterControl`

### Common Entity Models

**PrintItem:**
```csharp
public class PrintItem : Entity
{
    public string Name { get; set; }
    public string FileLocation { get; set; }
    public DateTime DateAdded { get; set; }
    [Indexed]
    public int PrintItemCollectionId { get; set; }
}
```

**PrintTask:**
```csharp
public class PrintTask : Entity
{
    public string PrintName { get; set; }
    public DateTime PrintStart { get; set; }
    public DateTime PrintEnd { get; set; }
    public int PrintTimeSeconds { get; set; }
    public double PercentDone { get; set; }
    public bool PrintComplete { get; set; }
    public string PrinterName { get; set; }
}
```

**UserSetting:**
```csharp
public class UserSetting : Entity
{
    [Indexed]
    public string Name { get; set; }
    public string Value { get; set; }
}
```

### ISQLite Interface

Database operations abstracted for testability:

| Method | Description |
|--------|-------------|
| `CreateTable<T>()` | Create table from type |
| `Insert(object)` | Insert record |
| `Update(object)` | Update record |
| `Delete(object)` | Delete record |
| `Table<T>()` | Query table |
| `ExecuteScalar<T>(sql)` | Execute scalar query |
| `Close()` | Close connection |

### Session Tracking

`ApplicationSession` records usage:
```csharp
public class ApplicationSession : Entity
{
    public DateTime SessionStart { get; set; }
    public DateTime SessionEnd { get; set; }
    public int PrintCount { get; set; }
}
```

### Design Rationale

**SQLite**: Chosen for:
- Zero configuration deployment
- Single-file database
- Cross-platform support
- ACID compliance

**Entity Pattern**: Simple ORM provides:
- Minimal boilerplate
- Type-safe queries
- Automatic schema management

**Singleton Datastore**: Ensures:
- Single database connection
- Coordinated access
- Clean shutdown

## Reference

### Core Classes

| Class | Location | Description |
|-------|----------|-------------|
| `Datastore` | [DataStorage/Datastore.cs](MatterControlLib/DataStorage/Datastore.cs) | Database manager |
| `Entity` | [DataStorage/Models.cs](MatterControlLib/DataStorage/Models.cs) | Base entity class |
| `ApplicationDataStorage` | [DataStorage/ApplicationDataStorage.cs](MatterControlLib/DataStorage/ApplicationDataStorage.cs) | Path management |

### Entity Models

| Class | Location | Description |
|-------|----------|-------------|
| `PrintItem` | [DataStorage/Models.cs](MatterControlLib/DataStorage/Models.cs) | Library item |
| `PrintItemCollection` | [DataStorage/Models.cs](MatterControlLib/DataStorage/Models.cs) | Library folder |
| `PrintTask` | [DataStorage/Models.cs](MatterControlLib/DataStorage/Models.cs) | Print job record |
| `ApplicationSession` | [DataStorage/Models.cs](MatterControlLib/DataStorage/Models.cs) | Session data |
| `UserSetting` | [DataStorage/Models.cs](MatterControlLib/DataStorage/Models.cs) | User preference |

### Database Attributes

| Attribute | Purpose |
|-----------|---------|
| `[PrimaryKey]` | Primary key column |
| `[AutoIncrement]` | Auto-increment integer |
| `[Indexed]` | Create index on column |