[Database] Lesson 7 – Some Other Procedures & View Concepts

Before talking about the new concept, as expected from the end of the previous article, we will do the procedure retrieving the article records by category-id and the basic procedure belonging to the group. procedure/article. However for basic procedures like insert, select-by-id, update, delete-by-id, most of the time we can copy/paste the processing code from the procedure/category group and made some minor tweaks. So here I think we just need to finish the example of the select-by-category-id operation of the procedure/article group. 😀 ## A little performance note The select-by-category-id procedure here has general processing logic that we will filter out article records with category-id match the supplied parameter. Even though it’s just a simple procedure, the way we go about it will have some points worth thinking about. To filter out the matching records as said, we can proceed in two ways – – Read all the article records and convert to an array containing object data in the software environment; Then iterate through this array to filter out the records that match the provided category-id. – Read a first article record and convert it to an object data in the software environment; Then immediately check category-id to see if it matches. If appropriate, add to the results array; And so on repeat the same process with all remaining records. It is clear here that in the first case there will be at least some moment where computer memory will have to allocate to store 1001 object data describing article records. The storage will be maintained until the filtering of matching posts is done and the procedure is finished, then the memory that holds the objects' that do not match will be released. Whereas for the second method, when an object of data describing an article is deemed unsuitable for the search results, it is immediately released from the computer's memory. This is a small but important note when we are writing code to work with database, because when the number of records in database is large enough, we will see the difference is noticeable. . procedure/article/select-by-category-id–async-throw.js const readAllRecordIds = require(“./sub-procedure/read-all-record-ids–async-throw”); const selectArticleById = require(“../article/select-by-id–async-throw”); const Article = require(“../../type/Article”); module.exports = async ( in_categoryId = “Infinity”, out_matchedArticles = [] ) => { var allRecordIds = []; await readAllRecordIds(allRecordIds); /* one-by-one select and check */ for (var recordId of allRecordIds) { var selected = new Article(); await selectArticleById(recordId, selected); /* collect the record if matched */ var selectedArticleIsMatched = (selected.get(“category-id”) == in_categoryId); if (selectedArticleIsMatched) out_matchedArticles.push(selected); else /* do nothing */; } // for }; As in the example code above, we have done the job of collecting all article-ids from the directory names of 1001 article records. We then loop through and retrieve each article record to check the category-id, and decide whether to save the result array or drop the object article right away. express-blog/test.js const Article = require(“./database/type/Article”); const Category = require(“./database/type/Category”); const databaseManager = require(“./database/manager”); const view = require(“./database/view/article-left-join-category–all-join-name–async-throw”); const ArticleJoinCategory = require(“./database/type/ArticleJoinCategory/all-join-name”); void async function() { var procedureName, id, selected; await databaseManager.execute( procedureName = “select-articles-by-category-id”, id = “01”, selected = [] ); console.log(selected); }(); // void CMD | Terminal npm test [ Article(7) [Map] { ‘@id’ => ‘0001’, ‘title’ => ‘How To Create A Web Page?’, ‘short-title’ = > ‘Introduction’, ‘keywords’ => [ ‘basic tutorial’, ‘web programming’, ‘html’, ‘introduction’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 10:13:22 GMT’, ‘category-id’ => ’01’, ‘markdown’ => ‘Content of first article…’ }, Article(7) [Map] { ‘@id’ => ‘0002’, ‘title’ => ‘How to Insert Pictures & Links’, ‘short-title’ => ‘Images & Links’, ‘keywords’ => [ ‘basic tutorial’, ‘setup’ web program’, ‘html’, ‘photo’, ‘link’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 19:13:22 GMT’, ‘category-id’ => ’01’, ‘markdown’ => ‘Content of the second post…’ } ] ## Manipulating the combinatorial query select-top Actually, in the previous post, I suggested creating the procedure/article/select-by-category-id procedure is meant to check before deleting a category but is essentially for an example of a slightly more complex query operation and is build on material is the underlying query operation procedure/article/select-by-id. In terms of application, this procedure is not a good solution, because if we have a lot of articles it will be very laborious to iterate through all the article records. Instead we should just loop until the first post with a matching category-id. Thus we can consider writing another procedure to check the valid category deletion. For example procedure to select some of the latest article records in that category (if any) - procedure/article/select-top-by-category-id–async-throw. js const readAllRecordIds = require(“./sub-procedure/read-all-record-ids–async-throw”); const selectArticleById = require(“./select-by-id–async-throw”); const Article = require(“../../type/Article”); module.exports = async ( in_options = { numberOfRecords: 0, reverseOrder: false }, in_categoryId = “Infinity”, out_selected = [] ) => { /* prepare list of all record ids */ var allRecordIds = []; await readAllRecordIds(allRecordIds); if (in_options.reverseOrder == false) /* do nothing */; else allRecordIds = allRecordIds.reverse(); /* select each record to check */ for (var recordId of allRecordIds) { var selected = new Article(); await selectArticleById(recordId, selected); /* collect the record if matched */ if (selected.get(“category-id”) != in_categoryId) /* not matched */; else out_selected.push(selected); /* stop if found enough records */ if (out_selected.length < in_options.numberOfRecords) /* continue collecting */; else break; } // for }; // module.exports With this procedure we loop from the largest id value of the article records back to the first article record. As soon as a matching record is encountered, the iteration is break to end procedure right there. Thus the number of operations that the computer has to perform is likely to be much lower than using procedure first. And the category deletion procedure code can be modified like this. procedure/category/delete-by-id—async-throw.js const selectTopArticleByCategoryId = require(“../article/select-top-by-category-id–async-throw”); const removeRecordFromDatabase = require(“./sub-procedure/remove-record-from-database–async-throw”); const Category = require(“../../type/Category”); module.exports = async ( in_recordId = “Infinity”, out_deleted = new Category() ) => { try { var selectedArticles = []; await selectTopArticleByCategoryId( { numberOfRecords: 1, reverseOrder: true }, in_recordId, selectedArticles ); var theCategoryIsEmpty = (selectedArticles.length == 0); if (theCategoryIsEmpty) await removeRecordFromDatabase(in_recordId, out_deleted); else throw new Error(“There are articles in this category”); } catch (error) { throw error; } }; // module.exports The above select-top query operation is very common and is often used when we want to select the first few records in a large result set. ## Combined query operation join Let's say we need data to create a single article page. Now in addition to the data fields provided by the article record, we will need to add the name field of the corresponding category record. Code that uses databaseManager from the outside can be handled by calling the select-article-by-id procedure to get the article record, and then retrieving the category-id and continuing to call procedure select-category-by-id to get the corresponding category record and then retrieve name. A union query operation like this is very common when using relational database and so relational database management systems often provide a method called join that joins the two versions. related records to form a new conjugate data type as the return result. To write similar processing code for simple database management software, we can define a federated data type class ArticleJoinCategory, and then write a procedure that performs the associated query operation. match to return a result object of that data type. database/type/ArticleJoinCategory–all-join-name.js const Article = require(“./Article”); const Category = require(“./Category”); const ArticleJoinCategory = class extends Map { constructor(…params) { super(…params); ArticleJoinCategory.initialize(“@id”, this) .initialize(“title”, this) .initialize(“short-title”, this) .initialize(“keywords”, this) .initialize(“edited-datetime”, this ) .initialize(“markdown”, this) .initialize(“category-id”, this) .initialize(“category-name”, this); return this; } static initialize( in_key = “”, out_article = new Article() ) { if (out_article.has(in_key)) /* do nothing */ ; else out_article.set(in_key, null); return Article; } static populate( in_article = new Article(), in_category = new Category(), out_joined = new ArticleJoinCategory() ) { var allArticleEntries = [ …in_article ]; for (var entry of allArticleEntries) { var [key, value] = entry; out_joined.set(key, value); } // for var categoryName = in_category.get(“name”); out_joined.set(“category-name”, categoryName); } }; // ArticleJoinCategory module.exports = ArticleJoinCategory; procedure/article-join-category–all-join-name/select-by-article-id–async-throw.js const ArticleJoinCategory = require(“../../type/ArticleJoinCategory –all-join-name”); const Article = require(“../../type/Article”); const Category = require(“../../type/Category”); const selectArticleById = require(“./select-by-id–async-throw”); const selectCategoryById = require(“../category/select-by-id–async-throw”); module.exports = async ( in_articleId = “”, out_selectedJoin = new ArticleJoinCategory() ) => { var selectedArticle = new Article(); await selectArticleById(in_articleId, selectedArticle); var categoryId = selectedArticle.get(“category-id”); var selectedCategory = new Category(); await selectCategoryById(categoryId, selectedCategory); ArticleJoinCategory.populate(selectedArticle, selectedCategory, out_selectedJoin); }; database/manager.js const storedProcedure = new Map(); /* other procedures … */ storedProcedure.set( “select-article-by-id-join-category–all-join-name”, require(“./procedure/article-join-category–all- join-name/select-by-article-id–async-throw”) ); exports.execute = async ( procedureName = “procedure-name”, …parameters ) => { await storedProcedure .get(procedureName) .call(null, …parameters); }; express-blog/test.js const databaseManager = require(“./database/manager”); const ArticleJoinCategory = require(“./database/type/ArticleJoinCategory–all-join-name”); void async function() { var procedureName, id, selected; await databaseManager.execute( procedureName = “select-article-by-id-join-category–all-join-name”, id = “01”, selected = new ArticleJoinCategory() ); console.log(selected); }(); // void CMD | Terminal npm test ArticleJoinCategory(8) [Map] { ‘@id’ => ‘0001’, ‘title’ => ‘How To Create A Web Page?’, ‘short-title’ => ‘Introduction’, ‘keywords’ => [ ‘basic tutorial’, ‘web programming’, ‘html’, ‘introduction’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 10 :13:22 GMT’, ‘markdown’ => ‘Text of first post…’, ‘category-id’ => ’01’, ‘category-name’ => ‘html5’ } ## Initializing & Using view The concept of view - or viewport - in managing database is no different from other contexts we have seen since view comes before . A view is an interface that presents information to an observer - and in database management in particular a view is considered a data table interface with data fields designed to meet meet certain query and usage needs. Derived from the procedure union query we just wrote above; The result we get is an object describing a union record of class ArticleJoinCategory with the data fields @id, title, ..., category-name. If we query all the associated article records and put the resulting objects into an array one by one, we can consider that array as a view; And this view can be expressed in tabular form as follows - +——+——————–+– ———+————+———-+ | @id | title | ……… | category-id | category-name | +——+————————–+———–+———- —-+—————+ | 0000 | How To … | ……… | 01 | html | +——+————————–+———–+———- —-+—————+ | 0001 | How to Insert Pictures… | ……… | 01 | html | +——+————————–+———–+———- —-+—————+ | …. | ………… | ……… | .. | …. | +——+————————–+———–+———- —-+—————+ | 1001 | Complete Series | ……… | .. | …. | +——+————————–+———–+———- —-+—————+ And below is the expression in the management code database - structure.txt [database] . | . +—–[data] . +—–[procedure] . | | . | +—–[article] . | +—–[category] . | +—–[article-join-category–all-join-name] . | | . | +—–select-by-article-id–async-throw.js . | . +—–[type] . +—–[view] . | . +—–article-join-category–all-join-name–async-throw.js database/view/article-join-category–all-join-name–async -throw.js const readAllArticleIds = require(“../procedure/article/sub-procedure/read-all-record-ids–async-throw”); const ArticleJoinCategory = require(“../type/ArticleJoinCategory–all-join-name”); const selectArticleByIdJoinCategory = require(“../procedure/article-join-category–all-join-name/select-by-article-id–async-throw”); const view = { indexData: async function* ( in_options = { reverseOrder: false } ) { var allArticleIds = []; await readAllArticleIds(allArticleIds); if (in_options.reverseOrder == false) /* do nothing */; else allArticleIds = allArticleIds.reverse(); for (var articleId of allArticleIds) { var joinedRecord = new ArticleJoinCategory(); await selectArticleByIdJoinCategory(articleId, joinedRecord); yield joinedRecord; } // for .. of } // indexData }; // view module.exports = view; Here we have view which is an object which contains the method indexData which is a generator function. When we call this method, the result is an abstract tuple containing all the associated article records - and the same data table we talked about above. Now we will loop through each record in this table and print to console. express-blog/test.js const view = require(“./database/view/article-join-category–all-join-name–async-throw”); void async function() { var allJoinedRecords = view.indexData({ reverseOrder: true }); for await (var record of allJoinedRecords) { console.log(record); } } (); // void CMD | Terminal npm test ArticleJoinCategory(8) [Map] { ‘@id’ => ‘0002’, ‘title’ => ‘How to Insert Images & Links’, ‘short-title’ => ‘Image & Links’, ‘keywords’ => [ ‘basic tutorial’, ‘web programming’, ‘html’, ‘photo’, ‘link’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 19:13:22 GMT’, ‘markdown’ => ‘Content of second article…’, ‘category-id’ => ’01’, ‘category-name’ => ‘html5’ } ArticleJoinCategory( 8) [Map] { ‘@id’ => ‘0001’, ‘title’ => ‘How to Create a Website?’, ‘short-title’ => ‘Introduction’, ‘keywords ‘ => [ ‘basic tutorial’, ‘web programming’, ‘html’, ‘introduction’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 10:13:22 GMT’, ‘markdown ‘ => ‘Content of first article…’, ‘category-id’ => ’01’, ‘category-name’ => ‘html5’ } ArticleJoinCategory(8) [Map] { ‘@id’ => ‘0000’, ‘title’ => ‘The content you searched for does not exist’, ‘short-title’ => ‘Content does not exist’, ‘keywords’ => [ ‘basic guide’, ‘web programming’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 01:13:2 2 GMT’, ‘markdown’ => ‘The content you are looking for does not exist…’, ‘category-id’ => ’00’, ‘category-name’ => ‘unknown’ } Thus, for other query operations related to the association record type ArticleJoinCategory, we can query through this view. A good example is when we want to select a few of the latest articles to present a short introduction on the homepage interface. Now we can write a select-top routine using this view as follows - procedure/article-join-category–all-join-name/select-top–async-throw .js const view = require(“../../view/article-join-category–all-join-name–async-throw”); module.exports = async ( in_options = { numberOfRecords: 0, reverseOrder: false }, out_selected = [] ) => { var allJoinedRecords = view.indexData({ …in_options }); for await (var record of allJoinedRecords) { if (out_selected.length < in_options.numberOfRecords) out_selected.push(record); else break; } // for await }; database/manager.js const storedProcedure = new Map(); /* other procedures … */ storedProcedure.set( “select-top-articles-join-category–all-join-name”, require(“./procedure/article-join-category–all-join- name/select-top-by-category-id–async-throw”) ); exports.execute = async ( procedureName = “procedure-name”, …parameters ) => { await storedProcedure .get(procedureName) .call(null, …parameters); }; database/test.js const databaseManager = require(“./database/manager”); void async function() { var procedure, options, selected; await databaseManager.execute( procedure = “select-top-articles-join-category–all-join-name”, options = { numberOfRecords: 3, reverseOrder: true }, selected = [] ); console.log(selected); }(); // void CMD | Terminal [ ArticleJoinCategory(8) [Map] { ‘@id’ => ‘0002’, ‘title’ => ‘How to Insert Pictures & Links’, ‘short-title’ => ‘Image & Links’ End’, ‘keywords’ => [ ‘basic tutorial’, ‘web programming’, ‘html’, ‘photo’, ‘link’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 19 :13:22 GMT’, ‘markdown’ => ‘Content of second article…’, ‘category-id’ => ’01’, ‘category-name’ => ‘html5’ }, ArticleJoinCategory( 8) [Map] { ‘@id’ => ‘0001’, ‘title’ => ‘How to Create a Website?’, ‘short-title’ => ‘Introduction’, ‘keywords ‘ => [ ‘basic tutorial’, ‘web programming’, ‘html’, ‘introduction’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 10:13:22 GMT’, ‘markdown ‘ => ‘Content of first article…’, ‘category-id’ => ’01’, ‘category-name’ => ‘html5’ }, ArticleJoinCategory(8) [Map] { ‘@id ‘ => ‘0000’, ‘title’ => ‘The content you searched for does not exist’, ‘short-title’ => ‘Content does not exist’, ‘keywords’ => [ ‘basic guide’ , ‘web programming’ ], ‘edited-datetime’ => ‘Sat, 16 Apr 2022 01:13:22 GMT ‘, ‘markdown’ => ‘The content you are looking for does not exist…’, ‘category-id’ => ’00’, ‘category-name’ => ‘unknown’ } ] # # End of the article So we got a rough idea of ​​the view concept in database management and some common compound query methods select-top and join. We will see these concepts again in Sub-Series SQL in more detail. For now we will pause Sub-Series Database` here for a short while to finalize the simple blog site under construction as previously planned. See you in the next articles. 😀 (Not Published) [**[Database] Lesson 8 – Take it slow to see what we need to learn more. Learn the natural way. :D**](#)

Source : Viblo