database

package
v0.0.0-...-66940dd Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 16, 2025 License: MIT Imports: 9 Imported by: 0

README

Storage Documentation

This directory contains storage implementations for the web scraper.

Storage Interface

All storage implementations follow a common interface for easy swapping:

type Storage interface {
    Store(content *ScrapedContent) error
    GetByURL(url string) (*ScrapedContent, error)
    GetByID(id string) (*ScrapedContent, error)
    List(offset, limit int) ([]*ScrapedContent, error)
    Count() (int64, error)
    Close() error
}

SQLite Storage (sqlite_storage.go)

Default storage implementation using SQLite for development and small deployments.

Features
  • Zero Configuration: No external database required
  • File-based: Single file database for easy backup/restore
  • ACID Compliance: Full transaction support
  • Embedded: No separate database server needed
Database Schema
CREATE TABLE scraped_content (
    id TEXT PRIMARY KEY,
    url TEXT UNIQUE NOT NULL,
    title TEXT,
    content TEXT,
    links TEXT,        -- JSON array of links
    images TEXT,       -- JSON array of image URLs
    scraped_at INTEGER,
    created_at INTEGER,
    updated_at INTEGER
);

CREATE INDEX idx_scraped_content_url ON scraped_content(url);
CREATE INDEX idx_scraped_content_scraped_at ON scraped_content(scraped_at);
Configuration
storage:
  type: "sqlite"
  path: "./data/scraper.db"
Usage
storage, err := database.NewSQLiteStorage("./data/scraper.db")
if err != nil {
    log.Fatal("Failed to initialize storage:", err)
}
defer storage.Close()

// Store content
content := &database.ScrapedContent{
    ID:        uuid.New().String(),
    URL:       "https://example.com",
    Title:     "Example Page",
    Content:   "Page content...",
    Links:     []string{"https://example.com/page1"},
    Images:    []string{"https://example.com/image.jpg"},
    ScrapedAt: time.Now(),
}

err = storage.Store(content)

Data Models

ScrapedContent
type ScrapedContent struct {
    ID        string    `json:"id" db:"id"`
    URL       string    `json:"url" db:"url"`
    Title     string    `json:"title" db:"title"`
    Content   string    `json:"content" db:"content"`
    Links     []string  `json:"links" db:"links"`
    Images    []string  `json:"images" db:"images"`
    ScrapedAt time.Time `json:"scraped_at" db:"scraped_at"`
    CreatedAt time.Time `json:"created_at" db:"created_at"`
    UpdatedAt time.Time `json:"updated_at" db:"updated_at"`
}

Extending Storage

PostgreSQL Implementation Example
type PostgreSQLStorage struct {
    db *sql.DB
}

func NewPostgreSQLStorage(connStr string) (*PostgreSQLStorage, error) {
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        return nil, err
    }
    
    storage := &PostgreSQLStorage{db: db}
    if err := storage.migrate(); err != nil {
        return nil, err
    }
    
    return storage, nil
}

func (s *PostgreSQLStorage) Store(content *ScrapedContent) error {
    query := `
        INSERT INTO scraped_content (id, url, title, content, links, images, scraped_at, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        ON CONFLICT (url) DO UPDATE SET
            title = EXCLUDED.title,
            content = EXCLUDED.content,
            links = EXCLUDED.links,
            images = EXCLUDED.images,
            updated_at = EXCLUDED.updated_at
    `
    
    _, err := s.db.Exec(query, 
        content.ID, content.URL, content.Title, content.Content,
        pq.Array(content.Links), pq.Array(content.Images),
        content.ScrapedAt, content.CreatedAt, content.UpdatedAt)
    
    return err
}
MongoDB Implementation Example
type MongoStorage struct {
    client     *mongo.Client
    database   *mongo.Database
    collection *mongo.Collection
}

func NewMongoStorage(uri, dbName string) (*MongoStorage, error) {
    client, err := mongo.Connect(context.TODO(), options.Client().ApplyURI(uri))
    if err != nil {
        return nil, err
    }
    
    db := client.Database(dbName)
    collection := db.Collection("scraped_content")
    
    // Create indexes
    indexModel := mongo.IndexModel{
        Keys: bson.D{{"url", 1}},
        Options: options.Index().SetUnique(true),
    }
    _, err = collection.Indexes().CreateOne(context.TODO(), indexModel)
    if err != nil {
        return nil, err
    }
    
    return &MongoStorage{
        client:     client,
        database:   db,
        collection: collection,
    }, nil
}

