3

I am using SQLiteStudio 3.0.7 to dump the database schema regularly for versioning purposes and it seems to change the order of the table definitions at random. This wreaks havoc on the diffs as even a small change like adding one or two additional columns seems to result in wild diffs all over the place. A side by side comparision can result in a whole table being list out separately when it is just the line difference that should show.

Can SQLiteStudio be configured to do that correctly., or can sqlite itself do the same schema export in a fixed formatted manner?

1
  • Some discussion about also here, but not really useful
    – phil294
    Jul 17, 2021 at 7:49

3 Answers 3

2

SQLiteStudio sorts the database objects by type (table, index, trigger, view), but inside each group, sorts them randomly. (If you consider this a bug, report it.)

The sqlite3 shell dumps tables in the order in which they are stored in the sqlite_master table, which is typically the same order in which they were (re)created.

1
  • It is not a bug, but I will request it as a feature
    – vfclists
    Feb 12, 2016 at 13:48
0

To get a consistently sorted schema dump output, you might want to manually process the output yourself. I made a small helper script for this purpose:

#!/bin/bash
db=the_database.db
for table in $(sqlite3 $db "select name from sqlite_master where type = 'table' order by name"); do
    sqlite3 $db ".schema $table" | grep 'CREATE TABLE' | grep -v 'sqlite_sequence('
    sqlite3 $db ".schema $table" | grep -v 'CREATE TABLE' | sort
done

Does what I want, no guarantee for correctness though. The order of the columns of each respective table however can still vary.

0

Save it as whatever.sh and run: sh whatever.sh

#!/bin/bash
db="db.sqlite"
tables=("table1" "table2" "table3")
output_file="dump.sql"

# Clear or create the result.sql file
echo "" > $output_file

# Output the schema and data for the specified tables
for table in "${tables[@]}"; do
    # Output the schema for the current table
    echo "/* Schema for table $table */" >> $output_file
    sqlite3 $db ".schema $table" >> $output_file

    # Output the data for the current table as INSERT statements
    echo "/* Data for table $table */" >> $output_file
    sqlite3 $db ".mode insert $table" ".headers on" "SELECT * FROM $table;" >> $output_file
done

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.