Welcome Guest.   Make a donation to an author on the site October 31, 2014, 01:15:49 AM  *

Please login or register.
Or did you miss your validation email?


Login with username and password (forgot your password?)
Why not become a lifetime supporting member of the site with a one-time donation of any amount? Your donation entitles you to a ton of additional benefits, including access to exclusive discounts and downloads, the ability to enter monthly free software drawings, and a single non-expiring license key for all of our programs.


You must sign up here before you can post and access some areas of the site. Registration is totally free and confidential.
 
Your Support Funds this Site: View the Supporter Yearbook.
   
   Forum Home   Thread Marks Chat! Downloads Search Login Register  
Pages: [1]   Go Down
  Reply  |  New Topic  |  Print  
Author Topic: Idea: Program to update a MySQL database with data obtained from other programs  (Read 2530 times)
Tat1990
Supporting Member
**
Posts: 5


♀♀

see users location on a map View Profile Give some DonationCredits to this forum member
« 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.  smiley

Kisses for ladies, hugs to gentlemen,

Tatjana


Logged
JoTo
Super Honorary
Charter Member
***
Posts: 235



see users location on a map View Profile Give some DonationCredits to this forum member
« 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
Renegade
Charter Member
***
Posts: 11,667



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« Reply #2 on: September 26, 2011, 02:45:05 AM »

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-...p_access_visual_guide.php

Logged

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
Tat1990
Supporting Member
**
Posts: 5


♀♀

see users location on a map View Profile Give some DonationCredits to this forum member
« 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.  tongue


Tatjana
Logged
Renegade
Charter Member
***
Posts: 11,667



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
JoTo
Super Honorary
Charter Member
***
Posts: 235



see users location on a map View Profile Give some DonationCredits to this forum member
« 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" smiley 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
Honorary Member
**
Posts: 5,344



View Profile WWW Give some DonationCredits to this forum member
« 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. smiley
Logged
Tat1990
Supporting Member
**
Posts: 5


♀♀

see users location on a map View Profile Give some DonationCredits to this forum member
« 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" smiley 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? smiley
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
Super Honorary
Charter Member
***
Posts: 235



see users location on a map View Profile Give some DonationCredits to this forum member
« 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
Super Honorary
Charter Member
***
Posts: 235



see users location on a map View Profile Give some DonationCredits to this forum member
« 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. Sad

Greetings
JoTo
Logged
Renegade
Charter Member
***
Posts: 11,667



Tell me something you don't know...

see users location on a map View Profile WWW Give some DonationCredits to this forum member
« 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" smiley 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. smiley

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. smiley

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~! smiley
Logged

Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker
f0dder
Charter Honorary Member
***
Posts: 8,774



[Well, THAT escalated quickly!]

see users location on a map View Profile WWW Read user's biography. Give some DonationCredits to this forum member
« 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
Supporting Member
**
Posts: 5


♀♀

see users location on a map View Profile Give some DonationCredits to this forum member
« 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 smiley

Kiss kiss,
Tat
Logged
Pages: [1]   Go Up
  Reply  |  New Topic  |  Print  
 
Jump to:  
   Forum Home   Thread Marks Chat! Downloads Search Login Register  

DonationCoder.com | About Us
DonationCoder.com Forum | Powered by SMF
[ Page time: 0.037s | Server load: 0.21 ]