func (s *MongoStorage) Store(content *ScrapedContent) error {
    filter := bson.M{"url": content.URL}
    update := bson.M{
        "$set": bson.M{
            "title":      content.Title,
            "content":    content.Content,
            "links":      content.Links,
            "images":     content.Images,
            "scraped_at": content.ScrapedAt,
            "updated_at": time.Now(),
        },
        "$setOnInsert": bson.M{
            "id":         content.ID,
            "created_at": time.Now(),
        },
    }
    
    opts := options.Update().SetUpsert(true)
    _, err := s.collection.UpdateOne(context.TODO(), filter, update, opts)
    return err
}

Performance Considerations

SQLite Optimization
// Enable WAL mode for better concurrency
db.Exec("PRAGMA journal_mode=WAL")

// Increase cache size
db.Exec("PRAGMA cache_size=10000")

// Optimize for faster writes
db.Exec("PRAGMA synchronous=NORMAL")

// Use connection pooling
db.SetMaxOpenConns(1)  // SQLite doesn't support concurrent writes
db.SetMaxIdleConns(1)
Batch Operations
func (s *SQLiteStorage) StoreBatch(contents []*ScrapedContent) error {
    tx, err := s.db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()
    
    stmt, err := tx.Prepare(insertQuery)
    if err != nil {
        return err
    }
    defer stmt.Close()
    
    for _, content := range contents {
        _, err = stmt.Exec(content.ID, content.URL, content.Title, /* ... */)
        if err != nil {
            return err
        }
    }
    
    return tx.Commit()
}

Migrations

SQLite Migration System
type Migration struct {
    Version int
    Query   string
}

var migrations = []Migration{
    {1, `CREATE TABLE scraped_content (...)`},
    {2, `ALTER TABLE scraped_content ADD COLUMN metadata TEXT`},
    {3, `CREATE INDEX idx_scraped_content_domain ON scraped_content(domain)`},
}

func (s *SQLiteStorage) migrate() error {
    // Create migrations table
    s.db.Exec(`CREATE TABLE IF NOT EXISTS migrations (version INTEGER PRIMARY KEY)`)
    
    // Get current version
    var currentVersion int
    s.db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM migrations").Scan(&currentVersion)
    
    // Apply pending migrations
    for _, migration := range migrations {
        if migration.Version > currentVersion {
            if _, err := s.db.Exec(migration.Query); err != nil {
                return fmt.Errorf("migration %d failed: %v", migration.Version, err)
            }
            s.db.Exec("INSERT INTO migrations (version) VALUES (?)", migration.Version)
        }
    }
    
    return nil
}

Backup and Recovery

SQLite Backup
# Create backup
sqlite3 ./data/scraper.db ".backup backup.db"

# Restore from backup
cp backup.db ./data/scraper.db

# Export to SQL
sqlite3 ./data/scraper.db ".dump" > backup.sql

# Import from SQL
sqlite3 new_database.db < backup.sql
Automated Backup Script
#!/bin/bash
# backup-sqlite.sh

DB_PATH="./data/scraper.db"
BACKUP_DIR="./backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# Create backup
sqlite3 $DB_PATH ".backup $BACKUP_DIR/scraper_$TIMESTAMP.db"

# Compress backup
gzip "$BACKUP_DIR/scraper_$TIMESTAMP.db"

# Clean old backups (keep last 7 days)
find $BACKUP_DIR -name "scraper_*.db.gz" -mtime +7 -delete

echo "Backup completed: scraper_$TIMESTAMP.db.gz"

Monitoring

Storage Metrics
// Metrics to track
type StorageMetrics struct {
    TotalRecords    int64
    StorageSize     int64
    AverageSize     float64
    WriteLatency    time.Duration
    ReadLatency     time.Duration
    ErrorRate       float64
}

func (s *SQLiteStorage) GetMetrics() (*StorageMetrics, error) {
    var metrics StorageMetrics
    
    // Count records
    s.db.QueryRow("SELECT COUNT(*) FROM scraped_content").Scan(&metrics.TotalRecords)
    
    // Calculate storage size
    var pageCount, pageSize int64
    s.db.QueryRow("PRAGMA page_count").Scan(&pageCount)
    s.db.QueryRow("PRAGMA page_size").Scan(&pageSize)
    metrics.StorageSize = pageCount * pageSize
    
    // Average content size
    if metrics.TotalRecords > 0 {
        metrics.AverageSize = float64(metrics.StorageSize) / float64(metrics.TotalRecords)
    }
    
    return &metrics, nil
}

Best Practices

Data Integrity
  1. Use transactions for related operations
  2. Implement proper error handling
  3. Add database constraints for data validation
  4. Regular integrity checks
Performance
  1. Create appropriate indexes
  2. Use prepared statements
  3. Implement connection pooling
  4. Consider partitioning for large datasets
Security
  1. Use parameterized queries to prevent SQL injection
  2. Encrypt sensitive data at rest
  3. Implement proper access controls
  4. Regular security audits

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type Content 

