SQLite Schema Discovery

[billy1624]

I found a SQLite version of Sakila schema for testing

• https://github.com/ivanceras/sakila/tree/master/sqlite-sakila-db

Discovery

1. Getting column info: https://www.sqlite.org/pragma.html#pragma_table_info
   

2. Getting foreign key info: https://www.sqlite.org/pragma.html#pragma_foreign_key_list
   

3. Getting index & primary key info: https://www.sqlite.org/pragma.html#pragma_index_list
   

Parser

Parse the above result into schema definition structs. Including information like

• table name
• columns
  • name
  • data type
    • length (if any)
  • default value
  • nullable
  • primary key
  • auto increment
• foreign key constraints

Writer

Converting the discovered & parsed table info into sea_query::TableCreateStatement.

[tyt2y3]

No, we should not write a custom SQL parser

I guess this may help?

https://stackoverflow.com/questions/604939/how-can-i-get-the-list-of-a-columns-in-a-table-for-a-sqlite-database

[billy1624]

No, we should not write a custom SQL parser

I guess this may help?

https://stackoverflow.com/questions/604939/how-can-i-get-the-list-of-a-columns-in-a-table-for-a-sqlite-database

Yes, we could use a combination of SQLite pragma statements for

1. Getting column info: https://www.sqlite.org/pragma.html#pragma_table_info

2. Getting foreign key info: https://www.sqlite.org/pragma.html#pragma_foreign_key_list

3. Getting index & primary key info: https://www.sqlite.org/pragma.html#pragma_index_list

[billy1624]

Hey @charleschege, just a quick check, are you taking this issue?

[charleschege]

Hi @billy1624 I am working on it

[billy1624]

Thanks!! Do ping me if you need any help, either here or Discord, both fine.