SGH database update for MBV files

Guest post from PB.

After the local server hdd crash in Nov 2015

​,​

 we had lost the sghtelugu file_id records. On re-importing the files into the database, I missed out the info that the mbv files had a different download filename

.

Due to this sghtelugu database and sgh database had different download filenames causing end

​-user download issues.

Did the following.

​...​

1. Tried to create a soft link in dl.radiosai.org expecting that http download link would automatically get redirected to the  softlink. But this did not work. It downloaded the file without the modification.

2. The only way is to update the records 

​both sghtelugu and the schedule page database (radiosai)

a. Created a SQL query that would give me the update SQL statements :

psql -d internal_db -A -F ',' -t  -c "SELECT 'UPDATE our_primary_key SET download_filename = \'' || download_filename || '\' WHERE filename = \'' || filename ||'\';' FROM our_primary_key WHERE filename <> download_filename AND filename ILIKE '%mbv%' " -o /home/sgh/update.sql

​This sql statement searches for all the MBV files where the filename is not matching the download filename in our internal database and forms a update sql list. 

UPDATE file_id SET download_filename = 'MBV_2003_08_MODIFIED.mp3' WHERE filename = 'MBV_2003_08_UNMODIFIED.mp3';
UPDATE file_id SET download_filename = .... etc 

about 1358 records

b. Executed this set of update queries on sghtelugu database. This step cleans up the local sghtelugu database

c.​

​ Then modified the update.sql file by using find-replace the database table name and column names like

UPDATE web_db_table_name SET dfileName = 'MBV_04_MODIFIED.mp3' WHERE fileName = 'MBV_04_UNMODIFIED.mp3';
etc

d. Logged into our website Plesk and then to phpMyAdmin database and executed the modified update queries.