SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `users` ( id int not null primary key ); CREATE TABLE `ib_processed_tickets` ( `volume` decimal(20,8) DEFAULT NULL, `close_time` datetime DEFAULT NULL, `symbol` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `commission` decimal(20,10) DEFAULT NULL, `processedAt` datetime DEFAULT NULL, `ibId` bigint(20) unsigned DEFAULT NULL, `refId` int(11) DEFAULT NULL, `fromLoginSid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `currency` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `id` int(10) NOT NULL AUTO_INCREMENT, `calculatedCommission` decimal(20,10) DEFAULT NULL, `calculatedCurrency` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `level` smallint(6) DEFAULT NULL, `isProcessedToWallet` smallint(6) NOT NULL DEFAULT '0', `log` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL, `conversionRate` decimal(10,5) DEFAULT NULL, `ruleId` int(11) DEFAULT NULL, `isLast` smallint(6) DEFAULT '0', `ticketSid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `walletLoginSid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `uniqueSalt` varchar(50) COLLATE utf8_unicode_ci DEFAULT '-', `divider` int(10) unsigned NOT NULL DEFAULT '1', `transactionId` int(11) DEFAULT NULL, `hasNegativeBalance` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `IDX_UNIQUE_COMMISSION_PER_TICKET` (`ticketSid`,`ibId`,`uniqueSalt`), KEY `IDX_2BD954FB475D29D6` (`ibId`), KEY `IDX_2BD954FB6EF6A51C` (`refId`), KEY `IDX_2BD954FB5EC879BD` (`fromLoginSid`), KEY `isProcessedToWallet` (`isProcessedToWallet`), KEY `IDX_AUTOREFERRAL` (`ibId`,`refId`), KEY `IDX_LAST` (`isLast`), KEY `IDX_CLOSE_TIME` (`close_time`), KEY `IDX_IB_COMMISSION` (`ibId`,`commission`), KEY `IDX_FIX_BALANCE` (`ibId`,`isProcessedToWallet`,`currency`), KEY `IDX_WALET_LOGINSID` (`walletLoginSid`), KEY `IDX_UNIQUE_COMMISSION` (`ibId`,`ticketSid`), KEY `IDX_TRANSACTION` (`transactionId`), KEY `IDX_FIX_BALANCE2` (`walletLoginSid`,`isProcessedToWallet`), CONSTRAINT `FK_2BD954FB6EF6A51C` FOREIGN KEY (`refId`) REFERENCES `users` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB AUTO_INCREMENT=2214954250 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ; CREATE TABLE `ib_processed_tickets_last` ( `processedTicketId` int(10) NOT NULL, `ticketSid` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`processedTicketId`), KEY `IDX_TICKETSID` (`ticketSid`), CONSTRAINT `FK_IS_LAST_PROCESSED_TICKETS` FOREIGN KEY (`processedTicketId`) REFERENCES `ib_processed_tickets` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `ib_processed_tickets_tmp` ( `volume` decimal(20,8) DEFAULT NULL, `close_time` datetime DEFAULT NULL, `symbol` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `commission` decimal(20,10) DEFAULT NULL, `processedAt` datetime DEFAULT NULL, `ibId` bigint(20) unsigned DEFAULT NULL, `refId` int(11) DEFAULT NULL, `fromLoginSid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `currency` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `calculatedCommission` decimal(20,10) DEFAULT NULL, `calculatedCurrency` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `level` smallint(6) DEFAULT NULL, `isProcessedToWallet` smallint(6) NOT NULL DEFAULT '0', `log` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL, `conversionRate` decimal(10,5) DEFAULT NULL, `ruleId` int(11) DEFAULT NULL, `isLast` smallint(6) DEFAULT '0', `ticketSid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `walletLoginSid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `uniqueSalt` varchar(50) COLLATE utf8_unicode_ci DEFAULT '-', `divider` int(10) unsigned NOT NULL DEFAULT '1', `transactionId` int(11) DEFAULT NULL, `hasNegativeBalance` tinyint(3) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2214954250 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; INSERT INTO `ib_processed_tickets_tmp` SELECT * FROM `fxbackoffice`.`ib_processed_tickets`; ALTER TABLE ib_processed_tickets drop constraint FK_2BD954FB6EF6A51C; ALTER TABLE ib_processed_tickets_tmp ADD UNIQUE KEY `IDX_UNIQUE_COMMISSION_PER_TICKET` (`ticketSid`,`ibId`,`uniqueSalt`), ADD KEY `IDX_2BD954FB475D29D6` (`ibId`), ADD KEY `IDX_2BD954FB6EF6A51C` (`refId`), ADD KEY `IDX_2BD954FB5EC879BD` (`fromLoginSid`), ADD KEY `isProcessedToWallet` (`isProcessedToWallet`), ADD KEY `IDX_AUTOREFERRAL` (`ibId`,`refId`), ADD KEY `IDX_LAST` (`isLast`), ADD KEY `IDX_CLOSE_TIME` (`close_time`), ADD KEY `IDX_IB_COMMISSION` (`ibId`,`commission`), ADD KEY `IDX_FIX_BALANCE` (`ibId`,`isProcessedToWallet`,`currency`), ADD KEY `IDX_WALET_LOGINSID` (`walletLoginSid`), ADD KEY `IDX_UNIQUE_COMMISSION` (`ibId`,`ticketSid`), ADD KEY `IDX_TRANSACTION` (`transactionId`), ADD KEY `IDX_FIX_BALANCE2` (`walletLoginSid`,`isProcessedToWallet`), ADD CONSTRAINT `FK_2BD954FB6EF6A51C` FOREIGN KEY (`refId`) REFERENCES `users` (`id`) ON DELETE SET NULL; ALTER TABLE ib_processed_tickets_last DROP FOREIGN KEY FK_IS_LAST_PROCESSED_TICKETS; ALTER TABLE ib_processed_tickets_last modify processedTicketId bigint UNSIGNED not null; ALTER TABLE ib_processed_tickets RENAME to ib_processed_tickets_bak20240905; ALTER TABLE ib_processed_tickets_tmp RENAME to ib_processed_tickets; ALTER TABLE ADD CONSTRAINT `FK_IS_LAST_PROCESSED_TICKETS` FOREIGN KEY (`processedTicketId`) REFERENCES `ib_processed_tickets` (`id`) ON DELETE CASCADE;