M2SQL

[rafi farchi]

Hello Mumps/Vista Community ,

I have developed a very promising tool (to my opinion) which let you
map your mumps Globals in a relational view and as a result you can replicate
your mumps data to your favorite Relational data base .
The Replication can be either entire (generally daily) or
incremental , transferring only changes assuring an almost online relational
clone of your mumps data .
The purpose id not migration but benefiting from industry standard reporting tools and BI tools which will run on your clone relational DB.
The performance is very good . Replicating 10,000,000 records on mumps globals to sqlite DB (export and import) will take 2 minutes .
In case of incremental approach , updating 10,000 changes vs 10,000,000 lines Db will take 2 seconds (using sqlite replace) .

link :
https://sourceforge.net/projects/m2sql/


The product itself is completed and i still polishing the documentation .
Will be happy to assist any organization who wish to be a beta site .
It shroud be a medium size mumps data base and a real active application .
I will guide them on the mapping process and improve the product as we go .

[rafi farchi]

Hello Mumps/VISTA  Community ,

[Kekoa]

We also did this internally using InterSystems native journal filter routines and tagging the deltas in VistA. One difference is ours only flows in a single direction - from VistA to SQL server.

Let's just say their are folks in the VA who love dumping the database nightly. I hope you can make a positive impact. 👍

Are you also working with the VA team that is/was prototyping AppStream running CPRS?

[rafi farchi]

Hi

M2sql is written in pure ansi standard mumps and can run on any M platform .

The m2sql is general purpose and not oriented just o vista .

It is capable to replicate any gobal . I belive it will be easy to use it  for vista and mayme transfer automatically mappings from va fileman to m2sql 

Refards

Rafi

בתאריך יום ו׳, 22 באוק׳ 2021, 09:59, מאת Kekoa ‏<chris....@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 a topic in the Google Groups "Hardhats" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/hardhats/lSe6NhW610g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to hardhats+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/a0260445-b89e-484a-8d44-21702344b880n%40googlegroups.com.

[rafi farchi]

M2sql is written in pure ansi standard mumps and can run on any M platform .

The m2sql is general purpose and not oriented just o vista .

It is capable to replicate any gobal . I belive it will be easy to use it  for vista and mayme transfer automatically mappings from va fileman to m2sql 

Refards

Rafi

בתאריך יום ו׳, 22 באוק׳ 2021, 09:59, מאת Kekoa ‏<chris....@gmail.com>:

We also did this internally using InterSystems native journal filter routines and tagging the deltas in VistA. One difference is ours only flows in a single direction - from VistA to SQL server.

--

[Nancy Anthracite]

This might allow the creation of the VA's Corporate Data Warehouse but perhaps more accurate and broad based. How could updates to the SQL database from the M database happen without rebuilding the whole instance of the database?

 

--

Nancy Anthracite

> > <https://groups.google.com/d/msgid/hardhats/a0260445-b89e-484a-8d44-21702344b880n%40googlegroups.com?utm_medium=email&utm_source=footer>

> > .

> >

>

>

 

 

[rafi farchi]

Hi

The replication can be either entire or incremental .

In case of incremental replication , it will be required that at those points where data is written to M , the programer will add a call to function passing the key of the relation that have been changed or deleted . Based on this the Sql db will be updated by replace or delete statemrnts generatrd authomaticslly .

Asiming that a well written app

Has only few points impacting the M db this aproach should not be to complecated.

Thanks

Rafi

בתאריך יום ו׳, 22 באוק׳ 2021, 17:10, מאת Nancy Anthracite ‏<nanth...@earthlink.net>:

[OldMster]

Looks like a perfect tool for use with YottaDB/GT.m triggers.

[David Whitten]

I'm sad to say that unless we get a much better handle on our code, your solution is too onerous for the VA.

On Fri, Oct 22, 2021 at 10:45 AM rafi farchi <rafi....@gmail.com> wrote:

Hi

The replication can be either entire or incremental .

In case of incremental replication , it will be required that at those points where data is written to M , the programer will add a call to function passing the key of the relation that have been changed or deleted . Based on this the Sql db will be updated by replace or delete statemrnts generatrd authomaticslly .

Asiming that a well written app

Has only few points impacting the M db this aproach should not be to complecated.

Thanks

Rafi

I'm sad to say that unless we get a much better handle on the VistA code, 

your solution is too onerous for the VA.  There is literally no one who understands

all the code in VistA, much less all the various ways a value can be changed in

a global.

This isn't even recognizing all the places that are automatically generated by FileMan

when compiling templates and cross-references

For example: 

https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html

is code that was created to handle a cross reference in the PHARMACY PATIENT FILE #55

in the Intravenous meds "IV" subfile and the INTERVENTION subfile #55.1153, which is a pointer

