Idea: Program to update a MySQL database with data obtained from other programs

[Tat1990]

I have a media database in MySQL. The program used to make it is not open source.
I would like to add information about the files on the database in automatic mode.

Example: Every time a new line is added or modified in a specific table,
get the path of the file (path is on database), run one or more programs like ImageMagik, Media info, a CRC32 command line utility,
and add the that data to predefined fields of another table.

I am not a coder, I don't know if it is possible, but my idea would be a program that we could use batch SQL statements to read and update the info.
The program should have several possibilities to get info from external programs (EX: read a log file, act as a console and read the result)
The program would receive the path of file to process via the batch SQL statement, run external programs, get their output and run update SQL batch.

Now this is me dreaming, it would be possible to change SQL batches behavior with tokens program would replace before launching the the SQL batch.

I am sorry for my poor English and for my poor capacity to explain my idea. Please feel free to ask what on heck I have in mind if this doesn't make a lot of sense to you. 

Kisses for ladies, hugs to gentlemen,

Tatjana

[JoTo]

Welcome Tatjana,

i see you followed my advice on the irc channel to post your wish here.

I hope, someone will grab the task for you. *FingersCrossed*

Greetings
JoTo

PS:
Hope to chat you in the channel again. Bye!

[Renegade]

I'm not really sure what you are trying to do, but there are lots of front ends for MySQL. HeidiSQL is a good one. There are many others. Is that what you are looking for?

If you want, you can try using MS Access as a front end for MySQL. There seem to be many results for it. Found this in the first result:

http://www.mysql.com/why-mysql/white-papers/mysql_wp_access_visual_guide.php

[Tat1990]

No, I am trying to add to a MySQL database data the media collection manager software doesn't, but can read it if placed as "custom fields"

I know how to add data to a MySQL database, what I don't know is how to read that data from external programs and insert it into the database automatically.

I am avoiding naming the software because it is a commercial application. 


Tatjana

[Renegade]

...what I don't know is how to read that data from external programs and insert it into the database automatically.

If the data in the external program is in a proprietary format, then you need to be able to program. It may not be easy.

If the data in the external program is in a MySQL database, then it's simple -- read it from where it is stored, then insert it where you want it.

For automation, I don't know of any easy tools to do that other than MS Access. I only know low-level tools that require programming.

Maybe someone else here knows some consumer-level tools that can help.

[JoTo]

Hi Renegade,

what Tatjana wants is that:

- She uses a MediaManager program that stores the data in a SQL database
- But she wants more information (like a CRC checksum and whatnot) that the media manager won't provide itself additionally in the database
- The MediaManager can handle such additional fields as "custom fields" (read and display them) if they are present in the database
- She wants to use some other external programs to collect the data (e.g. look for the bit depth in the image file, crc checksum, blah blah) and store them in the media manager database to the related record so that this information is available on the next run in the media manager

So she needs a script/app that reads from the SQL database the path to the image file. Then runs several external programs (or offer the functionality itself) to look into the image file for the bit depth and whatnot and calculate a crc checksum. Then the script/app should add these additional information to the actual record in the SQL database as additional custom fields to make these information available in the media manager.

This process should work mostly automatically and/or in the background whenever the media manager stores a new record. The media manager, as Tatjana said to me, has a plugin SDK to hook that script/app into the media manager. If that is not possible, maybe a "run once a day" or "run after media manager session ends" independent script/app will do the trick too? Tatjana, will this be acceptable too?

She is NOT looking for a full blown SQL Manager app where you can create and execute SQL queries and/or manage a database manually.

This is how i understood Tatjanas request. Oh, and may i remind, that we are in the coding snack section? So the people here asking for help are mostly not capable to program the request themselves, or they wouldn't have asked here. She is searching for one, that maybe is willing to help her program that script/app.

I'm not a SQL guru, so i think its a bit over my head. Otherwise i'd be glad to try that challenge.

Greetings
JoTo

PS:
Tatjana, just name the "enemy" There is nothing wrong in naming even commercial products here, as long as you don't advertise when you are related to the application. And even this can be done in the "Announcing my software" section of the forum.

[Stoic Joker]

While I don't have time to tackle this I just thought I'd mention that is sounds like a job for a stored procedure. If the procedure is added to the MySQL db to fire for any insert to table X, then it should be ably to fire an external script that will gather and add the rest of the info needed/wanted.

*Shrug* Just a Thought.

[Tat1990]

Hi Renegade,

what Tatjana wants is that:

- She uses a MediaManager program that stores the data in a SQL database
- But she wants more information (like a CRC checksum and whatnot) that the media manager won't provide itself additionally in the database
- The MediaManager can handle such additional fields as "custom fields" (read and display them) if they are present in the database
- She wants to use some other external programs to collect the data (e.g. look for the bit depth in the image file, crc checksum, blah blah) and store them in the media manager database to the related record so that this information is available on the next run in the media manager

So she needs a script/app that reads from the SQL database the path to the image file. Then runs several external programs (or offer the functionality itself) to look into the image file for the bit depth and whatnot and calculate a crc checksum. Then the script/app should add these additional information to the actual record in the SQL database as additional custom fields to make these information available in the media manager.

This process should work mostly automatically and/or in the background whenever the media manager stores a new record. The media manager, as Tatjana said to me, has a plugin SDK to hook that script/app into the media manager. If that is not possible, maybe a "run once a day" or "run after media manager session ends" independent script/app will do the trick too? Tatjana, will this be acceptable too?

She is NOT looking for a full blown SQL Manager app where you can create and execute SQL queries and/or manage a database manually.

