Kevin work - HackMD

--- tag：Kevin Homework --- # Kevin work ## target(專案目標) 1. 需可用以下資料登入系統，登入後將能更改密碼 account： (1) 帳:Regan 密:plod123 (2) 帳:Carter 密:fuzz43 2. 需可使用人名或駕照號碼進行搜索，搜尋不到需要顯示message。人名要能接受大小寫或是部分姓名 3. 需可使用車牌號碼進行搜索，系統將顯示汽車的詳細信息（例如類型，顏色等），車主的姓名和駕照號。能允許系統缺少數據（例如，車輛可能不在系統中，或者車輛可能在系統中，但所有者可能未知）。 4. 可以新增新車的詳細訊息(包含車輛的license number、make, model, colour of the vehicle, owner) 如果擁有者已在系統中，將可直接選擇該傭有者，如不再系統中，將能新增個人資料，包含license number 5. 需能新增事件功能(需輸入紀錄文字說明、車輛、發生時間、所涉及人員)。如人員或車輛不再系統中，需有功能進行新增。罪刑將能從現有系統中加入 (不用顯示罰款，罰款應由法院發布，跟警方無關) 6. administrator權限管理功能 admin account: haskins/copper99 功能：可以新增新的警察帳號可以對資料庫新增罰款， ## 目前DB架構(2021/1/4 01:26更新) ![](https://i.imgur.com/uo4cJDq.png) ![](https://i.imgur.com/jWUbDTL.png) ![](https://i.imgur.com/68tF08U.png) ## 簡易架構理解圖 ![](https://i.imgur.com/QPzEHhH.jpg) ## 專案日誌紀錄 2021/1/1 - Techbeck 建置LAB環境(XAMPP) 建置DB架構(執行Kevin提供的SQL) 2021/1/2 - Techbeck 創線上筆記(https://hackmd.io/@6AhJdzpGTyGDlAxru4jZbA/rykqJw6pv) 訂定專案目標(作業得分點) ## DB Create data(2021/1/4 01:22更新) ```CREATE TABLE account ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Username VARCHAR(50) NOT NULL, Password VARCHAR(50) NOT NULL, Type VARCHAR(50) NOT NULL, Createdate datetime Not null default CURRENT_TIMESTAMP , Updatedate datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); INSERT INTO Account (uuid,Username, Password, Type) VALUES (uuid_short(),‘Regan’, ‘plod123’, ‘Officer’), (uuid_short(),‘Carter’, ‘fuzz43’, ‘Officer’), (uuid_short(),‘haskins’, ‘copper99’, ‘Administrator’); DROP TABLE IF EXISTS account; CREATE TABLE account ( `uuid` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `Username` VARCHAR(50) NOT NULL, `Password` VARCHAR(50) NOT NULL, `Type` VARCHAR(50) NOT NULL ); ``` ### 因應學校的mysql版本老舊(version:5.5.60-MariaDB)，create table的時候不能直接使用CURRENT_TIMESTAMP，故請執行以下sql自行新增trigger: (2021/01/06更新) ``` //查看版本 select version(); ``` ``` //開始 DROP TABLE IF EXISTS account; CREATE TABLE account ( `uuid` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `Username` VARCHAR(50) NOT NULL, `Password` VARCHAR(50) NOT NULL, `Type` VARCHAR(50) NOT NULL ); ALTER TABLE `account` ADD `Createdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; ALTER TABLE `account` ADD `Updatedate` DATETIME NULL; DROP TRIGGER IF EXISTS account_Updatedate_CURRENT_TIMESTAMP; DELIMITER // CREATE TRIGGER account_update_CURRENT_TIMESTAMP BEFORE INSERT ON account FOR EACH ROW BEGIN IF (NEW.Updatedate IS NULL) THEN -- change the isnull check for the default used SET NEW.Updatedate = now(); END IF; END// DELIMITER ; DELIMITER ; CREATE TRIGGER `account_uuid_before_insert` BEFORE INSERT ON `account` FOR EACH ROW BEGIN IF new.uuid IS NULL THEN SET new.uuid = uuid(); END IF; END;; DELIMITER ; INSERT INTO account (Username, Password, Type) VALUES ('Regan', 'plod123', 'Officer'); select * from account; //結束 ``` 解決mariadb 5.5 uuid bug的替代方案 https://stackoverflow.com/questions/9750536/mysql-uuid-duplication-bug/9750591 ``` DELIMITER // CREATE FUNCTION uuid_v4() RETURNS CHAR(36) BEGIN -- Generate 8 2-byte strings that we will combine into a UUIDv4 SET @h1 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'); SET @h2 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'); SET @h3 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'); SET @h6 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'); SET @h7 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'); SET @h8 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'); -- 4th section will start with a 4 indicating the version SET @h4 = CONCAT('4', LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0')); -- 5th section first half-byte can only be 8, 9 A or B SET @h5 = CONCAT(HEX(FLOOR(RAND() * 4 + 8)), LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0')); -- Build the complete UUID RETURN LOWER(CONCAT( @h1, @h2, '-', @h3, '-', @h4, '-', @h5, '-', @h6, @h7, @h8 )); END // -- Switch back the delimiter DELIMITER ; /*改良*/ DROP FUNCTION IF EXISTS uuid_v4; DELIMITER // CREATE FUNCTION uuid_v4() RETURNS CHAR(36) BEGIN -- Generate 8 2-byte strings that we will combine into a UUIDv4 SET @h1 = LPAD(FLOOR(RAND() * 0xffff), 2, '0'); SET @h2 = LPAD(FLOOR(RAND() * 0xffff), 2, '0'); SET @h3 = LPAD(FLOOR(RAND() * 0xffff), 2, '0'); SET @h4 = LPAD(FLOOR(RAND() * 0xffff), 2, '0'); -- Build the complete UUID RETURN LOWER(CONCAT( @h1, @h2, @h3, @h4 )); END // -- Switch back the delimiter DELIMITER ; DROP TRIGGER IF EXISTS account_insert_data; DELIMITER // CREATE TRIGGER account_insert_data BEFORE INSERT ON account FOR EACH ROW BEGIN IF (NEW.Updatedate IS NULL) THEN -- change the isnull check for the default used SET NEW.Updatedate = now(); END IF; IF (NEW.uuid = 0 ) THEN SET NEW.uuid = uuid_v4(); END IF; END// DELIMITER ; DROP FUNCTION IF EXISTS uuid_v4; DELIMITER // CREATE FUNCTION uuid_v4() RETURNS CHAR(36) BEGIN -- Generate 8 2-byte strings that we will combine into a UUIDv4 SET @h1 = LPAD(FLOOR(RAND() * 0xffff), 4, '0'); SET @h2 = LPAD(FLOOR(RAND() * 0xffff), 4, '0'); SET @h3 = LPAD(FLOOR(RAND() * 0xffff), 4, '0'); SET @h6 = LPAD(FLOOR(RAND() * 0xffff), 4, '0'); SET @h7 = LPAD(FLOOR(RAND() * 0xffff), 4, '0'); SET @h8 = LPAD(FLOOR(RAND() * 0xffff), 4, '0'); -- 4th section will start with a 4 indicating the version SET @h4 = CONCAT('4', LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0')); -- 5th section first half-byte can only be 8, 9 A or B SET @h5 = CONCAT(HEX(FLOOR(RAND() * 4 + 8)), LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0')); -- Build the complete UUID RETURN LOWER(CONCAT( @h1, @h2, '-', @h3, '-', @h4, '-', @h5, '-', @h6, @h7, @h8 )); END // -- Switch back the delimiter DELIMITER ; ``` ## PHP參考資料 ### php連接DB >https://twosheng.com/php%E6%95%99%E5%AD%B8-3%E8%88%87mysql%E8%B3%87%E6%96%99%E5%BA%AB%E4%BA%92%E5%8B%95/ ### login頁面code > https://www.tutorialspoint.com/php/php_mysql_login.htm ### login web page sample https://www.tutorialspoint.com/php/php_login_example.htm ## CSS參考資料 ### CSS修改後未生效 >https://zh-hant.hotbak.net/key/%E9%97%9C%E6%96%BC%E4%BD%BF%E7%94%A8%E6%9C%8D%E5%8B%99%E5%99%A8%E6%89%93%E9%96%8Bhtml%E9%A0%81%E9%9D%A2%E4%BF%AE%E6%94%B9%E5%A4%96%E8%81%AFcss%E6%96%87%E4%BB%B6%E4%B8%8D%E9%A1%AF%E7%A4%BA%E6%A8%A3CSDN%E5%8D%9A%E5%AE%A2.html ## Github > https://github.com/techbeck015413/kevinwork ## schedule (2021/1/4 01:45更新) 1/4 (一) 功能一 1/5 (二) 功能二 1/6 (三) 功能二 1/7 (四) 功能三 1/8 (五) 功能三 1/9 (六) 功能四 1/10 (日) 功能四 1/11 (一) 功能五 1/12 (二) 功能五 1/13 (三) 功能六 1/14 (四)撰寫說明pdf ## Kevin test a. Write Unix commands to do the following: i. Send a file report.doc to the printer named splodge; \# lp -d splodge -n 1 report.doc https://askubuntu.com/questions/432746/print-from-command-line http://linux.vbird.org/linux_basic/0610hardware/0610hardware-centos5.php#lp ii. Show the contents of the text file new.log; cat new.log iii. Delete the file named temp; \# rm temp https://linuxize.com/post/how-to-remove-files-and-directories-using-linux-command-line/ iv. Overwrite the contents of project.txt with project2.txt; \# cp project.txt project2.txt https://www.ewdna.com/2012/05/linux-cp-force-overwrite.html v. Count the number of lines in a listing of the current directory. \# ls | wc -l https://devconnected.com/how-to-count-files-in-directory-on-linux/ b. What do the following Unix commands do? i. grep “^John.*Smith” myFile.txt 使用grep指令在myFile.txt檔案中，搜尋以"John.*Smith"為字首的字串(string) (*代表任意字元) https://bluelove1968.pixnet.net/blog/post/222276865 ii. chmod go-w myFile.txt 變更檔案所屬的群組 https://ithelp.ithome.com.tw/articles/10197769 iii. grep –i pszabc myFile.txt 在檔案myFile.txt搜尋字串"pszabc"(參數-i代表不分大小寫) https://blog.gtwang.org/linux/linux-grep-command-tutorial-examples/ iv. ls mydir > temp 2>&1 將執行$ls mydir的結果，透過">"command輸出到名為temp檔案內 (2>&1為重定向，作用是錯誤輸出將會和標準輸出輸出到同一個地方) https://codertw.com/%E5%89%8D%E7%AB%AF%E9%96%8B%E7%99%BC/392548/ v. grep “pszabc\*” myFile.txt 在檔案myFile.txt中搜尋字串"pszabc\*"("\*"在這裡不是代表任意字元，而是"*"這個字元的原始意義) https://kknews.cc/zh-tw/news/k842gqr.html c. Give two methods that you could use to show processes running on a Unix machine. (1) $ ps -aux (2) $ top https://www.cyberciti.biz/faq/how-to-check-running-process-in-linux-using-command-line/ d. What is an inode? Draw a diagram to illustrate the typical contents you would find in an inode. (1) An inode is a file data structure that stores information about any Linux file except its name and data. (2) ![](https://i.imgur.com/wfF68EB.png) https://www.bluematador.com/blog/what-is-an-inode-and-what-are-they-used-for e. What information would you expect to find in an NFS file handle and how might this relate to inodes? (設定檔路徑：/etc/exports) (1) 分享目錄、限制的主機 (權限)、可用主機名 (2) with "mount" command https://kb.iu.edu/d/adux https://ithelp.ithome.com.tw/articles/10078793 f. NIS is a well-known directory service used on Unix systems. i. Explain what is meant by a directory service; 專門管理帳號與密碼的伺服器服務 NIS (Network Information System) is a network naming and administration system for smaller networks http://linux.vbird.org/linux_server/0430nis.php#whatisnis_use https://searchnetworking.techtarget.com/definition/NIS ii. Give three examples of information managed by NIS. (1) # useradd -u 1001 nisuser1(在NIS Server上新增帳號) (2) # ypwhich -x (在NIS Client上檢驗NIS的資料庫) (3) # ypcat passwd.byname (在NIS Client上讀取NIS的資料庫內容) http://linux.vbird.org/linux_server/0430nis.php#nis_client_user