· 8 years ago · Feb 26, 2018, 07:58 PM
1--
2-- 資料庫: `volunteer`
3--
4CREATE DATABASE IF NOT EXISTS `volunteer` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
5USE `volunteer`;
6
7-- --------------------------------------------------------
8
9--
10-- 資料表çµæ§‹ `livearea`
11--
12
13DROP TABLE IF EXISTS `livearea`;
14CREATE TABLE IF NOT EXISTS `livearea` (
15 `id` int(8) NOT NULL AUTO_INCREMENT,
16 `room` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '房號',
17 `building` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä½ç½®',
18 `sex` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '使用性別',
19 `priority` int(8) DEFAULT '0' COMMENT 'ä½¿ç”¨å„ªå…ˆé †åº',
20 `maxsize` int(8) DEFAULT '0' COMMENT '容é‡',
21 `usegroup` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '建è°ä½¿ç”¨å¤§çµ„',
22 `usesubgroup` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '建è°ä½¿ç”¨å°çµ„',
23 `usesize` int(8) DEFAULT '0' COMMENT '使用ä¸å®¹é‡',
24 `remainsize` int(8) DEFAULT '0' COMMENT '剩餘容é‡',
25 `memo` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '備註',
26 PRIMARY KEY (`id`)
27) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
28
29
30-- --------------------------------------------------------
31
32--
33-- 資料表çµæ§‹ `member`
34--
35
36DROP TABLE IF EXISTS `member`;
37CREATE TABLE IF NOT EXISTS `member` (
38 `idx` int(8) NOT NULL AUTO_INCREMENT,
39 `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '姓å',
40 `account` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '帳號',
41 `password` varchar(80) COLLATE utf8_unicode_ci NOT NULL COMMENT '密碼',
42 `level` int(4) NOT NULL DEFAULT '1' COMMENT 'ç‰ç´š',
43 `auth` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '權é™è¡¨ roll-cere-stud-ext-sys',
44 `key` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'çç´š Keyword',
45 `group` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '大組',
46 `subgroup` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT 'å°çµ„',
47 `groupexpire` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '大組-éŽæœŸåªå‰©æŸ¥è©¢',
48 `expire` date NOT NULL DEFAULT '2018-03-02',
49 `expire1` date NOT NULL DEFAULT '2018-03-02',
50 `expire2` date NOT NULL DEFAULT '2018-03-02',
51 `expire3` date NOT NULL DEFAULT '2018-03-02',
52 `expire4` date NOT NULL DEFAULT '2018-03-02',
53 `expire5` date NOT NULL DEFAULT '2018-03-02',
54 `expire6` date NOT NULL DEFAULT '2018-03-02',
55 `expire7` date NOT NULL DEFAULT '2018-03-02',
56 `expire8` date NOT NULL DEFAULT '2018-03-02',
57 `expire9` date NOT NULL DEFAULT '2018-03-02',
58 `expire10` date NOT NULL DEFAULT '2018-03-02',
59 `expire11` date NOT NULL DEFAULT '2018-03-02',
60 `expire12` date NOT NULL DEFAULT '2018-03-02',
61 `expire13` date NOT NULL DEFAULT '2018-03-02',
62 `expire14` date NOT NULL DEFAULT '2018-03-02',
63 `expire15` date NOT NULL DEFAULT '2018-03-02',
64 `expire16` date NOT NULL DEFAULT '2018-03-02',
65 `expire17` date NOT NULL DEFAULT '2018-03-02',
66 `expire18` date NOT NULL DEFAULT '2018-03-02',
67 `expire19` date NOT NULL DEFAULT '2018-03-02',
68 `expire20` date NOT NULL DEFAULT '2018-03-02',
69 `memo` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '備註',
70 PRIMARY KEY (`idx`)
71) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
72
73--
74-- 資料表的匯出資料 `member`
75--
76
77INSERT INTO `member` (`idx`, `name`, `account`, `password`, `level`, `auth`, `key`, `group`, `subgroup`, `groupexpire`, `expire`, `expire1`, `expire2`, `expire3`, `expire4`, `expire5`, `expire6`, `expire7`, `expire8`, `expire9`, `expire10`, `expire11`, `expire12`, `expire13`, `expire14`, `expire15`, `expire16`, `expire17`, `expire18`, `expire19`, `expire20`, `memo`) VALUES
78 (1, 'root', 'root', PASSWORD('root'), 10, '11111111111111111111', '*', '********************', '********************', '********************', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', '2025-11-01', ''),
79 (2, '管ç†è€…', 'admin', PASSWORD('root'), 8, '11111111110000000000', '*', '********************', '********************', '********************', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2022-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', '2015-11-01', ''),
80 (3, '秘書大組助ç†', 'userA', PASSWORD('abc123'), 3, '11111100000000000000', '*', '---1----------------', '********************', '---1----------------', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', ''),
81 (4, '教育大組助ç†', 'userB', PASSWORD('abcd1234'), 3, '11111101000000000000', '*', '---2----------------', '********************', '---2----------------', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', ''),
82 (5, '庶務大組助ç†', 'userC', PASSWORD('123abc'), 3, '11110000000000000000', '*', '---3----------------', '********************', '---3----------------', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', ''),
83 (6, '總務大組助ç†', 'userD', PASSWORD('1234abcd'), 3, '11110000000000000000', '*', '---4----------------', '********************', '---4----------------', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', ''),
84 (7, '善法實è¸å¤§çµ„助ç†', 'userE', PASSWORD('1qaz'), 3, '11110000000000000000', '*', '---5----------------', '********************', '---5----------------', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '2018-01-25', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', ''),
85 (8, 'å°çµ„é•·', 'user', PASSWORD('user123'), 3, '11110000000000000000', '*', '--------------------', '********************', '--------------------', '2018-01-15', '2018-01-15', '2018-01-15', '2018-01-15', '2018-01-15', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', ''),
86 (9, 'å ±åå°çµ„', 'mgr', PASSWORD('mgr123456'), 3, '11110000000000000000', '*', '---*----------------', '********************', '---3----------------', '2018-03-20', '2018-03-20', '2018-03-20', '2018-03-20', '2018-03-20', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '1970-01-01', '');
87-- --------------------------------------------------------
88
89--
90-- 資料表çµæ§‹ `newyear_2018`
91--
92
93DROP TABLE IF EXISTS `newyear_2018`;
94CREATE TABLE IF NOT EXISTS `newyear_2018` (
95 `id` int(8) NOT NULL AUTO_INCREMENT,
96 `barcode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'å ±åˆ°æ¢ç¢¼',
97 `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '姓å',
98 `tel` varchar(20) COLLATE utf8_unicode_ci NOT NULL COMMENT '電話',
99 `sex` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '性別',
100 `age` int(8) DEFAULT '0' COMMENT '年齡',
101 `area` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'å€åŸŸ',
102 `classarea` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '教室',
103 `classroom` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'æ¯çç別',
104 `classroomid` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'æ¯çç別代碼',
105 `classother` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '其他身份',
106 `group` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '義工大組別',
107 `subgroup` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '義工å°çµ„別',
108 `join` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'åƒèˆ‡æ—¥',
109 `join1` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥1',
110 `join2` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥2',
111 `join3` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥3',
112 `join4` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥4',
113 `join5` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥5',
114 `join6` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥6',
115 `join7` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥7',
116 `join8` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥8',
117 `join9` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥9',
118 `joinx` int(4) DEFAULT '0' COMMENT 'åƒèˆ‡æ—¥x',
119 `live` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä½å®¿æ—¥',
120 `live1` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥1',
121 `live2` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥2',
122 `live3` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥3',
123 `live4` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥4',
124 `live5` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥5',
125 `live6` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥6',
126 `live7` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥7',
127 `live8` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥8',
128 `live9` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥9',
129 `livex` int(4) DEFAULT '0' COMMENT 'ä½å®¿æ—¥x',
130 `livewhere` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä½å®¿å€',
131 `liveroom` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä½å®¿æˆ¿',
132 `type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '義工類別',
133 `notify` int(4) DEFAULT '0' COMMENT '通知單發放',
134 `specialcase` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '身體特殊狀æ³',
135 `request` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä½å®¿ç‰¹æ®Šå¸Œæ±‚',
136 `trafficgo` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'æ車去',
137 `trafficback` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'æ車回',
138 `idcard` varchar(10) collate utf8_unicode_ci COMMENT '身份è‰å—號',
139 `biry` varchar(3) collate utf8_unicode_ci COMMENT '出生年',
140 `birm` varchar(2) collate utf8_unicode_ci COMMENT '出生月',
141 `bird` varchar(2) collate utf8_unicode_ci COMMENT '出生日',
142 `pay` int(4) DEFAULT '0' COMMENT '已交車資',
143 `trafficself` int(4) DEFAULT '0' COMMENT '自行往返',
144 `joinclean` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'åƒèˆ‡æ‰“掃日',
145 `trafficclean` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '打掃æ車',
146 `memo` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '備註',
147 `applydate` date DEFAULT '1970-01-01' COMMENT 'å ±å日期',
148 `applyby` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'å ±å者',
149 `checkin` varchar(10) COLLATE utf8_unicode_ci DEFAULT '0' COMMENT 'å ±åˆ°',
150 `checkin1` int(4) DEFAULT '0' COMMENT 'æ—¥1å ±åˆ°',
151 `checkin2` int(4) DEFAULT '0' COMMENT 'æ—¥2å ±åˆ°',
152 `checkin3` int(4) DEFAULT '0' COMMENT 'æ—¥3å ±åˆ°',
153 `checkin4` int(4) DEFAULT '0' COMMENT 'æ—¥4å ±åˆ°',
154 `checkin5` int(4) DEFAULT '0' COMMENT 'æ—¥5å ±åˆ°',
155 `checkin6` int(4) DEFAULT '0' COMMENT 'æ—¥6å ±åˆ°',
156 `checkin7` int(4) DEFAULT '0' COMMENT 'æ—¥7å ±åˆ°',
157 `checkin8` int(4) DEFAULT '0' COMMENT 'æ—¥8å ±åˆ°',
158 `checkin9` int(4) DEFAULT '0' COMMENT 'æ—¥9å ±åˆ°',
159 `checkinx1` int(4) DEFAULT '0' COMMENT 'æ—¥1æ‰“æŽƒå ±åˆ°',
160 `checkinx2` int(4) DEFAULT '0' COMMENT 'æ—¥2æ‰“æŽƒå ±åˆ°',
161 `duplication` int(4) DEFAULT '0' COMMENT 'é‡è¦†å ±å註記',
162 `invalidate` int(4) DEFAULT '0' COMMENT '無效資料註記',
163 PRIMARY KEY (`id`),
164 KEY `name` (`name`),
165 KEY `tel` (`tel`),
166 KEY `area` (`area`),
167 KEY `group` (`group`),
168 KEY `barcode` (`barcode`),
169 KEY `join2_join3_join4` (`join2`,`join3`,`join4`)
170) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
171-- --------------------------------------------------------
172
173--
174-- 資料表çµæ§‹ `param`
175--
176
177DROP TABLE IF EXISTS `param`;
178CREATE TABLE IF NOT EXISTS `param` (
179 `id` int(8) NOT NULL AUTO_INCREMENT,
180 `main` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '主',
181 `sub` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '副',
182 `valS` int(8) DEFAULT '0' COMMENT '值1',
183 `valR` int(8) DEFAULT '0' COMMENT '值2',
184 `reserve1` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä¿ç•™1',
185 `reserve2` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ä¿ç•™2',
186 `memo` varchar(80) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '備註',
187 PRIMARY KEY (`id`)
188) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
189
190--
191-- 資料表的匯出資料 `param`
192--
193INSERT INTO `param` (`id`, `main`, `sub`, `valS`, `valR`, `reserve1`, `reserve2`, `memo`) VALUES
194(1, '善法實è¸', '大組', 0, 0, '', '', ''),
195(2, '善法實è¸', '平安麵', 0, 0, '', '', ''),
196(3, '善法實è¸', 'ä¼æ¥å–„法', 0, 0, '', '', ''),
197(4, '善法實è¸', '美食展', 0, 0, '', '', ''),
198(5, '善法實è¸', '傳心å°çµ„', 0, 0, '', '', '');
199
200--
201-- 建立使用者 `vt_root` 僅能å°è³‡æ–™åº« volunteer 下所有物件æ“有權é™
202--
203-- CREATE USER IF NOT EXISTS 'vt_root'@'localhost' IDENTIFIED BY 'vt_root'TH GRANT OPTION;
204-- GRANT ALL PRIVILEGES ON `volunteer`.* TO 'vt_root'@'localhost' IDENT;
205GRANT ALL PRIVILEGES ON `volunteer`.* TO 'vt_root'@'localhost' WIIFIED BY 'vt_root' WITH GRANT OPTION;