Tool to Automatically port any realational db into Mumps

[rafi farchi]

Hi ,

had some exchange of ideas with Nancy about the possible need and use

of such a tool by the Vista Implementations .

The idea is to take as input the schema metadata of the relational db and

port id to mumps .

In addition , a mechanism to export all data from the relations db and import it

to mumps .

will appreciate if you  comment  on the idea and if you really see any use

of such a tool with some examples .

will be pleased also to here from you how you see the best technical way to

build such tool .

should it be written in mumps ?

or maybe in C with Yotadb in such a way you can easily connect to mumps

as well to the relational db on the same process .

Thanks

Rafi Farchi

[Nancy Anthracite]

This came to mind for me as we find ourselves finding tools we would like to use but are in relational databases, which makes it difficult for the community of VistA users to really use as effectively as we would like. For instance, there is a tool used to administer mental health test in the VA that is used on a tablet and the results are communicated to VistA. There was a presentation about this tool done by Rajashree Patel at the last VistA Community Meeting. We have made a FOIA request for it, but it is written in Java and the tests, etc., are stored in a relational database. It would be helpful to have that in a MUMPS database, and it would be easier to troubleshoot.

 

I had some correspondence with someone overseas who has a database about trees that he would like to move from an SQL database into MUMPS.

 

The foundation model of the Anatomy at the U of Washington would be a good database to convert.

 

The Corporate Data Warehouse for the VA is SQL. It would be nice if we could use it outside the VA. Not their data, but how they extract data and send it, but we would rather have it in a MUMPS database.

 

The MOCHA database for drug information for VistA is an SQL Databases.

 

The blood bank database for VistA is an SQL database.

 

The VA has a FHIR tool on Github that I believe is in an SQL database.

 

Cerner would do well being converted to MUMPS (just joking).

 

There are likely things from the National Library of Medicine that would be nice in MUMPS.

 

--

Nancy Anthracite

[rrichards]

Rafi,  all -

One can store and query relational data in a mumps database using the Octo plugin for YottaDB:

https://yottadb.com/whats-new/introducing-octo/

Octo is a SQL query compatible with PostgreSQL relational database.

Rafael

[Nancy Anthracite]

Rafi is aware, but there are no tools going the other way.

 

--

Nancy Anthracite

> > Nancy Anthracite

[rafi farchi]

Hello ,

wish to comment about the OCTO DB SQL Interface . It is a good solution for ad hoc queries but it can not replace

a full replication into relational data base where you can benefit industry standard  tools working directly on top of the databse

with no complications and with mechanisms for performance optimization and support for analytic queries .

Thanks

Rafi Farchi

[K.S. Bhaskar]

Rafi –

The goal of Octo is indeed to be a regular SQL/JDBC RDBMS. If you look through https://gitlab.com/YottaDB/DBMS/YDBOcto, you will notice that INSERT, UPDATE, and DELETE are implemented, and we are working on some other SQL functionality before giving it a formal release number. Owing to our CI/CD pipeline, what is in the Master branch is production grade code.(https://yottadb.com/yottadb-continuous-integration-continuous-delivery/). Since Octo allows you to specify the globals to which relational data loads, you can export relational data in a SQL export format and load it into Octo.

Note that INSERT, UPDATE and DELETE should not be used for globals that store Fileman files, as Fileman data should go through Fileman for validation.

I have actually been scratching my head wondering what the goals are of your project, but I am friendly to all FOSS projects and you are welcome to use any Octo code in compliance with its AGPL v3 license.

Regards

– Bhaskar

[rafi farchi]

Hi

Thanks for your detailed reply .

Octo db is an excellent technical solution but i still think that full replication into industry stundard rdbms can give vista extra credit points for its transparancy and might give more confidrnce for the decision makers on vista future . You will never catch the continues progress being done in industry standard tools . Sql is becoming reacher and complex contiuesly .

Thanks

Rafi

Thsnks

Rafi

בתאריך יום א׳, 2 בינו׳ 2022, 04:32, מאת K.S. Bhaskar ‏<ksbh...@gmail.com>:

--
--
http://groups.google.com/group/Hardhats
To unsubscribe, send email to Hardhats+u...@googlegroups.com

