OpenPortalServer-SSH
/
OpenPortalServer.sql

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.0.22-community-nt : Database - openportalserver
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`openportalserver` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `openportalserver`;

/*Table structure for table `portal_account` */

DROP TABLE IF EXISTS `portal_account`;

CREATE TABLE `portal_account` (
  `id` bigint(20) NOT NULL auto_increment,
  `loginName` varchar(255) default NULL,
  `password` varchar(255) default NULL,
  `name` varchar(255) default NULL,
  `gender` varchar(255) default NULL,
  `phoneNumber` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  `date` datetime default NULL,
  `time` bigint(20) default NULL,
  `state` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_account` */

/*Table structure for table `portal_card` */

DROP TABLE IF EXISTS `portal_card`;

CREATE TABLE `portal_card` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  `payTime` bigint(20) default NULL,
  `payType` varchar(255) default NULL,
  `state` varchar(255) default NULL,
  `cdKey` varchar(255) default NULL,
  `categoryType` varchar(255) default NULL,
  `accountName` varchar(255) default NULL,
  `accountId` bigint(20) default NULL,
  `payDate` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_card` */

/*Table structure for table `portal_cardcategory` */

DROP TABLE IF EXISTS `portal_cardcategory`;

CREATE TABLE `portal_cardcategory` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  `time` bigint(20) default NULL,
  `state` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_cardcategory` */

/*Table structure for table `portal_config` */

DROP TABLE IF EXISTS `portal_config`;

CREATE TABLE `portal_config` (
  `id` bigint(20) NOT NULL auto_increment,
  `bas_ip` varchar(255) default NULL,
  `bas_port` varchar(255) default NULL,
  `portalVer` varchar(255) default NULL,
  `authType` varchar(255) default NULL,
  `timeoutSec` varchar(255) default NULL,
  `sharedSecret` varchar(255) default NULL,
  `portal_port` varchar(255) default NULL,
  `bas` varchar(255) default NULL,
  `isdebug` varchar(255) default NULL,
  `verifyCode` varchar(255) default NULL,
  `userHeart` varchar(255) default NULL,
  `userHeartCount` varchar(255) default NULL,
  `userHeartTime` varchar(255) default NULL,
  `auth_interface` varchar(255) default NULL,
  `bas_user` varchar(255) default NULL,
  `bas_pwd` varchar(255) default NULL,
  `accountAdd` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_config` */

insert  into `portal_config`(`id`,`bas_ip`,`bas_port`,`portalVer`,`authType`,`timeoutSec`,`sharedSecret`,`portal_port`,`bas`,`isdebug`,`verifyCode`,`userHeart`,`userHeartCount`,`userHeartTime`,`auth_interface`,`bas_user`,`bas_pwd`,`accountAdd`) values (1,'192.168.2.100','2000','2','1','3','LeeSon','50100','0','1','0','1','3','10','1','LeeSon','LeeSon','1');

/*Table structure for table `portal_department` */

DROP TABLE IF EXISTS `portal_department`;

CREATE TABLE `portal_department` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  `parentId` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK86C8D505CD2E683F` (`parentId`),
  CONSTRAINT `FK86C8D505CD2E683F` FOREIGN KEY (`parentId`) REFERENCES `portal_department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_department` */

/*Table structure for table `portal_linkrecord` */

DROP TABLE IF EXISTS `portal_linkrecord`;

CREATE TABLE `portal_linkrecord` (
  `id` bigint(20) NOT NULL auto_increment,
  `ip` varchar(255) default NULL,
  `loginName` varchar(255) default NULL,
  `state` varchar(255) default NULL,
  `startDate` datetime default NULL,
  `endDate` datetime default NULL,
  `time` bigint(20) default NULL,
  `uid` bigint(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_linkrecord` */

/*Table structure for table `portal_logrecord` */

DROP TABLE IF EXISTS `portal_logrecord`;

CREATE TABLE `portal_logrecord` (
  `id` bigint(20) NOT NULL auto_increment,
  `info` varchar(255) default NULL,
  `rec_date` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_logrecord` */

/*Table structure for table `portal_message` */

DROP TABLE IF EXISTS `portal_message`;

CREATE TABLE `portal_message` (
  `id` bigint(20) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  `date` datetime default NULL,
  `state` varchar(255) default NULL,
  `fromid` varchar(255) default NULL,
  `toid` varchar(255) default NULL,
  `ip` varchar(255) default NULL,
  `toname` varchar(255) default NULL,
  `fromname` varchar(255) default NULL,
  `delin` varchar(255) default NULL,
  `delout` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_message` */

/*Table structure for table `portal_privilege` */

DROP TABLE IF EXISTS `portal_privilege`;

CREATE TABLE `portal_privilege` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `position` int(11) default NULL,
  `parentId` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK521DE29EFB45234E` (`parentId`),
  CONSTRAINT `FK521DE29EFB45234E` FOREIGN KEY (`parentId`) REFERENCES `portal_privilege` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_privilege` */

insert  into `portal_privilege`(`id`,`name`,`url`,`position`,`parentId`) values (1,'系统管理',NULL,1,NULL),(2,'角色管理','/role_list',11,1),(3,'分类管理','/department_list',12,1),(4,'用户管理','/user_list',13,1),(5,'权限管理','/privilege_list',14,1),(6,'对接设置','/config_show',15,1),(7,'角色列表','/role_list',111,2),(8,'角色删除','/role_delete',112,2),(9,'角色添加','/role_add',113,2),(10,'角色修改','/role_edit',114,2),(11,'角色权限设置','/role_setPrivilege',115,2),(12,'分类列表','/department_list',121,3),(13,'分类删除','/department_delete',122,3),(14,'分类添加','/department_add',123,3),(15,'分类修改','/department_edit',124,3),(16,'用户列表','/user_list',131,4),(17,'用户删除','/user_delete',132,4),(18,'用户添加','/user_add',133,4),(19,'用户修改','/user_edit',134,4),(20,'初始化密码','/user_initPassword',135,4),(21,'权限列表','/privilege_list',141,5),(22,'权限删除','/privilege_delete',142,5),(23,'权限添加','/privilege_add',143,5),(24,'权限修改','/privilege_edit',144,5),(25,'权限上移','/privilege_moveUp',145,5),(26,'权限下移','/privilege_moveDown',146,5),(27,'对接设置信息','/config_show',151,6),(28,'对接设置保存','/config_saveConfig',152,6),(29,'接入账户管理',NULL,2,NULL),(30,'账户列表','/account_list',21,29),(31,'账户添加','/account_addUI',22,29),(32,'账户导入','/account_in',23,29),(33,'账户导出','/account_out',24,29),(34,'连接记录','/linkRecord_list',25,29),(35,'在线列表','/linkRecord_listOnline',26,29),(36,'账户列表','/account_list',211,30),(37,'账户删除','/account_delete',212,30),(38,'账户添加','/account_add',213,30),(39,'账户修改','/account_edit',214,30),(40,'账户充值','/account_pay',215,30),(41,'连接记录','/linkRecord_list',216,30),(42,'连接记录列表','/linkRecord_list',251,34),(43,'连接记录删除','/linkRecord_delete',252,34),(44,'用户下线','/linkRecord_kick',261,35),(45,'充值卡管理',NULL,3,NULL),(46,'充值卡分类','/cardCategory_list',31,45),(47,'充值卡列表','/card_list',32,45),(48,'充值卡添加','/card_addUI',33,45),(49,'分类列表','/cardCategory_list',311,46),(50,'分类删除','/cardCategory_delete',312,46),(51,'分类添加','/cardCategory_add',313,46),(52,'分类修改','/cardCategory_edit',314,46),(53,'充值卡列表','/card_list',321,47),(54,'充值卡删除','/card_delete',322,47),(55,'充值卡添加','/card_add',323,47),(56,'充值卡修改','/card_edit',324,47),(57,'消息管理',NULL,4,NULL),(58,'收件箱','/message_listIn',41,57),(59,'发件箱','/message_listOut',42,57),(60,'发送消息','/message_addUI',43,57),(61,'消息列表','/message_listIn',411,58),(62,'消息删除','/message_deleteIn',412,58),(63,'发送消息','/message_add',413,58),(64,'修改状态','/message_edit',414,58),(65,'消息列表','/message_listOut',421,59),(66,'消息删除','/message_deleteOut',422,59),(67,'发送消息','/message_add',423,59);

/*Table structure for table `portal_role` */

DROP TABLE IF EXISTS `portal_role`;

CREATE TABLE `portal_role` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_role` */

/*Table structure for table `portal_role_privilege` */

DROP TABLE IF EXISTS `portal_role_privilege`;

CREATE TABLE `portal_role_privilege` (
  `privilegeId` bigint(20) NOT NULL,
  `roleId` bigint(20) NOT NULL,
  PRIMARY KEY  (`roleId`,`privilegeId`),
  KEY `FKF260565BC451AB95` (`privilegeId`),
  KEY `FKF260565B639B82F` (`roleId`),
  CONSTRAINT `FKF260565B639B82F` FOREIGN KEY (`roleId`) REFERENCES `portal_role` (`id`),
  CONSTRAINT `FKF260565BC451AB95` FOREIGN KEY (`privilegeId`) REFERENCES `portal_privilege` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_role_privilege` */

/*Table structure for table `portal_user` */

DROP TABLE IF EXISTS `portal_user`;

CREATE TABLE `portal_user` (
  `id` bigint(20) NOT NULL auto_increment,
  `loginName` varchar(255) default NULL,
  `password` varchar(255) default NULL,
  `name` varchar(255) default NULL,
  `gender` varchar(255) default NULL,
  `phoneNumber` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `description` varchar(255) default NULL,
  `departmentId` bigint(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FKF1617FBE4F2D98E7` (`departmentId`),
  CONSTRAINT `FKF1617FBE4F2D98E7` FOREIGN KEY (`departmentId`) REFERENCES `portal_department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_user` */

insert  into `portal_user`(`id`,`loginName`,`password`,`name`,`gender`,`phoneNumber`,`email`,`description`,`departmentId`) values (1,'admin','21232f297a57a5a743894a0e4a801fc3','超级管理员',NULL,NULL,NULL,NULL,NULL);

/*Table structure for table `portal_user_role` */

DROP TABLE IF EXISTS `portal_user_role`;

CREATE TABLE `portal_user_role` (
  `roleId` bigint(20) NOT NULL,
  `userId` bigint(20) NOT NULL,
  PRIMARY KEY  (`userId`,`roleId`),
  KEY `FKC45EE057639B82F` (`roleId`),
  KEY `FKC45EE057B8F0D99` (`userId`),
  CONSTRAINT `FKC45EE057B8F0D99` FOREIGN KEY (`userId`) REFERENCES `portal_user` (`id`),
  CONSTRAINT `FKC45EE057639B82F` FOREIGN KEY (`roleId`) REFERENCES `portal_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `portal_user_role` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;