to the APSP INTERVENTION FILE #9009032.4,  and which maintains the "B" crossreference

on that multiple. Effectively making sure the data 

in ^PS(55,DA(2),"IV",DA(1),8,"B",$E(X,1,30),DA) is consistent with the 

first piece in ^PS(55,DA(2),"IV",DA(1),8,DA,0)

This is just an example I randomly chose. 

This is the structured form of the M routine ^PSSJXR17

--- Routine Detail   --- with STRUCTURED ROUTINE LISTING --- [H [J [2J [HPSSJXR17   528     printed  Oct 14, 2021@15:56:43                                                                                                                                                                                                     Page 2

PSSJXR17  ; COMPILED XREF FOR FILE #55.1153 ; 09/30/19

 +1       ; 

 +2        SET DA(1)=0

           SET DA=0

A1        ;

 +1        IF $DATA(DIKILL)

               KILL DIKLM

               if DIKM1=2

                   SET DIKLM=1

               if DIKM1'=2&'$GET(DIKPUSH(2))

                   SET DIKPUSH(2)=1

                   SET DA(2)=DA(1)

                   SET DA(1)=DA

                   SET DA=0

               GOTO @DIKM1

A          SET DA(1)=$ORDER(^PS(55,DA(2),"IV",DA(1)))

           IF DA(1)'>0

               SET DA(1)=0

               GOTO END

1         ;

B          SET DA=$ORDER(^PS(55,DA(2),"IV",DA(1),8,DA))

           IF DA'>0

               SET DA=0

               if DIKM1=1

                   QUIT 

               GOTO A

2         ;

 +1        SET DIKZ(0)=$GET(^PS(55,DA(2),"IV",DA(1),8,DA,0))

 +2        SET X=$PIECE($GET(DIKZ(0)),U,1)

 +3        IF X'=""

               KILL ^PS(55,DA(2),"IV",DA(1),8,"B",$EXTRACT(X,1,30),DA)

 +4        if '$DATA(DIKLM)

               GOTO B

           if $DATA(DIKILL)

               QUIT 

END        GOTO ^PSSJXR18

 

And for the Dwarves among us:

PSSJXR17 ; COMPILED XREF FOR FILE #55.1153 ; 09/30/19

 ; 

 S DA(1)=0 S DA=0

A1 ;

 I $D(DIKILL) K DIKLM S:DIKM1=2 DIKLM=1 S:DIKM1'=2&'$G(DIKPUSH(2)) DIKPUSH(2)=1,DA(2)=DA(1),DA(1)=DA,DA=0 G @DIKM1

A S DA(1)=$O(^PS(55,DA(2),"IV",DA(1))) I DA(1)'>0 S DA(1)=0 G END

1 ;

B S DA=$O(^PS(55,DA(2),"IV",DA(1),8,DA)) I DA'>0 S DA=0 Q:DIKM1=1  G A

2 ;

 S DIKZ(0)=$G(^PS(55,DA(2),"IV",DA(1),8,DA,0))

 S X=$P($G(DIKZ(0)),U,1)

 I X'="" K ^PS(55,DA(2),"IV",DA(1),8,"B",$E(X,1,30),DA)

 G:'$D(DIKLM) B Q:$D(DIKILL)

END G ^PSSJXR18

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/CAMT4ZXOPo9GEbCASsYUzb_8DvA2AEC8dN7yMwyr%2BbCd5LeUKzw%40mail.gmail.com.

[rafi farchi]

Hello

In few days i am going to release a version where Vista meta data is built into m2sql .

This  is done by importing the official vista meta data exactly as done in  octo db .

Thanks

Rafu

בתאריך יום א׳, 31 באוק׳ 2021, 01:07, מאת David Whitten ‏<whi...@worldvista.org>:

To view this discussion on the web visit https://groups.google.com/d/msgid/hardhats/CA%2Bi2CTK9XaqdiNKxgrBUhP2NUQ9AB97u%3DO%3D0arxyOs8ynTYhiA%40mail.gmail.com.

[Nancy Anthracite]

Good of you to try taking on the VistA database. It is a big, complex one!

 

--

Nancy Anthracite

> > 1. --- Routine Detail --- with STRUCTURED ROUTINE LISTING --- [H [J [2J [HPSSJXR17 528 printed Oct 14, 2021@15:56:43 Page 2

> >

> >

> > 1. PSSJXR17 ; COMPILED XREF FOR FILE #55.1153 ; 09/30/19

> >

> >

> > 1. +1 ;

> >

> >

> > 1. +2 SET DA(1)=0

> >

> >

> > 1. SET DA=0

> >

> >

> > 1. A1 ;

> >

> >

> > 1. +1 IF $DATA(DIKILL)

> >

> >

> > 1. KILL DIKLM

> >

> >

> > 1. if DIKM1=2

> >

> >

> > 1. SET DIKLM=1

> >

> >

> > 1. if DIKM1'=2&'$GET(DIKPUSH(2))

> >

> >

> > 1. SET DIKPUSH(2)=1

> >

> >

> > 1. SET DA(2)=DA(1)

> >

> >

> > 1. SET DA(1)=DA

> >

> >

> > 1. SET DA=0

> >

> >

> > 1. GOTO @DIKM1

> >

> >

> > 1. A SET DA(1)=$ORDER(^PS(55,DA(2),"IV",DA(1)))

> >

> >

> > 1. IF DA(1)'>0

> >

> >

> > 1. SET DA(1)=0

> >

> >

> > 1. GOTO END <https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html#_END>

> >

> >

> > 1. 1 ;

> >

> >

> > 1. B SET DA=$ORDER(^PS(55,DA(2),"IV",DA(1),8,DA))

> >

> >

> > 1. IF DA'>0

> >

> >

> > 1. SET DA=0

> >

> >

> > 1. if DIKM1=1

> >

> >

> > 1. QUIT

> >

> >

> > 1. GOTO A <https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html#_A>

> >

> >

> > 1. 2 ;

> >

> >

> > 1. +1 SET DIKZ(0)=$GET(^PS(55,DA(2),"IV",DA(1),8,DA,0))

> >

> >

> > 1. +2 SET X=$PIECE($GET(DIKZ(0)),U,1)

> >

> >

> > 1. +3 IF X'=""

> >

> >

> > 1. KILL ^PS(55,DA(2),"IV",DA(1),8,"B",$EXTRACT(X,1,30),DA)

> >

> >

> > 1. +4 if '$DATA(DIKLM)

> >

> >

> > 1. GOTO B <https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html#_B>

> >

> >

> > 1. if $DATA(DIKILL)

> >

> >

> > 1. QUIT

> >

> >

> > 1. END GOTO ^PSSJXR18 <https://vivian.worldvista.org/dox/Routine_PSSJXR18_source.html#_>

> >

> >

> > And for the Dwarves among us:

> >

> >

> > 1. PSSJXR17 ; COMPILED XREF FOR FILE #55.1153 ; 09/30/19

> >

> >

> > 1. ;

> >

> >

> > 1. S DA(1)=0 S DA=0

> >

> >

> > 1. A1 ;

> >

> >

> > 1. I $D(DIKILL) K DIKLM S:DIKM1=2 DIKLM=1 S:DIKM1'=2&'$G(DIKPUSH(2)) DIKPUSH(2)=1,DA(2)=DA(1),DA(1)=DA,DA=0 G @DIKM1

> >

> >

> > 1. A S DA(1)=$O(^PS(55,DA(2),"IV",DA(1))) I DA(1)'>0 S DA(1)=0 G END <https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html#END>

> >

> >

> > 1. 1 ;

> >

> >

> > 1. B S DA=$O(^PS(55,DA(2),"IV",DA(1),8,DA)) I DA'>0 S DA=0 Q:DIKM1=1 G A <https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html#A>

> >

> >

> > 1. 2 ;

> >

> >

> > 1. S DIKZ(0)=$G(^PS(55,DA(2),"IV",DA(1),8,DA,0))

> >

> >

> > 1. S X=$P($G(DIKZ(0)),U,1)

> >

> >

> > 1. I X'="" K ^PS(55,DA(2),"IV",DA(1),8,"B",$E(X,1,30),DA)

> >

> >

> > 1. G:'$D(DIKLM) B <https://vivian.worldvista.org/dox/Routine_PSSJXR17_source.html#B> Q:$D(DIKILL)

> >

> >

> > 1. END G ^PSSJXR18 <https://vivian.worldvista.org/dox/Routine_PSSJXR18_source.html#>

> >> <https://groups.google.com/d/msgid/hardhats/CAMT4ZXOPo9GEbCASsYUzb_8DvA2AEC8dN7yMwyr%2BbCd5LeUKzw%40mail.gmail.com?utm_medium=email&utm_source=footer>

> >> .

> >>

> > --

> > --

> > 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/CA%2Bi2CTK9XaqdiNKxgrBUhP2NUQ9AB97u%3DO%3D0arxyOs8ynTYhiA%40mail.gmail.com

> > <https://groups.google.com/d/msgid/hardhats/CA%2Bi2CTK9XaqdiNKxgrBUhP2NUQ9AB97u%3DO%3D0arxyOs8ynTYhiA%40mail.gmail.com?utm_medium=email&utm_source=footer>

> > .

> >

>

>