Perintah sql jika database slims anda kehilangan auto increment, primary key (uniqe), indexs dan triger

-- =====================================================
-- PERBAIKAN AUTO_INCREMENT DAN PRIMARY KEY
-- =====================================================

-- 1. Tabel backup_log
ALTER TABLE `backup_log` 
MODIFY `backup_log_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`backup_log_id`);

-- 2. Tabel biblio
ALTER TABLE `biblio` 
MODIFY `biblio_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`biblio_id`);

-- 3. Tabel biblio_attachment
ALTER TABLE `biblio_attachment`
ADD PRIMARY KEY (`biblio_id`, `file_id`);

-- 4. Tabel biblio_author
ALTER TABLE `biblio_author`
ADD PRIMARY KEY (`biblio_id`, `author_id`);

-- 5. Tabel biblio_topic
ALTER TABLE `biblio_topic`
ADD PRIMARY KEY (`biblio_id`, `topic_id`);

-- 6. Tabel content
ALTER TABLE `content` 
MODIFY `content_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`content_id`);

-- 7. Tabel files
ALTER TABLE `files` 
MODIFY `file_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`file_id`);

-- 8. Tabel fines
ALTER TABLE `fines` 
MODIFY `fines_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`fines_id`);

-- 9. Tabel group_access
ALTER TABLE `group_access`
ADD PRIMARY KEY (`group_id`, `module_id`);

-- 10. Tabel holiday
ALTER TABLE `holiday` 
MODIFY `holiday_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`holiday_id`);

-- 11. Tabel item
ALTER TABLE `item` 
MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`item_id`);

-- 12. Tabel item_custom
ALTER TABLE `item_custom`
ADD PRIMARY KEY (`item_id`);

-- 13. Tabel kardex
ALTER TABLE `kardex` 
MODIFY `kardex_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`kardex_id`);

-- 14. Tabel loan
ALTER TABLE `loan` 
MODIFY `loan_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`loan_id`);

-- 15. Tabel member
ALTER TABLE `member`
ADD PRIMARY KEY (`member_id`);

-- 16. Tabel mst_author
ALTER TABLE `mst_author` 
MODIFY `author_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`author_id`);

-- 17. Tabel mst_coll_type
ALTER TABLE `mst_coll_type` 
MODIFY `coll_type_id` int(3) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`coll_type_id`);

-- 18. Tabel mst_frequency
ALTER TABLE `mst_frequency` 
MODIFY `frequency_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`frequency_id`);

-- 19. Tabel mst_gmd
ALTER TABLE `mst_gmd` 
MODIFY `gmd_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`gmd_id`);

-- 20. Tabel mst_item_status
ALTER TABLE `mst_item_status`
ADD PRIMARY KEY (`item_status_id`);

-- 21. Tabel mst_label
ALTER TABLE `mst_label` 
MODIFY `label_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`label_id`);

-- 22. Tabel mst_language
ALTER TABLE `mst_language`
ADD PRIMARY KEY (`language_id`);

-- 23. Tabel mst_loan_rules
ALTER TABLE `mst_loan_rules` 
MODIFY `loan_rules_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`loan_rules_id`);

-- 24. Tabel mst_location
ALTER TABLE `mst_location`
ADD PRIMARY KEY (`location_id`);

-- 25. Tabel mst_member_type
ALTER TABLE `mst_member_type` 
MODIFY `member_type_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`member_type_id`);

-- 26. Tabel mst_module
ALTER TABLE `mst_module` 
MODIFY `module_id` int(3) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`module_id`);

-- 27. Tabel mst_place
ALTER TABLE `mst_place` 
MODIFY `place_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`place_id`);

-- 28. Tabel mst_publisher
ALTER TABLE `mst_publisher` 
MODIFY `publisher_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`publisher_id`);

-- 29. Tabel mst_supplier
ALTER TABLE `mst_supplier` 
MODIFY `supplier_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`supplier_id`);

-- 30. Tabel mst_topic
ALTER TABLE `mst_topic` 
MODIFY `topic_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`topic_id`);

