Skip to main content
The database crate provides SQLite-based storage for the Ubu-Block blockchain and election data.

​
Overview

This crate manages:
  • Blockchain storage and retrieval
  • Public and private key management
  • Election data (stations, wards, constituencies, counties)
  • Candidate and voting results
  • Chain validation

​
Main Types

​
Database

The main database interface managing both chain and private databases. 
pub struct Database {
    pub chain_db: SqlitePool,
    pub private_db: SqlitePool,
}

​
Construction

pub fn new(chain_db: SqlitePool, private_db: SqlitePool) -> Self
Creates a new database instance with separate pools for chain and private data. Example: 
use database::{Database, SqlitePool};

let chain_db = SqlitePool::connect("chain.db").await?;
let private_db = SqlitePool::connect("private.db").await?;

let db = Database::new(chain_db, private_db);

pub fn new_in_memory() -> Self
Creates an in-memory database for testing. Example: 
let db = Database::new_in_memory();

​
Blockchain Operations

​
Block Management

pub async fn add_block(&mut self, block: &Block) -> Result<i64, sqlx::Error>
Adds a new block to the blockchain. Returns the block height. Example: 
let height = db.add_block(&block).await?;
println!("Block added at height: {}", height);

pub async fn get_block_by_height(&self, height: i64) -> Result<Block, sqlx::Error>
Retrieves a block by its height. Example: 
let block = db.get_block_by_height(42).await?;

pub async fn get_block_by_hash(&self, hash: &str) -> Result<Block, sqlx::Error>
Retrieves a block by its hash. Example: 
let block = db.get_block_by_hash("abc123...").await?;

pub async fn last_block(&self) -> Result<Block, sqlx::Error>
Returns the most recent block in the chain. 
pub async fn get_height(&self) -> Result<i64, sqlx::Error>
Returns the current blockchain height. Example: 
let height = db.get_height().await?;

pub async fn get_blocks_in_range(
    &self,
    start: i64,
    end: i64,
) -> Result<Vec<Block>, sqlx::Error>
Retrieves multiple blocks within a height range. Example: 
let blocks = db.get_blocks_in_range(0, 100).await?;

​
Chain Validation

pub async fn is_valid(&self) -> Result<bool, sqlx::Error>
Validates the entire blockchain by checking:
  • Block hash integrity
  • Previous hash linkage
  • Merkle root correctness
  • Digital signatures
Example: 
if db.is_valid().await? {
    println!("Blockchain is valid");
} else {
    println!("Blockchain validation failed");
}

​
Key Management

​
Public Keys

pub async fn add_public_key(
    &self,
    pub_key: &[u8],
    creator: &str,
    pubkey_hash: &str,
    block_height: i32,
) -> Result<i64, sqlx::Error>
Adds a public key to the chain database. 
pub async fn get_public_key(&self, hash: &str) -> Result<PubKey, sqlx::Error>
Retrieves a public key by its hash. Example: 
let pub_key = db.get_public_key("hash123").await?;

​
Private Keys

pub async fn add_private_key(
    &self,
    priv_key: &Vec<u8>,
    pub_key_hash: &str,
) -> Result<i64, sqlx::Error>
Stores a private key in the private database. 
pub async fn get_my_public_key_hashes(&self) -> Result<Vec<String>, sqlx::Error>
Returns all public key hashes for which we have private keys. 
pub async fn get_private_key(
    &self
) -> Result<(SigningKey, VerifyingKey, PubKey), sqlx::Error>
Retrieves the first available private key with its corresponding public key. Example: 
let (signing_key, verifying_key, pub_key) = db.get_private_key().await?;

​
Election Data Queries

​
Geographic Entities

pub async fn counties(&self) -> Result<Vec<County>, sqlx::Error>
Returns all counties. 
pub async fn constituencies_by_county(
    &self,
    county_id: &u32,
) -> Result<Vec<Constituency>, sqlx::Error>
Returns constituencies within a specific county. 
pub async fn wards_by_constituency(
    &self,
    constituency_code: &u32,
) -> Result<Vec<Ward>, sqlx::Error>
Returns wards within a specific constituency. 
pub async fn stations_by_ward(&self, ward_id: &u32) -> Result<Vec<Station>, sqlx::Error>
Returns polling stations within a specific ward.

​
Political Data

pub async fn parties(&self) -> Result<Vec<Party>, sqlx::Error>
Returns all political parties. 
pub async fn positions(&self) -> Result<Vec<String>, sqlx::Error>
Returns all election positions (e.g., President, Governor, MCA).

​
Candidates

pub async fn candidates_by_station(
    &self,
    station_id: i32,
) -> Result<Vec<Candidate>, sqlx::Error>
Returns candidates available at a specific polling station. 
pub async fn candidates_by_ward(
    &self,
    ward_code: &i32,
) -> Result<Vec<Candidate>, sqlx::Error>
Returns MCA candidates for a specific ward. 
pub async fn candidates_by_constituency(
    &self,
    constituency_code: &i32,
    position_type: &str,
) -> Result<Vec<Candidate>, sqlx::Error>
Returns candidates for a position within a constituency. 
pub async fn candidates_by_county(
    &self,
    county_code: &i32,
    position_type: &str,
) -> Result<Vec<Candidate>, sqlx::Error>
Returns candidates for a position within a county. 
pub async fn candidates_national(&self) -> Result<Vec<Candidate>, sqlx::Error>
Returns presidential candidates.

​
Results Queries

​
By Geographic Level

pub async fn results_by_station(
    &self,
    station_id: i64,
) -> Result<Vec<StationResult>, sqlx::Error>
Returns voting results for a specific polling station. 
pub async fn results_by_ward(
    &self,
    ward_code: i32,
) -> Result<Vec<WardResult>, sqlx::Error>
Returns aggregated results for a ward. 
pub async fn results_by_constituency(
    &self,
    constituency_code: i32,
) -> Result<Vec<ConstituencyResult>, sqlx::Error>
Returns aggregated results for a constituency. 
pub async fn results_by_county(
    &self,
    county_code: i32,
) -> Result<Vec<CountyResult>, sqlx::Error>
Returns aggregated results for a county. 
pub async fn results_by_position(
    &self,
    position_type: &str,
) -> Result<Vec<PositionResult>, sqlx::Error>
Returns national results for a specific position. Example: 
let presidential_results = db.results_by_position("President").await?;
for result in presidential_results {
    println!("{}: {} votes", result.candidate_name, result.total_votes);
}

​
Database Schema

The database uses two separate SQLite databases:

​
Chain Database (Public)

  • blockchain - Block headers and metadata
  • results - Election results per station
  • pubkeys - Public keys of block creators
  • counties, constituencies, wards, stations - Geographic data
  • parties, positions, candidates - Political data

​
Private Database (Local Only)

  • privkeys - Private keys for block signing
Initialization SQL is available in:
  • MAIN_SETUP constant (chain database schema)
  • PRIV_SETUP constant (private database schema)

​
Error Handling

All methods return Result<T, sqlx::Error> for database operations. Common errors include:
  • Connection failures
  • Query execution errors
  • Data integrity violations

​
Thread Safety

The Database struct is Clone and uses SqlitePool for connection pooling, making it safe to use across multiple async tasks.

Build docs developers (and LLMs) love

Get started for freeTalk to us