Ireader

来自ling
跳转至: 导航搜索

sync

134执行爬取--->|
               |
群晖执行爬取-->|-->笔记本local-->oss&qunhui
               |
oss源文件   -->|


READER_NEW_qunhui-->READER_NEW_local-->OSS&qunhui
OSS_READER_NEW
OSS_no_READER_NEW


oss->list.txt    |
                 |-->differ.txt-->upload
local->list.txt  |

常用连接

未出版 https://m.zhangyue.com/detail/10037430


http://cloud.ling2.cn:5433/browser/#

http://qunhui.ling2.cn:5006/iReader/getStatus

http://qunhui.ling2.cn:5006/iReader/initAllChapters/198

http://qunhui.ling2.cn:5006/iReader/stop

http://qunhui.ling2.cn:5006/iReader/start/198

http://qunhui.ling2.cn:5006/iReader/syncAllBooks/336


http://192.168.74.134:5006/iReader/getStatus

http://192.168.74.134:5006/iReader/initAllChapters/198

http://192.168.74.134:5006/iReader/stop

http://192.168.74.134:5006/iReader/start/198

http://192.168.74.134:5006/iReader/syncAllBooks/336

http://192.168.74.134:5006/iReader/syncOldFolder

http://192.168.74.134:5006/iReader/downloadFolder

http://localhost:5006/iReader/scanNewBook/2022-03-07

http://cloud.ling2.cn:5006/iReader/syncOldFolder


kpmg

http://localhost:5006/iReader/getStatus

http://localhost:5006/iReader/initAllChapters/156

http://localhost:5006/iReader/stop

http://localhost:5006/iReader/start/156

http://localhost:5006/iReader/downloadFolder


Docker常用服务#ecs迁移到rds

sql

select t.name, t.sale_date, t.publish_date
from base_reader_book t
where DATE(t.creation_date) = '2022-03-04';

-- update base_reader_book book
-- set is_chapter_info= true
-- where exists(select 1 from base_reader_book_chapter chapter where book.id = chapter.book_id);

select book.i_reader_id, count(1) num
from base_reader_book_chapter chapter,
     base_reader_book book
where book.id = chapter.book_id
group by book.i_reader_id
having count(1) > 1;


--124--134--151--245--263--280
select count(1)
from base_reader_book book
where exists(select 1 from base_reader_book_chapter chapter where book.id = chapter.book_id);

select count(1)
from base_reader_book book
where is_chapter_info = true;

--17865--17945--18143--18333--18722--32459--35416--47547--73319--190042--245688--292617--361258--397733--445813--472737--494234--594467
select count(1)
from base_reader_book_chapter;

select *
from base_reader_book_chapter
where i_reader_id = '11682045';
select *
from base_reader_book
where i_reader_id = '11682045';

select max(index_)
from base_reader_book_chapter
where i_reader_id = '11682045';
-- 93 93 小说
-- 10 10 小说 9995
-- 113 文学 9813
-- 142 历史 7967
-- 127 传记 4463
-- 447 青春 4268
-- 198 励志 9656
-- 179 179 社科 9541
-- 168 168 心理 3202
-- 212 212 经济 5647
-- 448 448 管理 282
-- 232 232 理财 1564
-- 182 文化 2357
-- 244 244 旅游 1108
-- 252 252 美食 1725
-- 265 265 时尚 598
-- 273 273 健身 505
-- 284 284 孕产 1229
-- 296 296 少儿 9676
-- 308 308 科学 2848
-- 453 453 科普 0
-- 336 336 医学
-- 347 347 教辅 9602
-- 351 351 外语 3371
-- 325 325 工业 49
-- 180 宗教 1269
-- 181 哲学 2897
-- 653 653 军事 49
-- 156 艺术 6580
-- 201 修养 1365
-- 277 养生 3112
-- 240 240 两性 376
-- 449 家居 117
-- 334 334 建筑 1340
-- 183 183 法律 3560
-- 454 454 农业 0
-- 450 450 休闲 0
-- 451 451 体育 582
-- 365 365 外文 9968
-- 320 320 计算机 9268
-- 119 国学经典 1379
-- 126 掌阅公版 2207
-- 190 政治 3816

