vtiger

mikecrowe · Joined: 04 Jan 2005 Posts: 499

Hi Richie,

You've done a great job on this data migration! Here's an analysis and list of feedback of what's left to do (in my case).

Note: I don't use some features (FAQ, etc), so that really isn't tested.

1) I'm running 2 copies of vtiger, normal (htdocs/vtigerCRM) which is still 4.0.1, and beta (htdocs/vtiger4.2). Had to rename directories to get migration to work. This isn't too serious.
2) I ran datamigration.sh within Cygwin in WinXP. Worked great after changed 1 line: "local flag=0" to "flag=0". Just FYI...

Here are the tables that are in 4.0.1 which are not in a new copy of 4.2:
Quote: Table: crmentity_seq
Table: customfield_sequence_seq
Table: accounts_potential
Table: sefaqrel

These tables are in a new 4.2 installation, but not in the converted database. They need to be initialized in DataMigration.php:
Quote: Table: taxclass
Table: poshipads
Table: quotesproductrel
Table: quotes
Table: pricebook
Table: soproductrel
Table: invoicebillads
Table: sobillads
Table: rss
Table: def_org_field
Table: cvstdfilter
Table: reportmodules
Table: mail_accounts
Table: relcriteria
Table: reportsummary
Table: report
Table: customview
Table: invoiceshipads
Table: faqstatus
Table: invoiceproductrel
Table: inventorynotification_seq
Table: recurringevents
Table: invoice
Table: quotesshipads
Table: quotestage
Table: selectquery_seq
Table: reportdatefilter
Table: relatedlists_seq
Table: cvcolumnlist
Table: customview_seq
Table: pobillads
Table: reportsortcol
Table: glacct
Table: carrier
Table: faqcomments
Table: relatedlists
Table: poproductrel
Table: soshipads
Table: selectcolumn
Table: customerdetails
Table: ticketcomments
Table: usageunit
Table: quotesbillads
Table: cvadvfilter
Table: recurringtype
Table: Unknown2
Table: portalinfo
Table: ticketseverities
Table: rsscategory
Table: pricebookproductrel
Table: currency_info
Table: organizationdetails
Table: selectquery
Table: activity_reminder
Table: salesorder
Table: vendor
Table: vendorcontactrel
Table: purchaseorder
Table: reportfolder
Table: inventorynotification

Here's a detailed list of table-by-table issues:
Code: #######################################################
OK, here's the table-by-table changes.
1) Format for the compare is:
"tablename.conv" is converted table using datamigration.sh
"tablename.new" is table created from install.php
2) If you see a field (as in account below) where it goes from a "-" to a "+", the definition of the field changed.
3) I've inserted my comments between these long pound signs
4) GVIM/Cream color-codes this wonderfully.
#######################################################

--- account.conv 2005-07-11 12:40:50.797928000 -0400
+++ account.new 2005-07-11 12:40:51.038273600 -0400
@@ -15,17 +15,17 @@
`ownership` varchar(50) default NULL,
`siccode` int(10) default '0',
`tickersymbol` varchar(30) default NULL,
`phone` varchar(30) default NULL,
`otherphone` varchar(30) default NULL,
- `email1` varchar(30) default NULL,
- `email2` varchar(30) default NULL,
+ `email1` varchar(100) default NULL,
+ `email2` varchar(100) default NULL,
`website` varchar(30) default NULL,
`fax` varchar(30) default NULL,
`employees` int(10) default '0',
PRIMARY KEY (`accountid`)
-) TYPE=MyISAM;
+) TYPE=InnoDB;

--
-- Dumping data for table `account`
--

--- activity.conv 2005-07-11 12:40:50.827971200 -0400
+++ activity.new 2005-07-11 12:40:51.048288000 -0400
@@ -15,15 +15,16 @@
`time_start` varchar(50) NOT NULL default '',
`sendnotification` varchar(50) NOT NULL default 'false',
`duration_hours` char(2) default NULL,
`duration_minutes` char(2) default NULL,
`status` varchar(100) default NULL,
+ `eventstatus` varchar(100) default NULL,
`priority` varchar(150) default NULL,
`location` varchar(150) default NULL,
PRIMARY KEY (`activityid`),
KEY `Activity_IDX0` (`activityid`,`subject`)
-) TYPE=MyISAM;
+) TYPE=InnoDB;

--
-- Dumping data for table `activity`
--

--- contactdetails.conv 2005-07-11 12:40:50.888057600 -0400
+++ contactdetails.new 2005-07-11 12:40:51.058302400 -0400
@@ -21,15 +21,15 @@
`usertype` varchar(50) default NULL,
`contacttype` varchar(50) default NULL,
`otheremail` varchar(100) default NULL,
`yahooid` varchar(100) default NULL,
`donotcall` char(3) default NULL,
- `emailoptout` char(3) default NULL,
+ `emailoptout` char(3) default '0',
`currency` varchar(20) default 'Dollars',
PRIMARY KEY (`contactid`),
KEY `ContactDetails_IDX1` (`accountid`)
-) TYPE=MyISAM;
+) TYPE=InnoDB;

--
-- Dumping data for table `contactdetails`
--

--- dealintimation.conv 2005-07-11 12:40:50.908086400 -0400
+++ dealintimation.new 2005-07-11 12:40:51.078331200 -0400
@@ -6,11 +6,11 @@

