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!

eagles9999 wrote
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

BLOMASKY wrote
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

BenHayat wrote
BLOMASKY wrote
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

BLOMASKY wrote
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. :mrgreen: :mrgreen:

BenHayat wrote
BLOMASKY wrote
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

pureist wrote
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

pureist wrote
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??? :mrgreen:

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_support wrote
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.

BenHayat

aware_support wrote
Aware IM will then automatically create the Order.Customer reference attribute that will refer to the External business object Customer.

Q1: Where is this reference attribute created? Will Aware create it's own new table to maintain this relationship/reference or will it "Modify" the structure of the order table (by adding a new column) to store Customer ID in it? I doubt .Net Entity Framework allows another application change it's table schema. Because if Aware changes order table, then it will not match with Order class in the .Net side.

Q2: As of now, that I'm designing the external DB in .Net I "can" also include the Customer ID in Order table if that will help Aware. But .Net Entity Framework (ORM system) WILL automatically create foreign keys and composite keys.
Any guidelines that would help Aware to get a better access to the external DB, would be very helpful.

BenHayat

Support, could you please answer the above two questions, as the answer is very important to us. Since there is no docs on this subject, please provide details on how & where Aware creates the reference relationship coming from another DB? Does Aware create it's own table in that DB or does it try to modify structure of existing tables by adding more columns to it?

If Aware "Adds" column to existing table, i.e. Order Table to contain CustomerID attribute, then who is going to fill in the CustomerID in that column, after the discovery for new records being added to the table? Will Aware do it or is it the responsibility of the external application?
This whole discovery and adding reference needs a clear description so we know how to deal with it in the external app.

Thank you in advance!

aware_support

Aware IM NEVER modifies the structure of the existing external database table. The reference attribute is created for Aware IM purposes only. This reference attribute works like any other reference attribute in Aware IM as far as rules, queries, forms etc are concerned. However, as far as the implementation in the database is concerned, these references work differently - they rely on the foreign key defined in the external database. Aware IM will not maintain any separate tables either.

So all you have to do in the external database is define a foreign key to another table (it can be a composite one) and rely on Aware IM to automatically "discover" such an attribute.

BenHayat

aware_support wrote
So all you have to do in the external database is define a foreign key to another table (it can be a composite one) and rely on Aware IM to automatically "discover" such an attribute.

Sounds great and fair enough.
Thank you!