write yourls > polr migration

[Sushubh]

Just discovered this project and to switch from Yourls, a migration tool is pretty much mandatory! Thanks for considering it!

[cydrobolt]

I agree! Will work on a migration and close ticket when one is written.

[cydrobolt]

Reference: https://github.com/YOURLS/YOURLS/wiki/Database-Structure

[vesper8]

did anyone ever make one? I guess it looks pretty simple to do.. just one table after all.. but to migrate the clicks history though that's more tricky. Anyone got something made?

[vesper8]

Well.. 10 minutes later.. here's your migration script ;)

Not sure if it works with the very latest version of yourls but it worked with my installation which is a few years old. I'm sure you can tweak it a bit to work with yours.

INSERT INTO polr.links (short_url, long_url, ip, clicks, creator, secret_key)
SELECT keyword, url, ip, clicks, 'admin', ''
FROM   _yourls.yourls_url

INSERT INTO polr.clicks (ip, country, referer, user_agent, link_id, created_at, updated_at)
SELECT ip_address, country_code, referrer, user_agent, urls.id, click_time, click_time
FROM   _yourls.yourls_log log
INNER JOIN polr.links urls ON log.shorturl = urls.short_url

The last part does a INNER JOIN between tables of two different databases in order to get the numerical id (since yourls used the keyword aka shorturl as the primary key)

[cydrobolt]

@vesper8 thanks for posting your solution! I'll test it against the latest version of YOURLS to see if it still works. If it does, I'll add your SQL to the docs. Thank you!

[cydrobolt]

Also, I just noticed you're missing the crc32 hash on the link table.

Relevant: https://github.com/cydrobolt/polr/blob/master/database/migrations/2017_02_04_025727_add_link_table_indexes.php

You might want to add the crc32 hash to each row in order to enable lookups :)

This SQL statement should add the hashes in MySQL:

UPDATE links SET long_url_hash = crc32(long_url);

[Sushubh]

do tell if this is ready for use. and how one can use it! thanks...

[vesper8]

@cydrobolt thanks for the tip! I also noticed I was missing the referer_host and was incorrectly importing the referer (yourls.referrer = 'direct' should be polr.referer = null)

Here's my updated version including a truncate at the top for my convenience.

# In order to run these mysql queries, you must have both your yourl database and polr database on the same server
# In these examples, the polr database is simply called "polr" and the yourls database is called "yourls"

SET FOREIGN_KEY_CHECKS = 0; 
truncate polr.links;
truncate polr.clicks;
SET FOREIGN_KEY_CHECKS = 1; 

INSERT INTO polr.links (short_url, long_url, long_url_hash, ip, clicks, creator, secret_key, created_at, updated_at)
SELECT keyword, url, crc32(url), ip, clicks, 'admin', '', `timestamp`, `timestamp`
FROM   yourls.yourls_url;

INSERT INTO polr.clicks (ip, country, referer, referer_host, user_agent, link_id, created_at, updated_at)
SELECT ip_address, 
    country_code, 
    CASE referrer
     WHEN 'direct' THEN null
     ELSE referrer
    END as referer, 
    CASE referrer
     WHEN 'direct' THEN null
     ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '/', 3), '://', -1), '/', 1), '?', 1)
    END as referer_host,
    user_agent, 
    urls.id, 
    click_time, 
    click_time
FROM   yourls.yourls_log log
INNER JOIN polr.links urls ON log.shorturl = urls.short_url;

I was able to dig into the statistics in the polr dashboard so all seems to be working now.

Only one thing I don't understand but maybe this is just a missing feature.. is that when inspecting the history for a click in the polr dashboard, it only shows me analytics for clicks in the last 7 days. It shows the grand total of clicks but I can't look at the referer stats for ALL clicks, only clicks in the last 7 days. And there seems to be no way to modify the date range. Is this because I'm importing the data incorrectly or it's just a feature that hasn't been implemented yet?

It sure would be nice to have some stats on "top overall referers" somewhere too.

Looking forward to new features! Cheers!

[cydrobolt]

By default, the stats page fetches data for the last 30 days. It seems unusual that only 7 days of data is being fetched.

It's possible that you only see 7 days because there was no data before 7 days ago. The graphs simply don't show data points that don't exist (e.g no clicks). It's possible that only 7 days in the past month had activity. Could you check if this is the case?

I'll open a new ticket to add an option to adjust the exact date bounds for the data shown. That could be more useful than the hard-coded 30-day bounds. Additionally, we should add data points for days where there are no clicks.

[vesper8]

yea i'm sorry that was my bad, i was working on my dev and using an old db dump so there hadn't been data in the last 3 weeks. It does indeed show the last month. However I have history that spans back several years so yea it would be great to be able to modify the data range with a set of handy calendar date pickers. At the very least if I could modify the GET parameters in the url that would make for a very quick fix

[johndezember]

@vesper8 Thanks! Your script worked great!

I'm running YOURLS v1.7.1 and Polr v2.2.0

I did have to capitalize "TRUNCATE" and add parenthesis around the CASEs before it would run in phpMyAdmin:

# In order to run these mysql queries, you must have both your yourl database and polr database on the same server
# In these examples, the polr database is simply called "polr" and the yourls database is called "yourls"

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE polr.links;
TRUNCATE polr.clicks;
SET FOREIGN_KEY_CHECKS = 1; 

INSERT INTO polr.links (short_url, long_url, long_url_hash, ip, clicks, creator, secret_key, created_at, updated_at)
SELECT keyword, url, crc32(url), ip, clicks, 'admin', '', `timestamp`, `timestamp`
FROM   yourls.yourls_url;

INSERT INTO polr.clicks (ip, country, referer, referer_host, user_agent, link_id, created_at, updated_at)
SELECT ip_address, 
    country_code, 
    (CASE referrer
     WHEN 'direct' THEN null
     ELSE referrer
    END) as referer, 
    (CASE referrer
     WHEN 'direct' THEN null
     ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(referrer, '/', 3), '://', -1), '/', 1), '?', 1)
    END) as referer_host,
    user_agent, 
    urls.id, 
    click_time, 
    click_time
FROM   yourls.yourls_log log
INNER JOIN polr.links urls ON log.shorturl = urls.short_url;