CREATE TABLE `dealintimation` (
`dealintimationid` int(19) NOT NULL default '0',
`dealname` varchar(100) NOT NULL default '',
`intimationamount` int(19) NOT NULL default '0',
- `dealprobability` decimal(3,2) NOT NULL default '0.00',
+ `dealprobability` decimal(4,3) NOT NULL default '0.000',
`dealintimationactive` int(1) default NULL,
`fromname` varchar(120) NOT NULL default '',
`fromemailid` varchar(100) NOT NULL default '',
`notifyemails` varchar(50) NOT NULL default '',
`notifybccemails` varchar(50) NOT NULL default '',
--- def_org_share.conv 2005-07-11 12:40:50.908086400 -0400
+++ def_org_share.new 2005-07-11 12:40:51.088345600 -0400

#######################################################
# Note in def_org_share: Need to add 12-17 entries to converted table
#######################################################
@@ -13,17 +13,23 @@

--
-- Dumping data for table `def_org_share`
--

-INSERT INTO `def_org_share` VALUES (1, 2, 3);
-INSERT INTO `def_org_share` VALUES (2, 4, 3);
-INSERT INTO `def_org_share` VALUES (3, 6, 3);
-INSERT INTO `def_org_share` VALUES (4, 7, 3);
-INSERT INTO `def_org_share` VALUES (5, 8, 3);
-INSERT INTO `def_org_share` VALUES (6, 9, 3);
-INSERT INTO `def_org_share` VALUES (7, 10, 3);
-INSERT INTO `def_org_share` VALUES (8, 13, 3);
-INSERT INTO `def_org_share` VALUES (9, 14, 3);
-INSERT INTO `def_org_share` VALUES (10, 15, 3);
-INSERT INTO `def_org_share` VALUES (11, 16, 3);
+INSERT INTO `def_org_share` VALUES (1, 2, 2);
+INSERT INTO `def_org_share` VALUES (2, 4, 2);
+INSERT INTO `def_org_share` VALUES (3, 6, 2);
+INSERT INTO `def_org_share` VALUES (4, 7, 2);
+INSERT INTO `def_org_share` VALUES (5, 8, 2);
+INSERT INTO `def_org_share` VALUES (6, 9, 2);
+INSERT INTO `def_org_share` VALUES (7, 10, 2);
+INSERT INTO `def_org_share` VALUES (8, 13, 2);
+INSERT INTO `def_org_share` VALUES (9, 14, 2);
+INSERT INTO `def_org_share` VALUES (10, 15, 2);
+INSERT INTO `def_org_share` VALUES (11, 16, 2);
+INSERT INTO `def_org_share` VALUES (12, 18, 2);
+INSERT INTO `def_org_share` VALUES (13, 19, 2);
+INSERT INTO `def_org_share` VALUES (14, 20, 2);
+INSERT INTO `def_org_share` VALUES (15, 21, 2);
+INSERT INTO `def_org_share` VALUES (16, 22, 2);
+INSERT INTO `def_org_share` VALUES (17, 23, 2);

--- def_org_share_seq.conv 2005-07-11 12:40:50.908086400 -0400
+++ def_org_share_seq.new 2005-07-11 12:40:51.088345600 -0400
@@ -10,7 +10,7 @@

--
-- Dumping data for table `def_org_share_seq`
--

-INSERT INTO `def_org_share_seq` VALUES (11);
+INSERT INTO `def_org_share_seq` VALUES (17);

--- emailtemplates.conv 2005-07-11 12:40:50.918100800 -0400
+++ emailtemplates.new 2005-07-11 12:40:51.098360000 -0400
@@ -9,12 +9,14 @@
`templatename` varchar(100) default NULL,
`subject` varchar(100) default NULL,
`description` longtext,
`body` longtext,
`deleted` int(1) NOT NULL default '0',
+ `templateid` int(19) NOT NULL auto_increment,
+ PRIMARY KEY (`templateid`),
KEY `idx_emailtemplates` (`foldername`,`templatename`,`subject`)
-) TYPE=MyISAM;
+) TYPE=MyISAM AUTO_INCREMENT=1 ;

--
-- Dumping data for table `emailtemplates`
--

#######################################################
# was comments changed to status? Looks like we lost the comments field
#######################################################
@@ -4,16 +4,18 @@
-- Table structure for table `faq`
--

CREATE TABLE `faq` (
`id` int(11) NOT NULL auto_increment,
+ `product_id` varchar(100) default NULL,
`question` longtext,
`answer` longtext,
`category` varchar(100) NOT NULL default '',
- `comments` longtext,
- PRIMARY KEY (`id`)
-) TYPE=MyISAM AUTO_INCREMENT=1 ;
+ `status` varchar(100) NOT NULL default '',
+ PRIMARY KEY (`id`),
+ KEY `faq_IDX0` (`id`)
+) TYPE=InnoDB AUTO_INCREMENT=1 ;

--
-- Dumping data for table `faq`
--

--- faqcategories.conv 2005-07-11 12:40:50.928115200 -0400
+++ faqcategories.new 2005-07-11 12:40:51.098360000 -0400
@@ -14,7 +14,7 @@

--
-- Dumping data for table `faqcategories`
--

-INSERT INTO `faqcategories` VALUES (2, NULL, 0, 1);
+INSERT INTO `faqcategories` VALUES (1, 'General', 0, 1);

--- loginhistory.conv 2005-07-11 12:40:50.948144000 -0400
+++ loginhistory.new 2005-07-11 12:40:51.138417600 -0400
@@ -6,15 +6,15 @@

CREATE TABLE `loginhistory` (
`login_id` int(11) NOT NULL auto_increment,
`user_name` varchar(25) NOT NULL default '',
`user_ip` varchar(25) NOT NULL default '',
- `login_time` timestamp(14) NOT NULL,
- `logout_time` timestamp(14) NOT NULL default '00000000000000',
+ `logout_time` timestamp(14) NOT NULL,
+ `login_time` timestamp(14) NOT NULL default '00000000000000',
`status` varchar(25) default NULL,
PRIMARY KEY (`login_id`)
-) TYPE=MyISAM AUTO_INCREMENT=217 ;
+) TYPE=MyISAM AUTO_INCREMENT=1 ;

