Git corrupts SQLite database

gitsqlite

I use SQLite for websites I create and manage for my clients (I am a web developer). They are very small and straightforward databases.

This is now happened twice, so it is pretty clear that it wasn't a fluke. This last one was a clear set of steps that then corrupted the database:

Open the database in SQLite3 by linux command line
Make a mistake in the data
Ask Git to restore the released version of the file (this is reaching out to GitHub for the copy)
Open the restored version, and file is corrupt

I then struggle to get it back in order, nothing I do in git fixes it. I can luckily grab it from the live website (no updates there) and all is well.

My two questions:

Is there something I can do differently while getting the file from the git repository to revert, it is not clear to me why git would corrupt the file
Is there something I can do that is easier to restore the file other than get the copy from the live website

Best Answer

This was what I finally figured out, in case someone else comes along and hits the same problem. It was very confusing because the corruption was coming from git, so when I did something like rollback, it got corrupted then, not with my own activities.

I am running PHPStorm on Windows, with a shared directory on a virtual linux machine. I need line endings to be LF, but since PHPStorm is running in Windows, the CRLF and LF battles ensue.

I had long ago fixed it with .gitattributes. But when I switched from MySQL to SQLite, I had not added .db as a file not to be messed with when it came to line endings.

Git was replacing a CRLF with an LF in the .db binary file

In .gitattributes, you need:

# Set the default behavior to always be linefeed for linux
* text=auto

# Definitively text files
*.txt text eol=lf
*.php text eol=lf
*.html text eol=lf
...

# Ensure those won't be changed
*.jpg -text
*.png -text
*.db -text

I added the last line and all was well, git is no longer trying to be helpful with the binary file and corrupting it with LFs.

One last thing I learned, you can set the gitattributes file, then go to your root directory and run the command:

git add --renormalize .
git status

You will then see any file that will be converted from CRLF to LF, a good way to fix an entire directory as well as making sure it is not doing to the SQLite database.

Related Solutions

Sqlite database: advanced SQL or redesign

I think the tables o_products, o_qtys, o_prices should be combined into a single table, with a one-to-many relationship with the orders table as follows:

order_details
(
    order_id INTEGER,
    order_item_id INTEGER,
    product_id INTEGER,
    price REAL,
    quantity INTEGER,
    PRIMARY KEY (order_id, order_item_id)
    FOREIGN KEY (order_id) REFERENCES Orders(o)
)

When you want to find out the total quantity of a given product, then

SELECT SUM(quantity) 
FROM order_details
WHERE product_id = :product_id

SQLite – How to Read Metadata from Database

The equivalent of, for instance, MySQL describe t1; is

PRAGMA TABLE_INFO(t1);

and

SELECT * FROM sqlite_master WHERE tbl_name = 't1';

is also useful. Most of the time, however, I simply use

.schema t1

which outputs the SQL definition of the table or view, even with the original comments (exactly the same as SELECT sql FROM sqlite_master WHERE..., with less typing. .sch works too.)

Best Answer

Related Solutions

Sqlite database: advanced SQL or redesign

SQLite – How to Read Metadata from Database

Related Question