I’m designing out an asset library tool, the first stage of which will be a searchable database of texture files; Though I intend to expand to assets of various types.
I’ve made a version of this tool before, but on a smaller scale. Now I’m looking at larger number of texture files(250k+) and it seems like my original implementation using XML documents to store the vital file information and metadata won’t hold up.
I want to write the tool with C# and I’m thinking I should use something like SQLite. It looks like of some/all of the functionality I need is floating around in LINQ and ADO.net as well. However, I don’t have any experience creating and working with databases. Anyone have tips or resources they’d like to share?
We have a installation of xampp set up here for the TA team for learning purposes.
Xampp offers a mySQL installation right out of the box with a web interface where you can easily manage the database and try out some SQL yourself. For the sql syntax you can look here: SQL Tutorial - Learn SQL
You also want to read up on some basic database design. How to avoid redundant information, how primary keys and indices work and how to plan your tables before you start coding. I haven’t found a good tutorial on this yet. It’s usually something I teach the guys here myself. I don’t think you need to concern yourself with triggers, stored procedures, events and stuff like this though.
I use SQLite in Python and C++ and I really like using it (not a C# person, so I can’t help with that). The performance is pretty good and it offers all the query and filtering options of a proper database. On top you’re not just limited to having your tables on the disk, you can also have a database in memory, which is not only faster but also a nice alternative to arrays and other data structures.
I wrote a texture/shader browser with a thumbnail cache for the textures/shader icons where I make use sqlite to store metadata and the thumbnail itself. It’s pretty fast and even runs inside Maya (since I wrote it in Python/Qt).
If you are looking to use C# I would recommend using an ORM like NHibernate or SubSonic. Very easy to get data persisted into a DB.
http://nhforge.org/Default.aspx
http://www.subsonicproject.com/
[QUOTE=RobertKist;10674]
I wrote a texture/shader browser with a thumbnail cache for the textures/shader icons where I make use sqlite to store metadata and the thumbnail itself. It’s pretty fast and even runs inside Maya (since I wrote it in Python/Qt).[/QUOTE]
I love that it runs inside of Maya as well. That alone is almost enough to make me want to switch to Python/Qt. It seems like you’d be able to easily drop assets into Maya from that browser, where it might be a lot more difficult from a stand-alone C# app; I suppose I might be able to make it work with socket communication, or maybe use a separate Python tool to handle the Maya stuff outside of the C# app, but at that point it seems like it’d be worth just doing it all inside of Python/Qt anyway.
Do you find yourself limited much by the implementation of the tool through Python/Qt?
Thanks for the resources and tips. I’ll be digging into those shortly!
In most cases writing a Qt app isn’t too different from writing a stand alone python app. Python is pretty powerful in Maya and you can easily extend its functionality with 3rd party modules - e.g. PIL for advance image handling, mySQL for db access (Qt’s own drivers do noth work in maya though), etc. If we need something that isn’t supported we can either code it in C++ as maya plugin or as python module and then access it via Python. Overall I’m pretty happy and it turns out to be a solid platform for writing even more complicated apps which go beyond your standard “little helper script”.
There’s only 3 bigger issues we have so far…
There’s no x64 P4 module available for older Maya/Python versions - so we can only access P4 from our scripts via command line in maya 2008/2009 x64.
In older Maya versions (2010<) Maya is not the owner of the Qt windows, so they don’t minimize, maximize with the app, etc. but you’d have this problem in C# regardless.
Having different Maya/Qt versions on one System can lead to .dll chaos. PyQt expects to find the dlls in the path. So you either need to set the path before you launch maya or put the dlls directly into the Maya folder. The whole dll thing is a bit of a hassle when it comes to deployment and troubleshooting.
If your studio doesn’t work on multiple projects where you use different/older Maya versions then all this shouldn’t be a problem though. It mostly applies to 2010<
I’m designing out an asset library tool, the first stage of which will be a searchable database of texture files; Though I intend to expand to assets of various types.
I’ve made a version of this tool before, but on a smaller scale. Now I’m looking at larger number of texture files(250k+) and it seems like my original implementation using XML documents to store the vital file information and metadata won’t hold up.
Heh, we’re doing the exact opposite (going from database to XML metadata docs). A couple of reasons for this:
- Our projects are getting smaller with regard to number of asset files, not larger, so the XML metadata docs dont seem like too much overhead to maintain.
- Someone’s last attempt at a database driven system integrated with maya was poorly designed and ended up being hated by artists and tech artists alike.
A summary of problems we ended up having:
-
The database was “live” so attribute values were dynamically updated/saved from/to the database. Changing an attribute in maya meant this attribute would get updated in other people’s maya even if they didnt have the most recent maya file from the repo. This led to a fair bit of confusion as it did not perform the way other maya attributes did (i.e. saved into the .ma scene)
-
With this live approach, debugging was difficult on artists assets, as changing metadata was permanent, metadata getting automatically updated into the database
-
We had no system for artists to easily see database history (e.g. see who changed metadata and why)
-
We stored texture metadata using the texture path, which meant we couldn’t have 2 separate file nodes with 2 different sets of metadata
-
To maintain our database, our system had many hooks into maya’s mel files (such as AEfileTemplate.mel) which ended up being very difficult to remove.
-
Integration with temporary local textures on artist hard drives left a lot to be desired.
-
Our error reporting system for “invalid” textures and the like happened on opening a maya scene. This error window was so common, artists got used to just closing it instantly.
These problems obviously weren’t considered from the outset, and by the time we realised how problematic the system was, we were deep into production. This isnt a warning to not use databases, its a warning to design your system to avoid the same traps we fell into!
our library here doesn’t have very deep integration to Maya… I can see that this could be quite chaotic and error prone, just as Damon described.
Users can submit materials to our db, and others can import it from the db. The db keeps track of the users who created the material. There’s no version control built into the system now other than autoback files of the materials on the fileserver when someone does a change.
For us, the main goal was that people can share materials in an easy manner (other than exporting the .ma file to a network share). They can preview materials before they import them. Materials can be categorized and renamed, moved, etc.
The can see which textures a material uses, where they link to, etc.
I think writing a networked tool that truly allows for collaborative work in Maya (i.e. I updated something and you see it right away) is quite a big undertaking (both when it comes to coding and when it comes to db design). I definitely wouldn’t recommend this to anyone who’s new to dbs and who doesn’t have lots and lots of time to production test it.
Also for writing any multiuser db, add some metadata to the db itself (or make a table for it) to store the DB’s version number. Sometimes you have to upgrade your script or the db during production. In this case you don’t want that old versions of the scripts modify the db any more and users have to upgrade. Adding a simple version number table to your db can save you from all this trouble as it prevents older clients from writing wrong data.
There’s some more discussion about asset management in this thread. I don’t know if source control is in the mix for you, but I’m still a big fan of our Perforce attributes backed by a local database for fast searching (described in that thread). Built-in history/revisioning, no side-by-side files or XML parsing, etc.
I’m also a huge fan of ORM for database work. If you get the right system it can greatly simplify your life when developing and maintaining database-backed tools. SQLAlchemy for Python is a great example.
I wrote an outsourcing managment tool a few years back in python inside of maya with database connectivity. On retrospect I think it would have been better to have a “client” version of the tool in Maya and a larger one outside. Still, DB access from within Maya was really easy using the odbc module in the python for windows extensions: http://python.net/crew/mhammond/win32/
Unfortunately this extension isn’t included in the Mayapy version (but can be deployed with your tool scripts) so maybe now that QT is built in that might be a better way to go. I can’t comment on the QT interface but I found the odbc to be relatively effective. My only wish was that there was a way to get your query returned as a dictionary instead of a list.