GeezerVison is showing github.com — APIJSON-Demo/PostgreSQL/postgres_sys_ddl.sql at main · APIJSON/APIJSON-Demo

Expand file tree

postgres_sys_ddl.sql

Copy path

More file actions

Latest commit

History

355 lines (260 loc) · 11.4 KB

postgres_sys_ddl.sql

Copy path

File metadata and controls

355 lines (260 loc) · 11.4 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

create table "Praise"

(

);

alter table "Praise"

owner to postgres;

create table "Random"

(

);

alter table "Random"

owner to postgres;

create table "_Visit"

(

);

alter table "_Visit"

owner to postgres;

create table "Method"

(

);

alter table "Method"

owner to postgres;

create table "Document"

(

id bigint,

debug smallint,

"userId" bigint,

"testAccountId" bigint,

version smallint,

name varchar(100),

type varchar(5),

url varchar(250),

request text,

apijson text,

sqlauto text,

standard text,

header text,

date timestamp,

detail text

);

alter table "Document"

owner to postgres;

create table apijson_privacy

(

id bigint not null

primary key,

certified smallint not null,

phone varchar(64) not null,

balance numeric(10, 2) not null,

_password varchar(20) not null,

"_payPassword" varchar(32) not null

);

comment on table apijson_privacy is '用户隐私信息表。\n对安全要求高，不想泄漏真实名称。对外名称为 Privacy';

comment on column apijson_privacy.id is '唯一标识';

comment on column apijson_privacy.certified is '已认证';

comment on column apijson_privacy.phone is '手机号，仅支持 11 位数的。不支持 +86 这种国家地区开头的。如果要支持就改为 VARCHAR(14)';

comment on column apijson_privacy.balance is '余额';

comment on column apijson_privacy._password is '登录密码';

comment on column apijson_privacy."_payPassword" is '支付密码';

alter table apijson_privacy

owner to postgres;

create index "phone_UNIQUE"

on apijson_privacy (phone);

create table apijson_user

(

id bigint not null

primary key,

sex smallint not null,

name varchar(20),

tag varchar(45),

head varchar(300),

"contactIdList" jsonb,

"pictureList" jsonb,

date timestamp(6)

);

comment on table apijson_user is '用户公开信息表。对安全要求高，不想泄漏真实名称。对外名称为 User';

comment on column apijson_user.id is '唯一标识';

comment on column apijson_user.sex is '性别：

0-男

1-女';

comment on column apijson_user.name is '名称';

comment on column apijson_user.tag is '标签';

comment on column apijson_user.head is '头像url';

comment on column apijson_user."contactIdList" is '联系人id列表';

comment on column apijson_user."pictureList" is '照片列表';

comment on column apijson_user.date is '创建日期';

alter table apijson_user

owner to postgres;

create table "Comment"

(

id bigint not null

primary key,

"toId" bigint default 0 not null,

"userId" bigint not null,

"momentId" bigint not null,

date timestamp(6),

content varchar(1000) not null

);

comment on table "Comment" is '评论';

comment on column "Comment".id is '唯一标识';

comment on column "Comment"."toId" is '被回复的id';

comment on column "Comment"."userId" is '评论人id';

comment on column "Comment"."momentId" is '动态id';

comment on column "Comment".date is '创建日期';

comment on column "Comment".content is '内容';

alter table "Comment"

owner to postgres;

create table "Moment"

(

id bigint not null

primary key,

"userId" bigint not null,

date timestamp(6),

content varchar(300),

"praiseUserIdList" jsonb not null,

"pictureList" jsonb not null

);

comment on table "Moment" is '动态';

comment on column "Moment".id is '唯一标识';

comment on column "Moment"."userId" is '用户id';

comment on column "Moment".date is '创建日期';

comment on column "Moment".content is '内容';

comment on column "Moment"."praiseUserIdList" is '点赞的用户id列表';

comment on column "Moment"."pictureList" is '图片列表';

alter table "Moment"

owner to postgres;

create table "TestRecord"

(

id bigint not null

primary key,

"userId" bigint not null,

"documentId" bigint not null,

response text not null,

date timestamp(6) default CURRENT_TIMESTAMP not null,

compare text,

standard text,

"randomId" bigint default 0,

headless smallint default 0 not null,

"reportId" bigint default 0 not null,

"testAccountId" bigint default 0 not null,

duration bigint default 0 not null,

"minDuration" bigint default 0 not null,

"maxDuration" bigint default 0 not null,

host varchar(200)

);

comment on column "TestRecord".id is '唯一标识';

comment on column "TestRecord"."userId" is '用户id';

comment on column "TestRecord"."documentId" is '测试用例文档id';

comment on column "TestRecord".response is '接口返回结果JSON';

