同时,可以使用如下命令,将schema定义转化成json, yml, sql等多种形式:
cds db/schema.cds -2 json
cds db/schema.cds -2 yml
cds db/schema.cds -2 sql
--看看它转化成的SQL语句的样子:(节选)
CREATE TABLE sap_capire_bookshop_Books (
createdAt TIMESTAMP_TEXT,
createdBy NVARCHAR(255),
modifiedAt TIMESTAMP_TEXT,
modifiedBy NVARCHAR(255),
ID INTEGER NOT NULL,
title NVARCHAR(111),
descr NVARCHAR(1111),
author_ID INTEGER,
genre_ID INTEGER,
stock INTEGER,
price DECIMAL(9, 2),
currency_code NVARCHAR(3),
PRIMARY KEY(ID)
);
CREATE TABLE sap_capire_bookshop_Authors (
createdAt TIMESTAMP_TEXT,
createdBy NVARCHAR(255),
modifiedAt TIMESTAMP_TEXT,
modifiedBy NVARCHAR(255),
ID INTEGER NOT NULL,
name NVARCHAR(111),
PRIMARY KEY(ID)
);
CREATE TABLE sap_capire_bookshop_Genres (
name NVARCHAR(255),
descr NVARCHAR(1000),
ID INTEGER NOT NULL,
parent_ID INTEGER,
PRIMARY KEY(ID)
);
CREATE TABLE sap_common_Currencies (
name NVARCHAR(255),
descr NVARCHAR(1000),
code NVARCHAR(3) NOT NULL,
symbol NVARCHAR(5),
minorUnit SMALLINT,
PRIMARY KEY(code)
);
.......
这里考虑到PostgreSQL中表名或其它对象名的63个字符长度的限制,我们还真不能把包名得太长,一不小心就会超长了。
3、定义相关服务(service)
定义完schema之后,我们就可以定义一下服务(service)了。
文件 svc/admin-service.cds
using { sap.capire.bookshop as my } from '../db/schema';
service AdminService @(requires:'authenticated-user') {
entity Books as projection on my.Books;
entity Authors as projection on my.Authors;
}
文件 srv/cat-service.cds
using { sap.capire.bookshop as my } from '../db/schema';
service CatalogService @(path:'/browse') {
@readonly entity Books as select from my.Books {*,
author.name as author
} excluding { createdBy, modifiedBy };
@requires: 'authenticated-user'
action submitOrder (book: Books:ID, quantity: Integer);
}
这里解释一下这两个service的逻辑:
admin-service: 它需要进行user的认证,认证通过以后,就可以基于Books和Authors两个schema描述的entity进行正常的访问了。
cat-service: 它直接可以进行浏览数据 ,能以只读方式访问Books, 顺带访问Author(根据作者名字). 但是会排除createdBy, modifiedBy两个字段。同时,如果提供了用户认证,则可以按照Book的ID和数量来提交订单(action:submitOrder)。
这里感觉整个过程,基本上没怎么写代码,全部给你生成了相应的逻辑。
这次我们再看看效果:
cds watch
......
[cds] - serving AdminService { path: '/odata/v4/admin' }
[cds] - serving CatalogService { path: '/browse' }
[cds] - server listening on { url: 'http://localhost:4004' }
[cds] - launched at 2024/3/10 20:03:40, version: 7.7.0, in: 991.416ms
[cds] - [ terminate with ^C ]
这里我们看到了/odata/v4/admin的rest路径了。使用浏览器访问,看到如下效果:
image-20240310200645298
访问:http://localhost:4004/odata/v4/admin/$metadata 时,可以使用alice这个缺省用户,不用输入密码即可得到admin权限。
4、配置访问使用数据库
默认方式使用的就是sqlite内存方式启动的数据库。我们一步步演化推进:
添加如下两个csv格式的数据文件:
db/data/sap.capire.bookshop-Books.csv
ID,title,author_ID,stock
201,Wuthering Heights,101,12
207,Jane Eyre,107,11
251,The Raven,150,333
252,Eleonora,150,555
271,Catweazle,170,22
db/data/sap.capire.bookshop-Authors.csv
ID,name
101,Emily Brontë
107,Charlotte Brontë
150,Edgar Allen Poe
170,Richard Carpenter
我们修改一下package.json, 指定一下数据库,添加内容如下:
"cds": { "requires": {
"db": {
"kind": "sqlite",
"credentials": { "url": "db.sqlite" }
}
}}
这个时候,我们只要运行cds deploy就可以将数据deploy到sqlite数据库当中。文件名为db.sqlite.
E:\work\3rdparty\postgres\cap\bookshop>cds deploy
> init from db\data\sap.capire.bookshop-Books.csv
> init from db\data\sap.capire.bookshop-Authors.csv
/> successfully deployed to db.sqlite
E:\work\3rdparty\postgres\cap\bookshop>sqlite3 db.sqlite
SQLite version 3.45.1 2024-01-30 16:01:20 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> .tables
AdminService_Authors
AdminService_Books
AdminService_Books_texts
AdminService_Currencies
AdminService_Currencies_texts
AdminService_Genres
AdminService_Genres_texts
CatalogService_Books
CatalogService_Books_texts
CatalogService_Currencies
CatalogService_Currencies_texts
CatalogService_Genres
............
sap_capire_bookshop_Authors
sap_capire_bookshop_Books
sap_capire_bookshop_Books_texts
sap_capire_bookshop_Genres
sap_capire_bookshop_Genres_texts
sap_common_Currencies
sap_common_Currencies_texts
sqlite> select * from sap_capire_bookshop_Books;
2024-03-10T13:37:12.624Z|anonymous|2024-03-10T13:37:12.624Z|anonymous|201|Wuthering Heights||101||12||
2024-03-10T13:37:12.624Z|anonymous|2024-03-10T13:37:12.624Z|anonymous|207|Jane Eyre||107||11||
2024-03-10T13:37:12.624Z|anonymous|2024-03-10T13:37:12.624Z|anonymous|251|The Raven||150||333||
2024-03-10T13:37:12.624Z|anonymous|2024-03-10T13:37:12.624Z|anonymous|252|Eleonora||150||555||
2024-03-10T13:37:12.624Z|anonymous|2024-03-10T13:37:12.624Z|anonymous|271|Catweazle||170||22||
你会发现它建了很多表。上边两个数据文件的数据也都添加进去了。
如果我们访问:http://localhost:4004/odata/v4/admin/Books 都能看到对应的数据了。
{
"@odata.context": "$metadata#Books",
"value": [{
"createdAt": "2024-03-10T13:44:54.391Z",
"createdBy": "anonymous",
"modifiedAt": "2024-03-10T13:44:54.391Z",
"modifiedBy": "anonymous",
"ID": 201,
"title": "Wuthering Heights",
"descr": null,
"author_ID": 101,
"genre_ID": null,
"stock": 12,
"price": null,
"currency_code": null
},
...
}
截至目前为止,我们看到的还是连接到sqlite数据库。大多数功能都是直接提供给你的。
我们看看,切换到PostgreSQL数据库是啥样的?该如何做?
对于Node.js而言,有如下依赖关系:
Database | Package | Remarks |
---|---|---|
SAP HANA Cloud | @sap/cds-hana | recommended for production |
SQLite | @cap-js/sqlite | recommended for development |
PostgreSQL | @cap-js/postgres | maintained by community + CAP team |
可以使用 npm add @cap-js/postgres
来添加对PostgreSQL的依赖。
我这里是提前准备好的一个VM上的PostgreSQL环境,CentOS7.9, PG14.x, 用户名mydb, 密码test123,它的数据库名也为mydb。提前准备好。
E:\work\3rdparty\postgres\postgres>psql -h 192.168.0.20 -U mydb -p 5555
用户 mydb 的口令:
psql (14.4, 服务器 14.11)
输入 "help" 来获取帮助信息.
mydb=> \d
没有找到任何关系.
mydb=>
如果要设成PostgreSQL的数据库连接环境,在工程根目录下边建一个文件.env。内容如下:
cds.requires.db.[pg].kind = postgres
cds.requires.db.[pg].credentials.host = 192.168.0.20
cds.requires.db.[pg].credentials.port = 5555
cds.requires.db.[pg].credentials.user = mydb
cds.requires.db.[pg].credentials.password = test123
cds.requires.db.[pg].credentials.database = mydb
这是一种配置方式,另一种方式是使用直接在package.json中修改添加相关的数据库类型及连接信息:
"cds": {
"requires": {
"db": {
"[sqlite]": { "kind": "sqlite", "impl": "@cap-js/sqlite", "credentials": { "url": "db.sqlite" } },
"[pg]": {"kind": "postgres", "impl": "@cap-js/postgres",
"credentials": {
"host": "192.168.0.20",
"port": 5555,
"user": "mydb",
"password": "test123",
"database": "mydb"
}
}
}
}
},
上边[pg]部分定义的就是与PostgreSQL相关的内容。
我们可以使用下边的命令诊断一下:(注 --profile 用于指定是哪种profile)
E:\work\3rdparty\postgres\cap\bookshop>cds env requires.db --profile pg
{
impl: '@cap-js/postgres',
dialect: 'postgres',
vcap: { label: 'postgresql-db' },
schema_evolution: 'auto',
kind: 'postgres',
credentials: {
host: '192.168.0.20',
port: 5555,
user: 'mydb',
password: 'test123',
database: 'mydb'
}
}
5、验证访问PostgreSQL
我们再次启动这个应用:
cds watch --profile pg
.......
[cds] - connect using bindings from: { registry: '~/.cds-services.json' }
[cds] - connect to db > postgres {
host: '192.168.0.200',
port: 5555,
user: 'mydb',
password: '...',
database: 'mydb'
}
[cds] - using auth strategy {
kind: 'mocked',
impl: 'node_modules\\@sap\\cds\\lib\\auth\\basic-auth'
}
[cds] - serving AdminService { path: '/odata/v4/admin' }
[cds] - serving CatalogService { path: '/browse' }
[cds] - server listening on { url: 'http://localhost:4004' }
[cds] - launched at 2024/3/13 05:07:19, version: 7.7.1, in: 16.285s
[cds] - [ terminate with ^C ]
同时,数据库部分,需要对SCHEMA以及数据表中的数据进行初始化:
E:\work\3rdparty\postgres\cap\bookshop>cds deploy --profile pg
> init from db\data\sap.capire.bookshop-Books.csv
> init from db\data\sap.capire.bookshop-Authors.csv
/> successfully deployed to 192.168.0.20:5555
我们再次访问PG里头的数据库:mydb:
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------------------------+-------+-------
public | adminservice_authors | view | mydb
public | adminservice_books | view | mydb
public | adminservice_books_texts | view | mydb
public | adminservice_currencies | view | mydb
public | adminservice_currencies_texts | view | mydb
public | adminservice_genres | view | mydb
public | adminservice_genres_texts | view | mydb
public | catalogservice_books | view | mydb
public | catalogservice_books_texts | view | mydb
public | catalogservice_currencies | view | mydb
public | catalogservice_currencies_texts | view | mydb
public | catalogservice_genres | view | mydb
public | catalogservice_genres_texts | view | mydb
public | cds_model | table | mydb
public | localized_adminservice_authors | view | mydb
public | localized_adminservice_books | view | mydb
public | localized_adminservice_currencies | view | mydb
public | localized_adminservice_genres | view | mydb
public | localized_catalogservice_books | view | mydb
public | localized_catalogservice_currencies | view | mydb
public | localized_catalogservice_genres | view | mydb
public | localized_sap_capire_bookshop_authors | view | mydb
public | localized_sap_capire_bookshop_books | view | mydb
public | localized_sap_capire_bookshop_genres | view | mydb
public | localized_sap_common_currencies | view | mydb
public | sap_capire_bookshop_authors | table | mydb
public | sap_capire_bookshop_books | table | mydb
### 总结
>技术学到手后,就要开始准备面试了,找工作的时候一定要好好准备简历,毕竟简历是找工作的敲门砖,还有就是要多做面试题,复习巩固。
kshop_authors | view | mydb
public | localized_sap_capire_bookshop_books | view | mydb
public | localized_sap_capire_bookshop_genres | view | mydb
public | localized_sap_common_currencies | view | mydb
public | sap_capire_bookshop_authors | table | mydb
public | sap_capire_bookshop_books | table | mydb
### 总结
>技术学到手后,就要开始准备面试了,找工作的时候一定要好好准备简历,毕竟简历是找工作的敲门砖,还有就是要多做面试题,复习巩固。
![](https://img-blog.csdnimg.cn/img_convert/7519d54a318f0d601ad5d9bf7d41b04e.webp?x-oss-process=image/format,png)