145 lines
No EOL
5.5 KiB
Markdown
145 lines
No EOL
5.5 KiB
Markdown
# Database Specification
|
|
|
|
## Overview
|
|
|
|
QuickNotes uses SQLite as its database engine, with GORM as the object-relational mapping (ORM) layer. The database stores notes, read later items, feeds, and feed entries.
|
|
|
|
## Database Engine
|
|
|
|
- **SQLite**: A self-contained, serverless, zero-configuration, transactional SQL database engine
|
|
- **File Location**: The database is stored in a file named `notes.db` in the application root directory
|
|
- **Access**: The database is accessed directly by the Go backend using the `glebarez/sqlite` driver for GORM
|
|
|
|
## Schema
|
|
|
|
### Notes Table
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|--------|------|-------------|-------------|
|
|
| id | TEXT | PRIMARY KEY | Unique identifier for the note |
|
|
| title | TEXT | NOT NULL | Title of the note |
|
|
| content | TEXT | NOT NULL | Content of the note in Markdown format |
|
|
| created_at | DATETIME | | When the note was created |
|
|
| updated_at | DATETIME | | When the note was last updated |
|
|
|
|
### Note Links Table
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|--------|------|-------------|-------------|
|
|
| source_note_id | TEXT | PRIMARY KEY, FOREIGN KEY | ID of the source note |
|
|
| target_note_id | TEXT | PRIMARY KEY, FOREIGN KEY | ID of the target note |
|
|
| created_at | DATETIME | | When the link was created |
|
|
|
|
### Read Later Items Table
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|--------|------|-------------|-------------|
|
|
| id | TEXT | PRIMARY KEY | Unique identifier for the item |
|
|
| url | TEXT | NOT NULL | Original URL of the article |
|
|
| title | TEXT | NOT NULL | Title of the article |
|
|
| content | TEXT | | Extracted HTML content of the article |
|
|
| description | TEXT | | Brief description or excerpt of the article |
|
|
| created_at | DATETIME | | When the item was saved |
|
|
| updated_at | DATETIME | | When the item was last updated |
|
|
| read_at | DATETIME | | When the item was marked as read (null if unread) |
|
|
| archived_at | DATETIME | | When the item was archived (null if not archived) |
|
|
|
|
### Feeds Table
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|--------|------|-------------|-------------|
|
|
| id | TEXT | PRIMARY KEY | Unique identifier for the feed |
|
|
| title | TEXT | | Title of the feed |
|
|
| url | TEXT | UNIQUE | URL of the feed (RSS/Atom) |
|
|
| description | TEXT | | Description of the feed |
|
|
| site_url | TEXT | | URL of the website associated with the feed |
|
|
| image_url | TEXT | | URL of the feed's image or logo |
|
|
| last_fetched | DATETIME | | When the feed was last fetched |
|
|
| created_at | DATETIME | | When the feed was added |
|
|
| updated_at | DATETIME | | When the feed was last updated |
|
|
|
|
### Entries Table
|
|
|
|
| Column | Type | Constraints | Description |
|
|
|--------|------|-------------|-------------|
|
|
| id | TEXT | PRIMARY KEY | Unique identifier for the entry |
|
|
| feed_id | TEXT | FOREIGN KEY, INDEX | ID of the parent feed |
|
|
| title | TEXT | | Title of the entry |
|
|
| url | TEXT | UNIQUE | URL of the entry |
|
|
| content | TEXT | | HTML content of the entry |
|
|
| summary | TEXT | | Summary or excerpt of the entry |
|
|
| author | TEXT | | Author of the entry |
|
|
| published | DATETIME | | When the entry was published |
|
|
| updated | DATETIME | | When the entry was last updated |
|
|
| read_at | DATETIME | | When the entry was marked as read (null if unread) |
|
|
| full_content | TEXT | | Full content of the entry (if fetched separately) |
|
|
| created_at | DATETIME | | When the entry was added to the system |
|
|
| updated_at | DATETIME | | When the entry was last updated in the system |
|
|
|
|
## Relationships
|
|
|
|
1. **Notes to Notes (Many-to-Many)**:
|
|
- A note can link to many other notes
|
|
- A note can be linked from many other notes
|
|
- The relationship is managed through the `note_links` table
|
|
|
|
2. **Feeds to Entries (One-to-Many)**:
|
|
- A feed can have many entries
|
|
- An entry belongs to one feed
|
|
- The relationship is managed through the `feed_id` foreign key in the `entries` table
|
|
|
|
## Initialization
|
|
|
|
The database schema is automatically created and migrated when the application starts:
|
|
|
|
```go
|
|
// Initialize database
|
|
db, err := gorm.Open(sqlite.Open(config.DBPath), &gorm.Config{})
|
|
if err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
|
|
// Auto migrate the schema
|
|
if err := db.AutoMigrate(¬es.Note{}, ¬es.NoteLink{}, &readlist.ReadLaterItem{}, &feeds.Feed{}, &feeds.Entry{}); err != nil {
|
|
log.Fatal(err)
|
|
}
|
|
```
|
|
|
|
## Indexes
|
|
|
|
The following indexes are created to optimize query performance:
|
|
|
|
1. **URL Index on Feeds**: Ensures fast lookup of feeds by URL
|
|
2. **Feed ID Index on Entries**: Ensures fast lookup of entries by feed
|
|
3. **URL Index on Entries**: Ensures fast lookup of entries by URL
|
|
|
|
## Data Access
|
|
|
|
Data access is managed through GORM, which provides:
|
|
|
|
1. **Object-Relational Mapping**: Maps Go structs to database tables
|
|
2. **Query Building**: Simplifies building SQL queries
|
|
3. **Transactions**: Ensures data consistency
|
|
4. **Hooks**: Allows for custom logic before and after database operations
|
|
|
|
## Data Integrity
|
|
|
|
The following measures ensure data integrity:
|
|
|
|
1. **Foreign Key Constraints**: Ensure referential integrity between related tables
|
|
2. **Unique Constraints**: Prevent duplicate entries for URLs
|
|
3. **Not Null Constraints**: Ensure required fields are provided
|
|
4. **Transactions**: Used for operations that affect multiple tables
|
|
|
|
## Backup and Recovery
|
|
|
|
The SQLite database file (`notes.db`) can be backed up by:
|
|
|
|
1. Copying the file when the application is not running
|
|
2. Using SQLite's backup API
|
|
3. Exporting data to a SQL dump file
|
|
|
|
Recovery can be performed by:
|
|
|
|
1. Replacing the database file with a backup
|
|
2. Importing data from a SQL dump file |