DR - images [lvwiki]

This query is marked as a draft This query has been published by Edgars2007.

#SET @cats = "Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas","Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki","Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas";

use lvwiki_p;
select now();

#dublicates
/*
SELECT p.page_title FROM page p
INNER JOIN (image e JOIN commonswiki_p.image c ON c.img_sha1 = e.img_sha1) ON e.img_name = p.page_title
WHERE p.page_namespace = 6;
*/
#visv izmantotie aizs att
SELECT
  imgtmp.page_title,
  COUNT(cl_to)
FROM page AS pg1
JOIN categorylinks
ON cl_from = pg1.page_id
JOIN (SELECT
        pg2.page_namespace,
        pg2.page_title,
        il_to
      FROM page AS pg2
      JOIN imagelinks
      ON il_from = page_id) AS imgtmp
ON il_to = pg1.page_title
WHERE pg1.page_namespace = 6 and imgtmp.page_namespace=0
AND cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
GROUP BY 
imgtmp.page_namespace,
imgtmp.page_title
HAVING COUNT(cl_to) > 4
ORDER BY COUNT(cl_to) DESC;

#lvwiki aizs. attēli - ir Commons
SELECT
  DISTINCT(lvwiki_p.page.page_title),
  commonswiki_p.image.img_name
FROM lvwiki_p.image, commonswiki_p.image, lvwiki_p.categorylinks, lvwiki_p.page
WHERE lvwiki_p.image.img_sha1 = commonswiki_p.image.img_sha1 and lvwiki_p.page.page_title != commonswiki_p.image.img_name
AND lvwiki_p.page.page_title = lvwiki_p.image.img_name
AND lvwiki_p.categorylinks.cl_from = lvwiki_p.page.page_id
AND lvwiki_p.categorylinks.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND lvwiki_p.image.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1';


#non free arpus rakstiem
SELECT p.page_title, p2.page_namespace, p2.page_title
#CONCAT('* [[:Attēls:',page_title,']]')
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from
INNER JOIN (imagelinks i LEFT JOIN redirect r ON i.il_from = r.rd_from) ON p.page_title = i.il_to
INNER JOIN page p2 ON p2.page_id = i.il_from
WHERE c.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND i.il_from_namespace != 0 and (p2.page_title not like "Kopienas%" and p2.page_title not like "Vai_tu%") and i.il_from_namespace!=2
AND r.rd_from IS NULL;

#vikikratuve cits nos
SELECT 
  DISTINCT p.page_title,
  ci.img_name
  #il.il_from
FROM image i, commonswiki_p.image ci, categorylinks cl, page p
#left laikam ir lēnāks...
join imagelinks il on il.il_to=p.page_title
#join page p2 on il.il_from=p2.page_title
WHERE i.img_sha1 = ci.img_sha1 and p.page_title != ci.img_name
AND p.page_title = i.img_name
#and p2.page_namespace=10
AND cl.cl_from = p.page_id
AND i.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1'
and il.il_from in (select page_id from page where page_namespace=10)
order by il.il_from
limit 100;

#nelauj izmantot commons
SELECT 
e.img_name
FROM image e
INNER JOIN commonswiki_p.image c ON c.img_name = e.img_name AND c.img_sha1 != e.img_sha1;

#parak daudz izmantoti aizs att
SELECT
  page_title,
  COUNT(*)
FROM imagelinks
JOIN (SELECT
        page_id,
        page_title
      FROM page
      JOIN categorylinks
      ON cl_from = page_id
      WHERE cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
      AND page_namespace = 6) AS pgtmp
ON pgtmp.page_title = il_to
GROUP BY il_to
HAVING COUNT(*) > 4
ORDER BY COUNT(*) desc;

#parak lidzigi nosaukumi
SELECT
  LOWER(CONVERT(page_title USING utf8)),
  GROUP_CONCAT(concat("[[:Attēls:",REPLACE(page_title, '_', ' '),"]]") SEPARATOR '{{!}}{{!}}')#,
  #COUNT(*)
