SQL scratchpad

-- change default collation of tables in a database  
 
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci ; ') 
AS collationchange  
FROM  information_schema.TABLES 
WHERE TABLE_SCHEMA<>'information_schema' AND TABLE_SCHEMA<>'mysql'
AND TABLE_COLLATION <>'utf8_danish_ci' 
AND TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='name of database'
 
UNION DISTINCT  
 
-- change default collation of a fields
 
SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`', TABLE_NAME, '` CHANGE `', COLUMN_NAME, '` `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8 COLLATE utf8_danish_ci ;')
AS collationchange    
FROM  information_schema.COLUMNS 
WHERE COLLATION_NAME <>'utf8_danish_ci'
AND (COLUMN_TYPE LIKE 'varchar%' OR COLUMN_TYPE LIKE '%TEXT') 
AND TABLE_SCHEMA<>'information_schema' 
AND TABLE_SCHEMA='name of database' 
;

-- lage generisk importskript
SELECT 
 
CONCAT('\n-- ', `TABLE_NAME` , '\n'
'INSERT INTO ', `TABLE_SCHEMA` , '.', `TABLE_NAME` , 
'\n\nSELECT \n\t\'\' AS ', 
GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR '\n\t\'\' AS ' ), 
`TABLE_SCHEMA` , '.', `TABLE_NAME` , 
'\nFROM ', `TABLE_SCHEMA` , '.', `TABLE_NAME` , 
'\n; \n' )  
AS createstatement
 
FROM information_schema.COLUMNS 
 
 
-- sett inn navn på database som skal 
WHERE TABLE_SCHEMA IN ('utdno2_geo')
 
-- sett inn liste over tabeller det skal lages skriptfor 
AND TABLE_NAME IN ('node')
 
GROUP BY TABLE_NAME
;

UPDATE files SET html=REPLACE(html,'À','À') ;  	-- capital a, grave accent 
UPDATE files SET html=REPLACE(html,'Á','Á') ;  	-- capital a, acute accent 
UPDATE files SET html=REPLACE(html,'Â','Â') ;  	-- capital a, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ã','Ã') ;  	-- capital a, tilde 
UPDATE files SET html=REPLACE(html,'Ä','Ä') ;  	-- capital a, umlaut mark 
UPDATE files SET html=REPLACE(html,'Å','Å') ;  	-- capital a, ring 
UPDATE files SET html=REPLACE(html,'Æ','Æ') ;  	-- capital ae 
UPDATE files SET html=REPLACE(html,'Ç','Ç') ;  	-- capital c, cedilla 
UPDATE files SET html=REPLACE(html,'È','È') ;  	-- capital e, grave accent 
UPDATE files SET html=REPLACE(html,'É','É') ;  	-- capital e, acute accent 
UPDATE files SET html=REPLACE(html,'Ê','Ê') ;  	-- capital e, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ë','Ë') ;  	-- capital e, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ì','Ì') ;  	-- capital i, grave accent 
UPDATE files SET html=REPLACE(html,'Í','Í') ;  	-- capital i, acute accent 
UPDATE files SET html=REPLACE(html,'Î','Î') ;  	-- capital i, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ï','Ï') ;  	-- capital i, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ð','Ð') ;  	-- capital eth, Icelandic 
UPDATE files SET html=REPLACE(html,'Ñ','Ñ') ;  	-- capital n, tilde 
UPDATE files SET html=REPLACE(html,'Ò','Ò') ;  	-- capital o, grave accent 
UPDATE files SET html=REPLACE(html,'Ó','Ó') ;  	-- capital o, acute accent 
UPDATE files SET html=REPLACE(html,'Ô','Ô') ;  	-- capital o, circumflex accent 
UPDATE files SET html=REPLACE(html,'Õ','Õ') ;  	-- capital o, tilde 
UPDATE files SET html=REPLACE(html,'Ö','Ö') ;  	-- capital o, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ø','Ø') ;  	-- capital o, slash 
UPDATE files SET html=REPLACE(html,'Ù','Ù') ;  	-- capital u, grave accent 
UPDATE files SET html=REPLACE(html,'Ú','Ú') ;  	-- capital u, acute accent 
UPDATE files SET html=REPLACE(html,'Û','Û') ;  	-- capital u, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ü','Ü') ;  	-- capital u, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ý','Ý') ;  	-- capital y, acute accent 
UPDATE files SET html=REPLACE(html,'Þ','Þ') ;  	-- capital THORN, Icelandic 
UPDATE files SET html=REPLACE(html,'ß','ß') ;  	-- small sharp s, German 
UPDATE files SET html=REPLACE(html,'à','à') ;  	-- small a, grave accent 
UPDATE files SET html=REPLACE(html,'á','á') ;  	-- small a, acute accent 
UPDATE files SET html=REPLACE(html,'â','â') ;  	-- small a, circumflex accent 
UPDATE files SET html=REPLACE(html,'ã','ã') ;  	-- small a, tilde 
UPDATE files SET html=REPLACE(html,'ä','ä') ;  	-- small a, umlaut mark 
UPDATE files SET html=REPLACE(html,'å','å') ;  	-- small a, ring 
UPDATE files SET html=REPLACE(html,'æ','æ') ;  	-- small ae 
UPDATE files SET html=REPLACE(html,'ç','ç') ;  	-- small c, cedilla 
UPDATE files SET html=REPLACE(html,'è','è') ;  	-- small e, grave accent 
UPDATE files SET html=REPLACE(html,'é','é') ;  	-- small e, acute accent 
UPDATE files SET html=REPLACE(html,'ê','ê') ;  	-- small e, circumflex accent 
UPDATE files SET html=REPLACE(html,'ë','ë') ;  	-- small e, umlaut mark 
UPDATE files SET html=REPLACE(html,'ì','ì') ;  	-- small i, grave accent 
UPDATE files SET html=REPLACE(html,'í','í') ;  	-- small i, acute accent 
UPDATE files SET html=REPLACE(html,'î','î') ;  	-- small i, circumflex accent 
UPDATE files SET html=REPLACE(html,'ï','ï') ;  	-- small i, umlaut mark 
UPDATE files SET html=REPLACE(html,'ð','ð') ;  	-- small eth, Icelandic 
UPDATE files SET html=REPLACE(html,'ñ','ñ') ;  	-- small n, tilde 
UPDATE files SET html=REPLACE(html,'ò','ò') ;  	-- small o, grave accent 
UPDATE files SET html=REPLACE(html,'ó','ó') ;  	-- small o, acute accent 
UPDATE files SET html=REPLACE(html,'ô','ô') ;  	-- small o, circumflex accent 
UPDATE files SET html=REPLACE(html,'õ','õ') ;  	-- small o, tilde 
UPDATE files SET html=REPLACE(html,'ö','ö') ;  	-- small o, umlaut mark 
UPDATE files SET html=REPLACE(html,'ø','ø') ;  	-- small o, slash 
UPDATE files SET html=REPLACE(html,'ù','ù') ;  	-- small u, grave accent 
UPDATE files SET html=REPLACE(html,'ú','ú') ;  	-- small u, acute accent 
UPDATE files SET html=REPLACE(html,'û','û') ;  	-- small u, circumflex accent 
UPDATE files SET html=REPLACE(html,'ü','ü') ;  	-- small u, umlaut mark 
UPDATE files SET html=REPLACE(html,'ý','ý') ;  	-- small y, acute accent 
UPDATE files SET html=REPLACE(html,'þ','þ') ;  	-- small thorn, Icelandic 
UPDATE files SET html=REPLACE(html,'ÿ','ÿ') ;  	-- small y, umlaut mark

UPDATE files SET html=REPLACE(html,'À','À') ;  -- capital a, grave accent 
UPDATE files SET html=REPLACE(html,'Á','Á') ;  -- capital a, acute accent 
UPDATE files SET html=REPLACE(html,'Â','Â') ;  -- capital a, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ã','Ã') ;  -- capital a, tilde 
UPDATE files SET html=REPLACE(html,'Ä','Ä') ;  -- capital a, umlaut mark 
UPDATE files SET html=REPLACE(html,'Å','Å') ;  -- capital a, ring 
UPDATE files SET html=REPLACE(html,'Æ','Æ') ;  -- capital ae 
UPDATE files SET html=REPLACE(html,'Ç','Ç') ;  -- capital c, cedilla 
UPDATE files SET html=REPLACE(html,'È','È') ;  -- capital e, grave accent 
UPDATE files SET html=REPLACE(html,'É','É') ;  -- capital e, acute accent 
UPDATE files SET html=REPLACE(html,'Ê','Ê') ;  -- capital e, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ë','Ë') ;  -- capital e, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ì','Ì') ;  -- capital i, grave accent 
UPDATE files SET html=REPLACE(html,'Í','Í') ;  -- capital i, acute accent 
UPDATE files SET html=REPLACE(html,'Î','Î') ;  -- capital i, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ï','Ï') ;  -- capital i, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ð','Ð') ;  -- capital eth, Icelandic 
UPDATE files SET html=REPLACE(html,'Ñ','Ñ') ;  -- capital n, tilde 
UPDATE files SET html=REPLACE(html,'Ò','Ò') ;  -- capital o, grave accent 
UPDATE files SET html=REPLACE(html,'Ó','Ó') ;  -- capital o, acute accent 
UPDATE files SET html=REPLACE(html,'Ô','Ô') ;  -- capital o, circumflex accent 
UPDATE files SET html=REPLACE(html,'Õ','Õ') ;  -- capital o, tilde 
UPDATE files SET html=REPLACE(html,'Ö','Ö') ;  -- capital o, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ø','Ø') ;  -- capital o, slash 
UPDATE files SET html=REPLACE(html,'Ù','Ù') ;  -- capital u, grave accent 
UPDATE files SET html=REPLACE(html,'Ú','Ú') ;  -- capital u, acute accent 
UPDATE files SET html=REPLACE(html,'Û','Û') ;  -- capital u, circumflex accent 
UPDATE files SET html=REPLACE(html,'Ü','Ü') ;  -- capital u, umlaut mark 
UPDATE files SET html=REPLACE(html,'Ý','Ý') ;  -- capital y, acute accent 
UPDATE files SET html=REPLACE(html,'Þ','Þ') ;  -- capital THORN, Icelandic 
UPDATE files SET html=REPLACE(html,'ß','ß') ;  -- small sharp s, German 
UPDATE files SET html=REPLACE(html,'à','à') ;  -- small a, grave accent 
UPDATE files SET html=REPLACE(html,'á','á') ;  -- small a, acute accent 
UPDATE files SET html=REPLACE(html,'â','â') ;  -- small a, circumflex accent 
UPDATE files SET html=REPLACE(html,'ã','ã') ;  -- small a, tilde 
UPDATE files SET html=REPLACE(html,'ä','ä') ;  -- small a, umlaut mark 
UPDATE files SET html=REPLACE(html,'å','å') ;  -- small a, ring 
UPDATE files SET html=REPLACE(html,'æ','æ') ;  -- small ae 
UPDATE files SET html=REPLACE(html,'ç','ç') ;  -- small c, cedilla 
UPDATE files SET html=REPLACE(html,'è','è') ;  -- small e, grave accent 
UPDATE files SET html=REPLACE(html,'é','é') ;  -- small e, acute accent 
UPDATE files SET html=REPLACE(html,'ê','ê') ;  -- small e, circumflex accent 
UPDATE files SET html=REPLACE(html,'ë','ë') ;  -- small e, umlaut mark 
UPDATE files SET html=REPLACE(html,'ì','ì') ;  -- small i, grave accent 
UPDATE files SET html=REPLACE(html,'í','í') ;  -- small i, acute accent 
UPDATE files SET html=REPLACE(html,'î','î') ;  -- small i, circumflex accent 
UPDATE files SET html=REPLACE(html,'ï','ï') ;  -- small i, umlaut mark 
UPDATE files SET html=REPLACE(html,'ð','ð') ;  -- small eth, Icelandic 
UPDATE files SET html=REPLACE(html,'ñ','ñ') ;  -- small n, tilde 
UPDATE files SET html=REPLACE(html,'ò','ò') ;  -- small o, grave accent 
UPDATE files SET html=REPLACE(html,'ó','ó') ;  -- small o, acute accent 
UPDATE files SET html=REPLACE(html,'ô','ô') ;  -- small o, circumflex accent 
UPDATE files SET html=REPLACE(html,'õ','õ') ;  -- small o, tilde 
UPDATE files SET html=REPLACE(html,'ö','ö') ;  -- small o, umlaut mark 
UPDATE files SET html=REPLACE(html,'ø','ø') ;  -- small o, slash 
UPDATE files SET html=REPLACE(html,'ù','ù') ;  -- small u, grave accent 
UPDATE files SET html=REPLACE(html,'ú','ú') ;  -- small u, acute accent 
UPDATE files SET html=REPLACE(html,'û','û') ;  -- small u, circumflex accent 
UPDATE files SET html=REPLACE(html,'ü','ü') ;  -- small u, umlaut mark 
UPDATE files SET html=REPLACE(html,'ý','ý') ;  -- small y, acute accent 
UPDATE files SET html=REPLACE(html,'þ','þ') ;  -- small thorn, Icelandic 
UPDATE files SET html=REPLACE(html,'ÿ','ÿ') ;  -- small y, umlaut mark

DROP TABLE IF EXISTS test ;
 
CREATE TABLE test (
  grepxml varchar(255) NOT NULL, 
  KEY grepxml (grepxml)
  )
SELECT topicID AS grepxml 
FROM g_occurrence
;

-- reformat all views made unreadable by the mysql precompiler 
-- configuration options: change WHERE clause to limit to one database 
 
SELECT
CONCAT('-- ', TABLE_NAME, '\n\n',
 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
VIEW_DEFINITION,
'select','SELECT\n\t'),
'from ','\nFROM '),
'on(',' ON('),
'in (\'','\nIN ('),
'left join ','\nLEFT JOIN '),
'`,`',',\n\t'),
'`','')
) AS viewdefinitions 
 
FROM  information_schema.VIEWS
-- WHERE TABLE_NAME LIKE '%grep%'
;

-- object_count = column containing values to be ranked according to.
-- word = column w label or similar 
 
SET @ranking := 0, @previousval:=NULL;
SELECT @ranking := IF(@ranking=@previousval,@ranking,@ranking+1) AS ranking,
@previousval := object_count AS object_count,
word
FROM `h_utdnoordfrekvens`
ORDER BY object_count DESC
;

-- give some permissions to all tables in the database apachelogs
 
CREATE USER 'username'@'localhost' IDENTIFIED BY 'secret';
 
GRANT SELECT, INSERT, UPDATE ON apachelogs.* TO 'logger'@'localhost' IDENTIFIED BY 'secret'  ;

SET GLOBAL group_concat_max_len = 2000;