Package Management Database with SQLite

[pbalduino]

Description

Implement a package management system using SQLite to track installed software, dependencies, versions, and file ownership on meniOS.

Overview

A package manager is essential for any OS to:

• Track what software is installed
• Manage dependencies between packages
• Handle upgrades and removals cleanly
• Prevent file conflicts
• Query package information

SQLite is perfect for this because:

• Not performance-critical (installs happen rarely)
• Complex queries needed (find dependencies, conflicts)
• Needs reliable persistence
• Standard tool (pkg managers use SQLite: apt, rpm, pacman)

Database Schema

Tables

packages - Installed packages

• package_id (primary key)
• name (unique)
• version
• architecture (x86_64, etc)
• description
• install_date
• size_kb

files - Files owned by packages

• file_id (primary key)
• package_id (foreign key)
• path (unique)
• size
• permissions
• checksum (SHA256)

dependencies - Package dependencies

• package_id (foreign key)
• depends_on_package (foreign key)
• dependency_type (required, optional, conflicts)

repositories - Package sources

• repo_id (primary key)
• name
• url
• enabled (boolean)

Package Manager Features

Basic Operations

• Install package (download, extract, register in DB)
• Remove package (delete files, update DB)
• Update package (remove old, install new)
• List installed packages
• Search for packages
• Show package info

Advanced Features

• Dependency resolution
• Conflict detection
• Orphaned package detection
• Package verification (checksum validation)
• Rollback capability

Command-Line Interface

# Install package
pkg install gcc

# Remove package
pkg remove gcc

# Search
pkg search compiler

# List installed
pkg list

# Show package info
pkg info gcc

# Update package database
pkg update

# Upgrade all packages
pkg upgrade

Implementation

Package Format

• Tarball with metadata (package.json or similar)
• Files to install
• Pre/post install scripts

Package Manager Daemon

• Runs in userspace
• Uses SQLite for database
• Downloads packages from repositories
• Handles installation/removal

Database Location

• /var/lib/pkg/packages.db
• /var/cache/pkg/ for downloaded packages
• /var/lib/pkg/repos.d/ for repository configs

SQL Queries

Common operations implemented as SQL queries for dependency resolution, conflict detection, orphan finding, and file ownership lookup.

Tasks

• Design package format and metadata schema
• Create SQLite database schema
• Implement package installation logic
• Implement package removal with dependency checks
• Add dependency resolution algorithm
• Create command-line interface
• Add repository management
• Implement package download/cache
• Add package verification (checksums)
• Create package building tools
• Add rollback/transaction support
• Implement upgrade logic

Integration

• File system (VFS) for package file operations
• SQLite for database
• Network stack (future) for downloading packages
• FAT32 write support for installing files

Acceptance Criteria

• Can install packages from tarball
• Tracks all installed files in database
• Prevents file conflicts
• Resolves dependencies correctly
• Can remove packages cleanly
• Lists installed packages with queries
• Package database persists across reboots
• Handles upgrades without breaking system
• Command-line tool works intuitively

Priority

Medium - Very useful but not blocking core milestones

Estimated Effort

3-4 weeks

Notes

• Similar to apt/dpkg, rpm/yum, or pacman
• Start simple, add features incrementally
• SQLite makes complex queries easy