Repository files navigation

Helpful queries andPhoenix Live Dashboard integration forSQLite. It's likeecto_psql_extras but for SQLite instead of PostgreSQL.

The package can be installed by addingecto_sqlite3_extras to your list of dependencies inmix.exs:

defdepsdo[{:ecto_sqlite3_extras,"~> 1.2.2"}]end

When you haveecto_sqlite3_extras in the list of dependencies for yourPhoenix project that usesecto_sqlite3, thePhoenix Live Dashboard will automatically show the tables produced byecto_sqlite3_extras in the "Ecto Stats" tab. Magic!

If you don't have Phoenix Live Dashboard on the environment you want to inspect, you can useecto_sqlite3_extras directly from theiex shell.

# run the query and print a nice ASCII table into stdoutEctoSQLite3Extras.table_size(MyProject.Repo)# get the raw output of the queryEctoSQLite3Extras.table_size(MyProject.Repo,format::raw)# run the query on a remote nodeEctoSQLite3Extras.table_size({MyProject.Repo,self()})

1. total_size. The total size of all tables and indices. It's a summary table, it has only 2 columns:name andvalue. Rows:
   1. cells: The number of cells in the DB. Each value stored in the DB is represented as at least one cell. So, the number of cells correlates with the number of records in the DB.
   2. payload_size: How much space the actual useful payload takes in the DB.
   3. unused_size: How much space in the DB is reserved, not used yet, and can be used later to store more data. This is a surplus that occurs because SQLite allocates space for data in chunks ("pages").
   4. vacuum_size: How much space is unused and cannot be used for future data. You can runVACUUM command to reduce it.
   5. page_size: The total space occupied by all pages. Each page is a single chunk of space allocated by SQLite. This number is the sum ofpayload_size,unused_size, andvacuum_size.
   6. pages: The total number of pages.
   7. pages: leaf: The pages that store the actual data. ReadSQLite Internals: Pages & B-trees to learn more.
   8. pages: internal: The pages that store ranges for leaf pages for a faster lookup. Sometimes also called "interior pages".
   9. pages: overflow: The pages that store chunks of big data that don't fit in a single leaf page.
   10. pages: table: The pages used for storing data for tables.
   11. pages: index: The pages used for storing indices.
2. table_size. Information about the space used (and unused) by all tables. Based on thedbstat virtual table.
   1. name: The table name.
   2. payload_size.
   3. unused_size.
   4. vacuum_size.
   5. page_size.
   6. cells.
   7. pages.
   8. max_payload_size: The size of the biggest payload in the table.
3. index_size. Size of all indices.
   1. name: The index name.
   2. table_name: The table where the index is defined.
   3. column_name: The name of the column being indexed. This column is NULL if the column is the rowid or an expression.
   4. payload_size.
   5. unused_size.
   6. page_size.
   7. cells.
   8. pages.
   9. max_payload_size.
4. sequence_number. Sequence numbers of autoincrement columns. Generated based on thesqlite_sequence table. The query will fail if there are no autoincrement columns in the DB yet.
   1. table_name.
   2. sequence_number.
5. pragma. List values of PRAGMAs (settings). Only includes the ones that have an integer or a boolean value. For brevity, the ones with the0 (false) value are excluded from the output (based on the observation that this is the default value for most of the PRAGMAs). Check out the SQLite documentation to learn more about what each PRAGMA means:PRAGMA Statements.
   1. name: the name of the PRAGMA as listed in the SQLite documentation.
   2. value: the value of the PRAGMA. Thetrue value is converted into1 (andfalse is simply excluded).
6. compile_options. List thecompile-time options used when building SQLite, one option per row. The "SQLITE_" prefix is omitted from the returned option names. Seeexqlite docs on how to change these options.
7. integrity_check. Run integrity checks on the database. ExecutesPRAGMA integrity_check and returns the resulting messages.

These are the projects that madeecto_sqlite3_extras possible:

1. phoenix_live_dashboard is the reason why I made the project. I want my SQLite-powered Phoenix service to have the same nice-looking live dashboard as for PostgreSQL.
2. exqlite provides SQLite support for Elixir. Theyenabled just for me theSQLITE_ENABLE_DBSTAT_VTAB option required forecto_sqlite3_extras to work, literally making this project possible.
3. ecto_psql_extras is a similar project for PostgreSQL. I shamelessly copied the project structure and tests, so that I can be sure thatecto_sqlite3_extras can be a drop-in replacement forecto_psql_extras.