--
-- Dumping data for table `loginhistory`
--

#######################################################
Did description become label? Do we need to convert?
#######################################################
--- notificationscheduler.conv 2005-07-11 12:40:50.958158400 -0400
+++ notificationscheduler.new 2005-07-11 12:40:51.138417600 -0400
@@ -5,17 +5,17 @@
--

CREATE TABLE `notificationscheduler` (
`schedulednotificationid` int(19) NOT NULL auto_increment,
`schedulednotificationname` varchar(200) default NULL,
- `description` longtext,
`active` int(1) default NULL,
`notificationsubject` varchar(200) default NULL,
`notificationbody` longtext,
+ `label` varchar(50) default NULL,
PRIMARY KEY (`schedulednotificationid`),
UNIQUE KEY `notificationscheduler_UK0` (`schedulednotificationname`)
-) TYPE=MyISAM AUTO_INCREMENT=7 ;
+) TYPE=InnoDB AUTO_INCREMENT=8 ;

--
-- Dumping data for table `notificationscheduler`
--

#######################################################
This changed pretty significantly. More attention needed?
#######################################################
@@ -17,12 +17,13 @@

--
-- Dumping data for table `notificationscheduler`
--

-INSERT INTO `notificationscheduler` VALUES (1, 'Notification stating tasks which are delayed beyond 24 hrs', 'Delayed Task Notification', 1, 'Task Delay Notification', 'Tasks delayed beyond 24 hrs');
-INSERT INTO `notificationscheduler` VALUES (2, 'Big Deal Notification', 'A big deal has been achieved', 1, 'Big Deal notification', 'Success! A big deal has been won!');
-INSERT INTO `notificationscheduler` VALUES (3, 'Notification for pending tickets', 'Pending Tickets notification', 1, 'Pending Tickets notification', 'Ticket pending please');
-INSERT INTO `notificationscheduler` VALUES (4, 'Notifcation stating too many tickets are allocated to an entity', 'Too many tickets Notification', 1, 'Too many tickets Notification', 'Too many tickets pending against this entity ');
-INSERT INTO `notificationscheduler` VALUES (5, 'Support Starting Notification', 'Your support starts today', 1, 'Support Start Notification', 'Support starts please');
-INSERT INTO `notificationscheduler` VALUES (6, 'Support Ending Notification', 'Support Ending Notification', 1, 'Support ending please', 'Support Ending Notification');
+INSERT INTO `notificationscheduler` VALUES (1, 'LBL_TASK_NOTIFICATION_DESCRITPION', 1, 'Task Delay Notification', 'Tasks delayed beyond 24 hrs ', 'LBL_TASK_NOTIFICATION');
+INSERT INTO `notificationscheduler` VALUES (2, 'LBL_BIG_DEAL_DESCRIPTION', 1, 'Big Deal notification', 'Success! A big deal has been won! ', 'LBL_BIG_DEAL');
+INSERT INTO `notificationscheduler` VALUES (3, 'LBL_TICKETS_DESCRIPTION', 1, 'Pending Tickets notification', 'Ticket pending please ', 'LBL_PENDING_TICKETS');
+INSERT INTO `notificationscheduler` VALUES (4, 'LBL_MANY_TICKETS_DESCRIPTION', 1, 'Too many tickets Notification', 'Too many tickets pending against this entity ', 'LBL_MANY_TICKETS');
+INSERT INTO `notificationscheduler` VALUES (5, 'LBL_START_DESCRIPTION', 1, 'Support Start Notification', 'Support starts please ', 'LBL_START_NOTIFICATION');
+INSERT INTO `notificationscheduler` VALUES (6, 'LBL_SUPPORT_DESCRIPTION', 1, 'Support ending please', 'Support Ending Notification', 'LBL_SUPPORT_NOTICIATION');
+INSERT INTO `notificationscheduler` VALUES (7, 'LBL_ACTIVITY_REMINDER_DESCRIPTION', 1, 'Activity Reminder Notication', 'This is a reminder notification for the Activity', 'LBL_ACTIVITY_NOTIFICATION');

--- notificationscheduler_seq.conv 2005-07-11 12:40:50.958158400 -0400
+++ notificationscheduler_seq.new 2005-07-11 12:40:51.148432000 -0400
@@ -10,7 +10,7 @@

--
-- Dumping data for table `notificationscheduler_seq`
--

-INSERT INTO `notificationscheduler_seq` VALUES (6);
+INSERT INTO `notificationscheduler_seq` VALUES (7);

--- opportunitystage.conv 2005-07-11 12:40:50.958158400 -0400
+++ opportunitystage.new 2005-07-11 12:40:51.148432000 -0400
@@ -7,14 +7,14 @@
CREATE TABLE `opportunitystage` (
`potstageid` int(19) NOT NULL auto_increment,
`stage` varchar(200) NOT NULL default '',
`sortorderid` int(19) NOT NULL default '0',
`presence` int(1) NOT NULL default '1',
- `probability` decimal(3,2) default '0.00',
+ `probability` decimal(4,3) default '0.000',
PRIMARY KEY (`potstageid`),
UNIQUE KEY `OpportunityStage_UK0` (`stage`)
-) TYPE=MyISAM AUTO_INCREMENT=1 ;
+) TYPE=InnoDB AUTO_INCREMENT=1 ;

--
-- Dumping data for table `opportunitystage`
--

