阅读视图

发现新文章,点击刷新页面。

博客: 疏通下水管道

这几天大雨,发现外墙的下水管道堵住了,雨水从漏斗里涌出来顺着墙面下泄,今天趁着雨停我试图自己动手疏通一下,找了根空心的蚊帐杆(薄钢管)往下试探,大概在漏斗两米多的位置找到了堵塞点,堵的严严实实一滴水都不能漏,实际情况显然比我想象的严重,堵塞物异常坚硬,无法攻不破。这些堵塞物类似山洞里的钟乳石,来源一方面应该是水泥冲刷出的钙化物,另一个重要来源应该是我之前养花搞过的几袋火山石,经雨水冲刷流失进入下水管道,时间长了沉积下来变成了坚硬的石头。

我想过购买盐酸来腐蚀,考虑到会有一些副作用,不安全也不一定能成功,最后放弃。下午在网上搜索疏通下水管的视频,看到有使用电钻驱动类似弹簧的带钻头的软管一类的装置以此达到破碎疏通的作用,这类软管淘宝上有卖,价格是按米计算的,不贵,我手里是有一个电钻的,后来考虑要不要买个软管自己动手,再三考虑又放弃了,觉得不一定能成功。

晚饭前我拨打了下水道疏通小广告上的电话,对方问了情况非常肯定地说能通,我问价格开价200,我表示价格贵要考虑下,对方显然是不想错过这单,说先过来看好弄就收个百来十块钱,不好弄就200,现在就来。他很快就到了,带了一台220V的电机,还有一卷细细的软管,试了一下,无济于事,说不好弄得上粗的软管,要收200块,我感觉这是他的惯用操作,很快他搬过来一盘粗软管,这软管比网上卖的那种要粗很多,装到那台电机上,它开始疯狂下钻,下水管道被它打的哒哒响,大约半分钟吧,果然通了,一瞬间心里的忧虑被化解了,我想我大概感受到结石患者的一部分痛苦了。

大概就是这样,倘若我选择买那个软管自己动手,有可能花上一些时间也能搞通,前提是得多花时间。另一方面这类服务由于不常见也不常用得到,所以价格一点都不透明,好一些的办法是多找几家对比,或者一开始就谈好价,就这个钱你看要不要干,不干我再找别人,以免他坐地起价。

博客: 扬州炒饭

最近带家人去扬州,本想领家人们尝尝地道的扬州炒饭,结果发现之前常去的那家扬州炒饭不在了,打听得知早在疫情刚结束就关闭了。这家店原本开在广陵区政府边上,斜对着石塔寺的那棵大银杏树。老板是扬州本地人,由于老板信佛,佛教文化是戒酒戒肉的,他家的饭食里是没有荤菜的,因此,扬州当地的朋友说他家的扬州炒饭严格来说不是正宗的。对酒也不例外,他家不卖酒,并且不允许客人在店内饮酒,店里会提供那种介绍素食做法的食谱小册子供食客免费取阅,你要是喜欢也可以带走。即便如此,在我心中他仍是一家有特色的扬州炒饭,可惜啊。

博客: 使用 iOS 的快捷指令发布 WordPress 日志

意思就是利用 iOS 的快捷指令发日志,不用装 WordPress APP,并且还能实现一些有意思的功能。原理是利用 iOS 的快捷指令调用 WordPress 的 REST API 日志接口。

只需要动手做两个一次性的配置即可

第一步,创建用于验证的应用程序密码

  1. 登录 WordPress 后台,在【用户】-【个人资料】页面找到【应用程序密码】,在【新的应用程序密码名称】下方的文本框里随便输入一个标记名称,点下方的【添加新的应用程序密码】按钮,此时,WordPress 会为刚输入的名称创建一个密码,这个密码是接下来用来验证 REST-API 接口的;
  2. 把刚刚创建的密码记下来,去掉空格,在密码前面加上当前登录的 WordPress 用户名以冒号隔开,比如我登录 WordPress 后台的用户名是root,刚刚创建的应用程序密码是gttc Pvt4 OTD1 8HiE Q3x0 w9pv,处理后就是root:gttcPvt4OTD18HiEQ3x0w9pv
  3. 将上一步得到的这串文本进行 Base64 编码,搜一下【base64在线编码】转一下。拿到转换后的文本串,在最前面加上Basic中间加个空格(得到类似这样的文本串Basic cm9vdDpndHRjUHZ0NE9URDE4SGlFUTN4MHc5cHY=),至此,第一步的准备工作已经完成。

