suggestion about database

General discussions about Automagic and automation in general

Moderator: Martin

Post Reply
ziotempa
Posts: 55
Joined: 30 Nov 2018 08:54

suggestion about database

Post by ziotempa » 20 Dec 2018 09:07

Hi, in case I want to store organized informations on a file to use with Automagic, considering that I have no root, would you suggest the use of:
- CSV
- JSON
- XML
- SQL Lite
- Other

...don't know if sql lite can be used without root
I'd like to start programming something with the need of saving data for future use, but I don't know what would be the most efficient file format to retrieve and modify data from Automagic.
What do you think/suggest?

User avatar
Martin
Posts: 4468
Joined: 09 Nov 2012 14:23

Re: suggestion about database

Post by Martin » 20 Dec 2018 11:25

Hi,

In my opinion this depends on the structure of the data. If you want to store many equal records and want to process/view the data in an external spreadsheet app, you could go with CSV but if you only have few records but structured data, you could go with JSON.
Both formats are somewhat supported in action Script of Automagic. XML is not well supported and I've never tried to use SQL Lite from Automagic.

Regards,
Martin

User avatar
Desmanto
Posts: 2709
Joined: 21 Jul 2017 17:50

Re: suggestion about database

Post by Desmanto » 20 Dec 2018 14:21

I have typed my post several hours ago, but Martin has posted first. It seems my answer still has the similar point of view with Martin.

Global Variable
Global Variable (Glovar) is the best choice if you want to save some state or small data
- Integrated into automagic
- You can choose many types of data, including map/list, time, location
- It can be accessed directly in almost every element in Automagic.
- Logging data directly to glovar is much faster and requires the least action/element execution per run (compared to other method)
- You can combine glovar usage with JSON or CSV, to save the glovar space

The main downside, which I realized later after using glovar intensively : The more glovar data you have, the slower the script/expression/control UI run at flow startup. It is because every flow will be triggered with the glovar as the context. When I have logged too much data and never delete them, I experienced a long delay before the notif on screen appear. And a lot flows seems to be delayed or late to execute. I thought it is bug in Automagic 1.36.0. Have tried to troubleshoot it first before reporting (In the end, I never report it). I tried restart, reinstalling, disable all flows, granting/revoke permission and many others. Until I started to clean up my glovar, deleting those thousands of unused list elements; then suddenly my script run very fast again.

The variables.bin (Automagic store glovar in this file) at my backup is up to 100 KB when I have the slowdown. After cleaned up, it is less than 20 KB and script run fast again as usual. I still have a lot of glovar, but most of the massive logging glovar data has been emptied and dumped to text file.

So use Glovar wisely, don't dump all your data there. Tens to hundreds elements still fine. But if you have thousands of data, better store it somewhere else.

JSON
JSON is the best choice for now, if you have a lot of data and can't dump all in Glovar.
- Automagic has the function to convert to and from JSON.
- Converted JSON also play nice with map/list object in Automagic, make it easy for conversion, add/remove element, looping and many other object operation. You don't have to learn new scripting language except Automagic's
- You can use multiple nested mixed map/list as you want. (multi dimension data)
- You can easily save your data in glovar map/list first. Then convert it to JSON. Or vice versa, import the json file to glovar.
- If your database is very small, only some string, you can temporary save it to glovar first. Then after daily/weekly/monthly, dump the glovar into JSON file and save it locally as text file. Delete the glovar so it won't pile up. (see above glovar downside)
- You save the JSON as usual txt file, using Write to File, and open it using any text editor. I recommend MiXplorer text editor, as it is built-in in the MiXplorer File manager. You can reimport back using Init Variable Text File.
- JSON is widely used as data transfer in most website, so make it easier in the future if you want to integrate to other services.

CSV
CSV is preferred if you don't plan to convert it back, but want to export it to spreadsheet program.
- to convert to CSV, you can use join and combine with listformat.
- converting back from CSV can sometimes give you problem, especially for data with multiple lines break or quotes.
- CSV is easy to import to excel spreadsheet for later analysis.
- CSV is best for row and column logic, but very difficult for multiple nested map/list. Hence your data is preferred to be 2 dimension only if you choose to use CSV.

