Author Topic: Some usefull SQL scripts in PVD  (Read 6188 times)

0 Members and 1 Guest are viewing this topic.

Offline VVV_Easy_Programing

  • Older Power User
  • *****
  • Posts: 199
    • View Profile
Some usefull SQL scripts in PVD
« on: January 28, 2017, 08:10:45 pm »
Hello,
there are sometimes that the script is not a good tool for arrange PVD. Happily we have the possibility of use SQL scripts.

First you must start  PVD in Debug Mode (viddb.exe -debug) and then you have in Menu->Help->SQL where you can run a SQL Script (tick the box if the script have several lines).

I publish two examples easy to understand:
1) Now I have problems with IMDB[EN] and the original title (give me a false string 'var ....'). I can solve easily the problem running:
                  update MOVIES set "origtitle"="title" where "origtitle"='var ue_t0=window.ue_t0||+new Date();';
or
                  update MOVIES set "origtitle"="title" where SUBSTR("origtitle",1,3) = 'var';

2) Some strange chars appears in the people IMDB url. I use:
                  update PEOPLE set "url"=replace("url",'?ref_=tt_ov_dr', '/');
                  update PEOPLE set "url"=replace("url",'?ref_=tt_ov_wr', '/');

I anime to all to publish here their own SQL scripts for PVD.

Offline Ivek23

  • Global Moderator
  • *****
  • Posts: 2667
    • View Profile
Re: Some usefull SQL scripts in PVD
« Reply #1 on: December 22, 2023, 09:00:47 pm »
Use this SQL script if you are missing this part in the imdb url: www.

Quote
update MOVIES set "url"=replace("url",'http://imdb', 'http://www.imdb');

Instructions on how to use the SQL script are in the first post.

Here you have some more similar SQL scripts.

Quote
update MOVIES set "url"=replace("url",'http://httpbin.org/response-headers?key=http://www.imdb', 'http://www.imdb');

Quote
update MOVIES set "url"=replace("url",'http://httpbin.org/response-headers?key=https://', 'http://');

Quote
update MOVIES set "url"=replace("url",'http://httpbin.org/response-headers?key=', '');
Ivek23
Win 10 64bit (32bit)   PVD v0.9.9.21, PVD v1.0.2.7, PVD v1.0.2.7 + MOD


Offline afrocuban

  • Moderator
  • *****
  • Posts: 444
    • View Profile
Re: Some usefull SQL scripts in PVD
« Reply #2 on: December 22, 2023, 11:03:00 pm »
I have this problem with some urls from the times IMDb script wasn't updated for a long time before _2b:


Code: [Select]
http://www.imdb.comhttps://www.imdb.com

So I guess this should help?


Code: [Select]
update MOVIES set "url"=replace("url",'http://www.imdb.comhttps://www.imdb.com', 'https://www.imdb.com');
« Last Edit: December 22, 2023, 11:11:50 pm by afrocuban »

Offline afrocuban

  • Moderator
  • *****
  • Posts: 444
    • View Profile
Re: Some usefull SQL scripts in PVD
« Reply #3 on: December 22, 2023, 11:20:10 pm »
Related to http://www.videodb.info/forum_en/index.php/topic,4134.msg21969.html#msg21969

The query has to be
Code: [Select]
update PEOPLE set "url"=replace("url",'http://imdb', 'http://www.imdb');

Offline Ivek23

  • Global Moderator
  • *****
  • Posts: 2667
    • View Profile
Re: Some usefull SQL scripts in PVD
« Reply #4 on: December 23, 2023, 08:29:08 am »
Related to http://www.videodb.info/forum_en/index.php/topic,4134.msg21969.html#msg21969

The query has to be
Code: [Select]
update PEOPLE set "url"=replace("url",'http://imdb', 'http://www.imdb');

Of course, I just gave the movies as an example, but you can decide for yourself what you need it for.
Ivek23
Win 10 64bit (32bit)   PVD v0.9.9.21, PVD v1.0.2.7, PVD v1.0.2.7 + MOD