How would the mime type [1] be decided when serving a resource. It is important to set the mime type properly so that browsers will trigger the necessary program to open a downloaded file.
Are we going to save the mime type sent when uploading the resource or are we going to use a predefined set based on data type ? Thanks Jo [1] https://www.sitepoint.com/web-foundations/mime-types-complete-list/ On Fri, Nov 18, 2016 at 3:52 PM, Uvindra Dias Jayasinha <[email protected]> wrote: > Further amended to include a resource name column, though this should be > used for display purposes, download will happen via the RESOURCE_ID since > that is the primary key > > CREATE TABLE `AM_RESOURCE_DATA_TYPES` ( > `DATA_TYPE_ID` VARCHAR(255), > `DATA_TYPE_NAME` VARCHAR(255), > PRIMARY KEY (`DATA_TYPE_ID`) > ); > > CREATE TABLE `AM_RESOURCE_TYPES` ( > `RESOURCE_TYPE_ID` VARCHAR(255), > `DATA_TYPE_ID` INTEGER, > `RESOURCE_TYPE_NAME` VARCHAR(255), > PRIMARY KEY (`RESOURCE_TYPE_ID`), > FOREIGN KEY (`DATA_TYPE_ID`) REFERENCES `AM_RESOURCE_DATA_TYPES`(` > DATA_TYPE_ID`) > ); > > CREATE TABLE `AM_API_RESOURCES` ( > `RESOURCE_ID` VARCHAR(255), > `API_ID` VARCHAR(255), > *`RESOURCE_NAME` VARCHAR(255),* > `RESOURCE_TYPE_ID` INTEGER, > `RESOURCE_TEXT_VALUE` VARCHAR(1024), > `RESOURCE_BINARY_VALUE` BLOB, > PRIMARY KEY (`RESOURCE_ID`), > FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`UUID`) ON UPDATE CASCADE ON > DELETE CASCADE, > FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES > `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) > ); > > On 18 November 2016 at 15:02, Tharindu Dharmarathna <[email protected]> > wrote: > >> Hi Uvindra, >> >> Don't we need to keep the resource name inside the table . As we give >> document download capability, We need to give the attachment name for >> download. >> >> On Fri, Nov 18, 2016 at 12:22 PM, Uvindra Dias Jayasinha < >> [email protected]> wrote: >> >>> Hi Lahiru, >>> >>> Yes what you have suggested is a lot more normalised, but I still prefer >>> to refer to it as a DATA_TYPE as opposed to a FILE_TYPE. The reason is that >>> File types are always stored in BLOB format where as shorter text data is >>> stored as VARCHAR. So referring to free form text as a file type is >>> misleading, because it isn't treated in the same way that files are >>> treated. So to be more generic its better to consider these as Data Types. >>> >>> So this would be the more normalised table structure, >>> >>> CREATE TABLE `AM_RESOURCE_DATA_TYPES` ( >>> `DATA_TYPE_ID` INTEGER AUTO_INCREMENT, >>> `DATA_TYPE_NAME` VARCHAR(255), >>> PRIMARY KEY (`DATA_TYPE_ID`) >>> ); >>> >>> CREATE TABLE `AM_RESOURCE_TYPES` ( >>> `RESOURCE_TYPE_ID` INTEGER AUTO_INCREMENT, >>> `DATA_TYPE_ID` INTEGER, >>> `RESOURCE_TYPE_NAME` VARCHAR(255), >>> PRIMARY KEY (`RESOURCE_TYPE_ID`), >>> FOREIGN KEY (`DATA_TYPE_ID`) REFERENCES `AM_RESOURCE_DATA_TYPES`(`DATA >>> _TYPE_ID`) >>> ); >>> >>> CREATE TABLE `AM_API_RESOURCES` ( >>> `RESOURCE_ID` INTEGER AUTO_INCREMENT, >>> `API_ID` VARCHAR(255), >>> `RESOURCE_TYPE_ID` INTEGER, >>> `RESOURCE_TEXT_VALUE` VARCHAR(1024), >>> `RESOURCE_BINARY_VALUE` BLOB, >>> PRIMARY KEY (`RESOURCE_ID`), >>> FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`UUID`) ON UPDATE CASCADE >>> ON DELETE CASCADE, >>> FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES >>> `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) >>> ); >>> >>> >>> >>> On 13 November 2016 at 12:35, Lahiru Cooray <[email protected]> wrote: >>> >>>> Hi, >>>> Well if I use an example to explain what I said, refer data_type column. >>>> Check the given sample values. >>>> xml,png,json are file extensions while text is a magic >>>> word/implementation logic we use to refer urls and inline text (file type). >>>> AFAIC these are different data. >>>> Even if we treat this as a generic field, we could further nomalize the >>>> DB structure having this field in resource type table. >>>> >>>> eg: >>>> CREATE TABLE `AM_FILE_TYPES` ( >>>> `FILE_TYPE_ID` INTEGER AUTO_INCREMENT, >>>> `FILE_TYPE_NAME` VARCHAR(255), >>>> PRIMARY KEY (`FILE_TYPE_ID `) >>>> ); >>>> (file type: either blob/inline text or url) >>>> >>>> CREATE TABLE `AM_RESOURCE_TYPES` ( >>>> `RESOURCE_TYPE_ID` INTEGER AUTO_INCREMENT, >>>> `RESOURCE_TYPE_NAME` VARCHAR(255), >>>> `FILE_TYPE_ID ` INTEGER, >>>> PRIMARY KEY (`RESOURCE_TYPE_ID`), >>>> FOREIGN KEY (`FILE_TYPE_ID `) REFERENCES `AM_FILE_TYPES `(` >>>> FILE_TYPE_ID `) ON UPDATE CASCADE ON DELETE CASCADE, >>>> >>>> ); >>>> >>>> CREATE TABLE `AM_API_RESOURCES` ( >>>> `RESOURCE_ID` INTEGER AUTO_INCREMENT, >>>> `API_ID` INTEGER, >>>> `RESOURCE_TYPE_ID` INTEGER, >>>> // `DATA_TYPE` VARCHAR(255), [remove this field] >>>> `RESOURCE_TEXT_VALUE` VARCHAR(1024), >>>> `RESOURCE_BINARY_VALUE` BLOB, >>>> PRIMARY KEY (`RESOURCE_ID`), >>>> FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`API_ID`) ON UPDATE >>>> CASCADE ON DELETE CASCADE, >>>> FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES >>>> `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) ON UPDATE CASCADE ON DELETE >>>> CASCADE >>>> ); >>>> >>>> >>>> further if required, we could maintain a extension field in >>>> AM_API_RESOURCES >>>> table. >>>> >>>> I'm not telling that the proposed structure is wrong. This is just a >>>> suggestion to further normalize and minimize data repetition and to make >>>> the structure more clearer. >>>> >>>> On Wed, Nov 9, 2016 at 4:54 PM, Uvindra Dias Jayasinha < >>>> [email protected]> wrote: >>>> >>>>> >>>>> >>>>> On 9 November 2016 at 12:36, Lahiru Cooray <[email protected]> wrote: >>>>> >>>>>> >>>>>> >>>>>> On Tue, Nov 8, 2016 at 1:23 PM, Uvindra Dias Jayasinha < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> >>>>>>> >>>>>>> On 8 November 2016 at 11:10, Lahiru Cooray <[email protected]> wrote: >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Nov 3, 2016 at 4:01 PM, Uvindra Dias Jayasinha < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> Hi All, >>>>>>>>> >>>>>>>>> Currently APIs have a few resources such as Swagger File, Optional >>>>>>>>> WSDL file, Related Documents file and an Optional Thumbnail image that >>>>>>>>> needs to be stored as blobs in the DB. >>>>>>>>> >>>>>>>>> Initially we thought of having separate tables to store these >>>>>>>>> resources, but what if we have a single generic resource table to >>>>>>>>> store all >>>>>>>>> these? >>>>>>>>> >>>>>>>>> We could have schema such as below for the generic resource table >>>>>>>>> >>>>>>>>> >>>>>>>>> Since we previously stored our resources in the registry, a >>>>>>>>> similar generic schema was used to store all such resources by the >>>>>>>>> registry >>>>>>>>> itself. So anything that is not a text data type can be considered as >>>>>>>>> a >>>>>>>>> BLOB. >>>>>>>>> >>>>>>>>> The advantages of doing this are, >>>>>>>>> >>>>>>>>> 1. Can manage all API related resources from a central table >>>>>>>>> without having to define custom tables for each resource. >>>>>>>>> >>>>>>>> +1 >>>>>>>> >>>>>>>>> 2. When an API is deleted its very easy to locate and remove all >>>>>>>>> the resources related with it >>>>>>>>> >>>>>>>> +1 >>>>>>>> >>>>>>>>> 3. When a new version of an API is created its very easy to copy >>>>>>>>> over the resources associated with the previous version to the new >>>>>>>>> one. >>>>>>>>> >>>>>>>> Do we have a new API_ID for each version or do we have multiple >>>>>>>> version no's against an API_ID? Then we need to maintain Version also >>>>>>>> as >>>>>>>> another column >>>>>>>> >>>>>>> >>>>>>> A new API version will have a new API_ID. >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>>> WDYT? >>>>>>>>> >>>>>>>>> >>>>>>>> +1 for the idea. >>>>>>>> Also is DATA_TYPE mean the file extension? if so I suggest to >>>>>>>> rename the name and also keep the file name as another column. >>>>>>>> >>>>>>> >>>>>>> In some case we are saving URLs with DATA_TYPE column as TEXT so >>>>>>> those aren't files. To be generic its better to keep this as DATA_TYPE >>>>>>> >>>>>> >>>>>> In that case I have a different suggestion. Im not sure if >>>>>> maintaining irrelevant data in same column is a good practice. >>>>>> Can't we maintain the file type (eg: binary or inline) in the resources >>>>>> types table which Akalanka suggested? >>>>>> And in this transaction table, if the file type is inline we could >>>>>> store the url in a separate column and if the file type is a binary we >>>>>> could store binary value/file name/extension in separate columns. >>>>>> >>>>>> As mentioned earlier we dont store the data in the same column. There >>>>> is a VARCHAR column(RESOURCE_TEXT_VALUE) and a BLOB >>>>> column(RESOURCE_BINARY_VALUE). We are using a separate table to store the >>>>> type as Akalanka suggested, below are the tables, >>>>> >>>>> CREATE TABLE `AM_RESOURCE_TYPES` ( >>>>> `RESOURCE_TYPE_ID` INTEGER AUTO_INCREMENT, >>>>> `RESOURCE_TYPE_NAME` VARCHAR(255), >>>>> PRIMARY KEY (`RESOURCE_TYPE_ID`) >>>>> ); >>>>> >>>>> CREATE TABLE `AM_API_RESOURCES` ( >>>>> `RESOURCE_ID` INTEGER AUTO_INCREMENT, >>>>> `API_ID` INTEGER, >>>>> `RESOURCE_TYPE_ID` INTEGER, >>>>> `DATA_TYPE` VARCHAR(255), >>>>> `RESOURCE_TEXT_VALUE` VARCHAR(1024), >>>>> `RESOURCE_BINARY_VALUE` BLOB, >>>>> PRIMARY KEY (`RESOURCE_ID`), >>>>> FOREIGN KEY (`API_ID`) REFERENCES `AM_API`(`API_ID`) ON UPDATE >>>>> CASCADE ON DELETE CASCADE, >>>>> FOREIGN KEY (`RESOURCE_TYPE_ID`) REFERENCES >>>>> `AM_RESOURCE_TYPES`(`RESOURCE_TYPE_ID`) ON UPDATE CASCADE ON DELETE >>>>> CASCADE >>>>> ); >>>>> >>>>>> >>>>>>>>> -- >>>>>>>>> Regards, >>>>>>>>> Uvindra >>>>>>>>> >>>>>>>>> Mobile: 777733962 >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> Architecture mailing list >>>>>>>>> [email protected] >>>>>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> *Lahiru Cooray* >>>>>>>> Software Engineer >>>>>>>> WSO2, Inc.;http://wso2.com/ >>>>>>>> lean.enterprise.middleware >>>>>>>> >>>>>>>> Mobile: +94 715 654154 >>>>>>>> >>>>>>>> _______________________________________________ >>>>>>>> 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 >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> *Lahiru Cooray* >>>>>> Software Engineer >>>>>> WSO2, Inc.;http://wso2.com/ >>>>>> lean.enterprise.middleware >>>>>> >>>>>> Mobile: +94 715 654154 >>>>>> >>>>>> _______________________________________________ >>>>>> 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 >>>>> >>>>> >>>> >>>> >>>> -- >>>> *Lahiru Cooray* >>>> Software Engineer >>>> WSO2, Inc.;http://wso2.com/ >>>> lean.enterprise.middleware >>>> >>>> Mobile: +94 715 654154 >>>> >>>> _______________________________________________ >>>> 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 >>> >>> >> >> >> -- >> Thanks >> >> *Tharindu Dharmarathna*Software Engineer >> WSO2 Inc.; http://wso2.com >> lean.enterprise.middleware >> >> mobile: *+94779109091 <%2B94779109091>* >> > > > > -- > 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
