Add export db to mysql

[shri120kant]

Is there a way to show the extracted data into a particular database for example sql server or mysql...........

[aardappel]

You'd have to write code yourself to perform such a transformation. The binary format is documented (https://github.com/aardappel/procrastitracker/blob/master/PT/file_format.txt) and there's also a HTML export that you could work from.

[shri120kant]

In which application can I open the exported database view

[aardappel]

In.. a web browser?

[krthush]

Hi aardappel, thank so much for such an amazing piece of software and its even more awesome you made it open source.

I'm working on integrating this into a website so that people can see their time usage online (I want to then use other applications to get mobile/wearable activity data) thus giving a better and more full understanding on time spent during a day.

I'm quite new to C++ programming and trying to hack my way away at it. Like shri120kant mentioned, I too am trying to create a method that would allow for data to be exported to an mysql database.

I assume I would have to place such a method in the nodedb.h file? Since the data is hierarchical, I assume I will need to write something involving ids/parent_ids so that it can be placed into a mysql format? Am I thinking along the right lines?

Another way to solve this (if its easier), is if I could send JSON objects to my webserver? I can then do the transformation on my php Laravel server, and I'd be a lot more comfortable parsing that into an SQL database there.

Thanks again

[aardappel]

Yes, likely the easiest path for you would be to clone the exporthtml function (and all code it calls / being called from), and then modify it to generate JSON or SQL statements instead. Probably JSON is easier since it can also be hierarchical.

[krthush]

Awesome. I just managed to create an "Export to JSON object" function and it seems to be working well (albeit its not automatically saving). An example of the object can be found here: https://codebeautify.org/jsonviewer/cbdaff73

Now my next step is to transfer this over to my webserver. Any ideas on how I do this in C++? I can't really seem to find much stuff online, and it seems overly complicated. Not to mention most of the stuff I find is transferring char types, and the json object I have is pretty large.

Surely I'm missing something?

[aardappel]

Looks good, though I would export numbers as numbers, not strings.

There's no standard way to send anything to a webserver in C++, you'd have to link in a library that knows how to do a http request. There are many. Or since this is Windows, use https://docs.microsoft.com/en-us/windows/desktop/winhttp/using-the-winhttp-c-c---api directly. Can't help you with the details of that :)

[krthush]

Awesome! Thanks- yeah I need to remember to change them into numbers, will defo do that.

I managed to get the http post working all good now (can see my test dumps here: http://ptsv2.com/t/r11yd-1551813592). I used a combination of nlohmann json & libcurl to get the data and then send it.

I have one query tho, so the data packet that is being sent right now (i.e. the same data that is from html export) is not very precise. By that I mean even though you get very good clarity in terms of time spent on activities, you don't actually get much data in terms of when you started/finished activities?

I noted that on stats view you can get a crude way of seeing this by adjusting the date to specific days, then you get to view the "start time". Even so this is still not very accurate in terms of what if a user started/stopped multiple times during a day? And then how would I go about implementing this into an export function.

I imagine this a bit of hell to store in an understandable data format, but I want to be able to have a detailed overview of the times when user's start and end tasks as well. Do you think you have any clues on where I should start?

Thanks again so much for the help so far and can't iterate how cool a program you have :)

[aardappel]

that is generally how PT collects data.. it accumulates time spent per day, with only a first starting time, to keep things compact. It doesn't have a full list of start/end times. That could at some point be added, but would require a change to how it internally stores data.

Here is where it decides to add time to an existing day, or create a new one if it doesn't exist: https://github.com/aardappel/procrastitracker/blob/7d57768de4ab734b5548a7278ed3d572cd8a0bc4/src/node.h#L109-L118 Simplest probably would be to force it to create a new "day" when time being added to the existing day is not "contiguous". You now need to update all code dealing with days such that it takes into account that there are possibly multiple "days" with the same date, and that it gets accumulated properly etc. That, or you stick a vector of time ranges inside day, but that is probably even more intrusive, and less efficient.

[krthush]

I see, makes sense, thanks for being so detailed with the response. Will definitely look into implementing what you mentioned about having the multiple days.

For now I will stick with this setup and make sure the web-server is working all good and test out the product. Then if I can get a bit more time & more comfortable with C++ and it works well, I'll implement the start/end times.

[krthush]

Apologies aardappel for emailing you, will return to posting on here then.

So I tried following your advice with forcing the creation of a new day and I've been struggling quite a bit. I've also realised that for my specific purpose (which is to send packets of json http requests to a webserver - like sending time logs), it would not be sensible to send the entire db file every few seconds.

I did more digging and realised that my best starting point is probably the following function: void addtodatabase(char *elements, SYSTEMTIME &st, DWORD idletime, DWORD awaysecs) {...} So I started by calling a http post request and the end of the function. This worked to some degree (so it was sending the current activity data correctly), but because it was sending a request almost every second (due to time activity being logged every second) it started to freeze up my system a bit, especially my browsers - I assume it was a bit intensive on my internet?

Anyways, I'm now thinking of basically creating a local .json file, which I add records to (i.e. whenever addtodatabase is called, it also adds entries into the .json file), then sending this file up every 10mins or so and afterwards clearing it and starting again.

Is this efficient or am I overlooking something? (again sorry for being such a newb) Is bad to be saving to .json directly? Should I be using something like zlib instead?

[aardappel]

np, just want to keep the conversation public.

Yes, you're dealing with a lot of data, so you need to be efficient. http and json are not efficient :)

Rather than trying to record everything in your own structures, simply send the database file as-is (it is already a very efficient compressed binary format) infrequently (once a day would be good, but once and hr or every 10 mins should still work), then clear the entire db.

[krthush]

Thanks for the response, yep totally agree on sending the data in batches every few min.

I've managed to edit the void addtodatabase(char *elements, SYSTEMTIME &st, DWORD idletime, DWORD awaysecs) {...} to create small JSON packets of data in arrays and then stored it all in a string.

Then I edited the VOID CALLBACK timerfunc(HWND hwnd, UINT uMsg, UINT_PTR idEvent, DWORD dwTime) {...} to send the string with a HTTP POST request every minute -> piggy backing off the pt autosaving.

Agreed that this isn't the most efficient, but it seems to be functioning well enough for my purposes (around a 1kb JSON string is sent every 1min), I'll be sending it to AWS and it should be able to manage that well enough.

Now there is one minor bug I'm facing, so I rely on the idletime counter for making my data packets, but it seems to max out at 180 seconds? Why does this happen? Would be awesome if this counter would tick up without maxing out.

[aardappel]

See max idle time is set to 180 in the "Advanced Settings"

[krthush]

Ah thanks! thats perf, will change that ^^

[TheMayhem6328]

Awesome. I just managed to create an "Export to JSON object" function and it seems to be working well (albeit its not automatically saving). An example of the object can be found here: https://codebeautify.org/jsonviewer/cbdaff73

Now my next step is to transfer this over to my webserver. Any ideas on how I do this in C++? I can't really seem to find much stuff online, and it seems overly complicated. Not to mention most of the stuff I find is transferring char types, and the json object I have is pretty large.

Surely I'm missing something?

How do I export my own JSON?