前言
Visor（http://www.visor.com.cn) 是一个基于HTML5 Canvas 开发的IDE 框架和设计开发平台，有关Visor的设计架构和技术应用，在以后的文章里会逐渐跟大家分享，今天主要是分享一下它的数据库设计功能和SQL生成功能。在众多的软件项目里面，权限管理基本上都是必备的，看到有网友甚至为权限管理单独做了一套框架，目的都是为了很快的应用到不同的项目里面，减少开发的成本。本人也做过很多类似的工作，不论多好的框架都只能解决80%的用户，总有20%的用户会让你花80%的精力重复做过的事情，下边就说说在Visor 中如何完成数据库设计，并且通过模板生成数据库DDL SQL 和实体对象。
1. 建模设计，在Visor 里面，现在提供三种设计器，流程设计，ER设计和JPA实体设计，只有ER设计里面完成的ER图可以生成数据库需要的DDL SQL。
2. 设计模板
在文档菜单里面可以下载建模生成的数据表对象关系数据文件，数据文件是JSON格式的，然后用你熟悉的模板语言去生成想要的SQL代码和实体代码，在项目有严格规范的情况下，可以生成更多的对象，如DAO，Service，resultful api 等。可以说一劳永逸。 在Visor里面，默认使用的是Jsrender ，而且已经提供了生成ORACLE，SQL Server 和MYSQL 对应的ＤＤＬ的模板。完成建模以后，点击相应的模板，可以下载对应数据库的DDL SQL文件. 里面会包含唯一索引和非空约束， 如果你要在数据库里面建立更多的约束和索引，这还需要手动完成。但是这些对象是否要创建在数据库里都还有争议。
{
"tables": [
{
"name": "Users",
"text": "Users",
"auditable": false,
"prefix": "",
"abbreviation": "",
"annotation": "",
"cachable": false,
"namespace": "",
"strategy": "seqence",
"sequenceName": "",
"owner": "",
"fields": [
{
"name": "id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "name",
"text": "name",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "email",
"text": "email",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "password",
"text": "password",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "active",
"text": "active",
"abbreviation": "",
"datatype": "boolean",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
}
],
"pks": [
{
"name": "id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
]
},
{
"name": "Roles",
"text": "Roles",
"auditable": false,
"prefix": "",
"abbreviation": "",
"annotation": "",
"cachable": false,
"namespace": "",
"strategy": "seqence",
"sequenceName": "",
"owner": "",
"fields": [
{
"name": "id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "name",
"text": "name",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "description",
"text": "description",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
}
],
"pks": [
{
"name": "id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
]
},
{
"name": "User_Role",
"text": "User_Role",
"auditable": false,
"prefix": "",
"abbreviation": "",
"annotation": "",
"cachable": false,
"namespace": "",
"strategy": "seqence",
"sequenceName": "",
"owner": "",
"fields": [
{
"name": "User_id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": true,
"reftable": "Users",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "Role_id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": true,
"reftable": "Roles",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
],
"pks": [
{
"name": "User_id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": true,
"reftable": "Users",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "Role_id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": true,
"reftable": "Roles",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
]
},
{
"name": "privileges",
"text": "table1",
"auditable": false,
"prefix": "",
"abbreviation": "",
"annotation": "",
"cachable": false,
"namespace": "",
"strategy": "seqence",
"sequenceName": "",
"owner": "",
"fields": [
{
"name": "id",
"text": "id",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": true,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "name",
"text": "name",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "description",
"text": "description",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": false,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": true,
"unique": false,
"annotation": "",
"defaultvalue": "",
,
,
}
],
"pks": [
{
"name": "id",
"text": "id",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": true,
"isfk": false,
"reftable": "",
"refield": "",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
]
},
{
"name": "Role_Privilege",
"text": "table1",
"auditable": false,
"prefix": "",
"abbreviation": "",
"annotation": "",
"cachable": false,
"namespace": "",
"strategy": "seqence",
"sequenceName": "",
"owner": "",
"fields": [
{
"name": "Roles_id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": true,
"reftable": "Roles",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "privilege_id",
"text": "id",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": true,
"isfk": true,
"reftable": "privilege",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
],
"pks": [
{
"name": "Roles_id",
"text": "id",
"abbreviation": "",
"datatype": "number",
,
"ispk": true,
"isfk": true,
"reftable": "Roles",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
},
{
"name": "privilege_id",
"text": "id",
"abbreviation": "",
"datatype": "varchar",
,
"ispk": true,
"isfk": true,
"reftable": "privilege",
"refield": "id",
"nullable": false,
"unique": true,
"annotation": "",
"defaultvalue": "",
,
,
}
]
}
],
"connections": [
{
"name": "R1",
"identifying": true,
"nullable": false,
"source_owner": "",
"source_prefix": "",
"source": "Users",
"target_owner": "",
"target_prefix": "",
"target": "User_Role",
"elements": [
{
"key": "id",
"value": "User_id"
}
]
},
{
"name": "R2",
"identifying": true,
"nullable": false,
"source_owner": "",
"source_prefix": "",
"source": "Roles",
"target_owner": "",
"target_prefix": "",
"target": "User_Role",
"elements": [
{
"key": "id",
"value": "Role_id"
}
]
},
{
"name": "R3",
"identifying": true,
"nullable": false,
"source_owner": "",
"source_prefix": "",
"source": "Roles",
"target_owner": "",
"target_prefix": "",
"target": "Role_Privilege",
"elements": [
{
"key": "id",
"value": "Roles_id"
}
]
},
{
"name": "R4",
"identifying": true,
"nullable": false,
"source_owner": "",
"source_prefix": "",
"source": "privileges",
"target_owner": "",
"target_prefix": "",
"target": "Role_Privilege",
"elements": [
{
"key": "id",
"value": "privilege_id"
}
]
}
],
"option": {
"datetime": "2015-05-25T04:32:51.916Z"
}
}
3. 完善建模设计和模板
项目需求会不停的改变，不同项目也有可能会应用不同的技术和语言。而我们要做的只是完善建模和制作新的模板。 而那些重复的工作，就让Visor 替你去完成吧。