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 | .jpg) |
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