第二步,配置快捷指令的接口地址和验证信息

  1. 点后方链接安装本人分享的快捷指令:https://www.icloud.com/shortcuts/2779823e2c724396ad4dd9dfbf43e24e
  2. 点快捷指令右上角的三个点,进入编辑状态,往下滑找到里面有个url键的【词典】块,将url后面的 url 地址改成你自己的,就是你的域名加上/?rest_route=/wp/v2/posts,比如我的就是
    https://域名.com/?rest_route=/wp/v2/posts
  3. 词典下面那个块是【网络】,展开,【头部】下面有个Authorization的键,后面的值用第一步准备好的那串 Base64 文本串替换掉,点右上角的【完成】,大功告成。

接下来,就可以点刚刚的快捷指令按提示发日志了,但是慢着,看下下面的注意事项。

注意
1、这个快捷指令只能算是玩玩,不可过度依赖,受网络等不可控因素影响,若发布失败将无法保留草稿记录;
2、用这个快捷指令发日志,需要开定位服务,这个快捷指令会利用定位通过 iOS 的天气服务获取天气、GPS、所在城市等信息,记录在日志的自定义字段里;
3、REST API 日志发布接口对自定义字段的处理策略是先注册再使用,只有已注册的自字段是才会被写入到日志的字段值字段里的,下方的代码,正是用来注册上方快捷指令里的那些自定义字段的,放到主题文件的 function.php 即可。

/**
 * Register meta keys for posts
 * rest api posts 接口只能在 meta 中使用这里注册的自定义字段
 */
function register_brave_post_meta() {
	// 这些是需要注册的自定义字段
	$brave_post_meta = array('geo_latitude', 'geo_longitude', 'geo_city', 'geo_public', 'wx_weather', 'wx_temp', 'post_device_name', 'post_device_ver');
	$arg = array(
					'single'       => true,
					'type'         => 'string',
					'default'      => '',
					'show_in_rest' => true,
				);
	array_walk($brave_post_meta, function ($val, $key, $arg) {register_meta('post', $val, $arg);}, $arg);
}

add_action('init', 'register_brave_post_meta');

博客: WordPress 自定义 rest api 接口

