The Datagath Project

Try it here!

Github Page

>>> I'm afraid the name is not much good, however it does get to the point.
The project is a user-friendly infrastructure for collecting/storing/analysing data, through a simple GET/POST request
>>> It is written in Java, using Spring Boot and Hibernate, and is currently around 80% done. It is designed to be as user-friendly as possible while maintaining security and functionality.

General Functionality

>>> There are two main components of the project: Collection Tables and Scheduled Events. The first provides the user the ability to create a custom database table, with any number of columns of various types. The second is a tool that allows the users to schedule events using CRON expressions, and to have those events perform a task from the provided list. The intended use of these tools is analytics and small-scale automation.

>>> The user may then view the created events and tables in their dashboard, where they can view the totality of their instances or download them in .CSV format

>>> From there the user can easily acces more data on the table, or generate code snippets/GET requests for inserting new values into the table.

Code solutions

>>> Now, to the actual code and solutions to various problems. Firstly, I want the tables to be accessible via URL, meaning the tables actually have to have unique identifiers. This is why the table class looks like this: 

@Entity
public class CollectionTable {
    @Id
    private String id;
    private String name;
    private Long ownerId;
    private String url;

    public CollectionTable(String name, Long ownerId, String datatype) {
        this.id = UUID.randomUUID().toString();
        this.name = name;
        this.ownerId = ownerId;
    };

>>> The Hibernate instances of the tables are independent from the MySQL instances of the tables as there is no concrete template for it - a user creates them dynamically. In the actual SQL database these tables are stored by their id value, and while this does create a bit of a mess in the DB, it does also create an additonal layer between the users and the SQL table. If I ever get to Hibernate encryption, it would also create a potential layer of security for the users' data, as the Java class effectively acts as a proxy/metadata for the actual data, and there is no way to relate the data in the SQL tables to a user without the Hibernate-managed entity associated with the table.

>>> The template for the table isn't stored either, as it only takes a simple query to get it, and to minimize the usage of this already small query, I have implemented a method to generate the table URL on initialization: 

public void setUrl(Map columns) {
StringBuilder u = new StringBuilder("http://datagath.ljzcvs.dev/tables/input/" + id + "?");
columns.forEach((name, type) -> {
    switch (type) {
        case "TEXT":
            u.append(name).append("='YOURTEXT'&");
            break;
        case "DATETIME":
            u.append(name).append("=YOURDATETIME&")
            break;
        default:
            u.append(name).append("=YOURNUMBER&")
            break;
    
});
if (u.charAt(u.length() - 1) == '&') {
    u.deleteCharAt(u.length() - 1);
}
this.url = u.toString();
    }

>>> So now, with a simple Controller method, the table can have values added to it whenever the client calls it - methods in different langauges are also provided, as you may see in Fig. 3. I also wanted to have a chart to monitor the activity of the table: you may see that in Fig.2 and Fig.3. To do that, I wrote a simple bucketing SQL query. The method returns an int[], as it operates with the assumption that the client already set it the time interval it wishes to bucket: 

public int[] getActivity(CollectionTable table, String timeframe) {
String sql;
int[] buckets;
switch (timeframe) {
    case "hour":
        sql = """
                SELECT FLOOR(TIMESTAMPDIFF(MINUTE, tmstp, NOW()) / 5) AS bucket, COUNT(*) AS cnt
                FROM `%s`
                WHERE tmstp > NOW() - INTERVAL 1 HOUR
                GROUP BY bucket;
                """.formatted(table.getId());
        buckets = new int[12];
        break;
    case "week":
        sql = """
                SELECT FLOOR(TIMESTAMPDIFF(DAY, tmstp, NOW()) / 1) AS bucket, COUNT(*) AS cnt
                FROM `%s`
                WHERE tmstp > NOW() - INTERVAL 7 DAY
                GROUP BY bucket;
                """.formatted(table.getId());
        buckets = new int[7];
        break;
    case "month":
        sql = """
                SELECT FLOOR(TIMESTAMPDIFF(DAY, tmstp, NOW()) / 1) AS bucket, COUNT(*) AS cnt
                FROM `%s`
                WHERE tmstp > NOW() - INTERVAL 30 DAY
                GROUP BY bucket;
                """.formatted(table.getId());
        buckets = new int[30]
        break;
    case "day":
    default:
        sql = """
                SELECT FLOOR(TIMESTAMPDIFF(HOUR, tmstp, NOW()) / 1) AS bucket, COUNT(*) AS cnt
                FROM `%s`
                WHERE tmstp > NOW() - INTERVAL 1 DAY
                GROUP BY bucket;
                """.formatted(table.getId());
        buckets = new int[24]
        break;
}
Query query = entityManager.createNativeQuery(sql)
@SuppressWarnings("unchecked")
List resultsText = query.getResultList()
for (Object[] row : resultsText) {
    Integer bucket = ((Number) row[0]).intValue();
    Integer count = ((Number) row[1]).intValue();
    int position = buckets.length - 1 - bucket;
    if (position >= 0 && position < buckets.length) {
        buckets[position] = count;
    }
}
return buckets;
        };

>>> Moving on to the events, they are only stored as Hibernate entities: 

public ScheduledEvent(String name, String cronString, User owner, String action) {
    this.name = name;
    this.cronString = cronString;
    this.owner = owner;
    this.action = action;
}
public void generateActionBody(Map actionBodyMap) {
    String action = actionBodyMap.containsKey("action") ? actionBodyMap.get("action") : null;
    if (action == null) {
        return;
    }
    Map> requiredKeys = new HashMap>() {
        {
            put("PING", Arrays.asList("sendAddress", "pingAddress"));
            put("AI/LLM", Arrays.asList("sendAddress", "prompt", "apikey", "model"));
            put("REPORT", Arrays.asList("sendAddress", "dataset"));
            put("VISUALISATION", Arrays.asList("sendAddress", "dataset", "visualisationType"));
        }
    };
    StringBuilder bodyBuilder = new StringBuilder();
    String[] specificKeyRequirements = requiredKeys.containsKey(action)
            ? requiredKeys.get(action).toArray(new String[0])
            : new String[0];
    if (specificKeyRequirements.length > 0) {
        Boolean containsKeys = Arrays.stream(specificKeyRequirements).allMatch(actionBodyMap::containsKey);
        if (containsKeys) {
            actionBodyMap.forEach((key, value) -> {
                bodyBuilder.append("%s:%s".formatted(key, value)).append(";");
            });
        }
    }else{
        throw new IllegalArgumentException("some of the arguments are missing");
    }
    this.actionBody = bodyBuilder.toString();
}

>>> And then, a Java library Jobrunr takes over, using the user-provided CRON time to execute the events on according time. The execution of the events is not yet fully implemented, only the file emailing part is complete: 

public void sendMessageWithAttachment(
String to, String subject, String text, byte[] document)
throws MessagingException, DocumentException {
MimeMessage message = emailSender.createMimeMessage();
MimeMessageHelper helper = new MimeMessageHelper(message, true);
helper.setFrom("[email protected]");
helper.setTo(to);
helper.setSubject(subject);
helper.setText(text, false);
helper.addAttachment("Report.pdf", new ByteArrayResource(document));
emailSender.send(message);
}

>>> All in all, to see the full extent of the experience that working on this project has provided me with, you may visit its' GitHub page. I hope this shows you how versatile my programming experience is.
>>> Thank you for reading,
>>> Ilja Zaicevs.