FROM page
WHERE page_namespace = 6
GROUP BY 1
HAVING COUNT(*) > 1
LIMIT 1000;

#lieli aizsargati atteli
SELECT
  page_title,
  img_size
FROM page
JOIN image
ON img_name = page_title
JOIN categorylinks
ON cl_from = page_id
WHERE page_namespace = 6
AND cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND img_size > 999999
order by img_size desc;

SQL

#SET @cats = "Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas","Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki","Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas";

use lvwiki_p;
select now();

#dublicates
/*
SELECT p.page_title FROM page p
INNER JOIN (image e JOIN commonswiki_p.image c ON c.img_sha1 = e.img_sha1) ON e.img_name = p.page_title
WHERE p.page_namespace = 6;
*/
#visv izmantotie aizs att
SELECT
  imgtmp.page_title,
  COUNT(cl_to)
FROM page AS pg1
JOIN categorylinks
ON cl_from = pg1.page_id
JOIN (SELECT
        pg2.page_namespace,
        pg2.page_title,
        il_to
      FROM page AS pg2
      JOIN imagelinks
      ON il_from = page_id) AS imgtmp
ON il_to = pg1.page_title
WHERE pg1.page_namespace = 6 and imgtmp.page_namespace=0
AND cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
GROUP BY 
imgtmp.page_namespace,
imgtmp.page_title
HAVING COUNT(cl_to) > 3
ORDER BY COUNT(cl_to) DESC;
/* 
SELECT
  imgtmp.page_title,
  group_concat(pg1.page_title separator '|')
FROM page AS pg1
JOIN categorylinks
ON cl_from = pg1.page_id
JOIN (SELECT
        pg2.page_namespace,
        pg2.page_title,
        il_to
      FROM page AS pg2
      JOIN imagelinks
      ON il_from = page_id) AS imgtmp
ON il_to = pg1.page_title
WHERE pg1.page_namespace = 6 and imgtmp.page_namespace=0
AND cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
GROUP BY 
imgtmp.page_namespace,
imgtmp.page_title
HAVING COUNT(pg1.page_title) > 3
ORDER BY COUNT(pg1.page_title) DESC;

*/

#lvwiki aizs. attēli - ir Commons
SELECT
  DISTINCT(lvwiki_p.page.page_title),
  commonswiki_p.image.img_name
FROM lvwiki_p.image, commonswiki_p.image, lvwiki_p.categorylinks, lvwiki_p.page
WHERE lvwiki_p.image.img_sha1 = commonswiki_p.image.img_sha1 and lvwiki_p.page.page_title != commonswiki_p.image.img_name
AND lvwiki_p.page.page_title = lvwiki_p.image.img_name
AND lvwiki_p.categorylinks.cl_from = lvwiki_p.page.page_id
AND lvwiki_p.categorylinks.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND lvwiki_p.image.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1';

/*

#non free arpus rakstiem
SELECT p.page_title, p2.page_namespace, p2.page_title
#CONCAT('* [[:Attēls:',page_title,']]')
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from
INNER JOIN (imagelinks i LEFT JOIN redirect r ON i.il_from = r.rd_from) ON p.page_title = i.il_to
INNER JOIN page p2 ON p2.page_id = i.il_from
WHERE c.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND i.il_from_namespace != 0 and (p2.page_title not like "Kopienas%" and p2.page_title not like "Vai_tu%") and i.il_from_namespace!=2
AND r.rd_from IS NULL;

*/
#vikikratuve cits nos
SELECT 
  DISTINCT p.page_title,
  ci.img_name
  #il.il_from
