Dynamic SQLite

    Mar 09 2012 | 11:01 pm
    Hi to all.
    I am using the SQLite patch (https://cycling74.com/tutorials/data-collection-building-databases-using-sqlite/), but I have noticed that the database is not dynamic at all. If you change a file inside the folder that is loaded, the database does not respond to any metadata sent.
    I was expecting the code to understand a specific filename and try to locate its metadata. However, in this example it seems that it reads the order of the file, which leads to problems when you add a new file.
    Does anyone have any idea of how this can be fixed?

    • Mar 09 2012 | 11:15 pm
      what do you mean by "change a file"?
      you do understand that changes to data (deletion, insertion, updates, truncation and schema changes) should only take place via the db engine.
      you should never mess around manually with files in the db, this will screw up indices storage etc, and leave you with at best an inconsistent db, at worse a corrupt db and loss of data.
    • Mar 09 2012 | 11:28 pm
      After loading in the database a folder that includes a number of files, the database responds correctly. However, if you add or delete any file that exists inside this folder, all the metadata get screwed up. It is annoying to change the database every time you want to change the file list, don't you think?
      So, I am trying to find a method that will allow us to change the file list of the folder and retain automatically the metadata for each file.
      If someone has any good idea for this let me know.
    • Mar 09 2012 | 11:52 pm
      If your'e not happy delegating responsibility to the db engine, maybe you should just use flat files.
      It's not annoying, and does not need fixing.
      Remember also this is not just used by max, sqlite is an external product, if you really want changes in its behaviour, this is the wrong forum.
    • Mar 10 2012 | 4:57 pm
      I want to clarify -again- that the db engine works fine, however, inside the context of my work I find it a little bit difficult to say that the engine supports my needs (thus, I need to fix this accordingly). Let's assume for a second that there is a collaborative performance and many performers read and write constantly data to a specific shared space. It would be impossible to keep track of all this information.
      One solution to keep everything in sync would be to insert each entry (every file added), and try to re-order the database (I'm trying to do it alphabetically). So it is important to add something like this in the javascript code:
      exec("SELECT * FROM dumpster ORDER BY name");
      I tried to implement it, but it doesn't work. Any suggestions?
    • Mar 10 2012 | 9:07 pm
      Ok, to get the results in an ordered fashion (important if you want the jit.cellblock to communicate with umenu), change the get_all function in javascript with:
      function get_all() { exec("SELECT * FROM dumpster ORDER BY name"); }
      This will order the rows on startup.
      p.s. SQLite is case sensitive. It first shows the uppercase names, however, if you change everything to lowercase it will work alright.