type Content struct {
	ID          string
	URL         string
	Title       string
	TextContent string
	RawHTML     string
	Links       []string
	Images      []string
	ScrapedAt   time.Time
	ContentHash string
	Metadata    map[string]string
}

Content represents scraped content

type Job 

type Job struct {
	ID           string
	URL          string
	Status       JobStatus
	CreatedAt    time.Time
	UpdatedAt    time.Time
	ErrorMessage string
	RetryCount   int32
	ContentID    string // Links to content table
}

Job represents a scraping job

type JobStatus 

type JobStatus int32

JobStatus represents the status of a scraping job 

const (
	JobStatusPending    JobStatus = 1
	JobStatusProcessing JobStatus = 2
	JobStatusCompleted  JobStatus = 3
	JobStatusFailed     JobStatus = 4
	JobStatusCancelled  JobStatus = 5
)

type Page 

type Page struct {
	URL         string
	ScrapedAt   time.Time
	ContentHash string
}

Page represents a scraped web page (legacy compatibility)

type SQLiteStorage 

type SQLiteStorage struct {
	// contains filtered or unexported fields
}

SQLiteStorage implements the Storage interface using SQLite

func (*SQLiteStorage) Close 

func (s *SQLiteStorage) Close() error

Close closes the database connection

func (*SQLiteStorage) GetContent 

func (s *SQLiteStorage) GetContent(ctx context.Context, contentID string) (*Content, error)

GetContent retrieves content by ID

func (*SQLiteStorage) GetContentByURL 

func (s *SQLiteStorage) GetContentByURL(ctx context.Context, url string) (*Content, error)

GetContentByURL retrieves content by URL

func (*SQLiteStorage) GetJob 

func (s *SQLiteStorage) GetJob(ctx context.Context, jobID string) (*Job, error)

GetJob retrieves a job by ID

func (*SQLiteStorage) GetLastScrapeTime 

func (s *SQLiteStorage) GetLastScrapeTime(ctx context.Context, url string) (time.Time, error)

GetLastScrapeTime retrieves the last time a URL was scraped. Returns sql.ErrNoRows if the URL has not been scraped.

func (*SQLiteStorage) GetScrapedPages 

func (s *SQLiteStorage) GetScrapedPages(ctx context.Context, limit int) ([]Page, error)

GetScrapedPages retrieves a list of scraped pages

func (*SQLiteStorage) GetScrapedPagesCount 

func (s *SQLiteStorage) GetScrapedPagesCount(ctx context.Context) (int, error)

GetScrapedPagesCount returns the total count of scraped pages

func (*SQLiteStorage) GetScrapedPagesPaginated 

func (s *SQLiteStorage) GetScrapedPagesPaginated(ctx context.Context, limit int, offset int) ([]Page, error)

GetScrapedPagesPaginated retrieves a paginated list of scraped pages

func (*SQLiteStorage) SaveContent 

func (s *SQLiteStorage) SaveContent(ctx context.Context, content *Content) error

SaveContent saves scraped content to the database

func (*SQLiteStorage) SaveJob 

func (s *SQLiteStorage) SaveJob(ctx context.Context, job *Job) error

SaveJob saves a job to the database

func (*SQLiteStorage) SaveScrapedData 

func (s *SQLiteStorage) SaveScrapedData(ctx context.Context, url string, scrapedAt time.Time, contentHash string) error

SaveScrapedData saves metadata about a scraped page

func (*SQLiteStorage) UpdateJobStatus 

func (s *SQLiteStorage) UpdateJobStatus(ctx context.Context, jobID string, status JobStatus, errorMessage string) error

UpdateJobStatus updates the status of a job

type Storage 

type Storage interface {
	// Job tracking methods
	SaveJob(ctx context.Context, job *Job) error
	GetJob(ctx context.Context, jobID string) (*Job, error)
	UpdateJobStatus(ctx context.Context, jobID string, status JobStatus, errorMessage string) error

	// Content methods
	SaveContent(ctx context.Context, content *Content) error
	GetContent(ctx context.Context, contentID string) (*Content, error)
	GetContentByURL(ctx context.Context, url string) (*Content, error)

	// Legacy methods (for compatibility)
	SaveScrapedData(ctx context.Context, url string, scrapedAt time.Time, contentHash string) error
	GetLastScrapeTime(ctx context.Context, url string) (time.Time, error)
	GetScrapedPages(ctx context.Context, limit int) ([]Page, error)
	GetScrapedPagesCount(ctx context.Context) (int, error)
	GetScrapedPagesPaginated(ctx context.Context, limit int, offset int) ([]Page, error)
	Close() error
}

Storage defines the interface for data persistence

func NewSQLiteStorage 

func NewSQLiteStorage(cfg config.DatabaseConfig) (Storage, error)

NewSQLiteStorage creates a new SQLite-based storage

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.