/**
* Create MySQL tables. Return array( 'success' => array of success strings, 'errors' => array of error strings )
*
*/
function yourls_create_sql_tables()
{
global $ydb;
$error_msg = array();
$success_msg = array();
// Create Table Query
$create_tables = array();
$create_tables[YOURLS_DB_TABLE_URL] = 'CREATE TABLE IF NOT EXISTS `' . YOURLS_DB_TABLE_URL . '` (' . '`keyword` varchar(200) BINARY NOT NULL,' . '`url` text BINARY NOT NULL,' . '`title` text CHARACTER SET utf8,' . '`timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,' . '`ip` VARCHAR(41) NOT NULL,' . '`clicks` INT(10) UNSIGNED NOT NULL,' . ' PRIMARY KEY (`keyword`),' . ' KEY `timestamp` (`timestamp`),' . ' KEY `ip` (`ip`)' . ');';
$create_tables[YOURLS_DB_TABLE_OPTIONS] = 'CREATE TABLE IF NOT EXISTS `' . YOURLS_DB_TABLE_OPTIONS . '` (' . '`option_id` bigint(20) unsigned NOT NULL auto_increment,' . '`option_name` varchar(64) NOT NULL default "",' . '`option_value` longtext NOT NULL,' . 'PRIMARY KEY (`option_id`,`option_name`),' . 'KEY `option_name` (`option_name`)' . ') AUTO_INCREMENT=1 ;';
$create_tables[YOURLS_DB_TABLE_LOG] = 'CREATE TABLE IF NOT EXISTS `' . YOURLS_DB_TABLE_LOG . '` (' . '`click_id` int(11) NOT NULL auto_increment,' . '`click_time` datetime NOT NULL,' . '`shorturl` varchar(200) BINARY NOT NULL,' . '`referrer` varchar(200) NOT NULL,' . '`user_agent` varchar(255) NOT NULL,' . '`ip_address` varchar(41) NOT NULL,' . '`country_code` char(2) NOT NULL,' . 'PRIMARY KEY (`click_id`),' . 'KEY `shorturl` (`shorturl`)' . ') AUTO_INCREMENT=1 ;';
$create_table_count = 0;
$ydb->show_errors = true;
// Create tables
foreach ($create_tables as $table_name => $table_query) {
$ydb->query($table_query);
$create_success = $ydb->query("SHOW TABLES LIKE '{$table_name}'");
if ($create_success) {
$create_table_count++;
$success_msg[] = yourls_s("Table '%s' created.", $table_name);
} else {
$error_msg[] = yourls_s("Error creating table '%s'.", $table_name);
}
}
// Insert data into tables
yourls_update_option('version', YOURLS_VERSION);
yourls_update_option('db_version', YOURLS_DB_VERSION);
yourls_update_option('next_id', 1);
// Insert sample links
yourls_insert_link_in_db('http://planetozh.com/blog/', 'ozhblog', 'planetOzh: Ozh\' blog');
yourls_insert_link_in_db('http://ozh.org/', 'ozh', 'ozh.org');
yourls_insert_link_in_db('http://yourls.org/', 'yourls', 'YOURLS: Your Own URL Shortener');
// Check results of operations
if (sizeof($create_tables) == $create_table_count) {
$success_msg[] = yourls__('YOURLS tables successfully created.');
} else {
$error_msg[] = yourls__('Error creating YOURLS tables.');
}
return array('success' => $success_msg, 'error' => $error_msg);
}
/**
* Add a new link in the DB, either with custom keyword, or find one
*
*/
function yourls_add_new_link($url, $keyword = '', $title = '')
{
// Allow plugins to short-circuit the whole function
$pre = yourls_apply_filter('shunt_add_new_link', false, $url, $keyword, $title);
if (false !== $pre) {
return $pre;
}
$url = yourls_encodeURI($url);
$url = yourls_escape(yourls_sanitize_url($url));
if (!$url || $url == 'http://' || $url == 'https://') {
$return['status'] = 'fail';
$return['code'] = 'error:nourl';
$return['message'] = yourls__('Missing or malformed URL');
$return['errorCode'] = '400';
return yourls_apply_filter('add_new_link_fail_nourl', $return, $url, $keyword, $title);
}
// Prevent DB flood
$ip = yourls_get_IP();
yourls_check_IP_flood($ip);
// Prevent internal redirection loops: cannot shorten a shortened URL
if (yourls_get_relative_url($url)) {
if (yourls_is_shorturl($url)) {
$return['status'] = 'fail';
$return['code'] = 'error:noloop';
$return['message'] = yourls__('URL is a short URL');
$return['errorCode'] = '400';
return yourls_apply_filter('add_new_link_fail_noloop', $return, $url, $keyword, $title);
}
}
yourls_do_action('pre_add_new_link', $url, $keyword, $title);
$strip_url = stripslashes($url);
$return = array();
// duplicates allowed or new URL => store it
if (yourls_allow_duplicate_longurls() || !($url_exists = yourls_url_exists($url))) {
if (isset($title) && !empty($title)) {
$title = yourls_sanitize_title($title);
} else {
$title = yourls_get_remote_title($url);
}
$title = yourls_apply_filter('add_new_title', $title, $url, $keyword);
// Custom keyword provided
if ($keyword) {
yourls_do_action('add_new_link_custom_keyword', $url, $keyword, $title);
$keyword = yourls_escape(yourls_sanitize_string($keyword));
$keyword = yourls_apply_filter('custom_keyword', $keyword, $url, $title);
if (!yourls_keyword_is_free($keyword)) {
// This shorturl either reserved or taken already
$return['status'] = 'fail';
$return['code'] = 'error:keyword';
$return['message'] = yourls_s('Short URL %s already exists in database or is reserved', $keyword);
} else {
// all clear, store !
yourls_insert_link_in_db($url, $keyword, $title);
$return['url'] = array('keyword' => $keyword, 'url' => $strip_url, 'title' => $title, 'date' => date('Y-m-d H:i:s'), 'ip' => $ip);
$return['status'] = 'success';
$return['message'] = yourls_s('%s added to database', yourls_trim_long_string($strip_url));
$return['title'] = $title;
$return['html'] = yourls_table_add_row($keyword, $url, $title, $ip, 0, time());
$return['shorturl'] = YOURLS_SITE . '/' . $keyword;
}
// Create random keyword
} else {
yourls_do_action('add_new_link_create_keyword', $url, $keyword, $title);
$timestamp = date('Y-m-d H:i:s');
$id = yourls_get_next_decimal();
$ok = false;
do {
$keyword = yourls_int2string($id);
$keyword = yourls_apply_filter('random_keyword', $keyword, $url, $title);
if (yourls_keyword_is_free($keyword)) {
if (@yourls_insert_link_in_db($url, $keyword, $title)) {
// everything ok, populate needed vars
$return['url'] = array('keyword' => $keyword, 'url' => $strip_url, 'title' => $title, 'date' => $timestamp, 'ip' => $ip);
$return['status'] = 'success';
$return['message'] = yourls_s('%s added to database', yourls_trim_long_string($strip_url));
$return['title'] = $title;
$return['html'] = yourls_table_add_row($keyword, $url, $title, $ip, 0, time());
$return['shorturl'] = YOURLS_SITE . '/' . $keyword;
} else {
// database error, couldnt store result
$return['status'] = 'fail';
$return['code'] = 'error:db';
$return['message'] = yourls_s('Error saving url to database');
}
$ok = true;
}
$id++;
} while (!$ok);
@yourls_update_next_decimal($id);
}
// URL was already stored
} else {
yourls_do_action('add_new_link_already_stored', $url, $keyword, $title);
$return['status'] = 'fail';
$return['code'] = 'error:url';
$return['url'] = array('keyword' => $url_exists->keyword, 'url' => $strip_url, 'title' => $url_exists->title, 'date' => $url_exists->timestamp, 'ip' => $url_exists->ip, 'clicks' => $url_exists->clicks);
$return['message'] = yourls_s('%s already exists in database', yourls_trim_long_string($strip_url));
$return['title'] = $url_exists->title;
$return['shorturl'] = YOURLS_SITE . '/' . $url_exists->keyword;
}
yourls_do_action('post_add_new_link', $url, $keyword, $title);
$return['statusCode'] = 200;
// regardless of result, this is still a valid request
return yourls_apply_filter('add_new_link', $return, $url, $keyword, $title);
}
function yourls_add_new_link($url, $keyword = '')
{
global $ydb;
if (!$url || $url == 'http://' || $url == 'https://') {
$return['status'] = 'fail';
$return['code'] = 'error:nourl';
$return['message'] = 'Missing URL input';
$return['errorCode'] = '400';
return $return;
}
// Prevent DB flood
$ip = yourls_get_IP();
yourls_check_IP_flood($ip);
// Prevent internal redirection loops: cannot shorten a shortened URL
$url = yourls_escape(yourls_sanitize_url($url));
if (preg_match('!^' . YOURLS_SITE . '/!', $url)) {
if (yourls_is_shorturl($url)) {
$return['status'] = 'fail';
$return['code'] = 'error:noloop';
$return['message'] = 'URL is a short URL';
$return['errorCode'] = '400';
return $return;
}
}
$table = YOURLS_DB_TABLE_URL;
$strip_url = stripslashes($url);
$url_exists = $ydb->get_row("SELECT keyword,url FROM `{$table}` WHERE `url` = '" . $strip_url . "';");
$return = array();
// New URL : store it -- or: URL exists, but duplicates allowed
if (!$url_exists || yourls_allow_duplicate_longurls()) {
// Custom keyword provided
if ($keyword) {
$keyword = yourls_escape(yourls_sanitize_string($keyword));
if (!yourls_keyword_is_free($keyword)) {
// This shorturl either reserved or taken already
$return['status'] = 'fail';
$return['code'] = 'error:keyword';
$return['message'] = 'Short URL ' . $keyword . ' already exists in database or is reserved';
} else {
// all clear, store !
yourls_insert_link_in_db($url, $keyword);
$return['url'] = array('keyword' => $keyword, 'url' => $strip_url, 'date' => date('Y-m-d H:i:s'), 'ip' => $ip);
$return['status'] = 'success';
$return['message'] = $strip_url . ' added to database';
$return['html'] = yourls_table_add_row($keyword, $url, $ip, 0, time());
$return['shorturl'] = YOURLS_SITE . '/' . $keyword;
}
// Create random keyword
} else {
$timestamp = date('Y-m-d H:i:s');
$id = yourls_get_next_decimal();
$ok = false;
do {
$keyword = yourls_int2string($id);
$free = yourls_keyword_is_free($keyword);
$add_url = @yourls_insert_link_in_db($url, $keyword);
$ok = $free && $add_url;
if ($ok === false && $add_url === 1) {
// we stored something, but shouldn't have (ie reserved id)
$delete = yourls_delete_link_by_keyword($keyword);
$return['extra_info'] .= '(deleted ' . $keyword . ')';
} else {
// everything ok, populate needed vars
$return['url'] = array('keyword' => $keyword, 'url' => $strip_url, 'date' => $timestamp, 'ip' => $ip);
$return['status'] = 'success';
$return['message'] = $strip_url . ' added to database';
$return['html'] = yourls_table_add_row($keyword, $url, $ip, 0, time());
$return['shorturl'] = YOURLS_SITE . '/' . $keyword;
}
$id++;
} while (!$ok);
@yourls_update_next_decimal($id);
}
} else {
// URL was already stored
$return['status'] = 'fail';
$return['code'] = 'error:url';
$return['message'] = $strip_url . ' already exists in database';
$return['shorturl'] = YOURLS_SITE . '/' . $url_exists->keyword;
}
$return['statusCode'] = 200;
// regardless of result, this is still a valid request
return $return;
}
