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

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

mysql -h remote_host -u lib_data_user -Ap lib_data -p

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 ;