This is how i understood Tatjanas request. Oh, and may i remind, that we are in the coding snack section? So the people here asking for help are mostly not capable to program the request themselves, or they wouldn't have asked here. She is searching for one, that maybe is willing to help her program that script/app.

I'm not a SQL guru, so i think its a bit over my head. Otherwise i'd be glad to try that challenge.

Greetings
JoTo

PS:
Tatjana, just name the "enemy" There is nothing wrong in naming even commercial products here, as long as you don't advertise when you are related to the application. And even this can be done in the "Announcing my software" section of the forum.

Do you have a mind reader?
Thanks for explaining my thoughts so clearly.

The software supports Plugins for reading/writing file formats only. A custom jpeg codec would probably do the trick for jpg files, but some images are in other formats, tga, tiff, psd, etc...

The "Enemy" is ThumbsPlus 7 SP2 from Cerious Software

Thanks a lot for your help!

Tat

[JoTo]

@StoicJoker:
Nice idea...that would render the plugin stuff obsolete and can be solved with SQL only knowledge, keeping the automatic execution in realtime.

Greetings
JoTo

[JoTo]

@Tat1990:
No, but i had some more background from our chat.

*Sigh* I wish i would be better in SQL and have some time.

Greetings
JoTo

[Renegade]

Hi Renegade,

what Tatjana wants is that:

- She uses a MediaManager program that stores the data in a SQL database
- But she wants more information (like a CRC checksum and whatnot) that the media manager won't provide itself additionally in the database
- The MediaManager can handle such additional fields as "custom fields" (read and display them) if they are present in the database
- She wants to use some other external programs to collect the data (e.g. look for the bit depth in the image file, crc checksum, blah blah) and store them in the media manager database to the related record so that this information is available on the next run in the media manager

So she needs a script/app that reads from the SQL database the path to the image file. Then runs several external programs (or offer the functionality itself) to look into the image file for the bit depth and whatnot and calculate a crc checksum. Then the script/app should add these additional information to the actual record in the SQL database as additional custom fields to make these information available in the media manager.

This process should work mostly automatically and/or in the background whenever the media manager stores a new record. The media manager, as Tatjana said to me, has a plugin SDK to hook that script/app into the media manager. If that is not possible, maybe a "run once a day" or "run after media manager session ends" independent script/app will do the trick too? Tatjana, will this be acceptable too?

She is NOT looking for a full blown SQL Manager app where you can create and execute SQL queries and/or manage a database manually.

This is how i understood Tatjanas request. Oh, and may i remind, that we are in the coding snack section? So the people here asking for help are mostly not capable to program the request themselves, or they wouldn't have asked here. She is searching for one, that maybe is willing to help her program that script/app.

I'm not a SQL guru, so i think its a bit over my head. Otherwise i'd be glad to try that challenge.

Greetings
JoTo

PS:
Tatjana, just name the "enemy" There is nothing wrong in naming even commercial products here, as long as you don't advertise when you are related to the application. And even this can be done in the "Announcing my software" section of the forum.

Ahhh... Ok. That's much clearer now.

(And thanks for reminding me about the CS section -- I sometimes click on links in the "Unread Posts" board and miss that.

While I don't have time to tackle this I just thought I'd mention that is sounds like a job for a stored procedure. If the procedure is added to the MySQL db to fire for any insert to table X, then it should be ably to fire an external script that will gather and add the rest of the info needed/wanted.

*Shrug* Just a Thought.

And a brilliant thought at that!


If I have some time, I'll see if I can get to it. I've got a hectic week though, and don't think that I'll be able to look at it until next Tuesday. I'll see what I can squeeze in.

As far as SQL goes, I love it. It's such an elegant language. That might be because it's relatively simple, but whatever, I love doing database stuff~!

[f0dder]

As far as SQL goes, I love it. It's such an elegant language.

Right up until you have to do anything remotely complex, when it turns into a nightmare of proprietary vendor extensions, (even more) messed up syntax and horrible performance (cursors, I'm looking at you!), et cetera.

Stored procedures (combined with update triggers) is an interesting idea, but personally I'm not a big fan of the database process spawning external tools, probably while in the middle of a transaction and everything... is the MySQL database even on the same computer as the media files being indexed?

[Tat1990]

Right up until you have to do anything remotely complex, when it turns into a nightmare of proprietary vendor extensions, (even more) messed up syntax and horrible performance (cursors, I'm looking at you!), et cetera.

Stored procedures (combined with update triggers) is an interesting idea, but personally I'm not a big fan of the database process spawning external tools, probably while in the middle of a transaction and everything... is the MySQL database even on the same computer as the media files being indexed?

Ouch! Sounds hard :/
Yes, media files & MySQL database are on same PC.

I don't know how stored procedures work, but if this was possible it would be preferable, I guess:

- when a new row is added, updated or deleted from database Thumbs --> table thumbnails, copy the relevant fields (path + filename) to another table in another database.
- when a row is deleted from database Thumbs --> table thumbnails, delete the corresponding line on the second database.

This second database, is not touched by ThumbsPlus, so fields can be added as needed.

a program / php script or whatever you find appropriate, would read line by line the table of the second database, and execute the external programs to collect data to insert.

The same program / php script or whatever you find appropriate would populate another table of this second database with the collected data.

Finally, another stored procedure, this one on the second database would copy the fields of the populated table of the second database to the main one, and delete the 2 rows of both tables in the second database.

I guess (and it is a guess!) this method would not compromise database speed/integrity, while a stored procedure starting a instance of an external something every time a line is added/updated on main database, would kill my PC, because ThumbsPlus might add a few thousand lines/minute, but my PC is not fast enough to compute crc32 and other data of big files (ex: DVDs) in real time

Again, sorry for my poor English. I try

Kiss kiss,
Tat