记开发个人图书收藏清单小程序开发（十）DB开发——新增图书信息

昨晚完成了Web端新增图书信息的功能，现在就差DB的具体实现了。

因为我把Book相关的信息拆分的比较多，所以更新有点小麻烦。

首先，我需要创建一个Book Type的Matter；

然后，将图片路径保存到FileBank中，并返回FileBankID；

继续，插入Publisher信息（需要判断name不存在才会insert），然后返回PublisherID；

CREATE PROCEDURE [base].[Publisher#Insert](@json nvarchar(max), @id int out)
WITH ENCRYPTION
AS
BEGIN
...

        declare    @name nvarchar(100);
        select    @name=Publisher from openjson(@json, '$') with (Publisher nvarchar(100))

        -- insert Publisher
        insert    base._Publisher(Name)select @name
        where    not exists(select 1 from base._Publisher p where p.Name=@name);

        select    @id=ID from base.Publisher#Raw() where Name=@name;
...
END

继续，插入Binding信息（也需要判断name不存在才insert），返回BindingID；

CREATE PROCEDURE [base].[Binding#Insert](@json nvarchar(max), @id int out)
WITH ENCRYPTION
AS
BEGIN
...

        declare    @name nvarchar(100);
        select    @name=Binding from openjson(@json, '$') with (Binding nvarchar(100))

        -- insert Binding
        insert    base._Binding(Name)select @name
        where    not exists(select 1 from base._Binding p where p.Name=@name);

        select    @id=ID from base.Binding#Raw() where Name=@name;

...
END

继续，插入Book信息；

继续，插入BookInfo的信息；

继续，插入BookNbr信息；

继续，插入BookSupplement信息；

继续，插入BookTag信息；

CREATE PROCEDURE [base].[BookTag#Insert](@json nvarchar(max), @bookID bigint)
WITH ENCRYPTION
AS
BEGIN
...

        -- insert Tag
        insert    base._Tag(Name)select value
        from    openjson(@json, '$.Tags') x
        where    not exists(select 1 from base._Tag p where p.Name=x.value);

        insert    base._BookTag(BookID, TagID) select @bookID, x.ID
        from    openjson(@json, '$.Tags') j join base.Tag#Raw() x on x.Name=j.value

...
END

继续，插入BookAuthor信息；

CREATE PROCEDURE [base].[BookAuthor#Insert](@json nvarchar(max), @bookID bigint)
WITH ENCRYPTION
AS
BEGIN
...

        -- insert Author
        insert    base._Author(Name)select value
        from    openjson(@json, '$.Authors') x
        where    not exists(select 1 from base._Author p where p.Name=x.value);

        insert    base._BookAuthor(BookID, AuthorID) select @bookID, x.ID
        from    openjson(@json, '$.Authors') j join base.Author#Raw() x on x.Name=j.value

...
END

继续，插入BookTranslator信息；

CREATE PROCEDURE [base].[BookTranslator#Insert](@json nvarchar(max), @bookID bigint)
WITH ENCRYPTION
AS
BEGIN
...

        -- insert Translator
        insert    base._Author(Name)select value
        from    openjson(@json, '$.Translators') x
        where    not exists(select 1 from base._Author p where p.Name=x.value);

        insert    base._BookTranslator(BookID, TranslatorID) select @bookID, x.ID
        from    openjson(@json, '$.Translators') j join base.Author#Raw() x on x.Name=j.value

...
END

最后，关联新增的Book信息和Shelf，插入ShelfBook信息。

现在放出Init Script

Book_Init.sql

CREATE PROCEDURE [svc].[Book$Init](@json nvarchar(max))
WITH ENCRYPTION
AS
BEGIN
...

        declare    @stringID varchar(36), @userID int,  @shelfID int;
        select    @stringID=u.StringID,  @userID=u.ID, @shelfID=s.ID
        from    openjson (@json, '$') with (StringID varchar(36))
        cross    apply core.User#For(StringID) u
        join    core.Party#Raw() s on s.PID=u.ID;

        declare    @stateID int=(select BookCreated from core.Status#ID());

        -- init Matter
        insert    core._Matter(Type, UserID, StateID)
        select    k._Book, @userID, @stateID from core.Matter#Type() k;
        declare    @matterID int=@@identity;

        -- init FileBank
        insert    base._FileBank(Type, Url)
        select    k._BookImage, ImageUrl
        from    openjson(@json, '$') with (ImageUrl varchar(200))
        cross    apply base.FileBank#Type() k;
        declare    @imageID int=@@identity;
        
        -- insert Publisher
        declare    @publisherID int;
        exec    base.Publisher#Insert @json=@json, @id=@publisherID out;
        
        -- insert Binding
        declare    @bindingID int;
        exec    base.Binding#Insert @json=@json, @id=@bindingID out;

        -- insert Book
        insert    base._Book(ID, Title, PublisherID, BindingID, ImageID)
        select    @matterID, Title, @publisherID, @bindingID, @imageID
        from    openjson(@json, '$') with (Title nvarchar(100));
        
        -- insert BookInfo
        insert    base._BookInfo(ID, OriginTitle, PageCnt, Pubdate, SubTitle)
        select    @matterID, OriginTitle, Pages, Pubdate, SubTitle
        from    openjson(@json, '$')
        with (
            Pages       int, 
            Pubdate     char(10), 
            SubTitle    nvarchar(150), 
            OriginTitle nvarchar(150)
        );
        
        -- insert BookNbr
        insert    base._BookNbr(ID, Type, Number)
        select    @matterID, k._ISBN13, Isbn13
        from    base.BookNbr#Type() k, openjson(@json, '$') with (Isbn13 char(13));
        
        insert    base._BookNbr(ID, Type, Number)
        select    @matterID, k._ISBN10, Isbn10
        from    base.BookNbr#Type() k, openjson(@json, '$') with (Isbn10 char(10));
        
        -- insert BookSupplement
        insert    base._BookSupplement(ID, Type, Supplement)
        select    @matterID, k._AuthorIntro, AuthorIntro
        from    base.BookSupplement#Type() k, openjson(@json, '$') with (AuthorIntro nvarchar(max));
        
        insert    base._BookSupplement(ID, Type, Supplement)
        select    @matterID, k._Summary, Summary
        from    base.BookSupplement#Type() k, openjson(@json, '$') with (Summary nvarchar(max));
        
        insert    base._BookSupplement(ID, Type, Supplement)
        select    @matterID, k._Catalog, Catalog
        from    base.BookSupplement#Type() k, openjson(@json, '$') with (Catalog nvarchar(max));

        -- insert BookTag
        exec    base.BookTag#Insert @json=@json, @bookID=@matterID;
        
        -- insert BookAuthor
        exec    base.BookAuthor#Insert @json=@json, @bookID=@matterID;

        -- insert BookTranslator
        exec    base.BookTranslator#Insert @json=@json, @bookID=@matterID;
        
        -- insert ShelfBook
        insert    base._ShelfBook(BookID, ShelfID) values(@matterID, @shelfID);

...
END

好了，开始测试。

...

查询DB，看看有没有数据进DB：

 截图中展示了部分查询结果，基本没什么问题了。

下面要做的是展示Shelf中的Book信息，要等今天活干完才能继续写了。