A sqlite database explorer written in Prolog

Introduction

This post presents a simple explorer for sqlite databases. It can find out all table names of the sqlite file as well as the number of rows kept by each table. In addition, it can also disclose all column names of the tables and shows the data type for each column.

Notice that this script uses the predicate select_query to send a SQL command to sqlite for retreiving the table names of the given database.

As a demonstration, the prolog script below opens a sample sqlite file found on GitHub. Please refer to the link below for its setup.

Database Source: https://github.com/dtaivpp/car_company_database

Output

?- go. Sqlite DB Explorer ----------------------------- Tables in the database: |------------------------|-------| | Table | Rows | |------------------------|-------| | Brands | 6 | | Car_Options | 26 | | Car_Parts | 7 | | Car_Vins | 5 | | Customer_Ownership | 5 | | Customers | 6 | | Dealer_Brand | 13 | | Dealers | 4 | | Manufacture_Plant | 5 | | Models | 17 | | sqlite_sequence | 8 | |------------------------|-------| Column names of each table: Brands |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | brand_id | INTEGER | | brand_name | VARCHAR(50) | |---------------------------|----------------| Car_Options |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | option_set_id | INTEGER | | model_id | INTEGER | | engine_id | INTEGER | | transmission_id | INTEGER | | chassis_id | INTEGER | | premium_sound_id | INTEGER | | color | VARCHAR(30) | | option_set_price | INTEGER | |---------------------------|----------------| Car_Parts |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | part_id | INTEGER | | part_name | VARCHAR(100) | | manufacture_plant_id | INTEGER | | manufacture_start_date | DATE | | manufacture_end_date | DATE | | part_recall | INTEGER | |---------------------------|----------------| Car_Vins |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | vin | INTEGER | | model_id | INTEGER | | option_set_id | INTEGER | | manufactured_date | DATE | | manufactured_plant_id | INTEGER | |---------------------------|----------------| Customer_Ownership |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | customer_id | INTEGER | | vin | INTEGER | | purchase_date | DATE | | purchase_price | INTEGER | | warantee_expire_date | DATE | | dealer_id | INTEGER | |---------------------------|----------------| Customers |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | customer_id | INTEGER | | first_name | VARCHAR(50) | | last_name | VARCHAR(50) | | gender | STRING | | household_income | INTEGER | | birthdate | DATE | | phone_number | INTEGER | | email | VARCHAR(128) | |---------------------------|----------------| Dealer_Brand |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | dealer_id | INTEGER | | brand_id | INTEGER | |---------------------------|----------------| Dealers |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | dealer_id | INTEGER | | dealer_name | VARCHAR(50) | | dealer_address | VARCHAR(100) | |---------------------------|----------------| Manufacture_Plant |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | manufacture_plant_id | INTEGER | | plant_name | VARCHAR(50) | | plant_type | VARCHAR (7) | | plant_location | VARCHAR(100) | | company_owned | INTEGER | |---------------------------|----------------| Models |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | model_id | INTEGER | | model_name | VARCHAR(50) | | model_base_price | INTEGER | | brand_id | INTEGER | |---------------------------|----------------| sqlite_sequence |---------------------------|----------------| | Column | Data Type | |---------------------------|----------------| | name | | | seq | | |---------------------------|----------------| true. ?-

Source Code

% Reference: % https://www.swi-prolog.org/pack/file_details/prosqlite/doc/prosqlite.html openDb :- [library(prosqlite)], Opt = [as_predicates(true), ext('db')], sqlite_connect('Car_Database',sqldb,Opt). closeDb :- sqlite_disconnect(sqldb). % show column names of a table show_column_names(DbHandle, Table) :- % find all column names of a table findall([C,DT],sqlite_table_column(DbHandle,Table,C,data_type(DT)), ColList), writeln(Table), F1 = '|~`-t~28||~`-t~45||~n', F2 = '| ~w ~28||~t~w ~45||~n', format(F1), format(F2,['Column','Data Type']), format(F1), forall(member(X1,ColList),format(F2,X1)), format(F1), nl. % get table name table_name(DbHandle, Table) :- sqlite_query(DbHandle, 'SELECT name FROM sqlite_schema WHERE type=\'table\' ORDER BY name', row(Table)). % get table name and respective row count table_row_count(DbHandle, Table, RowCount) :- table_name(DbHandle,Table), sqlite_table_count(DbHandle,Table,RowCount). go :- nl, writeln('Sqlite DB Explorer'), writeln('-----------------------------'), nl, openDb, % find all table names in a database findall([T,RC], table_row_count(sqldb,T,RC),TRC), writeln("Tables in the database:"), F1 = '|~`-t~25||~`-t~33||~n', F2 = '| ~w ~25||~t~w ~33||~n', format(F1), format(F2,['Table','Rows']), format(F1), forall(member(X1,TRC),format(F2,X1)), format(F1), nl, % % show column names of each table writeln("Column names of each table:"), forall(member([T,_],TRC),show_column_names(sqldb,T)), nl, closeDb.