4

i have a php app on Heroku where the user data is stored in sqlite. Every time i update my php files and push it to Heroku, the local sqlite file is also updated...meaning that i lose the working data and get the startup data.

including the file in .gitignore makes sure that this file is not available to the Heroku app. it simply deletes the file from the remote git repository.

git update-index --assume-unchanged fails to mark the sqlite file. it gives me an error saying "unable to mark file".

Need help with having the sqlite file created once and then not updated/deleted at all. Thanx for the suggestions.

Pallavi Singh
  • 113
  • 2
  • 9

3 Answers3

1

Adding file mask to .gitignore does not do anything by itself, and certainly does NOT delete file from remote repository (read more here).

In general, it is really bad idea to store SQLite databases under source control (including git). Best solution for this is to create script that creates template database if necessary. This script can look like this:

database.sql:

BEGIN TRANSACTION; -- wrap everything into one transaction
-- create tables, always use NOT EXISTS:
CREATE TABLE IF NOT EXISTS items (
    item_id INTEGER AUTOINCREMENT PRIMARY KEY,
    item_name VARCHAR(32)
);
-- create indexes, always use NOT EXISTS:
CREATE INDEX IF NOT EXISTS items_item_name ON items (item_name);
-- add more CREATE TABLE and/or CREATE INDEX statements... 

COMMIT;

Then you can execute this script using following command:

sqlite3 -init database.sql database.db ""

If you execute it first time, database.db would be created if it did not exist. If you execute it second time, nothing will happen. But, you can decide later to add more indexes or tables into your database.sql (which would be under source control), and executing it later would add missing objects into your database.

Community
  • 1
  • 1
mvp
  • 111,019
  • 13
  • 122
  • 148
  • @mvp.... have tried this option too.... the first time my tables were created...but with the next push of any php file, my database was also deleted and i had to start with a fresh DB/table creation. my .sqlite file is not under git anymore... but for some reason... since it is not there on local, it gets deleted from git repository also (even though i just use git add and not git add . – Pallavi Singh Sep 22 '13 at 10:38
  • @PallaviSingh: my answer assumes that you *delete* your `database.db` from git: `git rm database.db`, and add `database.sql` to git. Then you run script `database.sql` every time - this will either create it anew or update if necessary. Since database.db will not be under git control, it will not be overwritten. It is also worth adding it (*.db) to `.gitignore` then. – mvp Sep 22 '13 at 10:41
  • @mvp... your assumption is correct and it would have worked if I was working with a static DB...in which case re-creating the tables would work, but my DB is expected to have something like the score of a user. i wont have a copy of that data on my local so i cannot afford to recreate the tables after every push. i want that once the DB is created (using your suggestion of executing a script file rather than uploading the DB from local) none of my push commands touch this file. un fortunately --assume-unchanged does not work either. – Pallavi Singh Sep 22 '13 at 12:07
  • @PallaviSingh: here we go again: 1. remove `database.db` from git: `git rm database.db`. 2. execute script every time. 3. profit! (there is no need to fool with --assume-unchanged or what not). You will not have static db - it is fully dynamic, and is stored on user computer. It gets created or updated only as necessary. – mvp Sep 23 '13 at 06:37
0
Edit .gitignore to match the file you want to ignore
git rm --cached /path/to/file

See here: Applying .gitignore to committed files

Community
  • 1
  • 1
Raghvendra Parashar
  • 3,883
  • 1
  • 23
  • 36
  • Doesn't work either. After removing the cached copy of the file, when i do a git push, the Remove action is applied on the remote git and the sqlite file is removed from the repository. – Pallavi Singh Sep 21 '13 at 18:41
  • Here is a step by step of the commands (.gitignore already has the entry myDb.sqlite) > git rm --cached myDB.sqlite rm 'myDB.sqlite' > git add myfile.php > git commit -m "update" [master eb7edf3] update 2 files changed, 1 insertion(+) delete mode 100644 myDB.sqlite the sqlite file is marked for deletion and after the push command it is deleted. – Pallavi Singh Sep 21 '13 at 19:05
  • hey friend: you should not use `rm 'myDB.sqlite'`, this command is removing file from git repo – Raghvendra Parashar Sep 22 '13 at 09:28
  • ....the line rm 'myDB.sqlite' was output of the command rm --cached myDB.sqlite. i am clearly doing some thing wrong here because everywhere i read the rm --cached is expected to do the job... the problem is knowing what is wrong, – Pallavi Singh Sep 22 '13 at 10:34
0

Ok.... seems i was over doing it. I should have had either the .gitignore entry or the --assume-unchanged. Both together some how messed up all settings. Currently removed the .gitignore entry and have set the sqlite file to be marked as unchanged until further notice, using git update-index --assume-unchanged myDB.sqlite This did the trick for me. @rkp & @mvp...thanks for the help ... no amount of help is good if i am over zealous! :)

Pallavi Singh
  • 113
  • 2
  • 9