记开发个人图书收藏清单小程序开发（十）DB开发——新增图书信息
昨晚完成了Web端新增图书信息的功能，现在就差DB的具体实现了。
因为我把Book相关的信息拆分的比较多，所以更新有点小麻烦。
首先，我需要创建一个Book Type的Matter；
然后，将图片路径保存到FileBank中，并返回FileBankID；
继续，插入Publisher信息（需要判断name不存在才会insert），然后返回PublisherID；
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；
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
继续，插入Book信息；
继续，插入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
好了，开始测试。
...
查询DB，看看有没有数据进DB：
截图中展示了部分查询结果，基本没什么问题了。
下面要做的是展示Shelf中的Book信息，要等今天活干完才能继续写了。