#######################################################
Early on, I had an issue with probabilities. This may be my issue.
#######################################################
--- potential.conv 2005-07-11 12:40:50.958158400 -0400
+++ potential.new 2005-07-11 12:40:51.158446400 -0400
@@ -13,11 +13,11 @@
`closingdate` date default NULL,
`typeofrevenue` varchar(50) default NULL,
`campaignsource` varchar(30) default NULL,
`nextstep` varchar(100) default NULL,
`private` int(1) default '0',
- `probability` tinyint(3) default '0',
+ `probability` decimal(4,3) default '0.000',
`sales_stage` varchar(50) default NULL,
`potentialtype` varchar(50) default NULL,
`leadsource` varchar(50) default NULL,
`productid` int(50) default NULL,
`productversion` varchar(50) default NULL,
--- potstagehistory.conv 2005-07-11 12:40:50.968172800 -0400
+++ potstagehistory.new 2005-07-11 12:40:51.158446400 -0400
@@ -7,11 +7,11 @@
CREATE TABLE `potstagehistory` (
`historyid` int(19) NOT NULL auto_increment,
`potentialid` int(19) NOT NULL default '0',
`amount` decimal(10,0) default NULL,
`stage` varchar(100) default NULL,
- `probability` decimal(3,2) default NULL,
+ `probability` decimal(4,3) default NULL,
`expectedrevenue` decimal(10,0) default NULL,
`closedate` date default NULL,
`lastmodified` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`historyid`),
KEY `PotStageHistory_IDX1` (`potentialid`)
--- products.conv 2005-07-11 12:40:50.968172800 -0400
+++ products.new 2005-07-11 12:40:51.168460800 -0400
@@ -9,23 +9,40 @@
`productname` varchar(50) NOT NULL default '',
`productcode` varchar(40) default NULL,
`productcategory` varchar(40) default NULL,
`manufacturer` varchar(40) default NULL,
`product_description` longtext,
- `qty_per_unit` decimal(5,2) default '0.00',
- `unit_price` decimal(5,2) default NULL,
- `weight` decimal(10,0) default NULL,
+ `qty_per_unit` decimal(11,2) default '0.00',
+ `unit_price` decimal(11,2) default NULL,
+ `weight` decimal(11,3) default NULL,
`pack_size` int(11) default NULL,
- `start_date` date NOT NULL default '0000-00-00',
- `expiry_date` date NOT NULL default '0000-00-00',
- `purchase_date` date NOT NULL default '0000-00-00',
+ `sales_start_date` date default NULL,
+ `sales_end_date` date default NULL,
+ `start_date` date default NULL,
+ `expiry_date` date default NULL,
`cost_factor` int(11) default NULL,
- `commissionrate` decimal(4,2) default NULL,
+ `commissionrate` decimal(4,3) default NULL,
`commissionmethod` varchar(50) default NULL,
`discontinued` int(1) default NULL,
+ `usageunit` varchar(200) default NULL,
+ `handler` int(11) default NULL,
+ `contactid` int(11) default NULL,
+ `currency` varchar(200) default NULL,
+ `reorderlevel` int(11) default NULL,
+ `website` varchar(100) default NULL,
+ `taxclass` varchar(200) default NULL,
+ `mfr_part_no` varchar(200) default NULL,
+ `vendor_part_no` varchar(200) default NULL,
+ `serialno` varchar(200) default NULL,
+ `qtyinstock` int(11) default NULL,
+ `productsheet` varchar(200) default NULL,
+ `qtyindemand` int(11) default NULL,
+ `glacct` varchar(200) default NULL,
+ `vendor_id` int(11) default NULL,
+ `imagename` varchar(150) default NULL,
PRIMARY KEY (`productid`)
-) TYPE=MyISAM;
+) TYPE=InnoDB;

--
-- Dumping data for table `products`
--

--- profile2field.conv 2005-07-11 12:40:50.978187200 -0400
+++ profile2field.new 2005-07-11 12:40:51.168460800 -0400
########################################
# There were lots of changes in profile2*. I guess it's correct. Are you comfortable with it?
########################################

#######################################################
Notice: Tabs 18-25 were not added to converted tab table
#######################################################
--- tab.conv 2005-07-11 12:40:51.008230400 -0400
+++ tab.new 2005-07-11 12:40:51.238561600 -0400
@@ -20,21 +20,28 @@

--
-- Dumping data for table `tab`
--

INSERT INTO `tab` VALUES (1, 'Dashboard', 0, 12, 'Dashboards', 0, 0, 1);
INSERT INTO `tab` VALUES (2, 'Potentials', 0, 7, 'Potentials', 0, 0, 1);
INSERT INTO `tab` VALUES (3, 'Home', 0, 1, 'Home', 0, 0, 1);
INSERT INTO `tab` VALUES (4, 'Contacts', 0, 6, 'Contacts', 0, 0, 1);
INSERT INTO `tab` VALUES (8, 'Notes', 0, 9, 'Notes', 0, 0, 1);
INSERT INTO `tab` VALUES (9, 'Activities', 0, 3, 'Activities', 0, 0, 1);
INSERT INTO `tab` VALUES (10, 'Emails', 0, 10, 'Emails', 0, 0, 1);
-INSERT INTO `tab` VALUES (13, 'HelpDesk', 2, 11, 'HelpDesk', 0, 0, 1);
+INSERT INTO `tab` VALUES (13, 'HelpDesk', 0, 11, 'HelpDesk', 0, 0, 1);
INSERT INTO `tab` VALUES (14, 'Products', 0, 8, 'Products', 0, 0, 1);
INSERT INTO `tab` VALUES (15, 'Faq', 2, 14, 'Faq', 0, 0, 1);
INSERT INTO `tab` VALUES (16, 'Events', 2, 13, 'Events', 0, 0, 1);
INSERT INTO `tab` VALUES (17, 'Calendar', 0, 2, 'Calendar', 0, 0, 1);
+INSERT INTO `tab` VALUES (18, 'Vendor', 2, 15, 'Vendor', 0, 0, 1);
+INSERT INTO `tab` VALUES (19, 'PriceBook', 2, 16, 'PriceBook', 0, 0, 1);
+INSERT INTO `tab` VALUES (20, 'Quotes', 0, 17, 'Quotes', 0, 0, 1);
+INSERT INTO `tab` VALUES (21, 'Orders', 0, 18, 'Orders', 0, 0, 1);
+INSERT INTO `tab` VALUES (22, 'SalesOrder', 2, 19, 'SalesOrder', 0, 0, 1);
+INSERT INTO `tab` VALUES (23, 'Invoice', 0, 20, 'Invoice', 0, 0, 1);
+INSERT INTO `tab` VALUES (24, 'Rss', 0, 21, 'Rss', 0, 0, 1);
+INSERT INTO `tab` VALUES (25, 'Reports', 0, 22, 'Reports', 0, 0, 1);

