/db/test_structure.sql

https://github.com/squarezw/tiexin · SQL · 363 lines · 335 code · 28 blank · 0 comment · 0 complexity · 0d4fed30058f86ac01381e105c098ff7 MD5 · raw file

  1. CREATE TABLE `access_logs` (
  2. `id` int(11) NOT NULL auto_increment,
  3. `member_id` int(11) default NULL,
  4. `created_at` datetime default NULL,
  5. `mobile` tinyint(1) default NULL,
  6. `locale` varchar(5) default NULL,
  7. `remote_ip` varchar(15) default NULL,
  8. `controller` varchar(256) default NULL,
  9. `action` varchar(256) default NULL,
  10. `method` varchar(10) default NULL,
  11. `user_agent` varchar(1024) default NULL,
  12. `referer` varchar(4096) default NULL,
  13. `request_uri` varchar(4096) default NULL,
  14. `response_status` varchar(20) default NULL,
  15. `browser` varchar(50) default NULL,
  16. `browser_version` varchar(50) default NULL,
  17. `from_robot` tinyint(1) default NULL,
  18. PRIMARY KEY (`id`)
  19. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  20. CREATE TABLE `areas` (
  21. `id` int(11) NOT NULL auto_increment,
  22. `city_id` int(11) NOT NULL,
  23. `name_en` varchar(60) default NULL,
  24. `name_zh_cn` varchar(60) default NULL,
  25. `nw_x` int(11) default NULL,
  26. `nw_y` int(11) default NULL,
  27. `width` int(11) default NULL,
  28. `height` int(11) default NULL,
  29. `center_x` int(11) default NULL,
  30. `center_y` int(11) default NULL,
  31. `visit_count` int(11) default '0',
  32. PRIMARY KEY (`id`)
  33. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
  34. CREATE TABLE `articles` (
  35. `id` int(11) NOT NULL auto_increment,
  36. `subject` varchar(3000) NOT NULL,
  37. `member_id` int(11) NOT NULL,
  38. `folder_id` int(11) default NULL,
  39. `created_at` datetime default NULL,
  40. `updated_at` datetime default NULL,
  41. `read_times` int(11) NOT NULL default '0',
  42. `comments_count` int(11) default '0',
  43. `hot_spot_id` int(11) default NULL,
  44. `brand_id` int(11) default NULL,
  45. `content` text,
  46. PRIMARY KEY (`id`)
  47. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  48. CREATE TABLE `blogs` (
  49. `id` int(11) NOT NULL auto_increment,
  50. `member_id` int(11) NOT NULL,
  51. `created_at` datetime default NULL,
  52. `articles_count` int(11) NOT NULL default '0',
  53. `name` varchar(90) NOT NULL,
  54. `simple_name` varchar(50) NOT NULL,
  55. PRIMARY KEY (`id`)
  56. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  57. CREATE TABLE `brands` (
  58. `id` int(11) NOT NULL auto_increment,
  59. `name_zh_cn` varchar(300) default NULL,
  60. `name_en` varchar(300) default NULL,
  61. `pic` varchar(255) default NULL,
  62. `intro` varchar(3000) default NULL,
  63. `created_at` datetime default NULL,
  64. `updated_at` datetime default NULL,
  65. `creator_id` int(11) default NULL,
  66. `home_page` varchar(1000) default NULL,
  67. `visit_count` int(11) default '0',
  68. `owner_id` int(11) default NULL,
  69. `hot_spot_category_id` int(11) default NULL,
  70. PRIMARY KEY (`id`)
  71. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  72. CREATE TABLE `bulletins` (
  73. `id` int(11) NOT NULL auto_increment,
  74. `title` varchar(90) NOT NULL,
  75. `language` varchar(10) NOT NULL default 'zh_cn',
  76. `content` text,
  77. `expire_date` date default NULL,
  78. `created_at` datetime default NULL,
  79. PRIMARY KEY (`id`)
  80. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  81. CREATE TABLE `cities` (
  82. `id` int(11) NOT NULL auto_increment,
  83. `name_en` varchar(180) default NULL,
  84. `name_zh_cn` varchar(180) default NULL,
  85. `width` int(11) default NULL,
  86. `height` int(11) default NULL,
  87. `x0` decimal(12,2) default NULL,
  88. `y0` decimal(12,2) default NULL,
  89. `start_point_x` int(11) default NULL,
  90. `start_point_y` int(11) default NULL,
  91. `created_at` datetime default NULL,
  92. `updated_at` datetime default NULL,
  93. PRIMARY KEY (`id`)
  94. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
  95. CREATE TABLE `cities_promotions` (
  96. `city_id` int(11) NOT NULL,
  97. `promotion_id` int(11) NOT NULL
  98. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  99. CREATE TABLE `comments` (
  100. `id` int(11) NOT NULL auto_increment,
  101. `commentable_type` varchar(255) NOT NULL,
  102. `commentable_id` int(11) NOT NULL,
  103. `member_id` int(11) NOT NULL,
  104. `created_at` datetime NOT NULL,
  105. `content` text,
  106. `status` int(11) default '1',
  107. `agree` int(11) default '0',
  108. `disagree` int(11) default '0',
  109. PRIMARY KEY (`id`)
  110. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  111. CREATE TABLE `folders` (
  112. `id` int(11) NOT NULL auto_increment,
  113. `name` varchar(90) NOT NULL,
  114. `member_id` int(11) NOT NULL,
  115. PRIMARY KEY (`id`)
  116. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  117. CREATE TABLE `hot_spot_access_logs` (
  118. `id` int(11) NOT NULL auto_increment,
  119. `member_id` int(11) default NULL,
  120. `hot_spot_id` int(11) NOT NULL,
  121. `created_at` datetime default NULL,
  122. `from_mobile` tinyint(1) default NULL,
  123. PRIMARY KEY (`id`)
  124. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  125. CREATE TABLE `hot_spot_categories` (
  126. `id` int(11) NOT NULL auto_increment,
  127. `name_en` varchar(60) default NULL,
  128. `name_zh_cn` varchar(60) default NULL,
  129. `parent_id` int(11) default NULL,
  130. `icon` varchar(255) default NULL,
  131. `icon_mime_type` varchar(255) default NULL,
  132. `icon_filesize` varchar(255) default NULL,
  133. `traffic_stop_type` int(11) default '0',
  134. `created_at` datetime default NULL,
  135. `updated_at` datetime default NULL,
  136. PRIMARY KEY (`id`)
  137. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
  138. CREATE TABLE `hot_spots` (
  139. `id` int(11) NOT NULL auto_increment,
  140. `city_id` int(11) NOT NULL,
  141. `hot_spot_category_id` int(11) default NULL,
  142. `name_en` varchar(180) default NULL,
  143. `name_zh_cn` varchar(180) default NULL,
  144. `address_en` varchar(180) default NULL,
  145. `address_zh_cn` varchar(180) default NULL,
  146. `x` int(11) default NULL,
  147. `y` int(11) default NULL,
  148. `phone_number` varchar(60) default NULL,
  149. `introduction_en` varchar(3000) default NULL,
  150. `introduction_zh_cn` varchar(3000) default NULL,
  151. `creator_id` int(11) NOT NULL,
  152. `approved` tinyint(1) default NULL,
  153. `created_at` datetime default NULL,
  154. `comments_count` int(11) default '0',
  155. `price_level` int(11) default NULL,
  156. `price_memo` varchar(600) default NULL,
  157. `parking_slot` int(11) default NULL,
  158. `container_id` int(11) default NULL,
  159. `operation_time_zh_cn` varchar(600) default NULL,
  160. `operation_time_en` varchar(600) default NULL,
  161. `home_page` varchar(450) default NULL,
  162. `ref_id` int(11) default NULL,
  163. `visit_count` int(11) default '0',
  164. `brand_id` int(11) default NULL,
  165. `owner_id` int(11) default NULL,
  166. PRIMARY KEY (`id`)
  167. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
  168. CREATE TABLE `layout_maps` (
  169. `id` int(11) NOT NULL auto_increment,
  170. `name_en` varchar(60) default NULL,
  171. `name_zh_cn` varchar(60) default NULL,
  172. `layoutable_type` varchar(255) NOT NULL,
  173. `layoutable_id` int(11) NOT NULL,
  174. `introduction_zh_cn` varchar(3000) default NULL,
  175. `introduction_en` varchar(3000) default NULL,
  176. PRIMARY KEY (`id`)
  177. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  178. CREATE TABLE `map_levels` (
  179. `id` int(11) NOT NULL auto_increment,
  180. `city_id` int(11) NOT NULL,
  181. `no` int(11) NOT NULL,
  182. `scale` float NOT NULL,
  183. `row` int(11) NOT NULL,
  184. `column` int(11) NOT NULL,
  185. PRIMARY KEY (`id`)
  186. ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;
  187. CREATE TABLE `members` (
  188. `id` int(11) NOT NULL auto_increment,
  189. `login_name` varchar(255) NOT NULL,
  190. `password_hash` varchar(255) NOT NULL,
  191. `mail` varchar(255) NOT NULL,
  192. `nick_name` varchar(255) default NULL,
  193. `created_at` datetime NOT NULL,
  194. `is_admin` tinyint(1) NOT NULL default '0',
  195. `confirmed` tinyint(1) default '0',
  196. `confirm_code` varchar(100) default NULL,
  197. `favorite_lang` varchar(5) default NULL,
  198. `logo` varchar(255) default NULL,
  199. `logo_mime_type` varchar(50) default NULL,
  200. `mobile_phone` varchar(20) default NULL,
  201. `city_for_mobile` int(11) default NULL,
  202. `locked_until` datetime default NULL,
  203. `experience` int(11) default '0',
  204. `credit` int(11) default '0',
  205. `is_merchant` tinyint(1) default '0',
  206. `inviter_id` int(11) default NULL,
  207. PRIMARY KEY (`id`)
  208. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
  209. CREATE TABLE `navi_photos` (
  210. `id` int(11) NOT NULL auto_increment,
  211. `owner_type` varchar(255) NOT NULL,
  212. `owner_id` int(11) NOT NULL,
  213. `subject` varchar(300) default NULL,
  214. `photo` varchar(255) default NULL,
  215. `uploader_id` int(11) NOT NULL,
  216. `created_at` datetime default NULL,
  217. PRIMARY KEY (`id`)
  218. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  219. CREATE TABLE `owner_applications` (
  220. `id` int(11) NOT NULL auto_increment,
  221. `target_type` varchar(30) NOT NULL,
  222. `target_id` int(11) NOT NULL,
  223. `member_id` int(11) NOT NULL,
  224. `status` int(11) NOT NULL default '0',
  225. `admin_id` int(11) default NULL,
  226. `created_at` datetime default NULL,
  227. `opinion` varchar(300) default NULL,
  228. PRIMARY KEY (`id`)
  229. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  230. CREATE TABLE `positions` (
  231. `id` int(11) NOT NULL auto_increment,
  232. `hot_spot_id` int(11) NOT NULL,
  233. `layout_map_id` int(11) NOT NULL,
  234. `x` float NOT NULL,
  235. `y` float NOT NULL,
  236. PRIMARY KEY (`id`)
  237. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  238. CREATE TABLE `products` (
  239. `id` int(11) NOT NULL auto_increment,
  240. `vendor_id` int(11) default NULL,
  241. `name_en` varchar(300) default NULL,
  242. `name_zh_cn` varchar(300) default NULL,
  243. `official` tinyint(1) NOT NULL default '0',
  244. `introduction_en` varchar(3000) default NULL,
  245. `introduction_zh_cn` varchar(3000) default NULL,
  246. `photo` varchar(255) default NULL,
  247. `creator_id` int(11) default NULL,
  248. `last_updater_id` int(11) default NULL,
  249. `created_at` datetime default NULL,
  250. `updated_at` datetime default NULL,
  251. `vendor_type` varchar(20) default NULL,
  252. `price` varchar(100) default NULL,
  253. PRIMARY KEY (`id`)
  254. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  255. CREATE TABLE `promotions` (
  256. `id` int(11) NOT NULL auto_increment,
  257. `vendor_type` varchar(20) default NULL,
  258. `vendor_id` int(11) default NULL,
  259. `member_id` int(11) default NULL,
  260. `summary_en` varchar(3000) NOT NULL,
  261. `summary_zh_cn` varchar(3000) NOT NULL,
  262. `content_en` text,
  263. `content_zh_cn` text,
  264. `begin_date` date default NULL,
  265. `end_date` date default NULL,
  266. `banner` varchar(500) default NULL,
  267. `created_at` datetime default NULL,
  268. `updated_at` datetime default NULL,
  269. `allcity` tinyint(1) default '0',
  270. PRIMARY KEY (`id`)
  271. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  272. CREATE TABLE `revises` (
  273. `id` int(11) NOT NULL auto_increment,
  274. `hot_spot_id` int(11) NOT NULL,
  275. `member_id` int(11) NOT NULL,
  276. `suggestion` varchar(2048) default NULL,
  277. `remark` varchar(2048) default NULL,
  278. `admin_id` int(11) default NULL,
  279. `processed_at` datetime default NULL,
  280. `created_at` datetime default NULL,
  281. `status` int(11) NOT NULL default '0',
  282. PRIMARY KEY (`id`)
  283. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  284. CREATE TABLE `schema_info` (
  285. `version` int(11) default NULL
  286. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  287. CREATE TABLE `taboo_words` (
  288. `id` int(11) NOT NULL auto_increment,
  289. `word` varchar(30) NOT NULL,
  290. `regexp` varchar(180) default NULL,
  291. `active` tinyint(1) default '1',
  292. PRIMARY KEY (`id`)
  293. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  294. CREATE TABLE `traffic_lines` (
  295. `id` int(11) NOT NULL auto_increment,
  296. `name_zh_cn` varchar(300) default NULL,
  297. `name_en` varchar(300) default NULL,
  298. `line_type` int(11) NOT NULL default '1',
  299. `introduction_zh_cn` varchar(3000) default NULL,
  300. `introduction_en` varchar(3000) default NULL,
  301. `city_id` int(11) default NULL,
  302. `fid` int(11) default NULL,
  303. `operation_time_zh_cn` varchar(600) default NULL,
  304. `operation_time_en` varchar(600) default NULL,
  305. PRIMARY KEY (`id`)
  306. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  307. CREATE TABLE `traffic_stops` (
  308. `id` int(11) NOT NULL auto_increment,
  309. `traffic_line_id` int(11) NOT NULL,
  310. `position` int(11) NOT NULL default '0',
  311. `hot_spot_id` int(11) NOT NULL,
  312. PRIMARY KEY (`id`)
  313. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  314. CREATE TABLE `votes` (
  315. `id` int(11) NOT NULL auto_increment,
  316. `member_id` int(11) NOT NULL,
  317. `target_type` varchar(30) NOT NULL,
  318. `target_id` int(11) NOT NULL,
  319. `vote` int(11) default NULL,
  320. `created_at` datetime default NULL,
  321. PRIMARY KEY (`id`)
  322. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  323. CREATE TABLE `zoom_levels` (
  324. `id` int(11) NOT NULL auto_increment,
  325. `layout_map_id` int(11) NOT NULL,
  326. `width` int(11) default NULL,
  327. `height` int(11) default NULL,
  328. `scale` int(11) NOT NULL default '1',
  329. `map_file` varchar(255) default NULL,
  330. `map_file_width` int(11) default NULL,
  331. `map_file_height` int(11) default NULL,
  332. `created_at` datetime default NULL,
  333. PRIMARY KEY (`id`)
  334. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  335. INSERT INTO schema_info (version) VALUES (63)