How to store matrix information in MySQL?

Question

Hi,

I'm working on an application that analizes music similarity. In order to do that I proccess audio data and store the results in txt files. For each audio file I create 2 files, 1 containing and 16 values (each value can be like this:2.7000023942731723) and the other file contains 16 rows, each row containing 16 values like the one previously shown.

I'd like to store the contents of these 2 file in a table of my MySQL database.

My table looks like:

Name varchar(100)
Author varchar (100)

in order to add the content of those 2 file I think I need to use the BLOB data type:

file1 blob
file2 blob

My question is how should I store this info in the data base? I'm working with Java where I have a double array containing the 16 values (for the file1) and a matrix containing the file2 info. Should I process the values as strings and add them to the columns in my database?

Thanks

Answer 1

Do you need to query the data (say for all the values that are bigger than 2.7) or just store it (you always load the whole file from the database)?

Given the information in the comment I would save the files in a BLOB or TEXT like said in other answers. You don't even need a line delimiter since you can do a modulus operation on the list of values to get the row of the matrix.

Answer 2

I think you need to normalize a schema like this if you intend to keep it in a relational database.

Sounds like you have a matrix table that has a one-to-many relationship with its files.

If you insist on one denormalized table, one way to do it would be to store the name of the file, its author, the name of its matrix, and its row and column position in the named matrix that owns it.

Please clarify one thing: Is this a matrix in the linear algebra sense? A mathematical entity?

If yes, and you only use the matrix in its entirety, then maybe you can store it in a single column as a blob. That still forces you to serialize and deserialize to a string or blob every time it goes into and comes out of the database.

Answer 3

Hope I don't get negative repped into oblivion with this crazy answer, but I am trying to think outside the box. My first question is, how are you processing this data after a potential query? If I were doing something similar, I would likely use something like matlab or octave, which have a specific notation for representing matricies. It is basically a bunch of comma and semicolon delimited text with square brackets at the right spots. I would store just a string that my mathematics software or module can parse natively. After all, it doesn't sound like you want to do some kind of query based on a data point.

Answer 4

I think the problem that dedalo is facing is that he's working with arrays (I assume one is jagged, one is multi-demensional) and he wants to serialize these to blob.

But, arrays aren't directly serializable so he's asking how to go about doing this.

The simplest way to go about it would be to loop through the array and build a string as Dave suggested and store the string. This would allow you to view the contents from the value in the database instead of deserializing the data whenever you need to inpsect it, as duffymo points out.

If you'd like to know how to serialize the array into BLOB...(this just seems like overkill)

You are able to serialize one-dimensional arrays and jagged arrays, e.g.:

public class Test {
    public static void main(String[] args) throws Exception {

        // Serialize an int[]
        ObjectOutputStream out = new ObjectOutputStream(new FileOutputStream("test.ser"));
        out.writeObject(new int[] {0, 1, 2, 3, 4, 5, 6, 7, 8, 9});
        out.flush();
        out.close();

        // Deserialize the int[]
        ObjectInputStream in = new ObjectInputStream(new FileInputStream("test.ser"));
        int[] array = (int[]) in.readObject();
        in.close();

        // Print out contents of deserialized int[]
        System.out.println("It is " + (array instanceof Serializable) + " that int[] implements Serializable");
        System.out.print("Deserialized array: " + array[0]);
        for (int i=1; i<array.length; i++) {
            System.out.print(", " + array[i]);
        }
        System.out.println();
    }
}

As for what data type to store it as in MySQL, there are only four blob types to choose from:
The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB

Choose the best one depends on the size of the serialized object. I'd imagine BLOB would be good enough.