The flat file approach is also an option for us, thanks for suggesting it Akila. At the moment we dont have a feature to make use of audit information and it is not a priority for us to implement. So we will revisit our options latter on if we do decide to implement this.
On 23 October 2016 at 09:51, Akila Ravihansa Perera <raviha...@wso2.com> wrote: > Hi, > > What exactly is the purpose of Audit table or tables? Will those be used > to query the history and display it to the user through the system? Or is > it only for auditing purposes in which APIM will never directly query the > data but a separate system or tool will use. > > If it is the latter case then why not simply use an auditing log file? We > define a well structured format and keep appending audit events. It should > be a flat structure. In production, logs are usually centralized (ELK, > Splunk etc) so file-system errors won't matter. > > Thanks. > > On Sat, Oct 22, 2016 at 11:21 AM, Abimaran Kugathasan <abima...@wso2.com> > wrote: > >> >> >> On Fri, Oct 21, 2016 at 12:10 PM, Bhathiya Jayasekara <bhath...@wso2.com> >> wrote: >> >>> >>> On Wed, Oct 12, 2016 at 12:30 PM, Inosh Goonewardena <in...@wso2.com> >>> wrote: >>> >>>> >>>> >>>> On Tue, Oct 11, 2016 at 2:40 PM, Uvindra Dias Jayasinha < >>>> uvin...@wso2.com> wrote: >>>> >>>>> Thanks for the feedback, some interesting points were brought up >>>>> >>>>> @Abimaran, the problem with maintaining a rigid structure like old/new >>>>> column is that if a user changes the value of 5 columns at a given time >>>>> that would mean 5 different inserts to the table, when in actual fact it >>>>> was a single transaction that took place when the user did the change and >>>>> saved. So its better to use a implementation like >>>>> google-diff-match-patch[1] to record the string diff between the values of >>>>> the columns before the change took place and after the update. Though we >>>>> dont need to worry about this implementation detail for now. The idea of >>>>> using a single table to store the history of all tables that will require >>>>> auditing sounds good. >>>>> >>>>> @Sanjeewa, yes this would improve performance when trying to retreive >>>>> the LAST_UPDATED_TIME for a given entity. >>>>> >>>>> Let me elaborate a bit on Sanjeewa's point. So there can be only one >>>>> CREATED_BY and CREATED_TIME for a given entity so that can remain as part >>>>> of the original entities schema. Having the LAST_UPDATED_TIME as part of >>>>> the original entities schema gives a performance advantage on checking if >>>>> a >>>>> given entity has been modified since it was last checked. This is vital >>>>> for >>>>> features such as ETags support for the REST API. So CREATED_BY, >>>>> CREATED_TIME, LAST_UPDATED_TIME can remain with the original entities >>>>> schema. >>>>> >>>>> We can still use the master audit table(building on Abimarans idea) to >>>>> actually keep track of change history of a given entity, so that table >>>>> could look like this, >>>>> >>>>> ENTRY_ID PK >>>>> TABLE_NAME VARCHAR >>>>> ENTITY_ID FK >>>>> DIFF BLOB >>>>> ACTION *VARCHAR* >>>>> ACTION_BY *VARCHAR* >>>>> ACTION_TIME *TIMESTAMP* >>>>> >>>>> >>>> +1 for having single audit table and recording a diff value. Using a >>>> structure with old/new column could become unmanageable when the changes >>>> happen to multiple columns. Also we are planning to do audit table updates >>>> from our code right? Database level triggers can be used in such cases but >>>> IMO we should avoid using triggers since it could affect the performance. >>>> >>> >>> Another reason to avoid database triggers is that if we use triggers, we >>> complemetely depend on trigger support in each database. So we may not be >>> able to support all databases, and we'll have to spend a lot of time and >>> effort to support auditing for different databases. >>> >>> Another important thing is that when we implement auditing from our >>> code, we need to make sure to do it in async manner to avoid performance >>> degradations. >>> >> >> Normally auditing with trigger is used in financial system where auditing >> is very important, but in our case we don't have much importance for >> auditing, so we can avoid using triggers. >> >> >>> >>> Thanks, >>> Bhathiya >>> >>> >>> >>>> >>>> >>>> >>>>> >>>> >>>>> [1] https://code.google.com/p/google-diff-match-patch/ >>>>> >>>>> On 11 October 2016 at 13:44, Sanjeewa Malalgoda <sanje...@wso2.com> >>>>> wrote: >>>>> >>>>>> I think we can manage audit table while still having CREATED_BY, >>>>>> CREATED_TIME,UPDATED_BY, UPDATED_TIME in same tables. So with that >>>>>> approach we may never need to do table scan of audit table while fetching >>>>>> updates. So each updates will recorded in separate table while original >>>>>> table having all relevant information. WDYT? >>>>>> >>>>>> Thanks, >>>>>> sanjeewa. >>>>>> >>>>>> _______________________________________________ >>>>>> Architecture mailing list >>>>>> Architecture@wso2.org >>>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Regards, >>>>> Uvindra >>>>> >>>>> Mobile: 777733962 >>>>> >>>>> _______________________________________________ >>>>> Architecture mailing list >>>>> Architecture@wso2.org >>>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>>> >>>>> >>>> >>>> >>>> -- >>>> Thanks & Regards, >>>> >>>> Inosh Goonewardena >>>> Associate Technical Lead- WSO2 Inc. >>>> Mobile: +94779966317 >>>> >>>> _______________________________________________ >>>> Architecture mailing list >>>> Architecture@wso2.org >>>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>>> >>>> >>> >>> >>> -- >>> *Bhathiya Jayasekara* >>> *Senior Software Engineer,* >>> *WSO2 inc., http://wso2.com <http://wso2.com>* >>> >>> *Phone: +94715478185 <%2B94715478185>* >>> *LinkedIn: http://www.linkedin.com/in/bhathiyaj >>> <http://www.linkedin.com/in/bhathiyaj>* >>> *Twitter: https://twitter.com/bhathiyax <https://twitter.com/bhathiyax>* >>> *Blog: http://movingaheadblog.blogspot.com >>> <http://movingaheadblog.blogspot.com/>* >>> >>> _______________________________________________ >>> Architecture mailing list >>> Architecture@wso2.org >>> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >>> >>> >> >> >> -- >> Thanks >> Abimaran Kugathasan >> Senior Software Engineer - API Technologies >> >> Email : abima...@wso2.com >> Mobile : +94 773922820 >> >> <http://stackoverflow.com/users/515034> >> <http://lk.linkedin.com/in/abimaran> >> <http://www.lkabimaran.blogspot.com/> <https://github.com/abimarank> >> <https://twitter.com/abimaran> >> >> >> _______________________________________________ >> Architecture mailing list >> Architecture@wso2.org >> https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture >> >> > > > -- > Akila Ravihansa Perera > WSO2 Inc.; http://wso2.com/ > > Blog: http://ravihansa3000.blogspot.com > > _______________________________________________ > Architecture mailing list > Architecture@wso2.org > https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture > > -- Regards, Uvindra Mobile: 777733962
_______________________________________________ Architecture mailing list Architecture@wso2.org https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