-- 113 文学 9813
-- 142 历史 7967
-- 127 传记 4463
-- 277 养生 3112
-- 449 449 家居 117
-- 448 448 管理 282
-- 232 232 理财 1564
-- 244 244 旅游 1108
-- 252 252 美食 1725
-- 265 265 时尚 598
-- 273 273 健身 505
-- 284 284 孕产 1229
-- 308 308 科学 2848
-- 180 宗教 1269
-- 181 哲学 2897
-- 182 文化 2357
-- 119 国学经典 1379
-- 126 掌阅公版 2207
-- 201 修养 1365
-- 449 家居 117

--6759--6527--6530--6516--6512-6500--6436--6071--4829--4150--4037--3690--3575--2995--2720--2264--2017--1880--1137
--9813--9431(13:05)--8673(21:02)--7426(10:47)-6136(02:19)--5813(11:25)--2751(10:39)--1517(15:31)--889(10:20)
--1295(7:56)--699(22:36)
--7967(2021-12-06-12:01)--7358(2021-12-06-22:04)--6251(2021-12-07 14:37)--5637--5024--4620(2021-12-08 16:20)--1998(2021-12-10 9:45)--2021-12-11(9:30)
--4463(9:30)--3462(17:30)--2862(22:23)
select count(1)
from base_reader_book
where category2 = 198
  and is_publish = true
  and is_chapter_info = false;

select count(1)
from base_reader_book
where category2 = 201
  and is_publish = true;

select count(1)
from base_reader_book
where is_publish = true
  and is_chapter_info = false;

select *
from base_reader_book
where is_publish = true
  and is_chapter_info = false;

select count(1)
from base_reader_book
where is_chapter_info = true;

select count(1)
from base_reader_book
where is_publish = false;

select id
from base_reader_book
where i_reader_id = '10155039';

-- update base_reader_book
-- set is_chapter_info= false,
--     is_publish= true
-- where i_reader_id = '10155039';
--
-- delete
-- from base_reader_book_chapter
-- where book_id = '0935c716-afa2-41b6-8141-1a343a2aa16b';

select *
from base_reader_book_chapter
where book_id = '0935c716-afa2-41b6-8141-1a343a2aa16b';

select count(1)
from base_reader_book
WHERE publish_date IS NOT NULL
ORDER BY publish_date DESC;

select category2, count(1) as num
from base_reader_book
where is_chapter_info = false
group by category2
order by num desc;

select *
from base_reader_book
where i_reader_id = '10894270';

select chapter_num, i_reader_id, name
from base_reader_book
where category2 = 336
  and is_chapter_info = false
order by chapter_num asc;


select count(1)
from base_reader_book_chapter
where key_ = '11847584_112';

select tenant_id
from base_reader_book
group by tenant_id;

select distinct (category2)
from base_reader_book;

select *
from base_reader_book t
where t.tenant_id is null;
select *
from base_reader_book_chapter t
where t.tenant_id is null;

select book.name, book.i_reader_id
from base_reader_book book
where book.is_charge = true;

-- update base_reader_book
-- set is_chapter_info= false,
--     is_charge= false
-- where is_charge = true;


select book.i_reader_id, count(chapter.id) num, book.chapter_num, book.is_charge, book.is_chapter_info
from base_reader_book_chapter chapter,
     base_reader_book book
where book.id = chapter.book_id
group by book.i_reader_id, book.chapter_num, book.is_charge, book.is_chapter_info
having count(1) > 1
   and count(chapter.id) != book.chapter_num
   and book.is_charge = false;

-- update base_reader_book
-- set is_chapter_info= false
-- where i_reader_id in (
--     select book.i_reader_id
--     from base_reader_book_chapter chapter,
--          base_reader_book book
--     where book.id = chapter.book_id
--     group by book.i_reader_id, book.chapter_num, book.is_charge
--     having count(1) > 1
--        and count(chapter.id) != book.chapter_num
--        and book.is_charge = false);


