Hi Jo, my responses are inline. On 16 November 2016 at 13:03, Joseph Fonseka <[email protected]> wrote:
> Hi Uvindra > > How do we paginate searched and sorted data, searching and sorting will be > hard without using a DBMSs support ? And I think we can come up with > simpler solutions for the points you have mention and still stick with a > DBMS. > Searching and sorting will be done at DB level via SQL. It is only the pagination of the returned result set that will be done at Java level. > > On Wed, Nov 16, 2016 at 10:34 AM, Uvindra Dias Jayasinha <[email protected] > > wrote: > >> >>>> 1. Display only the latest version of a given published API by the API >>>> Store - Since the version is a VARCHAR column we cannot assume it is >>>> numerical so its not straightforward to find the latest version of a given >>>> API via SQL. >>>> >>> > As a solution we can introduce a numeric version column which will be > updated when the API is saved based on the version string given. With that > update cost is high retrieve cost is low. > Here we are adding complexity just to be able to support this from the DB. We dont know what version strategy the end user will use so its going to difficult to decide how we are going to populate this numeric version column. > > >> >>>> 2. Validate an APIs roles against a given users roles to validate >>>> eligibility to view the API - In situations where users have a large number >>>> of roles assigned to them we will need to pass all these roles in the WHERE >>>> clause of the SELECT statement when filtering the result set further >>>> complicating statement. >>>> >>> > If the visibility is only depend on roles I guess it is a straight forward > thing to implement with SQL. When you mention large number of roles what > would be a rough amount we have to expect and in which scenarios ? > Agreed it can be done at SQL level but its a lot easier to read the entire result set without the where for roles and then validate it. Think of a scenario of a user that has a 1000 roles(from a real scenario that was encountered), the WHERE clause will need to check for all 1000 roles which is a great way to slow down the query. So this can get pretty heavy. > >> >>>> Given that in the C5 tenancy model each tenant will have there own DB >>>> instance, data numbers will be quite low. For example it will be unusual to >>>> have a 1000 APIs in the DB. So in this scenario its a lot easier to read >>>> the entire result set into memory at the back end and then paginate what is >>>> returned to the caller at Java level along with doing permission checks. >>>> the advantage of this is, >>>> >>> > Even though its unusual there are existing users with 700+ APIs so we > should be able to support it. > Looping over 10,000 results in Java and paginating can be done fairly quickly, so this can even scale up to 10,000 APIs. > >> 1. Minimise the need to maintain vendor specific SQL for pagination >>>> 2. Simplifies SQL statements >>>> >>> > If the above is a big problem we can see the possibility of using a > in-memory db (ie h2). Then we only have to maintain SQL queries only for > that. I guess that will be lot efficient than processing raw data using > Java. We can load the data to in-memory db on-demand. > > Why would this be more efficient than Java? We achieve a similar thing by using a local cache so there is no need to have an intermediate in memory DB. What was proposed makes sense when you consider that we a dealing with only a very small amount of data at DB level now. In the future if we foresee a requirement for DB level pagination we can implement that within the DAO layer, but for now this is more than enough to meet our needs. This way we can avoid introducing unwanted complexity upfront since there is no rationale for doing so. > Thanks & Regards > Jo > > > > > > > > > -- >>> With regards, >>> *Manu*ranga Perera. >>> >>> phone : 071 7 70 20 50 >>> mail : [email protected] >>> >>> _______________________________________________ >>> Architecture mailing list >>> [email protected] >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> Regards, >> Uvindra >> >> Mobile: 777733962 >> >> _______________________________________________ >> Architecture mailing list >> [email protected] >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > > -- > *Joseph Fonseka* > WSO2 Inc.; http://wso2.com > lean.enterprise.middleware > > mobile: +94 772 512 430 > skype: jpfonseka > > * <http://lk.linkedin.com/in/rumeshbandara>* > > > _______________________________________________ > Architecture mailing list > [email protected] > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Regards, Uvindra Mobile: 777733962
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