在 rest api 出现之前,想暴露一个接口需要先使用 wp_ajax_nopriv_wp_ajax_ 这两个钩子定义接口,再通过 admin-ajax.php 调用之(调用形式固定为 http://127.0.0.1/wp-admin/admin-ajax.php?action=),有了 rest api 就不用受此限制了,可玩性也大大提高了。

使用 rest api 只需要定义自己的路由和处理函数,就可以通过自定义链接来访问自己的接口了。

注册路由

在主题文件的 function.php 文件中加入下方的钩子,以分别定义 test 和 test2 两个路由

register_rest_route(
		'/v1', // 命名空间, 注意: wp 这两个字符为系统保留使用, 不建议在这里使用
		'/test', // 路由基础路径
		array(
			'methods'  => 'GET,POST', // HTTP METHOD, 支持逗号分割的字符串, 或字符串数组, 如: 'GET,POST' 或 array('POST', 'PUT');
            'callback' => 'func_demo1', // 处理路由请求的最终处理函数
			'permission_callback' => '__return_true', // 这是一个回调函数, 若对外公开需要返回 true; 否则, 返回 false. 可以通过此回调函数来判断处理用户权限. for security
		),
	);
	register_rest_route(
		'/v1',
		'/test2',
		array(
			'methods'  => 'POST',
            'callback' => 'func_demo2',
			'permission_callback' => '__return_true',
		),
	);

add_action('rest_api_init', 'register_brave_router');

添加处理函数

只有路由还不行,还要有对应的路由处理函数,下面在主题文件的 function.php 文件中分别添加 test 和 test2 的路由处理函数。

// test 的处理函数
function func_demo1 () {
	return array('key' => 'hello');
}

// test2 的处理函数
function func_demo2 ($request) {
	$data = wp_unslash($request->get_json_params()); // 取出来接口传入的参数
	return $data; // 返回接口传入的参数
}

访问自定义的接口

有两种方式访问刚刚添加的接口,一种是默认形式链接,一种是固定连接,分别如下

http://127.0.0.1/?rest_route=/v1/test
http://127.0.0.1/wp-json/v1/test

如果使用后者将得到下图这样的响应结果:

自定义前缀

如果不想要 wp-json 这样的前缀,wp 也提供了自定义的钩子,下方的钩子(同样,需要添加到主题文件的 function.php 文件中)将把 wp-json 改为 api。
注意:更改这里的前缀需要到【设置】-【固定链接】, 点一下保存【更改按钮】, 以刷新路由重定向规则使之生效

function rename_brave_rest_url_prefix() {
	return 'api';
}

add_filter('rest_url_prefix', 'rename_brave_rest_url_prefix');

博客: 理解

不小心咬破了口腔,最近几天用了这药我终于理解影视剧里那些任凭敌人严刑拷打拒不屈服,往往敌人抓上一小撮盐往伤口上一撒便失节丧气的人了。
药瓶

博客: 献血记

今天午饭过后我去血站献了血,献血的原因很直接,老丈人生病住院需要用血。医院告知血液紧张暂时没血,若想尽快用血,就需要家人朋友去血站献血。

去年11月初家人在另一所医院住院,隔壁床位需要用血的老太太也遇到了缺血的问题,同样医生也是催促家属献血。

上述献血行为是定向的,需要献血人在血站告知和登记自己献血的目的是为哪个医院哪个病区几号床的病人供血,并且,不要求献血人跟用血人的血型一致,哪怕病人是O型血,献血人是B型血,也是可以的,亲属献了血病人就有血液可用了。这里有些事情不能明说,总之,就是这样。

献血前一定要吃饭,献血人最近不能感冒发烧,没生过重大疾病,不曾感染过特定的传染性疾病,年龄在18-55岁之间,体重不低于100斤,前一天没有熬夜喝酒,血站工作人员一一询问确认这些情况,核对献血人的身份信息(要带上身份证)。然后,献血人扫二维码填写个人信息阅读确认献血须知,确认献血量(分别有200毫升、300毫升、400毫升三档可选,不过血站的人会尽量推荐你献300毫升或400毫升,其实坚持选200毫升也是可以的);最后,血站人员会采集献血人的血液现场化验,大约1分钟左右便可知道献血人的血液是否符合标准,符合标准便进入采血环节了。

采血的过程还是比较快的,确认完在哪支胳膊上采血,便安排我坐下。采血操作很简单,工作人员拿过来一套类似医院打吊瓶用的输液设备,其实是一样的,只不过工作方式反了一下:针头扎进血管,血液顺着连接导管流进另一端的储血袋,装满就行了。采血时工作人员提醒说采血的针头比较粗,刚扎进去时会有点疼,建议不要看,我说我不看你扎吧。确实是有点疼的,仔细看那个针头比医院输液用的针头粗了很多,胖一圈都不止。

随后这位工作人员坐旁边问了我一堆问题:在哪上班,休息几天,过年什么时候放假,提醒献完血要多喝水,最近不能剧烈运动高空作业。当然我也问了她一些问题,比如一天有多少人过来献血,比较敏感就不发出来了。没等我将注意力转移到储血袋上,血已经采完了,拔了针按了一会扎针的地方,最后缠上一圈胶带,整个采血环节就结束了。

采完血,工作人员询问有没有不舒服的感觉,让我先喝水休息个15分钟再离开。对了,虽然是定向献血,但也有献血纪念品的,是一只杯子。另外,这个血站虽在闹市人流密集的商场边上,工作人员却只有一个人。从我进门到离开,整个血站总共就工作人员还有我这个献血人两个人,碰巧这几天大降温,外面寒风凛冽,献血屋的空调开的是真足,春天般的温暖,毫不夸张地说这次献血是我有生以来第一次享受这种一对一的公共服务。

关于献血后有没有不适的症状,针对这一问题,我刻意把这篇文章拖了几天再发出来,就是为了观察自己有没有什么不适。总体而言并没有感觉到头晕等不适症状,跟正常时候一样,献完血我就去上班了。献血后的第二天和第三天每晚睡前会有一些畏寒的感觉,即便屋里开了空调也会觉得有些冷,之后就跟往日无异了。

博客: 为用户提供多个选择的设计,不好

我发现这个公司的产品设计有一个偏好或是成规,倾向于在每个基础操作上为用户提供多种选择,大概是坚信这样的设计能为用户使用提供便利。

这个是一种好设计吗?

我认为这种设计在开发、测试、培训、运维等环节上会额外增加难度,浪费资源。多个选择也意味着出 bug 的几率增加,影响产品稳定性。

至于用户最终怎么选择,取决于用户对产品的认知,取决于哪种选择的资源容易获取,取决于哪种选择的操作简单,取决于哪种选择的可靠性强,等等。可以肯定的是,这事不像一夫多妻制社会轮流过夜那种模式,用户兼顾不了每个选择。

粗暴地为用户提供多个选择的产品,看似是以用户为中心,为用户着想,对用户负责。恰恰,这算是典型的不花时间搞清楚用户真实需求、产品设计不明确的不负责任行为。

博客: 给经过两次转置的结果集添加列标题

最近读了 O'Reilly 的技术译文书《SQL经典实例》,原书最后一章有一篇标题为《给经过两次转置的结果集添加列标题》的实例,该实例处理后的数据如下图所示,其结果有点类似电影结尾的职员表,或是以前校园里张贴的成绩排行榜。

SQL 经典实例

作者的想法很棒,同时也觉得作者的实现似乎有些过于复杂和抽象了,不太容易阅读和理解,在此以另一种方法实现,也算是对我自己学习结果的一次检验。(这里的标题仅仅是为匹配原书标题而取的,实际上我并不知道这个场景叫什么比较合适。)

创建数据表:

create table it_app (deptno int NULL,ename VARCHAR(30) NULL);
create table it_sch as select * from it_app where 11;

写入示例数据:

INSERT INTO it_app VALUES (100, 'Clay');
INSERT INTO it_app VALUES (100, 'Mark');
INSERT INTO it_app VALUES (100, 'Jim');
INSERT INTO it_app VALUES (200, 'Lily');
INSERT INTO it_app VALUES (200, 'Lucy');
INSERT INTO it_app VALUES (200, 'Judah');
INSERT INTO it_app VALUES (300, 'Scott');
INSERT INTO it_app VALUES (300, 'Mary');
INSERT INTO it_app VALUES (100, 'Oracle');

INSERT INTO it_sch VALUES (500, 'Kate');
INSERT INTO it_sch VALUES (500, 'Steve');
INSERT INTO it_sch VALUES (500, 'Kettle');
INSERT INTO it_sch VALUES (400, 'Matt');
INSERT INTO it_sch VALUES (400, 'Lary');
INSERT INTO it_sch VALUES (400, 'Danny');

最终的 SQL 实现:

with temp_table as (
-- 整理数据,拼接上部门号, 序号取0(依据 row_number() 从1开始的事实)
select 'app' as mark, deptno, to_char(deptno) as ename, 0 as row_num from it_app
GROUP BY deptno
UNION ALL
select 'app' as mark, deptno,ename,row_number() over(PARTITION by deptno ORDER BY ename) row_num
from it_app
UNION ALL
-- 整理数据, 同上
select 'sch' as mark, deptno, to_char(deptno) as ename, 0 row_num from it_sch
GROUP BY deptno
UNION ALL
select 'sch' as mark, deptno, ename, row_number() over(PARTITION by deptno ORDER BY ename) row_num from it_sch
), tmp_data_src as (
-- 重新进行一次排序编号
select mark, deptno, ename, row_number() over (partition by mark order by deptno,row_num asc) as row_num from temp_table
)
-- 完成转置
select
	max(case mark when 'app' then ename end) as apps,
	max(case mark when 'sch' then ename end) as research
from tmp_data_src
GROUP BY row_num
ORDER BY row_num;

实际上我的这个方案也有它的缺点:较原书的实现方式本实现会各多读一次数据表。

对原书实现方案的改进

原书的实现似乎有 bug: 生成 row_number() 时只用了 id 一个字段,显然还需要加上 ename 字段,否则没法保证多截取的那行跟被替换为 deptno 的那行是同一个员工,下面是我在原作者的基础上做了调整后的实现,简化了层级结构,且实现了按姓名升序排列:

with temp_level as (
	select level as id from dual connect by level  c.ttl_row - 1
)
select
	max(case d.mark when 'app' then ename2 end) as apps,
	max(case d.mark when 'sch' then ename2 end) as research
from temp_table d
GROUP BY d.last_rownum
ORDER BY d.last_rownum

博客: SQL 中处理千分位

实际工作中经常需要对金额类的数值做格式转换,比如有千分位四舍五入保留两位小数,下面介绍下几种常见数据库中这种转化的处理逻辑。

MySQL

SELECT FORMAT(123456789.1234567, 4);
-- 123,456,789.1235

FORMAT() 有三个参数,第一个是要转换的数值,第二个参数是(四舍五入)保留的小数位数,第三个参数用于指定区域设置(将决定转化结果千位分隔符和小数点的格式,缺省为'en US')。

PostgreSQL

select to_char(123456789.12345, 'FM999,999,999,999.9999');
-- 123,456,789.1235

select to_char(123456789.68, '999,999,999,999.9999');
-- 123,456,789.6800

to_char() 的第一个参数是要转换的数值。
to_char() 的第二个参数中的 FM 用于抑制前导的零或尾随的空白(注意对比上面的两个语句,第二个语句没有 FM,小数位部分以0作了填充补齐4位小数),否则结果可能是一个固定宽度的字符串;.9999表示的是小数部分的位数这里是(四舍五入)保留4位小数,改成.99就是保留两位小数;FM999,999,999,999定义的是千分位格式和最长支持的有效数值长度,将其中的,改成_,结果中的千分位将以_分隔。

Oracle

select to_char(123456789.12345, 'FM999,999,999,999.9999')  from dual;
-- 123,456,789.1235

Oracle 的语法同 PostgreSQL

SQL Server

select convert(VARCHAR, cast(123456789.12345 as money), 1);
-- 123,456,789.12

由于一些历史原因,SQL Server 的底层仍然是 Sybase 的那套逻辑,绝大部分的函数名和语法跟 Sybase 都是通用的,包括这里的千分位转换语法,也就是说把 Sybase 上正常运行的 SQL 语句放在 SQL Server 里也能运行。上述语句包含两个函数,内层的 cast(123456789.12345 as money) 用于将数值转换为 money 格式,外层的 conver() 函数是转化的关键:第一个参数指定转换的目标格式;第二个参数求值 cast(123456789.12345 as money) 的结果是要转换的来源值;第三个参数,用1来指定转换格式为逗号分隔的千分位,同时小数点保留两位小数。
如果你想保留4位小数或者1位小数,对不起不支持,只能自己想办法,下面是一个有千分位且保留4位小数的示例:

select substring(convert(varchar, cast(123456789.12345 as money), 1), 1,
                 charindex('.', convert(varchar, cast(123456789.12345 as money), 0)))
       +
       convert(varchar, round(convert(float, '0.' + substring(convert(varchar, 123456789.12345 * 1.0000), charindex('.', convert(varchar, 123456789.12345 * 1.0000)) + 1, 5)), 4));
-- * 1.0000 是为了兼容整数, 4 为要保留的小数位数

原理是截取利用 SQL Server 原生函数生成千分位,并截取千分位部分及小数点,再拼上小数部分。

博客: PostgreSQL DO 块使用示例

DO $demo_do$ 
DECLARE
	demo_table VARCHAR ( 64 );
	val TEXT;
	sql TEXT;
	err TEXT;
	msg TEXT;
BEGIN
	-- 创建临时表 demo_dept
	demo_table = 'demo_dept';
	sql = 'drop table if EXISTS ' || demo_table || ';';
	sql = sql || 'CREATE temp table ' || demo_table || '(name VARCHAR(20) NULL);';
	raise notice'sql___1(%)', sql;
	EXECUTE ( sql );
	-- 往临时表 demo_dept 写一行数据
	val = 'Tom';
	sql = 'INSERT into ' || demo_table || '(name) VALUES (''' || val || ''')';
	raise notice'sql___2(%)', sql;
	EXECUTE ( sql );
	-- 异常捕获和处理(可选)
	EXCEPTION 
	WHEN OTHERS THEN
		GET stacked DIAGNOSTICS err = RETURNED_SQLSTATE,
		msg = PG_EXCEPTION_DETAIL;
	raise notice'err(%),msg(%)', err, msg;
	-- do something
END;
$demo_do$;

博客: postgresql connectby 函数使用示例

connectby 是 postgresql 的 tablefunc 扩展模块所提供一个函数,它的作用是生成递归形式的记录行。

connectby 的参数

connectby 有7个参数,简单用示例说明如下
select * from connectby ('数据表字', '子字段', '父字段', '排序字段(可选)', '开始行的字段值', '查询深度(0表示不限)', '分支间隔符(可选)')
as t('子字段 数据类型', '父字段 数据类型', level int/*层级,固定数据类型*/, branch text/* 分支描述,固定数据类型 */, pos int /* 排序序号, 固定数据类型*/)

使用示例

create table prod_cat(cat_id INTEGER, cat_name VARCHAR(40), parent_cat_id INTEGER);
INSERT into prod_cat(cat_id, cat_name, parent_cat_id)
VALUES
(1, 'A', null),
(2, 'AA', 1),
(3, 'AB', 1),
(4, 'AAA', 2),
(5, 'AAB', 4),
(6, 'B', NULL),
(7, 'BA', 6),
(8, 'BAA', 7),
(9, 'BAAA', 8)
select t.*,t1.cat_name,t2.cat_name as parent_cat_name from connectby('prod_cat', 'cat_id', 'parent_cat_id', 'cat_name', '1', 0, '~')
AS t(cat_id INTEGER, parent_cat_id INTEGER, LEVEL1 INT, branch1 text, se1q integer)
left join prod_cat t1 on t1.cat_id=t.cat_id
left join prod_cat t2 on t2.cat_id=t.parent_cat_id

博客: postgresql crosstab 函数使用示例

crosstab 是 postgresql 的 tablefunc 扩展模块所提供的函数之一,tablefunc 提供了多个返回多行数据的函数,crosstab 便是其一,crosstab 提供了一种将多行数据转换成一行数据的能力,可以简单地理解为行转列。postgresql 帮助文档上都有对 crosstab 函数的详细介绍,不过使用的例子比较抽象,再加上粗陋的翻译质量可能让人不是很好理解,在此总结一下 crosstab 这个函数,并提供一个手册上没有的实用技巧(见本篇后半部的《双参数的 crosstab 示例2》)。

安装 tablefunc 模块

默认情况下 tablefunc 模块是未安装的,需要手动执行下方命令安装,重复执行会提示该扩展已创建:

CREATE EXTENSION tablefunc;

创建示例表

创建示例表:

CREATE table prod_attr
(
    prod_name      VARCHAR(20), -- 产品名称
    attr_name      VARCHAR(40), -- 属性名称
    attr_val       NUMERIC(15, 2), -- 属性值
    attr_val_desc  VARCHAR(20) -- 属性单位
);

写入示例数据:

insert into prod_attr(prod_name, attr_name, attr_val, attr_val_desc)
VALUES
('农夫山泉','库存数量', 100, '瓶'),
('农夫山泉','规格', 500, '毫升'),
('农夫山泉','单价', 2.00, '元'),
('雪碧','规格', 200, '毫升'),
('雪碧','单价', 1.80, '元'),
('雪碧','折后价', 1.60, '元'),
('手工面包','库存数量', 10, '盒'),
('手工面包','规格', 300, '克'),
('手工面包','单价', 12.00, '元'),
('手工面包','保质期', 7, '天');

执行查询:

select * from prod_attr;

下图是表里的原始数据:

在进一步介绍上述语句前先介绍下示例表和示例表中的数据:
示例表有四个字段,前三个字段分别是产品名称(prod_name),属性名称(attr_name)和属性值(attr_val),第四个字段(attr_val_desc)在这里并未实际使用,仅出于方便理解的目的作为对第三个字段的解释。

通过插入的实际值能够看出这是一个记录产品常见属性的表,如规格、价格、库存数量、保质期,每一个这样的产品属性占据一行,即同一个产品有几个属性就有几行数据在表里。

这里我们使用 crosstab 的最终目的就是要把分散在每一行的这些产品属性置于同一行来显示,以便用户使用,可以简单地把这种行为理解为行转列。

crosstab 第一个参数的约束限制

上述语句中的 crosstab 只有一个字符串参数,这个字符串参数必须是一个合法的 sql 查询,它的作用是从数据表中找出来需要转换处理的目标数据,这个查询需要满足以下几个条件:

  1. select 字段:必须具有顺序固定的三个字段列,第一列是名称(示例是产品名称 prod_name),第二列是属性名称(示例是属性名称 atrr_name),第三列是属性值(示例是属性值 attr_val);
  2. order by 字句:order by 子句的作用是给 select 子句的前两个字段(prod_name、atrr_name)排序,顺序必须是第一个字段在前、第二个字段在后,order by 不能省略且字段顺序一定要处理好,否则执行结果会有问题;

定义 crosstab 的返回字段和数据类型

同时,由于 crosstab 函数定义的返回类型为 record 即多行记录,需要在 crosstab 的 from 子句中为 crosstab 定义返回的字段及数据类型,这里的字段顺序是固定的,第一个字段名及其数据类型同 select 子句的第一个字段(prod_name),后续字段可以根据实际需要(一般是需要显示几个属性就定义几个)自行定义,但是它们的数据类型必备与 select 子句的第三个字段(attr_val)相同。

至此,上述 crosstab 的使用就清楚了,如果你不想看上述繁琐的文字叙述,看一下接下来的图片即明白这个函数的作用了。

一个参数的 crosstab 示例

注意参数中的 sql 里加了 where 条件限制,移除 where 后执行查询你会发现这里有一个问题

select
prod_name,attr_a as "规格",attr_b as "库存数量",attr_c as "单价", attr_d as "保质期"
from crosstab(
    'select prod_name, attr_name, attr_val
       from prod_attr t1
      where t1.prod_name in (''农夫山泉'', ''手工面包'') -- 条件过滤
   order by prod_name, attr_name desc'
) as cte(prod_name VARCHAR, attr_a NUMERIC, attr_b NUMERIC, attr_c NUMERIC, attr_d NUMERIC)

这是通过 crosstab 转换后的数据:

是不是很简单,这里的前提是建立在每个商品属性一致的(即有都有同样的属性,即便其值有所不同值)假设前提下,使用者需要做的是通过第一个参数 sql 中的 order by 保证每个产品名称(prod_name)的属性字段(atrr_name)的顺序一致。当商品的属性不一致时上述语句就会有问题,这也是接下来要描述的双参数的 crosstab 要解决的问题。

两个参数的 crosstab

双参数的 crosstab 与一个参数的 crosstab 作用是相同的,区别是对属性字段的处理上更加灵活。

双参数 crosstab 的第一个参数

双参数 crosstab 的第一个参数与单参数 crosstab 的第一个参数是一样的,也是一个 sql 查询,特别之处是

  1. 在第一列(商品名称(prod_name))后方可以出现可选的一个或多个额外字段列(extra columns);与单参数一样,属性名称(示例是属性名称 atrr_name)和属性值(示例是属性值 attr_val)这两个字段始终要保证在倒数第二和倒数第一的位置上;
  2. order by 仍然要做排序,区别是只需要对第一列商品名称(prod_name)进行排序了。

双参数 crosstab 的第二个参数

它的第二个参数也是一个 sql 语句它的作用是指定需要显示的属性名称的列表,即在这里指定那些你希望在最后的结果出现的属性列,且要满足如下条件:
第二个参数的查询结果只能有一个字段,且不能存在重复行,否则会报错,即你要在第二个参数的 select 子句里预防性地加一个 distinct;
注意顺序,与返回字段配合好,关于顺利这里还有另一个技巧可以让你显示地指定这些字段和它们出现的顺序,下面会介绍。

定义 crosstab 的返回字段和数据类型

要求同单参数,不再赘述。

双参数的 crosstab 示例1

还是以上述的示例表中的数据为例,加入第二个参数,通过第二个参数里的 sql 查询指定属性名称(atrr_name)及其排序顺序。

select
prod_name,
attr_a as "规格",
attr_b as "单价",attr_c as "折后价", attr_d as "保质期", attr_e as "库存数量"
from 
crosstab(
    'select prod_name, attr_name, attr_val
       from prod_attr t1
   order by prod_name', -- 要排序,仅需对第一个字段排序
	 'select DISTINCT attr_name from prod_attr' -- 保证只有一列且不能存在重复行
) as cte(prod_name VARCHAR,
				 attr_a NUMERIC, attr_b NUMERIC, attr_c NUMERIC, attr_d NUMERIC, attr_e NUMERIC)

执行结果如下图:

双参数的 crosstab 示例2(强烈推荐)

通过第二个参数显示指定属性名称字段及其顺序。

select
prod_name, attr_a as "保质期", attr_b as "规格",attr_c as "单价",attr_d as "折后价"
from 
crosstab(
    'select prod_name, attr_name, attr_val
       from prod_attr t1
   order by prod_name', -- 排序仍然要, 仅需对第一个字段排序
	 'VALUES (''保质期''), (''规格''), (''单价''), (''折后价'')' -- 显示指定attr_name值和排序
) as cte(prod_name VARCHAR, attr_a NUMERIC, attr_b NUMERIC, attr_c NUMERIC, attr_d NUMERIC)

执行结果如下图:

参考:http://postgres.cn/docs/12/tablefunc.html

博客: 在 WordPress 中实现事务

WordPress 使用 wpdb 类创建的全局对象 $wpdb 实现对底层 MySQL 数据库的 DML 操作。如果你想将 WordPress 用作涉及数据库交互的开发框架,那么面临的第一个问题是 $wpdb 不支持事务,没有事务的支持,就无法保证业务的一致性。对于事务的概念这里就不做介绍了,如果你找到这篇文章,那你自然是理解的。

本插件是 wpdb 类的子类,以 wpdb 为基础实现了对事务的支持,做法是在 wpdb 类上扩展出一个子类 conn,在子类 conn 上实现对事务的支持(不受影响 wpdb 的实例 $wpdb)。为方便使用已做成插件,项目地址在这里:https://github.com/yusn/wp_conn下载地址在这里

使用方法

插件已定义了全局对象 $conn,在您的程序里只需要加入global $conn;即可通过 $conn 调用那些从 wpdb 类继承来的方法,以及 $conn 自有的一些方法。换句话说在需要时你完全可以用 $conn 来替代 $wpdb。

特性介绍

这是一个试验性的项目,仅供交流。

  • 仅支持 REST API;
  • 自动开启事务;
  • 异常自动回滚;
  • 运行结束自动提交(返回 REST API 请求时);
  • 支持多行插入;
  • 支持自动提交模式;
  • 支持手动提交;
  • 支持手动回滚;

仅支持 REST API

之所以仅仅支持 REST API,是因为开启事务/自动提交/自动回滚的实现是在 REST API 提供的 filter 上实现的(与 $wpdb 不同,$conn 默认运行在手动提交模式下)。

自动开启事务

接收到 REST API 请求时自动开启事务,无需手动开启。

运行结束自动提交

API 程序运行结束,如未有异常发生,响应返回前将自动提交事务。

异常自动回滚

当 API 程序抛出异常,或返回 WP_ERROR 类实例时,将自动执行事务回滚。
若在程序抛出异常前执行了手动提交 $conn->commit();,则提交之前的 DML 操作不会被回滚;若异常发生在手动开启事务之后,或执行$conn->commit();之后返回 WP_ERROR,则回滚仅限于手动开启事务之后的部分。

支持自动提交模式

也可以像 $wpdb 那样,让你的代码运行在自动提交模式下,只需要在程序中执行执行数据库操作前执行$conn->set_autocommit();即开启自动提交模式,此后若再发生异常或错误,则无法回滚。

支持手动提交

默认情况下(未开启自动提交)在程序执行过程中,你可以在需要时随时手动提交之前的操作,只需要执行$conn->commit();方法。执行$conn->commit();将自动开启一个新的事务,这会让你之后的代码运行在一个新事务里,这个事务在正常响应返回前会自动被提交,若后续代码发生了异常或返回 WP_ERROR 类的实例,则这部分代码会被回滚。
开启自动提交后($conn->set_autocommit();),若再执行手动提交($conn->commit();),则手动提交不会生效(也不会产生新事务),因为当前已经运行在自动提交模式下,无需手动提交。

支持手动回滚

在一个事务里,你可以随时执行$conn->rollback();方法以回滚之前的操作;若在此之前已开启了自动提交模式($conn->set_autocommit();),则回滚操作不会生效。

支持多行插入

$wpdb 的插入方法只支持单行插入,使用$conn->insert_rows();方法能够实现一次插入多行,该方法的返回结果是成功插入的总行数。

$res = $conn->insert_rows(
            'test', // 写入的表名称
            array('name'), // 写入的表字段
            array('string'), // 写入字段的字段类型
            array(
                array('aa'), // 第一行数据
                array('bb'), // 第二行数据
                array('cc'), // 第三行数据
            ),
        );

注意事项

MySQL 的 DDL 操作是无法被回滚的,即 create/drop databases,create/drop/alter table 这些命令无法被回滚,应慎重考虑在事务中包含 DDL 语句,若无法避免应考虑做相应的容错处理。

❌