-- 31. Tabel reserve
ALTER TABLE `reserve` 
MODIFY `reserve_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`reserve_id`);

-- 32. Tabel serial
ALTER TABLE `serial` 
MODIFY `serial_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`serial_id`);

-- 33. Tabel setting
ALTER TABLE `setting` 
MODIFY `setting_id` int(3) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`setting_id`);

-- 34. Tabel stock_take
ALTER TABLE `stock_take` 
MODIFY `stock_take_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`stock_take_id`);

-- 35. Tabel stock_take_item
ALTER TABLE `stock_take_item`
ADD PRIMARY KEY (`stock_take_id`, `item_id`);

-- 36. Tabel system_log
ALTER TABLE `system_log` 
MODIFY `log_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`log_id`);

-- 37. Tabel user
ALTER TABLE `user` 
MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`user_id`);

-- 38. Tabel user_group
ALTER TABLE `user_group` 
MODIFY `group_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`group_id`);

-- 39. Tabel visitor_count
ALTER TABLE `visitor_count` 
MODIFY `visitor_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`visitor_id`);

-- 40. Tabel biblio_custom
ALTER TABLE `biblio_custom`
ADD PRIMARY KEY (`biblio_id`);

-- 41. Tabel member_custom
ALTER TABLE `member_custom`
ADD PRIMARY KEY (`member_id`);

-- 42. Tabel comment
ALTER TABLE `comment` 
MODIFY `comment_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`comment_id`);

-- 43. Tabel mst_carrier_type
ALTER TABLE `mst_carrier_type` 
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);

-- 44. Tabel mst_content_type
ALTER TABLE `mst_content_type` 
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);

-- 45. Tabel mst_media_type
ALTER TABLE `mst_media_type` 
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);

-- 46. Tabel mst_relation_term
ALTER TABLE `mst_relation_term` 
MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`ID`);

-- 47. Tabel mst_voc_ctrl
ALTER TABLE `mst_voc_ctrl` 
MODIFY `vocabolary_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`vocabolary_id`);

-- 48. Tabel biblio_relation
ALTER TABLE `biblio_relation`
ADD PRIMARY KEY (`biblio_id`, `rel_biblio_id`);

-- 49. Tabel mst_servers
ALTER TABLE `mst_servers` 
MODIFY `server_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`server_id`);

-- 50. Tabel biblio_log
ALTER TABLE `biblio_log` 
MODIFY `biblio_log_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`biblio_log_id`);

-- 51. Tabel loan_history
ALTER TABLE `loan_history`
ADD PRIMARY KEY (`loan_id`);

