mirror of
https://github.com/opensourcepos/opensourcepos.git
synced 2026-05-27 01:38:48 -04:00
b0dddc22a3
* Bugfixes to get Migration working on MySQL Signed-off-by: objec <objecttothis@gmail.com> * MariaDB compatibility fixes - Drop foreign key constraints before making charset changes - Fix dropAllForeignKeyConstraints helper function. - Added `IF EXISTS` to DROP statements - Do not try to readd FK constraints for tables which were dropped. - MariaDB 11.8.x changes the default implicit collation to uca1400 which breaks the IndiaGST migration, et. al. Explicitly declare utf8_general_ci in affected migrations. Signed-off-by: objec <objecttothis@gmail.com> * Fix changes which break MySQL migrations - MySQL does not support IF EXISTS in foreign key constraints. Since the PHP is now handling dropping those constraints, these lines are redundant. Remove them. Signed-off-by: objec <objecttothis@gmail.com> * Resolve code review recommendations - Add try/catch around DB connect statement - Heed result of execute_script function and throw an exception on failure. Signed-off-by: objec <objecttothis@gmail.com> * Refactor out duplicate code Signed-off-by: objec <objecttothis@gmail.com> * Initialize array variable causing potential issues Signed-off-by: objec <objecttothis@gmail.com> --------- Signed-off-by: objec <objecttothis@gmail.com>
27 lines
1.1 KiB
SQL
27 lines
1.1 KiB
SQL
-- Improve payment tracking
|
|
|
|
RENAME TABLE ospos_sales_payments TO ospos_sales_payments_backup;
|
|
|
|
CREATE TABLE `ospos_sales_payments` (
|
|
`payment_id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`sale_id` int(10) NOT NULL,
|
|
`payment_type` varchar(40) NOT NULL,
|
|
`payment_amount` decimal(15,2) NOT NULL,
|
|
`payment_user` int(11) NOT NULL DEFAULT 0,
|
|
`payment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`reference_code` varchar(40) NOT NULL DEFAULT '',
|
|
PRIMARY KEY (`payment_id`),
|
|
KEY `payment_sale` (`sale_id`, `payment_type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
|
|
|
|
INSERT INTO ospos_sales_payments (sale_id, payment_type, payment_amount, payment_user)
|
|
SELECT payments.sale_id, payments.payment_type, payments.payment_amount, sales.employee_id
|
|
FROM ospos_sales_payments_backup AS payments
|
|
JOIN ospos_sales AS sales ON payments.sale_id = sales.sale_id
|
|
ORDER BY payments.sale_id, payments.payment_type;
|
|
|
|
DROP TABLE IF EXISTS ospos_sales_payments_backup;
|
|
|
|
ALTER TABLE `ospos_sales_payments`
|
|
ADD CONSTRAINT `ospos_sales_payments_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `ospos_sales` (`sale_id`);
|