56 changes: 36 additions & 20 deletions packages/nocodb-sdk/src/lib/Api.ts
Original file line number Diff line number Diff line change
Expand Up @@ -805,7 +805,7 @@ export class Api<
* @tags Auth
* @name Signup
* @summary Signup
* @request POST:/api/v1/db/auth/user/signup
* @request POST:/api/v1/auth/user/signup
* @response `200` `{ token?: string }` OK
* @response `400` `{ msg?: string }` Bad Request
* @response `401` `void` Unauthorized
Expand All @@ -816,7 +816,7 @@ export class Api<
params: RequestParams = {}
) =>
this.request<{ token?: string }, { msg?: string } | void>({
path: `/api/v1/db/auth/user/signup`,
path: `/api/v1/auth/user/signup`,
method: 'POST',
body: data,
format: 'json',
Expand All @@ -829,7 +829,7 @@ export class Api<
* @tags Auth
* @name Signin
* @summary Signin
* @request POST:/api/v1/db/auth/user/signin
* @request POST:/api/v1/auth/user/signin
* @response `200` `{ token?: string }` OK
* @response `400` `{ msg?: string }` Bad Request
*/
Expand All @@ -838,7 +838,7 @@ export class Api<
params: RequestParams = {}
) =>
this.request<{ token?: string }, { msg?: string }>({
path: `/api/v1/db/auth/user/signin`,
path: `/api/v1/auth/user/signin`,
method: 'POST',
body: data,
type: ContentType.Json,
Expand All @@ -852,12 +852,12 @@ export class Api<
* @tags Auth
* @name Me
* @summary User info
* @request GET:/api/v1/db/auth/user/me
* @request GET:/api/v1/auth/user/me
* @response `200` `UserInfoType` OK
*/
me: (query?: { project_id?: string }, params: RequestParams = {}) =>
this.request<UserInfoType, any>({
path: `/api/v1/db/auth/user/me`,
path: `/api/v1/auth/user/me`,
method: 'GET',
query: query,
format: 'json',
Expand All @@ -870,13 +870,13 @@ export class Api<
* @tags Auth
* @name PasswordForgot
* @summary Password forgot
* @request POST:/api/v1/db/auth/password/forgot
* @request POST:/api/v1/auth/password/forgot
* @response `200` `void` OK
* @response `401` `void` Unauthorized
*/
passwordForgot: (data: { email?: string }, params: RequestParams = {}) =>
this.request<void, void>({
path: `/api/v1/db/auth/password/forgot`,
path: `/api/v1/auth/password/forgot`,
method: 'POST',
body: data,
type: ContentType.Json,
Expand All @@ -889,7 +889,7 @@ export class Api<
* @tags Auth
* @name PasswordChange
* @summary Password change
* @request POST:/api/v1/db/auth/password/change
* @request POST:/api/v1/auth/password/change
* @response `200` `{ msg?: string }` OK
* @response `400` `{ msg?: string }` Bad request
*/
Expand All @@ -898,7 +898,7 @@ export class Api<
params: RequestParams = {}
) =>
this.request<{ msg?: string }, { msg?: string }>({
path: `/api/v1/db/auth/password/change`,
path: `/api/v1/auth/password/change`,
method: 'POST',
body: data,
type: ContentType.Json,
Expand All @@ -912,12 +912,12 @@ export class Api<
* @tags Auth
* @name PasswordResetTokenValidate
* @summary Reset token verify
* @request POST:/api/v1/db/auth/token/validate/{token}
* @request POST:/api/v1/auth/token/validate/{token}
* @response `200` `void` OK
*/
passwordResetTokenValidate: (token: string, params: RequestParams = {}) =>
this.request<void, any>({
path: `/api/v1/db/auth/token/validate/${token}`,
path: `/api/v1/auth/token/validate/${token}`,
method: 'POST',
...params,
}),
Expand All @@ -928,12 +928,12 @@ export class Api<
* @tags Auth
* @name EmailValidate
* @summary Verify email
* @request POST:/api/v1/db/auth/email/validate/{token}
* @request POST:/api/v1/auth/email/validate/{token}
* @response `200` `void` OK
*/
emailValidate: (token: string, params: RequestParams = {}) =>
this.request<void, any>({
path: `/api/v1/db/auth/email/validate/${token}`,
path: `/api/v1/auth/email/validate/${token}`,
method: 'POST',
...params,
}),
Expand All @@ -944,7 +944,7 @@ export class Api<
* @tags Auth
* @name PasswordReset
* @summary Password reset
* @request POST:/api/v1/db/auth/password/reset/{token}
* @request POST:/api/v1/auth/password/reset/{token}
* @response `200` `void` OK
*/
passwordReset: (
Expand All @@ -953,7 +953,7 @@ export class Api<
params: RequestParams = {}
) =>
this.request<void, any>({
path: `/api/v1/db/auth/password/reset/${token}`,
path: `/api/v1/auth/password/reset/${token}`,
method: 'POST',
body: data,
type: ContentType.Json,
Expand All @@ -966,12 +966,12 @@ export class Api<
* @tags Auth
* @name TokenRefresh
* @summary Refresh token
* @request POST:/api/v1/db/auth/token/refresh
* @request POST:/api/v1/auth/token/refresh
* @response `200` `void` OK
*/
tokenRefresh: (params: RequestParams = {}) =>
this.request<void, any>({
path: `/api/v1/db/auth/token/refresh`,
path: `/api/v1/auth/token/refresh`,
method: 'POST',
...params,
}),
Expand Down Expand Up @@ -3197,12 +3197,28 @@ export class Api<
*
* @tags Utils
* @name AppVersion
* @request GET:/api/v1/db/meta/nocodb/version
* @request GET:/api/v1/version
* @response `200` `any` OK
*/
appVersion: (params: RequestParams = {}) =>
this.request<any, any>({
path: `/api/v1/db/meta/nocodb/version`,
path: `/api/v1/version`,
method: 'GET',
format: 'json',
...params,
}),

/**
* No description
*
* @tags Utils
* @name AppHealth
* @request GET:/api/v1/health
* @response `200` `any` OK
*/
appHealth: (params: RequestParams = {}) =>
this.request<any, any>({
path: `/api/v1/health`,
method: 'GET',
format: 'json',
...params,
Expand Down
30 changes: 30 additions & 0 deletions packages/nocodb-sdk/src/lib/TemplateGenerator.ts
Original file line number Diff line number Diff line change
@@ -0,0 +1,30 @@
import UITypes from './UITypes';

export interface Column {
column_name: string;
ref_column_name: string;
uidt?: UITypes;
dtxp?: any;
dt?: any;
}
export interface Table {
table_name: string;
ref_table_name: string;
columns: Array<Column>;
}
export interface Template {
title: string;
tables: Array<Table>;
}

export default abstract class TemplateGenerator {
abstract parse(): Promise<any>;
abstract parseTemplate(): Promise<Template>;
abstract getColumns(): Promise<any>;
abstract parseData(): Promise<any>;
abstract getData(): Promise<{
[table_name: string]: Array<{
[key: string]: any;
}>;
}>;
}
41 changes: 41 additions & 0 deletions packages/nocodb-sdk/src/lib/passwordHelpers.ts
Original file line number Diff line number Diff line change
@@ -0,0 +1,41 @@
export function validatePassword(p) {
let error = '';
let progress = 0;
let hint = null;
let valid = true;
if (!p) {
error =
'At least 8 letters with one Uppercase, one number and one special letter';
valid = false;
} else {
if (!(p.length >= 8)) {
error += 'Atleast 8 letters. ';
valid = false;
} else {
progress = Math.min(100, progress + 25);
}

if (!p.match(/.*[A-Z].*/)) {
error += 'One Uppercase Letter. ';
valid = false;
} else {
progress = Math.min(100, progress + 25);
}

if (!p.match(/.*[0-9].*/)) {
error += 'One Number. ';
valid = false;
} else {
progress = Math.min(100, progress + 25);
}

if (!p.match(/[$&+,:;=?@#|'<>.^*()%!_-]/)) {
error += 'One special letter. ';
hint = "Allowed special character list : $&+,:;=?@#|'<>.^*()%!_-";
valid = false;
} else {
progress = Math.min(100, progress + 25);
}
}
return { error, valid, progress, hint };
}
7 changes: 5 additions & 2 deletions packages/nocodb-sdk/src/lib/sqlUi/MssqlUi.ts
Original file line number Diff line number Diff line change
Expand Up @@ -123,6 +123,7 @@ export class MssqlUi {
pk: false,
un: false,
ai: false,
au: true,
cdf: 'GETDATE()',
clen: 45,
np: null,
Expand Down Expand Up @@ -919,7 +920,9 @@ export class MssqlUi {
// if (1) {
col.altered = col.altered || 2;
// }

if (col.au) {
col.cdf = 'GETDATE()';
}
// if (!col.ai) {
// col.dtx = 'specificType'
// } else {
Expand Down Expand Up @@ -1144,7 +1147,7 @@ export class MssqlUi {
colProp.dt = 'double';
break;
case 'Duration':
colProp.dt = 'int';
colProp.dt = 'decimal';
break;
case 'Rating':
colProp.dt = 'int';
Expand Down
2 changes: 1 addition & 1 deletion packages/nocodb-sdk/src/lib/sqlUi/MysqlUi.ts
Original file line number Diff line number Diff line change
Expand Up @@ -1036,7 +1036,7 @@ export class MysqlUi {
colProp.dt = 'double';
break;
case 'Duration':
colProp.dt = 'int';
colProp.dt = 'decimal';
break;
case 'Rating':
colProp.dt = 'int';
Expand Down
4 changes: 2 additions & 2 deletions packages/nocodb-sdk/src/lib/sqlUi/PgUi.ts
Original file line number Diff line number Diff line change
Expand Up @@ -1660,7 +1660,7 @@ export class PgUi {
colProp.dt = 'double precision';
break;
case 'Duration':
colProp.dt = 'int8';
colProp.dt = 'decimal';
break;
case 'Rating':
colProp.dt = 'smallint';
Expand Down Expand Up @@ -1740,7 +1740,7 @@ export class PgUi {
return ['json', 'char', 'character', 'character varying', 'text'];

case 'JSON':
return ['json', 'text'];
return ['json', 'jsonb', 'text'];
case 'Checkbox':
return [
'bit',
Expand Down
2 changes: 1 addition & 1 deletion packages/nocodb-sdk/src/lib/sqlUi/SqliteUi.ts
Original file line number Diff line number Diff line change
Expand Up @@ -853,7 +853,7 @@ export class SqliteUi {
colProp.dt = 'double';
break;
case 'Duration':
colProp.dt = 'integer';
colProp.dt = 'decimal';
break;
case 'Rating':
colProp.dt = 'integer';
Expand Down
5 changes: 3 additions & 2 deletions packages/nocodb/README.md
Original file line number Diff line number Diff line change
@@ -1,10 +1,11 @@
# nocodb
# Nocodb

## Running locally

Even though this package is a backend project, you can still visit the dashboard as it includes ``nc-lib-gui``.

```
npm install
npm run watch:run
# open localhost:8080/dashboard in browser
Expand All @@ -18,4 +19,4 @@ If you wish to combine the frontend and backend together in your local devlopmen
"nc-lib-gui": "file:../nc-lib-gui"
```

In this case, whenever there is any changes made in frontend, you need to run ``npm run build:copy`` under ``packages/nc-gui/``.
In this case, whenever there is any changes made in frontend, you need to run ``npm run build:copy`` under ``packages/nc-gui/``.
58 changes: 35 additions & 23 deletions packages/nocodb/package-lock.json
7 changes: 4 additions & 3 deletions packages/nocodb/package.json
Original file line number Diff line number Diff line change
Expand Up @@ -69,6 +69,7 @@
"watch:serve": "nodemon -e ts -w ./build -x npm run debug-local ",
"watch:run": "cross-env NC_DISABLE_TELE1=true EE=true nodemon -e ts,js -w ./src -x \"ts-node src/run/docker --log-error --project tsconfig.json\"",
"watch:run:cypress": "cross-env EE=true nodemon -e ts,js -w ./src -x \"ts-node src/run/docker --log-error --project tsconfig.json\"",
"watch:run:cypress:pg": "cross-env EE=true nodemon -e ts,js -w ./src -x \"ts-node src/run/dockerRunPG --log-error --project tsconfig.json\"",
"watch:run:mysql": "cross-env NC_DISABLE_TELE=true EE=true nodemon -e ts,js -w ./src -x \"ts-node src/run/dockerRunMysql --log-error --project tsconfig.json\"",
"watch:run:pg": "cross-env NC_DISABLE_TELE=true EE=true nodemon -e ts,js -w ./src -x \"ts-node src/run/dockerRunPG --log-error --project tsconfig.json\"",
"run": "ts-node src/run/docker",
Expand Down Expand Up @@ -153,11 +154,11 @@
"mysql2": "^2.2.5",
"nanoid": "^3.1.20",
"nc-common": "0.0.6",
"nc-help": "0.2.61",
"nc-help": "0.2.67",
"nc-lib-gui": "0.91.10",
"nc-plugin": "0.1.2",
"ncp": "^2.0.0",
"nocodb-sdk": "0.91.10",
"nocodb-sdk": "file:../nocodb-sdk",
"nodemailer": "^6.4.10",
"object-hash": "^3.0.0",
"ora": "^4.0.4",
Expand Down Expand Up @@ -257,4 +258,4 @@
"**/*.spec.js"
]
}
}
}
10 changes: 5 additions & 5 deletions packages/nocodb/src/__tests__/restv2.test.ts
Original file line number Diff line number Diff line change
Expand Up @@ -209,7 +209,7 @@ describe('Noco v2 Tests', () => {
type: UITypes.Rollup,
alias: 'filmCount',
rollupColumn: 'FilmId',
relationColumn: 'FilmMMList',
relationColumn: 'Film List',
rollupFunction: 'count'
}
];
Expand Down Expand Up @@ -413,7 +413,7 @@ describe('Noco v2 Tests', () => {
type: UITypes.Lookup,
alias: 'filmNames',
lookupColumn: 'Title',
relationColumn: 'FilmMMList'
relationColumn: 'Film List'
};
request(app)
.post(`/nc/${projectId}/generate`)
Expand Down Expand Up @@ -1335,7 +1335,7 @@ describe('Noco v2 Tests', () => {
type: UITypes.Lookup,
alias: 'filmIds',
lookupColumn: 'FilmId',
relationColumn: 'FilmMMList'
relationColumn: 'Film List'
},
{
table: 'actor',
Expand Down Expand Up @@ -1398,15 +1398,15 @@ describe('Noco v2 Tests', () => {
type: UITypes.Rollup,
alias: 'actorsCount',
rollupColumn: 'ActorId',
relationColumn: 'ActorMMList',
relationColumn: 'ActorList',
rollupFunction: 'count'
},
{
table: 'actor',
type: UITypes.Lookup,
alias: 'actorsCountList',
lookupColumn: 'actorsCount',
relationColumn: 'FilmMMList'
relationColumn: 'Film List'
},
{
table: 'actor',
Expand Down
39 changes: 24 additions & 15 deletions packages/nocodb/src/lib/Noco.ts
Original file line number Diff line number Diff line change
Expand Up @@ -42,6 +42,7 @@ import { Tele } from 'nc-help';
import * as http from 'http';
import weAreHiring from './utils/weAreHiring';
import getInstance from './utils/getInstance';
import initAdminFromEnv from './meta/api/userApi/initAdminFromEnv';

const log = debug('nc:app');
require('dotenv').config();
Expand Down Expand Up @@ -186,8 +187,8 @@ export default class Noco {
}

await Noco._ncMeta.metaInit();

await this.readOrGenJwtSecret();
await this.initJwt();
await initAdminFromEnv();

await NcUpgrader.upgrade({ ncMeta: Noco._ncMeta });

Expand Down Expand Up @@ -488,20 +489,28 @@ export default class Noco {
}
}

private async readOrGenJwtSecret(): Promise<any> {
if (this.config?.auth?.jwt && !this.config.auth.jwt.secret) {
let secret = (
await Noco._ncMeta.metaGet('', '', 'nc_store', {
key: 'nc_auth_jwt_secret'
})
)?.value;
if (!secret) {
await Noco._ncMeta.metaInsert('', '', 'nc_store', {
key: 'nc_auth_jwt_secret',
value: secret = uuidv4()
});
private async initJwt(): Promise<any> {
if (this.config?.auth?.jwt) {
if (!this.config.auth.jwt.secret) {
let secret = (
await Noco._ncMeta.metaGet('', '', 'nc_store', {
key: 'nc_auth_jwt_secret'
})
)?.value;
if (!secret) {
await Noco._ncMeta.metaInsert('', '', 'nc_store', {
key: 'nc_auth_jwt_secret',
value: secret = uuidv4()
});
}
this.config.auth.jwt.secret = secret;
}

this.config.auth.jwt.options = this.config.auth.jwt.options || {};
if (!this.config.auth.jwt.options?.expiresIn) {
this.config.auth.jwt.options.expiresIn =
process.env.NC_JWT_EXPIRES_IN ?? '10h';
}
this.config.auth.jwt.secret = secret;
}
let serverId = (
await Noco._ncMeta.metaGet('', '', 'nc_store', {
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -41,6 +41,7 @@ import { customValidators } from './customValidators';
import { NcError } from '../../../../meta/helpers/catchError';
import { customAlphabet } from 'nanoid';
import DOMPurify from 'isomorphic-dompurify';
import { sanitize, unsanitize } from './helpers/sanitize';

const GROUP_COL = '__nc_group_id';

Expand Down Expand Up @@ -90,7 +91,7 @@ class BaseModelSqlv2 {
}

public async readByPk(id?: any): Promise<any> {
const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);

await this.selectObject({ qb });

Expand All @@ -106,7 +107,7 @@ class BaseModelSqlv2 {
}

public async exist(id?: any): Promise<any> {
const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);
await this.selectObject({ qb });
const pks = this.model.primaryKeys;
if ((id + '').split('___').length != pks.length) {
Expand All @@ -119,12 +120,14 @@ class BaseModelSqlv2 {
args: {
where?: string;
filterArr?: Filter[];
sort?: string | string[];
} = {}
): Promise<any> {
const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);
await this.selectObject({ qb });

const aliasColObjMap = await this.model.getAliasColObjMap();
const sorts = extractSortsObject(args?.sort, aliasColObjMap);
const filterObj = extractFilterFromXwhere(args?.where, aliasColObjMap);

await conditionV2(
Expand All @@ -145,6 +148,12 @@ class BaseModelSqlv2 {
this.dbDriver
);

if (Array.isArray(sorts) && sorts?.length) {
await sortV2(sorts, qb, this.dbDriver);
} else if (this.model.primaryKey) {
qb.orderBy(this.model.primaryKey.column_name);
}

const data = await qb.first();

if (data) {
Expand All @@ -167,15 +176,12 @@ class BaseModelSqlv2 {
): Promise<any> {
const { where, ...rest } = this._getListArgs(args as any);

const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);
await this.selectObject({ qb });

const aliasColObjMap = await this.model.getAliasColObjMap();

let sorts = extractSortsObject(args?.sort, aliasColObjMap);

const filterObj = extractFilterFromXwhere(args?.where, aliasColObjMap);

// todo: replace with view id
if (!ignoreFilterSort && this.viewId) {
await conditionV2(
Expand Down Expand Up @@ -241,7 +247,6 @@ class BaseModelSqlv2 {

if (!ignoreFilterSort) applyPaginate(qb, rest);
const proto = await this.getProto();

const data = await this.extractRawQueryAndExec(qb);

return data?.map(d => {
Expand All @@ -257,7 +262,7 @@ class BaseModelSqlv2 {
await this.model.getColumns();
const { where } = this._getListArgs(args);

const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);

// qb.xwhere(where, await this.model.getAliasColMapping());
const aliasColObjMap = await this.model.getAliasColObjMap();
Expand Down Expand Up @@ -306,11 +311,11 @@ class BaseModelSqlv2 {
);
}

qb.count(this.model.primaryKey?.column_name || '*', {
qb.count(sanitize(this.model.primaryKey?.column_name) || '*', {
as: 'count'
}).first();

return ((await qb) as any).count;
const res = (await this.dbDriver.raw(unsanitize(qb.toQuery()))) as any;
return (this.isPg ? res.rows[0] : res[0][0] ?? res[0]).count;
}

async groupBy(
Expand All @@ -326,7 +331,7 @@ class BaseModelSqlv2 {
) {
const { where, ...rest } = this._getListArgs(args as any);

const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);
qb.count(`${this.model.primaryKey?.column_name || '*'} as count`);
qb.select(args.column_name);

Expand Down Expand Up @@ -907,7 +912,7 @@ class BaseModelSqlv2 {

const proto = await childModel.getProto();

return (await qb).map(c => {
return (await this.extractRawQueryAndExec(qb)).map(c => {
c.__proto__ = proto;
return c;
});
Expand Down Expand Up @@ -993,8 +998,7 @@ class BaseModelSqlv2 {
applyPaginate(qb, args);

const proto = await parentModel.getProto();

return (await qb).map(c => {
return (await this.extractRawQueryAndExec(qb)).map(c => {
c.__proto__ = proto;
return c;
});
Expand Down Expand Up @@ -1242,7 +1246,7 @@ class BaseModelSqlv2 {
await populatePk(this.model, data);

// todo: filter based on view
const insertObj = await this.model.mapAliasToColumn(data, sanitize);
const insertObj = await this.model.mapAliasToColumn(data);

await this.validate(insertObj);

Expand All @@ -1258,12 +1262,11 @@ class BaseModelSqlv2 {
// const driver = trx ? trx : this.dbDriver;

const query = this.dbDriver(this.tnPath).insert(insertObj);

if (this.isPg || this.isMssql) {
query.returning(
`${this.model.primaryKey.column_name} as ${this.model.primaryKey.title}`
);
response = await query;
response = await this.extractRawQueryAndExec(query);
}

const ai = this.model.columns.find(c => c.ai);
Expand All @@ -1275,11 +1278,19 @@ class BaseModelSqlv2 {
if (response?.length) {
id = response[0];
} else {
id = (await query)[0];
const res = await this.extractRawQueryAndExec(query);
id = res?.id ?? res[0]?.insertId;
}

if (ai) {
// response = await this.readByPk(id)
if (this.isSqlite) {
// sqlite doesnt return id after insert
id = (
await this.dbDriver(this.tnPath)
.select(ai.column_name)
.max(ai.column_name, { as: 'id' })
)[0].id;
}
response = await this.readByPk(id);
} else {
response = data;
Expand Down Expand Up @@ -1326,14 +1337,11 @@ class BaseModelSqlv2 {

await this.beforeUpdate(data, trx, cookie);

// const driver = trx ? trx : this.dbDriver;
//
// this.validate(data);
// await this._run(
await this.dbDriver(this.tnPath)
const query = this.dbDriver(this.tnPath)
.update(updateObj)
.where(await this._wherePk(id));
// );

await this.extractRawQueryAndExec(query);

const response = await this.readByPk(id);
await this.afterUpdate(response, trx, cookie);
Expand Down Expand Up @@ -1610,7 +1618,7 @@ class BaseModelSqlv2 {
} else {
await this.model.getColumns();
const { where } = this._getListArgs(args);
const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);
const aliasColObjMap = await this.model.getAliasColObjMap();
const filterObj = extractFilterFromXwhere(where, aliasColObjMap);

Expand Down Expand Up @@ -1672,7 +1680,7 @@ class BaseModelSqlv2 {
try {
await this.model.getColumns();
const { where } = this._getListArgs(args);
const qb = this.dbDriver(this.model.table_name);
const qb = this.dbDriver(this.tnPath);
const aliasColObjMap = await this.model.getAliasColObjMap();
const filterObj = extractFilterFromXwhere(where, aliasColObjMap);

Expand Down Expand Up @@ -2029,11 +2037,19 @@ class BaseModelSqlv2 {
}

private async extractRawQueryAndExec(qb: QueryBuilder) {
let query = qb.toQuery();
if (!this.isPg && !this.isMssql) {
query = unsanitize(qb.toQuery());
} else {
query = sanitize(query);
}
return this.isPg
? qb
: await this.dbDriver.from(
this.dbDriver.raw(qb.toString()).wrap('(', ') __nc_alias')
);
? (await this.dbDriver.raw(query))?.rows
: query.slice(0, 6) === 'select'
? await this.dbDriver.from(
this.dbDriver.raw(query).wrap('(', ') __nc_alias')
)
: await this.dbDriver.raw(query);
}
}

Expand Down Expand Up @@ -2168,10 +2184,6 @@ function getCompositePk(primaryKeys: Column[], row) {
return primaryKeys.map(c => row[c.title]).join('___');
}

export function sanitize(v) {
return v?.replace(/([^\\]|^)([?])/g, '$1\\$2');
}

export { BaseModelSqlv2 };
/**
* @copyright Copyright (c) 2021, Xgene Cloud Ltd
Expand Down
18 changes: 11 additions & 7 deletions packages/nocodb/src/lib/db/sql-data-mapper/lib/sql/conditionV2.ts
Original file line number Diff line number Diff line change
Expand Up @@ -10,6 +10,7 @@ import formulaQueryBuilderv2 from './formulav2/formulaQueryBuilderv2';
import FormulaColumn from '../../../../models/FormulaColumn';
import { RelationTypes, UITypes } from 'nocodb-sdk';
// import LookupColumn from '../../../models/LookupColumn';
import { sanitize } from './helpers/sanitize';

export default async function conditionV2(
conditionObj: Filter | Filter[],
Expand Down Expand Up @@ -203,11 +204,13 @@ const parseConditionV2 = async (
filter.comparison_op === 'notempty'
)
filter.value = '';
let field = customWhereClause
? filter.value
: alias
? `${alias}.${column.column_name}`
: column.column_name;
let field = sanitize(
customWhereClause
? filter.value
: alias
? `${alias}.${column.column_name}`
: column.column_name
);
let val = customWhereClause ? customWhereClause : filter.value;

return qb => {
Expand All @@ -216,12 +219,13 @@ const parseConditionV2 = async (
qb = qb.where(field, val);
break;
case 'neq':
case 'not':
qb = qb.whereNot(field, val);
break;
case 'like':
if (column.uidt === UITypes.Formula) {
[field, val] = [val, field];
val = `%${val}%`.replace(/^%'([\s\S]*)'%$/, '%$1%')
val = `%${val}%`.replace(/^%'([\s\S]*)'%$/, '%$1%');
} else {
val = `%${val}%`;
}
Expand All @@ -234,7 +238,7 @@ const parseConditionV2 = async (
case 'nlike':
if (column.uidt === UITypes.Formula) {
[field, val] = [val, field];
val = `%${val}%`.replace(/^%'([\s\S]*)'%$/, '%$1%')
val = `%${val}%`.replace(/^%'([\s\S]*)'%$/, '%$1%');
} else {
val = `%${val}%`;
}
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -86,7 +86,7 @@ const getAst = async ({
(!fields?.length || fields.includes(col.title)) &&
value
: fields?.length
? fields.includes(col.title)
? fields.includes(col.title) && value
: value
};
}, Promise.resolve({}));
Expand Down
Original file line number Diff line number Diff line change
@@ -0,0 +1,9 @@
export function sanitize(v) {
return v?.replace(/([^\\]|^)(\?+)/g, (_, m1, m2) => {
return `${m1}${m2.split('?').join('\\?')}`;
});
}

export function unsanitize(v) {
return v?.replace(/\\[?]/g, '?');
}
3 changes: 2 additions & 1 deletion packages/nocodb/src/lib/db/sql-data-mapper/lib/sql/sortV2.ts
Original file line number Diff line number Diff line change
Expand Up @@ -8,6 +8,7 @@ import LookupColumn from '../../../../models/LookupColumn';
import formulaQueryBuilderv2 from './formulav2/formulaQueryBuilderv2';
import FormulaColumn from '../../../../models/FormulaColumn';
import { RelationTypes, UITypes } from 'nocodb-sdk';
import { sanitize } from './helpers/sanitize';

export default async function sortV2(
sortList: Sort[],
Expand Down Expand Up @@ -205,7 +206,7 @@ export default async function sortV2(
}
break;
default:
qb.orderBy(`${column.column_name}`, sort.direction || 'asc');
qb.orderBy(sanitize(column.column_name), sort.direction || 'asc');
break;
}
}
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -439,7 +439,7 @@ class ModelXcMetaPg extends BaseModelXcMeta {
case 'set':
return 'MultiSelect';
case 'json':
return 'LongText';
return 'JSON';
case 'blob':
return 'LongText';
case 'geometry':
Expand Down Expand Up @@ -512,9 +512,8 @@ class ModelXcMetaPg extends BaseModelXcMeta {
case 'interval':
return 'string';
case 'json':
return 'json';
case 'jsonb':
return 'string';
return 'json';

case 'language_handler':
case 'lsec':
Expand Down
4 changes: 3 additions & 1 deletion packages/nocodb/src/lib/jobs/RedisJobsMgr.ts
Original file line number Diff line number Diff line change
Expand Up @@ -11,7 +11,9 @@ export default class RedisJobsMgr extends JobsMgr {
super();
this.queue = {};
this.workers = {};
this.connection = new Redis(config);
this.connection = new Redis(config, {
maxRetriesPerRequest: null
});
}

async add(
Expand Down
14 changes: 10 additions & 4 deletions packages/nocodb/src/lib/meta/api/columnApis.ts
Original file line number Diff line number Diff line change
Expand Up @@ -57,7 +57,7 @@ async function createHmAndBtColumn(
{
const title = getUniqueColumnAliasName(
await child.getColumns(),
type === 'bt' ? alias : `${parent.title}Read`
type === 'bt' ? alias : `${parent.title}`
);
await Column.insert<LinkToAnotherRecordColumn>({
title,
Expand All @@ -79,7 +79,7 @@ async function createHmAndBtColumn(
{
const title = getUniqueColumnAliasName(
await parent.getColumns(),
type === 'hm' ? alias : `${child.title}List`
type === 'hm' ? alias : `${child.title} List`
);
await Column.insert({
title,
Expand Down Expand Up @@ -427,7 +427,7 @@ export async function columnAdd(req: Request, res: Response<TableType>) {
await Column.insert({
title: getUniqueColumnAliasName(
await child.getColumns(),
`${child.title}MMList`
`${parent.title} List`
),
uidt: UITypes.LinkToAnotherRecord,
type: 'mm',
Expand All @@ -447,7 +447,7 @@ export async function columnAdd(req: Request, res: Response<TableType>) {
await Column.insert({
title: getUniqueColumnAliasName(
await parent.getColumns(),
req.body.title ?? `${parent.title}MMList`
req.body.title ?? `${child.title} List`
),

uidt: UITypes.LinkToAnotherRecord,
Expand Down Expand Up @@ -503,6 +503,12 @@ export async function columnAdd(req: Request, res: Response<TableType>) {
default:
{
colBody = getColumnPropsFromUIDT(colBody, base);
if (colBody.uidt === UITypes.Duration) {
colBody.dtxp = '20';
// by default, colBody.dtxs is 2
// Duration column needs more that that
colBody.dtxs = '4';
}
const tableUpdateBody = {
...table,
tn: table.table_name,
Expand Down
12 changes: 8 additions & 4 deletions packages/nocodb/src/lib/meta/api/dataApis/dataAliasExportApis.ts
Original file line number Diff line number Diff line change
Expand Up @@ -5,18 +5,22 @@ import {
getViewAndModelFromRequestByAliasOrId
} from './helpers';
import apiMetrics from '../../helpers/apiMetrics';
import View from '../../../models/View';

async function csvDataExport(req: Request, res: Response) {
const { view } = await getViewAndModelFromRequestByAliasOrId(req);

const { offset, elapsed, data } = await extractCsvData(view, req);
const { model, view } = await getViewAndModelFromRequestByAliasOrId(req);
let targetView = view;
if (!targetView) {
targetView = await View.getDefaultView(model.id);
}
const { offset, elapsed, data } = await extractCsvData(targetView, req);

res.set({
'Access-Control-Expose-Headers': 'nc-export-offset',
'nc-export-offset': offset,
'nc-export-elapsed-time': elapsed,
'Content-Disposition': `attachment; filename="${encodeURI(
view.title
targetView.title
)}-export.csv"`
});
res.send(data);
Expand Down
8 changes: 4 additions & 4 deletions packages/nocodb/src/lib/meta/api/metaDiffApis.ts
Original file line number Diff line number Diff line change
Expand Up @@ -671,7 +671,7 @@ export async function metaDiffSync(req, res) {
if (change.relationType === RelationTypes.BELONGS_TO) {
const title = getUniqueColumnAliasName(
childModel.columns,
`${parentModel.title || parentModel.table_name}Read`
`${parentModel.title || parentModel.table_name}`
);
await Column.insert<LinkToAnotherRecordColumn>({
uidt: UITypes.LinkToAnotherRecord,
Expand All @@ -686,7 +686,7 @@ export async function metaDiffSync(req, res) {
} else if (change.relationType === RelationTypes.HAS_MANY) {
const title = getUniqueColumnAliasName(
childModel.columns,
`${childModel.title || childModel.table_name}List`
`${childModel.title || childModel.table_name} List`
);
await Column.insert<LinkToAnotherRecordColumn>({
uidt: UITypes.LinkToAnotherRecord,
Expand Down Expand Up @@ -785,7 +785,7 @@ export async function extractAndGenerateManyToManyRelations(
await Column.insert<LinkToAnotherRecordColumn>({
title: getUniqueColumnAliasName(
modelA.columns,
`${modelB.title}MMList`
`${modelB.title} List`
),
fk_model_id: modelA.id,
fk_related_model_id: modelB.id,
Expand All @@ -803,7 +803,7 @@ export async function extractAndGenerateManyToManyRelations(
await Column.insert<LinkToAnotherRecordColumn>({
title: getUniqueColumnAliasName(
modelB.columns,
`${modelA.title}MMList`
`${modelA.title} List`
),
fk_model_id: modelB.id,
fk_related_model_id: modelA.id,
Expand Down
15 changes: 13 additions & 2 deletions packages/nocodb/src/lib/meta/api/projectApis.ts
Original file line number Diff line number Diff line change
Expand Up @@ -95,6 +95,10 @@ async function projectCreate(req: Request<any, any>, res) {
projectBody.is_meta = false;
}

if (projectBody?.title.length > 50) {
NcError.badRequest('Project title exceeds 50 characters');
}

if (await Project.getByTitle(projectBody?.title)) {
NcError.badRequest('Project title already in use');
}
Expand Down Expand Up @@ -211,7 +215,7 @@ async function populateMeta(base: Base, project: Project): Promise<any> {
uidt: UITypes.LinkToAnotherRecord,
type: 'hm',
hm,
title: `${hm.title}List`
title: `${hm.title} List`
};
}),
...belongsTo.map(bt => {
Expand All @@ -226,7 +230,7 @@ async function populateMeta(base: Base, project: Project): Promise<any> {
uidt: UITypes.LinkToAnotherRecord,
type: 'bt',
bt,
title: `${bt.rtitle}Read`
title: `${bt.rtitle}`
};
})
];
Expand Down Expand Up @@ -412,6 +416,13 @@ export async function projectCost(req, res) {
cost = Math.min(120 * userCount, 36000);
}

Tele.event({
event: 'a:project:cost',
data: {
cost
}
});

res.json({ cost });
}

Expand Down
4 changes: 3 additions & 1 deletion packages/nocodb/src/lib/meta/api/projectUserApis.ts
Original file line number Diff line number Diff line change
Expand Up @@ -15,6 +15,7 @@ import NcPluginMgrv2 from '../helpers/NcPluginMgrv2';
import Noco from '../../Noco';
import { PluginCategory } from 'nocodb-sdk';
import { metaApiMetrics } from '../helpers/apiMetrics';
import { randomTokenString } from '../helpers/stringHelpers';

async function userList(req, res) {
res.json({
Expand Down Expand Up @@ -101,7 +102,8 @@ async function userInvite(req, res, next): Promise<any> {
invite_token,
invite_token_expires: new Date(Date.now() + 24 * 60 * 60 * 1000),
email,
roles: 'user'
roles: 'user',
token_version: randomTokenString()
});

// add user to project
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -166,6 +166,27 @@ export const getModelPaths = async (ctx: {
}
}
},
[`/api/v1/db/data/${ctx.orgs}/${ctx.projectName}/${ctx.tableName}/find-one`]: {
get: {
summary: `${ctx.tableName} find-one`,
operationId: 'db-table-row-find-one',
description: `Find first record matching the conditions.`,
tags: [ctx.tableName],
parameters: [fieldsParam, whereParam, sortParam],
responses: {
'200': {
description: 'OK',
content: {
'application/json': {
schema: {
$ref: `#/components/schemas/${ctx.tableName}Response`
}
}
}
}
}
}
},
[`/api/v1/db/data/${ctx.orgs}/${ctx.projectName}/${ctx.tableName}/groupby`]: {
get: {
summary: `${ctx.tableName} groupby`,
Expand Down
5 changes: 5 additions & 0 deletions packages/nocodb/src/lib/meta/api/userApi/helpers.ts
Original file line number Diff line number Diff line change
@@ -1,4 +1,5 @@
import * as jwt from 'jsonwebtoken';
import crypto from 'crypto';
import User from '../../../models/User';
import { NcConfig } from '../../../../interface/config';

Expand All @@ -16,3 +17,7 @@ export function genJwt(user: User, config: NcConfig) {
config.auth.jwt.options
);
}

export function randomTokenString(): string {
return crypto.randomBytes(40).toString('hex');
}
236 changes: 236 additions & 0 deletions packages/nocodb/src/lib/meta/api/userApi/initAdminFromEnv.ts
Original file line number Diff line number Diff line change
@@ -0,0 +1,236 @@
import User from '../../../models/User';
import { v4 as uuidv4 } from 'uuid';
import { promisify } from 'util';
import { Tele } from 'nc-help';

import bcrypt from 'bcryptjs';
import Noco from '../../../Noco';
import { CacheScope, MetaTable } from '../../../utils/globals';
import ProjectUser from '../../../models/ProjectUser';
import { validatePassword } from 'nocodb-sdk';
import boxen from 'boxen';
import NocoCache from '../../../cache/NocoCache';

const { isEmail } = require('validator');
const rolesLevel = { owner: 0, creator: 1, editor: 2, commenter: 3, viewer: 4 };

export default async function initAdminFromEnv(_ncMeta = Noco.ncMeta) {
if (process.env.NC_ADMIN_EMAIL && process.env.NC_ADMIN_PASSWORD) {
if (!isEmail(process.env.NC_ADMIN_EMAIL?.trim())) {
console.log(
'\n',
boxen(
`Provided admin email '${process.env.NC_ADMIN_EMAIL}' is not valid`,
{
title: 'Invalid admin email',
padding: 1,
borderStyle: 'double',
titleAlignment: 'center',
borderColor: 'red'
}
),
'\n'
);
process.exit(1);
}

const { valid, error, hint } = validatePassword(
process.env.NC_ADMIN_PASSWORD
);
if (!valid) {
console.log(
'\n',
boxen(`${error}${hint ? `\n\n${hint}` : ''}`, {
title: 'Invalid admin password',
padding: 1,
borderStyle: 'double',
titleAlignment: 'center',
borderColor: 'red'
}),
'\n'
);
process.exit(1);
}

let ncMeta;
try {
ncMeta = await _ncMeta.startTransaction();
const email = process.env.NC_ADMIN_EMAIL.toLowerCase().trim();

const salt = await promisify(bcrypt.genSalt)(10);
const password = await promisify(bcrypt.hash)(
process.env.NC_ADMIN_PASSWORD,
salt
);
const email_verification_token = uuidv4();

// if super admin not present
if (await User.isFirst(ncMeta)) {
const roles = 'user,super';

// roles = 'owner,creator,editor'
Tele.emit('evt', {
evt_type: 'project:invite',
count: 1
});

await User.insert(
{
firstname: '',
lastname: '',
email,
salt,
password,
email_verification_token,
roles
},
ncMeta
);
} else {
const salt = await promisify(bcrypt.genSalt)(10);
const password = await promisify(bcrypt.hash)(
process.env.NC_ADMIN_PASSWORD,
salt
);
const email_verification_token = uuidv4();
const superUser = await ncMeta.metaGet2(null, null, MetaTable.USERS, {
roles: 'user,super'
});

if (email !== superUser.email) {
// update admin email and password and migrate projects
// if user already present and associated with some project

// check user account already present with the new admin email
const existingUserWithNewEmail = await User.getByEmail(email, ncMeta);

if (existingUserWithNewEmail?.id) {
// get all project access belongs to the existing account
// and migrate to the admin account
const existingUserProjects = await ncMeta.metaList2(
null,
null,
MetaTable.PROJECT_USERS,
{
condition: { fk_user_id: existingUserWithNewEmail.id }
}
);

for (const existingUserProject of existingUserProjects) {
const userProject = await ProjectUser.get(
existingUserProject.project_id,
superUser.id,
ncMeta
);

// if admin user already have access to the project
// then update role based on the highest access level
if (userProject) {
if (
rolesLevel[userProject.roles] >
rolesLevel[existingUserProject.roles]
) {
await ProjectUser.update(
userProject.project_id,
superUser.id,
existingUserProject.roles,
ncMeta
);
}
} else {
// if super doesn't have access then add the access
await ProjectUser.insert(
{
...existingUserProject,
fk_user_id: superUser.id
},
ncMeta
);
}
// delete the old project access entry from DB
await ProjectUser.delete(
existingUserProject.project_id,
existingUserProject.fk_user_id,
ncMeta
);
}

// delete existing user
await ncMeta.metaDelete(
null,
null,
MetaTable.USERS,
existingUserWithNewEmail.id
);

// clear cache
await NocoCache.delAll(
CacheScope.USER,
`${existingUserWithNewEmail.email}___*`
);
await NocoCache.del(
`${CacheScope.USER}:${existingUserWithNewEmail.id}`
);
await NocoCache.del(
`${CacheScope.USER}:${existingUserWithNewEmail.email}`
);

// Update email and password of super admin account
await User.update(
superUser.id,
{
salt,
email,
password,
email_verification_token,
token_version: null,
refresh_token: null
},
ncMeta
);
} else {
// if email's are not different update the password and hash
await User.update(
superUser.id,
{
salt,
email,
password,
email_verification_token,
token_version: null,
refresh_token: null
},
ncMeta
);
}
} else {
const newPasswordHash = await promisify(bcrypt.hash)(
process.env.NC_ADMIN_PASSWORD,
superUser.salt
);

if (newPasswordHash !== superUser.password) {
// if email's are same and passwords are different
// then update the password and token version
await User.update(
superUser.id,
{
salt,
password,
email_verification_token,
token_version: null,
refresh_token: null
},
ncMeta
);
}
}
}
await ncMeta.commit();
} catch (e) {
console.log('Error occurred while updating/creating admin user');
console.log(e);
await ncMeta.rollback(e);
}
}
}
25 changes: 17 additions & 8 deletions packages/nocodb/src/lib/meta/api/userApi/initStrategies.ts
Original file line number Diff line number Diff line change
Expand Up @@ -2,17 +2,17 @@ import User from '../../../models/User';
import ProjectUser from '../../../models/ProjectUser';
import { promisify } from 'util';
import { Strategy as CustomStrategy } from 'passport-custom';

import { Strategy } from 'passport-jwt';
import passport from 'passport';
import { ExtractJwt } from 'passport-jwt';
import passportJWT from 'passport-jwt';
import { Strategy as AuthTokenStrategy } from 'passport-auth-token';
import { Strategy as GoogleStrategy } from 'passport-google-oauth20';
import { randomTokenString } from '../../helpers/stringHelpers';

const PassportLocalStrategy = require('passport-local').Strategy;
const ExtractJwt = passportJWT.ExtractJwt;
const JwtStrategy = passportJWT.Strategy;

const jwtOptions = {
expiresIn: process.env.NC_JWT_EXPIRES_IN ?? '10h',
jwtFromRequest: ExtractJwt.fromHeader('xc-auth')
};

Expand Down Expand Up @@ -83,7 +83,7 @@ export function initStrategies(router): void {
});

passport.use(
new Strategy(
new JwtStrategy(
{
secretOrKey: Noco.getConfig().auth.jwt.secret,
...jwtOptions,
Expand All @@ -102,15 +102,23 @@ export function initStrategies(router): void {
);

if (cachedVal) {
if (cachedVal.token_version !== jwtPayload.token_version) {
if (
!cachedVal.token_version ||
!jwtPayload.token_version ||
cachedVal.token_version !== jwtPayload.token_version
) {
return done(new Error('Token Expired. Please login again.'));
}
return done(null, cachedVal);
}

User.getByEmail(jwtPayload?.email)
.then(async user => {
if (user.token_version !== jwtPayload.token_version) {
if (
!user.token_version ||
!jwtPayload.token_version ||
user.token_version !== jwtPayload.token_version
) {
return done(new Error('Token Expired. Please login again.'));
}
if (req.ncProjectId) {
Expand Down Expand Up @@ -266,7 +274,8 @@ export function initStrategies(router): void {
password: '',
salt,
roles,
email_verified: true
email_verified: true,
token_version: randomTokenString()
});
return done(null, user);
}
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -54,7 +54,7 @@ export default `<!DOCTYPE html>
methods: {},
async created() {
try {
const valid = (await axios.post('<%- baseUrl %>/api/v1/db/auth/email/validate/' + this.token)).data;
const valid = (await axios.post('<%- baseUrl %>/api/v1/auth/email/validate/' + this.token)).data;
this.valid = !!valid;
} catch (e) {
this.valid = false;
Expand Down
74 changes: 61 additions & 13 deletions packages/nocodb/src/lib/meta/api/userApi/userApis.ts
Original file line number Diff line number Diff line change
@@ -1,5 +1,5 @@
import { Request, Response } from 'express';
import { TableType } from 'nocodb-sdk';
import { TableType, validatePassword } from 'nocodb-sdk';
import catchError, { NcError } from '../../helpers/catchError';
const { isEmail } = require('validator');
import * as ejs from 'ejs';
Expand All @@ -11,7 +11,6 @@ import { Tele } from 'nc-help';

const { v4: uuidv4 } = require('uuid');
import Audit from '../../../models/Audit';
import crypto from 'crypto';
import NcPluginMgrv2 from '../../helpers/NcPluginMgrv2';

import passport from 'passport';
Expand All @@ -20,6 +19,7 @@ import ncMetaAclMw from '../../helpers/ncMetaAclMw';
import { MetaTable } from '../../../utils/globals';
import Noco from '../../../Noco';
import { genJwt } from './helpers';
import { randomTokenString } from '../../helpers/stringHelpers';

export async function signup(req: Request, res: Response<TableType>) {
const {
Expand All @@ -31,6 +31,12 @@ export async function signup(req: Request, res: Response<TableType>) {
} = req.body;
let { password } = req.body;

// validate password and throw error if password is satisfying the conditions
const { valid, error } = validatePassword(password);
if (!valid) {
NcError.badRequest(`Password : ${error}`);
}

if (!isEmail(_email)) {
NcError.badRequest(`Invalid email`);
}
Expand Down Expand Up @@ -173,15 +179,14 @@ async function successfulSignIn({
await promisify((req as any).login.bind(req))(user);
const refreshToken = randomTokenString();

let token_version = user.token_version;
if (!token_version) {
token_version = randomTokenString();
if (!user.token_version) {
user.token_version = randomTokenString();
}

await User.update(user.id, {
refresh_token: refreshToken,
email: user.email,
token_version
token_version: user.token_version
});
setTokenCookie(res, refreshToken);

Expand Down Expand Up @@ -237,10 +242,6 @@ async function googleSignin(req, res, next) {
)(req, res, next);
}

function randomTokenString(): string {
return crypto.randomBytes(40).toString('hex');
}

function setTokenCookie(res, token): void {
// create http only cookie with refresh token that expires in 7 days
const cookieOptions = {
Expand All @@ -262,6 +263,13 @@ async function passwordChange(req: Request<any, any>, res): Promise<any> {
if (!currentPassword || !newPassword) {
return NcError.badRequest('Missing new/old password');
}

// validate password and throw error if password is satisfying the conditions
const { valid, error } = validatePassword(newPassword);
if (!valid) {
NcError.badRequest(`Password : ${error}`);
}

const user = await User.getByEmail((req as any).user.email);
const hashedPassword = await promisify(bcrypt.hash)(
currentPassword,
Expand Down Expand Up @@ -318,10 +326,10 @@ async function passwordForgot(req: Request<any, any>, res): Promise<any> {
subject: 'Password Reset Link',
text: `Visit following link to update your password : ${
(req as any).ncSiteUrl
}/api/v1/db/auth/password/reset/${token}.`,
}/api/v1/auth/password/reset/${token}.`,
html: ejs.render(template, {
resetLink:
(req as any).ncSiteUrl + `/api/v1/db/auth/password/reset/${token}`
(req as any).ncSiteUrl + `/api/v1/auth/password/reset/${token}`
})
})
);
Expand Down Expand Up @@ -381,6 +389,12 @@ async function passwordReset(req, res): Promise<any> {
NcError.badRequest('Email registered via social account');
}

// validate password and throw error if password is satisfying the conditions
const { valid, error } = validatePassword(req.body.password);
if (!valid) {
NcError.badRequest(`Password : ${error}`);
}

const salt = await promisify(bcrypt.genSalt)(10);
const password = await promisify(bcrypt.hash)(req.body.password, salt);

Expand Down Expand Up @@ -501,7 +515,7 @@ const mapRoutes = router => {
})(req, res, next)
);

// new API
// deprecated APIs
router.post('/api/v1/db/auth/user/signup', catchError(signup));
router.post('/api/v1/db/auth/user/signin', catchError(signin));
router.get(
Expand Down Expand Up @@ -534,5 +548,39 @@ const mapRoutes = router => {
'/api/v1/db/auth/password/reset/:tokenId',
catchError(renderPasswordReset)
);

// new API
router.post('/api/v1/auth/user/signup', catchError(signup));
router.post('/api/v1/auth/user/signin', catchError(signin));
router.get(
'/api/v1/auth/user/me',
extractProjectIdAndAuthenticate,
catchError(me)
);
router.post('/api/v1/auth/password/forgot', catchError(passwordForgot));
router.post(
'/api/v1/auth/token/validate/:tokenId',
catchError(tokenValidate)
);
router.post(
'/api/v1/auth/password/reset/:tokenId',
catchError(passwordReset)
);
router.post(
'/api/v1/auth/email/validate/:tokenId',
catchError(emailVerification)
);
router.post(
'/api/v1/auth/password/change',
ncMetaAclMw(passwordChange, 'passwordChange')
);
router.post(
'/api/v1/auth/token/refresh',
ncMetaAclMw(refreshToken, 'refreshToken')
);
router.get(
'/api/v1/auth/password/reset/:tokenId',
catchError(renderPasswordReset)
);
};
export { mapRoutes as userApis };
33 changes: 31 additions & 2 deletions packages/nocodb/src/lib/meta/api/utilApis.ts
Original file line number Diff line number Diff line change
Expand Up @@ -60,8 +60,17 @@ export async function releaseVersion(_req: Request, res: Response) {
res.json(result);
}

export async function axiosRequestMake(req: Request, res: Response) {
export async function appHealth(_: Request, res: Response) {
res.json({
message: 'OK',
timestamp: Date.now(),
uptime: process.uptime()
});
}

async function _axiosRequestMake(req: Request, res: Response) {
const { apiMeta } = req.body;

if (apiMeta?.body) {
try {
apiMeta.body = JSON.parse(apiMeta.body);
Expand Down Expand Up @@ -106,12 +115,32 @@ export async function axiosRequestMake(req: Request, res: Response) {
return res.json(data?.data);
}

export async function axiosRequestMake(req: Request, res: Response) {
const {
apiMeta: { url }
} = req.body;
const isExcelImport = /.*\.(xls|xlsx|xlsm|ods|ots)/;
const isCSVImport = /.*\.(csv)/;
const ipBlockList = /(10)(\.([2]([0-5][0-5]|[01234][6-9])|[1][0-9][0-9]|[1-9][0-9]|[0-9])){3}|(172)\.(1[6-9]|2[0-9]|3[0-1])(\.(2[0-4][0-9]|25[0-5]|[1][0-9][0-9]|[1-9][0-9]|[0-9])){2}|(192)\.(168)(\.(25[0-5]|2[0-4][0-9]|1[0-9][0-9]|[1-9][0-9]|[0-9])){2}|(0.0.0.0)|localhost?/g;
if (
ipBlockList.test(url) ||
(!isCSVImport.test(url) && !isExcelImport.test(url))
) {
return res.json({});
}
if (isCSVImport || isExcelImport) {
req.body.apiMeta.responseType = 'arraybuffer';
}
return await _axiosRequestMake(req, res);
}

export default router => {
router.post(
'/api/v1/db/meta/connection/test',
ncMetaAclMw(testConnection, 'testConnection')
);
router.get('/api/v1/db/meta/nocodb/info', catchError(appInfo));
router.get('/api/v1/db/meta/nocodb/version', catchError(releaseVersion));
router.post('/api/v1/db/meta/axiosRequestMake', catchError(axiosRequestMake));
router.get('/api/v1/version', catchError(releaseVersion));
router.get('/api/v1/health', catchError(appHealth));
};
66 changes: 48 additions & 18 deletions packages/nocodb/src/lib/meta/helpers/NcPluginMgrv2.ts
Original file line number Diff line number Diff line change
Expand Up @@ -31,6 +31,7 @@ import Noco from '../../Noco';
import Local from '../../v1-legacy/plugins/adapters/storage/Local';
import { MetaTable } from '../../utils/globals';
import { PluginCategory } from 'nocodb-sdk';
import Plugin from '../../models/Plugin';

const defaultPlugins = [
SlackPluginConfig,
Expand Down Expand Up @@ -97,25 +98,54 @@ class NcPluginMgrv2 {
pluginConfig.id
);
}
}
await this.initPluginsFromEnv();
}

/* init only the active plugins */
// if (pluginConfig?.active) {
// const tempPlugin = new plugin.builder(this.app, plugin);
//
// this.activePlugins.push(tempPlugin);
//
// if (pluginConfig?.input) {
// pluginConfig.input = JSON.parse(pluginConfig.input);
// }
//
// try {
// await tempPlugin.init(pluginConfig?.input);
// } catch (e) {
// console.log(
// `Plugin(${plugin?.title}) initialization failed : ${e.message}`
// );
// }
// }
private static async initPluginsFromEnv() {
/*
* NC_S3_BUCKET_NAME
* NC_S3_REGION
* NC_S3_ACCESS_KEY
* NC_S3_ACCESS_SECRET
* */

if (
process.env.NC_S3_BUCKET_NAME &&
process.env.NC_S3_REGION &&
process.env.NC_S3_ACCESS_KEY &&
process.env.NC_S3_ACCESS_SECRET
) {
const s3Plugin = await Plugin.getPluginByTitle(S3PluginConfig.title);
await Plugin.update(s3Plugin.id, {
active: true,
input: JSON.stringify({
bucket: process.env.NC_S3_BUCKET_NAME,
region: process.env.NC_S3_REGION,
access_key: process.env.NC_S3_ACCESS_KEY,
access_secret: process.env.NC_S3_ACCESS_SECRET
})
});
}

if (
process.env.NC_SMTP_FROM &&
process.env.NC_SMTP_HOST &&
process.env.NC_SMTP_PORT
) {
const smtpPlugin = await Plugin.getPluginByTitle(SMTPPluginConfig.title);
await Plugin.update(smtpPlugin.id, {
active: true,
input: JSON.stringify({
from: process.env.NC_SMTP_FROM,
host: process.env.NC_SMTP_HOST,
port: process.env.NC_SMTP_PORT,
username: process.env.NC_SMTP_USERNAME,
password: process.env.NC_SMTP_PASSWORD,
secure: process.env.NC_SMTP_SECURE,
ignoreTLS: process.env.NC_SMTP_IGNORE_TLS
})
});
}
}

Expand Down
5 changes: 5 additions & 0 deletions packages/nocodb/src/lib/meta/helpers/stringHelpers.ts
Original file line number Diff line number Diff line change
@@ -0,0 +1,5 @@
import crypto from 'crypto';

export function randomTokenString(): string {
return crypto.randomBytes(40).toString('hex');
}
1 change: 1 addition & 0 deletions packages/nocodb/src/lib/models/Filter.ts
Original file line number Diff line number Diff line change
Expand Up @@ -23,6 +23,7 @@ export default class Filter {
comparison_op?:
| 'eq'
| 'neq'
| 'not'
| 'like'
| 'nlike'
| 'empty'
Expand Down
10 changes: 6 additions & 4 deletions packages/nocodb/src/lib/models/Model.ts
Original file line number Diff line number Diff line change
Expand Up @@ -20,6 +20,7 @@ import {
import View from './View';
import { NcError } from '../meta/helpers/catchError';
import Audit from './Audit';
import { sanitize } from '../db/sql-data-mapper/lib/sql/helpers/sanitize';

export default class Model implements TableType {
copy_enabled: boolean;
Expand Down Expand Up @@ -399,13 +400,14 @@ export default class Model implements TableType {
return true;
}

async mapAliasToColumn(data, sanitize = v => v) {
async mapAliasToColumn(data) {
const insertObj = {};
for (const col of await this.getColumns()) {
if (isVirtualCol(col)) continue;
const val =
data?.[sanitize(col.column_name)] ?? data?.[sanitize(col.title)];
if (val !== undefined) insertObj[sanitize(col.column_name)] = val;
const val = data?.[col.column_name] ?? data?.[col.title];
if (val !== undefined) {
insertObj[sanitize(col.column_name)] = val;
}
}
return insertObj;
}
Expand Down
4 changes: 2 additions & 2 deletions packages/nocodb/src/lib/models/Plugin.ts
Original file line number Diff line number Diff line change
Expand Up @@ -91,15 +91,15 @@ export default class Plugin implements PluginType {
/**
* get plugin by title
*/
public static async getPluginByTitle(title: string) {
public static async getPluginByTitle(title: string, ncMeta = Noco.ncMeta) {
let plugin =
title &&
(await NocoCache.get(
`${CacheScope.PLUGIN}:${title}`,
CacheGetType.TYPE_OBJECT
));
if (!plugin) {
plugin = await Noco.ncMeta.metaGet2(null, null, MetaTable.PLUGIN, {
plugin = await ncMeta.metaGet2(null, null, MetaTable.PLUGIN, {
title
});
await NocoCache.set(`${CacheScope.PLUGIN}:${title}`, plugin);
Expand Down
3 changes: 3 additions & 0 deletions packages/nocodb/src/lib/models/User.ts
Original file line number Diff line number Diff line change
Expand Up @@ -84,6 +84,9 @@ export default class User implements UserType {

if (updateObj.email) {
updateObj.email = updateObj.email.toLowerCase();
} else {
// set email prop to avoid generation of invalid cache key
updateObj.email = (await this.get(id, ncMeta))?.email?.toLowerCase();
}
// get existing cache
const keys = [
Expand Down
29 changes: 29 additions & 0 deletions packages/nocodb/src/lib/models/View.ts
Original file line number Diff line number Diff line change
Expand Up @@ -157,6 +157,32 @@ export default class View implements ViewType {
return viewId && this.get(viewId?.id || viewId);
}

public static async getDefaultView(
fk_model_id: string,
ncMeta = Noco.ncMeta
) {
let view =
fk_model_id &&
(await NocoCache.get(
`${CacheScope.VIEW}:${fk_model_id}:default`,
CacheGetType.TYPE_OBJECT
));
if (!view) {
view = await ncMeta.metaGet2(
null,
null,
MetaTable.VIEWS,
{
fk_model_id,
is_default: 1
},
null
);
await NocoCache.set(`${CacheScope.VIEW}:${fk_model_id}:default`, view);
}
return view && new View(view);
}

public static async list(modelId: string, ncMeta = Noco.ncMeta) {
let viewsList = await NocoCache.getList(CacheScope.VIEW, [modelId]);
if (!viewsList.length) {
Expand Down Expand Up @@ -666,6 +692,9 @@ export default class View implements ViewType {
if (o) {
// update data
o = { ...o, ...updateObj };
if (o.is_default) {
await NocoCache.set(`${CacheScope.VIEW}:${o.fk_model_id}:default`, o);
}
// set cache
await NocoCache.set(key, o);
}
Expand Down
23 changes: 23 additions & 0 deletions packages/nocodb/tests/export-import/ReadMe.md
Original file line number Diff line number Diff line change
@@ -0,0 +1,23 @@
## config.json
{
"srcProject": "sample",
"dstProject": "sample-copy",
"baseURL": "http://localhost:8080",
"xc-auth": "Copy Auth Token"
}
- baseURL & xc-auth are common configurations for both import & export

## Export
- `srcProject`: specify source project name to be exported.
- Export JSON file will be created as `srcProject.json`
- execute
`cd packages/nocodb/tests/export-import`
`node exportSchema.js`

## Import
- `srcProject`: specify JSON file name to be imported (sans .JSON suffix)
- `dstProject`: new project name to be imported as
- Data will also be imported if `srcProject` exists in NocoDB. Note that, data import isn't via exported JSON
- execute
`cd packages/nocodb/tests/export-import`
`node importSchema.js`
6 changes: 6 additions & 0 deletions packages/nocodb/tests/export-import/config.json
Original file line number Diff line number Diff line change
@@ -0,0 +1,6 @@
{
"srcProject": "sample",
"dstProject": "sample-copy",
"baseURL": "http://localhost:8080",
"xc-auth": "Copy Auth Token"
}
297 changes: 297 additions & 0 deletions packages/nocodb/tests/export-import/exportSchema.js
Original file line number Diff line number Diff line change
@@ -0,0 +1,297 @@
const Api = require('nocodb-sdk').Api;
const { UITypes } = require('nocodb-sdk');
const jsonfile = require('jsonfile');

const GRID = 3, GALLERY = 2, FORM = 1;

let ncMap = { /* id: name <string> */ };
let tblSchema = [];
let api = {};
let viewStore = { columns: {}, sort: {}, filter: {} };

let inputConfig = jsonfile.readFileSync(`config.json`)
let ncConfig = {
projectName: inputConfig.srcProject,
baseURL: inputConfig.baseURL,
headers: {
'xc-auth': `${inputConfig["xc-auth"]}`
}
};


// helper routines
// remove objects containing 0/ false/ null
// fixme: how to handle when cdf (default value) is configured as 0/ null/ false
function removeEmpty(obj) {
return Object.fromEntries(
Object.entries(obj)
.filter(([_, v]) => v != null && v != 0 && v != false)
.map(([k, v]) => [k, v === Object(v) ? removeEmpty(v) : v])
);
}

function addColumnSpecificData(c) {
// pick required fields to proceed further
let col = removeEmpty(
(({ id, title, column_name, uidt, dt, pk, pv, rqd, dtxp, system }) => ({
id,
title,
column_name,
uidt,
dt,
pk,
pv,
rqd,
dtxp,
system
}))(c)
);

switch (c.uidt) {
case UITypes.Formula:
col.formula = c.colOptions.formula;
col.formula_raw = c.colOptions.formula_raw;
break;
case UITypes.LinkToAnotherRecord:
col[`colOptions`] = {
fk_model_id: c.fk_model_id,
fk_related_model_id: c.colOptions.fk_related_model_id,
fk_child_column_id: c.colOptions.fk_child_column_id,
fk_parent_column_id: c.colOptions.fk_parent_column_id,
type: c.colOptions.type
};
break;
case UITypes.Lookup:
col[`colOptions`] = {
fk_model_id: c.fk_model_id,
fk_relation_column_id: c.colOptions.fk_relation_column_id,
fk_lookup_column_id: c.colOptions.fk_lookup_column_id
};
break;
case UITypes.Rollup:
col[`colOptions`] = {
fk_model_id: c.fk_model_id,
fk_relation_column_id: c.colOptions.fk_relation_column_id,
fk_rollup_column_id: c.colOptions.fk_rollup_column_id,
rollup_function: c.colOptions.rollup_function
};
break;
}

return col;
}

function addViewDetails(v) {
// pick required fields to proceed further
let view = (({ id, title, type, show_system_fields, lock_type, order }) => ({
id,
title,
type,
show_system_fields,
lock_type,
order
}))(v);

// form view
if (v.type === FORM) {
view.property = (({
heading,
subheading,
success_msg,
redirect_after_secs,
email,
submit_another_form,
show_blank_form
}) => ({
heading,
subheading,
success_msg,
redirect_after_secs,
email,
submit_another_form,
show_blank_form
}))(v.view);
}

// gallery view
else if (v.type === GALLERY) {
view.property = {
fk_cover_image_col_id: ncMap[v.view.fk_cover_image_col_id]
};
}

// gallery view doesn't share column information in api yet
if (v.type !== GALLERY) {
if (v.type === GRID)
view.columns = viewStore.columns[v.id].map(a =>
(({ id, width, order, show }) => ({ id, width, order, show }))(a)
);
if (v.type === FORM)
view.columns = viewStore.columns[v.id].map(a =>
(({ id, order, show, label, help, description, required }) => ({
id,
order,
show,
label,
help,
description,
required
}))(a)
);

for (let i = 0; i < view.columns?.length; i++)
view.columns[i].title = ncMap[viewStore.columns[v.id][i].id];

// skip hm & mm columns
view.columns = view.columns
?.filter(a => a.title?.includes('_nc_m2m_') === false)
.filter(a => a.title?.includes('nc_') === false);
}

// filter & sort configurations
if (v.type !== FORM) {
view.sort = viewStore.sort[v.id].map(a =>
(({ fk_column_id, direction, order }) => ({
fk_column_id,
direction,
order
}))(a)
);
view.filter = viewStore.filter[v.id].map(a =>
(({ fk_column_id, logical_op, comparison_op, value, order }) => ({
fk_column_id,
logical_op,
comparison_op,
value,
order
}))(a)
);
}
return view;
}

// view data stored as is for quick access
async function storeViewDetails(tableId) {
// read view data for each table
let viewList = await api.dbView.list(tableId);
for (let j = 0; j < viewList.list.length; j++) {
let v = viewList.list[j];
let viewDetails = [];

// invoke view specific read to populate columns information
if (v.type === FORM) viewDetails = (await api.dbView.formRead(v.id)).columns;
else if (v.type === GALLERY) viewDetails = await api.dbView.galleryRead(v.id);
else if (v.type === GRID) viewDetails = await api.dbView.gridColumnsList(v.id);
viewStore.columns[v.id] = viewDetails;

// populate sort information
let vSort = await api.dbTableSort.list(v.id);
viewStore.sort[v.id] = vSort.sorts.list;

let vFilter = await api.dbTableFilter.read(v.id);
viewStore.filter[v.id] = vFilter;
}
}

// mapping table for quick information access
// store maps for tableId, columnId, viewColumnId & viewId to their names
async function generateMapTbl(pId) {
const tblList = await api.dbTable.list(pId);

for (let i = 0; i < tblList.list.length; i++) {
let tblId = tblList.list[i].id;
let tbl = await api.dbTable.read(tblId);

// table ID <> name
ncMap[tblId] = tbl.title;

// column ID <> name
tbl.columns.map(x => (ncMap[x.id] = x.title));

// view ID <> name
tbl.views.map(x => (ncMap[x.id] = x.tn));

for (let i = 0; i < tbl.views.length; i++) {
let x = tbl.views[i];
let viewColumns = [];
if (x.type === FORM) viewColumns = (await api.dbView.formRead(x.id)).columns;
else if (x.type === GALLERY)
viewColumns = (await api.dbView.galleryRead(x.id)).columns;
else if (x.type === GRID) viewColumns = await api.dbView.gridColumnsList(x.id);

// view column ID <> name
viewColumns?.map(a => (ncMap[a.id] = ncMap[a.fk_column_id]));
}
}
}

// main
//
async function exportSchema() {
api = new Api(ncConfig);

// fetch project details (id et.al)
const x = await api.project.list();
const p = x.list.find(a => a.title === ncConfig.projectName);

await generateMapTbl(p.id);

// read project
const tblList = await api.dbTable.list(p.id);

// for each table
for (let i = 0; i < tblList.list.length; i++) {
let tblId = tblList.list[i].id;
await storeViewDetails(tblId);

let tbl = await api.dbTable.read(tblId);

// prepare schema
let tSchema = {
id: tbl.id,
title: tbl.title,
table_name: tbl?.table_name,
columns: [...tbl.columns.map(c => addColumnSpecificData(c))]
.filter(a => a.title.includes('_nc_m2m_') === false) // mm
.filter(a => a.title.includes(p.prefix) === false) // hm
.filter(
a => !(a?.system === 1 && a.uidt === UITypes.LinkToAnotherRecord)
),
views: [...tbl.views.map(v => addViewDetails(v))]
};
tblSchema.push(tSchema);
}
}

(async () => {
await exportSchema();
jsonfile.writeFileSync(
`${ncConfig.projectName.replace(/ /g, '_')}.json`,
tblSchema,
{ spaces: 2 }
);
})().catch(e => {
console.log(e);
});

/**
* @copyright Copyright (c) 2021, Xgene Cloud Ltd
*
* @author Raju Udava <sivadstala@gmail.com>
*
* @license GNU AGPL version 3 or any later version
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
*/
537 changes: 537 additions & 0 deletions packages/nocodb/tests/export-import/importSchema.js

Large diffs are not rendered by default.

4,759 changes: 4,759 additions & 0 deletions packages/nocodb/tests/pg-cy-quick/01-cy-quick.sql

Large diffs are not rendered by default.

5 changes: 3 additions & 2 deletions scripts/cypress/cypress.json
Original file line number Diff line number Diff line change
Expand Up @@ -20,7 +20,8 @@
"test/pg-restTableOps.js",
"test/pg-restViews.js",
"test/pg-restRoles.js",
"test/pg-restMisc.js"
"test/pg-restMisc.js",
"common/9a_QuickTest.js"
],
"defaultCommandTimeout": 13000,
"pageLoadTimeout": 600000,
Expand All @@ -47,7 +48,7 @@
"screenshot": false,
"airtable": {
"apiKey": "keyn1MR87qgyUsYg4",
"sharedBase": "https://airtable.com/shrkSQdtKNzUfAbIY"
"sharedBase": "https://airtable.com/shr4z0qmh6dg5s3eB"
}
},
"fixturesFolder": "scripts/cypress/fixtures",
Expand Down
17 changes: 17 additions & 0 deletions scripts/cypress/docker-compose-pg-cy-quick.yml
Original file line number Diff line number Diff line change
@@ -0,0 +1,17 @@
version: "2.1"

services:
pg96:
image: postgres:9.6
restart: always
environment:
POSTGRES_PASSWORD: password
ports:
- 5432:5432
volumes:
- ../../packages/nocodb/tests/pg-cy-quick:/docker-entrypoint-initdb.d
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 10s
timeout: 5s
retries: 5
Binary file not shown.
6 changes: 3 additions & 3 deletions scripts/cypress/integration/common/1a_table_operations.js
Original file line number Diff line number Diff line change
Expand Up @@ -80,9 +80,9 @@ export const genTest = (apiType, dbType) => {
// 4a. Address table, has many field
cy.openTableTab("Address", 25);

mainPage.getCell("CityRead", 1).scrollIntoView();
mainPage.getCell("City", 1).scrollIntoView();
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.find(".name")
.contains("Lethbridge")
.should("exist");
Expand All @@ -92,7 +92,7 @@ export const genTest = (apiType, dbType) => {
cy.openTableTab("Country", 25);

mainPage
.getCell("CityList", 1)
.getCell("City List", 1)
.find(".name")
.contains("Kabul")
.should("exist");
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -23,7 +23,7 @@ export const genTest = (apiType, dbType) => {
/*
Original order of list items
Actor, Address, Category, City, Country, Customer, FIlm, FilmText, Language, Payment, Rental Staff
ActorInfo, CustomerList, FilmList, NiceButSlowerFilmList, SalesByFilmCategory, SalesByStore, StaffList
ActorInfo, Customer List, Film List, NiceButSlowerFilm List, SalesByFilmCategory, SalesByStore, Staff List
*/

it(`Table & SQL View list, Drag/drop`, () => {
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -18,7 +18,7 @@ export const genTest = (apiType, dbType) => {
/*
Original order of list items
Actor, Address, Category, City, Country, Customer, FIlm, FilmText, Language, Payment, Rental Staff
ActorInfo, CustomerList, FilmList, NiceButSlowerFilmList, SalesByFilmCategory, SalesByStore, StaffList
ActorInfo, Customer List, Film List, NiceButSlowerFilm List, SalesByFilmCategory, SalesByStore, Staff List
*/

before(() => {
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -23,12 +23,12 @@ export const genTest = (apiType, dbType) => {

it("Expand belongs-to column", () => {
// expand first row
cy.get('td[data-col="CityList"] div:visible', {
cy.get('td[data-col="City List"] div:visible', {
timeout: 12000,
})
.first()
.click();
cy.get('td[data-col="CityList"] div .mdi-arrow-expand:visible')
cy.get('td[data-col="City List"] div .mdi-arrow-expand:visible')
.first()
.click();

Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -23,10 +23,10 @@ export const genTest = (apiType, dbType) => {

it("Expand m2m column", () => {
// expand first row
cy.get('td[data-col="FilmMMList"] div', { timeout: 12000 })
cy.get('td[data-col="Film List"] div', { timeout: 12000 })
.first()
.click({ force: true });
cy.get('td[data-col="FilmMMList"] div .mdi-arrow-expand')
cy.get('td[data-col="Film List"] div .mdi-arrow-expand')
.first()
.click({ force: true });

Expand Down
275 changes: 275 additions & 0 deletions scripts/cypress/integration/common/3e_duration_column.js
Original file line number Diff line number Diff line change
@@ -0,0 +1,275 @@
import { mainPage } from "../../support/page_objects/mainPage";
import {
isTestSuiteActive,
} from "../../support/page_objects/projectConstants";

export const genTest = (apiType, dbType) => {
if (!isTestSuiteActive(apiType, dbType)) return;

describe(`${apiType.toUpperCase()} api - DURATION`, () => {
const tableName = "DurationTable";

// to retrieve few v-input nodes from their label
//
const fetchParentFromLabel = (label) => {
cy.get("label").contains(label).parents(".v-input").click();
};

// Run once before test- create table
//
before(() => {
mainPage.tabReset();
cy.createTable(tableName);
});

after(() => {
cy.deleteTable(tableName);
});

// Routine to create a new look up column
//
const addDurationColumn = (columnName, durationFormat) => {
// (+) icon at end of column header (to add a new column)
// opens up a pop up window
//
cy.get(".new-column-header").click();

// Column name
cy.get(".nc-column-name-input input").clear().type(`${columnName}`);

// Column data type
cy.get(".nc-ui-dt-dropdown").click();
cy.getActiveMenu().contains("Duration").click();

// Configure Child table & column names
fetchParentFromLabel("Duration Format");
cy.getActiveMenu().contains(durationFormat).click();

// click on Save
cy.get(".nc-col-create-or-edit-card").contains("Save").click();

// Verify if column exists.
//
cy.get(`th:contains(${columnName})`).should("exist");
};

// routine to delete column
//
const deleteColumnByName = (columnName) => {
// verify if column exists before delete
cy.get(`th:contains(${columnName})`).should("exist");

// delete opiton visible on mouse-over
cy.get(`th:contains(${columnName}) .mdi-menu-down`)
.trigger("mouseover")
.click();

// delete/ confirm on pop-up
cy.get(".nc-column-delete").click();
cy.getActiveModal().find("button:contains(Confirm)").click();

// validate if deleted (column shouldnt exist)
cy.get(`th:contains(${columnName})`).should("not.exist");
};

// routine to edit column
//
const editColumnByName = (oldName, newName, newDurationFormat) => {
// verify if column exists before delete
cy.get(`th:contains(${oldName})`).should("exist");

// delete opiton visible on mouse-over
cy.get(`th:contains(${oldName}) .mdi-menu-down`)
.trigger("mouseover")
.click();

// edit/ save on pop-up
cy.get(".nc-column-edit").click();
cy.get(".nc-column-name-input input").clear().type(newName);

// Configure Child table & column names
fetchParentFromLabel("Duration Format");
cy.getActiveMenu().contains(newDurationFormat).click();

cy.get(".nc-col-create-or-edit-card")
.contains("Save")
.click({ force: true });

cy.toastWait("Duration column updated successfully");

// validate if deleted (column shouldnt exist)
cy.get(`th:contains(${oldName})`).should("not.exist");
cy.get(`th:contains(${newName})`).should("exist");
};

const addDurationData = (colName, index, cellValue, expectedValue, isNewRow = false) => {
if (isNewRow) {
cy.get(".nc-add-new-row-btn:visible").should("exist");
cy.wait(500)
cy.get(".nc-add-new-row-btn").click({ force: true });
} else {
mainPage.getRow(index).find(".nc-row-expand-icon").click({ force: true });
}
cy.get(".duration-cell-wrapper > input").first().should('exist').type(cellValue);
cy.getActiveModal().find("button").contains("Save row").click({ force: true });
cy.toastWait("Row updated successfully");
mainPage.getCell(colName, index).find('input').then(($e) => {
expect($e[0].value).to.equal(expectedValue)
})
}

///////////////////////////////////////////////////
// Test case
{
// Duration: h:mm
it("Duration: h:mm", () => {
addDurationColumn("NC_DURATION_0", "h:mm (e.g. 1:23)");
addDurationData("NC_DURATION_0", 1, "1:30", "01:30", true);
addDurationData("NC_DURATION_0", 2, "30", "00:30", true);
addDurationData("NC_DURATION_0", 3, "60", "01:00", true);
addDurationData("NC_DURATION_0", 4, "80", "01:20", true);
addDurationData("NC_DURATION_0", 5, "12:34", "12:34", true);
addDurationData("NC_DURATION_0", 6, "15:130", "17:10", true);
addDurationData("NC_DURATION_0", 7, "123123", "2052:03", true);
});

it("Duration: Edit Column NC_DURATION_0", () => {
editColumnByName(
"NC_DURATION_0",
"NC_DURATION_EDITED_0",
"h:mm:ss (e.g. 3:45, 1:23:40)"
);
});

it("Duration: Delete column", () => {
deleteColumnByName("NC_DURATION_EDITED_0");
});
}

{
// Duration: h:mm:ss
it("Duration: h:mm:ss", () => {
addDurationColumn("NC_DURATION_1", "h:mm:ss (e.g. 3:45, 1:23:40)");
addDurationData("NC_DURATION_1", 1, "11:22:33", "11:22:33");
addDurationData("NC_DURATION_1", 2, "1234", "00:20:34");
addDurationData("NC_DURATION_1", 3, "50", "00:00:50");
addDurationData("NC_DURATION_1", 4, "1:1111", "00:19:31");
addDurationData("NC_DURATION_1", 5, "1:11:1111", "01:29:31");
addDurationData("NC_DURATION_1", 6, "15:130", "00:17:10");
addDurationData("NC_DURATION_1", 7, "123123", "34:12:03");
});

it("Duration: Edit Column NC_DURATION_1", () => {
editColumnByName(
"NC_DURATION_1",
"NC_DURATION_EDITED_1",
"h:mm:ss.s (e.g. 3:34.6, 1:23:40.0)"
);
});

it("Duration: Delete column", () => {
deleteColumnByName("NC_DURATION_EDITED_1");
});
}

{
// h:mm:ss.s
it("Duration: h:mm:ss.s", () => {
addDurationColumn("NC_DURATION_2", "h:mm:ss.s (e.g. 3:34.6, 1:23:40.0)");
addDurationData("NC_DURATION_2", 1, "1234", "00:20:34.0");
addDurationData("NC_DURATION_2", 2, "12:34", "00:12:34.0");
addDurationData("NC_DURATION_2", 3, "12:34:56", "12:34:56.0");
addDurationData("NC_DURATION_2", 4, "12:34:999", "12:50:39.0");
addDurationData("NC_DURATION_2", 5, "12:999:56", "28:39:56.0");
addDurationData("NC_DURATION_2", 6, "12:34:56.12", "12:34:56.1");
addDurationData("NC_DURATION_2", 7, "12:34:56.199", "12:34:56.2");
});

it("Duration: Edit Column NC_DURATION_2", () => {
editColumnByName(
"NC_DURATION_2",
"NC_DURATION_EDITED_2",
"h:mm:ss (e.g. 3:45, 1:23:40)"
);
});

it("Duration: Delete column", () => {
deleteColumnByName("NC_DURATION_EDITED_2");
});
}

{
// h:mm:ss.ss
it("Duration: h:mm:ss.ss", () => {
addDurationColumn("NC_DURATION_3", "h:mm:ss.ss (e.g. 3.45.67, 1:23:40.00)");
addDurationData("NC_DURATION_3", 1, "1234", "00:20:34.00");
addDurationData("NC_DURATION_3", 2, "12:34", "00:12:34.00");
addDurationData("NC_DURATION_3", 3, "12:34:56", "12:34:56.00");
addDurationData("NC_DURATION_3", 4, "12:34:999", "12:50:39.00");
addDurationData("NC_DURATION_3", 5, "12:999:56", "28:39:56.00");
addDurationData("NC_DURATION_3", 6, "12:34:56.12", "12:34:56.12");
addDurationData("NC_DURATION_3", 7, "12:34:56.199", "12:34:56.20");
});

it("Duration: Edit Column NC_DURATION_3", () => {
editColumnByName(
"NC_DURATION_3",
"NC_DURATION_EDITED_3",
"h:mm:ss.ss (e.g. 3.45.67, 1:23:40.00)"
);
});

it("Duration: Delete column", () => {
deleteColumnByName("NC_DURATION_EDITED_3");
});
}

{
// h:mm:ss.sss
it("Duration: h:mm:ss.sss", () => {
addDurationColumn("NC_DURATION_4", "h:mm:ss.sss (e.g. 3.45.678, 1:23:40.000)");
addDurationData("NC_DURATION_4", 1, "1234", "00:20:34.000");
addDurationData("NC_DURATION_4", 2, "12:34", "00:12:34.000");
addDurationData("NC_DURATION_4", 3, "12:34:56", "12:34:56.000");
addDurationData("NC_DURATION_4", 4, "12:34:999", "12:50:39.000");
addDurationData("NC_DURATION_4", 5, "12:999:56", "28:39:56.000");
addDurationData("NC_DURATION_4", 6, "12:34:56.12", "12:34:56.012");
addDurationData("NC_DURATION_4", 7, "12:34:56.199", "12:34:56.199");
});

it("Duration: Edit Column NC_DURATION_4", () => {
editColumnByName(
"NC_DURATION_4",
"NC_DURATION_EDITED_4",
"h:mm (e.g. 1:23)"
);
});

it("Duration: Delete column", () => {
deleteColumnByName("NC_DURATION_EDITED_4");
});
}
});
};

/**
* @copyright Copyright (c) 2021, Xgene Cloud Ltd
*
* @author Wing-Kam Wong <wingkwong.code@gmail.com>
*
* @license GNU AGPL version 3 or any later version
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
*/
Original file line number Diff line number Diff line change
Expand Up @@ -119,7 +119,7 @@ export const genTest = (apiType, dbType) => {
.should("exist");
cy.get(".nc-field-wrapper")
.eq(1)
.contains("CityList")
.contains("City List")
.should("exist");
cy.get(".nc-field-wrapper")
.eq(2)
Expand Down
Original file line number Diff line number Diff line change
Expand Up @@ -77,18 +77,18 @@ export const genTest = (apiType, dbType) => {

// check if add/ expand options available for 'has many' column type
mainPage
.getCell("CityList", 1)
.getCell("City List", 1)
.click()
.find("button.mdi-plus")
.should(`${vString}exist`);
mainPage
.getCell("CityList", 1)
.getCell("City List", 1)
.click()
.find("button.mdi-arrow-expand")
.should(`${vString}exist`);

// update row option (right click) - should not be available for Lock view
mainPage.getCell("CityList", 1).rightclick();
mainPage.getCell("City List", 1).rightclick();
cy.get(".menuable__content__active").should(
`${vString}be.visible`
);
Expand Down
6 changes: 3 additions & 3 deletions scripts/cypress/integration/common/4e_form_view_share.js
Original file line number Diff line number Diff line change
Expand Up @@ -72,7 +72,7 @@ export const genTest = (apiType, dbType) => {
// "#data-table-form-City"
// );

cy.get('[title="AddressList"]').drag(".nc-drag-n-drop-to-hide");
cy.get('[title="Address List"]').drag(".nc-drag-n-drop-to-hide");

cy.get(".nc-form > .mx-auto")
.find('[type="checkbox"]')
Expand Down Expand Up @@ -131,8 +131,8 @@ export const genTest = (apiType, dbType) => {
// all fields, barring removed field should exist
cy.get('[title="City"]').should("exist");
cy.get('[title="LastUpdate"]').should("exist");
cy.get('[title="CountryRead"]').should("exist");
cy.get('[title="AddressList"]').should("not.exist");
cy.get('[title="Country"]').should("exist");
cy.get('[title="Address List"]').should("not.exist");

// order of LastUpdate & City field is retained
cy.get(".nc-field-wrapper")
Expand Down
30 changes: 15 additions & 15 deletions scripts/cypress/integration/common/4f_grid_view_share.js
Original file line number Diff line number Diff line change
Expand Up @@ -170,7 +170,7 @@ export const genTest = (apiType, dbType) => {
const verifyCsv = (retrievedRecords) => {
// expected output, statically configured
let storedRecords = [
`Address,District,PostalCode,Phone,Location,CustomerList,StaffList,CityRead,StaffMMList`,
`Address,District,PostalCode,Phone,Location,Customer List,Staff List,City,Staff List`,
`1013 Tabuk Boulevard,West Bengali,96203,158399646978,[object Object],2,,Kanchrapara,`,
`1892 Nabereznyje Telny Lane,Tutuila,28396,478229987054,[object Object],2,,Tafuna,`,
`1993 Tabuk Lane,Tamil Nadu,64221,648482415405,[object Object],2,,Tambaram,`,
Expand Down Expand Up @@ -231,7 +231,7 @@ export const genTest = (apiType, dbType) => {
const verifyCsv = (retrievedRecords) => {
// expected output, statically configured
let storedRecords = [
`Address,District,PostalCode,Phone,Location,CustomerList,StaffList,CityRead,StaffMMList`,
`Address,District,PostalCode,Phone,Location,Customer List,Staff List,City,Staff List`,
`1993 Tabuk Lane,Tamil Nadu,64221,648482415405,[object Object],2,,Tambaram,`,
`1661 Abha Drive,Tamil Nadu,14400,270456873752,[object Object],1,,Pudukkottai,`,
];
Expand Down Expand Up @@ -267,24 +267,24 @@ export const genTest = (apiType, dbType) => {

it(`Share GRID view : Virtual column validation > has many`, () => {
// verify column headers
cy.get('[data-col="CustomerList"]').should("exist");
cy.get('[data-col="StaffList"]').should("exist");
cy.get('[data-col="CityRead"]').should("exist");
cy.get('[data-col="StaffMMList"]').should("exist");
cy.get('[data-col="Customer List"]').should("exist");
cy.get('[data-col="Staff List"]').should("exist");
cy.get('[data-col="City"]').should("exist");
cy.get('[data-col="Staff List"]').should("exist");

// has many field validation
mainPage
.getCell("CustomerList", 3)
.getCell("Customer List", 3)
.click()
.find("button.mdi-close-thick")
.should("not.exist");
mainPage
.getCell("CustomerList", 3)
.getCell("Customer List", 3)
.click()
.find("button.mdi-plus")
.should("not.exist");
mainPage
.getCell("CustomerList", 3)
.getCell("Customer List", 3)
.click()
.find("button.mdi-arrow-expand")
.click();
Expand All @@ -308,17 +308,17 @@ export const genTest = (apiType, dbType) => {
it(`Share GRID view : Virtual column validation > belongs to`, () => {
// belongs to field validation
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.click()
.find("button.mdi-close-thick")
.should("not.exist");
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.click()
.find("button.mdi-arrow-expand")
.should("not.exist");
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.find(".v-chip")
.contains("Kanchrapara")
.should("exist");
Expand All @@ -327,17 +327,17 @@ export const genTest = (apiType, dbType) => {
it(`Share GRID view : Virtual column validation > many to many`, () => {
// many-to-many field validation
mainPage
.getCell("StaffMMList", 1)
.getCell("Staff List", 1)
.click()
.find("button.mdi-close-thick")
.should("not.exist");
mainPage
.getCell("StaffMMList", 1)
.getCell("Staff List", 1)
.click()
.find("button.mdi-plus")
.should("not.exist");
mainPage
.getCell("StaffMMList", 1)
.getCell("Staff List", 1)
.click()
.find("button.mdi-arrow-expand")
.click();
Expand Down
28 changes: 14 additions & 14 deletions scripts/cypress/integration/common/4f_pg_grid_view_share.js
Original file line number Diff line number Diff line change
Expand Up @@ -228,7 +228,7 @@ export const genTest = (apiType, dbType) => {
const verifyCsv = (retrievedRecords) => {
// expected output, statically configured
let storedRecords = [
`Address,District,PostalCode,Phone,Location,CustomerList,StaffList,CityRead,StaffMMList`,
`Address,District,PostalCode,Phone,Location,Customer List,Staff List,City,Staff List`,
`1888 Kabul Drive,,20936,,1,,Ife,,`,
`1661 Abha Drive,,14400,,1,,Pudukkottai,,`,
];
Expand Down Expand Up @@ -262,24 +262,24 @@ export const genTest = (apiType, dbType) => {

it(`Share GRID view : Virtual column validation > has many`, () => {
// verify column headers
cy.get('[data-col="CustomerList"]').should("exist");
cy.get('[data-col="StaffList"]').should("exist");
cy.get('[data-col="CityRead"]').should("exist");
cy.get('[data-col="StaffMMList"]').should("exist");
cy.get('[data-col="Customer List"]').should("exist");
cy.get('[data-col="Staff List"]').should("exist");
cy.get('[data-col="City"]').should("exist");
cy.get('[data-col="Staff List"]').should("exist");

// has many field validation
mainPage
.getCell("CustomerList", 3)
.getCell("Customer List", 3)
.click()
.find("button.mdi-close-thick")
.should("not.exist");
mainPage
.getCell("CustomerList", 3)
.getCell("Customer List", 3)
.click()
.find("button.mdi-plus")
.should("not.exist");
mainPage
.getCell("CustomerList", 3)
.getCell("Customer List", 3)
.click()
.find("button.mdi-arrow-expand")
.click();
Expand All @@ -303,17 +303,17 @@ export const genTest = (apiType, dbType) => {
it(`Share GRID view : Virtual column validation > belongs to`, () => {
// belongs to field validation
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.click()
.find("button.mdi-close-thick")
.should("not.exist");
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.click()
.find("button.mdi-arrow-expand")
.should("not.exist");
mainPage
.getCell("CityRead", 1)
.getCell("City", 1)
.find(".v-chip")
.contains("al-Ayn")
.should("exist");
Expand All @@ -322,17 +322,17 @@ export const genTest = (apiType, dbType) => {
it(`Share GRID view : Virtual column validation > many to many`, () => {
// many-to-many field validation
mainPage
.getCell("StaffMMList", 1)
.getCell("Staff List", 1)
.click()
.find("button.mdi-close-thick")
.should("not.exist");
mainPage
.getCell("StaffMMList", 1)
.getCell("Staff List", 1)
.click()
.find("button.mdi-plus")
.should("not.exist");
mainPage
.getCell("StaffMMList", 1)
.getCell("Staff List", 1)
.click()
.find("button.mdi-arrow-expand")
.click();
Expand Down
2 changes: 1 addition & 1 deletion scripts/cypress/integration/common/5a_user_role.js
Original file line number Diff line number Diff line change
Expand Up @@ -209,7 +209,7 @@ export const genTest = (apiType, dbType) => {
const verifyCsv = (retrievedRecords) => {
// expected output, statically configured
let storedRecords = [
`City,AddressList,CountryRead`,
`City,Address List,Country`,
`A Corua (La Corua),939 Probolinggo Loop,Spain`,
`Abha,733 Mandaluyong Place,Saudi Arabia`,
`Abu Dhabi,535 Ahmadnagar Manor,United Arab Emirates`,
Expand Down
4 changes: 2 additions & 2 deletions scripts/cypress/integration/common/6b_downloadCsv.js
Original file line number Diff line number Diff line change
Expand Up @@ -31,7 +31,7 @@ export const genTest = (apiType, dbType) => {
// `Angola,"Benguela, Namibe"`,
// ];
let storedRecords = [
['Country','CityList'],
['Country','City List'],
['Afghanistan','Kabul'],
['Algeria','Skikda', 'Bchar', 'Batna'],
['American Samoa','Tafuna'],
Expand All @@ -41,7 +41,7 @@ export const genTest = (apiType, dbType) => {
// if (isPostgres()) {
// // order of second entry is different
// storedRecords = [
// `Country,CityList`,
// `Country,City List`,
// `Afghanistan,Kabul`,
// `Algeria,"Skikda, Bchar, Batna"`,
// `American Samoa,Tafuna`,
Expand Down
2 changes: 1 addition & 1 deletion scripts/cypress/integration/common/6f_attachments.js
Original file line number Diff line number Diff line change
Expand Up @@ -113,7 +113,7 @@ export const genTest = (apiType, dbType) => {

const verifyCsv = (retrievedRecords) => {
let storedRecords = [
`Country,CityList,testAttach`,
`Country,City List,testAttach`,
`Afghanistan,Kabul,1.json(http://localhost:8080/download/p_h0wxjx5kgoq3w4/vw_skyvc7hsp9i34a/2HvU8R.json)`,
];

Expand Down
Loading