keywords missing when added by direct SQL

McMurryIAS
McMurryIAS's picture

Joined: 2009-12-01
Posts: 63
Posted: Tue, 2010-02-09 18:09

Given two .jpg files, one with keywords in the EXIF data, one without. Both properly uploaded to the Gallery. Let's call them with_keywords.jpg and missing_keywords.jpg.

Now, let's say I use SQL to add keywords to the g2_Item.g_keywords field for missing_keywords.jpg.

When I Edit Photo for with_keywords.jpg, I see data in the Keywords textarea. But when I Edit Photo for missing_keywords.jpg, the Keywords textarea is blank. Also, when viewing missing_keywords.jpg's resize page, there is no keyword list below it.

After much experimentation, with .jpg files and other formats (even those without Exif data extracted by Gallery), it seems that the only way to get data to appear in the Keywords textarea, or on the resize page, is if 1) the original file had extractable keywords in it, or 2) the keywords have been added by Edit Photo.

The most confusing part is, is that if I Edit Photo and add some keywords, the *only* field which is changed is g2_Item.g_keywords: The field I SQL'd in the first place!

WHY IS THIS?


Gallery version = 2.3 core 1.3.0
API = Core 7.54, Module 3.9, Theme 2.6, Embed 1.5
PHP version = 5.2.6-2ubuntu4.3 cgi-fcgi
Webserver = lighttpd/1.4.19
Database = mysqli 5.0.67-0ubuntu6, lock.system=flock
Toolkits = Dcraw, Exif, Ffmpeg, Getid3, jpegtran, Thumbnail, ImageMagick
Acceleration = full/86400, full/86400
Operating system = Linux atropos 2.6.27-14-server #1 SMP Tue Jun 30 20:34:56 UTC 2009 x86_64
Default theme = mcmurry
gettext = enabled
Locale = en_US
Browser = Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.20) Gecko/20081217 Firefox/2.0.0.20
Rows in GalleryAccessMap table = 48
Rows in GalleryAccessSubscriberMap table = 63842
Rows in GalleryUser table = 126
Rows in GalleryItem table = 63817
Rows in GalleryAlbumItem table = 590
Rows in GalleryCacheMap table = 2703

-----
http://www.mcmurry.com
 
suprsidr
suprsidr's picture

Joined: 2005-04-17
Posts: 6134
Posted: Tue, 2010-02-09 18:19

Did you clear gallery's database cache after adding the keywords?
And why wouldn't you use gallery's API in the first place?

-s
FlashYourWeb and Your Gallery with The E2 XML Media Player for Gallery2
 
alecmyers

Joined: 2006-08-01
Posts: 4338
Posted: Tue, 2010-02-09 18:25

As supersidr says, all db queries are cached on disc. If you make changes to any db table outside the API you need to flush the db cache to see G2 pick up the new values.
 
nivekiam
nivekiam's picture

Joined: 2002-12-10
Posts: 16501
Posted: Tue, 2010-02-09 18:27

This is a snippet from the Bulk Upload module written by Bharat:
http://codex.gallery2.org/Gallery2:Modules:bulkupload

I did not copy everything out, just the setting of KeyWords and as you can see from the note there is no API for doing that, but you can see how Bharat set them:
ItemAddBulk.inc:

                        /* Set the keywords by hand, since we don't offer that $
                        if (!empty($line['keywords'])) {
                            list ($ret, $itemLockId) =
                                GalleryCoreApi::acquireWriteLock($newItem->getId());
                            if ($ret) {
                                return array($ret, null, null);
                            }

                            list ($ret, $newItem) = $newItem->refresh();
                            if ($ret) {
                                return array($ret, null, null);
                            }

                            $newItem->setKeywords($line['keywords']);
                            $ret = $newItem->save();
                            if ($ret) {
                                return array($ret, null, null);
                            }

                            $ret = GalleryCoreApi::releaseLocks($itemLockId);
                            if ($ret) {
                                return array($ret, null, null);
                            }
                        }
                        $newItemId = $newItem->getId();
                    } else {

____________________________________________
Like Gallery? Like the support? Donate now!!! See G2 live here