User:Revansx/Gource/How to make a wiki evolution video using Gource
< User:Revansx | Gource
What does this software do? edit
This page captures my notes on how to extract edit history data from a mediawiki database and visualize it using the gource software |
Wiki Server Steps edit
Tailor the mysql command for your wiki database edit
1. Find and replace the word "FOO" in the sql query below with the name of your wiki's database..
(
SELECT
CAST(UNIX_TIMESTAMP(rev_timestamp) AS INT) as revtimestamp,
actor_name as username,
IF(rev_parent_id>0, 'M', 'A') as revtype,
CONCAT('FOO/',cl_to,'/',page_title) as pagetitle,
CASE page_namespace WHEN 0 THEN '266DD3'
WHEN 2 THEN 'FB5607'
WHEN 4 THEN 'FFBE0B'
WHEN 6 THEN 'FF006E'
WHEN 8 THEN '2EA513'
WHEN 10 THEN '8338EC'
WHEN 12 THEN 'FF00FF'
WHEN 14 THEN 'E71D36'
WHEN 102 THEN '00FFFF'
WHEN 106 THEN '00FF00'
WHEN 1 OR 3 OR 5 OR 7 OR 9 OR 11 OR 13 OR 15 OR 103 OR 107 THEN 'FFFF00'
ELSE 'FFFFFF' END as color
FROM
( SELECT rev_id,
rev_timestamp,
rev_parent_id,
rev_page,
revactor_actor,
page_id,
page_namespace,
page_title,
actor.actor_id,
actor_name,
cl_from,
COALESCE(cl_to,'TBD') AS cl_to
FROM `wiki_FOO`.revision
LEFT JOIN `wiki_FOO`.page ON page.page_id=rev_page
LEFT JOIN `wiki_FOO`.revision_actor_temp ON revision_actor_temp.revactor_timestamp=rev_timestamp
LEFT JOIN `wiki_FOO`.actor ON actor.actor_id=revactor_actor
LEFT JOIN `wiki_FOO`.categorylinks ON categorylinks.cl_from=rev_page
ORDER BY rev_timestamp ASC
)a
ORDER BY rev_timestamp ASC
);
2. Open a command shell and enter the mysql command line interface with: mysql 3. Then copy and past the tailored sql command above into the mysql CLI RESULT: You should get a complete table of revision data ready for gource to consume Work through any errors in the mysql until you get a nice output table |
|
Upload a mini text file to your wiki edit
- create a text file named "GourceDataFromWiki.csv.txt" with content "123"
- the contents don't matter. It will be over-written later with the gource log file
- Upload "GourceDataFromWiki.csv.txt" to your wiki
Make a shell script to automate the query of the edit history to file and other post-sql editing steps edit
1. Create a shell script called "GetGourceDataFromWiki.sh" on your server
vi GetGourceDataFromWiki.sh
2. Copy the shell script below into GetGourceDataFromWiki.sh
- note that the entire sql statement above is enclosed in
`..`
and so the "`
" has to be escaped as "\\\`
" in the shell script
#!/bin/sh
zResult=`mysql -e "
( SELECT
CAST(UNIX_TIMESTAMP(rev_timestamp) AS INT) as revtimestamp,
actor_name as username,
IF(rev_parent_id>0, 'M', 'A') as revtype,
CONCAT('FOO/',cl_to,'/',page_title) as pagetitle,
CASE page_namespace WHEN 0 THEN '266DD3'
WHEN 2 THEN 'FB5607'
WHEN 4 THEN 'FFBE0B'
WHEN 6 THEN 'FF006E'
WHEN 8 THEN '2EA513'
WHEN 10 THEN '8338EC'
WHEN 12 THEN 'FF00FF'
WHEN 14 THEN 'E71D36'
WHEN 102 THEN '00FFFF'
WHEN 106 THEN '00FF00'
WHEN 1 OR 3 OR 5 OR 7 OR 9 OR 11 OR 13 OR 15 OR 103 OR 107 THEN 'FFFF00'
ELSE 'FFFFFF' END as color
FROM
( SELECT rev_id,
rev_timestamp,
rev_parent_id,
rev_page,
revactor_actor,
page_id,
page_namespace,
page_title,
actor.actor_id,
actor_name,
cl_from,
COALESCE(cl_to,'TBD') AS cl_to
FROM \\\`wiki_FOO\\\`.revision
LEFT JOIN \\\`wiki_FOO\\\`.page ON page.page_id=rev_page
LEFT JOIN \\\`wiki_FOO\\\`.revision_actor_temp ON revision_actor_temp.revactor_timestamp=rev_timestamp
LEFT JOIN \\\`wiki_FOO\\\`.actor ON actor.actor_id=revactor_actor
LEFT JOIN \\\`wiki_FOO\\\`.categorylinks ON categorylinks.cl_from=rev_page
ORDER BY rev_timestamp ASC
)a
ORDER BY rev_timestamp ASC
); "`
# Pipe the result of the sql query to a tab-separated ".dat" file
echo "$zResult" > GourceDataFromWiki.dat
# Convert the native sql "tab" delimited file to a "|" delimited CSV file
sed 's/\t/|/g' GourceDataFromWiki.dat > GourceDataFromWiki.csv
# Remove the headers in line 1 from the file
echo -e "$(sed '1d' GourceDataFromWiki.csv)\n" > GourceDataFromWiki.csv
# Copy the gource-ready file to the place on your wiki server where the text file from step 1 is stored
cp GourceDataFromWiki.csv /opt/data-meza/uploads/FOO/7/7d/GourceDataFromWiki.csv.txt
From a Windows system with a good graphics card edit
Download the gource custom log file to windows edit
https://<YourWikiServer>/FOO/img_auth.php/7/7d/GourceDataFromWiki.csv.txt
Download and install gource edit
Run gource on the log file edit
Open a cmd and run:
"c:\Program Files\Gource\gource.exe" "c:\Users\user1\Desktop\stuff\gource\ptc\GourceDataFromWiki.csv.txt" --file-idle-time 0 --bloom-intensity 0.4 --max-file-lag 0.1 --seconds-per-day 0.5 --auto-skip-seconds 2 --date-format "%Y/%m/%d" --hide dirnames,filenames --font-size 36 --logo "c:\Users\user1\Desktop\stuff\gource\nasa-200x167.png" --title "Some Title"
Ref: https://github.com/acaudwell/Gource/wiki/Controls
Pipe gource into ffmpeg specifying bitrate to get smaller file edit
Open a cmd and run:
"c:\Program Files\Gource\gource.exe" "c:\Users\user1\Desktop\stuff\gource\ptc\GourceDataFromWiki.csv.txt" --file-idle-time 0 --bloom-intensity 0.4 --max-file-lag 0.1 --seconds-per-day 0.5 --auto-skip-seconds 2 --date-format "%Y/%m/%d" --hide dirnames,filenames --font-size 36 -2560x1440 --logo "c:\Users\user1\Desktop\stuff\gource\logo.png" --title "Title" -o - | ffmpeg -y -r 60 -f image2pipe -vcodec ppm -i - -vcodec libx264 -b:v 5M -bufsize 2M "c:\Users\user1\Desktop\GourceWikiEvolution.mp4"
c:\Program Files\Gource\gource.exe" "c:\Users\user1\Desktop\stuff\gource\ptc\GourceDataFromWiki.csv.txt" --file-idle-time 0 --bloom-intensity 0.4 --max-file-lag 0.1 --seconds-per-day 0.5 --auto-skip-seconds 2 --date-format "%Y/%m/%d" --hide dirnames,filenames --font-size 36 -2560x1440 --logo "c:\Users\user1\Desktop\stuff\gource\logo.png" --title "Title" -o - | ffmpeg -y -r 60 -f image2pipe -vcodec ppm -i -vcodec libx264 -b:v 5M -bufsize 2M "c:\Users\user1\Desktop\GourceWikiEvolution.mp4"
Upload to YouTube edit
In the usual way