--- ticketpriorities.conv 2005-07-11 12:40:51.008230400 -0400
+++ ticketpriorities.new 2005-07-11 12:40:51.248576000 -0400
@@ -15,9 +15,9 @@
--
-- Dumping data for table `ticketpriorities`
--

INSERT INTO `ticketpriorities` VALUES (1, 'Low', 0, 1);
-INSERT INTO `ticketpriorities` VALUES (2, 'Medium', 1, 1);
+INSERT INTO `ticketpriorities` VALUES (2, 'Normal', 1, 1);
INSERT INTO `ticketpriorities` VALUES (3, 'High', 2, 1);
-INSERT INTO `ticketpriorities` VALUES (4, 'Critical', 3, 1);
+INSERT INTO `ticketpriorities` VALUES (4, 'Urgent', 3, 1);

--- troubletickets.conv 2005-07-11 12:40:51.018244800 -0400
+++ troubletickets.new 2005-07-11 12:40:51.258590400 -0400
@@ -5,20 +5,24 @@
--

CREATE TABLE `troubletickets` (
`ticketid` int(19) NOT NULL default '0',
`groupname` varchar(100) default NULL,
- `contact_id` varchar(100) default NULL,
+ `parent_id` varchar(100) default NULL,
+ `product_id` varchar(100) default NULL,
`priority` varchar(150) default NULL,
+ `severity` varchar(150) default NULL,
`status` varchar(150) default NULL,
`category` varchar(150) default NULL,
`title` varchar(255) NOT NULL default '',
`description` longtext,
+ `solution` longtext,
`update_log` longtext,
`version_id` int(11) default NULL,
- PRIMARY KEY (`ticketid`)
-) TYPE=MyISAM;
+ PRIMARY KEY (`ticketid`),
+ KEY `troubletickets_IDX0` (`ticketid`)
+) TYPE=InnoDB;

--
-- Dumping data for table `troubletickets`
--

--- users.conv 2005-07-11 12:40:51.018244800 -0400
+++ users.new 2005-07-11 12:40:51.258590400 -0400
@@ -26,10 +26,11 @@
`phone_fax` varchar(50) default NULL,
`email1` varchar(100) default NULL,
`email2` varchar(100) default NULL,
`yahoo_id` varchar(100) default NULL,
`status` varchar(25) default NULL,
+ `signature` varchar(50) default NULL,
`address_street` varchar(150) default NULL,
`address_city` varchar(100) default NULL,
`address_state` varchar(100) default NULL,
`address_country` varchar(25) default NULL,
`address_postalcode` varchar(9) default NULL,
@@ -37,15 +38,16 @@
`tz` varchar(30) default NULL,
`holidays` varchar(60) default NULL,
`namedays` varchar(60) default NULL,
`workdays` varchar(30) default NULL,
`weekstart` int(11) default NULL,
+ `date_format` varchar(30) default NULL,
`deleted` int(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `idx_user_name` (`user_name`),
KEY `user_password` (`user_password`)
-) TYPE=MyISAM AUTO_INCREMENT=19643 ;
+) TYPE=InnoDB AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

--- wordtemplates.conv 2005-07-11 12:40:51.028259200 -0400
+++ wordtemplates.new 2005-07-11 12:40:51.278619200 -0400
@@ -19,5 +19,501 @@

--
-- Dumping data for table `wordtemplates`
--

