记开垦个人图书收藏清单小程序支付(三)DB设计

发布时间:2019-10-05  栏目:sqlite  评论:0 Comments

今晚做到了Web端新扩张图书音讯的效果,以往就差DB的求实完结了。

重在是参照他事他说加以考察豆瓣的书籍查询接口:

因为本人把Book相关的新闻拆分的可比多,所以更新有一些小麻烦。

https://api.douban.com/v2/book/isbn/:9780132350884

首先,笔者索要创设贰个Book Type的马特er;

归来内容如下:

下一场,将图纸路线保存到FileBank中,并赶回FileBankID;

图片 1图片 2

持续,插入Publisher音讯(须求判断name空头支票才会insert),然后回到PublisherID;

 1 {
 2     "rating": {
 3         "max": 10, 
 4         "numRaters": 361, 
 5         "average": "8.8", 
 6         "min": 0
 7     }, 
 8     "subtitle": "A Handbook of Agile Software Craftsmanship", 
 9     "author": [
10         "Robert C. Martin"
11     ], 
12     "pubdate": "2008-8-11", 
13     "tags": [
14         {
15             "count": 295, 
16             "name": "编程", 
17             "title": "编程"
18         }, 
19         {
20             "count": 257, 
21             "name": "programming", 
22             "title": "programming"
23         }, 
24         {
25             "count": 150, 
26             "name": "软件开发", 
27             "title": "软件开发"
28         }, 
29         {
30             "count": 109, 
31             "name": "程序设计", 
32             "title": "程序设计"
33         }, 
34         {
35             "count": 100, 
36             "name": "计算机", 
37             "title": "计算机"
38         }, 
39         {
40             "count": 87, 
41             "name": "软件工程", 
42             "title": "软件工程"
43         }, 
44         {
45             "count": 66, 
46             "name": "敏捷开发", 
47             "title": "敏捷开发"
48         }, 
49         {
50             "count": 55, 
51             "name": "agile", 
52             "title": "agile"
53         }
54     ], 
55     "origin_title": "", 
56     "image": "https://img3.doubanio.com/view/subject/m/public/s29624974.jpg", 
57     "binding": "Paperback", 
58     "translator": [ ], 
59     "catalog": "", 
60     "pages": "464", 
61     "images": {
62         "small": "https://img3.doubanio.com/view/subject/s/public/s29624974.jpg", 
63         "large": "https://img3.doubanio.com/view/subject/l/public/s29624974.jpg", 
64         "medium": "https://img3.doubanio.com/view/subject/m/public/s29624974.jpg"
65     }, 
66     "alt": "https://book.douban.com/subject/3032825/", 
67     "id": "3032825", 
68     "publisher": "Prentice Hall", 
69     "isbn10": "0132350882", 
70     "isbn13": "9780132350884", 
71     "title": "Clean Code", 
72     "url": "https://api.douban.com/v2/book/3032825", 
73     "alt_title": "", 
74     "author_intro": "Robert C. “Uncle Bob” Martin has been a software professional since 1970 and an international software consultant since 1990. He is founder and president of Object Mentor, Inc., a team of experienced consultants who mentor their clients worldwide in the fields of C++, Java, C#, Ruby, OO, Design Patterns, UML, Agile Methodologies, and eXtreme programming.", 
75     "summary": "Even bad code can function. But if code isn’t clean, it can bring a development organization to its knees. Every year, countless hours and significant resources are lost because of poorly written code. But it doesn’t have to be that way.
76 Noted software expert Robert C. Martin presents a revolutionary paradigm with Clean Code: A Handbook of Agile Software Craftsmanship. Martin has teamed up with his colleagues from Object Mentor to distill their best agile practice of cleaning code “on the fly” into a book that will instill within you the values of a software craftsman and make you a better programmer—but only if you work at it.
77 What kind of work will you be doing? You’ll be reading code—lots of code. And you will be challenged to think about what’s right about that code, and what’s wrong with it. More importantly, you will be challenged to reassess your professional values and your commitment to your craft.
78 Clean Code is divided into three parts. The first describes the principles, patterns, and practices of writing clean code. The second part consists of several case studies of increasing complexity. Each case study is an exercise in cleaning up code—of transforming a code base that has some problems into one that is sound and efficient. The third part is the payoff: a single chapter containing a list of heuristics and “smells” gathered while creating the case studies. The result is a knowledge base that describes the way we think when we write, read, and clean code.
79 Readers will come away from this book understanding
80 How to tell the difference between good and bad code
81 How to write good code and how to transform bad code into good code
82 How to create good names, good functions, good objects, and good classes
83 How to format code for maximum readability
84 How to implement complete error handling without obscuring code logic
85 How to unit test and practice test-driven development
86 This book is a must for any developer, software engineer, project manager, team lead, or systems analyst with an interest in producing better code.", 
87     "price": "USD 49.99"
88 }
 1 CREATE PROCEDURE [base].[Publisher#Insert](@json nvarchar(max), @id int out)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         declare    @name nvarchar(100);
 8         select    @name=Publisher from openjson(@json, '$') with (Publisher nvarchar(100))
 9 
10         -- insert Publisher
11         insert    base._Publisher(Name)select @name
12         where    not exists(select 1 from base._Publisher p where p.Name=@name);
13 
14         select    @id=ID from base.Publisher#Raw() where Name=@name;
15 ...
16 END

豆类图书查询重临内容

 

 

承袭,插入Binding新闻(也急需剖断name子虚乌有才insert),重临BindingID;

刚刚出去拿纱窗了,等早上安顿DB的时候继续立异。

 1 CREATE PROCEDURE [base].[Binding#Insert](@json nvarchar(max), @id int out)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         declare    @name nvarchar(100);
 8         select    @name=Binding from openjson(@json, '$') with (Binding nvarchar(100))
 9 
10         -- insert Binding
11         insert    base._Binding(Name)select @name
12         where    not exists(select 1 from base._Binding p where p.Name=@name);
13 
14         select    @id=ID from base.Binding#Raw() where Name=@name;
15 
16 ...
17 END

 

 

传说豆瓣API获取的Response消息,所以收取如下新闻:

继续,插入Book信息;

豆瓣图书API信息
字段名称 豆瓣字段 字段描述
Title title  
SubTitle subtitle  
Authors author  
Translator translator  
ISBN13 isbn13  
ISBN10 isbn10  
AuthorIntro author_intro  
Summary summary  
Publisher publisher  
Binding binding  
OriginTitle origin_title  
Pages pages  
ImageUrl image  
Pubdate pubdate  
Catalog catalog  
Tags tags  

继续,插入BookInfo的信息;

 

继续,插入BookNbr信息;

 

继续,插入BookSupplement信息;

 

继续,插入BookTag信息;

 

 1 CREATE PROCEDURE [base].[BookTag#Insert](@json nvarchar(max), @bookID bigint)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         -- insert Tag
 8         insert    base._Tag(Name)select value
 9         from    openjson(@json, '$.Tags') x
10         where    not exists(select 1 from base._Tag p where p.Name=x.value);
11 
12         insert    base._BookTag(BookID, TagID) select @bookID, x.ID
13         from    openjson(@json, '$.Tags') j join base.Tag#Raw() x on x.Name=j.value
14 
15 ...
16 END

 

 

 

继续,插入BookAuthor信息;

 

 1 CREATE PROCEDURE [base].[BookAuthor#Insert](@json nvarchar(max), @bookID bigint)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         -- insert Author
 8         insert    base._Author(Name)select value
 9         from    openjson(@json, '$.Authors') x
10         where    not exists(select 1 from base._Author p where p.Name=x.value);
11 
12         insert    base._BookAuthor(BookID, AuthorID) select @bookID, x.ID
13         from    openjson(@json, '$.Authors') j join base.Author#Raw() x on x.Name=j.value
14 
15 ...
16 END

 

 

 

继续,插入BookTranslator信息;

 

 1 CREATE PROCEDURE [base].[BookTranslator#Insert](@json nvarchar(max), @bookID bigint)
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         -- insert Translator
 8         insert    base._Author(Name)select value
 9         from    openjson(@json, '$.Translators') x
10         where    not exists(select 1 from base._Author p where p.Name=x.value);
11 
12         insert    base._BookTranslator(BookID, TranslatorID) select @bookID, x.ID
13         from    openjson(@json, '$.Translators') j join base.Author#Raw() x on x.Name=j.value
14 
15 ...
16 END

 

 

 

最终,关联新扩张的Book音讯和Shelf,插入ShelfBook消息。

 

 

 

前天假释Init Script

 

Book_Init.sql

 

 1 CREATE PROCEDURE [svc].[Book$Init](@json nvarchar(max))
 2 WITH ENCRYPTION
 3 AS
 4 BEGIN
 5 ...
 6 
 7         declare    @stringID varchar(36), @userID int,  @shelfID int;
 8         select    @stringID=u.StringID,  @userID=u.ID, @shelfID=s.ID
 9         from    openjson (@json, '$') with (StringID varchar(36))
10         cross    apply core.User#For(StringID) u
11         join    core.Party#Raw() s on s.PID=u.ID;
12 
13         declare    @stateID int=(select BookCreated from core.Status#ID());
14 
15         -- init Matter
16         insert    core._Matter(Type, UserID, StateID)
17         select    k._Book, @userID, @stateID from core.Matter#Type() k;
18         declare    @matterID int=@@identity;
19 
20         -- init FileBank
21         insert    base._FileBank(Type, Url)
22         select    k._BookImage, ImageUrl
23         from    openjson(@json, '$') with (ImageUrl varchar(200))
24         cross    apply base.FileBank#Type() k;
25         declare    @imageID int=@@identity;
26         
27         -- insert Publisher
28         declare    @publisherID int;
29         exec    base.Publisher#Insert @json=@json, @id=@publisherID out;
30         
31         -- insert Binding
32         declare    @bindingID int;
33         exec    base.Binding#Insert @json=@json, @id=@bindingID out;
34 
35         -- insert Book
36         insert    base._Book(ID, Title, PublisherID, BindingID, ImageID)
37         select    @matterID, Title, @publisherID, @bindingID, @imageID
38         from    openjson(@json, '$') with (Title nvarchar(100));
39         
40         -- insert BookInfo
41         insert    base._BookInfo(ID, OriginTitle, PageCnt, Pubdate, SubTitle)
42         select    @matterID, OriginTitle, Pages, Pubdate, SubTitle
43         from    openjson(@json, '$')
44         with (
45             Pages       int, 
46             Pubdate     char(10), 
47             SubTitle    nvarchar(150), 
48             OriginTitle nvarchar(150)
49         );
50         
51         -- insert BookNbr
52         insert    base._BookNbr(ID, Type, Number)
53         select    @matterID, k._ISBN13, Isbn13
54         from    base.BookNbr#Type() k, openjson(@json, '$') with (Isbn13 char(13));
55         
56         insert    base._BookNbr(ID, Type, Number)
57         select    @matterID, k._ISBN10, Isbn10
58         from    base.BookNbr#Type() k, openjson(@json, '$') with (Isbn10 char(10));
59         
60         -- insert BookSupplement
61         insert    base._BookSupplement(ID, Type, Supplement)
62         select    @matterID, k._AuthorIntro, AuthorIntro
63         from    base.BookSupplement#Type() k, openjson(@json, '$') with (AuthorIntro nvarchar(max));
64         
65         insert    base._BookSupplement(ID, Type, Supplement)
66         select    @matterID, k._Summary, Summary
67         from    base.BookSupplement#Type() k, openjson(@json, '$') with (Summary nvarchar(max));
68         
69         insert    base._BookSupplement(ID, Type, Supplement)
70         select    @matterID, k._Catalog, Catalog
71         from    base.BookSupplement#Type() k, openjson(@json, '$') with (Catalog nvarchar(max));
72 
73         -- insert BookTag
74         exec    base.BookTag#Insert @json=@json, @bookID=@matterID;
75         
76         -- insert BookAuthor
77         exec    base.BookAuthor#Insert @json=@json, @bookID=@matterID;
78 
79         -- insert BookTranslator
80         exec    base.BookTranslator#Insert @json=@json, @bookID=@matterID;
81         
82         -- insert ShelfBook
83         insert    base._ShelfBook(BookID, ShelfID) values(@matterID, @shelfID);
84 
85 ...
86 END

 

 

拆分如上字段出来。

好了,开端测量试验。

 ……

因为在看FIFA World Cup,所以规划有个别时有时无的,放出DB的Diagram:

查询DB,看看有未有数据进DB:

图片 3

图片 4

 

图片 5

图片 6

 

 

 截图中浮现了某些查询结果,基本没什么难点了。

时下的逻辑应该是从未有过难点了,未来亟需加上的是DB的源委,以及每条线的上扬安插。

上面要做的是展现Shelf中的Book消息,要等今日活干完才干一而再写了。

顺便放出多少个Type的Script:

 

1 CREATE FUNCTION [core].[Matter#Type] ()
2 RETURNS TABLE
3 WITH SCHEMABINDING, ENCRYPTION
4 AS RETURN
5 (
6     select    1        as        _Book
7     ,         2        as        _ShelfBook
8     ,         4        as        _BookList
9 )

 1 CREATE FUNCTION [core].[Party#Type] ()
 2 RETURNS TABLE
 3 WITH SCHEMABINDING, ENCRYPTION
 4 AS RETURN
 5 (
 6     select    1        as        _Administrator
 7     ,         4        as        _User
 8     ---------------------------------------
 9     ,        16        as        _Shelf
10 )

1 CREATE FUNCTION [base].[BookInfo#Type] ()
2 RETURNS TABLE
3 WITH SCHEMABINDING, ENCRYPTION
4 AS RETURN
5 (
6     select    1        as        _Summary
7     ,         2        as        _Catalog
8     ,         4        as        _AuthorIntro
9 )

 

好的,明天就先到那吗。

 

留下评论

网站地图xml地图