הבדלים בין גרסאות בדף "אגרונט לייב באינטרנט"

גרסה מ־13:25, 24 בפברואר 2010

mysql -h 82.80.102.38 -u lib_data_user -Ap lib_data -ptdrui

CREATE TABLE `loans` (

 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `card_no` int(11) DEFAULT NULL,
 `copy_no` int(11) DEFAULT NULL,
 `date_borrowed` datetime NOT NULL,
 `date_returned` datetime DEFAULT NULL,
 `date_promise` datetime DEFAULT NULL,
 `ismanuy` tinyint(4) DEFAULT '0',
 PRIMARY KEY (`ID`),
 UNIQUE KEY `card_no_2` (`card_no`,`copy_no`,`date_borrowed`),
 KEY `card_no` (`card_no`),
 KEY `copy_no` (`copy_no`),
 KEY `date_borrowed` (`date_borrowed`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

ALTER TABLE Loans ADD PRIMARY KEY (ID), ADD UNIQUE KEY (card_no, copy_no, date_borrowed), ADD INDEX (card_no), ADD INDEX (copy_no), ADD INDEX(date_borrowed), MODIFY ID int NOT NULL AUTO_INCREMENT, MODIFY ismanuy tinyint(4) DEFAULT 0;

CREATE TABLE `loanshistory` (

 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `card_no` int(11) DEFAULT NULL,
 `copy_no` int(11) DEFAULT NULL,
 `date_borrowed` datetime NOT NULL,
 `date_returned` datetime DEFAULT NULL,
 `date_promise` datetime DEFAULT NULL,
 `ismanuy` int(11) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `card_no_2` (`card_no`,`copy_no`,`date_borrowed`),
 KEY `card_no` (`card_no`),
 KEY `copy_no` (`copy_no`),
 KEY `date_borrowed` (`date_borrowed`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ALTER TABLE LoansHistory ADD PRIMARY KEY (ID), ADD UNIQUE KEY (card_no, copy_no, date_borrowed), ADD INDEX (card_no), ADD INDEX (copy_no), ADD INDEX(date_borrowed);

CREATE TABLE `reservations` (

 `res_no` int(11) NOT NULL AUTO_INCREMENT,
 `reader_no` int(11) NOT NULL,
 `title_no` int(11) NOT NULL,
 `By_copy` int(11) DEFAULT NULL,
 `reserve_date` datetime DEFAULT NULL,
 `DeadLineDate` datetime DEFAULT NULL,
 `ExpireDate` datetime DEFAULT NULL,
 `SendEmail` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`res_no`),
 UNIQUE KEY `reader_no_2` (`reader_no`,`title_no`),
 KEY `title_no` (`title_no`),
 KEY `reader_no` (`reader_no`),
 KEY `reserve_date` (`reserve_date`)

) ENGINE=MyISAM AUTO_INCREMENT=10644 DEFAULT CHARSET=utf8

ALTER TABLE Reservations MODIFY COLUMN res_no INT NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (res_no), ADD UNIQUE KEY (reader_no, title_no), ADD INDEX (title_no), ADD INDEX (reader_no), ADD INDEX(reserve_date); SOME TRIGGERS: CREATE TRIGGER update_loan_copy_status_on_insert AFTER INSERT ON loans FOR EACH ROW UPDATE copies SET copies.status = 2 WHERE copies.copy_no = NEW.copy_no; CREATE TRIGGER update_loan_copy_status_on_delete AFTER DELETE ON loans FOR EACH ROW UPDATE copies SET copies.status = 1 WHERE copies.copy_no = OLD.copy_no; for the last trigger u'll need to change the delimiter (the sign that tells mysql to run the command; the default is ;): DELIMITER | CREATE TRIGGER update_reservation_time_on_insert BEFORE INSERT ON reservations FOR EACH ROW IF (NEW.reserve_date IS NULL) THEN SET NEW.reserve_date = NOW; END IF;| DELIMITER ;