+
+--
+-- Constraints for dumped tables
+--
+
+--
+-- Constraints for table `account`
+--
+ALTER TABLE `account`
+ ADD CONSTRAINT `fk_Account` FOREIGN KEY (`accountid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `accountbillads`
+--
+ALTER TABLE `accountbillads`
+ ADD CONSTRAINT `fk_AccountBillAds` FOREIGN KEY (`accountaddressid`) REFERENCES `account` (`accountid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `accountscf`
+--
+ALTER TABLE `accountscf`
+ ADD CONSTRAINT `fk_AccountsCF` FOREIGN KEY (`accountid`) REFERENCES `account` (`accountid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `accountshipads`
+--
+ALTER TABLE `accountshipads`
+ ADD CONSTRAINT `fk_AccountShipAds` FOREIGN KEY (`accountaddressid`) REFERENCES `account` (`accountid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `activity`
+--
+ALTER TABLE `activity`
+ ADD CONSTRAINT `fk_Activity` FOREIGN KEY (`activityid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `activitygrouprelation`
+--
+ALTER TABLE `activitygrouprelation`
+ ADD CONSTRAINT `fk_activitygrouprelation2` FOREIGN KEY (`groupname`) REFERENCES `groups` (`name`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_activitygrouprelation1` FOREIGN KEY (`activityid`) REFERENCES `activity` (`activityid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `activityproductrel`
+--
+ALTER TABLE `activityproductrel`
+ ADD CONSTRAINT `fk_activityproductrel1` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_activityproductRel` FOREIGN KEY (`activityid`) REFERENCES `activity` (`activityid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `attachments`
+--
+ALTER TABLE `attachments`
+ ADD CONSTRAINT `fk_Attachments` FOREIGN KEY (`attachmentsid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `cntactivityrel`
+--
+ALTER TABLE `cntactivityrel`
+ ADD CONSTRAINT `fk_CntActivityRel2` FOREIGN KEY (`activityid`) REFERENCES `activity` (`activityid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_CntActivityRel` FOREIGN KEY (`contactid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `competitor`
+--
+ALTER TABLE `competitor`
+ ADD CONSTRAINT `fk_Competitor` FOREIGN KEY (`competitorid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `contactaddress`
+--
+ALTER TABLE `contactaddress`
+ ADD CONSTRAINT `fk_ContactAddress` FOREIGN KEY (`contactaddressid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `contactdetails`
+--
+ALTER TABLE `contactdetails`
+ ADD CONSTRAINT `fk_ContactDetails` FOREIGN KEY (`contactid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `contactscf`
+--
+ALTER TABLE `contactscf`
+ ADD CONSTRAINT `fk_ContactsCF` FOREIGN KEY (`contactid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `contactsubdetails`
+--
+ALTER TABLE `contactsubdetails`
+ ADD CONSTRAINT `fk_ContactSubDetails` FOREIGN KEY (`contactsubscriptionid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `contpotentialrel`
+--
+ALTER TABLE `contpotentialrel`
+ ADD CONSTRAINT `fk_ContPotentialRel` FOREIGN KEY (`potentialid`) REFERENCES `potential` (`potentialid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_ContPotentialRel1` FOREIGN KEY (`contactid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `crmentitynotesrel`
+--
+ALTER TABLE `crmentitynotesrel`
+ ADD CONSTRAINT `fk_crmentityNotes2` FOREIGN KEY (`notesid`) REFERENCES `notes` (`notesid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_crmentityNotes` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `faq`
+--
+ALTER TABLE `faq`
+ ADD CONSTRAINT `fk_faqCF` FOREIGN KEY (`id`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `faqcomments`
+--
+ALTER TABLE `faqcomments`
+ ADD CONSTRAINT `fk_faqcommentsCF` FOREIGN KEY (`faqid`) REFERENCES `faq` (`id`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `field`
+--
+ALTER TABLE `field`
+ ADD CONSTRAINT `fk_pi` FOREIGN KEY (`tabid`) REFERENCES `tab` (`tabid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `invoice`
+--
+ALTER TABLE `invoice`
+ ADD CONSTRAINT `fk_Invoice1` FOREIGN KEY (`invoiceid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `invoicebillads`
+--
+ALTER TABLE `invoicebillads`
+ ADD CONSTRAINT `fk_InvoiceBillAds` FOREIGN KEY (`invoicebilladdressid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `invoicecf`
+--
+ALTER TABLE `invoicecf`
+ ADD CONSTRAINT `fk_InvoiceCF` FOREIGN KEY (`invoiceid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `invoiceproductrel`
+--
+ALTER TABLE `invoiceproductrel`
+ ADD CONSTRAINT `fk_InvoiceProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_InvoiceProductRel` FOREIGN KEY (`invoiceid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `invoiceshipads`
+--
+ALTER TABLE `invoiceshipads`
+ ADD CONSTRAINT `fk_InvoiceShipAds` FOREIGN KEY (`invoiceshipaddressid`) REFERENCES `invoice` (`invoiceid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `lar`
+--
+ALTER TABLE `lar`
+ ADD CONSTRAINT `fk_LAR` FOREIGN KEY (`createdby`) REFERENCES `users` (`id`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadacctrel`
+--
+ALTER TABLE `leadacctrel`
+ ADD CONSTRAINT `fk_LeadAcctRel2` FOREIGN KEY (`accountid`) REFERENCES `account` (`accountid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_LeadAcctRel1` FOREIGN KEY (`leadid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadaddress`
+--
+ALTER TABLE `leadaddress`
+ ADD CONSTRAINT `fk_LeadAddress` FOREIGN KEY (`leadaddressid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadcontrel`
+--
+ALTER TABLE `leadcontrel`
+ ADD CONSTRAINT `fk_LeadContRel2` FOREIGN KEY (`contactid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_LeadContRel1` FOREIGN KEY (`leadid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leaddetails`
+--
+ALTER TABLE `leaddetails`
+ ADD CONSTRAINT `fk_LeadDetails` FOREIGN KEY (`leadid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadgrouprelation`
+--
+ALTER TABLE `leadgrouprelation`
+ ADD CONSTRAINT `fk_leadgrouprelation2` FOREIGN KEY (`groupname`) REFERENCES `groups` (`name`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_leadgrouprelation` FOREIGN KEY (`leadid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadpotrel`
+--
+ALTER TABLE `leadpotrel`
+ ADD CONSTRAINT `fk_LeadPotRel2` FOREIGN KEY (`potentialid`) REFERENCES `potential` (`potentialid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_LeadPotRel1` FOREIGN KEY (`leadid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadscf`
+--
+ALTER TABLE `leadscf`
+ ADD CONSTRAINT `fk_LeadsCF` FOREIGN KEY (`leadid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `leadsubdetails`
+--
+ALTER TABLE `leadsubdetails`
+ ADD CONSTRAINT `fk_LeadSubDetails` FOREIGN KEY (`leadsubscriptionid`) REFERENCES `leaddetails` (`leadid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `notes`
+--
+ALTER TABLE `notes`
+ ADD CONSTRAINT `fk_Notes` FOREIGN KEY (`notesid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `pobillads`
+--
+ALTER TABLE `pobillads`
+ ADD CONSTRAINT `fk_PoBillAds` FOREIGN KEY (`pobilladdressid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `poproductrel`
+--
+ALTER TABLE `poproductrel`
+ ADD CONSTRAINT `fk_PoProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_PoProductRel` FOREIGN KEY (`purchaseorderid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `poshipads`
+--
+ALTER TABLE `poshipads`
+ ADD CONSTRAINT `fk_PoShipAds` FOREIGN KEY (`poshipaddressid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `potcompetitorrel`
+--
+ALTER TABLE `potcompetitorrel`
+ ADD CONSTRAINT `fk_PotCompetitorRel2` FOREIGN KEY (`competitorid`) REFERENCES `competitor` (`competitorid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_PotCompetitorRel` FOREIGN KEY (`potentialid`) REFERENCES `potential` (`potentialid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `potential`
+--
+ALTER TABLE `potential`
+ ADD CONSTRAINT `fk_Potential` FOREIGN KEY (`potentialid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `potentialscf`
+--
+ALTER TABLE `potentialscf`
+ ADD CONSTRAINT `fk_PotentialsCF2` FOREIGN KEY (`potentialid`) REFERENCES `potential` (`potentialid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `potstagehistory`
+--
+ALTER TABLE `potstagehistory`
+ ADD CONSTRAINT `fk_PotStageHistory` FOREIGN KEY (`potentialid`) REFERENCES `potential` (`potentialid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `pricebook`
+--
+ALTER TABLE `pricebook`
+ ADD CONSTRAINT `fk_PriceBook` FOREIGN KEY (`pricebookid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `pricebookcf`
+--
+ALTER TABLE `pricebookcf`
+ ADD CONSTRAINT `fk_PriceBookCF` FOREIGN KEY (`pricebookid`) REFERENCES `pricebook` (`pricebookid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `pricebookproductrel`
+--
+ALTER TABLE `pricebookproductrel`
+ ADD CONSTRAINT `fk_PriceBookProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_PriceBookProductRel` FOREIGN KEY (`pricebookid`) REFERENCES `pricebook` (`pricebookid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `productcf`
+--
+ALTER TABLE `productcf`
+ ADD CONSTRAINT `fk_ProductCF` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `productcollaterals`
+--
+ALTER TABLE `productcollaterals`
+ ADD CONSTRAINT `fk_productcollaterals` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `products`
+--
+ALTER TABLE `products`
+ ADD CONSTRAINT `fk_productsCF` FOREIGN KEY (`productid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `purchaseorder`
+--
+ALTER TABLE `purchaseorder`
+ ADD CONSTRAINT `fk_PO1` FOREIGN KEY (`purchaseorderid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `purchaseordercf`
+--
+ALTER TABLE `purchaseordercf`
+ ADD CONSTRAINT `fk_PoCF` FOREIGN KEY (`purchaseorderid`) REFERENCES `purchaseorder` (`purchaseorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `quotes`
+--
+ALTER TABLE `quotes`
+ ADD CONSTRAINT `fk_Quotes1` FOREIGN KEY (`quoteid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `quotesbillads`
+--
+ALTER TABLE `quotesbillads`
+ ADD CONSTRAINT `fk_QuotesBillAds` FOREIGN KEY (`quotebilladdressid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `quotescf`
+--
+ALTER TABLE `quotescf`
+ ADD CONSTRAINT `fk_QuotesCF` FOREIGN KEY (`quoteid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `quotesproductrel`
+--
+ALTER TABLE `quotesproductrel`
+ ADD CONSTRAINT `fk_QuotesProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_QuotesProductRel` FOREIGN KEY (`quoteid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `quotesshipads`
+--
+ALTER TABLE `quotesshipads`
+ ADD CONSTRAINT `fk_QuotesShipAds` FOREIGN KEY (`quoteshipaddressid`) REFERENCES `quotes` (`quoteid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `reportdatefilter`
+--
+ALTER TABLE `reportdatefilter`
+ ADD CONSTRAINT `reportdatefilter_FK1` FOREIGN KEY (`datefilterid`) REFERENCES `report` (`reportid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `reportmodules`
+--
+ALTER TABLE `reportmodules`
+ ADD CONSTRAINT `reportmodules_FK1` FOREIGN KEY (`reportmodulesid`) REFERENCES `report` (`reportid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `reportsortcol`
+--
+ALTER TABLE `reportsortcol`
+ ADD CONSTRAINT `reportsortcol_FK1` FOREIGN KEY (`reportid`) REFERENCES `report` (`reportid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `reportsummary`
+--
+ALTER TABLE `reportsummary`
+ ADD CONSTRAINT `reportsummary_FK1` FOREIGN KEY (`reportsummaryid`) REFERENCES `report` (`reportid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `salesmanactivityrel`
+--
+ALTER TABLE `salesmanactivityrel`
+ ADD CONSTRAINT `fk_SalesmanActivityRel2` FOREIGN KEY (`activityid`) REFERENCES `activity` (`activityid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SalesmanActivityRel` FOREIGN KEY (`smid`) REFERENCES `users` (`id`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `salesmanticketrel`
+--
+ALTER TABLE `salesmanticketrel`
+ ADD CONSTRAINT `fk_SalesmanTicketRel2` FOREIGN KEY (`id`) REFERENCES `troubletickets` (`ticketid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SalesmanTicketRel` FOREIGN KEY (`smid`) REFERENCES `users` (`id`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `salesorder`
+--
+ALTER TABLE `salesorder`
+ ADD CONSTRAINT `fk_SO1` FOREIGN KEY (`salesorderid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `salesordercf`
+--
+ALTER TABLE `salesordercf`
+ ADD CONSTRAINT `fk_SoCF` FOREIGN KEY (`salesorderid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `seactivityrel`
+--
+ALTER TABLE `seactivityrel`
+ ADD CONSTRAINT `fk_SeActivityRel2` FOREIGN KEY (`activityid`) REFERENCES `activity` (`activityid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SeActivityRel1` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `seattachmentsrel`
+--
+ALTER TABLE `seattachmentsrel`
+ ADD CONSTRAINT `fk_SeAttachmentsRel2` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SeAttachmentsRel` FOREIGN KEY (`attachmentsid`) REFERENCES `attachments` (`attachmentsid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `senotesrel`
+--
+ALTER TABLE `senotesrel`
+ ADD CONSTRAINT `fk_SeNotesRel2` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SeNotesRel` FOREIGN KEY (`notesid`) REFERENCES `notes` (`notesid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `seproductsrel`
+--
+ALTER TABLE `seproductsrel`
+ ADD CONSTRAINT `fk_SeProductsRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SeProductsRel1` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `seticketsrel`
+--
+ALTER TABLE `seticketsrel`
+ ADD CONSTRAINT `fk_SeTicketsRel2` FOREIGN KEY (`ticketid`) REFERENCES `troubletickets` (`ticketid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SeTicketsRel1` FOREIGN KEY (`crmid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `sobillads`
+--
+ALTER TABLE `sobillads`
+ ADD CONSTRAINT `fk_SoBillAds` FOREIGN KEY (`sobilladdressid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `soproductrel`
+--
+ALTER TABLE `soproductrel`
+ ADD CONSTRAINT `fk_SoProductRel2` FOREIGN KEY (`productid`) REFERENCES `products` (`productid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_SoProductRel` FOREIGN KEY (`salesorderid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `soshipads`
+--
+ALTER TABLE `soshipads`
+ ADD CONSTRAINT `fk_SoShipAds` FOREIGN KEY (`soshipaddressid`) REFERENCES `salesorder` (`salesorderid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `ticketcf`
+--
+ALTER TABLE `ticketcf`
+ ADD CONSTRAINT `fk_HelpDeskCF` FOREIGN KEY (`ticketid`) REFERENCES `troubletickets` (`ticketid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `ticketcomments`
+--
+ALTER TABLE `ticketcomments`
+ ADD CONSTRAINT `fk_ticketcommentsCF` FOREIGN KEY (`ticketid`) REFERENCES `troubletickets` (`ticketid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `ticketgrouprelation`
+--
+ALTER TABLE `ticketgrouprelation`
+ ADD CONSTRAINT `fk_ticketgrouprelation2` FOREIGN KEY (`groupname`) REFERENCES `groups` (`name`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_ticketgrouprelation1` FOREIGN KEY (`ticketid`) REFERENCES `troubletickets` (`ticketid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `troubletickets`
+--
+ALTER TABLE `troubletickets`
+ ADD CONSTRAINT `fk_troubleticketsCF` FOREIGN KEY (`ticketid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `user2role`
+--
+ALTER TABLE `user2role`
+ ADD CONSTRAINT `fk_user2role2` FOREIGN KEY (`roleid`) REFERENCES `role` (`roleid`) ON DELETE CASCADE,
+ ADD CONSTRAINT `fk_user2role1` FOREIGN KEY (`userid`) REFERENCES `users` (`id`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `vendor`
+--
+ALTER TABLE `vendor`
+ ADD CONSTRAINT `fk_Vendor` FOREIGN KEY (`vendorid`) REFERENCES `crmentity` (`crmid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `vendorcf`
+--
+ALTER TABLE `vendorcf`
+ ADD CONSTRAINT `fk_VendorCF` FOREIGN KEY (`vendorid`) REFERENCES `vendor` (`vendorid`) ON DELETE CASCADE;
+
+--
+-- Constraints for table `vendorcontactrel`
+--
+ALTER TABLE `vendorcontactrel`
+ ADD CONSTRAINT `fk_VendorContactRel` FOREIGN KEY (`contactid`) REFERENCES `contactdetails` (`contactid`) ON DELETE CASCADE;

Guest

Impressive Mike. Thanks for the great work !

5 stars to you !

NonoFr · Joined: 22 Jun 2005 Posts: 31

Impressive Mike. Thanks for the great work !

5 stars to you !

indigoleopard · Joined: 21 Aug 2004 Posts: 2111 Location: india,chennai

Hello Team!

Mike, how did you go about the Migration itself?
I am listing out the procedure to test the Migration script. I guess, I should be saying, if the following is done, the migration script should work ideally.

Kindly point out the changes if any.

a) Install the 4.2 GA.
Run the install.php and other files to ensure that you have the data populated.

b) Now, run the migration script.

That, should suffice. I am still open to issues. I am clarifying the pending tables issue below.
Do get back please.

The custom field tables will be formed ONLY if there were custom fields in the 4.0.1 setup.
Sefaqrel table is dropped in the 4.2 GA as it was not required anymore.

There should not be any table called account_potential in 4.0.1 . That table belonged to the 3.2 version and was dropped in the 4.0.1 itself.

Thanks,
Richie

mikecrowe · Joined: 04 Jan 2005 Posts: 499

Richie,

The first thing the script does is erase vtigercrm4_2, which is the new install data. So, Migrate.php must handle the upgrade completely.

See the private message I sent you. I include a lot more detail.

Mike