FROM image i, commonswiki_p.image ci, categorylinks cl, page p
#left laikam ir lēnāks...
join imagelinks il on il.il_to=p.page_title
#join page p2 on il.il_from=p2.page_title
WHERE i.img_sha1 = ci.img_sha1 and p.page_title != ci.img_name
AND p.page_title = i.img_name
#and p2.page_namespace=10
AND cl.cl_from = p.page_id
AND i.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1'
and il.il_from in (select page_id from page where page_namespace=10)
order by il.il_from
limit 100;
/*
neveiksmīgi - pielikt klāt imagelinks
use lvwiki_p;
SELECT
  DISTINCT(lvwiki_p.page.page_title),
  commonswiki_p.image.img_name, imglinks1.page_namespace, imglinks1.page_title, il_to
FROM lvwiki_p.image, commonswiki_p.image, lvwiki_p.categorylinks, lvwiki_p.page
#$join (SELECT ppp1.page_namespace, ppp1.page_title, il_to
#	FROM lvwiki_p.imagelinks
#	join page ppp1 on il_from=ppp1.page_title) as imglinks1 on imglinks1.il_to=lvwiki_p.page.page_title
WHERE lvwiki_p.image.img_sha1 = commonswiki_p.image.img_sha1 and lvwiki_p.page.page_title != commonswiki_p.image.img_name
AND lvwiki_p.page.page_title = lvwiki_p.image.img_name
AND lvwiki_p.categorylinks.cl_from = lvwiki_p.page.page_id
AND lvwiki_p.categorylinks.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND lvwiki_p.image.img_sha1 != 'phoiac9h4m842xq45sp7s6u21eteeq1';
*/

#nelauj izmantot commons
SELECT 
e.img_name
FROM image e
INNER JOIN commonswiki_p.image c ON c.img_name = e.img_name AND c.img_sha1 != e.img_sha1;

#parak daudz izmantoti aizs att
SELECT
  page_title,
  COUNT(*)
FROM imagelinks
JOIN (SELECT
        page_id,
        page_title
      FROM page
      JOIN categorylinks
      ON cl_from = page_id
      WHERE cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
      AND page_namespace = 6) AS pgtmp
ON pgtmp.page_title = il_to
GROUP BY il_to
HAVING COUNT(*) > 4
ORDER BY COUNT(*) desc;

#parak lidzigi nosaukumi
SELECT
  LOWER(CONVERT(page_title USING utf8)),
  GROUP_CONCAT(page_title SEPARATOR '|'),
  COUNT(*)
FROM page
WHERE page_namespace = 6
GROUP BY 1
HAVING COUNT(*) > 1
LIMIT 1000;

#lieli aizsargati atteli
SELECT
  page_title,
  img_size
FROM page
JOIN image
ON img_name = page_title
JOIN categorylinks
ON cl_from = page_id
WHERE page_namespace = 6
AND cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND img_size > 999999
order by img_size desc;



####dublikati
SELECT p.page_title, c.img_name, count(il.il_from) FROM page p
INNER JOIN (image e JOIN commonswiki_p.image c ON c.img_sha1 = e.img_sha1) ON e.img_name = p.page_title
left join imagelinks il on p.page_title = il.il_to
WHERE p.page_namespace = 6
group by il.il_to;


################ Non-free files used outside articles (lvwiki)
SELECT p.page_title,count(*)
#concat("[[:Attēls:",REPLACE(p.page_title, '_', ' '),"]]"), p2.page_namespace, p2.page_title
#CONCAT('* [[:Attēls:',page_title,']]')
FROM page p
INNER JOIN categorylinks c ON p.page_id = c.cl_from
INNER JOIN (imagelinks i LEFT JOIN redirect r ON i.il_from = r.rd_from) ON p.page_title = i.il_to
INNER JOIN page p2 ON p2.page_id = i.il_from
WHERE c.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",
"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",
"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")
AND i.il_from_namespace != 0 and (p2.page_title not like "Kopienas%" and p2.page_title not like "Vai_tu%") and i.il_from_namespace!=2
AND r.rd_from IS NULL
group by p.page_id
order by 2 desc;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...