XML
XML is actually more robust, but not preferred for Automagic
- Automagic preference, flow and widget are stored using XML.
- Flow/widget sharing also use XML as the export format.
- Automagic currently (1.36.0) only has function to extract from XML, using XPath.
- There is no function to convert back to XML. There is way you can construct your own XML from the map/list, but too painful to do
- Looping, add/remove element and conversion of XML is much more difficult in Automagic, not so close to map/list object
- Parsing XML using XPath function in Automagic is much slower than using regex. When dealing more than thousands of lines, the difference start to get significant. At my test, regex can finish approx 10-50 times faster

SQLite
SQLite is not recommended, although a lot of android app use it.
- SQlite has no built-in binary in most android ROM, you have to find a working binary for your phone (the one from Titanium Backup is usually working fine). It also require root to make it executeable
- MiXplorer has SQLite editor to edit it. But no way to access the automation part yet (probably there will be in the future, but don't hold your breath waiting for it)
- SQLite require you to learn again about SQLite command and syntax
- Converting and accessing SQLite db require you to go through the execute root command. The command method is separated from Automagic scripting, so you can't make the scripting in a single logic. Interacting with the data require you to go back-forth between script and execute root command.

ZIP
ZIP is just complementary method, to compress the existing database you choose, especially when you have massive data.
- Used with other database type, only to compress the data
- required to compress and decompress when using it
- Useful for massive data, to save spaces, especially with a lot of similar data, or just to stored for a long period without being accessed (backup).


My Own Usage
For me, I use glovar as the first step, then use JSON or CSV for alternate usage.
- Saving state to glovar such as on/off, or screenshot mode (full, half, clean, partial), timedate, hyperlink, clipboard and some small data. Some logging data also stored temporary here.
- As soon as the data started to pile up, I will export it to JSON, if I want to access it back; or CSV if it is going to be analyzed later in excel.
- I use some XML especially for flow/widget related operation. I can export a flow/widget, init it, change some data and reimport back the flow xml.
- I never use SQLite yet to store data. Only use it to query data from other app (usually require root).
- ZIP is used for the automagic flow/widget/glovar backup.
Index of Automagic useful thread List of my other useful posts (and others')
Xiaomi Redmi Note 5 (whyred), AOSP Extended v6.7 build 20200310 Official, Android Pie 9.0, Rooted.

ziotempa
Posts: 55
Joined: 30 Nov 2018 08:54

Re: suggestion about database

Post by ziotempa » 20 Dec 2018 15:25

thanks to both of you
I'll go with json, it's a good way to learn more about it since I'm not so used at this format. I enjoy creating flows, so this can be a good idea to start learning more about json format and maps in Automagic ;-)

ziotempa
Posts: 55
Joined: 30 Nov 2018 08:54

Re: suggestion about database

Post by ziotempa » 05 Mar 2019 09:02

Yeah, it's a couple of months since I've been using json and it's perfect for my needs

johan
Posts: 3
Joined: 09 Apr 2019 18:06

Re: suggestion about database

Post by johan » 09 Apr 2019 23:57

ziotempa wrote:...don't know if sql lite can be used without root
SQLlite is the standard database of Android an can be used without root. I have written an app "Sehtest" (which is not available in Playstore ore anywhere else, it's just on my mobile, because I have no rights for the pictures, that this app is using) and I have had implemented the highscore table with a SQlite database. Unfortunately, I don't know, how to change/add the records manually (i.e. I'm not able to forge the highscore table).

Does anyone know a SQLlite editor, which can access the database of an app?

User avatar
Desmanto
Posts: 2709
Joined: 21 Jul 2017 17:50

Re: suggestion about database

Post by Desmanto » 10 Apr 2019 06:36

@johan : Use MiXplorer from xda. It is free, can edit SQLite db, including using the custom SQL query. This for manual edit, not automation.

If you want the binary version, you need to download from github or other place. The easiest one is to download Titanium Backup, explore the apk and go the asset folder to take the appropriate sqlite binary for you device (usually it is sqlite.armeabi). You can use this binary in action execute root command to edit any SQLite db in your device. You can look example on how to delete sms using SQLite : viewtopic.php?f=5&t=7145
Index of Automagic useful thread List of my other useful posts (and others')
Xiaomi Redmi Note 5 (whyred), AOSP Extended v6.7 build 20200310 Official, Android Pie 9.0, Rooted.

Post Reply