Project: bookmarks.dev - File: personal-bookmarks-search.service.js

To perform a text search query on the text index of a collection, you need to use the $text operator. In the example below the results are sorted in order of relevance score, which you must explicitly project to the $meta textScore field and sort on it:

let getPersonalBookmarksForSearchedTerms = async function (nonSpecialSearchTerms, page, limit, userId, specialSearchFilters, searchInclude) {

  let filter = {userId: userId};
  if ( nonSpecialSearchTerms.length > 0 ) {
    if ( searchInclude === 'any' ) {
      filter.$text = {$search: nonSpecialSearchTerms.join(' ')}
    } else {
      filter.$text = {$search: bookmarksSearchHelper.generateFullSearchText(nonSpecialSearchTerms)};
    }
  }
  addSpecialSearchFiltersToMongoFilter(specialSearchFilters, filter);

  let bookmarks = await Bookmark.find(
    filter,
    {
      score: {$meta: "textScore"}
    }
  )
    .sort({score: {$meta: "textScore"}})
    .skip((page - 1) * limit)
    .limit(limit)
    .lean()
    .exec();

  return bookmarks;
}

By default, the full text search in Mongo looks for any of the terms, so a query of “java exception” will look for the bookmarks that container java or exception. You can force an AND search by using the terms in double quotes, so the query text would be "java" "exception". This is what the following generateFullSearchText function does for terms that you do not want excluded (To exclude a word in mongo, you can prepend a “-” character):

let generateFullSearchText = function (nonSpecialSearchTerms) {
  let termsQuery = '';
  nonSpecialSearchTerms.forEach(searchTerm => {
    if ( searchTerm.startsWith('-') ) {
      termsQuery += ' ' + searchTerm;
    } else { //wrap it in quotes to make it a default AND in search
      termsQuery += ' "' + searchTerm.substring(0, searchTerm.length) + '"';
    }
  });

  return termsQuery.trim();
};

Reference - https://docs.mongodb.com/manual/text-search/


Shared with from Bookmarks.dev. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Project: bookmarks.dev - File: personal-bookmarks-search.service.js

Use the mongo cursor skip method with offset in combination with the limit method:

let getPersonalBookmarksForSearchFilter = async function (filter, pageNumber, nPerPage) {

  let bookmarks = await Bookmark.find(
    filter,
    {
      score: {$meta: "textScore"}
    }
  )
    .sort({score: {$meta: "textScore"}})
    .skip(pageNumber > 0 ? ((pageNumber - 1) * nPerPage) : 0)
    .limit(nPerPage)
    .lean()
    .exec();

  return bookmarks;
}

Reference - https://docs.mongodb.com/manual/reference/method/cursor.skip/


Shared with from Bookmarks.dev. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Project: bookmarks.dev - File: personal-bookmarks.service.js

Use the sort and limit methods. In the following example we receive the latest 30 (limit) created bookmarks (sort descending by createdAt date) :

let getLastCreatedBookmarks = async (userId) => {
  const bookmarks = await Bookmark.find({userId: userId})
    .sort({createdAt: -1}) // -1 for descending sort
    .limit(30);

  return bookmarks;
};

Reference - https://docs.mongodb.com/manual/reference/method/cursor.sort/


Shared with from Bookmarks.dev. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Suppose we have the following table, and we often execute queries to display the bookmarks belonging to a certain category:

CREATE TABLE bookmark (
    id           NUMBER(10, 0), -- number 10 digits before the decimal and 0 digits after the decimal
    title        VARCHAR2(255 CHAR) NOT NULL, -- String with a maximum length of 255 charachters
    url          VARCHAR2(500 CHAR) UNIQUE NOT NULL, -- holds unique values across the table data
    category     VARCHAR2(500 CHAR) NOT NULL, -- holds unique values across the table data
    is_public    NUMBER(1, 0) NOT NULL, -- plays the role of a boolean '0'-false, '1'-true ,
    created_at   DATE NOT NULL, --  when the entry is created
    PRIMARY KEY ( id )
);

Then an index on the column category would make the query more performant. To create it, issue the following command (creates by default a B-Tree index):

CREATE INDEX category_i ON bookmark (category);

--confirm its creation by querying the existing index on the table
SELECT * FROM all_indexes WHERE table_name = 'BOOKMARK';

Now, maybe retrieving the latest bookmarks with a category is also a common scenario, so it might be worth creating then an index on multiple columns, in our case category and created_at. The command would look something like the following:

CREATE INDEX category_created_at_i ON bookmark (category, created_at DESC);

-- this would be a perfect fit for the following query
SELECT * FROM bookmark WHERE category='blog' ORDER BY created_at DESC;

To delete the created indexes use the DROP INDEX commands:

DROP INDEX category_i;
DROP INDEX category_created_at_i;

Reference - https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database


Shared with from Bookmarks.dev. Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

To use a PL/SQL cursor we set up a scenario where we want to update values from a main table and also add auditing data about it. We create some tables in oracle database to hold data about bookmarks and revision info data (bookmarks auditing table, and a revision info table):

  • BOOKMARK - to hold data about bookmarks
  • REVINFO - holds data about the revisions that took place in our system
  • BOOKMARK_AUD - audit table, to hold data from the main table (BOOKMARK) that were affected by the different revisions

The concept from the scenarios is inspired from the Hibernate Envers project, where you would the same functionality in java, automatically, with annotations. There might be cases where you need to this at the SQL level, maybe for some single time house keeping jobs or similar…

