|
Tat1990
|
 |
« on: September 26, 2011, 02:30:04 AM » |
|
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 |
|
|
|
|
Logged
|
|
|
|
|
JoTo
|
|
« Reply #1 on: September 26, 2011, 02:41:35 AM » |
|
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!
|
|
|
|
|
Logged
|
|
|
|
|
|
|
|
Tat1990
|
|
« Reply #3 on: September 26, 2011, 04:05:38 AM » |
|
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 |
|
|
|
|
Logged
|
|
|
|
|
Renegade
|
|
« Reply #4 on: September 26, 2011, 06:09:04 AM » |
|
...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. |
|
|
|
|
Logged
|
|
|
|
|
JoTo
|
|
« Reply #5 on: September 26, 2011, 06:29:08 AM » |
|
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. |
|
|
|
« Last Edit: September 26, 2011, 06:38:04 AM by JoTo »
|
Logged
|
|
|
|
|
Stoic Joker
|
|
« Reply #6 on: September 26, 2011, 07:08:20 AM » |
|
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. |
|
|
|
|
Logged
|
|
|
|
|
Tat1990
|
|
« Reply #7 on: September 26, 2011, 07:15:42 AM » |
|
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 |
|
|
|
|
Logged
|
|
|
|
|
JoTo
|
|
« Reply #8 on: September 26, 2011, 07:28:56 AM » |
|
@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
|
|
|
|
|
Logged
|
|
|
|
|
JoTo
|
|
« Reply #9 on: September 26, 2011, 07:31:31 AM » |
|
@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 |
|
|
|
|
Logged
|
|
|
|
|
Renegade
|
|
« Reply #10 on: September 26, 2011, 08:00:45 AM » |
|
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~! |
|
|
|
|
Logged
|
|
|
|
|
f0dder
|
|
« Reply #11 on: September 26, 2011, 11:34:06 AM » |
|
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? |
|
|
|
|
Logged
|
 - carpe noctem |
|
|
|
Tat1990
|
|
« Reply #12 on: September 28, 2011, 01:43:50 PM » |
|
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 |
|
|
|
|
Logged
|
|
|
|
|
