Skip to main content

​
Overview

Ubu-Block stores all blockchain data in SQLite, making it easily queryable with standard SQL. This provides powerful analytics capabilities while maintaining blockchain integrity.

​
Basic Query Command

Execute SQL queries against the blockchain: 
ubu-block --config config.toml query -q "SELECT * FROM results"

​
Database Schema

The blockchain database contains several tables:

​
Results Table

Stores individual vote counts: 
CREATE TABLE results (
    station_id INTEGER,
    candidate_id INTEGER,
    votes INTEGER,
    block_height INTEGER
);

​
Stations Table

Polling station information: 
CREATE TABLE stations (
    id INTEGER,
    ward_code TEXT,
    name TEXT
);

​
Candidates Table

Candidate details: 
CREATE TABLE candidates (
    id INTEGER,
    name TEXT,
    party_id INTEGER,
    position_type TEXT
);

​
Blocks Table

Blockchain metadata: 
CREATE TABLE blocks (
    height INTEGER PRIMARY KEY,
    hash TEXT,
    prev_hash TEXT,
    creator TEXT,
    signature TEXT,
    merkle_root TEXT,
    timestamp INTEGER
);

​
Example Queries

​
Query By Constituency

Get aggregated results for a specific constituency: 
SELECT
  c.name as candidate,
  SUM(votes) as votes,
  ward_name as ward,
  constituency_name as constituency,
  county_name as county,
  parties.title as party
FROM
  results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN candidates c ON c.id = results.candidate_id
  INNER JOIN wards on stations.ward_code = wards.ward_code
  INNER JOIN parties ON parties.id = c.party_id
  INNER JOIN constituencies ON wards.constituency_code = constituencies.constituency_code
  INNER JOIN counties ON constituencies.county_code = counties.county_code
WHERE
  position_type = 'Mp' and constituency = 'Juja'
GROUP BY candidate;
Output: 
+--------+--------------+----------+-----------+-------+-------+
| county | constituency |   ward   | candidate | party | votes |
+--------+--------------+----------+-----------+-------+-------+
| Kiambu |     Juja     | Kalimoni |   Omosh   |  ODM  |  21   |
| Kiambu |     Juja     | Kalimoni |   Mwas    |  PNU  |  66   |
+--------+--------------+----------+-----------+-------+-------+
Query results are displayed in a formatted table using the tabled library for easy reading.

​
Query By County

Get total votes by county: 
SELECT
  county_name as county,
  c.name as candidate,
  SUM(votes) as total_votes
FROM
  results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN candidates c ON c.id = results.candidate_id
  INNER JOIN wards ON stations.ward_code = wards.ward_code
  INNER JOIN constituencies ON wards.constituency_code = constituencies.constituency_code
  INNER JOIN counties ON constituencies.county_code = counties.county_code
GROUP BY county_name, c.name
ORDER BY county_name, total_votes DESC;

​
Query By Polling Station

Get results for a specific polling station: 
SELECT
  stations.name as station,
  c.name as candidate,
  results.votes,
  parties.title as party
FROM
  results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN candidates c ON c.id = results.candidate_id
  INNER JOIN parties ON parties.id = c.party_id
WHERE
  results.station_id = 022113056303301;

​
Query Block History

View the blockchain’s block history: 
SELECT
  height,
  substr(hash, 1, 16) as hash,
  creator,
  timestamp,
  (SELECT COUNT(*) FROM results WHERE block_height = blocks.height) as result_count
FROM blocks
ORDER BY height DESC
LIMIT 10;

​
Query Specific Candidate Performance

Track a candidate’s performance across all stations: 
SELECT
  stations.name as station,
  wards.ward_name as ward,
  results.votes,
  blocks.timestamp
FROM
  results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN wards ON stations.ward_code = wards.ward_code
  INNER JOIN candidates ON results.candidate_id = candidates.id
  INNER JOIN blocks ON results.block_height = blocks.height
WHERE
  candidates.name = 'Omosh'
ORDER BY results.votes DESC;

​
Advanced Queries

​
Voter Turnout Analysis

SELECCT
  constituency_name,
  SUM(votes) as total_votes,
  registered_voters,
  ROUND(SUM(votes) * 100.0 / registered_voters, 2) as turnout_percent
FROM
  results
  INNER JOIN stations ON stations.id = results.station_id
  INNER JOIN wards ON stations.ward_code = wards.ward_code
  INNER JOIN constituencies ON wards.constituency_code = constituencies.constituency_code
GROUP BY constituency_name
ORDER BY turnout_percent DESC;

​
Party Performance

SELECT
  parties.title as party,
  COUNT(DISTINCT candidates.id) as candidate_count,
  SUM(votes) as total_votes
FROM
  results
  INNER JOIN candidates ON results.candidate_id = candidates.id
  INNER JOIN parties ON candidates.party_id = parties.id
GROUP BY parties.title
ORDER BY total_votes DESC;

​
Time-based Analysis

Analyze when results were submitted: 
SELECT
  DATE(timestamp, 'unixepoch') as date,
  COUNT(*) as blocks_added,
  SUM((SELECT COUNT(*) FROM results WHERE block_height = blocks.height)) as total_results
FROM blocks
WHERE height > 0
GROUP BY date
ORDER BY date;

​
Query Best Practices

The database includes indexes on frequently queried columns. If you add custom tables, consider adding indexes:
CREATE INDEX idx_results_candidate ON results(candidate_id);
CREATE INDEX idx_results_station ON results(station_id);
Apply WHERE clauses before JOINs when possible to reduce data processing:
-- Good: Filter first
SELECT * FROM results WHERE station_id = 123
INNER JOIN stations ON ...

-- Less efficient: Filter after join
SELECT * FROM results
INNER JOIN stations ON ...
WHERE station_id = 123
When aggregating large datasets, consider:
  • GROUP BY on indexed columns
  • LIMIT results for initial exploration
  • Using subqueries for complex aggregations
When using the CLI, quote your SQL to avoid shell interpretation:
# Good
ubu-block query -q "SELECT * FROM results WHERE votes > 100"

# May cause issues
ubu-block query -q SELECT * FROM results WHERE votes > 100

​
Query Output Format

Queries return formatted tables with:
  • Automatic column width adjustment
  • Header row with column names
  • Aligned data based on type
  • Border styling for readability

​
Custom Output Formats

For programmatic access, query the database directly with SQLite tools: 
sqlite3 -csv data/blockchain.db "SELECT * FROM results"

​
Limitations

Current version has a temporary limitation: query results must include columns candidate, votes, ward, constituency, county, and party. This will be removed in future versions.

​
Direct Database Access

For complex analysis, access the database directly: 
sqlite3 data/blockchain.db
This opens an interactive SQL shell where you can:
  • Execute multiple queries
  • Create temporary views
  • Export data in various formats
  • Use SQLite-specific functions
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM results LIMIT 5;

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us