Permalink
Fetching contributors…
Cannot retrieve contributors at this time
4196 lines (3862 sloc) 250 KB
<?php
set_time_limit(300);
define('DBARRAY_NUM', MYSQLI_NUM);
define('DBARRAY_ASSOC', MYSQLI_ASSOC);
define('DBARRAY_BOTH', MYSQLI_BOTH);
if (!defined('DB_EXPLAIN'))
define('DB_EXPLAIN', false);
if (!defined('DB_QUERIES'))
define('DB_QUERIES', false);
if (version_compare(PHP_VERSION, '5.3.0') < 0) {
die('WebStore requires at least PHP version of 5.3 (5.4 is preferable). Sorry cannot continue installation.');
}
define ('__SUBDIRECTORY__', preg_replace('/\/?\w+\.php$/', '', $_SERVER['PHP_SELF']));
define ('__DOCROOT__', substr(dirname(__FILE__), 0, strlen(dirname(__FILE__)) - strlen(__SUBDIRECTORY__)));
define ('__VIRTUAL_DIRECTORY__', '');
//Installer can only run if the site hasn't been set up
if(file_exists("config/main.php") && isset($_SERVER['REQUEST_URI']) && $_SERVER['REQUEST_URI'] != __SUBDIRECTORY__ . "/install.php?check")
{
header("Location: index.php");
exit;
}
if(isset($_POST['getpg']))
{
if(file_exists("progress.txt"))
{
$c = file_get_contents("progress.txt");
if(empty($c)) $c=1;
echo json_encode(array('result'=>"success",'progress'=>$c));
}
else echo json_encode(array('result'=>"success",'progress'=>1));
return;
}
if(isset($argv)) $arg = arguments($argv); else $arg=array();
if(count($arg))
{
//We're running this install from the command line
//usage: php install.php --dbhost=localhost --dbuser=root
// --dbpass=mypass --dbname=webstore --url=store.example.com
// --oldbname=webstorebak (optional)
//WARNING: This method of install does not run the environment check
//We cannot guarantee all libraries are installed
//(This is because command line php may differ from Apache server php)
if(isset($arg['help'])) showCommandLine();
if(isset($arg['dbupdate']) && $arg['dbupdate']==1)
{
echo "**dbupdate command removed**\n\nUpdates should use yiic since you're already on the command line\n\n";
echo "\nCommand: core/protected/yiic migrate up --interactive=0\n\n";
die();
}
if(file_exists("config/main.php")) die("\nENTER 1 OR 2 FOR INSTRUCTIONS (ENTER 2 TO PAGE)\n\nENTER SEED NUMBER
INITIALIZING...\n\nYOU MUST DESTROY 17 KINGONS IN 30 STARDATES WITH 3 STARBASES\n\n-=--=--=--=--=--=--=--=-\n
*\n STARDATE 2100\n * * CONDITION GREEN\n <*> QUADRANT 5,2\n * SECTOR 5,4\n ENERGY 3000\n SHIELDS 0\n * PHOTON TORPEDOES 10\n-=--=--=--=--=--=--=--=-\nCOMMAND\n\nHey, ensign Wesley, Web Store is already installed!\n\n");
$arrRequired = array('dbhost','dbuser','dbpass','dbname','url');
foreach($arrRequired as $item)
if(!isset($arg[$item]))
showCommandLine();
echo "\n**Installing Web Store**\n\n";
$upgrade=0;
if(isset($arg['dboldname']))
{
$_POST['dboldname']=$arg['dboldname'];
$upgrade=1;
}
$arg=modifyArgs($arg);
downloadLatest();
zipAndFolders();
writeDB($arg['dbhost'],$arg['dbuser'],$arg['dbpass'],$arg['dbname']);
$db = createDbConnection();
$sqlline = 1;
$endline = 99999999;
$tag="";
$saveperc=0;
while($sqlline<=$endline)
{
$retVal = runInstall($db,$sqlline);
$j = json_decode($retVal);
if($j->result=="success")
{
$sqlline = $j->line+1;
$endline = $j->total;
if(isset($j->tag) && $tag != $j->tag)
{
echo $j->tag."\n";
$tag=$j->tag;
}
$perc = round($sqlline/$endline*50,0);
if ($perc != $saveperc)
{
$saveperc = $perc;
echo $perc."%\n";
}
} else {
echo $j->result;
die();
}
}
if(isset($arg['hosted']))
$hosted=1;
else
$hosted=0;
echo "\nLaunching Yii bootstrap\n";
runYii($arg['url'],$arg['scriptname'],1,$upgrade,$hosted);
}
else
{
//We're running this install from the browser
// Set up initial pathing so install can continue
$step = 1;
if (isset($_POST['step']))
$step = preg_replace('/[^0-9]/', '', $_POST['step']);
if (isset($_POST['sqlline']))
{
$db = createDbConnection();
echo runInstall($db,preg_replace('/[^0-9]/', '', $_POST['sqlline']));
exit();
}
switch ($step)
{
case 2:displayFormTwo(); break;
case 3:displayForm(); break;
case 1: default:
$checkenv = xls_check_server_environment();
if ((in_array("fail", $checkenv) && $_SERVER['REQUEST_URI'] != __SUBDIRECTORY__ . "/install.php?ignore")
|| $_SERVER['REQUEST_URI'] == __SUBDIRECTORY__ . "/install.php?check"
) {
displayNotAcceptable($checkenv);
} else {
displayForm();
}
break;
}
}
function showCommandLine()
{
die("\n*error halting*\n\nusage: php install.php --dbhost=localhost --dbuser=root --dbpass=mypass --dbname=webstore --url=store.example.com --dboldname=webstorebak (optional) --hosted=1 (optional)\n\n");
}
function runYii($url,$scriptname,$sqlline=1,$upgrade,$hosted)
{
global $arg;
$_SERVER=array(
'REQUEST_URI' => $scriptname,
'SERVER_NAME' => $url,
'SCRIPT_FILENAME' => realpath(__FILE__),
'SCRIPT_NAME' => $scriptname,
'PHP_SELF' => $scriptname,
'HTTP_USER_AGENT' => 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_4) AppleWebKit/536.30.1 (KHTML, like Gecko)',
'HTTP_HOST' => $url,
'QUERY_STRING' => '',
);
$_SESSION['DUMMY']="nothing"; //force creation of session just in case
//This is the halfway point, we have to switch to the Yii framework now, so let's bootstrap it
$yii=dirname(__FILE__).'/core/framework/yii.php';
$config=dirname(__FILE__).'/config/main.php';
require_once($yii);
$objYii = Yii::createWebApplication($config);
do
{
$_POST['online']=$sqlline;
$_POST['upgrade']=$upgrade;
$_POST['hosted']=$hosted;
ob_start();
if($upgrade)
$objYii->runController("install/upgrade");
else
$objYii->runController("install/install");
$retVal = ob_get_contents();
$j = json_decode($retVal);
if(isset($j->result) && $j->result=="success")
{
ob_clean();
$sqlline = $j->makeline;
$endline = $j->total;
if(isset($j->tag))
echo $j->tag." ";
echo (50+$sqlline)."%\n";
} else die();
ob_end_flush();
} while ($sqlline<50);
if(!isset($arg['dbupdate'])) //IOW only command line db updates
echo "\n** finished **\n\nCustomer needs to go to http://" .$url . str_replace('install.php', 'admin/license', $scriptname) ." to complete installation.\n\n";
}
function xls_check_file_signatures($complete = false)
{
$url = "http://updater.lightspeedretail.com";
$url .= "/webstore/hash";
$json = json_encode(array('version'=> _ws_version()));
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_VERBOSE, 0);
// Turn off the server and peer verification (TrustManager Concept).
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, FALSE);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_HTTPHEADER,
array("Content-type: application/json"));
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $json);
$strXml = curl_exec($ch);
curl_close($ch);
$oXML = new SimpleXMLElement($strXml);
$signatures = $oXML->signatures;
$versions = explode(",",$oXML->versions);
$checked = array();
$checked['<b>--File Signatures Check for ' . _ws_version() . '--</b>'] = "pass";
$fn = unserialize($signatures);
if (!isset($signatures)) {
$checked['Signature File in /core/protected/modules/admin'] = "fail";
}
foreach ($fn as $key => $value) {
if (!file_exists($key)) {
$checked[$key] = "MISSING";
} else {
$hashes = explode(",", $value);
$hashfile = md5_file($key);
if (!in_array($hashfile, $hashes)) {
$checked[$key] = "modified";
} elseif (_ws_version() != $versions[array_search($hashfile, $hashes)] || $complete) {
$checked[$key] = $versions[array_search($hashfile, $hashes)];
}
}
}
return $checked;
}
function makeHtaccess()
{
//Update Rewrite Base in htaccess
$origText = "RewriteBase /";
$replText = "RewriteBase ".$_SERVER['SCRIPT_NAME'];
$replText = str_replace("/install.php", "", $replText);
if ($replText=="RewriteBase ") $replText="RewriteBase /";
$strFileContents = file_get_contents('htaccess');
@$strFileContents2 = file_get_contents('.htaccess');
if ($strFileContents2 && $strFileContents2 ==$replText )
{
//our .htaccess is fine
} elseif ($strFileContents) {
$fp = @fopen('.htaccess', 'w');
if ($fp) {
$str = str_replace($origText, $replText, $strFileContents);
fwrite($fp, $str, strlen($str));
fclose($fp);
} else die("cannot create/update your .htaccess file. Try renaming/removing the existing one and running install again.");
}
//Write robots.txt too
$strFileContents = "User-agent: *
Disallow:
Sitemap: http://www.example.com/store/sitemap.xml
";
$replText = $_SERVER['SERVER_NAME'] . $_SERVER['SCRIPT_NAME'] . "/sitemap.xml";
$replText = str_replace("/install.php", "", $replText);
$fp = @fopen('robots.txt', 'w');
if ($fp) {
$str = str_replace('http://www.example.com/store/sitemap.xml', $replText, $strFileContents);
fwrite($fp, $str, strlen($str));
fclose($fp);
} else die("cannot create/update your robots.txt file. Try renaming/removing the existing one and running install again.");
}
function _ws_version()
{
if(file_exists("core/protected/config/wsver.php"))
{
include_once("core/protected/config/wsver.php");
return XLSWS_VERSION;
}
else return 3;
}
function displayForm()
{
displayHeader();
$dbhost = "localhost";
$dbport = "3306";
$dbuser = ini_get('mysql.default_user');
$dbpass = "";
$dbname = "";
?>
<h2>Welcome!</h2>
<div class="hero-unit">
<p>This process will install the latest Web Store, optionally importing your old 2.x information. This initial page will set up the database for you, and then you will be redirected for additional setup steps. We've made install as simple as possible to get you going on your new eCommerce store!</p>
<p><strong>Warning: Do not close this browser window until your setup has completed. Doing so will cause an incomplete install and you will have to begin again.</strong></p>
</div>
<h2>Install</h2>
<label>Enter your database connection information below. <strong>Note: This database must already exist and be blank.</strong></label>
<!-- Search form with input field and button -->
<form id="installform" action="install.php?<?php if(isset($_GET['debug'])) echo "&debug";if(isset($_GET['qa'])) echo "&qa=".$_GET['qa']; ?>" method="POST" class="well form-search">
<table class="table table-striped">
<tr>
<td nowrap>MySQL Database Host (Server name or IP):</td>
<td><input id="dbhost" name="dbhost" value="<?php echo $dbhost; ?>" type="text" class="input-medium"></td>
</tr>
<tr>
<td nowrap>MySQL Port:</td>
<td><input id="dbport" name="dbport" value="<?php echo $dbport; ?>" type="text" class="input-medium"></td>
</tr>
<tr>
<td nowrap>MySQL Username:</td>
<td><input id="dbuser" name="dbuser" value="<?php echo $dbuser; ?>" type="text" class="input-medium"></td>
</tr>
<tr>
<td nowrap>MySQL Password:</td>
<td><input id="dbpass" name="dbpass" value="<?php echo $dbpass; ?>" type="password" class="input-medium"></td>
</tr>
<tr>
<td nowrap>Database Name:</td>
<td><input id="dbname" name="dbname" value="<?php echo $dbname; ?>" type="text" class="input-medium"></td>
</tr>
</table>
<input type="hidden" name="step" value="2">
<p><strong>If you are upgrading, enter your old database name here. Because this installer copies your database, you cannot use the same database name if upgrading. If your original database was named "webstore" and you wish to keep that name, you will need to rename your existing database to something like "webstoreold" first, and then create a new blank "webstore" database.</strong></p>
<table class="table table-striped">
<tr>
<td nowrap>Web Store 2.x Database Name:</td>
<td><input name="dboldname" value="" type="text" class="input-medium"></td>
</tr>
</table>
<button type="submit" class="btn btn-primary pull-right">Install</button>
<P>&nbsp;</P>
</form>
</div>
<?php
displayFooter();
}
function displayNotAcceptable($checkenv)
{
displayHeader();
$warning_text = "<table class='table table-striped'>";
if (stripos($_SERVER['REQUEST_URI'],"install.php?check") !== false) {
?><h2>System Check</h2><?php
$warning_text .= "<tr><td colspan='2'><b>SYSTEM CHECK for " . _ws_version() . "</b></td></tr>";
$warning_text .= "<tr><td colspan='2'>The chart below shows the results of the system check and if upgrades have been performed.</td></td>";
//For 2.1.x upgrade, have the upgrades been run?
if (stripos($_SERVER['REQUEST_URI'],"install.php?check") !== false) {
//$checkenv = array_merge($checkenv, xls_check_upgrades());
$checkenv = array_merge($checkenv, xls_check_file_signatures());
}
} else {
?>
<h2>Error</h2>
<div class="hero-unit">
<p>Oops, we've detected a problem with your environment that will conflict with Web Store. The environment check results are shown below. Anything that has failed will need to be addressed, then just refresh this page.</p>
</div>
<?php
$warning_text .= "<tr><td colspan='2'><b>CANNOT INSTALL</b></td></tr>";
$warning_text .= "<tr><td colspan='2'>There are issues with your PHP environment which need to be fixed before you can install WebStore. Please check the chart below for required changes to your PHP installation which must be changed, and subdirectories which you need to make writeable. (Making php.ini changes on a web hosting service will vary by company. Please consult their technical support for exact instructions.)</td></td>";
}
$warning_text .= "<tr><td colspan='2'><hr></td></tr>";
$curver = _ws_version();
foreach ($checkenv as $key => $value) {
$warning_text
.= "<tr><td>$key</td><td>" . (($value == "pass" || $value == $curver) ? "$value"
: "<font color='#cc0000'><b>$value</b></font>") . "</td>";
}
$warning_text .= "</table>";
?>
<div>
<?php echo $warning_text; ?>
</div>
<p>&nbsp;</p>
<?php
displayFooter();
}
function displayFormTwo()
{
$sanitized = preg_replace('/[^a-zA-Z0-9\.\,\(\)@#!?_]/', '', $_POST);
writeDB($sanitized['dbhost'],$sanitized['dbuser'],$sanitized['dbpass'],$sanitized['dbname']);
displayHeader();
if (strlen($_POST['dboldname'])>0)
{
$headerstring = "Installing and migrating...";
$quip = "You probably have time to get a coffee.";
}
else
{
$headerstring = "Installing...";
$quip = "This shouldn't take too long.";
}
if (!isset($_POST['dboldname'])) $_POST['dboldname'] = "";
?>
<h2><?php echo $headerstring; ?></h2>
<div class="hero-unit">
<p id="quip"><?php echo $quip; ?></p>
<div class="progress progress-striped active">
<div class="bar" id="progressbar" style="width: 0%;"></div>
</div>
<div id="waitbar">
</div>
<div id="stats"></div>
</div>
<script language="javascript">
var prunning=0;
var pinttimer=0;
var online=1;
var total = 0;
var delay=50;
function startInstall(key) {
document.getElementById('progressbar').style.width = "1%";
pinttimer=self.setInterval(function(){runInstall(key)},50);
runInstall(key);
}
function runInstall(key)
{
if (prunning==1)
{
if(online==2)
{
var postvar = "getpg=1";
$.post("install.php", postvar, function(data)
{
if (data[0]=="{")
{
obj = JSON.parse(data);
if (obj.result=='success' && obj.progress>1) {
document.getElementById('progressbar').style.width = obj.progress + "%";
document.getElementById('progressbar').style.backgroundColor = "#AA0000";
}
}
});
}
return;
}
prunning=1;
var postvar = "sqlline="+ online +
"&dbname=" + "<?php echo $_POST['dbname'] ?>" +
"&dboldname=" + "<?php echo $_POST['dboldname'] ?><?php if(isset($_GET['qa'])) echo "&qa=".$_GET['qa']?><?php if(isset($_GET['debug'])) echo "&debug=1"?>";
//document.getElementById('waitbar').innerHTML = 'about to post '+postvar;
$.ajax({
url: "install.php",
type:'POST',data:postvar,
error: function(jqXHR, textStatus, errorThrown){
delay=delay+1000;
document.getElementById('quip').innerHTML = "Server appears to be throttling connections, setting delay to "+((delay-50)/1000)+ " seconds";
clearInterval(pinttimer);
pinttimer=self.setInterval(function(){runInstall(key)},delay);
prunning=0;
}
}).done(
function(data){
//document.getElementById('waitbar').innerHTML = "got back "+data;
if (data[0]=="{")
{
obj = JSON.parse(data);
if (obj.result=='success')
{
var perc = Math.round((100*(online/obj.total)));
perc = perc/2;
if(perc<1) perc=1;
document.getElementById('progressbar').style.width = perc + "%";
document.getElementById('progressbar').style.backgroundColor = "#149BDF";
if (!obj.tag) obj.tag = "";
document.getElementById('stats').innerHTML = obj.tag;
<?php if(isset($_GET['debug'])): ?>
document.getElementById('stats').innerHTML = obj.tag + " Running line "+online + " of " + obj.total + " (" + perc + "%)";
<?php endif; ?>
if (online==obj.total) {
clearInterval(pinttimer);
prunning=0;
online = 1;
pinttimer=self.setInterval(function(){runUpgrade(key)},delay);
}else {
prunning=0;
if (obj.line) online = (obj.line*1) +1;
else online = online + 1;
if (delay>1050)
{
delay=delay-1000;
document.getElementById('quip').innerHTML =
"Server appears to be throttling connections, setting delay to "+((delay-50)/1000)+ " seconds";
clearInterval(pinttimer);
pinttimer=self.setInterval(function(){runInstall(key)},delay);
}
}
}
}
else {
clearInterval(pinttimer);
if(data.indexOf("Table 'xlsws_customer' already exists")>0)
data = "Helpful information: This appears to be an error caused by installing into a database that is not blank. Web Store 3 requires a blank database to install.\n\n" + data;
data = "An error has occured. If this does not appear to be an issue you can easily remedy based on the information below, please contact Web Store technical support for additional assistance.\n\n" + data;
document.getElementById('progressbar').style.width = 0;
document.getElementById('stats').innerHTML = "";
document.getElementById('quip').innerHTML = "Error, install halted.";
alert(data);
}
//document.getElementById('waitbar').innerHTML = "end of function";
});
}
function runUpgrade(key)
{
if (prunning>2400)
{
clearInterval(pinttimer);
prunning=0;
alert("The install process has become unresponsive. This may indicate a problem with the database. Please contact technical support for additional information. Error information may be available in the xlsws_log table of your database for troubleshooting purposes.");
document.getElementById('progressbar').style.width = 0;
document.getElementById('stats').innerHTML = "Check xlsws_log for error information.";
document.getElementById('quip').innerHTML = "Error, install halted.";
}
if (prunning>0) { prunning++; return; }
prunning=1;
var postvar = "online="+ online + "&total=" + total +
"&dbname=" + "<?php echo $_POST['dbname'] ?>" +
"&dboldname=" + "<?php echo $_POST['dboldname'] ?>";
var exporturl = window.location.href.replace("/install.php", "/install/<?php echo strlen($_POST['dboldname']) > 0 ? 'upgrade' : 'install' ?>");
$.ajax({
url: exporturl,
type:'POST',data:postvar,
error: function(jqXHR, textStatus, errorThrown){
delay=delay+1000;
document.getElementById('quip').innerHTML = "Server appears to be throttling connections, setting delay to "+((delay-50)/1000)+ "seconds";
clearInterval(pinttimer);
pinttimer=self.setInterval(function(){runUpgrade(key)},delay);
prunning=0;
}
}).done(function(data){
if (data[0]=="{")
{
obj = JSON.parse(data);
if (obj.result=='success')
{
total = obj.total;
online = obj.makeline;
var perc = 50 + online;
document.getElementById('progressbar').style.width = perc + "%";
if (!obj.tag) obj.tag = "";
document.getElementById('stats').innerHTML = obj.tag;
<?php if(isset($_GET['debug'])): ?>
document.getElementById('stats').innerHTML = obj.tag + " at " + " (" + perc + "%)";
<?php endif; ?>
if (online==obj.total) {
clearInterval(pinttimer);
window.location.href = window.location.href.replace("/install.php", "/admin/license");
}
else
{
prunning=0;
}
}
else
{
clearInterval(pinttimer);
alert(obj.result);
}
}
else
{
clearInterval(pinttimer);
alert(data);
}
});
}
startInstall();
</script>
<?php
displayFooter();
}
function displayHeader()
{
?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html" lang="en-US">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Web Store Installation</title>
<link rel="stylesheet" href="http://cdn.lightspeedretail.com/bootstrap/css/bootstrap.css">
<style type="text/css">
.header-new {
background: url("http://www.lightspeedretail.com/wp-content/themes/lightspeed/images/bg-header.jpg") repeat scroll 0 0 transparent;
height: 80px;
position: relative;
width: 100%;
z-index: 999;
}
.header-inner {margin: 0 auto; width: 960px; }
.header-new .logo { float: left; padding: 24px 0 0 10px; width: 15%; }
.header-inner .logo a { width: 30px; height: 37px; }
.header-inner .logo a path.logo-flame { fill: #ed5153; }
.header-new .welcome { float: right; padding: 30px 20px 20px 10px; font-size: 28px; }
.table { width: 700px; margin: 0 auto; }
.hero-unit { padding: 20px; }
.hero-unit p { font-size: 0.9em; }
#stats { font-size: 0.7em; }
</style>
<script src="http://cdn.lightspeedretail.com/bootstrap/js/jquery.min.js"></script>
<script src="http://cdn.lightspeedretail.com/bootstrap/js/bootstrap.js"></script>
</head>
<body>
<div class="header-new">
<div class="header-inner">
<div class="logo">
<a class="navigation-secondary-logo" href="https://www.lightspeedhq.com/" rel="home" title="Lightspeed POS">
<svg class="logo-primary" viewBox="0 0 475 110" version="1.1" xmlns="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink">
<path class="logo-flame" d="M36.9,0.9 L41.9,9.6 C42.9,11.4 42.9,13.6 41.9,15.4 L12.1,67.1 L26.2,91.5 C28.4,95.3 32.5,97.7 36.9,97.7 C41.3,97.7 45.4,95.3 47.6,91.5 L61.7,67.1 L58,60.5 L42,88.2 C41,90 39,91.1 37,91.1 C34.9,91.1 33,90 32,88.2 L19.7,67.1 L47.4,19.1 L52.4,27.8 C53.4,29.6 53.4,31.8 52.4,33.6 L33.1,67.1 L36.9,73.7 L57.9,37.3 L71.7,61.2 C73.8,64.9 73.8,69.4 71.7,73.1 L57.7,97.4 C55.6,101.1 48.9,109.4 36.9,109.4 C24.9,109.4 18.3,101.1 16.1,97.4 L2.1,73.1 C-8.43769499e-15,69.4 -8.43769499e-15,64.9 2.1,61.2 L36.9,0.9"></path>
<g class="logo-text" transform="translate(102.000000, 25.000000)">
<rect x="0" y="1.8" width="9.7" height="57.3"></rect>
<circle cx="22.7" cy="6.6" r="5.7"></circle>
<rect x="17.8" y="19.3" width="9.8" height="39.8"></rect>
<path d="M53.3,18.3 C42.2,18.3 33.2,26.1 33.2,38.5 C33.2,50.9 41,58.5 53.4,58.5 C58.3,58.5 63.5,61 63.5,66.5 C63.5,72 59.1,75.1 53.4,75.1 C47.7,75.1 43,71.7 43,66.5 L33.2,66.5 C33.2,77.2 41.8,84.3 53.4,84.3 C64.9,84.3 73.3,77.5 73.3,66.5 C73.3,61.4 71.7,56.6 65.1,53.3 C71.6,50.3 73.5,43.4 73.5,38.4 C73.5,26.1 64.4,18.3 53.3,18.3 L53.3,18.3 Z M53.4,50.4 C47.7,50.4 43,45.5 43,38.5 C43,31.6 47.7,26.5 53.4,26.5 C59.1,26.5 63.7,31.7 63.7,38.5 C63.7,45.4 59,50.4 53.4,50.4 L53.4,50.4 Z"></path>
<path d="M101.9,18.3 C97.4,18.3 93.1,19.7 89.5,24.2 L89.5,1.7 L79.7,1.7 L79.7,59 L89.5,59 L89.5,38.8 C89.5,32.9 93.5,28 99.3,28 C104.5,28 108.3,31.1 108.3,38.3 L108.3,59 L118.1,59 L118.1,37.6 C118.2,25.9 113.2,18.3 101.9,18.3 L101.9,18.3 Z"></path>
<path d="M142,52.3 C140.8,52.3 139.8,51.9 139.2,51.1 C138.6,50.3 138.3,49.1 138.3,47.3 L138.3,27.7 L146.2,27.7 L147.1,19.2 L138.4,19.2 L138.4,8.3 L128.5,9.4 L128.5,19.3 L121,19.3 L121,27.8 L128.5,27.8 L128.5,47.6 C128.5,51.7 129.5,54.8 131.4,56.9 C133.3,59 136.2,60.1 139.9,60.1 C141.6,60.1 143.2,59.9 144.9,59.4 C146.6,58.9 148.1,58.2 149.4,57.3 L146,51 C144.7,51.9 143.3,52.3 142,52.3 L142,52.3 Z"></path>
<path d="M182.3,37.7 C178.3,35.2 173.6,34.9 169,34.6 C166.3,34.4 162.2,33.8 162.2,30.3 C162.2,27.8 164.8,26.4 169.5,26.4 C173.3,26.4 176.6,27.3 179.4,29.9 L184.9,23.5 C180.3,19.5 175.6,18.3 169.3,18.3 C162,18.3 152.4,21.5 152.4,30.7 C152.4,34.5 154.4,38 157.7,40 C161.4,42.3 166.2,42.6 170.3,43.1 C173.1,43.4 177.7,44.1 176.9,48 C176.4,50.7 173.1,51.5 170.8,51.6 C168.3,51.7 165.8,51.5 163.3,50.9 C160.7,50.2 158.6,49 156.3,47.5 L151.3,53.8 C151.6,54 151.9,54.3 151.9,54.3 C158.3,59.6 167.2,61.3 175.3,59.5 C181.4,58.1 186.6,53.7 186.6,47.1 C186.8,43.4 185.6,39.7 182.3,37.7 L182.3,37.7 Z"></path>
<path d="M214.6,18.3 C210.1,18.3 204.8,20.2 201.7,24.6 L201.4,19.2 L191.9,19.2 L191.9,76.5 L201.7,75.4 L201.7,54.4 C204.5,58.7 210.6,60.1 214.8,60.1 C227.5,60.1 234.9,50.6 234.9,39.1 C234.9,27.4 226.8,18.3 214.6,18.3 L214.6,18.3 Z M213.8,51.6 C207.1,51.6 202.6,45.5 202.6,39.3 C202.6,33.1 206.8,26.5 213.8,26.5 C220.9,26.5 225,33.2 225,39.3 C225.1,45.5 220.5,51.6 213.8,51.6 L213.8,51.6 Z"></path>
<path d="M247.7,42.7 C248.8,47.4 253.1,51.5 260.1,51.5 C263.7,51.5 268.5,49.7 270.8,47.4 L277.1,53.6 C272.9,57.9 266,60 260,60 C247.6,60 238.6,51.8 238.6,39.1 C238.6,27.1 247.9,18.3 259.3,18.3 C271.3,18.3 281.3,26.5 280.3,42.7 L247.7,42.7 L247.7,42.7 Z M270.8,35.2 C269.7,30.5 265,26.4 259.3,26.4 C254,26.4 249.1,30 247.7,35.2 L270.8,35.2 L270.8,35.2 Z"></path>
<path d="M293.1,42.7 C294.2,47.4 298.5,51.5 305.5,51.5 C309.1,51.5 313.9,49.7 316.2,47.4 L322.5,53.6 C318.3,57.9 311.4,60 305.4,60 C293,60 284,51.8 284,39.1 C284,27.1 293.3,18.3 304.7,18.3 C316.7,18.3 326.7,26.5 325.7,42.7 L293.1,42.7 L293.1,42.7 Z M316.3,35.2 C315.2,30.5 310.5,26.4 304.8,26.4 C299.5,26.4 294.6,30 293.2,35.2 L316.3,35.2 L316.3,35.2 Z"></path>
<path d="M349.4,60.1 C353.9,60.1 359.2,58.2 362.3,53.8 L362.6,59.1 L372.1,59.1 L372.1,1.8 L362.3,1.8 L362.3,24 C359.5,19.7 353.3,18.4 349.1,18.4 C336.4,18.4 329.1,27.8 329.1,39.4 C329.1,51 337.2,60.1 349.4,60.1 L349.4,60.1 Z M350.1,26.8 C356.8,26.8 361.3,32.9 361.3,39.1 C361.3,45.3 357.1,51.9 350.1,51.9 C343,51.9 338.9,45.2 338.9,39.1 C338.9,32.9 343.4,26.8 350.1,26.8 L350.1,26.8 Z"></path>
</g>
</svg>
</a>
</div>
<div class="welcome">Web Store Installation</div>
</div>
</div>
<div class="container">
<?php
}
function displayFooter()
{
?>
<script>
$('#installform').submit(validate);
function validate(){
var dbhost = $('#dbhost').val();
if (!$.trim(dbhost)) {alert('Database Host is required!'); return false; }
var dbuser = $('#dbuser').val();
if (!$.trim(dbuser)) {alert('Database Username is required!'); return false; }
var dbpass = $('#dbpass').val();
if (!$.trim(dbpass)) {alert('Database Password is required!'); return false; }
var dbname = $('#dbname').val();
if (!$.trim(dbname)) {alert('Database name is required!'); return false; }
}
</script>
</body>
</html>
<?php
}
function writeDB($dbhost,$dbuser,$dbpass,$dbname)
{
if (strlen($dbhost)==0 || strlen($dbuser)==0 || strlen($dbname)==0 || strlen($dbpass)==0)
return json_encode(array('result'=>"Database connection info missing."));
$strtoexport = "<?php
return array(
'connectionString' => 'mysql:host=".$dbhost.";dbname=".$dbname."',
'emulatePrepare' => true,
'username' => '".$dbuser."',
'password' => '".$dbpass."',
'charset' => 'utf8',
'tablePrefix'=>'xlsws_',
'schemaCachingDuration'=>30,
);";
@mkdir("config",0755,true);
$fp2 = fopen("config/wsdb.php","w");
if ($fp2 === false) die("Error, can't write file config/wsdb.php");
fwrite($fp2,$strtoexport);
fclose($fp2);
}
class DB_Class {
var $db;
var $olddb;
var $newdb;
var $schemaNumber = 0;
public function __construct($servername, $dbuser, $dbpassword, $dbname) {
$this->db = new mysqli($servername, $dbuser, $dbpassword);
if (!$this->db)
{ error_log("Unable to connect to Database Server. Invalid server, username or password.",3,"errorlog.txt");
echo("Unable to connect to Database Server. Invalid server, username or password."); die(); }
$this->newdb = $dbname;
}
public function changedb($to)
{
if ($to=="old")
{
$blnSuccess =$this->db->select_db($this->olddb);
if (!$blnSuccess)
{
error_log("Cannot find or use \"".$this->olddb."\" database to upgrade.", 3, "errorlog.txt");
echo ("Cannot find or use \"".$this->olddb."\" database to upgrade.");
die();
}
$this->getSchema();
}
if ($to=="new")
{
$blnSuccess =$this->db->select_db($this->newdb);
if (!$blnSuccess)
{
error_log("Cannot find or use \"".$this->newdb."\" database. Make sure it has been created and is blank", 3, "errorlog.txt");
echo ("Cannot find or use \"".$this->newdb."\" database. Make sure it has been created and is blank.");
die();
}
$this->getSchema();
}
}
public function getSchema()
{
$res = $this->fetch("show tables");
$tablenames= array();
foreach ($res as $row=>$value)
foreach ($value as $k=>$v)
$tablenames[] = $v;
if (in_array('xlsws_configuration',$tablenames))
{
$res = $this->fetch("SHOW COLUMNS FROM xlsws_configuration" );
foreach ($res as $row) $fieldnames[] = $row['Field'];
if(in_array('key',$fieldnames)) $kn = "key";
if(in_array('key_name',$fieldnames)) $kn = "key_name";
if(in_array('value',$fieldnames)) $vn = "value";
if(in_array('key_value',$fieldnames)) $vn = "key_value";
$res = $this->fetch("select `".$vn."` as id from xlsws_configuration where `".$kn."`='DATABASE_SCHEMA_VERSION'");
if (isset($res[0]))
$this->schemaNumber=$res[0]['id'];
else $this->schemaNumber=0;
} else $this->schemaNumber=0;
}
public function query($sql) {
if(!empty($sql))
{
$result = $this->db->query($sql) or die("Invalid query: " . $this->db->error."\n\nwhen attemping to run ".$sql);
return $result;
}
else return;
}
public function fetch($sql) {
$data = array();
$result = $this->query($sql);
while ($row = $result->fetch_array()){
$data[] = $row;
}
return $data;
}
public function add_index($table,$indexname) {
$res = $this->fetch("SHOW INDEXES FROM $table WHERE key_name='$indexname'" );
if($res) return false; //index already exists
$this->query("ALTER TABLE `$table` ADD INDEX `$indexname` (`$indexname`)");
return true;
}
public function add_column($table , $column , $create_sql , $version = false) {
$res = $this->fetch("SHOW COLUMNS FROM $table WHERE Field='$column'" );
if($res) return false;
$this->query($create_sql);
return true;
}
public function check_column_type($table , $column , $type , $misc ,$version = false){
$res = $this->fetch("SHOW COLUMNS FROM $table WHERE Field='$column'" );
if(!$res) return;
$ctype = $res[0]['Type'];
if($ctype != $type)
$this->query("ALTER TABLE `$table` CHANGE `$column` `$column` $type $misc ;");
}
//title,key,value,helper,config,sort,options
public function add_config_key($key,$title,$value,$helper,$config,$sort,$options = null, $template_specific=0, $param=1,$required=null)
{
$res = $this->fetch("SHOW COLUMNS FROM xlsws_configuration" );
foreach ($res as $row) $fields[] = $row['Field'];
if(in_array('key',$fields)) $kn = "key";
if(in_array('key_name',$fields)) $kn = "key_name";
if(in_array('value',$fields)) $vn = "value";
if(in_array('key_value',$fields)) $vn = "key_value";
$conf = $this->fetch("select * from xlsws_configuration where `".$kn."`='".$key."'");
if(!$conf)
{
$res = $this->fetch("SHOW COLUMNS FROM xlsws_configuration" );
foreach ($res as $row) $fieldnames[] = $row['Field'];
$sql = "insert into xlsws_configuration set title='".mysqli_real_escape_string($this->db,$title)."' ";
if(in_array('key',$fieldnames)) $sql .= ", `key`='$key' ";
if(in_array('key_name',$fieldnames)) $sql .= ", `key_name`='$key' ";
if(in_array('value',$fieldnames)) $sql .= ", `value`='$value' ";
if(in_array('key_value',$fieldnames)) $sql .= ", `key_value`='$value' ";
$sql .= ",helper_text='".mysqli_real_escape_string($this->db,$helper)."', `configuration_type_id`='$config', `sort_order`='$sort' ";
if(!is_null($options)) $sql .= ", `options`='".$options."'";
if(in_array('template_specific',$fieldnames)) $sql .= ", `template_specific`=".$template_specific;
if(in_array('param',$fieldnames)) $sql .= ", `param`=".$param;
if(in_array('required',$fieldnames) && !is_null($required)) $sql .= ", `required`=".$required;
$sql .= ", `created`='".date("Y-m-d H:i:s")."'";
$this->query($sql);
}
}
public function add_table($table , $create_sql , $version = false){
$res = $this->fetch("show tables");
foreach ($res as $row=>$value)
foreach ($value as $k=>$v)
$fieldnames[] = $v;
if(!in_array($table,$fieldnames)){
$this->query($create_sql);
}
}
public function update_row($table , $key_column , $key , $value_column , $value , $version = false){
$sql = "UPDATE $table SET $value_column = $value WHERE $key_column = $key ";
$this->query($sql);
}
}
function downloadFile($url)
{
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_VERBOSE, 0);
// Turn off the server and peer verification (TrustManager Concept).
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, FALSE);
curl_setopt($ch, CURLOPT_PROGRESSFUNCTION, 'progressCallback');
if(stripos($url,".zip") !== false)
curl_setopt($ch, CURLOPT_NOPROGRESS, false); // needed to make progress function work
else
curl_setopt($ch, CURLOPT_NOPROGRESS, true); // needed to make progress function work
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$resp = curl_exec($ch);
curl_close($ch);
return $resp;
}
function progressCallback( $download_size, $downloaded, $upload_size, $uploaded_size )
{
if ($download_size == 0)
$progress = 1;
else
$progress = round( ($downloaded / $download_size * 100 ),0);
file_put_contents("progress.txt",$progress);
}
function downloadLatest()
{
if(isset($_POST['debug'])) error_log(__FUNCTION__,3,"error.txt");
//if we've already downloaded and extracted, don't do it twice
if (!file_exists('core') && !file_exists('webstore.zip'))
{
$dest = (isset($_POST['qa']) ? "qa/".$_POST['qa'] : "latestwebstore");
$cdn = (isset($_POST['qa']) ? "webstore-qa" : "webstore-full");
$jLatest= downloadFile("http://updater.lightspeedretail.com/site/".$dest);
$result = json_decode($jLatest);
$strWebstoreInstall = "http://cdn.lightspeedretail.com/webstore/".$cdn."/".$result->latest->filename;
if(isset($_POST['debug'])) error_log("downloading $strWebstoreInstall",3,"error.txt");
$data = downloadFile($strWebstoreInstall);
if (stripos($data,"404 - Not Found")>0 || empty($data))
echo("ERROR downloading ".$result->latest->filename." from Lightspeed");
if(isset($_POST['debug'])) error_log("writing to to".$result->latest->filename,3,"error.txt");
$f=file_put_contents("webstore.zip", $data);
if(isset($_POST['debug'])) error_log("wrote to".$result->latest->filename,3,"error.txt");
if ($f)
{
if(!isset($_POST['debug'])) @unlink("progress.txt");
}
else {
echo("ERROR downloading ".$result->latest->filename." from Lightspeed");
}
}
}
function zipAndFolders()
{
if(isset($_POST['debug'])) error_log(__FUNCTION__,3,"error.txt");
//if we've already downloaded and extracted, don't do it twice
if (!file_exists('core') && file_exists("webstore.zip"))
{
if(isset($_POST['debug'])) error_log("decompressing webstore.zip",3,"error.txt");
decompress("webstore.zip");
if(isset($_POST['debug'])) error_log("removing webstore.zip",3,"error.txt");
if(!isset($_POST['debug'])) @unlink("webstore.zip");
}
// Verify the cache folders exist and if not, create them
// These may fail if cache isn't yet writable, so we ignore errors
// and will get them again after fixing cache
if (!file_exists('assets')) {
@mkdir('assets');
}
if (!file_exists('runtime')) {
@mkdir('runtime');
}
if (!file_exists('runtime/cache')) {
@mkdir('runtime/cache');
}
if (!file_exists('themes')) {
@mkdir('themes');
}
}
/**
* This is actually a copy of our zip.php from Web store from Florian
* This violates our DRY principle but we need everything in the installer
* @param string $zipFile
* @param string $dirFromZip
* @param null $zipDir
* @return bool
*/
function decompress($zipFile = '', $dirFromZip = '', $zipDir=null)
{
if (is_null($zipDir)) $zipDir = getcwd() . '/'; else $zipDir .= '/';
$zip = zip_open($zipDir.$zipFile);
if (is_resource($zip))
{
while ($zip_entry = zip_read($zip))
{
$completePath = $zipDir . dirname(zip_entry_name($zip_entry));
$completeName = $zipDir . zip_entry_name($zip_entry);
//Zip Mac OS hidden folders
if (stripos($completeName,"__MACOSX") === false && stripos($completePath,"__MACOSX") === false) {
// Walk through path to create non existing directories
// This won't apply to empty directories ! They are created further below
if(!file_exists($completePath) && preg_match( '#^' . $dirFromZip .'.*#', dirname(zip_entry_name($zip_entry)) ) )
{
$tmp = '';
foreach(explode('/',$completePath) AS $k)
{
$tmp .= $k.'/';
if(!file_exists($tmp) )
{
@mkdir($tmp, 0777);
}
}
}
if (zip_entry_open($zip, $zip_entry, "r"))
{
if( preg_match( '#^' . $dirFromZip .'.*#', dirname(zip_entry_name($zip_entry)) ) )
{
if ($fd = @fopen($completeName, 'w+'))
{
fwrite($fd, zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)));
fclose($fd);
if (substr($completeName,-5) == '/yiic')
chmod($completeName, 0755);
}
else
{
// We think this was an empty directory
@mkdir($completeName, 0777);
}
zip_entry_close($zip_entry);
}
}
}
}
zip_close($zip);
}
return true;
}
function createDbConnection()
{
//Since we have our db information already saved in the Yii file, read it back here
$dbinfo = require(dirname(__FILE__).'/config/wsdb.php');
$dbuser = $dbinfo['username'];
$dbpassword = $dbinfo['password'];
$connstring = $dbinfo['connectionString'];
$arrC = array();
preg_match('/host=(.*);/', $connstring,$arrC);
$servername = $arrC[1];
preg_match('/dbname=(.*)/',$connstring,$arrC);
$dbname = $arrC[1];
$dboldname = isset($_POST['dboldname']) ? $_POST['dboldname'] : "";
if (strlen($servername)==0 || strlen($dbuser)==0 || strlen($dbname)==0 || strlen($dbpassword)==0)
{
return json_encode(array('result'=>"Database connection info missing."));
}
$db = new DB_Class($servername, $dbuser, $dbpassword, $dbname);
$db->olddb = $dboldname;
$db->newdb = $dbname;
return $db;
}
/* The master function which is called by Javascript. We upgrade line by line */
function runInstall($db,$sqlline = 0)
{
global $arg;
if ($sqlline==0) return;
if (strlen($db->olddb)>0)
$upgrade=1; else $upgrade=0;
//Depending on our scenario, gather SQL strings
//The beginning of our line# process is for migrating
if ($upgrade)
{
$sqlstrings = initialMigrateTables().migrateTwoFiveToThree();
$arrSql = explode(";",$sqlstrings);
$total = count($arrSql)+13;
$lineDeduct=15;
}
else
{
$sqlstrings = ";;;";
$arrSql = explode(";",$sqlstrings);
$total = 16;
$lineDeduct=0;
}
switch ($sqlline)
{
case 1:
if (!file_exists('core') && !file_exists('webstore.zip'))
{
$dest = (isset($_POST['qa']) ? "qa/".$_POST['qa'] : "latestwebstore");
$cdn = (isset($_POST['qa']) ? "webstore-qa" : "webstore-full");
$jLatest= downloadFile("http://updater.lightspeedretail.com/site/".$dest);
$result = json_decode($jLatest);
return json_encode(array('result'=>"success",
'tag'=>'Downloading Web Store program file '.$result->latest->filename.'...','line'=>$sqlline,'total'=>$total,'upgrade'=>$upgrade));
} else
return json_encode(array('result'=>"success",
'tag'=>'Skipping downloading','line'=>$sqlline,'total'=>$total,'upgrade'=>$upgrade));
break;
case 2:
downloadLatest();
return json_encode(array('result'=>"success",
'tag'=>'Extracting Web Store files...','line'=>$sqlline,'total'=>$total,'upgrade'=>$upgrade));
break;
case 3:
zipAndFolders();
$tag = "Starting Install...";
return json_encode(array('result'=>"success",
'tag'=>$tag,'line'=>$sqlline,'total'=>$total,'upgrade'=>$upgrade));
break;
case 4:
if ($upgrade) $db->changedb('old');
if ($upgrade) if ($db->schemaNumber<217) up217($db);
if ($upgrade) $db->changedb('old');
if ($upgrade) if ($db->schemaNumber==217) up250($db,$sqlline);
$tag = "Applying pre-3.0 changes. Line #".$sqlline;
if (!$upgrade)
{
//we're going to skip ahead to the end. The butler did it!
$sqlline=15;
$tag = "Setting up new database, stand by...";
return json_encode(array('result'=>"success",
'tag'=>$tag,'line'=>$sqlline,'total'=>$total,'upgrade'=>$upgrade));
}
break;
case 5:
case 6:
case 7:
case 8:
case 9:
case 10:
case 11:
if ($upgrade) $db->changedb('old');
if ($upgrade) if ($db->schemaNumber==217) up250($db,$sqlline);
$tag = "Applying pre-3.0 changes. Line #".$sqlline;
break;
case 12:
if ($upgrade) $db->changedb('old');
if ($upgrade) if ($db->schemaNumber==250) up251($db);
$tag = "Applying pre-3.0 changes. Line #".$sqlline;
break;
case 13:
if ($upgrade) $db->changedb('old');
if ($upgrade) if ($db->schemaNumber==251) up252($db);
if ($upgrade) prep252($db);
$tag = "Creating new tables. Line #".$sqlline;
break;
case 14:
$db->changedb('new');
initialCreateTables($db); //Create all tables at once
if (!$upgrade)
initialConfigLoad($db);
break;
default:
$db->changedb('new');
if($upgrade)
{
$sqlStringtoRun = trim($arrSql[$sqlline-15],"\n\r\t");
$sqlStringtoRun = str_replace("{newdbname}",$db->newdb,$sqlStringtoRun);
if(!($upgrade==0 && strpos($sqlStringtoRun,'{olddbname}') !== false))
{
$sqlStringtoRun = str_replace("{olddbname}",$db->olddb,$sqlStringtoRun);
$db->query('SET NAMES utf8');
$db->query('SET FOREIGN_KEY_CHECKS=0');
$db->query($sqlStringtoRun);
if ($sqlline<28) $tag = "Creating tables";
if ($sqlline>=28 && $sqlline<=75) $tag = "Processing images table";
}
}
//Build our main.php in config so we can run the system
if ($sqlline==$total)
{
makeHtaccess();
installMainConfig();
if(!isset($arg['hosted']))
$tag = "Downloading default template...";
}
}
if (isset($tag))
{
$retVal = json_encode(array('result'=>"success",'line'=>$sqlline,'tag'=>$tag,'total'=>$total,'upgrade'=>$upgrade));
}
else
{
$retVal = json_encode(array('result' => "success",'line' => $sqlline,'total' => $total,'upgrade' => $upgrade));
}
return $retVal;
}
function installMainConfig()
{
$configtext = file_get_contents("core/protected/config/_main.php");
$fp2 = fopen("config/main.php","w");
fwrite($fp2,$configtext);
fclose($fp2);
}
function createOldConfiguration()
{
//If we're migrating, we need to copy the old config first, then run updates against it
$db = createDbConnection();
$db->query('SET NAMES utf8');
$db->query('SET FOREIGN_KEY_CHECKS=0');
$db->query("CREATE TABLE `xlsws_configuration` (
`rowid` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(64) NOT NULL,
`key` varchar(64) NOT NULL,
`value` mediumtext NULL,
`helper_text` varchar(255) NOT NULL,
`configuration_type_id` int(11) NOT NULL DEFAULT '0',
`sort_order` int(5) DEFAULT NULL,
`modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`created` datetime DEFAULT NULL,
`options` varchar(255) DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `key` (`key`),
KEY `configuration_type_id` (`configuration_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;");
}
function initialCreateTables($db)
{
$sql = "CREATE TABLE `{newdbname}`.`xlsws_customer`
(
`rowid` BIGINT(20) unsigned NOT NULL auto_increment,
`address1_1` VARCHAR(255) DEFAULT NULL,
`address1_2` VARCHAR(255) DEFAULT NULL,
`address2_1` VARCHAR(255) DEFAULT NULL,
`address_2_2` VARCHAR(255) DEFAULT NULL,
`city1` VARCHAR(64) DEFAULT NULL,
`city2` VARCHAR(64) DEFAULT NULL,
`company` VARCHAR(255) DEFAULT NULL,
`country1` VARCHAR(32) DEFAULT NULL,
`country2` VARCHAR(32) DEFAULT NULL,
`currency` VARCHAR(3) DEFAULT NULL,
`email` VARCHAR(255) DEFAULT NULL,
`firstname` VARCHAR(64) DEFAULT NULL,
`pricing_level` INT(11) unsigned DEFAULT 1,
`homepage` VARCHAR(255) DEFAULT NULL,
`id_customer` VARCHAR(32) DEFAULT NULL,
`language` VARCHAR(8) DEFAULT NULL,
`lastname` VARCHAR(64) DEFAULT NULL,
`mainname` VARCHAR(255) DEFAULT NULL,
`mainphone` VARCHAR(32) DEFAULT NULL,
`mainephonetype` VARCHAR(8) DEFAULT NULL,
`phone1` VARCHAR(32) DEFAULT NULL,
`phonetype1` VARCHAR(8) DEFAULT NULL,
`phone2` VARCHAR(32) DEFAULT NULL,
`phonetype2` VARCHAR(8) DEFAULT NULL,
`phone3` VARCHAR(32) DEFAULT NULL,
`phonetype3` VARCHAR(8) DEFAULT NULL,
`phone4` VARCHAR(32) DEFAULT NULL,
`phonetype4` VARCHAR(8) DEFAULT NULL,
`state1` VARCHAR(32) DEFAULT NULL,
`state2` VARCHAR(32) DEFAULT NULL,
`type` VARCHAR(1) DEFAULT NULL,
`user` VARCHAR(32) DEFAULT NULL,
`zip1` VARCHAR(16) DEFAULT NULL,
`zip2` VARCHAR(16) DEFAULT NULL,
`check_same` INT(11) DEFAULT NULL,
`newsletter_subscribe` TINYINT(1) DEFAULT NULL,
`html_email` TINYINT(1) DEFAULT '1',
`password` VARCHAR(255) DEFAULT NULL,
`temp_password` VARCHAR(255) DEFAULT NULL,
`allow_login` TINYINT(1) DEFAULT NULL,
`created` DATETIME NOT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_cart`
(
`rowid` BIGINT(20) unsigned NOT NULL auto_increment,
`id_str` VARCHAR(64) DEFAULT NULL,
`address_bill` VARCHAR(255) DEFAULT NULL,
`address_ship` VARCHAR(255) DEFAULT NULL,
`ship_firstname` VARCHAR(64) DEFAULT NULL,
`ship_lastname` VARCHAR(64) DEFAULT NULL,
`ship_company` VARCHAR(255) DEFAULT NULL,
`ship_address1` VARCHAR(255) DEFAULT NULL,
`ship_address2` VARCHAR(255) DEFAULT NULL,
`ship_city` VARCHAR(64) DEFAULT NULL,
`ship_zip` VARCHAR(10) DEFAULT NULL,
`ship_state` VARCHAR(16) DEFAULT NULL,
`ship_country` VARCHAR(16) DEFAULT NULL,
`ship_phone` VARCHAR(32) DEFAULT NULL,
`zipcode` VARCHAR(10) DEFAULT NULL,
`contact` VARCHAR(255) DEFAULT NULL,
`discount` DOUBLE DEFAULT NULL,
`firstname` VARCHAR(64) DEFAULT NULL,
`lastname` VARCHAR(64) DEFAULT NULL,
`company` VARCHAR(255) DEFAULT NULL,
`name` VARCHAR(255) DEFAULT NULL,
`phone` VARCHAR(64) DEFAULT NULL,
`po` VARCHAR(64) DEFAULT NULL,
`type` MEDIUMINT(9) DEFAULT NULL,
`status` VARCHAR(32) DEFAULT NULL,
`cost_total` DOUBLE DEFAULT NULL,
`currency` VARCHAR(3) DEFAULT NULL,
`currency_rate` DOUBLE DEFAULT NULL,
`datetime_cre` DATETIME DEFAULT NULL,
`datetime_due` DATETIME DEFAULT NULL,
`datetime_posted` DATETIME DEFAULT NULL,
`email` VARCHAR(255) DEFAULT NULL,
`sell_total` DOUBLE DEFAULT NULL,
`printed_notes` TEXT,
`shipping_method` VARCHAR(255) DEFAULT NULL,
`shipping_module` VARCHAR(64) DEFAULT NULL,
`shipping_data` VARCHAR(255) DEFAULT NULL,
`shipping_cost` DOUBLE DEFAULT NULL,
`shipping_sell` DOUBLE DEFAULT NULL,
`payment_method` VARCHAR(255) DEFAULT NULL,
`payment_module` VARCHAR(64) DEFAULT NULL,
`payment_data` VARCHAR(255) DEFAULT NULL,
`payment_amount` DOUBLE DEFAULT NULL,
`tracking_number` VARCHAR(255) DEFAULT NULL,
`fk_tax_code_id` INT(11) UNSIGNED,
`tax_inclusive` TINYINT(1) DEFAULT NULL,
`subtotal` DOUBLE DEFAULT NULL,
`tax1` DOUBLE DEFAULT '0',
`tax2` DOUBLE DEFAULT '0',
`tax3` DOUBLE DEFAULT '0',
`tax4` DOUBLE DEFAULT '0',
`tax5` DOUBLE DEFAULT '0',
`total` DOUBLE DEFAULT NULL,
`count` INT(11) DEFAULT '0',
`downloaded` TINYINT(1) DEFAULT '0',
`user` VARCHAR(32) DEFAULT NULL,
`ip_host` VARCHAR(255) DEFAULT NULL,
`customer_id` BIGINT(20) unsigned DEFAULT NULL,
`gift_registry` BIGINT(20) DEFAULT NULL,
`send_to` VARCHAR(255) DEFAULT NULL,
`submitted` DATETIME DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`linkid` VARCHAR(32) DEFAULT NULL,
`fk_promo_id` INT(5) DEFAULT NULL,
PRIMARY KEY (`rowid`),
KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_cart_item`
(
`rowid` BIGINT(20) unsigned NOT NULL auto_increment,
`cart_id` BIGINT(20) unsigned NOT NULL,
`cart_type` INT(11) DEFAULT '1',
`product_id` BIGINT(20) unsigned NOT NULL,
`code` VARCHAR(255) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`discount` VARCHAR(16) DEFAULT NULL,
`qty` FLOAT NOT NULL,
`sell` DOUBLE NOT NULL,
`sell_base` DOUBLE NOT NULL,
`sell_discount` DOUBLE NOT NULL,
`sell_total` DOUBLE NOT NULL,
`serial_numbers` VARCHAR(255) DEFAULT NULL,
`gift_registry_item` BIGINT(20) DEFAULT NULL,
`datetime_added` DATETIME NOT NULL,
`datetime_mod` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
KEY `cart_id` (`cart_id`),
KEY `code` (`code`),
KEY `product_id` (`product_id`),
KEY `gift_registry_item` (`gift_registry_item`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_cart_messages`
(
`rowid` INT(11) UNSIGNED NOT NULL auto_increment,
`cart_id` BIGINT(20) DEFAULT NULL,
`message` TEXT,
PRIMARY KEY (`rowid`),
KEY `cart_id` (`cart_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_category`
(
`rowid` INT(11) NOT NULL auto_increment,
`name` VARCHAR(64) DEFAULT NULL,
`parent` INT(11) DEFAULT NULL,
`position` INT(11) NOT NULL,
`child_count` INT(11) DEFAULT '1',
`request_url` VARCHAR(255) DEFAULT NULL,
`custom_page` VARCHAR(64) DEFAULT NULL,
`image_id` BIGINT(20) DEFAULT NULL,
`google_id` INT(11) DEFAULT NULL,
`meta_keywords` VARCHAR(255) DEFAULT NULL,
`meta_description` VARCHAR(255) DEFAULT NULL,
`created` DATETIME DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
KEY `name` (`name`),
KEY `parent` (`parent`),
KEY `request_url` (`request_url`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_category_addl`
(
`rowid` INT(11) NOT NULL auto_increment,
`name` VARCHAR(64) DEFAULT NULL,
`parent` INT(11) DEFAULT NULL,
`position` INT(11) NOT NULL,
`created` DATETIME DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
KEY `name` (`name`),
KEY `parent` (`parent`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_configuration`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`title` VARCHAR(64) NOT NULL,
`key` VARCHAR(64) NOT NULL,
`value` MEDIUMTEXT NOT NULL,
`helper_text` VARCHAR(255) NOT NULL,
`configuration_type_id` INT(11) NOT NULL DEFAULT '0',
`sort_order` INT(5) DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created` DATETIME DEFAULT NULL,
`options` VARCHAR(255) DEFAULT NULL,
`template_specific` tinyint(1) DEFAULT '0',
PRIMARY KEY (`rowid`),
UNIQUE KEY `key` (`key`),
KEY `configuration_type_id` (`configuration_type_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_country`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`code` CHAR(2) NOT NULL,
`code_a3` CHAR(3),
`region` CHAR(2) NOT NULL,
`avail` CHAR(1) NOT NULL DEFAULT 'Y',
`sort_order` INT(11) DEFAULT '10',
`country` VARCHAR(255) NOT NULL,
`zip_validate_preg` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `code` (`code`),
KEY `avail` (`avail`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_credit_card`
(
`rowid` INT(11) NOT NULL auto_increment,
`name` VARCHAR(32) NOT NULL,
`length` VARCHAR(16) NOT NULL,
`prefix` VARCHAR(64) NOT NULL,
`sort_order` INT(11) NOT NULL DEFAULT '0',
`enabled` TINYINT(1) NOT NULL,
`validfunc` VARCHAR(32) DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
UNIQUE KEY `name` (`name`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_custom_page`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`key` VARCHAR(32) NOT NULL,
`title` VARCHAR(64) NOT NULL,
`page` MEDIUMTEXT,
`request_url` VARCHAR(255) DEFAULT NULL,
`meta_keywords` VARCHAR(255) DEFAULT NULL,
`meta_description` VARCHAR(255) DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created` DATETIME DEFAULT NULL,
`product_tag` VARCHAR(255) DEFAULT NULL,
`tab_position` INT(11) DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `key` (`key`),
KEY `request_url` (`request_url`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_destination`
(
`rowid` INT(11) NOT NULL auto_increment,
`country` VARCHAR(5) DEFAULT NULL,
`state` VARCHAR(5) DEFAULT NULL,
`zipcode1` VARCHAR(10) DEFAULT NULL,
`zipcode2` VARCHAR(10) DEFAULT NULL,
`taxcode` INT(11) DEFAULT NULL,
`name` VARCHAR(32) DEFAULT NULL,
`base_charge` FLOAT DEFAULT NULL,
`ship_free` FLOAT DEFAULT NULL,
`ship_rate` FLOAT DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_family`
(
`rowid` INT(11) NOT NULL auto_increment,
`family` VARCHAR(255) DEFAULT NULL,
`request_url` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `family` (`family`),
KEY `request_url` (`request_url`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_gift_registry`
(
`rowid` INT(11) NOT NULL auto_increment,
`registry_name` VARCHAR(100) NOT NULL,
`registry_password` VARCHAR(100) NOT NULL,
`registry_description` TEXT,
`event_date` DATE NULL,
`html_content` TEXT NOT NULL,
`ship_option` VARCHAR(100) DEFAULT NULL,
`customer_id` INT(11) NOT NULL,
`gift_code` VARCHAR(100) NOT NULL,
`created` DATETIME NOT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
UNIQUE KEY `gift_code` (`gift_code`),
KEY `customer_id` (`customer_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_gift_registry_items`
(
`rowid` INT(11) NOT NULL auto_increment,
`registry_id` INT(11) NOT NULL,
`product_id` BIGINT(20) unsigned NOT NULL,
`qty` DOUBLE NOT NULL DEFAULT '1',
`registry_status` VARCHAR(50) DEFAULT '0',
`purchase_status` BIGINT(20) DEFAULT '0',
`purchased_by` VARCHAR(100) DEFAULT NULL,
`created` DATETIME NOT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
UNIQUE KEY `rowid` (`rowid`, `registry_id`),
KEY `registry_id` (`registry_id`),
KEY `product_id` (`product_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_modules`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`active` INT(11) DEFAULT NULL,
`file` VARCHAR(64) NOT NULL,
`type` VARCHAR(255) NOT NULL,
`sort_order` INT(5) DEFAULT NULL,
`configuration` MEDIUMTEXT,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created` DATETIME DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `file` (`file`, `type`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_product`
(
`rowid` BIGINT(20) unsigned NOT NULL auto_increment,
`name` VARCHAR(255) NOT NULL,
`image_id` BIGINT(20) DEFAULT NULL,
`class_name` VARCHAR(32) DEFAULT NULL,
`code` VARCHAR(255) NOT NULL,
`current` TINYINT(1) DEFAULT NULL,
`description` MEDIUMTEXT,
`description_short` MEDIUMTEXT,
`family` VARCHAR(255) DEFAULT NULL,
`gift_card` TINYINT(1) DEFAULT NULL,
`inventoried` TINYINT(1) DEFAULT NULL,
`inventory` FLOAT DEFAULT NULL,
`inventory_total` FLOAT DEFAULT NULL,
`inventory_reserved` FLOAT NOT NULL DEFAULT '0',
`inventory_avail` FLOAT NOT NULL DEFAULT '0',
`master_model` TINYINT(1) DEFAULT NULL,
`fk_product_master_id` BIGINT(20) DEFAULT '0',
`product_size` VARCHAR(255) DEFAULT NULL,
`product_color` VARCHAR(255) DEFAULT NULL,
`product_height` FLOAT DEFAULT NULL,
`product_length` FLOAT DEFAULT NULL,
`product_width` FLOAT DEFAULT NULL,
`product_weight` FLOAT DEFAULT '0',
`fk_tax_status_id` BIGINT(20) DEFAULT '0',
`sell` FLOAT DEFAULT NULL,
`sell_tax_inclusive` FLOAT DEFAULT NULL,
`sell_web` FLOAT DEFAULT NULL,
`upc` VARCHAR(255) DEFAULT NULL,
`web` TINYINT(1) DEFAULT NULL,
`web_keyword1` VARCHAR(255) DEFAULT NULL,
`web_keyword2` VARCHAR(255) DEFAULT NULL,
`web_keyword3` VARCHAR(255) DEFAULT NULL,
`request_url` VARCHAR(255) DEFAULT NULL,
`meta_desc` VARCHAR(255) DEFAULT NULL,
`meta_keyword` VARCHAR(255) DEFAULT NULL,
`featured` TINYINT(1) NOT NULL DEFAULT '0',
`created` DATETIME DEFAULT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
KEY `code` (`code`),
KEY `web` (`web`),
KEY `name` (`name`),
KEY `fk_product_master_id` (`fk_product_master_id`),
KEY `master_model` (`master_model`),
KEY `fk_tax_status_id` (`fk_tax_status_id`),
KEY `featured` (`featured`),
KEY `request_url` (`request_url`),
KEY `image_id` (`image_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_images`
(
`id` BIGINT(20) unsigned NOT NULL auto_increment,
`image_path` VARCHAR(255) DEFAULT NULL,
`width` MEDIUMINT(9) DEFAULT NULL,
`height` MEDIUMINT(9) DEFAULT NULL,
`parent` BIGINT(20) DEFAULT NULL,
`index` int(11) DEFAULT NULL,
`product_id` bigint(20) unsigned DEFAULT NULL,
`created` DATETIME NOT NULL,
`modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `width` (`width`, `height`, `parent`),
KEY `index` (`index`),
KEY `product_id` (`product_id`),
KEY `image_path` (`image_path`),
KEY `parent` (`parent`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_product_category_assn`
(
`product_id` BIGINT(20) unsigned NOT NULL,
`category_id` INT(11) unsigned NOT NULL,
PRIMARY KEY (`product_id`, `category_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_product_qty_pricing`
(
`rowid` BIGINT(20) unsigned NOT NULL auto_increment,
`product_id` BIGINT(20) unsigned NOT NULL,
`pricing_level` INT(11) DEFAULT NULL,
`qty` FLOAT DEFAULT NULL,
`price` FLOAT DEFAULT NULL,
PRIMARY KEY (`rowid`),
KEY `product_id` (`product_id`),
KEY `product_id_2` (`product_id`, `pricing_level`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_product_related`
(
`rowid` BIGINT(20) unsigned NOT NULL auto_increment,
`product_id` BIGINT(20) unsigned NOT NULL,
`related_id` BIGINT(20) unsigned NOT NULL,
`autoadd` TINYINT(1) DEFAULT NULL,
`qty` FLOAT DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `product_id` (`product_id`, `related_id`),
KEY `product_id_2` (`product_id`),
KEY `related_id` (`related_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_promo_code`
(
`rowid` INT(11) NOT NULL auto_increment,
`enabled` TINYINT(1) NOT NULL DEFAULT '1',
`except` TINYINT(1) NOT NULL DEFAULT '0',
`code` VARCHAR(255) DEFAULT NULL,
`type` INT(11) DEFAULT '0',
`amount` DOUBLE NOT NULL,
`valid_from` DATE NULL,
`qty_remaining` INT(11) NOT NULL DEFAULT '-1',
`valid_until` DATE NULL,
`lscodes` LONGTEXT NULL,
`threshold` DOUBLE NOT NULL DEFAULT '0',
PRIMARY KEY (`rowid`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_shipping_tiers`
(
`rowid` INT(11) NOT NULL auto_increment,
`start_price` DOUBLE DEFAULT '0',
`end_price` DOUBLE DEFAULT '0',
`rate` DOUBLE DEFAULT '0',
`class_name` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`rowid`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_sro`
(
`rowid` INT(11) NOT NULL auto_increment,
`ls_id` VARCHAR(20) DEFAULT NULL,
`customer_name` VARCHAR(255) DEFAULT NULL,
`customer_email_phone` VARCHAR(255) NOT NULL,
`zipcode` VARCHAR(10) DEFAULT NULL,
`problem_description` MEDIUMTEXT,
`printed_notes` MEDIUMTEXT,
`work_performed` MEDIUMTEXT,
`additional_items` MEDIUMTEXT,
`warranty` MEDIUMTEXT,
`warranty_info` MEDIUMTEXT,
`status` VARCHAR(32) DEFAULT NULL,
`cart_id` BIGINT(20) DEFAULT NULL,
`datetime_cre` DATETIME DEFAULT NULL,
`datetime_mod` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `ls_id` (`ls_id`),
KEY `cart_id` (`cart_id`),
KEY `customer_email_phone` (`customer_email_phone`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_sro_repair`
(
`rowid` INT(11) NOT NULL auto_increment,
`sro_id` VARCHAR(20) DEFAULT NULL,
`family` VARCHAR(255) DEFAULT NULL,
`description` VARCHAR(255) DEFAULT NULL,
`purchase_date` VARCHAR(32) DEFAULT NULL,
`serial_number` VARCHAR(255) DEFAULT NULL,
`datetime_cre` DATETIME DEFAULT NULL,
`datetime_mod` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`rowid`),
KEY `sro_id` (`sro_id`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_state` (
`rowid` int(11) unsigned NOT NULL AUTO_INCREMENT,
`country_code` char(2) NOT NULL,
`code` varchar(32) NOT NULL,
`avail` char(1) NOT NULL DEFAULT 'Y',
`sort_order` int(11) DEFAULT '10',
`state` varchar(255) NOT NULL,
PRIMARY KEY (`rowid`),
UNIQUE KEY `country_code` (`country_code`,`code`),
KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_tax`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`lsid` INT(11) unsigned NOT NULL,
`tax` CHAR(32),
`max` DOUBLE DEFAULT '0',
`compounded` TINYINT(1) DEFAULT '0',
PRIMARY KEY (`rowid`),
KEY `tax` (`tax`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_tax_code`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`lsid` INT(11) unsigned NOT NULL,
`code` CHAR(32) NOT NULL,
`list_order` INT(11) NOT NULL DEFAULT '0',
`tax1_rate` DOUBLE NOT NULL DEFAULT '0',
`tax2_rate` DOUBLE NOT NULL DEFAULT '0',
`tax3_rate` DOUBLE NOT NULL DEFAULT '0',
`tax4_rate` DOUBLE NOT NULL DEFAULT '0',
`tax5_rate` DOUBLE NOT NULL DEFAULT '0',
PRIMARY KEY (`rowid`),
UNIQUE KEY `code` (`code`)
)
engine=innodb
DEFAULT charset=utf8;
CREATE TABLE `{newdbname}`.`xlsws_tax_status`
(
`rowid` BIGINT(20) NOT NULL auto_increment,
`lsid` INT(11) unsigned NOT NULL,
`status` CHAR(32) NOT NULL,
`tax1_status` TINYINT(1) NOT NULL DEFAULT '1',
`tax2_status` TINYINT(1) NOT NULL DEFAULT '1',
`tax3_status` TINYINT(1) NOT NULL DEFAULT '1',
`tax4_status` TINYINT(1) NOT NULL DEFAULT '1',
`tax5_status` TINYINT(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`rowid`)
)
engine=innodb
DEFAULT charset=utf8;";
$arrSql = explode(";",$sql);
foreach($arrSql as $sqlline)
{
$sqlStringtoRun = trim($sqlline,"\n\r\t");
$sqlStringtoRun = str_replace("{newdbname}",$db->newdb,$sqlStringtoRun);
$db->query($sqlStringtoRun);
}
}
function initialMigrateTables()
{
return "INSERT INTO `{newdbname}`.`xlsws_customer`
SELECT *
FROM `{olddbname}`.`xlsws_customer`;
INSERT INTO `{newdbname}`.`xlsws_cart`
SELECT *
FROM `{olddbname}`.`xlsws_cart`;
INSERT INTO `{newdbname}`.`xlsws_cart_item`
SELECT *
FROM `{olddbname}`.`xlsws_cart_item`;
INSERT INTO `{newdbname}`.`xlsws_cart_messages`
SELECT *
FROM `{olddbname}`.`xlsws_cart_messages`;
INSERT INTO `{newdbname}`.`xlsws_category`
SELECT *
FROM `{olddbname}`.`xlsws_category`;
INSERT INTO `{newdbname}`.`xlsws_category_addl`
SELECT *
FROM `{olddbname}`.`xlsws_category_addl`;
INSERT INTO `{newdbname}`.`xlsws_configuration`
SELECT *
FROM `{olddbname}`.`xlsws_configuration`;
INSERT INTO `{newdbname}`.`xlsws_country`
SELECT *
FROM `{olddbname}`.`xlsws_country`;
INSERT INTO `{newdbname}`.`xlsws_credit_card`
SELECT *
FROM `{olddbname}`.`xlsws_credit_card`;
INSERT INTO `{newdbname}`.`xlsws_custom_page`
SELECT *
FROM `{olddbname}`.`xlsws_custom_page`;
INSERT INTO `{newdbname}`.`xlsws_destination`
SELECT *
FROM `{olddbname}`.`xlsws_destination`;
INSERT INTO `{newdbname}`.`xlsws_family`
SELECT *
FROM `{olddbname}`.`xlsws_family`;
INSERT INTO `{newdbname}`.`xlsws_gift_registry`
SELECT *
FROM `{olddbname}`.`xlsws_gift_registry`;
INSERT INTO `{newdbname}`.`xlsws_gift_registry_items`
SELECT *
FROM `{olddbname}`.`xlsws_gift_registry_items`;
INSERT INTO `{newdbname}`.`xlsws_modules`
SELECT *
FROM `{olddbname}`.`xlsws_modules`;
INSERT INTO `{newdbname}`.`xlsws_product`
SELECT *
FROM `{olddbname}`.`xlsws_product`;
INSERT INTO `{newdbname}`.`xlsws_product_category_assn`
SELECT *
FROM `{olddbname}`.`xlsws_product_category_assn`;
INSERT INTO `{newdbname}`.`xlsws_product_qty_pricing`
SELECT *
FROM `{olddbname}`.`xlsws_product_qty_pricing`;
INSERT INTO `{newdbname}`.`xlsws_product_related`
SELECT *
FROM `{olddbname}`.`xlsws_product_related`;
INSERT INTO `{newdbname}`.`xlsws_images` (id,image_path,width,height,parent,created,modified) SELECT rowid,image_path,width,height,parent,created,modified FROM `{olddbname}`.`xlsws_images`;
update `{newdbname}`.`xlsws_images` as a left join `{newdbname}`.`xlsws_product` as b on b.image_id=a.id set a.product_id=b.rowid;
update `{newdbname}`.`xlsws_images` as a left join `{newdbname}`.`xlsws_images` as b on b.id=a.parent set a.product_id=b.product_id where a.id<>a.parent;
UPDATE `{newdbname}`.`xlsws_images` set `index`=0 where product_id IS NOT NULL;
update `{newdbname}`.`xlsws_images` as a left join `{olddbname}`.`xlsws_product_image_assn` as b on b.image_id=a.id set a.product_id=b.product_id where a.product_id is null and b.product_id is not null;
update `{newdbname}`.`xlsws_images` as a left join `{newdbname}`.`xlsws_images` as b on b.id=a.parent set a.product_id=b.product_id where a.id<>a.parent and a.product_id is null;
update `{newdbname}`.`xlsws_images` set `index`=4 where `index` is null and image_path is not null and image_path like '%_3_add.%';
update `{newdbname}`.`xlsws_images` set `index`=4 where `index` is null and image_path is not null and image_path like '%-add-3.%';
update `{newdbname}`.`xlsws_images` set `index`=4 where `index` is null and image_path is not null and image_path like '%-add-3-%';
update `{newdbname}`.`xlsws_images` set `index`=4 where `index` is null and image_path is not null and image_path like '%_2_add.%';
update `{newdbname}`.`xlsws_images` set `index`=3 where `index` is null and image_path is not null and image_path like '%-add-2.%';
update `{newdbname}`.`xlsws_images` set `index`=3 where `index` is null and image_path is not null and image_path like '%-add-2-%';
update `{newdbname}`.`xlsws_images` set `index`=2 where `index` is null and image_path is not null and image_path like '%_1_add.%';
update `{newdbname}`.`xlsws_images` set `index`=2 where `index` is null and image_path is not null and image_path like '%-add-1.%';
update `{newdbname}`.`xlsws_images` set `index`=2 where `index` is null and image_path is not null and image_path like '%-add-1-%';
update `{newdbname}`.`xlsws_images` set `index`=1 where `index` is null and image_path is not null and image_path like '%_add.%';
update `{newdbname}`.`xlsws_images` set `index`=1 where `index` is null and image_path is not null and image_path like '%-add.%';
update `{newdbname}`.`xlsws_images` set `index`=1 where `index` is null and image_path is not null and image_path like '%-add-%';
INSERT INTO `{newdbname}`.`xlsws_promo_code`
SELECT *
FROM `{olddbname}`.`xlsws_promo_code`;
INSERT INTO `{newdbname}`.`xlsws_shipping_tiers`
SELECT *
FROM `{olddbname}`.`xlsws_shipping_tiers`;
INSERT INTO `{newdbname}`.`xlsws_sro`
SELECT *
FROM `{olddbname}`.`xlsws_sro`;
INSERT INTO `{newdbname}`.`xlsws_sro_repair`
SELECT *
FROM `{olddbname}`.`xlsws_sro_repair`;
INSERT INTO `{newdbname}`.`xlsws_state`
SELECT *
FROM `{olddbname}`.`xlsws_state`;
INSERT INTO `{newdbname}`.`xlsws_tax` (lsid,tax,`max`,compounded) SELECT rowid,`tax`,`max`,compounded FROM `{olddbname}`.`xlsws_tax`;
INSERT INTO `{newdbname}`.`xlsws_tax_code` (lsid,code,list_order,tax1_rate,tax2_rate,tax3_rate,tax4_rate,tax5_rate) SELECT rowid,code,list_order,tax1_rate,tax2_rate,tax3_rate,tax4_rate,tax5_rate FROM `{olddbname}`.`xlsws_tax_code`;
INSERT INTO `{newdbname}`.`xlsws_tax_status`
(lsid,status,tax1_status,tax2_status,tax3_status,tax4_status,tax5_status)
SELECT rowid,status,tax1_status,tax2_status,tax3_status,tax4_status,tax5_status FROM `{olddbname}`.`xlsws_tax_status`;";
}
function migrateTwoFiveToThree()
{
return "alter table `{newdbname}`.xlsws_cart CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_cart_item CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_cart_messages CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_category CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_category CHANGE `parent` `parent` INT(11) unsigned NULL;
alter table `{newdbname}`.xlsws_category_addl CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_configuration CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_country CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_credit_card CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_custom_page CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_customer CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_destination CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_family CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_gift_registry CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_gift_registry_items CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_modules CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_product CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_product_qty_pricing CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_product_related CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_promo_code CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_shipping_tiers CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_sro CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_sro_repair CHANGE `rowid` `id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_sro_repair CHANGE `sro_id` `sro_id` BIGINT(20) unsigned NOT NULL;
alter table `{newdbname}`.xlsws_state CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_tax CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_tax_code CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
alter table `{newdbname}`.xlsws_tax_status CHANGE `rowid` `id` INT(11) unsigned NOT NULL AUTO_INCREMENT;
ALTER TABLE `{newdbname}`.`xlsws_cart` CHANGE `name` `full_name` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_cart` CHANGE `type` `cart_type` MEDIUMINT(9) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_cart` CHANGE `lastname` `last_name` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_cart` CHANGE `firstname` `first_name` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_cart` CHANGE `count` `item_count` INT(11) NULL DEFAULT '0';
ALTER TABLE `{newdbname}`.`xlsws_cart` CHANGE `user` `lightspeed_user` VARCHAR(32) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_category` CHANGE `name` `label` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_category` CHANGE `position` `menu_position` INT(11) NOT NULL;
ALTER TABLE `{newdbname}`.`xlsws_category_addl` CHANGE `name` `label` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_category_addl` CHANGE `position` `menu_position` INT(11) NOT NULL;
ALTER TABLE `{newdbname}`.`xlsws_configuration` CHANGE `key` `key_name` VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_configuration` CHANGE `value` `key_value` MEDIUMTEXT NOT NULL;
ALTER TABLE `{newdbname}`.`xlsws_credit_card` CHANGE `name` `label` VARCHAR(32) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_credit_card` CHANGE `length` `numeric_length` VARCHAR(16) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_custom_page` CHANGE `key` `page_key` VARCHAR(32) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `language` `preferred_language` VARCHAR(8) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `type` `record_type` INT(11) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `user` `lightspeed_user` VARCHAR(32) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD `facebook` BIGINT(20) unsigned NULL DEFAULT NULL AFTER `lightspeed_user`;
ALTER TABLE `{newdbname}`.`xlsws_destination` CHANGE `name` `label` VARCHAR(32) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_product` CHANGE `name` `title` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_modules` CHANGE `type` `category` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_product` CHANGE `description` `description_long` MEDIUMTEXT NULL;
ALTER TABLE `{newdbname}`.`xlsws_modules` CHANGE `file` `module` VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE `{newdbname}`.`xlsws_product` CHANGE `fk_product_master_id` `parent` BIGINT(20) unsigned NULL;
ALTER TABLE `{newdbname}`.`xlsws_product` CHANGE `image_id` `image_id` BIGINT(20) unsigned NULL;
ALTER TABLE `{newdbname}`.`xlsws_promo_code` CHANGE `except` `exception` TINYINT(1) NOT NULL DEFAULT '0';
ALTER TABLE `{newdbname}`.`xlsws_tax` CHANGE `max` `max_tax` DOUBLE NULL DEFAULT '0';
ALTER TABLE `{newdbname}`.`xlsws_state` ADD `country_id` INT UNSIGNED NULL DEFAULT NULL AFTER `id`;
DROP TABLE IF EXISTS `{newdbname}`.`xlsws_log`;
create table `{newdbname}`.`xlsws_log`
(
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
level VARCHAR(128),
category VARCHAR(128),
logtime INTEGER,
message LONGTEXT,
created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY `createdidx` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_sessions` (
`id` char(32) NOT NULL,
`expire` int(11) DEFAULT NULL,
`created` TIMESTAMP ,
`modified` TIMESTAMP,
`data` blob,
PRIMARY KEY (`id`),
KEY `yiisession_expire_idx` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_transaction_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`cart_id` bigint(20) unsigned DEFAULT NULL,
`logline` varchar(255) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_cart` (`cart_id`),
CONSTRAINT `fk_cart` FOREIGN KEY (`cart_id`) REFERENCES `xlsws_cart` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_sro_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`sro_id` bigint(20) unsigned NOT NULL,
`cart_type` int(11) DEFAULT '1',
`product_id` bigint(20) unsigned NOT NULL,
`code` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`discount` varchar(16) DEFAULT NULL,
`qty` float NOT NULL,
`sell` double NOT NULL,
`sell_base` double NOT NULL,
`sell_discount` double NOT NULL,
`sell_total` double NOT NULL,
`serial_numbers` varchar(255) DEFAULT NULL,
`datetime_added` datetime NOT NULL,
`datetime_mod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `code` (`code`),
KEY `product_id` (`product_id`),
KEY `sro_id` (`sro_id`),
CONSTRAINT `xlsws_sro_item_ibfk_3` FOREIGN KEY (`sro_id`) REFERENCES `xlsws_sro` (`id`),
CONSTRAINT `xlsws_sro_item_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELETE a.* FROM `{newdbname}`.`xlsws_cart_item` as a left join `{newdbname}`.`xlsws_cart` as b on a.cart_id=b.id where b.id is null;
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD CONSTRAINT `xlsws_cart_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `xlsws_customer` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart_item` ADD CONSTRAINT `xlsws_cart_item_ibfk_1` FOREIGN KEY (`cart_id`) REFERENCES `xlsws_cart` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart_item` ADD CONSTRAINT `xlsws_cart_item_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_product_category_assn` ADD CONSTRAINT `xlsws_product_category_assn_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_product_category_assn` ADD CONSTRAINT `xlsws_product_category_assn_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `xlsws_category` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_category` ADD CONSTRAINT `xlsws_product_category_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `xlsws_category` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
ALTER TABLE `{newdbname}`.`xlsws_product` ADD CONSTRAINT `xlsws_product_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_product` ADD CONSTRAINT `xlsws_product_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `xlsws_images` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_state` ADD CONSTRAINT `fk_country` FOREIGN KEY (`country_id`) REFERENCES `xlsws_country` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_sro_repair` ADD CONSTRAINT `xlsws_sro_repair_ibfk_1` FOREIGN KEY (`sro_id`) REFERENCES `xlsws_sro` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_sro` ADD `customer_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `ls_id`;
ALTER TABLE `{newdbname}`.`xlsws_sro` ADD CONSTRAINT `xlsws_sro_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `xlsws_customer` (`id`);
RENAME TABLE `{newdbname}`.`xlsws_gift_registry_items` TO `{newdbname}`.`xlsws_wishlist_item`;
RENAME TABLE `{newdbname}`.`xlsws_gift_registry` TO `{newdbname}`.`xlsws_wishlist`;
ALTER TABLE `{newdbname}`.`xlsws_wishlist` CHANGE `customer_id` `customer_id` BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE `{newdbname}`.`xlsws_wishlist` ADD `visibility` INT NULL DEFAULT NULL AFTER `registry_description`;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` CHANGE `registry_id` `registry_id` BIGINT(20) UNSIGNED NOT NULL;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` CHANGE `purchase_status` `cart_item_id` BIGINT(20) UNSIGNED NULL;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` CHANGE `purchased_by` `purchased_by` BIGINT(20) UNSIGNED NULL;
UPDATE `{newdbname}`.`xlsws_wishlist_item` SET cart_item_id=NULL WHERE cart_item_id=0;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD `qty_received` INT NULL DEFAULT NULL AFTER `qty`;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD `qty_received_manual` INT NULL DEFAULT NULL AFTER `qty_received`;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD `priority` INT NULL DEFAULT 2 AFTER `qty_received`;
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD `comment` TEXT NULL DEFAULT NULL AFTER `priority`;
update `{newdbname}`.`xlsws_wishlist_item` set priority=1;
ALTER TABLE `{newdbname}`.`xlsws_wishlist` ADD CONSTRAINT `xlsws_wishlist_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `xlsws_customer` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD CONSTRAINT `xlsws_wishlist_item_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD CONSTRAINT `xlsws_wishlist_item_ibfk_3` FOREIGN KEY (`cart_item_id`) REFERENCES `xlsws_cart_item` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_wishlist_item` ADD CONSTRAINT `xlsws_wishlist_item_ibfk_4` FOREIGN KEY (`purchased_by`) REFERENCES `xlsws_customer` (`id`);
CREATE TABLE `{newdbname}`.`xlsws_category_integration` (
`category_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`module` varchar(30) DEFAULT NULL,
`foreign_id` int(11) unsigned DEFAULT NULL,
`extra` varchar(255) DEFAULT NULL,
KEY `module` (`module`),
KEY `foreign_id` (`foreign_id`),
KEY `category_id` (`category_id`),
CONSTRAINT `xlsws_category_integration_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `xlsws_category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_category_amazon` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name0` varchar(255) DEFAULT NULL,
`name1` varchar(255) DEFAULT NULL,
`name2` varchar(255) DEFAULT NULL,
`name3` varchar(255) DEFAULT NULL,
`name4` varchar(255) DEFAULT NULL,
`name5` varchar(255) DEFAULT NULL,
`name6` varchar(255) DEFAULT NULL,
`name7` varchar(255) DEFAULT NULL,
`name8` varchar(255) DEFAULT NULL,
`name9` varchar(255) DEFAULT NULL,
`item_type` varchar(255) DEFAULT NULL,
`product_type` varchar(255) DEFAULT NULL,
`refinements` text,
PRIMARY KEY (`id`),
KEY `name` (`name0`),
KEY `name1` (`name1`),
KEY `name2` (`name2`),
KEY `name3` (`name3`),
KEY `name4` (`name4`),
KEY `name5` (`name5`),
KEY `name6` (`name6`),
KEY `name7` (`name7`),
KEY `name8` (`name8`),
KEY `name9` (`name9`),
KEY `item_type` (`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_category_google` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name0` varchar(255) DEFAULT NULL,
`name1` varchar(255) DEFAULT NULL,
`name2` varchar(255) DEFAULT NULL,
`name3` varchar(255) DEFAULT NULL,
`name4` varchar(255) DEFAULT NULL,
`name5` varchar(255) DEFAULT NULL,
`name6` varchar(255) DEFAULT NULL,
`name7` varchar(255) DEFAULT NULL,
`name8` varchar(255) DEFAULT NULL,
`name9` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name0`),
KEY `name1` (`name1`),
KEY `name2` (`name2`),
KEY `name3` (`name3`),
KEY `name4` (`name4`),
KEY `name5` (`name5`),
KEY `name6` (`name6`),
KEY `name7` (`name7`),
KEY `name8` (`name8`),
KEY `name9` (`name9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_customer_address` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` bigint(20) unsigned DEFAULT NULL,
`address_label` varchar(255) DEFAULT NULL,
`active` int(11) DEFAULT '1',
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`company` varchar(255) DEFAULT NULL,
`address1` varchar(255) DEFAULT NULL,
`address2` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state_id` int(11) unsigned DEFAULT NULL,
`postal` varchar(64) DEFAULT NULL,
`country_id` int(11) unsigned DEFAULT NULL,
`phone` varchar(64) DEFAULT NULL,
`residential` int(11) DEFAULT NULL,
`modified` TIMESTAMP,
`created` TIMESTAMP,
PRIMARY KEY (`id`),
KEY `fk_customer_id` (`customer_id`),
KEY `state_id` (`state_id`),
KEY `country_id` (`country_id`),
CONSTRAINT `xlsws_customer_address_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `xlsws_country` (`id`),
CONSTRAINT `fk_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `xlsws_customer` (`id`),
CONSTRAINT `xlsws_customer_address_ibfk_1` FOREIGN KEY (`state_id`) REFERENCES `xlsws_state` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `{newdbname}`.`xlsws_stringsource` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category` varchar(32) DEFAULT NULL,
`message` varchar(1024) DEFAULT '',
PRIMARY KEY (`id`),
KEY `category` (`category`),
KEY `message` (`message`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `{newdbname}`.`xlsws_stringtranslate` (
`id` int(11) NOT NULL,
`language` varchar(16) NOT NULL default '',
`translation` varchar(1024),
PRIMARY KEY (`id`,`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `{newdbname}`.`xlsws_stringtranslate`
ADD CONSTRAINT `xlsws_stringsource_ibfk_1` FOREIGN KEY (`id`) REFERENCES `xlsws_stringsource` (`id`) ON DELETE CASCADE;
INSERT INTO `{newdbname}`.`xlsws_stringsource` (`id`, `category`, `message`)
VALUES
(21, 'cart', 'Qty'),
(22, 'cart', 'SubTotal'),
(23, 'cart', 'Checkout'),
(24, 'cart', 'Edit Cart'),
(97, 'cart', 'Description'),
(98, 'cart', 'Price'),
(99, 'cart', 'Total'),
(100, 'cart', 'Shipping'),
(130, 'cart', 'Thank you for your order!'),
(131, 'cart', 'Order ID'),
(132, 'cart', 'Date'),
(133, 'cart', 'Status'),
(134, 'cart', 'Payment'),
(135, 'cart', 'Authorization'),
(136, 'cart', 'Notes'),
(137, 'cart', 'Promo Code {code} Applied'),
(202, 'cart', 'Clear Cart'),
(203, 'cart', 'Are you sure you want to erase your cart items?'),
(204, 'cart', 'Email Cart'),
(205, 'cart', 'Continue Shopping'),
(206, 'cart', 'Update Cart'),
(20, 'checkout', 'Shopping Cart'),
(73, 'checkout', 'Choose your shipping address'),
(74, 'checkout', 'Or enter a new address'),
(75, 'checkout', 'Shipping Address'),
(88, 'checkout', 'Choose your billing address'),
(89, 'checkout', 'Billing Address'),
(90, 'checkout', 'Promo Code'),
(91, 'checkout', 'Enter a Promotional Code here to receive a discount.'),
(92, 'checkout', 'Apply Promo Code'),
(93, 'checkout', 'Shipping'),
(96, 'checkout', 'Click to Calculate Shipping'),
(101, 'checkout', 'Payment'),
(109, 'checkout', 'Submit your order'),
(111, 'checkout', 'I hereby agree to the Terms and Conditions of shopping with {storename}'),
(128, 'checkout', 'Customer Contact'),
(188, 'checkout', 'Billing'),
(77, 'CheckoutForm', 'Label for this address (i.e. Home, Work)'),
(78, 'CheckoutForm', 'First Name'),
(79, 'CheckoutForm', 'Last Name'),
(80, 'CheckoutForm', 'Address'),
(81, 'CheckoutForm', 'Address 2 (optional)'),
(82, 'CheckoutForm', 'City'),
(83, 'CheckoutForm', 'Country'),
(84, 'CheckoutForm', 'State/Province'),
(85, 'CheckoutForm', 'Zip/Postal'),
(86, 'CheckoutForm', 'This is a residential address'),
(87, 'CheckoutForm', 'My shipping address is also my billing address'),
(94, 'CheckoutForm', 'Shipping Method'),
(95, 'CheckoutForm', 'Delivery Speed'),
(102, 'CheckoutForm', 'Payment Provider'),
(103, 'CheckoutForm', 'Card Type'),
(104, 'CheckoutForm', 'Card Number'),
(105, 'CheckoutForm', 'CVV'),
(106, 'CheckoutForm', 'Expiry Month'),
(107, 'CheckoutForm', 'Expiry Year'),
(108, 'CheckoutForm', 'Cardholder Name'),
(110, 'CheckoutForm', 'Comments'),
(112, 'CheckoutForm', 'Accept Terms'),
(198, 'CheckoutForm', 'Phone'),
(186, 'email', 'Dear'),
(187, 'email', 'Thank you for your order with'),
(194, 'email', 'This email is a confirmation for the order. To view details or track your order, click on the visit link:'),
(195, 'email', 'Please refer to your order ID '),
(196, 'email', ' if you want to contact us about this order.'),
(197, 'email', 'Thank you, {storename}'),
(199, 'email', '{storename} Order Notification {orderid}'),
(7, 'global', '{description} : {storename}'),
(8, 'global', '{longdescription}'),
(9, 'global', 'Hover over image to zoom'),
(14, 'global', 'The following related products will be added to your cart automatically with this purchase:'),
(16, 'global', 'Other items you may be interested in:'),
(19, 'global', 'Submit'),
(25, 'global', 'Order Lookup'),
(26, 'global', 'Wish Lists'),
(27, 'global', 'View all my wish lists'),
(28, 'global', 'Create a Wish List'),
(29, 'global', 'Search for a wish list'),
(30, 'global', 'Logout'),
(31, 'global', 'Products'),
(55, 'global', 'SEARCH'),
(56, 'global', 'About Us'),
(57, 'global', 'Terms and Conditions'),
(58, 'global', 'Privacy Policy'),
(59, 'global', 'Sitemap'),
(60, 'global', 'Copyright'),
(61, 'global', 'All Rights Reserved'),
(62, 'global', '{storename} : {storetagline}'),
(63, 'global', 'First'),
(64, 'global', 'Last'),
(65, 'global', 'Previous'),
(66, 'global', 'Next'),
(67, 'global', 'Size'),
(68, 'global', 'Select {label}...'),
(69, 'global', 'Color'),
(70, 'global', 'Edit Cart'),
(71, 'global', 'Checkout'),
(72, 'global', 'Fields with {*} are required.'),
(76, 'global', 'You must accept Terms and Conditions'),
(113, 'global', '{label} ({price})'),
(114, 'global', 'Available during normal business hours'),
(115, 'global', 'Welcome'),
(116, 'global', 'Edit Profile'),
(117, 'global', 'My Addresses'),
(118, 'global', 'Add new address'),
(119, 'global', 'Default Billing Address'),
(120, 'global', 'Default Shipping Address'),
(121, 'global', 'My Orders'),
(122, 'global', 'Awaiting Processing'),
(123, 'global', 'My Wish Lists'),
(124, 'global', 'Click here to create a wish list.'),
(125, 'global', 'You have not created any wish list yet.'),
(126, 'global', 'Create a new address book entry'),
(127, 'global', 'Update your account'),
(129, 'global', 'Enter a new password here to change your password'),
(138, 'global', 'New Wish List'),
(140, 'global', 'Name'),
(141, 'global', 'Contains'),
(142, 'global', 'Description'),
(143, 'global', 'Edit'),
(144, 'global', 'Create a new Wish List'),
(159, 'global', '{items} item|{items} items'),
(161, 'global', 'Wish List'),
(162, 'global', 'View All Lists'),
(163, 'global', 'Settings'),
(164, 'global', 'Share'),
(165, 'global', 'Qty'),
(166, 'global', 'Status'),
(176, 'global', 'Update'),
(177, 'global', 'DELETE THIS ITEM'),
(180, 'global', 'Send'),
(181, 'global', 'Wish List Search'),
(183, 'global', 'Search for a wish list by email address'),
(184, 'global', 'Promo Code'),
(185, 'global', 'Promo Code applied at {amount}.'),
(189, 'global', 'Item'),
(190, 'global', 'Price'),
(191, 'global', 'SubTotal'),
(192, 'global', 'Total'),
(193, 'global', 'Payment Data'),
(200, 'global', '{name} : {storename}'),
(10, 'product', 'Regular Price'),
(11, 'product', '{qty} Available'),
(12, 'product', 'Add to Wish List'),
(13, 'product', 'Add to Cart'),
(15, 'product', 'Product Description'),
(32, 'tabs', 'Products'),
(51, 'tabs', 'New Products'),
(52, 'tabs', 'Top Products'),
(53, 'tabs', 'Promotions'),
(54, 'tabs', 'Contact Us'),
(17, 'wishlist', 'Add to Wish List'),
(18, 'wishlist', 'Add to what list'),
(139, 'wishlist', 'Click on the wish list name to view list contents, or click on edit to make changes to settings.'),
(145, 'wishlist', 'Name your Wish List'),
(146, 'wishlist', 'Description (Optional)'),
(147, 'wishlist', 'Event Date (Optional)'),
(148, 'wishlist', 'Visibility'),
(149, 'wishlist', 'Public, searchable by my email address'),
(150, 'wishlist', 'Personal, shared only by a special URL'),
(151, 'wishlist', 'Private, only viewable with my login'),
(152, 'wishlist', 'None'),
(153, 'wishlist', 'Ship Option'),
(154, 'wishlist', 'Leave the item in the Wish List, marked as Purchased'),
(155, 'wishlist', 'Delete the item automatically from Wish List'),
(156, 'wishlist', 'After purchase'),
(157, 'wishlist', 'My Wish List'),
(158, 'wishlist', 'Item has been added to your Wish List.'),
(160, 'wishlist', 'Please check out my Wish List at {url}'),
(167, 'wishlist', 'You can share this wish list with anyone using the URL: {url}'),
(168, 'wishlist', 'Edit Wish List Item'),
(169, 'wishlist', 'Qty Desired'),
(170, 'wishlist', 'Qty Received'),
(171, 'wishlist', 'Priority'),
(172, 'wishlist', 'Item Comment (max 500 characters)'),
(173, 'wishlist', 'Low Priority'),
(174, 'wishlist', 'Normal Priority'),
(175, 'wishlist', 'High Priority'),
(178, 'wishlist', 'Share my Wish List'),
(179, 'wishlist', 'Share via email'),
(182, 'wishlist', 'Click on the wish list name to view.'),
(201, 'wishlist', 'Please check out my shopping cart at {url}'),
(207, 'wishlist', 'Share my Cart'),
(208, 'wishlist', 'No publicly searchable wish lists for this email address.');
CREATE TABLE `{newdbname}`.`xlsws_tags` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tag` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_product_tags` (
`product_id` bigint(20) unsigned DEFAULT NULL,
`tag_id` bigint(20) unsigned DEFAULT NULL,
KEY `product_id` (`product_id`),
KEY `tag` (`tag_id`),
CONSTRAINT `xlsws_product_tags_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`),
CONSTRAINT `xlsws_product_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `xlsws_tags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_cart_shipping` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`shipping_method` varchar(255) DEFAULT NULL,
`shipping_module` varchar(64) DEFAULT NULL,
`shipping_data` varchar(255) DEFAULT NULL,
`shipping_cost` double DEFAULT NULL,
`shipping_sell` double DEFAULT NULL,
`tracking_number` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_cart_payment` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`payment_method` varchar(255) DEFAULT NULL,
`payment_module` varchar(64) DEFAULT NULL,
`payment_data` varchar(255) DEFAULT NULL,
`payment_amount` double DEFAULT NULL,
`datetime_posted` datetime default NULL,
`promocode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_document` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`cart_id` bigint(20) unsigned DEFAULT NULL,
`order_str` varchar(64) DEFAULT NULL,
`invoice_str` varchar(64) DEFAULT NULL,
`customer_id` bigint(20) unsigned DEFAULT NULL,
`shipaddress_id` bigint(20) unsigned DEFAULT NULL,
`billaddress_id` bigint(20) unsigned DEFAULT NULL,
`shipping_id` bigint(20) unsigned DEFAULT NULL,
`payment_id` bigint(20) unsigned DEFAULT NULL,
`discount` double DEFAULT NULL,
`po` varchar(64) DEFAULT NULL,
`order_type` mediumint(9) DEFAULT NULL,
`status` varchar(32) DEFAULT NULL,
`cost_total` double DEFAULT NULL,
`currency` varchar(3) DEFAULT NULL,
`currency_rate` double DEFAULT NULL,
`datetime_cre` datetime DEFAULT NULL,
`datetime_due` datetime DEFAULT NULL,
`sell_total` double DEFAULT NULL,
`printed_notes` text,
`fk_tax_code_id` int(11),
`tax_inclusive` tinyint(1) DEFAULT NULL,
`subtotal` double DEFAULT NULL,
`tax1` double DEFAULT '0',
`tax2` double DEFAULT '0',
`tax3` double DEFAULT '0',
`tax4` double DEFAULT '0',
`tax5` double DEFAULT '0',
`total` double DEFAULT NULL,
`item_count` int(11) DEFAULT '0',
`lightspeed_user` varchar(32) DEFAULT NULL,
`gift_registry` bigint(20) DEFAULT NULL,
`send_to` varchar(255) DEFAULT NULL,
`submitted` datetime DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`linkid` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
KEY `fk_ship` (`shipaddress_id`),
KEY `fk_bill` (`billaddress_id`),
KEY `fk_shiprecord` (`shipping_id`),
KEY `fk_payrecord` (`payment_id`),
KEY `cart_id` (`cart_id`),
CONSTRAINT `xlsws_document_ibfk_8` FOREIGN KEY (`cart_id`) REFERENCES `xlsws_cart` (`id`),
CONSTRAINT `xlsws_document_ibfk_1` FOREIGN KEY (`billaddress_id`) REFERENCES `xlsws_customer_address` (`id`),
CONSTRAINT `xlsws_document_ibfk_3` FOREIGN KEY (`shipaddress_id`) REFERENCES `xlsws_customer_address` (`id`),
CONSTRAINT `xlsws_document_ibfk_5` FOREIGN KEY (`customer_id`) REFERENCES `xlsws_customer` (`id`),
CONSTRAINT `xlsws_document_ibfk_6` FOREIGN KEY (`shipping_id`) REFERENCES `xlsws_document_shipping` (`id`),
CONSTRAINT `xlsws_document_ibfk_7` FOREIGN KEY (`payment_id`) REFERENCES `xlsws_document_payment` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_document_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`document_id` bigint(20) unsigned NOT NULL,
`cart_type` int(11) DEFAULT '1',
`product_id` bigint(20) unsigned NOT NULL,
`code` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`discount` varchar(16) DEFAULT NULL,
`qty` float NOT NULL,
`sell` double NOT NULL,
`sell_base` double NOT NULL,
`sell_discount` double NOT NULL,
`sell_total` double NOT NULL,
`serial_numbers` varchar(255) DEFAULT NULL,
`gift_registry_item` bigint(20) DEFAULT NULL,
`datetime_added` datetime NOT NULL,
`datetime_mod` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `code` (`code`),
KEY `product_id` (`product_id`),
KEY `gift_registry_item` (`gift_registry_item`),
KEY `document_id` (`document_id`),
CONSTRAINT `xlsws_document_item_ibfk_3` FOREIGN KEY (`document_id`) REFERENCES `xlsws_document` (`id`),
CONSTRAINT `xlsws_document_item_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_document_payment` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`payment_method` varchar(255) DEFAULT NULL,
`payment_module` varchar(64) DEFAULT NULL,
`payment_data` varchar(255) DEFAULT NULL,
`payment_amount` double DEFAULT NULL,
`datetime_posted` datetime DEFAULT NULL,
`promocode` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_document_shipping` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`shipping_method` varchar(255) DEFAULT NULL,
`shipping_module` varchar(64) DEFAULT NULL,
`shipping_data` varchar(255) DEFAULT NULL,
`shipping_cost` double DEFAULT NULL,
`shipping_sell` double DEFAULT NULL,
`tracking_number` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
UPDATE `{newdbname}`.`xlsws_category` SET parent=NULL WHERE parent=0;
UPDATE `{newdbname}`.`xlsws_product` SET parent=NULL WHERE parent=0;
DELETE FROM `{newdbname}`.`xlsws_configuration` WHERE key_name='IMAGE_STORE';
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `id_customer` `lightspeed_id` BIGINT(20) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_customer` MODIFY COLUMN `lightspeed_id` BIGINT(20) DEFAULT NULL AFTER `id`;
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD `email_verified` INT NULL DEFAULT NULL AFTER `email`;
ALTER TABLE `{newdbname}`.`xlsws_images` ADD FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart` MODIFY COLUMN `first_name` VARCHAR(64) DEFAULT NULL AFTER `id_str`;
ALTER TABLE `{newdbname}`.`xlsws_cart` MODIFY COLUMN `last_name` VARCHAR(64) DEFAULT NULL AFTER `first_name`;
ALTER TABLE `{newdbname}`.`xlsws_customer` MODIFY COLUMN `firstname` VARCHAR(64) DEFAULT NULL AFTER `id`;
ALTER TABLE `{newdbname}`.`xlsws_customer` MODIFY COLUMN `lastname` VARCHAR(64) DEFAULT NULL AFTER `firstname`;
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `firstname` `first_name` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `lastname` `last_name` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_customer` MODIFY COLUMN `record_type` INT(11) DEFAULT NULL AFTER `id`;
ALTER TABLE `{newdbname}`.`xlsws_cart` MODIFY COLUMN `customer_id` BIGINT(20) UNSIGNED DEFAULT NULL AFTER `id_str`;
UPDATE `{newdbname}`.`xlsws_customer` SET record_type=1 where first_name is not null;
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD `shipaddress_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `customer_id`;
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD `billaddress_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `shipaddress_id`;
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD `shipping_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `billaddress_id`;
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD `payment_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `shipping_id`;
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD CONSTRAINT `fk_ship` FOREIGN KEY (`shipaddress_id`) REFERENCES `xlsws_customer_address` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD CONSTRAINT `fk_bill` FOREIGN KEY (`billaddress_id`) REFERENCES `xlsws_customer_address` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD CONSTRAINT `fk_shiprecord` FOREIGN KEY (`shipping_id`) REFERENCES `xlsws_cart_shipping` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart` ADD CONSTRAINT `fk_payrecord` FOREIGN KEY (`payment_id`) REFERENCES `xlsws_cart_payment` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_cart` DROP `discount`;
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD `default_billing_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `company`;
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD `default_shipping_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `default_billing_id`;
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD FOREIGN KEY (`default_billing_id`) REFERENCES `xlsws_customer_address` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD FOREIGN KEY (`default_shipping_id`) REFERENCES `xlsws_customer_address` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_customer` ADD `last_login` TIMESTAMP NULL AFTER `modified`;
use `{newdbname}`;
CREATE TABLE `{newdbname}`.`xlsws_email_queue` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`sent_attempts` int(11) DEFAULT NULL,
`customer_id` bigint(20) unsigned DEFAULT NULL,
`cart_id` bigint(20) unsigned DEFAULT NULL,
`to` text,
`subject` varchar(255) DEFAULT NULL,
`plainbody` text,
`htmlbody` text,
`datetime_cre` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `xlsws_email_queue_ibfk_1` (`customer_id`),
KEY `cart_id` (`cart_id`),
CONSTRAINT `xlsws_email_queue_ibfk_2` FOREIGN KEY (`cart_id`) REFERENCES `xlsws_cart` (`id`),
CONSTRAINT `xlsws_email_queue_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `xlsws_customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_product_text` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`product_id` bigint(20) unsigned DEFAULT NULL,
`lang` varchar(6) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`description_short` mediumtext,
`description_long` mediumtext,
PRIMARY KEY (`id`),
KEY `lang` (`lang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_classes` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`class_name` varchar(255) DEFAULT NULL,
`child_count` int(11) NOT NULL DEFAULT '0',
`request_url` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `class_name` (`class_name`),
KEY `request_url` (`request_url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `{newdbname}`.`xlsws_pricing_levels` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`label` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('1', 'Regular Prices');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('2', 'Pricing Level A');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('3', 'Pricing Level B');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('4', 'Pricing Level C');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('5', 'Pricing Level D');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('6', 'Pricing Level E');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('7', 'Pricing Level F');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('8', 'Pricing Level G');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('9', 'Pricing Level H');
INSERT INTO `xlsws_pricing_levels` (`id`, `label`) VALUES ('10', 'Pricing Level J');
ALTER TABLE `xlsws_product_qty_pricing` CHANGE `pricing_level` `pricing_level` INT(11) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE `xlsws_product_qty_pricing` ADD FOREIGN KEY (`pricing_level`) REFERENCES `xlsws_pricing_levels` (`id`);
CREATE TABLE `xlsws_task_queue` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`module` varchar(255) DEFAULT NULL,
`controller` varchar(255) DEFAULT NULL,
`action` varchar(255) DEFAULT NULL,
`data_id` varchar(255) DEFAULT NULL,
`product_id` bigint(20) unsigned DEFAULT NULL,
`created` datetime DEFAULT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `xlsws_task_queue_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
update `{newdbname}`.xlsws_state as a set country_id=(select id from `{newdbname}`.xlsws_country as b where country_code=code);
ALTER TABLE `{newdbname}`.`xlsws_state` ADD `active` INT(11) UNSIGNED NULL DEFAULT NULL AFTER `avail`;
UPDATE `{newdbname}`.xlsws_state set active=1 where avail='Y';
UPDATE `{newdbname}`.xlsws_state set active=0 where avail='N';
ALTER TABLE `{newdbname}`.`xlsws_state` DROP `avail`;
ALTER TABLE `{newdbname}`.`xlsws_country` ADD `active` INT(11) UNSIGNED NULL DEFAULT NULL AFTER `avail`;
UPDATE `{newdbname}`.xlsws_country set active=1 where avail='Y';
UPDATE `{newdbname}`.xlsws_country set active=0 where avail='N';
ALTER TABLE `{newdbname}`.`xlsws_country` DROP `avail`;
ALTER TABLE `{newdbname}`.`xlsws_customer` CHANGE `mainephonetype` `mainphonetype` VARCHAR(8) NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_product` ADD `sell_web_tax_inclusive` FLOAT NULL DEFAULT NULL AFTER `sell_web`;
UPDATE `{newdbname}`.`xlsws_product` set `sell_web_tax_inclusive`=`sell_web`;
UPDATE `{newdbname}`.`xlsws_product` set `sell_web_tax_inclusive`=`sell_tax_inclusive` where `sell_tax_inclusive`>0;
ALTER TABLE `{newdbname}`.`xlsws_cart_item` ADD `tax_in` TINYINT(2) UNSIGNED NULL DEFAULT NULL AFTER `serial_numbers`;
ALTER TABLE `{newdbname}`.`xlsws_document_item` ADD `tax_in` TINYINT(2) UNSIGNED NULL DEFAULT NULL AFTER `serial_numbers`;
ALTER TABLE `{newdbname}`.`xlsws_family` ADD `child_count` INT(11) NOT NULL DEFAULT '0' AFTER `family`;
ALTER TABLE `{newdbname}`.`xlsws_product` ADD `family_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `family`;
ALTER TABLE `{newdbname}`.`xlsws_product` ADD FOREIGN KEY (`family_id`) REFERENCES `xlsws_family` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_product` ADD `class_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `class_name`;
ALTER TABLE `{newdbname}`.`xlsws_product` ADD FOREIGN KEY (`class_id`) REFERENCES `xlsws_classes` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_configuration` ADD `param` INT NOT NULL DEFAULT '1' AFTER `template_specific`;
ALTER TABLE `{newdbname}`.`xlsws_cart_item` CHANGE `gift_registry_item` `wishlist_item` BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE `{newdbname}`.`xlsws_cart_item` DROP INDEX `gift_registry_item`;
update `{newdbname}`.`xlsws_cart_item` set wishlist_item=null where wishlist_item=0;
ALTER TABLE `{newdbname}`.`xlsws_cart_item` ADD FOREIGN KEY (`wishlist_item`) REFERENCES `{newdbname}`.`xlsws_wishlist_item` (`id`);
ALTER TABLE `{newdbname}`.`xlsws_wishlist` ADD `after_purchase` INT NOT NULL AFTER `ship_option`;
update `{newdbname}`.`xlsws_wishlist` set after_purchase=1;
update xlsws_cart set fk_tax_code_id=null where fk_tax_code_id=-1;
UPDATE `xlsws_configuration` SET `param` = '0' WHERE `key_name` = 'NEXT_ORDER_ID';
UPDATE `xlsws_configuration` SET `key_name` = 'LANG_CODE' WHERE `key_name` = 'LANGUAGE_DEFAULT';
UPDATE `xlsws_configuration` SET `key_value` = '6' WHERE `key_name` = 'RESET_GIFT_REGISTRY_PURCHASE_STATUS' and `key_value`=0;
UPDATE `xlsws_configuration` SET `configuration_type_id` = '27',options=NULL,title='Enter relative URL',helper_text='This path should start with /images' WHERE `key_name` = 'HEADER_IMAGE';
ALTER TABLE `xlsws_cart` CHANGE `fk_tax_code_id` `tax_code_id` INT(11) UNSIGNED NULL ;
ALTER TABLE `xlsws_product` CHANGE `fk_tax_status_id` `tax_status_id` INT(11) UNSIGNED NULL;
ALTER TABLE `xlsws_tax` ADD INDEX (`lsid`);
ALTER TABLE `xlsws_tax_code` ADD INDEX (`lsid`);
ALTER TABLE `xlsws_tax_status` ADD INDEX (`lsid`);
ALTER TABLE `xlsws_document` ADD INDEX (`order_type`);
ALTER TABLE `xlsws_cart` ADD INDEX (`cart_type`);
ALTER TABLE `xlsws_product` ADD FOREIGN KEY (`tax_status_id`) REFERENCES `xlsws_tax_status` (`lsid`);
ALTER TABLE `xlsws_cart` ADD FOREIGN KEY (`tax_code_id`) REFERENCES `xlsws_tax_code` (`lsid`);
ALTER TABLE `xlsws_product_qty_pricing` ADD FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `xlsws_customer` ADD FOREIGN KEY (`pricing_level`) REFERENCES `xlsws_pricing_levels` (`id`);
update xlsws_product_qty_pricing set pricing_level=pricing_level+1;
ALTER TABLE `xlsws_product_related` ADD FOREIGN KEY (`product_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `xlsws_product_related` ADD FOREIGN KEY (`related_id`) REFERENCES `xlsws_product` (`id`);
ALTER TABLE `xlsws_cart` ADD `document_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL AFTER `payment_id`;
ALTER TABLE `xlsws_cart` ADD FOREIGN KEY (`document_id`) REFERENCES `xlsws_document` (`id`);
ALTER TABLE `xlsws_category` CHANGE `meta_keywords` `google_extra` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `xlsws_modules` ADD `version` INT NULL DEFAULT NULL AFTER `category`;
INSERT IGNORE INTO `xlsws_configuration` (`title`, `key_name`, `key_value`, `helper_text`, `configuration_type_id`, `sort_order`, `modified`, `created`, `options`, `template_specific`, `param`)
VALUES
('Share Cart Email Subject Line', 'EMAIL_SUBJECT_CART', '{storename} Cart for {customername}', 'Configure Email Subject line with variables for Customer Email', 24, 10, '2012-08-28 14:07:09', '2012-08-28 14:07:09', NULL, 0, 1);
INSERT IGNORE INTO `xlsws_configuration` (`title`, `key_name`, `key_value`, `helper_text`, `configuration_type_id`, `sort_order`, `modified`, `created`, `options`, `template_specific`, `param`)
VALUES ('Wishlist Email Subject Line', 'EMAIL_SUBJECT_WISHLIST', '{storename} Wishlist for {customername}', 'Configure Email Subject line with variables for Customer Email', 24, 10, '2012-08-28 14:07:09', '2012-08-28 14:07:09', NULL, 0, 1);
ALTER TABLE `xlsws_configuration` CHANGE `helper_text` `helper_text` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '';
ALTER TABLE `xlsws_cart` CHANGE `linkid` `linkid` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `xlsws_document` CHANGE `linkid` `linkid` VARCHAR(64) NULL DEFAULT NULL;
ALTER TABLE `xlsws_sro` CHANGE `cart_id` `linkid` VARCHAR(64) NULL DEFAULT NULL;
update xlsws_shipping_tiers set `class_name`='tieredshipping';
ALTER TABLE `xlsws_promo_code` CHANGE `qty_remaining` `qty_remaining` INT(11) NULL DEFAULT NULL;
update xlsws_promo_code set qty_remaining=NULL where qty_remaining=-1;
ALTER TABLE `xlsws_promo_code` CHANGE `threshold` `threshold` DOUBLE NULL DEFAULT NULL;
update `xlsws_promo_code` set threshold=null where threshold=0;
UPDATE `xlsws_configuration` SET `helper_text` = '' WHERE `key_name` = 'EMAIL_SEND_CUSTOMER';
UPDATE `xlsws_configuration` SET `helper_text` = 'Email store on every order' WHERE `key_name` = 'EMAIL_SEND_STORE';
UPDATE `xlsws_configuration` SET `helper_text` = '' WHERE `key_name` = 'STORE_NAME';
INSERT IGNORE INTO `xlsws_configuration` (`id`, `title`, `key_name`, `key_value`, `helper_text`, `configuration_type_id`, `sort_order`, `modified`, `created`, `options`, `template_specific`, `param`) VALUES (NULL, 'System Logging', 'DEBUG_LOGGING', 'error', '', '1', '21', '2013-02-07 11:13:36', '2012-03-08 09:57:38', 'LOGGING', '0', '1');
UPDATE `xlsws_configuration` SET `configuration_type_id` = '25', `sort_order` = '3' WHERE `key_name` = &