comment on column "TestRecord".date is '创建日期';

comment on column "TestRecord".compare is '对比结果';

comment on column "TestRecord".standard is 'response 的校验标准，是一个 JSON 格式的 AST ，描述了正确 Response 的结构、里面的字段名称、类型、长度、取值范围等属性。';

comment on column "TestRecord"."randomId" is '随机配置 id';

comment on column "TestRecord".headless is '是否为无 UI 的 Headless 模式：0-否 1-是';

comment on column "TestRecord"."reportId" is '测试报告 ID';

comment on column "TestRecord"."testAccountId" is '测试账号 id';

alter table "TestRecord"

owner to postgres;

create table "Function"

(

id bigint not null

primary key,

language varchar,

name varchar(30) not null,

"returnType" varchar(45) default 'Object'::character varying,

arguments varchar(100),

demo text not null,

detail varchar(1000),

date timestamp(6) default CURRENT_TIMESTAMP not null,

"userId" bigint default 0,

version integer default 0,

tag varchar,

methods varchar,

return integer

);

comment on column "Function".language is '语言：Java(java), JavaScript(js), Lua(lua), Python(py), Ruby(ruby), PHP(php) 等，NULL 默认为 Java，JDK 1.6-11 默认支持 JavaScript，JDK 12+ 需要额外依赖 Nashron/Rhiro 等 js 引擎库，其它的语言需要依赖对应的引擎库，并在 ScriptEngineManager 中注册';

comment on column "Function".name is '方法名';

comment on column "Function"."returnType" is '返回类型';

comment on column "Function".arguments is '参数列表，每个参数的类型都是 String。

用 , 分割的字符串比 [JSONArray] 更好，例如 array,item ，更直观，还方便拼接函数。';

comment on column "Function".demo is '可用的示例。';

comment on column "Function".detail is '详细描述';

comment on column "Function".date is '创建时间';

comment on column "Function"."userId" is '用户id';

comment on column "Function".version is '允许的最低版本号，只限于GET,HEAD外的操作方法。\nTODO 使用 requestIdList 替代 version,tag,methods';

comment on column "Function".tag is '允许的标签.\nnull - 允许全部\nTODO 使用 requestIdList 替代 version,tag,methods';

comment on column "Function".methods is '允许的操作方法。\nnull - 允许全部\nTODO 使用 requestIdList 替代 version,tag,methods';

comment on column "Function".return is '返回值示例';

alter table "Function"

owner to postgres;

create table "Request"

(

id bigint,

debug smallint,

version smallint,

method varchar(10),

tag varchar(30),

structure json,

detail varchar(10000),

date timestamp

);

alter table "Request"

owner to postgres;

create table "Script"

(

id bigint,

"userId" bigint,

"testAccountId" bigint,

"documentId" bigint,

simple smallint,

ahead smallint,

title varchar(100),

name varchar(100),

script text,

date timestamp,

detail varchar(1000)

);

alter table "Script"

owner to postgres;

create table "Access"

(

id integer not null

primary key,

schema varchar(100) default NULL::character varying,

debug integer default 0 not null,

name varchar(50) default '实际表名，例如 apijson_user'::character varying not null,

alias text,

get text default '["UNKNOWN", "LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text not null,

head text default '["UNKNOWN", "LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text not null,

gets text default '["LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text not null,

heads text default '["LOGIN", "CONTACT", "CIRCLE", "OWNER", "ADMIN"]'::text not null,

post text default '["OWNER", "ADMIN"]'::text not null,

put text default '["OWNER", "ADMIN"]'::text not null,

delete text default '["OWNER", "ADMIN"]'::text not null,

date text default CURRENT_TIMESTAMP not null,

detail text

);

comment on column "Access".id is '唯一标识';

comment on column "Access".debug is '是否为调试表，只允许在开发环境使用，测试和线上环境禁用';

comment on column "Access".alias is '外部调用的表别名，例如 User';

comment on column "Access".get is '允许 get 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]

用 JSON 类型不能设置默认值，反正权限对应的需求是明确的，也不需要自动转 JSONArray。

TODO: 直接 LOGIN,CONTACT,CIRCLE,OWNER 更简单，反正是开发内部用，不需要复杂查询。';

comment on column "Access".head is '允许 head 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".gets is '允许 gets 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".heads is '允许 heads 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".post is '允许 post 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".put is '允许 put 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".delete is '允许 delete 的角色列表，例如 ["LOGIN", "CONTACT", "CIRCLE", "OWNER"]';

comment on column "Access".date is '创建时间';

alter table "Access"

owner to postgres;

create table "Verify"

(

id bigint,

type integer,

phone bigint,

verify integer,

date timestamp

);

alter table "Verify"

owner to postgres;