select i_reader_id, count(1) as num
from base_reader_book_chapter
where tenant_id is null
group by i_reader_id;

-- update base_reader_book_chapter
-- set tenant_id=1
-- where tenant_id is null;

-- #1 查找锁表的pid

select pid,
       locktype,
       database,
       relation,
       page,
       tuple,
       virtualxid,
       transactionid,
       classid,
       objid,
       objsubid,
       virtualtransaction,
       pid,
       mode,
       granted,
       fastpath
from pg_locks l
         join pg_class t on l.relation = t.oid
where t.relkind = 'r'
  and t.relname = 'base_reader_book';

-- 2 查找锁表的语句

select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (select pid
              from pg_locks l
                       join pg_class t on l.relation = t.oid and t.relkind = 'r'
              where t.relname = 'base_reader_book');

-- 3 查找所有活动的被锁的表


select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
    select pid
    from pg_locks l
             join pg_class t on l.relation = t.oid
        and t.relkind = 'r'
);

-- 4 解锁

SELECT pg_cancel_backend(17705);


-- 5 批量(未排除自己)


select pg_cancel_backend(pid)
from pg_stat_activity
where pid in (
    select pid
    from pg_locks l
             join pg_class t on l.relation = t.oid
        and t.relkind = 'r'
);

select pg_cancel_backend(17705);


SELECT *
FROM pg_stat_activity
WHERE datname = '死锁的数据库ID ';


select oid, relname
from pg_class
where relname = 'base_reader_book';
select locktype, database, pid, relation, mode
from pg_locks
where relation = 60974;


select i_reader_id, name, chapter_num
from base_reader_book t
where chapter_num is not null
  and is_chapter_info = true
order by t.chapter_num desc;



select *
from pg_locks
where relation in (select oid
                   from pg_class
                   where relname = 'base_reader_book')
order by pid asc;

-- relation,24577,60974,,,,,,,,6/241913,14644,AccessShareLock,true,true
-- relation,24577,60974,,,,,,,,8/55220,14652,AccessShareLock,true,true
-- relation,24577,60974,,,,,,,,39/222747,17714,AccessShareLock,true,true
-- relation,24577,60974,,,,,,,,44/40285,17719,AccessShareLock,true,true
-- relation,24577,60974,,,,,,,,44/40285,17719,RowExclusiveLock,true,true

-- select pg_terminate_backend(pid) -- pid替换为上面查到的pid
select pg_terminate_backend(pid)
from pg_stat_activity
where client_addr in (select client_addr
                      from pg_stat_activity
                      where datname = 'ling_cloud_admin'
                        and application_name = 'PostgreSQL JDBC Driver'
                        and wait_event_type = 'Lock')
  and pid != (select pid
              from pg_stat_activity
              where datname = 'ling_cloud_admin'
                and application_name = 'PostgreSQL JDBC Driver'
                and wait_event_type = 'Lock');

select *
from pg_stat_activity
where datname = 'ling_cloud_admin'
  and application_name = 'PostgreSQL JDBC Driver';

select pid
from pg_stat_activity
where client_addr in (select client_addr
                      from pg_stat_activity
                      where datname = 'ling_cloud_admin'
                        and application_name = 'PostgreSQL JDBC Driver'
                        and wait_event_type = 'Lock')
  and pid != (select pid
              from pg_stat_activity
              where datname = 'ling_cloud_admin'
                and application_name = 'PostgreSQL JDBC Driver'
                and wait_event_type = 'Lock');
select pg_terminate_backend(19275);

select count(1)
from base_attachment;
--utf8_general_ci

--2021-12-14 10:21:47.423000
--2021-12-13 22:20:44.639000
select max(creation_date)
from base_reader_book_chapter;

select count(1)
from base_reader_book_chapter
where creation_date > to_date('2021-12-13 22:20:44.639000', 'yyyy-MM-dd hh24:mi:ss');

--295073
select count(1)
from base_reader_book;