This project has moved. For the latest updates, please go here.

Database Schema Can anyone Provide?

Feb 20, 2012 at 7:17 AM
Edited Feb 20, 2012 at 3:47 PM

Can anyone point me at the documentation or source code that defines the database columns, types and length, etc. As I need to create the MySQL table to hold the information once I import it.

I’m looking into the possibility of developing a module for Drupal that will allow me to display the data output from the movie card, but with the ability to sort and filter by any of the values.

I notice there was some discussion last year about writing the MC data directly to mySQL but nothing came of this. My design is much simpler in that I just have a routine that imports the XML or CSV output from MC to populate or update the database held in Drupal. This will only require a single table to be created.

If anyone has other ideas on the best way to do this I would be happy to receive your suggestions.

Dave

Coordinator
Feb 21, 2012 at 12:17 AM
Edited Feb 22, 2012 at 7:25 PM

Hi Dave,

This is something I've been wanting to do for some time; in fact, it is why I first got involved in MC development - to improve the HTML output to provide the info required. MC is now able to output XML, altho' I'm not sure if I have included the template yet.

Whatever you need, let me know, and I'll do whatever I can to help out; it will be great to see this up and running!

I have never fully delved into the world of Drupal modules, just dabbled a little bit. I have some experience in PHP but have not quite got my head around how it hooks into Drupal! Otherwise, I would be more than happy to collaborate, or just help out!

Cheers,

Huey

Feb 21, 2012 at 6:29 PM

Thanks for offering to help :)

My first challenge is to get the get the "Output List to HTML" to work when I modify the parameter template to use the following format:

imdbid,title,movieyear,thumb,image,releasedate,format,width,height,createdate,rating,runtime,genre,actors,outline,plot
<<imdb_id>>,<<title>>,<<movieyear>>,<<createimage:175>>,<<createimage:350>>,<<releasedate>>,<<nfo:file:video:container>>,<<nfo:file:video:width>>,<<nfo:file:video:height>>,<<nfo:createdate>>,<<rating>>,<<runtime>>,<<moviegenre>>,<<stars>>,<<outline>>,<<fullplot>>

For some reason the output routine does nothing, not even reporting an error when I use the text above in the template!

I assume it is looking for some HTML marker in the file?

All I need for now is to output the data into a CSV file format in UTF-8 and all the strings enclosed in quotes ,'example',

Are you able to help?

I've started to used some of the development tools to create the tables, I can then export the definition to my own install module.

Regards Dave

Coordinator
Feb 22, 2012 at 12:33 AM
The template you need looks like this:

<title>CSV Output</title>
<<MC HTML Page>>
"imdbid","title","movieyear","thumb","image","releasedate","format","width","height","createdate","rating","runtime","genre","actors","outline","plot"
<<media_item>>
"<<imdb_id>>","<<title>>","<<movieyear>>","<<createimage:175>>","<<createimage:350>>","<<releasedate>>","<<nfo:file:video:container>>","<<nfo:file:video:width>>","<<nfo:file:video:height>>","<<nfo:createdate>>","<<rating>>","<<runtime>>","<<moviegenre>>","<<stars>>","<<outline>>","<<fullplot>>"
<</media_item>>
<</MC HTML Page>>

Copy into a .txt file and add it to the html_templates folder, then start MC to pick it up.

Two other things; createdate won't work yet, but will be available in the next release. The new release will also allow to save as a .csv file - currently, you will need to manually change the file extension. If you want an updated version of MC, PM me and I will send you a link.

Feb 22, 2012 at 4:37 PM

Thanks for this.

I created my own CSV file manually and then found out that the CSV importer in Drupal doesn't manage items with 'quotes' around them!

I may therefore have to either modify the CSV routine or go back to using XML.

How much work do you think it would be to develop the code to write directly to MySQL as this would save having to go through the intermediate stage of outputting the text file and then importing it?

Drupal tables need a unique key which ideally should be the IMDB ref, but if my library is anything to go by I have multiple entries for the film/extras, etc.

We would have to have some way of allowing the user to indicate if there are multiple IMDB references which one should be used to ensure the stay unique.

I notice that when the images are exported the file names contain a number of numeric characters, how does the MC decide what to name the files and are they always unique.

The reason I ask is that we would want to minimise the requirement to upload the JPG's if they have to go to an ISP.  I have a Synology NAS and can simply save them to a shared directory.

I thought it would be a good idea if I produced an outline design specification for the Drupal module as this will allow you to understand what I'm doing and what help you could provide on the MC coding.

If you have any ideas about the topics above please let me know.

Regards Dave

Coordinator
Feb 22, 2012 at 7:34 PM
Hi Dave,
Whew, that's a lot to get thru!

I created my own CSV file manually and then found out that the CSV importer in Drupal doesn't manage items with 'quotes' around them!
Is that with both single and double quotes? I put double quotes in 'cos I tested the .csv using Excel, and it didn't work properly with single quotes but was just right with the double. BTW, the double quotes are escaped in the output but single quotes are not, so are very likely to cause problems.

I may therefore have to either modify the CSV routine or go back to using XML.
I personally would prefer to see an XML version - a bit more robust, perhaps?

How much work do you think it would be to develop the code to write directly to MySQL as this would save having to go through the intermediate stage of outputting the text file and then importing it?
Yes it would, but I wouldn't be keen to attempt this straight away, nor would I have the time I don't think. My thoughts were to have the info available in the XML file, but have Drupal populate the DB tables in an appropriate way. But I think it would be awesome to have MC update Drupal automatically in the future!

Drupal tables need a unique key which ideally should be the IMDB ref, but if my library is anything to go by I have multiple entries for the film/extras, etc.
Yes, we have previously found the the IMDb ID is not suitable for uniqueness. I think an auto-generated key would be fine, tho'

I notice that when the images are exported the file names contain a number of numeric characters, how does the MC decide what to name the files and are they always unique.
The generated image name consists of the file write time UTC, the file CRC, and the image height as multiple sizes are allowable. This pretty much guarantees uniqueness.

The reason I ask is that we would want to minimise the requirement to upload the JPG's if they have to go to an ISP. I have a Synology NAS and can simply save them to a shared directory.
My requirement is that I want the images on the web server but not in the DB. I think it would need to be a field in Drupal to indicate where the images are stored to make this more flexible.

I thought it would be a good idea if I produced an outline design specification for the Drupal module as this will allow you to understand what I'm doing and what help you could provide on the MC coding.
That would be awesome!

BTW, I'm currently working on the format tag that you asked for a while ago; maybe get that done this week.