Friday, February 28, 2014
PostgresSQL
PostgresSQL tutorial
https://wiki.postgresql.org/wiki/PostgreSQL_Tutorials
1) Create a PostgresSQL standard user account.
2)Port 5432
Wednesday, February 26, 2014
online UML diagram tools and MySQl table online designer
Online UML diagram tools
1)ArgoUML is not only a free UML modelling tool, it is also an Open Source Development project . You can launch via java web start or download it.
http://argouml.tigris.org/
ArgoUML with relational database modeling
http://argouml-db.tigris.org/
2) Another is genmymodel
http://genmymodel.com
You can login with Github account.
3) MySQL table and schema online designer:
http://diagrams.seaquail.net
List of Unified Modeling Language tools
http://en.wikipedia.org/wiki/UML_tools
Tuesday, February 25, 2014
PHP hash function for password encryption
PHP hash function for password encryption:
hash — Generate a hash value
Example
1) hash('md5', 'string'); 2)
hash('sha1', 'string'); More about hash algorithm
md2
md4
md5
sha1
sha256
sha384
sha512
ripemd128
ripemd160
ripemd256
ripemd320
whirlpool
tiger128,3
tiger160,3
tiger192,3
tiger128,4
tiger160,4
tiger192,4
snefru
gost
adler32
crc32
crc32b
haval128,3 ......
PHP function for input sanitizing
In PHP, there are several functions for input sanitizing:
1) strip_tags — Strip HTML and PHP tags from a string
2)
htmlspecialchars — Convert special characters to HTML entities
3)
htmlentities — Convert all applicable characters to HTML entities
htmlentities converts more characters to HTML entities than htmlspecialchar, and has more options.
mysqli_real_escape_string is used for MySQL
database sanitizing. It removes special characters that could
be considered dangerous when passed into a database.
another function
addslashes — Quote string with slashes
addslashes is less powerful than mysqli_real_escape_string
5)
filter_var — Filters a variable with a specified filter
PHP form - remember input checkbox values
In PHP form, we may need to remember inpt checkbox values. As checkbox has multiple choices,
the $_POST of the checkbox returns an array. The checkbox name is also an array. We use in_array to check if the return array contains the checkbox values.
Example as following:
<?php
if (($_SERVER['REQUEST_METHOD'] == 'POST') && (!empty($_POST['action']))):
if (isset($_POST['favoritefood'])) { $favoritefood = $_POST['favoritefood']; }
endif;
?>
<form id="myform" name="testform" action="<?php echo $_SERVER['PHP_SELF'] ?>" method="POST">
Favorite Food
<ol>
<li>
<input type="checkbox" name="favoritefood[]" value="apple" id="appleid"
<?php if ((isset($favoritefood)) && (in_array("apple", $favoritefood))) { echo "checked"; } ?> />
<label for="appleid">Apple</label>
</li>
<li>
<input type="checkbox" name="favoritefood[]" value="orange" id="orangeid"
<?php if ((isset($favoritefood)) && (in_array("orange", $favoritefood))) { echo "checked"; } ?> />
<label for="orangeid">Orange</label>
</li>
<li>
<input type="checkbox" name="favoritefood[]" value="banana" id="bananaid"
<?php if ((isset($favoritefood)) && (in_array("banana", $favoritefood))) { echo "checked"; } ?> />
<label for="bananaid">Banana</label>
</li>
</ol>
<button type="submit" name="action" value="submit">send</button>
</form>
Drop-down menu is blocked by bottom line
Drop-down menu is blocked by bottom line in Canhealth
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_css\cesei.css, line 93 change
#left-rail { width: 200px; padding-top: 5px; padding-bottom: 115px; }
to
#left-rail { width: 200px; padding-top: 5px; padding-bottom: 225px; }
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_css\cesei_template.css
and C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_css\cesei_default.css
line 90 change
#left-rail { width: 200px; padding-top: 5px; }
to
#left-rail { width: 200px; padding-top: 5px; padding-bottom: 225px; }
Add Advisory Board under Governance tab
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_templates\cesei_nav0.tmpl.php, line 64 add
<li><a href="/advisory.php"> Advisory Board</a></li>
create advisory.php
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_lib\edit_webcontent.php, line 146 add
else if ($content_id==13) {
header('Location: /advisory.php');
exit();
}
line 24 add
if (preg_match("/advisory.php/i",$_SERVER['PHP_SELF'])) $content_id=13;
line 69 add:
else if($content_id==13)
$sql_11="INSERT INTO web_content(content_id, revision, title, text) VALUES (13,0, 'advisory.php', 'TBD')";
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_templates\cesei_nav_lang.tmpl.php
Add fundraising committee
Add fundraising and sponsorship committee at Canhealth. In cesei_committee table
add committee_id: 2, committee_name: Fundraising and Sporsorship; committee_description: fundraising.php
modify /_lib/edit_webcontent.php, line 20 change
if (preg_match("/membership_committee.php/i",$_SERVER['PHP_SELF'])) $content_id=9;
to
if (preg_match("/fundraising.php/i",$_SERVER['PHP_SELF'])) $content_id=9;
line 61 change
$sql_11="INSERT INTO web_content(content_id, revision, title, text) VALUES (9,0, '/committee/membership_committee.php', 'TBD')";
to
$sql_11="INSERT INTO web_content(content_id, revision, title, text) VALUES (9,0, '/committee/fundraising.php', 'TBD')";
line 131 change
header('Location: /committee/membership_committee.php');
to
header('Location: /committee/fundraising.php');
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_templates\cesei_nav0.tmpl.php, line 143 change
<li><a href="/committee/membership_committee.php?edit=1&content_id=9"> Membership Committee</a></li>
to
<li><a href="/committee/fundraising.php?edit=1&content_id=9">Fundraising Committee</a></li>
line 184 change
<li class="tier0"><a href="/committee/">Committees</a>
to
<li class="tier0"><a href=" ">Committees</a>
line 209 change
<li id="nav-logged-in-research" class="tier0-sub"><a href="/committee/"><?php echo(INSTITUTION_SHORT_NAME); ?> Collaboratives</a>
to
<li id="nav-logged-in-research" class="tier0-sub"><a href=""><?php echo(INSTITUTION_SHORT_NAME); ?> Collaboratives</a>
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_templates\cesei_nav_lang.tmpl.php, line 189, change
elseif(htmlspecialchars($committee['committee_name']) =='Membership'){
echo "<li><a href='/committee/".$committee['committee_description']."'>".$map['Memberships']."</a></li>";
to
elseif(htmlspecialchars($committee['committee_name']) =='Fundraising and Sponsorship'){
echo "<li><a href='/committee/".$committee['committee_description']."'>".$map['Fundraising']."</a></li>";
line 243 change
echo "<li id='nav-logged-in-research' class='tier0-sub'><a href='/committee/'>".htmlspecialchars($committee['committee_name'])."</a><ul>";
to
echo "<li id='nav-logged-in-research' class='tier0-sub'><a href=' '>".htmlspecialchars($committee['committee_name'])."</a><ul>";
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\languages\trans_to_french\CanHealth_nav.french.php,
line 142 add
$map['Fundraising']='collecte de fonds et commandites';
Monday, February 24, 2014
upload file location
upload images location from Canahealth in general pages
C:\uploads\CNSH_file_repository\proposals\page\1
define('AT_CONTENT_DIR', 'C:\\Users\\jiansen\\Desktop\\real-server\\cesei_file\\canhealth_file_repository')
also \proposals\committee which does not exist in CESEI.
Saturday, February 22, 2014
Normalization in database design
1nf: no repeating values and no repeating groups,
2nf( second normal form,) mainly for composite key, no values based on just part of say half of a composite key,
3nf: none of your non-key values should be based on or determined from another non-key value.
Friday, February 21, 2014
Referential integrity in mysql
Referential integrity in mysql:
http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html
In student table:
student_id is primary key, teacher_id is foreign key
When we enter teacher_id in student table, we want to make sure teacher_id exsits in teacher table,
There are difference for InnoDB, NDB and MyISAM storage engines.
For InnoDB, we used
FOREIGN KEY (teacher_id) REFERENCES teacher (teacher_id)
For MyISAM
PRIMARY KEY (student_id, teacher_id)We may also Cascade UPDATES and DELETES in programming.
Modify schdule tasks usng Windows 2003 server scheduler graphic interface
Schduled task gnsh_backup starting 10/20/2011, backup everyday 10:10pm to
C:\sql_dumps\mysql_admin
Modify it to canhealth_backup.
In Windows 2003 server, click Start-> Programs->Accessories->System tools->Scheduled Taks
Rename gnsh_back to canhealth_backup.
Under properties, change
"C:\Program Files\MySQL\MySQL Tools for 5.0\MySQLAdministrator.exe" "-UDC:\Documents and Settings\Administrator\Application Data\MySQL\" "-cRoot" "-bpgnsh_backup" "-btC:\sql_dumps\mysql_admin\" "-bxgnsh_
to
"C:\Program Files\MySQL\MySQL Tools for 5.0\MySQLAdministrator.exe" "-UDC:\Documents and Settings\Administrator\Application Data\MySQL\" "-cRoot" "-bpcnsh_lms" "-btC:\sql_dumps\mysql_admin\" "-bxcanhealth_
Note -bpcnsh_lms
I defined Backup projects namecnsh_lms under Tools ->MySQL Administrator - Connection->Backup for canhealth
Input admin password to appl change
Thursday, February 20, 2014
SVN (tortoise SVN) and CVS tutorial
1) How to Work with SVN
2. CVS tutorial CVS is obsolete now. Please use SVN. Most of the concepts for CVS are still applicable to SVN. Why to use version control? What is a repository? How to checkout? How to commit? How to update? How to see the differences between different versions?
Wednesday, February 19, 2014
Introduction to computer network (videos)
1) Basic computer network concept
2) IP Address & Subnet Mask Basics
In this video you will learn the basics of IP addresses. We will talk about the IP protocol, how an IP address looks in binary and dotted decimal, binary to dotted decimal conversion, dotted decimal to binary conversion, IP address classes, private IP address space, subnet masks, and CIDR notation
3)Intro to TCP/IP
Monday, February 17, 2014
update the new site
2). Before log in, the only tabs we should see in the menu bar are
-CanHealth
-Committees
-Log in
remove education, research healthcare
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_templates\cesei_nav0.tmpl.php
line 57, remove
<li><a href="/member_directory/index.php"> Existing Members </a></li>
line 57, remove
<li><a href="/member_directory/index.php"> Existing Members </a></li>
line 27 change
<li id="nav-cesei" class="tier0"><a href="/launch.php"><?php echo(INSTITUTION_SHORT_NAME); ?></a>
to
<li id="nav-cesei" class="tier0"><a href="/"><?php echo(INSTITUTION_SHORT_NAME); ?></a>
line 519 change
<?php endif;?>
to
to
<?php endif;?>
<?php if (isset($_SESSION['portal_status']) &&
$_SESSION['portal_status']['research'] > STATUS_UNCONFIRMED
&& $_SESSION['member_id'] != GUEST_USER_ID): ?>
line 289 change
<li id="nav-education" class="tier0"><a href="/education/">Education</a>
<li id="nav-education" class="tier0"><a href="/education/">Education</a>
to
<?php if(isset($_SESSION['login'])): /* EDUCATIONAL PORTAL LINKS */ ?>
<li id="nav-education" class="tier0"><a href="/education/">Education</a>
<?php if(isset($_SESSION['login'])): /* EDUCATIONAL PORTAL LINKS */ ?>
<li id="nav-education" class="tier0"><a href="/education/">Education</a>
line 64 remove
<li><a href="/advisory_board.php"> Advisory Board</a></li>
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\_templates\cesei_nav_lang.tmpl.php, line 301, add
<?php if(isset($_SESSION['login'])): /* EDUCATIONAL PORTAL LINKS */ ?>
line 575 add
<?php endif; ?>
line 195 remove
echo '<li><a href="/help.php?pid=3">Help</a></li>';
line 203, comment
// echo '<li><a href="/help.php?pid=3">'.$map['Help'].'</a></li>';
line 140 remove
<li><a href="/advisory_board.php?edit=1&content_id=6"> Advisory Board</a></li>
line 28 change
<li id="nav-cesei" class="tier0"><a href="/launch.php"><?php echo(INSTITUTION_SHORT_NAME); ?></a>
to
<li id="nav-cesei" class="tier0"><a href="/"><?php echo(INSTITUTION_SHORT_NAME); ?></a>
line 58, remove
<li><a href="/member_directory/index.php"> <?php echo $map['Existing Members']; ?> </a></li>
3) In the Committees tab in the menu bar, please delete the MEMBERSHIP tab
<li><a href="/member_directory/index.php"> <?php echo $map['Existing Members']; ?> </a></li>
3) In the Committees tab in the menu bar, please delete the MEMBERSHIP tab
Delete FROM cnsh_lms.cesei_committee where committee_id=2;
4)
If you go CanHealth / Membership / Apply for Membership, the title
currently reads INDIVIDUAL MEMBERSHIP REGISTRATION. This should be
changed to read INSTITUTION MEMBERSHIP REGISTRATION.
http://localhost:8083/registration.php?type=2
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\registration.php, line 57
change
$pType = 'Individual Membership Registration';
to
$pType = 'Institution Membership Registration';
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_canhealth\canhealth\registration.php, line 57
change
$pType = 'Individual Membership Registration';
to
$pType = 'Institution Membership Registration';
5) After login, the Welcome to CanHealth page should appear next to the menu again.
6) In CanHealth tab / Governance - please delete the Advisory Board tab
Transfer modules between sites
Use MySQL dump, error message:
"The dump file was not created by MySQL Administrator and could contain unsupported syntax. Hence we cannot restore its content. You can override this behavior by enabling "Ignore errors" on the General page."
Change to command line:
C:\Program Files (x86)\MySQL\MySQL Server 5.0\bin>
mysql -u jiansen -p cnsh_lms < C:\Users\jiansen\Documents\transferrmodule.sql
find the errors in input sql files
1.
INSERT INTO `cesei_course_modules` (`course_id`,`module_id`) VALUES
(4,123);
2.
INSERT INTO `cesei_course_targets` (`course_id`,`target_id`) VALUES
(123,63);
3.
INSERT INTO `cesei_module_sharing` (`course_id`,`institution_id`) VALUES
(123,1);
4.
INSERT INTO `edu_content`
(`content_id`,`course_id`,`content_parent_id`,`ordering`,`last_modified`,`revision`,`formatting`,`release_date`,`keywords`,`content_path`,`title`,`text`,`inherit_release_date`) VALUES
(769,123,0,1,'2012-03-15 11:05:11',0,1,'2012-03-15 11:00:00','','','Welcome to CESEI','Welcome to the CESEI LMS.
INSERT INTO `edu_courses` (`course_id`,`member_id`,`created_date`,`title`,`description`,`max_quota`,`max_file_size`,`hide`,`copyright`,`primary_language`,`rss`,`status`) VALUES
(123,1,'2012-03-15','TEST-200: Case Study Test10','test','-2','-3',1,'','en',1,'development');
update `cnsh_lms`.`edu_courses` SET member_id=1;
Thursday, February 13, 2014
MySQL workbench -"Could not decrypt password cache"
In MySQL workbench, whenever I open a new connection, a message "Could not decrypt password cache" popup. To let this message, go to
to C:\Users\your_username\AppData\Roaming\MySQL\Workbench\
delete workbench_user_data.dat
In my case, I login as Administrator, go to C:\Documents and Settings\Administrator\Application Data\MySQL\Workbench, delete workbench_user_data.dat
Restart MySQL workbench. Enter password and check save password in vault. The message "Could not decrypt password cache" will not appear.
Reference:
http://forums.mysql.com/read.php?152,389642,404404
Apache Web service in Windows 2003 server too slow, restart
Windows 2003 server for cesei is too slow.
1) First check C:\php5.3.1\error.log, file size around 4MB OK
2) Second check C:\Program File\Apache 2.2\log
file size OK
3) Click Start->Shut down-
Under What do you want computer what to do
select Restart and write a comment.
Windows 2003 server is restarted.
Error 1069 the service did not start due to a logon failure
The error means the password is incorrect. Does the Application when installed have any other references to any other (older) account? Also maybe the account is disabled or expired.
Enable all other accounts and make them all not expired. Problem fixed.
Next time, before restart
click Start->Windows update, then restart windows 2003 server.
I have 2 other sftp accounts, and I want to disable them
edit C:\Program Files\OpenSSH\etc\passwd
remove the user not in sftp list, under service, restart OpenSSH
Wednesday, February 12, 2014
New activity log bug
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\admin\activity_log_new\reports.php, line 46 change
FROM cesei_activity_data AD
to
FROM cesei_activity_data_new AD
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\admin\activity_log_new\report_csv.php, line 161 change
$sql = "SELECT AD.*, DT.classification, DT.data_name
FROM cesei_activity_data AD
LEFT JOIN cesei_activity_data_types DT ON AD.enum_id=DT.enum_id
WHERE AD.event_id IN($event_ids) AND AD.count > 0";
to
$sql = "SELECT AD.*, DT.classification, DT.data_name
FROM cesei_activity_data_new AD
LEFT JOIN cesei_activity_data_types_new DT ON AD.enum_id=DT.enum_id
WHERE AD.event_id IN($event_ids) AND AD.count > 0";
line 191 change
$sql = "SELECT $prop_filters FROM cesei_activity WHERE event_id IN($event_ids) ORDER BY UNIX_TIMESTAMP(start_date)";
to
$sql = "SELECT $prop_filters FROM cesei_activity_new WHERE event_id IN($event_ids) ORDER BY UNIX_TIMESTAMP(start_date)";
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\admin\activity_log_new\report_csvD.php, line 35 change
$mydata=unserialize(html_entity_decode($_POST['mydata']));
foreach($mydata as $val0) foreach($val0 as $key=>$val){echo str_replace(',','',$val);
to
//$mydata=unserialize(html_entity_decode($_POST['mydata']));
$mydata=unserialize($_POST['mydata']);
foreach($mydata as $val0) foreach($val0 as $key=>$val){echo str_replace(',','',html_entity_decode($val));
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\admin\activity_log_new\report_pdfD.php, line 78 change
$mydata=unserialize(html_entity_decode($_POST['mydata']));
foreach($mydata as $val0) foreach($val0 as $key=>$val){
if($key=='name') $tbl = $tbl.'<tr><td>'.$val.'</td><td>';
elseif($key=='CME') $tbl = $tbl.$val.'</td></tr>';
else $tbl = $tbl.$val.'</td><td>';
to
$mydata=unserialize($_POST['mydata']);
foreach($mydata as $val0) foreach($val0 as $key=>$val){
if($key=='name') $tbl = $tbl.'<tr><td>'.html_entity_decode($val).'</td><td>';
elseif($key=='CME') $tbl = $tbl.html_entity_decode($val).'</td></tr>';
else $tbl = $tbl.html_entity_decode($val).'</td><td>';
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\_templates\activity_log_reportD.tmpl.php, line 211 change
if($classification == 'learner' && count($data) > 1){
to
if($classification == 'learner' ){
Department database design (13) - Create graduate student database
Create new table students:
DROP TABLE IF EXISTS `ubc_surgery_pub`.`students`;
CREATE TABLE `ubc_surgery_pub`.`students` (
`id` int(11) unsigned NOT NULL auto_increment,
`firstname` varchar(127) NOT NULL default '',
`lastname` varchar(127) NOT NULL default '',
`position` varchar(63) NOT NULL default '',
`program` varchar(100) NOT NULL default '',
`degree` varchar(63) NOT NULL default '',
`email` varchar(127) NOT NULL default '',
`pi_id` int(11) unsigned NOT NULL default 0,
PRIMARY KEY (`id`),
KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\manage\index.php, line 548 add
<li><a href="./?action=importcsvstudent">Import students via csv file </a></li>
line 97 add
case 'importcsvstudent':
require_once('superadmin/importcsvstudent.php');
break;
copy superadmin/importcsvuser.php to superadmin/importcsvstudent.php and modify
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\manage\common.php
Tuesday, February 11, 2014
Department database design (12) - improve search interface
First space between column is too c rowdy.
http://localhost/ubc_surgery_pub/scripts/manage/?action=listpeople&deptid=1
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\manage\index.php, line 29 change
echo "<table border=\"0\"><tr><td valign=\"top\">";
to
echo "<table border=\"0\"><tr><td width=20% valign=\"top\">";
line 30 change
echo "\n </td><td valign=\"top\">";
to
echo "\n </td><td width=20% valign=\"top\">";
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\query\searchform.inc.php, line 161 remove
<label><input type="checkbox" name="eprintsonly" value="y" />
Eprints only</label></p>
line 170 change
<label>Author search:<br /><input name="namesearch" type="text" id="namesearch" /></label>
to
<label>Author search:<br /></label>
<select name="namesearch">
<option value=""> --- Choose a person --- </option>
<?php
foreach(getallpeople1() as $k=>$v){
$numpublished = countPubsPerUser2($k);
echo "\n<option value=\"$k\">$v[LASTNAME],$v[FIRSTNAME] ($numpublished)</option>";
}
?>
</select>
line 227 add
function getallpeople1()
{
global $dbcon;
$res = mysql_query("SELECT USERID, FIRSTNAME, LASTNAME FROM USERS ORDER BY LASTNAME, FIRSTNAME", $dbcon);
$ret = array();
while($row = mysql_fetch_assoc($res))
$ret[$row['USERID']] = $row;
return $ret;
}
function countPubsPerUser2($userids){
global $dbcon;
$userids = mysql_real_escape_string($userids);
$q = "SELECT COUNT(*) AS cnt, userlist FROM PUBLICATIONS WHERE FIND_IN_SET('$userids',userlist)";
if(!($res = mysql_query($q, $dbcon)))
return 0;
while($row = mysql_fetch_assoc($res)){
$counts= $row['cnt'];
}
return $counts;
}
?>
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\query\displayqueryterms.inc.php, line 67 change
if(strlen($namesearch)>0){
$querystrings[] = "<em>" . htmlspecialchars(stripslashes($namesearch)) . "</em> in authors";
to
if(strlen($namesearch)>0){
$tempdbcon = mysql_connect($config['db_addr'],$config['db_user'],$config['db_pass']);
@mysql_select_db($config['db_db']);
$q = "SELECT firstname, lastname FROM USERS WHERE userid=$namesearch";
$res = mysql_query($q, $tempdbcon);
$deptsnames = array();
while($row = mysql_fetch_assoc($res))
// $querystrings[] = "<em>" . htmlspecialchars(stripslashes($namesearch)) . "</em> in authors";
$querystrings[] = "<em>" . $row['lastname'].','.$row['firstname']. "</em> in authors";
@mysql_close($tempdbcon);
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\query\index.php, line 288 change
$wh .= "CONCAT_WS(' ', authorlist, secondaryauthorlist, seriesauthorlist) LIKE '%$vv%' AND CONCAT_WS(' ', authorlist, secondaryauthorlist, seriesauthorlist) REGEXP '$rvv' AND ";
to
$wh .= " CONCAT_WS(' ', userlist) REGEXP '$rvv' AND ";
Wednesday, February 5, 2014
Windows 2008 server different ftp users to different directory
ftp directory D:\CESEIFTP, create folder D:\CESEIFTP\bcsimftp
1) Create new user bcsimftp, profile path and Local path under Profile D:\CESEIFTP\bcsimftp
D:\CESEIFTP\bcsimftp directory security inder properties, full control for bcsimftp
2) Go to IIS manage, a ftp site already created for another user..
Add virtula directory
Alias: bcsimftp
Physical Path D:\\CESEIFTP\bcsimftp
connect as specific user, click set put user name bcsimftp and password
check Test Settings, both OK,
3) Double click ftp User Isolation, choose "User name Directory"
Now ftp for bcsimftp, go to D:\CESEIFTP\bcsimftp
repeat the same process, to create another bcsimftp2 user, create folder D:\CESEIFTP\bcsimftp2
change file permission D:\CESEIFTP\bcsimftp2 only for bcsimftp2, D:\CESEIFTP\bcsimftp only for bcsimftp, you may have some error message about inherit, go to file properties->security->Edit->Change special permission, unchecked in inherited from parents.
Now the ftp will go to home directory D:\CESEIFTP\bcsimftp or D:\CESEIFTP\bcsimftp2,
if you go up directory, you can see two directories, but one access is denied.
PHP: transfer long data to url variable
In the following example, I have a pubmed abstract stored in $choppeddata and I want to transfer to a url variable in a popup up windows. Later I can use $_GET to obtain the value. PHP function urlencode is used to deal with space and non-alphanumeric characters properly.
code
<?php
$chosenperson = 2255;
$choppeddata = "1: Park YJ, Woo M, Kieffer TJ, Hakem R, Safikhan N, Yang F, Ao Z, Warnock GL, Marzban L. The role of caspase-8 in amyloid-induced beta cell death in human and mouse islets. Diabetologia. 2014 Jan 19. [Epub ahead of print] PubMed PMID: 24442508."
$puburldata=urlencode($choppeddata);
?>
<a class="txt-button" onclick="javascript:void window.open('test.php?person=<?php print $chosenperson; ?>&pubabstract=<?php print $puburldata; ?>','child','width=900,height=500,resizable=false,left=0,top=0' );return false;">To add a new pubmed, please click this link and fill in its details</a>
Department database design (11) - manully add during import
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\manage\pubmedimport\index.php, line 237 add (for updating department list)
if(strpos($existing['deptlist'], $chosendept)===false)
mysql_query("UPDATE PUBLICATIONS SET deptlist='"
. mysql_real_escape_string($existing['deptlist'] . "$chosendept,")
. "' WHERE pubid=$existing[pubid] LIMIT 1", connectpubsdb());
line 206 add
$puburldata=urlencode($choppeddata[0]);
?>
<a class="txt-button" onclick="javascript:void window.open('insertpubmed.php?person=<?php print $chosenperson; ?>&pubabstract=<?php print $puburldata; ?>','child','width=900,height=500,resizable=false,left=0,top=0' );return false;">To add a new pubmed manually, please click this link and fill in its details</a>
<?php
new file
C:\Users\jiansen\Desktop\CAS\jiansen_dir\www_cesei0_mobile\ubc_surgery_pub\scripts\manage\pubmedimport\insertpubmed.php
<?php
session_start();
if(isset($_SESSION['username']))
$_SERVER['REMOTE_USER']='jiansen';
else{
echo 'You do not have adminsitraction right. <br />';
echo "\n<p style='text-align:left;'>[<a href='../../../demosite/'>Back to publication homepage</a>]</p>";
exit();
}
require_once(dirname(dirname(__FILE__)) . '/common.php');
$person=$_GET['person'];
$pubabstract=$_GET['pubabstract'];
if($_POST['submitpub']){
$person=$_POST['person'];
$ret = array('reftype'=>array('JOUR'),
'notes'=>array('Imported from PubMed ' . date('d/m/Y'))
);
$ret['url'] = 'pm:'.$_POST['pubmedid'];
$ret['approvedby'] = mysql_real_escape_string($_SERVER['REMOTE_USER']);
$date = date('Y-m-d H:i:s');
$ret['approvaldate'] = "$date";
$chosendept = getdivision($person);
$ret['deptlist'] = ",$chosendept,";
$ret['authorlist'] = $_POST['authorlist'];
$ret['userlist'][] = ",$person,";
$ret['authorlist'] = $_POST['authorlist'];
$ret['title'] = $_POST['title'];
$ret['journal'] = $_POST['ptitle'];
$ret['year'] = $_POST['year'];
$ret['yearother'] = $_POST['monthday'];
$ret['volume'] = $_POST['volume'];
$ret['issue'] = $_POST['issue'];
$ret['startpage'] = $_POST['startpage'];
$ret['endpage'] = $_POST['endpage'];
$q = "SELECT pubid, url, deptlist, userlist FROM PUBLICATIONS WHERE url='" . mysql_real_escape_string($ret['url']) . "' LIMIT 1";
$res = mysql_query($q,connectpubsdb());
if($res && (mysql_num_rows($res)!=0)){
$existing = mysql_fetch_assoc($res);
if(strpos($existing['userlist'], $person)===false)
mysql_query("UPDATE PUBLICATIONS SET userlist='"
. mysql_real_escape_string($existing['userlist'] . "$person,")
. "' WHERE pubid=$existing[pubid] LIMIT 1", connectpubsdb());
if(strpos($existing['deptlist'], $chosendept)===false)
mysql_query("UPDATE PUBLICATIONS SET deptlist='"
. mysql_real_escape_string($existing['deptlist'] . "$chosendept,")
. "' WHERE pubid=$existing[pubid] LIMIT 1", connectpubsdb());
echo "\n<p>The record with PubMed ID <a href=\"$config[pageshomeurl]manage/?action=edit&pubid=$existing[pubid]\">{$current[url][0]}</a> (<strong><em>"
. htmlspecialchars($current['title'][0]) . "</em></strong>) is already in the database, so rather than inserting a duplicate, "
. " the system has associated your userid with the existing record.</p>";
}else{
insertentry($ret);
echo 'pm: '.$_POST['pubmedid'].' is added';
}
}else{
echo 'Person id:'.$person.'<br />';
echo $pubabstract;
}
?>
<form id="newform2" action = "<?php echo $_SERVER['PHP_SELF']; ?>" method="post" onsubmit="if(this.authorlist.value=='' || this.title.value==''|| this.ptitle.value==''|| this.pubmedid.value=='' )
{alert('Required fields:\n\n - author list\n - title\n - Periodical title\n - Pubmed id\n'); return false;} return true;">
To add a new pubmed, please fill in its details</a>.
</p>
<div id="newpubmed" style="margin: 5px 5px 5px 20px; padding: 1px; ">
<label style="display: block; float: left;">Author list(<font color="red">*</font>): <br />
<input name="authorlist" type="text" value="<?php echo $_POST['authorlist'];?>" ></label>
<label style="display: block; float: left;"> title:(<font color="red">*</font>): <br />
<input name="title" type="text" value="<?php echo $_POST['title'];?>" ></label>
<label style="display: block; float: left;">Periodical title:(<font color="red">*</font>): <br />
<input name="ptitle" type="text" value="<?php echo $_POST['ptitle'];?>" ></label>
<label style="display: block; float: left;">Year <br />
<input name="year" type="text" value="<?php echo $_POST['year'];?>" maxlength="128"></label>
<label style="display: block; float: left;">Month and day <br />
<input name="monthday" type="text" value="<?php echo $_POST['monthday'];?>" maxlength="128"></label>
<label style="display: block; float: left;">Volume: <br />
<input name="volume" type="text" value="<?php echo $_POST['volume'];?>" ></label>
<label style="display: block; float: left;">Issue: <br />
<input name="issue" type="text" value="<?php echo $_POST['issue'];?>" maxlength="128"></label>
<label style="display: block; float: left;">Start page: <br />
<input name="startpage" type="text" value="<?php echo $_POST['startpage'];?>" ></label>
<label style="display: block; float: left;">End page: <br />
<input name="endpage" type="text" value="<?php echo $_POST['endpage'];?>" ></label>
<label style="display: block; float: left;">pubmed id (<font color="red">*</font>):<br />
<input name="pubmedid" type="text" value="<?php echo $_POST['pubmedid'];?>" ></label>
<input name="person" type="hidden" value="<?php echo $person; ?>" >
<input type="submit" name="submitpub" value="Add new pubmed" style="display: block;clear:both;"/>
</div>
</form>
Tuesday, February 4, 2014
search newer file and add scheduler
In database server
C:\sql_dumps\sql_dump_script
create file jiansen_filecheck.bat, which contains:
set datetimef=%date:~-4%%date:~-10,2%%date:~-7,2%
echo %datetimef% >> filecheck_out.txt
forfiles /p C:\uploads\file_repository /s /D +0 /C "cmd /c echo @path" >> filecheck_out.txt
i.e. get the file list created today and send to file filecheck_out.txt
create scheduler
schtasks /create /tn "check new file" /tr C:\sql_dumps\sql_dump_script\jiansen_filecheck.bat /sc daily /st 23:00:00
change C:\sql_dumps\sql_dump_script\jiansen_ftp.bat to add filecheck_out.txt in ftp list
copy ftp_original.txt ftp1.txt
set datetimef=%date:~-4%%date:~-10,2%%date:~-7,2%
echo mput re*%datetimef%*.sql >>ftp1.txt
echo y >>ftp1.txt
echo mput ce*%datetimef%*.sql >>ftp1.txt
echo y >>ftp1.txt
echo bye >> ftp1.txt
ftp -s:ftp1.txt
to
copy ftp_original.txt ftp1.txt
set datetimef=%date:~-4%%date:~-10,2%%date:~-7,2%
echo mput re*%datetimef%*.sql >>ftp1.txt
echo y >>ftp1.txt
echo mput ce*%datetimef%*.sql >>ftp1.txt
echo y >>ftp1.txt
echo put C:\sql_dumps\sql_dump_script\filecheck_out.txt >>ftp1.txt
echo bye >> ftp1.txt
ftp -s:ftp1.txt
Create ftp batch script and add scheduler
In database serve, database is dumped into every 11:55 pm
C:\sql_dumps\mysql_admin
with timestamp 20140204 (year month day) .
Under C:\sql_dumps\sql_dump_script,
file ftp_original.txt
open 137.82.***.***
username
password
lcd C:\sql_dumps\mysql_admin
cd dbfile
jiansen_ftp.bat
copy ftp_original.txt ftp1.txt
set datetimef=%date:~-4%%date:~-10,2%%date:~-7,2%
echo mput re*%datetimef%*.sql >>ftp1.txt
echo y >>ftp1.txt
echo mput ce*%datetimef%*.sql >>ftp1.txt
echo y >>ftp1.txt
echo bye >> ftp1.txt
ftp -s:ftp1.txt
Create task to run jiansen_ftp.bat every night night 11:58 pm
schtasks /create /tn "ftp to UBC2" /tr C:\sql_dumps\sql_dump_script\jiansen_ftp.bat /sc daily /st 23:58:00
to check schedule tasks:
schtasks /QUERY /FO LIST /V
Reference for schtasks in Windows 2003 server:
http://support.microsoft.com/kb/814596
http://technet.microsoft.com/en-us/library/bb490996.aspx
Monday, February 3, 2014
Windows 2003 server scheduler
More about command in Windows 2003 server scheduler
http://support.microsoft.com/kb/814596
1) Start->Run
2) cmd to get command prompt
3) type
schtasks /QUERY /FO LIST /V
to query schedule
HostName: CESEI-DB
TaskName: bcsim_backup
Next Run Time: 11:45:00 PM, 2/3/2014
Status:
Logon Mode: Interactive/Background
Last Run Time: 11:45:00 PM, 1/31/2014
Last Result: 0
Creator: MySQL Administrator
Schedule: At 11:45 PM every Mon, Wed, Fri of every w
eek, starting 9/11/2007
Task To Run: C:\Program Files\MySQL\MySQL Tools for 5.0
\MySQLAdministrator.exe "-UDC:\Documents and Settings\Administrator\Application
Data\MySQL\" "-cRoot" "-bpbcsim_backup" "-btC:\sql_dumps\mysql_admin\" "-bxbcsim
_"
Start In: C:\Documents and Settings\Administrator\Ap
plication Data\MySQL\
Comment: N/A
Scheduled Task State: Enabled
Scheduled Type: Weekly
Start Time: 11:45:00 PM
Start Date: 9/11/2007
End Date: N/A
Days: MONDAY,WEDNESDAY,FRIDAY
Months: N/A
Run As User: CESEI\administrator
Delete Task If Not Rescheduled: Disabled
Stop Task If Runs X Hours and X Mins: Disabled
Repeat: Every: Disabled
Repeat: Until: Time: Disabled
Repeat: Until: Duration: Disabled
Repeat: Stop If Still Running: Disabled
Idle Time: Disabled
Power Management: Disabled
HostName: CESEI-DB
TaskName: cesei_backup
Next Run Time: 11:55:00 PM, 2/3/2014
Status:
Logon Mode: Interactive/Background
Last Run Time: 11:55:00 PM, 2/2/2014
Last Result: 0
Creator: MySQL Administrator
Schedule: At 11:55 PM every Mon, Tue, Wed, Thu, Fri,
Sat, Sun of every week, starting 9/11/2007
Task To Run: C:\Program Files\MySQL\MySQL Tools for 5.0
\MySQLAdministrator.exe "-UDC:\Documents and Settings\Administrator\Application
Data\MySQL\" "-cRoot" "-bpcesei_backup" "-btC:\sql_dumps\mysql_admin\" "-bxcesei
_"
Start In: C:\Documents and Settings\Administrator\Ap
plication Data\MySQL\
Comment: N/A
Scheduled Task State: Enabled
Scheduled Type: Weekly
Start Time: 11:55:00 PM
Start Date: 9/11/2007
End Date: N/A
Days: SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,F
RIDAY,SATURDAY
Months: N/A
Run As User: CESEI\administrator
Delete Task If Not Rescheduled: Disabled
Stop Task If Runs X Hours and X Mins: Disabled
Repeat: Every: Disabled
Repeat: Until: Time: Disabled
Repeat: Until: Duration: Disabled
Repeat: Stop If Still Running: Disabled
Idle Time: Disabled
Power Management: Disabled
HostName: CESEI-DB
TaskName: gnsh_backup
Next Run Time: 10:10:00 PM, 2/3/2014
Status:
Logon Mode: Interactive/Background
Last Run Time: 10:10:00 PM, 2/2/2014
Last Result: 0
Creator: administrator
Schedule: At 10:10 PM every day, starting 10/20/2011
Task To Run: C:\Program Files\MySQL\MySQL Tools for 5.0
\MySQLAdministrator.exe "-UDC:\Documents and Settings\Administrator\Application
Data\MySQL\" "-cRoot" "-bpgnsh_backup" "-btC:\sql_dumps\mysql_admin\" "-bxgnsh_
Start In: C:\Documents and Settings\Administrator\Ap
plication Data\MySQL\
Comment: N/A
Scheduled Task State: Enabled
Scheduled Type: Daily
Start Time: 10:10:00 PM
Start Date: 10/20/2011
End Date: N/A
Days: Everyday
Months: N/A
Run As User: CESEI\administrator
Delete Task If Not Rescheduled: Disabled
Stop Task If Runs X Hours and X Mins: Disabled
Repeat: Every: Disabled
Repeat: Until: Time: Disabled
Repeat: Until: Duration: Disabled
Repeat: Stop If Still Running: Disabled
Idle Time: Disabled
Power Management: No Start On Batteries, Stop On Battery Mod
e
HostName: CESEI-DB
TaskName: LiveUpdate
Next Run Time: 12:37:00 PM, 2/4/2014
Status:
Logon Mode: Interactive/Background
Last Run Time: 12:37:00 PM, 2/3/2014
Last Result: 4
Creator: administrator
Schedule: At 12:37 PM every day, starting 7/12/2006
Task To Run: C:\Program Files\Symantec\LiveUpdate\LUALL
.EXE
Start In: C:\Program Files\Symantec\LiveUpdate
Comment: N/A
Scheduled Task State: Enabled
Scheduled Type: Daily
Start Time: 12:37:00 PM
Start Date: 7/12/2006
End Date: N/A
Days: Everyday
Months: N/A
Run As User: CESEI\administrator
Delete Task If Not Rescheduled: Disabled
Stop Task If Runs X Hours and X Mins: 72:0
Repeat: Every: Disabled
Repeat: Until: Time: Disabled
Repeat: Until: Duration: Disabled
Repeat: Stop If Still Running: Disabled
Idle Time: Disabled
Power Management: No Start On Batteries, Stop On Battery Mod
e
HostName: CESEI-DB
TaskName: research_projects
Next Run Time: 11:55:00 PM, 2/3/2014
Status:
Logon Mode: Interactive/Background
Last Run Time: 11:55:00 PM, 2/2/2014
Last Result: 0
Creator: MySQL Administrator
Schedule: At 11:55 PM every Mon, Tue, Wed, Thu, Fri,
Sat, Sun of every week, starting 12/16/2008
Task To Run: C:\Program Files\MySQL\MySQL Tools for 5.0
\MySQLAdministrator.exe "-UDC:\Documents and Settings\Administrator\Application
Data\MySQL\" "-cRoot" "-bpresearch_projects" "-btC:\sql_dumps\mysql_admin\" "-bx
research_projects"
Start In: C:\Documents and Settings\Administrator\Ap
plication Data\MySQL\
Comment: N/A
Scheduled Task State: Enabled
Scheduled Type: Weekly
Start Time: 11:55:00 PM
Start Date: 12/16/2008
End Date: N/A
Days: SUNDAY,MONDAY,TUESDAY,WEDNESDAY,THURSDAY,F
RIDAY,SATURDAY
Months: N/A
Run As User: CESEI\administrator
Delete Task If Not Rescheduled: Disabled
Stop Task If Runs X Hours and X Mins: Disabled
Repeat: Every: Disabled
Repeat: Until: Time: Disabled
Repeat: Until: Duration: Disabled
Repeat: Stop If Still Running: Disabled
Idle Time: Disabled
Power Management: No Start On Batteries, Stop On Battery Mod
e
Subscribe to:
Posts (Atom)