Driving automation from MyInfo data -- is reading the SQLite file directly okay? [Yes]

Discuss MyInfo and get help here
Post Reply
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Driving automation from MyInfo data -- is reading the SQLite file directly okay? [Yes]

Post by InjAnnuity_1 »

As noted elsewhere in this Forum, MyInfo stores its data in a SQLite database, and its articles in an HTML-like format. I find this attractive, due to the potential for writing multi-purpose documentation: it's for human readers and programs alike.

For example, if I'm designing and documenting a database or API schema, clearly both kinds of information are present. I could, of course, duplicate the machine-significant parts outside of my MyInfo file. Then I have extra work: two copies to update, and to keep in sync with each other. I would prefer, instead, to maintain a complete, coherent set of definitions in my MyInfo file.

How would I use the machine-significant data? To generate API code, or sanity-checking code. Easy enough, for a C++ and Python programmer, like myself, with a handy SQLite library.

However, MyInfo is not open-source, and its data format may be considered proprietary. Therefore, trying to read my information, directly, from the SQLite file, may be frowned upon. Is there an official opinion on this?
Last edited by InjAnnuity_1 on Wed Mar 08, 2023 9:56 pm, edited 1 time in total.
Petko
MyInfo Support
Posts: 3230
Joined: Sun Jul 25, 2004 4:33 pm
Contact:

Post by Petko »

You can read MyInfo 7 files, because they use a combination of free file formats (SQLite & HTML). The file format is not documented, but it is pretty stable and it should work that way.

However, writing in MyInfo files from a script is not a good idea, because for some operations MyInfo does extra consistency checks, there might be some implied rules that the file format should follow, which are not expressed in the database and it should create consistent undo revisions.

So, I don't see problem reading MyInfo files, just do not modify them outside MyInfo.
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

Thank you! That opens the door to many more productive uses for MyInfo!

One of my old tech support gripes is having to tell users, "Yes. Too bad the program doesn't read its own documentation!"

Well, with this approach, it can! To good effect.
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

It appears that reading some of the SQLite data, directly, will require a collating function, tied to collating sequence name "MI_CI". SQLite will use this for comparisons (including joins and indexes) on any of the affected fields.

I'll venture a guess that this stands for "MyInfo Case-Insensitive", but I could be very wrong.
Petko
MyInfo Support
Posts: 3230
Joined: Sun Jul 25, 2004 4:33 pm
Contact:

Post by Petko »

Yes, you are right about that. You may need a SQLite extension for reading the files. It is embedded in MyInfoCore.dll, but you will need it as a separate DLL. I will upload both 32- and 64-bit versions and make it available for download.
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

Wow! Given how busy you must already be with MyInfo, I can't thank you enough. This opens doors for MyInfo that have never been open before: at work.

As you might guess, I'm an old programmer, but a very new MyInfo user. I'll study it well, and find good ways to marry both disciplines.
Psychologist Slim
Posts: 1
Joined: Thu Feb 23, 2023 8:51 pm

Post by Psychologist Slim »

InjAnnuity_1 wrote: Mon Feb 27, 2023 3:22 pm Wow! Given how busy you must already be with MyInfo, I can't thank you enough. This opens doors for MyInfo that have never been open before: at work.

As you might guess, I'm an old programmer, but a very new best fat burner for men MyInfo user. I'll study it well, and find good ways to marry both disciplines.
Yes you are right MyInfo is making things more easy then they were before.
Myinfo has many advantages for businesses persons as well.
Last edited by Psychologist Slim on Fri May 19, 2023 10:50 am, edited 1 time in total.
Petko
MyInfo Support
Posts: 3230
Joined: Sun Jul 25, 2004 4:33 pm
Contact:

Post by Petko »

InjAnnuity_1 wrote: Mon Feb 27, 2023 3:22 pm Wow! Given how busy you must already be with MyInfo, I can't thank you enough. This opens doors for MyInfo that have never been open before: at work.

As you might guess, I'm an old programmer, but a very new MyInfo user. I'll study it well, and find good ways to marry both disciplines.
You can download both x32 and x64 bit extensions here:
https://www.milenix.com/download/develo ... 1.10.0.zip

MyInfo uses the 32-bit version, but depending on your SQLite version or external database tool, you might need the 64-bit one.
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

Success! :D I installed the 64-bit DLL in

%LocalAppData%\SalSoft\SQLiteStudio\extensions

(had to create this folder) and told SQLite Studio to load it for ALL databases. Now SQLite Studio lets me browse my mixed-use data, in MyInfo format!

This will let me take care of the R=Read in "CRUD". (Create/Read/Update/Delete methods, using current jargon, for the less-technical reader.)

Mixed-use, in my case, means "to be used by both people and programs." I really dislike entering the same information in multiple programs! Discrepancies will happen, and almost always cause problems when they do. Best to prevent them when feasible.

This shows in your database design, too. I'm no stranger to these designs, so I can follow the Foreign Key references to figure out which data to read.

C=Create will likely need to be done via MyInfo, via OPML Import. (I should also look at "Send to MyInfo" Windows messages.) This is mainly for importing, documenting, and extending existing data schemas. Not a huge amount of data (so far), but tedious to enter and easy to screw up.

D=Delete and U=Update will still need to be manual, via MyInfo, for now. Eventually, perhaps, I can use PyAutoGui for larger-scale updates.

Thank you, Petko! This is going to save a lot of manual effort in the long run!
Telesto
Posts: 3365
Joined: Fri Dec 15, 2017 5:32 pm

Post by Telesto »

Is it possible to give some context to the requests. How do you want to deploy MyInfo? What's the advantage of having MyInfo as front-end? The storage part could in theory directly to SQLite. So why using using MyInfo as vessel?

