ohUrlShortener 1.5 版本升级指南
ohUrlShortener 短链接系统 1.6 版本做了较大的结构性调整，关系到管理端统计数据项及统计策略。 代码层面主要的变化可查阅：
- https://github.com/barats/ohUrlShortener/commits/v1.6
- https://gitee.com/barat/ohurlshortener/commits/v1.6
升级到1.6版本的过程中：为了兼容已存在的旧数据，需要手动对数据库进行一定的结构性改造，其具体过程如下：
升级数据库结构
ohUrlShortener 1.6 版本主要是对老版本的统计功能做了一定的升级，因此需对数据结构进行一定的调整。以下方法假定你使用的是 Docker 版本的 PostgreSQL 数据库，操作如下：
登录至 PostgreSQL 容器环境中
1sudo docker exec -it ohurlshortener_pg psql -U postgres
连接至 oh_url_shortener 数据库
1\c oh_url_shortener
执行以下数据库脚本
1
2-- Create table for top25 urls
3CREATE TABLE public.stats_top25 (
4 id serial4 NOT NULL,
5 short_url varchar(200) NOT NULL,
6 today_count int8 NOT NULL DEFAULT 0,
7 d_today_count int8 NOT NULL DEFAULT 0,
8 stats_time timestamp with time zone NOT NULL DEFAULT NOW(),
9 CONSTRAINT stats_tv_pk PRIMARY KEY (id)
10);
11
12-- Stored procedure for top25 urls
13CREATE FUNCTION p_stats_top25() RETURNS void AS $$
14BEGIN
15 RAISE NOTICE 'Procedure p_stats_top25() called';
16
17 -- delete all records
18 DELETE FROM public.stats_top25 WHERE 1=1;
19
20 -- insert fresh-new records
21 INSERT INTO public.stats_top25(short_url,today_count,d_today_count,stats_time)
22 SELECT l.short_url AS short_url, COUNT(l.ip) AS today_count ,COUNT(DISTINCT(l.ip)) AS d_today_count, NOW() AS stats_time
23 FROM public.access_logs l WHERE date(l.access_time) = date(NOW()) GROUP BY l.short_url ORDER BY today_count DESC LIMIT 25;
24END;
25$$ LANGUAGE plpgsql;
26
27-- Create table for sum view
28CREATE TABLE public.stats_sum (
29 stats_key varchar(200) NOT NULL,
30 stats_value int8 NOT NULL DEFAULT 0,
31 CONSTRAINT stats_sum_key PRIMARY KEY (stats_key)
32);
33
34-- Insert pre-defined stats
35INSERT INTO public.stats_sum (stats_key,stats_value) VALUES
36 ('today_count',0), ('d_today_count',0),
37 ('yesterday_count',0), ('d_yesterday_count',0),
38 ('last_7_days_count',0), ('d_last_7_days_count',0),
39 ('monthly_count',0), ('d_monthly_count',0);
40
41-- Stored procedure for stats sum view
42CREATE FUNCTION p_stats_sum() RETURNS void AS $$
43DECLARE
44 today_count int8;
45 d_today_count int8;
46 yesterday_count int8;
47 d_yesterday_count int8;
48 last_7_days_count int8;
49 d_last_7_days_count int8;
50 monthly_count int8;
51 d_monthly_count int8;
52BEGIN
53 RAISE NOTICE 'Procedure p_stats_sum() called';
54
55 SELECT COUNT(l.ip),COUNT(DISTINCT(l.ip)) INTO today_count,d_today_count
56 FROM public.access_logs l WHERE date(l.access_time) = date(NOW());
57
58 SELECT COUNT(l.ip),COUNT(DISTINCT(l.ip)) INTO yesterday_count,d_yesterday_count
59 FROM public.access_logs l WHERE date(l.access_time) = (NOW() - INTERVAL '1 day')::date;
60
61 SELECT COUNT(l.ip),COUNT(DISTINCT(l.ip)) INTO last_7_days_count,d_last_7_days_count
62 FROM public.access_logs l WHERE date(l.access_time) >= (NOW() - INTERVAL '7 day')::date;
63
64 SELECT COUNT(l.ip),COUNT(DISTINCT(l.ip)) INTO monthly_count,d_monthly_count
65 FROM public.access_logs l WHERE DATE_PART('month', l.access_time) = DATE_PART('month',NOW());
66
67 UPDATE public.stats_sum SET stats_value =
68 CASE
69 WHEN stats_key = 'today_count' THEN today_count
70 WHEN stats_key = 'd_today_count' THEN d_today_count
71 WHEN stats_key = 'yesterday_count' THEN yesterday_count
72 WHEN stats_key = 'd_yesterday_count' THEN d_yesterday_count
73 WHEN stats_key = 'last_7_days_count' THEN last_7_days_count
74 WHEN stats_key = 'd_last_7_days_count' THEN d_last_7_days_count
75 WHEN stats_key = 'monthly_count' THEN monthly_count
76 WHEN stats_key = 'd_monthly_count' THEN d_monthly_count
77 ELSE 0
78 END;
79END;
80$$ LANGUAGE plpgsql;
81
82-- Create table for ip url sum
83CREATE TABLE public.stats_ip_sum (
84 short_url varchar(200) NOT NULL,
85 today_count int8 NOT NULL DEFAULT 0,
86 d_today_count int8 NOT NULL DEFAULT 0,
87 yesterday_count int8 NOT NULL DEFAULT 0,
88 d_yesterday_count int8 NOT NULL DEFAULT 0,
89 last_7_days_count int8 NOT NULL DEFAULT 0,
90 d_last_7_days_count int8 NOT NULL DEFAULT 0,
91 monthly_count int8 NOT NULL DEFAULT 0,
92 d_monthly_count int8 NOT NULL DEFAULT 0,
93 total_count int8 NOT NULL DEFAULT 0,
94 d_total_count int8 NOT NULL DEFAULT 0,
95 CONSTRAINT stats_ip_sum_pk PRIMARY KEY (short_url)
96);
97
98-- Stored procedure for ip url sum
99CREATE FUNCTION p_stats_ip_sum() RETURNS void AS $$
100BEGIN
101
102 RAISE NOTICE 'Procedure p_stats_ip_sum() called';
103
104 -- Delete all records
105 DELETE FROM public.stats_ip_sum WHERE 1=1;
106
107 -- Calculate new stats data
108 INSERT INTO public.stats_ip_sum(short_url,today_count,d_today_count,yesterday_count,d_yesterday_count,last_7_days_count,d_last_7_days_count,
109 monthly_count,d_monthly_count,total_count,d_total_count)
110 SELECT
111 u.short_url,
112 (SELECT count(ip) FROM public.access_logs WHERE date(access_time) = date(NOW()) AND short_url = u.short_url),
113 (SELECT count(DISTINCT(ip)) FROM public.access_logs WHERE date(access_time) = date(NOW()) AND short_url = u.short_url),
114
115 (SELECT count(ip) FROM public.access_logs WHERE date(access_time) = (NOW() - INTERVAL '1 day')::date AND short_url = u.short_url),
116 (SELECT count(DISTINCT(ip)) FROM public.access_logs WHERE date(access_time) = (NOW() - INTERVAL '1 day')::date AND short_url = u.short_url),
117
118 (SELECT count(ip) FROM public.access_logs WHERE date(access_time) >= (NOW() - INTERVAL '7 day')::date AND short_url = u.short_url),
119 (SELECT count(DISTINCT(ip)) FROM public.access_logs WHERE date(access_time) >= (NOW() - INTERVAL '7 day')::date AND short_url = u.short_url),
120
121 (SELECT count(ip) FROM public.access_logs WHERE DATE_PART('month',access_time) = DATE_PART('month',NOW()) AND short_url = u.short_url),
122 (SELECT count(DISTINCT(ip)) FROM public.access_logs WHERE DATE_PART('month',access_time) = DATE_PART('month',NOW()) AND short_url = u.short_url),
123
124 (SELECT count(ip) FROM public.access_logs WHERE short_url = u.short_url),
125 (SELECT count(DISTINCT(ip)) FROM public.access_logs WHERE short_url = u.short_url)
126 FROM public.short_urls u
127 LEFT JOIN public.access_logs l ON u.short_url = l.short_url
128 GROUP BY u.short_url;
129END;
130$$ LANGUAGE plpgsql;
测试数据库对象是否正确
执行以下 SQL 函数确认期是否能够正常执行
1select * from p_stats_top25();
2select * from p_stats_ip_sum();
3select * from p_stats_sum();
退出 PostgreSQL 及 Docker 容器环境
1\q
重新启动 Docker 容器
停止当前运行的各容器
进入到 ohurlshortener\docker 目录中并执行
1./stop_destory.sh
修改 vars.env
进入到 ohurlshortener\docker 目录中修改 vars.env 文件，将其中的 OH_ADMIN_VERSION、OH_PORTAL_VERSION 改到最新版
1OH_PORTAL_VERSION=latest
2OH_ADMIN_VERSION=latest
重启启动各容器
进入到 ohurlshortener\docker 目录中并执行
1./one_step_start.sh
功能介绍
ohUrlShortener 适合中小型社区网站使用的企业级短链接服务系统，支持短链接生产、查询及302转向，并自带点击量统计、独立IP数统计、访问日志查询：
- 支持 Docker One Step Start 部署启动
- 支持短链接生产、查询、存储、302转向
- 支持访问日志查询、访问量统计、独立IP数统计
- 支持 HTTP API 方式新建短链接、禁用/启用短链接、查看短链接统计信息、新建管理员、修改管理员密码
- 支持访问日志导出，方便线下分析
ohUrlShortener 短链接系统