Cross database pagination problem and how we solved it

Cross database pagination problem and how we solved it

When we are developing a webapp with any type of listing, pagination is a must have feature. From the UI perspective we can use infinite scrolling or pages approach.

If you are familiar with google search this is how google uses the second approach,

Now the problem is how we generate listing for a particular page?
Lets take a hypothetical library application where we need to list down existing books, assume we need to list 10 books per page.

This is how we query books to generate the 1st page,

SELECT * FROM BOOKS LIMIT 10;

to generate the 2nd page,

SELECT * FROM BOOKS LIMIT 10 OFFSET 10;

So easy,  by providing the offset value we can dynamically generate our pages.

BUT can we use the same across database types?

If you know the exact backend DB type you gonna use in production, lucky you!
But if you are developing a product which needs to support multiple DB types (MySQL, Oracle, Postgres, H2 ....) above query will fail in some of them. For example above will work in MySQL, H2 and Postgres but will not work in Oracle.

In Oracle 11g you can run following but not in 12c

SELECT *
FROM (
 SELECT b.*, ROWNUM RN
 FROM (
 SELECT *
 FROM BOOKS
 ORDER BY ID ASC
 ) b
 WHERE ROWNUM <= 10
)
WHERE RN > 10

Now lets discuss how we solved this problem. Following is high-level architecture of the solution.







In this scenario we have exposed all CRUD operations as OSGI services to the outside world. This component may or may not have DB specific logic. But for sure we dont have any DB type specific logic within our main OSGI component. We have delegated all those DB type specific logic to the DB Adapter.

What are the DB type specific things we can have?

1. Above pagination related stuff (LIMIT, OFFSET)
2. Auto generate key usage(getGenaratedKeys)

How to load correct DB Adapter class through reflection

We can maintain a configuration file where we state the DB Adapter class. Then read that class name within our main component and use/call through reflection.

Reference

http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/

{ Read More }

Converting Windows 10 32 bit Windows 10 64 bit problem

Converting Windows 10 32 bit Windows 10 64 bit problem

Problem
Trying to find a way to Convert Windows 10 32 bit to Windows 10 64 bit


1.  Download ndows

{ Read More }

Create file folder on new HD Partition easily Permission problem solved

Create file folder on new HD Partition easily Permission problem solved

After fresh Linux install we may find that we have no permission to create file or folder to a new or another hard-disk partition. This is a safety feature of Linux OS. We have to get permission to write in that specific hard disk partition. I think you can ask this in some various way.

No permission to create file or folder on another hard-disk partition drive. Look at the mouse pointer.

1. How to create file of folder on a new hard disk partition?
2. How to get permission to create file and folder to another hard-disk partition?
3. Writing is disabled on HD partition. How to enable it.
4. How to enable writing permission on a specific folder or in a hd partition drive? etc.

I have write two post about it. One is Can not create file folder on newly installed Ubuntu [Solved] and another is ext4 partition require password to view files. Both are quite geeky solution. But I discovered most easy way to do this job. That is enabling permission to write or create on another hd drive.

Here is the solution tips step by step.. follow the tutorial below...

• a. Open terminal from Menu.
• b. Type "sudo nautilus"
• c. Supply password (It will not shown. Go typing). You are going to get root privileged to change anything on that correspondence partition. A new Nautilus window manager will open.
• d. Now open that specific Hard disk partition or another drive using opened nautilus window manager.
•  
• Right click menu on root nautilus. Click Properties to change options
• e. Right click there. A task menu will open.
• f. Point to Properties at the bottom of the menu and Click (Left click).
• g. Drive properties will open. Heed to Permission TAB.
• 
Properties window. Heed to Permission TAB
• h. Here you can enable permission for System Owner or for Group or Others.
• i. If you just want to get writing permission for your own, then click on the drop down menu just behind the "Owner" text.
• 
Hard disk Owner long menu
• j. A long menu will open.
• k. Scroll up or down and find your name their. Select your name.
• l. Change Folder access and File access to your name instead of ROOT. Give right to Create and Delete files permission for both.
• m. If there are many file and folder on that hard-disk drive then click once on "Apply Permissions to Enclosed Files". All file and folder access will not be locked to you.

Apply Permission to Enclosed Files to get permission to all files and folders which are found into that hard disk or any folder.

Everything has done. Just close that Properties window and return to your Hard Disk drive to check.
You will discover that now you are able to create file or folder on that hard-disk drive.

create file folder on another hard disk drive partition Now it will be easy for you creating or copying any file or folder on that hard-drive partition. Nothing will be locked for you. All locked folder and files will open instantly without any key. How funny???!!%%!

{ Read More }