Why?
A) It might introduces new use cases for MyInfo (with new audiences)
B) It might be useful to know what you actually need. You might get better support. Say an API/ or commandline interface which also allows you to Update and Delete entry's.
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

I'm documenting a database structure, so I can analyze it, and evolve it to serve new users and new purposes.
Why use MyInfo as a vessel?
I have two answers for this: a high-level answer, and a feature-based answer.

High-level answer
While it is possible to use multiple tools to store and manipulate this metadata, none of them can do a complete job. I would end up with multiple copies of this metadata, and I would have to spend much time and effort to keep them all in sync with each other. Using MyInfo to accumulate and document my schema, there will be a "single source of truth" for all aspects of the structure. A single document to edit.

Feature-based answer (which makes the high-level answer possible)
First, my data are very strongly structured. One of my JSON columns contains a description of a contract. It is nested 10-20 elements deep. This is what makes an outliner extremely valuable for documenting this kind of structure.

Second, MyInfo lets me add my own tags and data to each node. In this way, I can formally describe each datum, directly, as part of the exact same outline. (This is what avoids the duplication.)

This also will let me "tag" the data, at any level of detail, for analytical purposes. I can specify who should have access to this datum, and what types of access should be allowed.

Third, MyInfo can import outlines in various formats. This is essential, because I have a crude, partial schema already, in a .yaml format. I am writing a short program to translate it into something MyInfo can read. That will give me my initial framework for the data, where I can manually add information that is not part of any schema. This will be faster and much more reliable than manual entry.

I will be writing another program to decompose JSON structures into similar outlines. This will let me add volumes of more detail to my MyInfo outline.

In this way, I plan to use MyInfo to accumulate a description of the full structure.

I can then elaborate on that structure, by hand.
What's the advantage of having MyInfo as front-end?
Two things, mostly.

First, only some of the information can be loaded from existing data sources. The rest comes from the minds of developers, like me. I'll by typing in a lot of documentation by hand, creating it on the spot, as needed, where needed, i.e., inside the metadata nodes I had imported earlier. MyInfo makes a great "front-end" for that kind of effort, as it makes those nodes directly available for editing.

Second, with MyInfo, I get to shape my tool to fit my actual situation.

There are several different facets to any such structure: meaning and documentation; validation and integrity rules; access rules; translation to various programming languages (e.g., Python) and tools (REST APIs). For this, MyInfo has Perspectives. The ability to create and implement new Perspectives, as needed, makes this a very powerful tool. One cannot foresee every Perspective one might need in advance.

There are tools, from well-meaning people, who don't realize that. For example, in the MS-DOS days, Data Junction started out as a small tool for small businesses. Specifically, for individual programmers who needed to find and extract data from old, obscure, convoluted formats to new, simpler, more standard formats.

Somehow, over the decades, it eventually became a tool suitable only for people who manage and operate titanic data warehouses. The current owners assume, incorrectly, that if you need to convert data, then you must be in that sort of situation, and so you have the staff and the budget to afford a canned, one-shape-fits-all "enterprise level" solution.

For this task, I intend to use MyInfo to
  • Accumulate structural information.
  • Document that information.
  • Formalize that information enough to drive code-generators and standard documentation-generators.
  • Help me organize my work in making all of the above happen.
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

Addendum
It might be useful to know what you actually need. You might get better support. Say an API/ or commandline interface which also allows you to Update and Delete entry's.
I've only scratched the surface on what Import and Export can do. I haven't even tried MyInfo's Import/Export SDK.

However, as I work on this project, some of those needs will come to light. With your encouragement, I will post them here, in their own topics.

It's easy to imagine some of the possibilities, though.

Imagine a program-launching Note with a custom Form. Filling in the Form tells the Note which data the task needs to use, and/or how it should be used. Then, each link in the Note leads to a batch file, Python program, or other program, which receives the Form data, as command-line parameters (or a temporary text file containing those parameters) and performs the necessary work.

I can think of no faster, easier way to automate!
  • Writing even a simple GUI, to collect parameters, is truly awkward in .bat, .py, .lua, etc. A MyInfo Form bypasses all that.
  • Documentation is right there, under the Form.
  • Optional: Provide a full set of Node Attributes as a text file.
  • Optional: Provide supplemental ("context") attributes from Parent nodes.
The closest thing I've found, so far, is NoteCase Pro, which has its own built-in Lua interpreter. But
  • It has no Forms. (It does have a neat data syntax, but don't expect newbies to write it reliably.)
  • Launched programs start in NoteCase's directory, not their own.
  • Lua is much harder to read and write than Python. (Yes, I've tried.)
  • Lua doesn't reach the rest of the computing universe, the way Python does.
A direct Python API (to Create/Read/Update/Delete data) could take MyInfo a lot further. But that would surely be more work. You can get a lot of leverage out of the "program launcher" facility above.

Currently, there seems to be no way to pass any parameters out of MyInfo, via MyInfo's links.
weissenb
Posts: 1
Joined: Fri May 26, 2023 1:55 pm

Post by weissenb »

Thank you so much for that Information!
I put the dll her in %LocalAppData%\SalSoft\SQLiteStudio\extensions

But my questions is: where is the data file for the SQLite program?

If I choose in SQLiteStudio the command "open database" a file should be slected (e.g. a db file).
There myInfo is storing that file?

Best Regards, Christian
InjAnnuity_1
Posts: 32
Joined: Wed Feb 01, 2023 6:43 pm

Post by InjAnnuity_1 »

Where myInfo is storing that file?
In the MyInfo user interface, hover your mouse over the drop-down widget listing your Notebooks. MyInfo will show (as a pop-up hint) the full path to the selected Notebook file. That file is the Notebook's SQLite database, and it can be opened in SQLite Studio. (I've done it.)
Post Reply