Does Aware support full CRUD operation with External DB?

[BenHayat]

I have never used External Database in Aware. But I may need to now.

Can we perform full CRUD operation on External Databases that is created by other applications? For example, can I write an scheduled process that would read certain table and makes updates to it or use another table to perform emails in batch and etc?

In other words, is external database accessed in READ-ONLY or will Aware perform full CRUD operation?
Thanks!

[customaware]

CRUD is ok Ben.

Except for the changes in V7 regarding FK etc which I have yet to look at, you can do what you like except the implementation of Aware Reference Attribute features.

[BenHayat]

Ability to do CRUD is awesome!!! Thanks Mark!

except the implementation of Aware Reference Attribute features.

Can you please elaborate on this? I want to make sure what I can't do.
Compared to the tables/Objects created in Aware, what features of Aware will I lose when dealing with External DB?

My pan of action with this project:
Basically, this is a case where Aware will be strictly used as a batch processing, based scheduled processes. There are no users online or might be one or two admin invoking batch jobs.

Is this suitable for Aware?

Question: Doing PDF Search in Userguide V7.1, I don't see much info on the features of External DB.
Did Aware extend this feature in V8?

[BLOMASKY]

Rachel is the expert at this, and has found everything works just like you would expect except:

1). You can NOT change the data structure within aware. You have to Import the definition. So if any changes have to be done, they have to be done externally then the schema is re-imported

2). For all Aware relationships between tables, Aware will create 3 columns in the "child" table. Since external tables are NOT modified by aware, you can not have aware style relationships and can not have rules like "IF BO.ChildTable WAS CHANGED"

Otherwise, all is good.

Bruce

[BenHayat]

2). For all Aware relationships between tables, Aware will create 3 columns in the "child" table. Since external tables are NOT modified by aware, you can not have aware style relationships and can not have rules like "IF BO.ChildTable WAS CHANGED"

Bruce

Even thought Aware imports the schema, it does not use the existing foreign keys to build reference to related tables?
Thanks!

[BenHayat]

2). For all Aware relationships between tables, Aware will create 3 columns in the "child" table. Since external tables are NOT modified by aware, you can not have aware style relationships and can not have rules like "IF BO.ChildTable WAS CHANGED"

Bruce

Even thought Aware imports the schema, it does not use the existing foreign keys to build reference to related tables?

So, how do we get Aware to recognize the relationships between tables? I'm not using a single table as external table, I'll be importing several, like customer and it's orders and order lines.

Thanks!

[BLOMASKY]

Ben, understand that most SQL backends use foreign keys and triggers to handle the referential integrity. Aware's way is unique. So the fact that the order table has a column named "customerID" does not give enough info to aware to do anything.

You, the programmer, would, if you select a customer, would have a process or query to FIND Orders WHERE Orders.customerID = Customers.ID

have fun!
Bruce

[BenHayat]

You, the programmer, would, if you select a customer, would have a process or query to FIND Orders WHERE Orders.customerID = Customers.ID

Bruce

So, if I create a composite index made up of CistomerID+OrderID, when I do the above find Find, SQL engine is ONLY going to search for those Orders belong to that customer USING COMPOSITE INDEX and not primary index that only has OrderID element?

I wished Aware, just like any other system, would use standard foreign keys rather than this proprietary method and ignoring foreign indexes where they were made for.

[pureist]

It simply comes down to the 'Golden Rule of the Universe', Ben, being:

If you are getting something you overall want, like or need, then there is always a compromise or sacrifice to make, or price to pay. Always. Nothing for nothing. Ever!

Humans expect/seek out/live in hope of getting something for nothing.

But accept/expect the above Rule will always apply, and life is less frustrating.

Let go of the frustration, Ben. You'll live healthier longer.

You, the programmer, would, if you select a customer, would have a process or query to FIND Orders WHERE Orders.customerID = Customers.ID

Bruce

I wished Aware, just like any other system, would use standard foreign keys rather than this proprietary method and ignoring foreign indexes where they were made for.

[BenHayat]

Humans expect/seek out/live in hope of getting something for nothing.

Who said I was in hope of getting something for nothing???

My point was, when companies follow industry's standards, it makes things much smoother to work with. For example, if you work on a car that has followed "Metric" system, then all your metric tools work. But if you get a car that every nut and bolt is proprietary, it makes it frustrating to work on it. That's why there are standards, to avoid these problems.
That was my point and not hoping to get things for free!!!

[pureist]

But maybe the 'compromise'/price to pay in term of AIM offering everything it does (that other platforms don't) IS this table/index aspect you consider a shortcoming?

Maybe some things AIM offers wouldn't be possible if the table/index handling was as you would like.

[BenHayat]

But maybe the 'compromise'/price to pay in term of AIM offering everything it does (that other platforms don't) IS this table/index aspect you consider a shortcoming?

Maybe some things AIM offers wouldn't be possible if the table/index handling was as you would like.

I'm pretty sure those engineers who designed those successful SQL engines and designed those type of indexes had a lot more cases to accommodate for than Aware did.
For example, there are powerful report writers that import a DB structure in and all the relationships and operations are up and running with zero modification, because they simply followed the standards that SQL engines use and everybody is happy.

You throw an Aware DB structure against an SQL diagram or report writer, and it sees all the tables as flat tables. So, why compromise on something that is simply a standard in industry?

It's like, the native language in your country is English and all the road signs written in English. Now a company comes and make a new sign, but uses it's proprietary alphabets, and you're telling me that everyone in your country should go and learn this new alphabet because this company uses a better paint for the signs???

One thing that I forgot to clarify regarding your "Compromise" statement and that is, Aware uses proprietary relationship among tables and I can accept that compromise as it is part of it's integral system.
But what I have been talking about in this thread, is when Importing "External" databases to access their data, just like a report writer or a query system would. In this case, Aware should basically adhere to the DB standard and work with the DB as it was created and not half way. This is where the compromise hinders our work.
Hope you the difference is clarified.

[aware_support]

Aware IM can automatically recognize foreign keys and create an Aware IM reference attribute that corresponds to this key. You can indicate during the discovery process that the CustomerID is the foreign key that points to the Customer table. Aware IM will then automatically create the Order.Customer reference attribute that will refer to the External business object Customer. You can then use it in rules as you would use reference normally, for example:

Order.Customer.Name = 'Blah'

You don't need to do a FIND:
FIND Customer WHERE Customer.ID = Order.CustomerID

As far as search using composite indexes is concerned you can always create a stored procedure that would use indexes in the most optimal way, rather than relying on the Aware IM automatic queries.

[BenHayat]

Aware IM can automatically recognize foreign keys and create an Aware IM reference attribute that corresponds to this key. You can indicate during the discovery process that the CustomerID is the foreign key that points to the Customer table. Aware IM will then automatically create the Order.Customer reference attribute that will refer to the External business object Customer. You can then use it in rules as you would use reference normally,

Support, is there any documentation on this? I have looked everywhere and there is very minimum docs on this subject. Even others said that object reference is not supported, which is very important for my case, because all the tables in the external DB are setup as relational tables and not flat tables.
I hope you can beef up the docs on this topic for V8.1 or have a full video covering External DB in details than in general.

Thanks and wish you & AwareIM a great 2018!

[aware_support]

Thank you. Happy New Year to you too!

I believe automatic foreign keys should be covered in the video about external databases.