diff --git a/html/inc/new-file-bu.php b/html/inc/new-file-bu.php index 004b9f6..0da458e 100644 --- a/html/inc/new-file-bu.php +++ b/html/inc/new-file-bu.php @@ -27,7 +27,9 @@ $row[ $colIdxs['BuType']], $row[ $colIdxs['BuRunning']], $row[ $colIdxs['BuError']], - $row[ $colIdxs['LastRunDt']] + $row[ $colIdxs['LastRunDt']], + $row[ $colIdxs['Res_User']], + $row[ $colIdxs['Res_Pwd']] ); } @@ -60,13 +62,16 @@ if($db_id>0){ $id_line = "
ID
$db_id
"; - $item_data = get_bu_item( $db_id ); // [ BUName, Dir_Src, Dir_Dest, Files_Ex, Bu_Type, BuRunning, BuError, LastRunDt ] + $item_data = get_bu_item( $db_id ); // [ BUName, Dir_Src, Dir_Dest, Files_Ex, Bu_Type, BuRunning, BuError, LastRunDt, Res_User, Res_Pwd ] $bu_name_value = "value='{$item_data[0]}'"; $bu_src_value = "value='{$item_data[1]}'"; $bu_dest_value = "value='{$item_data[2]}'"; $bu_ex_files = $item_data[3]; + $db_usr_name_value = "value='{$item_data[8]}'"; + $db_usr_pwd_value = "value='{$item_data[9]}'"; + $bu_error_list = str_replace( "\n", '
', $item_data[6]); $bu_error_list = "
Errors
$bu_error_list
"; @@ -84,6 +89,11 @@ $btn_new_bu = 'btn_new_bu_db'; } + $db_usr_pwd = '
DB User Name
'; + $db_usr_pwd .= "
"; + $db_usr_pwd .= '
DB Password
'; + $db_usr_pwd .= "
"; + $lab1_cont = '
Database name
'; $test_row_html = ''; $test_btn_html = ''; @@ -118,6 +128,8 @@
Backup Name
(optional)
+ $db_usr_pwd + $lab1_cont
@@ -142,8 +154,8 @@ EOD; - - + + $content = str_replace('$db_usr_pwd', $db_usr_pwd, $content); $content = str_replace('$test_btn_html', $test_btn_html, $content); $content = str_replace('$test_row_html', $test_row_html, $content); $content = str_replace('$bu_error_list', $bu_error_list, $content); diff --git a/html/js/main.js b/html/js/main.js index d4a22a1..84eb471 100644 --- a/html/js/main.js +++ b/html/js/main.js @@ -468,8 +468,13 @@ map_params.set( 'new_bu_name', document.getElementById('new_bu_name').value ); map_params.set( 'new_bu_source', document.getElementById('new_bu_source').value ); map_params.set( 'new_bu_dest', document.getElementById('new_bu_dest').value ); - if(!b_is_db_type){ - map_params.set( 'new_bu_exf', document.getElementById('new_bu_exf').value ); + if(b_is_db_type){ + console.log("Setting DB params username=" + document.getElementById('db_usr_name').value ); + map_params.set( 'db_usr_name', document.getElementById('db_usr_name').value ); + map_params.set( 'db_usr_pwd', document.getElementById('db_usr_pwd').value ); + }else{ + console.log("Setting File params"); + map_params.set( 'new_bu_exf', document.getElementById('new_bu_exf').value ); } } diff --git a/html/svc/backup.php b/html/svc/backup.php index 6a0410b..94c4bbd 100644 --- a/html/svc/backup.php +++ b/html/svc/backup.php @@ -43,6 +43,8 @@ $exFiles = $row[3]; $bu_type = $row[4]; $bu_rnng = $row[5]; + $bu_res_usr = $row[6]; + $bu_res_pwd = $row[7]; // To ensure uniqueness, we add the backup ID on the end of the path as a subdirectory. $bu_dest = "$bu_dest/bu_$buid"; @@ -55,7 +57,7 @@ } if($bu_type==1 ){ - return run_backup_DB( $bu_test, $buid, $bu_src, $bu_dest ); + return run_backup_DB( $bu_test, $buid, $bu_src, $bu_res_usr, $bu_res_pwd, $bu_dest ); } return run_backup_F( $bu_test, $buid, $bu_src, $bu_dest, $exFiles ); @@ -142,7 +144,7 @@ /** * This function is now used directly by the backup service. */ -function run_backup_DB( $bu_test, $buid, $bu_src, $bu_dest ){ +function run_backup_DB( $bu_test, $buid, $bu_src, $bu_res_usr, $bu_res_pwd, $bu_dest ){ global $g_data_dir; @@ -167,11 +169,15 @@ $fn_e = getFilename_out( $buid, $bu_test, true ); exec("echo 'ID: $buid' > $g_data_dir/$fn_e"); - // bash command - //mysqldump johntest > /home/johnp/tmp/dbs/johntest.sql - - $command = "mysqldump $bu_src 2>> $g_data_dir/$fn_e 1>> $bu_dest/$fn_o"; + // set the bash command + if($bu_res_usr!=''){ + $command = "mysqldump -u$bu_res_usr -p$bu_res_pwd $bu_src 2>> $g_data_dir/$fn_e 1>> $bu_dest/$fn_o"; + }else{ + $command = "mysqldump $bu_src 2>> $g_data_dir/$fn_e 1>> $bu_dest/$fn_o"; + } + debug_println( "command = $command", DEBUG_MED ); + $output = array(); $result_code = null; if( exec( $command, $output, $result_code )===FALSE){ diff --git a/html/svc/db-reads.php b/html/svc/db-reads.php index 402a800..464f449 100644 --- a/html/svc/db-reads.php +++ b/html/svc/db-reads.php @@ -29,13 +29,15 @@ $colIdxs['BU_REPEAT'] = $i++; $colIdxs['BuError'] = $i++; $colIdxs['LastRunDt'] = $i++; + $colIdxs['Res_User'] = $i++; + $colIdxs['Res_Pwd'] = $i++; } if( count($allRows)>0) return; $html_rows=''; $cnt=0; - $results = $db->query('SELECT BUID, BUName, Dir_Src, Dir_Dest, Files_Ex, BuType, BuRunning, BU_DATE, BU_TIME, BU_REPEAT, BuError, LastRunDt FROM BULIST '.$orderby); + $results = $db->query('SELECT BUID, BUName, Dir_Src, Dir_Dest, Files_Ex, BuType, BuRunning, BU_DATE, BU_TIME, BU_REPEAT, BuError, LastRunDt, Res_User, Res_Pwd FROM BULIST '.$orderby); while ($row = $results->fetchArray()) { //debug_println("found buid = ({$row['BUID']})", DEBUG_MED ); @@ -52,7 +54,9 @@ $row['BU_TIME'], $row['BU_REPEAT'], $row['BuError'], - $row['LastRunDt'] + $row['LastRunDt'], + $row['Res_User'], + $row['Res_Pwd'] ); } @@ -69,7 +73,7 @@ $db = new SQLite3($g_database_path, SQLITE3_OPEN_READONLY ); $html_rows=''; - $results = $db->query('SELECT BUID, BUName, Dir_Src, Dir_Dest, Files_Ex, BuType, BuRunning, BU_DATE, BU_TIME, BU_REPEAT, BuError, LastRunDt FROM BULIST where BUID='.$buid); + $results = $db->query('SELECT BUID, BUName, Dir_Src, Dir_Dest, Files_Ex, BuType, BuRunning, BU_DATE, BU_TIME, BU_REPEAT, BuError, LastRunDt, Res_User, Res_Pwd FROM BULIST where BUID='.$buid); if($row = $results->fetchArray()) { return array( $row['BUID'], @@ -83,7 +87,10 @@ $row['BU_TIME'], $row['BU_REPEAT'], $row['BuError'], - $row['LastRunDt'] + $row['LastRunDt'], + $row['Res_User'], + $row['Res_Pwd'] + ); } diff --git a/html/svc/db-writes.php b/html/svc/db-writes.php index d8e986f..b045d0d 100644 --- a/html/svc/db-writes.php +++ b/html/svc/db-writes.php @@ -24,18 +24,22 @@ * Both inserts as well as edits a single backup item. */ function save_bu_item( $postVars, $bu_id=0 ){ - + + //debug_println("save_bu_item: ".$postVars['db_usr_name']." ".$postVars['db_usr_pwd']); + global $g_database_path; $db = new SQLite3( $g_database_path ); $db_st = null; if( $postVars['new_bu_is_dbType'] ){ + // Database backup if($bu_id==0){ - $db_st = $db->prepare('INSERT INTO BULIST ( BUName, Dir_Src, Dir_Dest, BuType ) values( :bun, :bus, :bud, 1 )'); + $db_st = $db->prepare('INSERT INTO BULIST ( BUName, Dir_Src, Dir_Dest, BuType ) values( :bun, :bus, :bud, 1, :buusr, :bupwd )'); }else{ - $db_st = $db->prepare('update BULIST set BUName=:bun, Dir_Src=:bus, Dir_Dest=:bud where BUID=:buid'); + $db_st = $db->prepare('update BULIST set BUName=:bun, Dir_Src=:bus, Dir_Dest=:bud, Res_User=:buusr, Res_Pwd=:bupwd where BUID=:buid'); } }else{ + // File system backup if($bu_id==0){ $db_st = $db->prepare('INSERT INTO BULIST ( BUName, Dir_Src, Dir_Dest, Files_Ex, BuType ) values( :bun, :bus, :bud, :buex, 0 )'); }else{ @@ -49,9 +53,13 @@ $db_st->bindValue(':bun', $postVars['new_bu_name'] ); $db_st->bindValue(':bus', $postVars['new_bu_source'] ); - $db_st->bindValue(':bud', $postVars['new_bu_dest'] ); - if( !$postVars['new_bu_is_dbType'] ){ - $db_st->bindValue(':buex', $postVars['new_bu_exf'] ); + $db_st->bindValue(':bud', $postVars['new_bu_dest'] ); + + if( !$postVars['new_bu_is_dbType'] ){ + $db_st->bindValue(':buex', $postVars['new_bu_exf'] ); + }else{ + $db_st->bindValue(':buusr', $postVars['db_usr_name'] ); + $db_st->bindValue(':bupwd', $postVars['db_usr_pwd'] ); } $db_st->execute(); diff --git a/html/svc/main.php b/html/svc/main.php index ae9db26..a9ca770 100644 --- a/html/svc/main.php +++ b/html/svc/main.php @@ -264,6 +264,8 @@ $bu_row[$i++] = $row[ $g_bu_colIdxs['Files_Ex'] ]; $bu_row[$i++] = $row[ $g_bu_colIdxs['BuType'] ]; $bu_row[$i++] = $isRunning; + $bu_row[$i++] = $row[ $g_bu_colIdxs['Res_User'] ]; + $bu_row[$i++] = $row[ $g_bu_colIdxs['Res_Pwd'] ]; // we should be able to use pcntl_fork() $pid = pcntl_fork(); @@ -406,11 +408,11 @@ set_sched_date_db( $buid, $dt_new ); - } - - set_bu_running_db( $buid, false ); + } - }//if manually kicked off - nothing to do + } + + set_bu_running_db( $buid, false ); //after a backup, we need to completely reload the cache because the ordering of the backups change and //the ordering is what the scheduler uses to get the next entry. diff --git a/install/apply-patches.php b/install/apply-patches.php new file mode 100644 index 0000000..86893c2 --- /dev/null +++ b/install/apply-patches.php @@ -0,0 +1,188 @@ +exec("delete from PARAMS where P_Name='db_apply_patch_php'"); + + //which patch to start with + $curr_version = get_curr_patch_ver( ); + $st_patch_val = $curr_version; + + print( "Current version is $curr_version\n" ); + + do{ + $st_patch_val++; + + //process the SQL patch first + $patch_name = get_patch_name( $st_patch_val ); + $patch_data = read_patch( $patch_name ); + if($patch_data!=null){ + apply_patch_sql( $st_patch_val, $patch_name, $patch_data ); + } + + //process the PHP patch + $patch_name = get_patch_name( $st_patch_val, "php"); + $patch_data = read_patch( $patch_name ); + if($patch_data!=null){ + apply_patch_php( $st_patch_val, $patch_name, $patch_data ); + } + + }while($patch_data!=null); + + $applied = $st_patch_val - $curr_version -1; + print( "Applied $applied patches\n" ); + + +} + +function apply_patch_php( $st_patch_val, $patch_name, $patch_data ){ + + print("Applying patch: $patch_name \n"); + + global $g_database_path; + + //first mark the database so that we know a patch has started + $db = new SQLite3( $g_database_path ); + $db->exec("insert into PARAMS(P_Name, P_Value) values('db_apply_patch_php', $st_patch_val )"); + try{ + + eval($patch_data); + $db->exec("delete from PARAMS where P_Name='db_apply_patch_php'"); + + }catch( Exception $e ){ + print( "Exception: " . $e->getMessage() ); + throw $e; + } +} + + +function apply_patch_sql( $st_patch_val, $patch_name, $patch_data ){ + + print("Applying patch: $patch_name \n"); + + $arr_sql_stat = split_sql_data( $patch_data ); + if( count($arr_sql_stat) == 0){ + print("Patch empty\n"); + return; + } + + global $g_database_path; + + //first mark the database so that we know a patch has started + $db = new SQLite3( $g_database_path ); + $db->exec("insert into PARAMS(P_Name, P_Value) values('db_apply_patch', $st_patch_val )"); + + try{ + for( $i=0; $iexec( $arr_sql_stat[$i] ); + } + + // update the version + $db->exec("delete from PARAMS where P_Name='db_apply_patch'"); + $db->exec("update PARAMS set P_Value=$st_patch_val where P_Name='db_curr_patch'"); + + }catch( Exception $e ){ + print( "Exception: " . $e->getMessage() ); + throw $e; + } + + +} + +/** + * Simple return the patch contents as a string. + * + * Returns null if no such patch exists. + * + */ +function read_patch( $patch_name ){ + global $g_patch_dir; + $fn = $g_patch_dir."/$patch_name"; + if(!file_exists($fn)) return null; + return file_get_contents($fn); +} + +/** + * Get the file name of the patch given it's integer number. + * + */ +function get_patch_name( $patch_int, $patch_type="sql"){ + return "p-$patch_int.$patch_type"; +} + +/** + * Read the database patch version from the params table. + * Will create the table if it does not exists thus assuming that this the first run, setting db_curr_patch=0 + */ +function get_curr_patch_ver( ){ + + global $g_database_path; + + $db = new SQLite3( $g_database_path ); + $results = $db->query("SELECT name FROM sqlite_master WHERE type='table' AND name='PARAMS'"); + $row = $results->fetchArray(); + if( !$row ) { + //create the table and the the version number + print "Creating table PARAMS\n"; + $db->exec("CREATE TABLE \"PARAMS\" ( \"P_Name\" TEXT NOT NULL UNIQUE, \"P_Value\" TEXT, PRIMARY KEY(\"P_Name\"))"); + $db->exec("insert into PARAMS(P_Name, P_Value) values('db_curr_patch', 0 )"); + return 0; + } + + //read the version number + $results = $db->query("SELECT P_Value FROM PARAMS WHERE P_Name='db_curr_patch'"); + $row = $results->fetchArray(); + if( !$row ) { + print "Param db_curr_patch not found\n"; + exit(1); + } + + return $row['P_Value']; +} + +/** + * Return an array of strings, one for each SQL statement. Comments are ignored. + */ +function split_sql_data( $patch_data ){ + + $arr_sql_stat = array(); + + $lines = preg_split("/\r\n|\n|\r/", $patch_data); + $stat_nxt = ''; + $i=0; + while( $i diff --git a/install/db/data.db b/install/db/data.db index fb0bb0d..481e427 100644 --- a/install/db/data.db +++ b/install/db/data.db Binary files differ diff --git a/install/db/p-1.sql b/install/db/p-1.sql new file mode 100644 index 0000000..80579a7 --- /dev/null +++ b/install/db/p-1.sql @@ -0,0 +1,14 @@ +-- This is the first patch to be applied. + +CREATE TABLE "LOGINS" ( + "UsrName" TEXT NOT NULL UNIQUE, + "UsrPword" TEXT NOT NULL, + PRIMARY KEY("UsrName") +); + +INSERT INTO LOGINS(UsrName,UsrPword) values("johnpearcey","password"); +INSERT INTO LOGINS(UsrName,UsrPword) values("larspetersen","nesretepsral"); +INSERT INTO LOGINS(UsrName,UsrPword) values("rolandovalle","ellavodnalor"); + +alter table BULIST add column Res_User TEXT; +alter table BULIST add column Res_Pwd TEXT; diff --git a/notes.txt b/notes.txt index c150247..194c1c9 100644 --- a/notes.txt +++ b/notes.txt @@ -45,3 +45,11 @@ const DEBUG_HIGH = DEBUG_MED+1; const DEBUG_VHIGH = DEBUG_HIGH+1; + Show the table cache running in the service (the numbers are column widths and optional) + php main.php f:cache-show 5 5 10 10 5 5 5 5 + + +Install +------- + + Much of this is defunct. Only the database updates are relevant currently.