-- 52. Tabel mst_custom_field
ALTER TABLE `mst_custom_field` 
MODIFY `field_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`field_id`),
ADD UNIQUE KEY `dbfield_unique` (`dbfield`);

-- 53. Tabel files_read
ALTER TABLE `files_read` 
MODIFY `filelog_id` int(11) NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`filelog_id`);

-- 54. Tabel plugins
ALTER TABLE `plugins`
ADD PRIMARY KEY (`id`);

-- 55. Tabel index_words
ALTER TABLE `index_words` 
MODIFY `id` bigint NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);

-- 56. Tabel index_documents
ALTER TABLE `index_documents`
ADD PRIMARY KEY (`document_id`, `word_id`, `location`);

-- 57. Tabel user_tokens
ALTER TABLE `user_tokens` 
MODIFY `id` int NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (`id`);

-- =====================================================
-- INDEX YANG HILANG
-- =====================================================

-- Tabel biblio
ALTER TABLE `biblio` ADD INDEX `references_idx` (`gmd_id`, `publisher_id`, `language_id`, `publish_place_id`);
ALTER TABLE `biblio` ADD INDEX `classification` (`classification`);
ALTER TABLE `biblio` ADD INDEX `biblio_flag_idx` (`opac_hide`, `promoted`);
ALTER TABLE `biblio` ADD INDEX `rda_idx` (`content_type_id`, `media_type_id`, `carrier_type_id`);
ALTER TABLE `biblio` ADD INDEX `uid` (`uid`);
ALTER TABLE `biblio` ADD FULLTEXT INDEX `title_ft_idx` (`title`, `series_title`);
ALTER TABLE `biblio` ADD FULLTEXT INDEX `notes_ft_idx` (`notes`);
ALTER TABLE `biblio` ADD FULLTEXT INDEX `labels` (`labels`);

-- Tabel biblio_attachment
ALTER TABLE `biblio_attachment` ADD INDEX `biblio_id` (`biblio_id`);
ALTER TABLE `biblio_attachment` ADD INDEX `file_id` (`file_id`);
ALTER TABLE `biblio_attachment` ADD INDEX `biblio_id_2` (`biblio_id`, `file_id`);

-- Tabel content
ALTER TABLE `content` ADD FULLTEXT INDEX `content_title` (`content_title`);
ALTER TABLE `content` ADD FULLTEXT INDEX `content_desc` (`content_desc`);

-- Tabel files
ALTER TABLE `files` ADD FULLTEXT INDEX `file_name` (`file_name`);
ALTER TABLE `files` ADD FULLTEXT INDEX `file_dir` (`file_dir`);

-- Tabel fines
ALTER TABLE `fines` ADD INDEX `member_id` (`member_id`);

-- Tabel holiday
ALTER TABLE `holiday` ADD UNIQUE INDEX `holiday_dayname` (`holiday_dayname`, `holiday_date`);

-- Tabel item
ALTER TABLE `item` ADD UNIQUE INDEX `item_code` (`item_code`);
ALTER TABLE `item` ADD INDEX `uid` (`uid`);
ALTER TABLE `item` ADD INDEX `item_references_idx` (`coll_type_id`, `location_id`, `item_status_id`);
ALTER TABLE `item` ADD INDEX `biblio_id_idx` (`biblio_id`);

-- Tabel kardex
ALTER TABLE `kardex` ADD INDEX `fk_serial` (`serial_id`);

-- Tabel loan
ALTER TABLE `loan` ADD INDEX `item_code` (`item_code`);
ALTER TABLE `loan` ADD INDEX `member_id` (`member_id`);
ALTER TABLE `loan` ADD INDEX `input_date` (`input_date`, `last_update`, `uid`);

-- Tabel member
ALTER TABLE `member` ADD INDEX `member_name` (`member_name`);
ALTER TABLE `member` ADD INDEX `member_type_id` (`member_type_id`);

-- Tabel mst_author
ALTER TABLE `mst_author` ADD UNIQUE INDEX `author_name` (`author_name`, `authority_type`);

-- Tabel mst_coll_type
ALTER TABLE `mst_coll_type` ADD UNIQUE INDEX `coll_type_name` (`coll_type_name`);

-- Tabel mst_gmd
ALTER TABLE `mst_gmd` ADD UNIQUE INDEX `gmd_name` (`gmd_name`);
ALTER TABLE `mst_gmd` ADD UNIQUE INDEX `gmd_code` (`gmd_code`);

-- Tabel mst_item_status
ALTER TABLE `mst_item_status` ADD UNIQUE INDEX `item_status_name` (`item_status_name`);

-- Tabel mst_label
ALTER TABLE `mst_label` ADD UNIQUE INDEX `label_name` (`label_name`);

-- Tabel mst_language
ALTER TABLE `mst_language` ADD UNIQUE INDEX `language_name` (`language_name`);

-- Tabel mst_location
ALTER TABLE `mst_location` ADD UNIQUE INDEX `location_name` (`location_name`);

-- Tabel mst_member_type
ALTER TABLE `mst_member_type` ADD UNIQUE INDEX `member_type_name` (`member_type_name`);

-- Tabel mst_module
ALTER TABLE `mst_module` ADD UNIQUE INDEX `module_name` (`module_name`, `module_path`);

-- Tabel mst_place
ALTER TABLE `mst_place` ADD UNIQUE INDEX `place_name` (`place_name`);

-- Tabel mst_publisher
ALTER TABLE `mst_publisher` ADD UNIQUE INDEX `publisher_name` (`publisher_name`);

-- Tabel mst_supplier
ALTER TABLE `mst_supplier` ADD UNIQUE INDEX `supplier_name` (`supplier_name`);

-- Tabel mst_topic
ALTER TABLE `mst_topic` ADD UNIQUE INDEX `topic` (`topic`, `topic_type`);

-- Tabel reserve
ALTER TABLE `reserve` ADD INDEX `references_idx` (`member_id`, `biblio_id`);
ALTER TABLE `reserve` ADD INDEX `item_code_idx` (`item_code`);

-- Tabel serial
ALTER TABLE `serial` ADD INDEX `fk_serial_biblio` (`biblio_id`);
ALTER TABLE `serial` ADD INDEX `fk_serial_gmd` (`gmd_id`);

-- Tabel setting
ALTER TABLE `setting` ADD UNIQUE INDEX `setting_name` (`setting_name`);

-- Tabel stock_take_item
ALTER TABLE `stock_take_item` ADD UNIQUE INDEX `item_code` (`item_code`);
ALTER TABLE `stock_take_item` ADD INDEX `status` (`status`);
ALTER TABLE `stock_take_item` ADD INDEX `item_properties_idx` (`gmd_name`, `classification`, `coll_type_name`, `location`);

-- Tabel system_log
ALTER TABLE `system_log` ADD INDEX `log_type` (`log_type`);
ALTER TABLE `system_log` ADD INDEX `id` (`id`);

-- Tabel user
ALTER TABLE `user` ADD UNIQUE INDEX `username` (`username`);
ALTER TABLE `user` ADD INDEX `realname` (`realname`);

-- Tabel user_group
ALTER TABLE `user_group` ADD UNIQUE INDEX `group_name` (`group_name`);

-- Tabel visitor_count
ALTER TABLE `visitor_count` ADD INDEX `member_id` (`member_id`);

-- Tabel mst_carrier_type
ALTER TABLE `mst_carrier_type` ADD UNIQUE INDEX `media_type` (`carrier_type`);
ALTER TABLE `mst_carrier_type` ADD INDEX `code` (`code`);

-- Tabel mst_content_type
ALTER TABLE `mst_content_type` ADD UNIQUE INDEX `content_type` (`content_type`);
ALTER TABLE `mst_content_type` ADD INDEX `code` (`code`);

-- Tabel mst_media_type
ALTER TABLE `mst_media_type` ADD UNIQUE INDEX `media_type` (`media_type`);
ALTER TABLE `mst_media_type` ADD INDEX `code` (`code`);

-- Tabel biblio_log
ALTER TABLE `biblio_log` ADD INDEX `realname` (`realname`);
ALTER TABLE `biblio_log` ADD INDEX `biblio_id` (`biblio_id`);
ALTER TABLE `biblio_log` ADD INDEX `user_id` (`user_id`);
ALTER TABLE `biblio_log` ADD INDEX `ip` (`ip`);
ALTER TABLE `biblio_log` ADD INDEX `action` (`action`);
ALTER TABLE `biblio_log` ADD INDEX `affectedrow` (`affectedrow`);
ALTER TABLE `biblio_log` ADD INDEX `date` (`date`);
ALTER TABLE `biblio_log` ADD FULLTEXT INDEX `title` (`title`);
ALTER TABLE `biblio_log` ADD FULLTEXT INDEX `rawdata` (`rawdata`);
ALTER TABLE `biblio_log` ADD FULLTEXT INDEX `additional_information` (`additional_information`);

-- Tabel loan_history
ALTER TABLE `loan_history` ADD INDEX `member_name` (`member_name`);

-- Tabel search_biblio
ALTER TABLE `search_biblio` ADD UNIQUE INDEX `biblio_id` (`biblio_id`);
ALTER TABLE `search_biblio` ADD INDEX `add_indexes` (`gmd`, `publisher`, `publish_place`, `language`, `classification`, `publish_year`, `call_number`);
ALTER TABLE `search_biblio` ADD INDEX `add_indexes2` (`opac_hide`, `promoted`);
ALTER TABLE `search_biblio` ADD INDEX `rda_indexes` (`carrier_type`, `media_type`, `content_type`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `title` (`title`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `author` (`author`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `topic` (`topic`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `location` (`location`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `items` (`items`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `collection_types` (`collection_types`);
ALTER TABLE `search_biblio` ADD FULLTEXT INDEX `labels` (`labels`);

-- Tabel index_documents
ALTER TABLE `index_documents` ADD INDEX `document_id` (`document_id`);
ALTER TABLE `index_documents` ADD INDEX `word_id` (`word_id`);
ALTER TABLE `index_documents` ADD INDEX `location` (`location`);

-- =====================================================
-- TRIGGER YANG HILANG
-- =====================================================

DROP TRIGGER IF EXISTS `delete_loan_history`;
DELIMITER //
CREATE TRIGGER `delete_loan_history` AFTER DELETE ON `loan`
FOR EACH ROW DELETE FROM loan_history WHERE loan_id=OLD.loan_id//
DELIMITER ;

DROP TRIGGER IF EXISTS `update_loan_history`;
DELIMITER //
CREATE TRIGGER `update_loan_history` AFTER UPDATE ON `loan`
FOR EACH ROW UPDATE loan_history 
SET is_lent=NEW.is_lent,
is_return=NEW.is_return,
renewed=NEW.renewed,
return_date=NEW.return_date
WHERE loan_id=NEW.loan_id//
DELIMITER ;

DROP TRIGGER IF EXISTS `insert_loan_history`;
DELIMITER //
CREATE TRIGGER `insert_loan_history` AFTER INSERT ON `loan`
FOR EACH ROW INSERT INTO loan_history
SET loan_id=NEW.loan_id,
item_code=NEW.item_code,
member_id=NEW.member_id,
loan_date=NEW.loan_date,
due_date=NEW.due_date,
renewed=NEW.renewed,
is_lent=NEW.is_lent,
is_return=NEW.is_return,
return_date=NEW.return_date,
input_date=NEW.input_date,
last_update=NEW.last_update,
title=(SELECT b.title FROM biblio b LEFT JOIN item i ON i.biblio_id=b.biblio_id WHERE i.item_code=NEW.item_code),
biblio_id=(SELECT b.biblio_id FROM biblio b LEFT JOIN item i ON i.biblio_id=b.biblio_id WHERE i.item_code=NEW.item_code),
call_number=(SELECT IF(i.call_number IS NULL, b.call_number,i.call_number) FROM biblio b LEFT JOIN item i ON i.biblio_id=b.biblio_id WHERE i.item_code=NEW.item_code),
classification=(SELECT b.classification FROM biblio b LEFT JOIN item i ON i.biblio_id=b.biblio_id WHERE i.item_code=NEW.item_code),
gmd_name=(SELECT g.gmd_name FROM biblio b LEFT JOIN item i ON i.biblio_id=b.biblio_id LEFT JOIN mst_gmd g ON g.gmd_id=b.gmd_id WHERE i.item_code=NEW.item_code),
language_name=(SELECT l.language_name FROM biblio b LEFT JOIN item i ON i.biblio_id=b.biblio_id LEFT JOIN mst_language l ON b.language_id=l.language_id WHERE i.item_code=NEW.item_code),
location_name=(SELECT ml.location_name FROM item i LEFT JOIN mst_location ml ON i.location_id=ml.location_id WHERE i.item_code=NEW.item_code),
collection_type_name=(SELECT mct.coll_type_name FROM mst_coll_type mct LEFT JOIN item i ON i.coll_type_id=mct.coll_type_id WHERE i.item_code=NEW.item_code),
member_name=(SELECT m.member_name FROM member m WHERE m.member_id=NEW.member_id),
member_type_name=(SELECT mmt.member_type_name FROM mst_member_type mmt LEFT JOIN member m ON m.member_type_id=mmt.member_type_id WHERE m.member_id=NEW.member_id)//
DELIMITER ;