CREATE TABLE bookmark (
    id                 NUMBER(10, 0), -- number 10 digits before the decimal and 0 digits after the decimal
    title              VARCHAR2(255 CHAR) NOT NULL, -- String with a maximum length of 255 charachters
    url                VARCHAR2(500 CHAR) UNIQUE NOT NULL, -- holds unique values across the table data
    category      VARCHAR2(500 CHAR) NOT NULL, -- holds unique values across the table data
    is_public      NUMBER(1, 0) NOT NULL, -- plays the role of a boolean '0'-false, '1'-true ,
    created_at   DATE NOT NULL, --  when the entry is created
    PRIMARY KEY( id )
);


-- revision info table
CREATE TABLE revinfo
(
    id NUMBER(10, 0) NOT NULL,
    revtstmp NUMBER(10, 0), -- unix time in milliseconds
    PRIMARY KEY( id )
)

-- audit table for BOOKMARK table
CREATE TABLE bookmark_aud (
    id                 NUMBER(10, 0),
    rev NUMBER(10, 0) NOT NULL, -- the id of the revision
    revtype NUMBER(1, 0), -- 0=CREATE, 1=MODIFY and 2=DELETE
    title              VARCHAR2(255 CHAR) NOT NULL,
    url                VARCHAR2(500 CHAR) UNIQUE NOT NULL,
    category      VARCHAR2(500 CHAR) NOT NULL,
    is_public      NUMBER(1, 0) NOT NULL,
    created_at   DATE NOT NULL,
    PRIMARY KEY( id )
);

More exactly, we want to update all the entries from the bookmark table that have a given category (v_category in plsql code), with a new one (v_new_category in the plsql script). We select the values afect in a cursor (bookmark_cur in plsql script), which we iterate over in a plsql FOR LOOP to do the individual updates and inserts. If any exception occurs in the loop it will be printed in the DBMS_OUTPUT:

SET SERVEROUTPUT ON;
DECLARE
    v_category VARCHAR2(500) := 'javax';
    v_new_category VARCHAR2(500) := 'java';
    next_rev_id NUMBER := HIBERNATE_SEQUENCE.nextval;
    created_at NUMBER(19, 0);
    CURSOR bookmark_cur IS
        SELECT
            *
        FROM
            bookmark
        WHERE
          category = v_category
       );
BEGIN
    -- get current timestamp as number
    SELECT
                EXTRACT(DAY FROM(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000
            + to_number(TO_CHAR(sys_extract_utc(systimestamp), 'SSSSSFF3'))
    INTO created_at
    FROM
        dual;

    -- update REVISION table
    INSERT
    INTO revinfo (
               rev,
               revtstmp
    )
    VALUES (
       next_rev_id,
       created_at
    );

    FOR c IN bookmark_cur LOOP
        BEGIN
            -- update BOOKMARK table
            UPDATE bookmark
            SET
                category = v_new_category
            WHERE
                 id = c.id;

            -- INSERT ENTRY into BOOKMARK_AUD table
            INSERT INTO bookmark_aud (
               id
               title,
               url,
               category,
               is_public,
               created_at,
               rev,
               revtype
            ) VALUES (
               c.id
               c.title,
               c.url,
               c.category,
               c.is_public,
               c. created_at,
               next_rev_id,
               1 -- 1=UPDATE/MODIFY (0=INSERT and 2=DELETE)
           );
        EXCEPTION
            WHEN OTHERS THEN --handle all exceptions
                DBMS_OUTPUT.PUT_LINE('Exception' || SQLCODE || ' captured for bookmark with id ' || c.id);
        END;
    END LOOP;
END;
/

But beware, it is not recommended using a cursor FOR loop if the body executes non-query DML (INSERT, UPDATE, DELETE, MERGE), because the INSERT or UPDATE will happen on a row-by-row basis1.

In this particular case you can ditch the cursor and use use plain SQL updates and inserts in the plsql body, as shown in the snippet below:

BEGIN
    -- get current timestamp as number
    SELECT
                EXTRACT(DAY FROM(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000
            + to_number(TO_CHAR(sys_extract_utc(systimestamp), 'SSSSSFF3'))
    INTO created_at
    FROM
        dual;

    -- update REVISION table
    INSERT
        INTO revinfo (
           rev,
           revtstmp
        )
        VALUES (
           next_rev_id,
           created_at
        );


    -- update BOOKMARK table
    UPDATE bookmark
    SET
        category = v_new_category
    WHERE
        category = v_category; --

    -- INSERT ENTRY into BOOKMARK_AUD table
    INSERT INTO bookmark_aud (
        id
        title,
        url,
        category,
        is_public,
        created_at,
        rev,
        revtype
    )
    SELECT
        c.id
        c.title,
        c.url,
        c.category,
        c.is_public,
        c. created_at,
        next_rev_id,
        1 -- 1=UPDATE/MODIFY (0=INSERT and 2=DELETE)
    FROM bookmark c
    WHERE
        c.id in (
           SELECT
               id
           FROM
                bookmark
           WHERE
             category = v_category
        )
    );
    EXCEPTION
        WHEN OTHERS THEN --handle all exceptions
              DBMS_OUTPUT.PUT_LINE('Exception' || SQLCODE || ' captured for bookmark with id ' || c.id);
END;
/

Note some times is not possible and then you might have to stick to the cursor. In oracle you can take advantage of the CURSOR bulk updates and INSERTS2

References