visualize_sqlite/
lib.rs

1#[macro_use]
2extern crate diesel;
3
4use diesel::prelude::*;
5use eyre::{Context, Result};
6use std::fmt::Display;
7
8mod raw {
9    use diesel::sql_types::*;
10
11    #[derive(QueryableByName)]
12    pub struct Table {
13        #[diesel(sql_type = Text)]
14        pub name: String,
15    }
16
17    #[derive(QueryableByName)]
18    pub struct Column {
19        #[diesel(sql_type = Text)]
20        pub name: String,
21        #[diesel(sql_type = Text, column_name = "type")]
22        pub typ: String,
23        #[diesel(sql_type = Bool)]
24        pub notnull: bool,
25        #[diesel(sql_type = Bool)]
26        pub pk: bool,
27        #[diesel(sql_type = Nullable<Text>)]
28        pub dflt_value: Option<String>,
29    }
30
31    #[derive(QueryableByName)]
32    pub struct ForeignKey {
33        #[diesel(sql_type = Text)]
34        pub table: String,
35        #[diesel(sql_type = Nullable<Text>)]
36        pub to: Option<String>,
37        #[diesel(sql_type = Text)]
38        pub from: String,
39    }
40}
41
42#[derive(Debug, Clone)]
43pub struct Column {
44    pub name: String,
45    pub typ: String,
46    pub nullable: bool,
47    pub default: Option<String>,
48    pub primary: bool,
49}
50
51#[derive(Debug, Clone)]
52pub struct Table {
53    pub name: String,
54    pub columns: Vec<Column>,
55    pub foreign_keys: Vec<ForeignKey>,
56}
57
58#[derive(Debug, Clone)]
59pub struct ForeignKey {
60    pub target_table: String,
61    pub target_column: Option<String>,
62    pub source_table: String,
63    pub source_column: String,
64}
65
66#[derive(Debug, Clone)]
67pub struct Schema(pub Vec<Table>);
68
69impl Schema {
70    fn get_tables(db: &mut SqliteConnection) -> Result<Vec<Table>> {
71        let tables: Vec<raw::Table> = diesel::sql_query(
72            "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT IN ('sqlite_sequence')",
73        )
74        .load(db)?;
75
76        tables
77            .into_iter()
78            .map(|table| {
79                Ok(Table {
80                    foreign_keys: Self::get_keys(db, &table.name)
81                        .wrap_err_with(|| format!("failed to get keys for {}", &table.name))?,
82                    columns: Self::get_columns(db, &table.name)
83                        .wrap_err_with(|| format!("failed to get columns for {}", &table.name))?,
84                    name: table.name,
85                })
86            })
87            .collect()
88    }
89
90    fn get_columns(db: &mut SqliteConnection, table: &str) -> Result<Vec<Column>> {
91        let columns: Vec<raw::Column> =
92            diesel::sql_query(format!("SELECT * FROM pragma_table_info('{}')", table)).load(db)?;
93
94        Ok(columns
95            .into_iter()
96            .map(|column| Column {
97                name: column.name,
98                typ: column.typ,
99                nullable: !column.notnull,
100                primary: column.pk,
101                default: column.dflt_value,
102            })
103            .collect())
104    }
105
106    fn get_keys(db: &mut SqliteConnection, table: &str) -> Result<Vec<ForeignKey>> {
107        let keys: Vec<raw::ForeignKey> = diesel::sql_query(format!(
108            "SELECT * FROM pragma_foreign_key_list('{}')",
109            table
110        ))
111        .load(db)?;
112
113        Ok(keys
114            .into_iter()
115            .map(|key| ForeignKey {
116                target_table: key.table,
117                target_column: key.to,
118                source_table: table.to_owned(),
119                source_column: key.from,
120            })
121            .collect())
122    }
123
124    pub fn load(db: &mut SqliteConnection) -> eyre::Result<Self> {
125        Ok(Self(Self::get_tables(db)?))
126    }
127}
128
129impl Display for Schema {
130    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
131        writeln!(f, "digraph {{")?;
132        writeln!(f, "rankdir=LR;")?;
133
134        for table in &self.0 {
135            table.fmt(f)?;
136        }
137
138        writeln!(f, "}}")
139    }
140}
141
142impl Display for Table {
143    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
144        writeln!(
145            f,
146            "{} [shape=plaintext label=< <table border='0' cellborder='1' cellspacing='0' cellpadding='5'>
147                <tr><td border='0'></td><td colspan='2'><b>{}</b></td></tr>",
148            self.name, self.name
149        )?;
150
151        for column in &self.columns {
152            writeln!(
153                f,
154                "<tr><td {} width='16'></td><td>{}</td><td port='{}'>{}</td></tr>",
155                if column.primary {
156                    "bgcolor='#2aa198'"
157                } else if column.nullable {
158                    "bgcolor='#6c71c4'"
159                } else {
160                    ""
161                },
162                column.name,
163                column.name,
164                column.typ,
165            )?;
166        }
167
168        writeln!(f, "</table> >]")?;
169
170        for key in &self.foreign_keys {
171            key.fmt(f)?;
172        }
173
174        Ok(())
175    }
176}
177
178impl Display for ForeignKey {
179    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
180        writeln!(
181            f,
182            "{}:{} -> {}",
183            self.source_table, self.source_column, self.target_table
184        )
185    }
186}
visualize_sqlite/lib.rs

visualize_sqlite/
lib.rs