---
You received this message because you are subscribed to the Google Groups "Hardhats" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hardhats+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/fe9c499a-1df7-42e3-9b5a-60051b4b4965n%40googlegroups.com.

[K.S. Bhaskar]

Rafi –

You absolutely correct that Octo will always be catching up to PostgreSQL, MariaDB, Oracle, SQL Server, Teradata, etc. They have decades-long headstarts. It is helpful to look at the use cases. We are not trying to take over the world (yet).

• For archival, taking a snapshot of a VistA and loading it into a database with extensive querying capabilities, or even a specialized data warehouse database makes complete sense.
• At the other end, real-time reporting and analytics, for example, to create a bed availability dashboard, being able to report on real-time data from the production system is essential. Octo wins here because of real-time replication from a live VistA system.
• In the middle are feeds to billing systems, statistical analysis, etc. where the solutions overlap. Octo has real-time data, whereas the other databases need ETL solutions, HL7 interfaces, etc. The other solutions have more extensive SQL capabilities. You have to look at needs on a case-by-case basis.
  

Hope this helps.

Regards

– Bhaskar

[K.S. Bhaskar]

I should also have mentioned hybrid solutions. Some databases have the ability to map tables in external databases, and in that case Octo can provide access to real-time VistA data mapped as external tables in another database.

Regards

– Bhaskar

[rafi farchi]

Hi

I agree with all you say . I still belive there is place for my approach . It is simple , efficient and can give an excellent snap shot  , not real time but yet goot for reportong and analysis . 

It can be replicated once a day .

You nan choose your target data base . You can apply on the target data base security rules .

I wish i had a real vista implemenation willing to serve as beta site .

I am sure the results will prove simple and efficient solution , 

with high level of end user satisfaction.

Thanks again  .

Rafi

בתאריך יום ב׳, 3 בינו׳ 2022, 00:45, מאת K.S. Bhaskar ‏<ksbh...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/7ecf46c2-d7e5-4604-9a91-541e8a0c55a6n%40googlegroups.com.

[rafi farchi]

Hi

Following idea exchange on the subject may be it might be better to import the sql metadsta into fileman metadat and benrfit from all goodies you have in fileman .

The phisical global structure can be some kind of standrd .

The question is if fileman has an api allowing to import metadata from external source without having to input it thru fileman data dictionary fotms 

Thanks

Rafi

[Sam Habiel]

> The question is if fileman has an api allowing to import metadata from external source without having to input it thru fileman data dictionary fotms

No it doesn't, but the DD format is simple enough to be able to use directly.

See here: https://www.hardhats.org/fileman/pm/gfs_frm.htm

> To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/2f783c5e-da92-49e5-9cca-2f1b14bcde3dn%40googlegroups.com.

[rafi farchi]

Hi

> Got very usefuĺl information from sam .

> Seems that it will be quite easy to import any sql app into fileman .

> Metadata as well as actual data .

> I will need an sql app of your choice . Schema as well as actual data .

>

> I prefet a complex onr in order to cover all aspects .

If someone has a real need for such operation will be pleased to assist and have a good test case .

> Thanks

[David Whitten]

I would like to see the Foundational Model of Anatomy created as a FileMan Database. It is useful to have a program accessible model of a human body if we are going to be able to tie diseases and drugs and medical procedures and lab tests to a grounded model for decision support. 

It is at http://si.washington.edu/projects/fma

To unsubscribe, send email to Hardhats+unsubscribe@googlegroups.com

---
You received this message because you are subscribed to the Google Groups "Hardhats" group.

To unsubscribe from this group and stop receiving emails from it, send an email to hardhats+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/8bba2a58-1b36-497f-82a3-e7342a1f7d2an%40googlegroups.com.

[rafi farchi]

Hi David ,

Seems to be very interesting idea .

As i am some kind of outsider to Vista

and my english is not as fluent as yours

can you pleae make the preliminary checks with

FMA project teem about

How to get the schema and contents and

also check the license and copyright aspects .

There is also the question about the imaging bank .

Is this also to be replicated to a vista server ?

I asume images are stored outside the database in a file system .

Many thanks

Rafi farchi

To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/8bba2a58-1b36-497f-82a3-e7342a1f7d2an%40googlegroups.com.