dict, coll sqlite and what about nosql...
Hi all,
I need some advice about dicts and eventually about a "real" database.
Basically I want to categorize and sort cues or samples or any other media material. It looks like I need to build a sort of database...
Yes, I have some basic idea how I would do it with colls and I know coll very well out of almost 20 years of experience. But now there is also dict.
Out of the existing documentation, I can't see a significant difference between a coll and a dict. The underlying technology is different probably, but as a user of those structures this is less important than my existing experience of something I know well.
Now first question:
What can I do with dict what I can't do with coll, or at least what would be much easier to do with dict than with a coll.
Next question:
For my specific need would it make more sense to learn dict, or to learn to build a database with sqlite or to stick with colls...
And another question:
Is there anybody who dived into NoSQL databases in the context of Max? Often the structure of the data changes along the development of a project, and for a relational database you need to decide a lot in advance. The stories I hear about NoSQL databases sound promising and simpler, though most of the problems they address are not at all relevant for me...
I just want some opinions about these questions, and of course I would like to see a better documentation about this new dict object in general...
I am curious...
Stefan
Hi Stefan,
maybe i can contribute a bit when it comes to databases. From my opinion a database is a good option either when the amount of data is huge or if you need fast and flexible filtering and grouping of data. I.e if you need to execute data queries like "give me all clips that have either two cue-points or are not longer than 33 seconds and order them by creation date in an descending order"
This would be definitely an indication to use a database (as you do not have to iterate over all entries multiple times but a single query will give you the result in no time (compared to dict or coll)).
Other strength of databases are the so called unique keys with which you can prevent double entries, default settings for field content that are not specified and for sure the "JOIN" statements.
With JOIN (As far as I know SQLite supports the JOIN, LEFT JOIN, RIGHT JOIN and INNER JOIN statements) you can combine fields from all tables all over your database. These statements allows you to distribute your data in multiple tables and group them by all kind of criteria you need.
A common practice in Database design is to have small link-tables that functions as a joint between others. I.e you could store all clip names in one table and give each row a unique identifier. In another table you store all - lets say - production countries of the clips you have, also each country with an id. now you can have a third table that simply consists out of two columns with integer-ids:
first the clip-id second a country id. This is your link-table that links the two others in a flexible way. A clip can have now no, one or multiple production countries it dosn't matter - and won't change your database structure as each production country of a clip is a separate row. if you want to select all clips that are produces in England (id: 1) or Botswana (id: 2) and you want them sorted by name ascending you can do so by a single statement that would look somehow like this:
SELECT table1.clipname FROM table1 LEFT JOIN table2 ON table1.clipid = table2.clipid WHERE table2.countryid = 1 OR table2.countryid = 2 ORDER BY table1.clipname ASC
With a design like this you don't need to do too many assumption about your data in advance and the database stays flexible for future extension.
In short - I'd use a database when it is not only about storing data but about managing them in a flexible way.
Hope that helps a bit.
Jan
Totally agree with Jan. If you ever want to search (and your database isn't extremely small), use SQL; it's a time-tested, well-designed technology. A knowledge of SQL is a great skill to have in your toolbox. There are websites out there, and IIRC also a pretty decent O'Reilly book. There are some concepts you need to understand like normalizing ("never store the same data in multiple places" and "a field only contains one piece of data") and joins (like Jan was mentioning) that take a bit of work, but once you get them, they're extraordinarily powerful.
Major difference between dict and coll: Dict is hierarchical, so you can have dicts within a dict. Coll is just a flat file.
My two cents re: NoSQL: avoid it. The nominal speed payoff is not worth it, and slowness in SQL code is often a result of poorly written queries. (There's an optimizer that can help you with those) It's an investment with SQL, but it's absolutely worth it.
Hi.
I agree on everything that has been said. I'd add that the management of SQL within Max is more complex than just talking to a coll or dict object, but this might not bother you.
On the other hand, another major difference between coll and dict is that dict is way faster when random access (for retrieval, insertion, deletion, etc.) is required on large sets of data. So I'd say:
- small and simple data collection, no need for advanced operations: use coll
- large-ish and/or hierarchical data collection, need for random access but no (or limited) need for queries: use dict
- very large amount of data, or you need serious queries: go for SQL!
just my two cents...
aa
Thanks for all the answers... It looks I should go for SQLite. Is there more than that short introduction, which tries to do everything in js?
(http://tinyurl.com/7nzmnja)
I prefer much the Max way of programming, but don't mind some lines of code in js either, if they help to do the main work in Max...
Stefan
Good Mornig Stefan,
I like the w3schools tutorial om SQL: http://www.w3schools.com/sql/default.asp
They are not on the exact SQLite implementation of SQL but 99.9% should be valid for SQLite.
For specific SQLIte information of the projects home http://www.sqlite.org/
Jan
When I was working on a couple of projects that required database type access (Pre MAx 6 & .dict family) I found a (MacOS) app that came in useful as a way of initially checking what was going on in SQLite dbs: http://sqlitebrowser.sourceforge.net/ also there is a relatively basic tutorial on using it here: http://www.makeuseof.com/tag/learn-sql-simple-database-sqlite/
Thanks to all contributers, I came along quite well and now have a video editor to prepare and tag the contents of a video sampler. I love SQLite now, it seems a very powerful tool for this kind of purpose...
Now I want to add to my database a table which should hold what is probably called a blob data type. That blob data should be the content of a coll.
Now the question, has anyone passed the content of a coll into a field of a SQLite table?
In Max it seems to be a nightmare, as all the white space reinterpretation kills my information somehow...
There might also be a way to pass the content of a dict to the database (its probably not a big deal to turn my colls into dicts...;-)
Any advice is much appreciated...
Stefan
you need the "pull_from_coll" message to [dict]. although probably best to start with [dict] in the first place.
But how to get it into SQLite?
You probably don't want blobs (Binary Large OBjects), since you'll have to encode everything to binary, but you might want clobs (Character Large OBjects) which are much more text friendly, and should be supported by SQLite.
+1
the dict tutorial is still missing!
how do you plan to get the information on your clips and audio files and Max patches and stuff.... into your database tables?