-
spjonez
Member
-
Posts: 213
Threads: 16
Joined: Oct 2014
Reputation:
10
03-15-2017, 11:36 AM
(This post was last modified: 03-16-2017, 06:00 AM by spjonez.)
CI is so awesome. Managed to do it without having to change any application logic!
Here's the code in case anyone else wants it.
application/helpers/geo_helper.php
PHP Code: <?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );
function get_remote_addr( ) { if ( isset( $_SERVER[ 'HTTP_X_FORWARDED_FOR' ] ) && $_SERVER[ 'HTTP_X_FORWARDED_FOR' ] ) { $ips = array_values( array_filter( explode( ',', $_SERVER[ 'HTTP_X_FORWARDED_FOR' ] ) ) );
$ip = end( $ips ); } else { $ip = $_SERVER[ 'REMOTE_ADDR' ]; }
return $ip; }
function haversine( $lat1, $lon1, $lat2, $lon2 ) { $dlat = deg2rad( $lat2 - $lat1 ); $dlon = deg2rad( $lon2 - $lon1 ); $r = 6372.797;
$a = sin( $dlat / 2 ) * sin( $dlat / 2 ) + cos( deg2rad( $lat1 ) ) * cos( deg2rad( $lat2 ) ) * sin( $dlon / 2 ) * sin( $dlon / 2 ); $c = 2 * asin( sqrt( $a ) );
$km = $r * $c;
return $km; }
?>
application/core/MY_Loader.php
PHP Code: <?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );
class MY_Loader extends CI_Loader { /* overloaded methods */
public function database( $params = '', $return = false, $query_builder = null ) { $ci =& get_instance( );
if ( $return === false && $query_builder === null && isset( $ci->db ) && is_object( $ci->db ) && !empty( $ci->db->conn_id) ) { return false; }
require_once( BASEPATH . 'database/DB.php' );
$db =& DB( $params, $query_builder );
$driver = config_item( 'subclass_prefix' ) . 'DB_' . $db->dbdriver . '_driver'; $file = APPPATH . 'libraries/' . $driver . '.php';
if ( file_exists( $file ) === true && is_file( $file ) === true ) { require_once( $file );
$dbo = new $driver( get_object_vars( $db ) ); $db = & $dbo; }
if ( $return === true ) { return $db; }
$ci->db = ''; $ci->db = $db;
return $this; } }
?>
libraries/MY_DB_mysqli_driver.php
PHP Code: <?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );
class MY_DB_mysqli_driver extends CI_DB_mysqli_driver { private $_read = 'write'; private $_rdb = null;
/* internal methods */
private function _q( $sql, $binds = false, $return_object = null ) { return parent::query( $sql, $binds, $return_object ); }
private function _set_reader( ) { $ci =& get_instance( ); $s = $ci->load->is_loaded( 'session' );
if ( $s === 'session' && $ci->session->userdata( '_read_replica' ) !== null ) { $this->_read = $ci->session->userdata( '_read_replica' ); $this->_rdb = $ci->load->database( $this->_read, true ); } else { $ip = get_remote_addr( ); $parts = explode( '.', $ip ); array_pop( $parts ); $ip = implode( '', $parts );
if ( $ip != '12700' ) { $sql = " SELECT ip.latitude, ip.longitude FROM ip_location ip WHERE ip_from LIKE ? LIMIT 1 ";
$result = $this->_q( $sql, $ip . '%' )->row_array( );
if ( empty( $result ) == false ) { $sql = " SELECT db.name, db.latitude, db.longitude FROM db_location db WHERE db.enabled = 1 ";
$servers = $this->_q( $sql )->result_array( );
$read = array( 'name' => 'write', 'reader' => 0, 'distance' => 0, );
foreach ( $servers as $server ) { $h = haversine( $result[ 'latitude' ], $result[ 'longitude' ], (float)$server[ 'latitude' ], (float)$server[ 'longitude' ] );
if ( $h < $read[ 'distance' ] || $read[ 'distance' ] == 0 ) { $read[ 'name' ] = $server[ 'name' ]; $read[ 'reader' ] = (int)$server[ 'reader' ]; $read[ 'distance' ] = $h; }
//echo ( $server[ 'name' ] . ' is ' . $h . ' away from user.<br>' ); }
if ( $read[ 'reader' ] === 1 ) { $this->_read = $read[ 'name' ]; $this->_rdb = $ci->load->database( $this->_read, true );
if ( $s === 'session' ) { $ci->session->set_userdata( '_read_replica', $this->_read ); }
//echo( '<br>' . $this->_read . ' chosen as read replica' ); } else { $this->_rdb = $this;
//echo( '<br>write chosen as read replica' ); } } else { $this->_rdb = $this;
//echo( '<br>write chosen as read replica' ); } } else { $this->_rdb = $this;
//echo( '<br>write chosen as read replica' ); } } }
/* public methods */
public function reader( ) { if ( $this->_rdb === null ) { $this->_set_reader( ); }
return $this->_rdb; }
public function writer( ) { return $this; }
public function query( $sql, $binds = false, $return_object = null ) { $t = strtolower( substr( trim( $sql ), 0, 6 ) );
if ( $t === 'select' ) { return $this->reader( )->_q( $sql, $binds, $return_object ); } else { return $this->_q( $sql, $binds, $return_object ); } } }
?>
migration
PHP Code: <?php if ( !defined( 'BASEPATH' ) ) exit( 'No direct script access allowed' );
class Migration_Geo extends CI_Migration { public function up( ) { $sql = " CREATE TABLE `ip_location` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ip_from` int(10) unsigned NOT NULL, `ip_to` int(10) unsigned NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, PRIMARY KEY (`id`), KEY `ip_location_ip_from_idx` (`ip_from`), KEY `ip_location_ip_to_idx` (`ip_to`), KEY `ip_location_ip_from_to_idx` (`ip_from`,`ip_to`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ";
$this->db->query( $sql );
$sql = " CREATE TABLE `db_location` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `latitude` decimal(7,4) DEFAULT NULL, `longitude` decimal(7,4) DEFAULT NULL, `replica` tinyint(1) unsigned NOT NULL DEFAULT '1', `enabled` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `db_location_enabled_idx` (`enabled`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ";
$this->db->query( $sql );
$data = array( array( 'name' => 'oregon', 'latitude' => '43.8041', 'longitude' => '-120.5542', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'california', 'latitude' => '36.7782', 'longitude' => '-119.4179', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'virginia', 'latitude' => '37.4315', 'longitude' => '-78.6568', 'replica' => '0', 'enabled' => '1' ), array( 'name' => 'ohio', 'latitude' => '40.4172', 'longitude' => '-82.9071', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'saopaulo', 'latitude' => '-23.5505', 'longitude' => '-46.6333', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'ireland', 'latitude' => '53.1423', 'longitude' => '-7.692', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'frankfurt', 'latitude' => '50.1109', 'longitude' => '8.6821', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'london', 'latitude' => '51.5073', 'longitude' => '-0.1277', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'singapore', 'latitude' => '1.352', 'longitude' => '103.8198', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'sydney', 'latitude' => '-33.8688', 'longitude' => '151.2092', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'tokyo', 'latitude' => '35.6894', 'longitude' => '139.6917', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'seoul', 'latitude' => '37.5665', 'longitude' => '126.9779', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'mumbai', 'latitude' => '19.0759', 'longitude' => '72.8776', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'beijing', 'latitude' => '39.9042', 'longitude' => '116.4073', 'replica' => '1', 'enabled' => '0' ), array( 'name' => 'toronto', 'latitude' => '43.6532', 'longitude' => '-79.3831', 'replica' => '1', 'enabled' => '0' ), );
$this->db->insert_batch( 'db_location', $data ); }
public function down( ) { $this->db->query( "DROP TABLE ip_location" ); $this->db->query( "DROP TABLE db_location" ); } }
?>
In your config/database.php file, set your default group to 'write' and create settings that match each enabled 'name' from db_location.
You will need to populate ip_location with a copy of IP2Location. I'd recommend using the paid version the free database is not very accurate and will lead to strange results. You'll also need to update db_location with the lat/lng's of your colo's and flag replica's as 1 and your write master as 0. The set included is for AWS.
|