Archive for February, 2007

Even more SQL madness! (It never ends!)

Feb 23rd

Posted by Chad Edge in code

No comments

So I’m still working on all that SQL stuff – now it’s up to figuring out how to get the multiple keyword search going again.

I have a method, not sure how efficient it is (I’m going to run some tests) but so far it’s working.

  1. /****** GETTING A LIST OF IMAGES BACK *****/
  2. SELECT
  3. it.Filename,
  4. it.Record_ID AS Record_ID,
  5. it.Path,
  6. it.File_Description,
  7. "image_server" AS database_name,
  8. "1" AS using_databases,
  9. "1" AS databaseID
  10. FROM
  11. image_server.Item_Table it
  12. JOIN
  13. image_server.Item_Keyword ik
  14. ON
  15. ik.Item_ID = it.Record_ID
  16. JOIN
  17. image_server.Keyword k
  18. ON
  19. k.Record_ID = ik.Keyword_ID
  20. WHERE
  21. k.Keyword = ‘dog’
  22. AND
  23. ik.Item_ID IN (
  24. SELECT
  25. ik.Item_ID
  26. FROM
  27. image_server.Item_Keyword ik
  28. WHERE
  29. ik.Keyword_ID = (SELECT
  30. k.Record_ID
  31. FROM
  32. image_server.Keyword k
  33. WHERE
  34. k.Keyword = ‘cat’) )

Update: I figured I’d use that search method above for now until I find a better method.
That’s not the end of my troubles, however. I’ve got some issue with a JOIN statement that’s not returning the right result set (the right row):

  1. SELECT
  2.         sl.id,
  3.         sl.userID,
  4.         sl.insertdate,
  5.         sl.searchString,
  6.         sl.galleryID,
  7.         sl.imageType,
  8.         sl.imageColor,
  9.         sl.databaseID,
  10.         sl.using_database,
  11.         sg.Name,
  12.         db.dbcommonname
  13. FROM
  14.         CE_search_log2 sl
  15. JOIN
  16.         Saved_Galleries sg
  17. ON
  18.         sl.galleryID = sg.Record_ID
  19. JOIN
  20.         CE_dblist db
  21. ON
  22.         db.id = sl.databaseID
  23. WHERE
  24.         sl.userID = ‘cedge’
  25. ORDER BY
  26.         sl.insertdate DESC
  27. LIMIT 0,1
  28. – returns search ID # 186  
  29.  
  30. SELECT * FROM CE_search_log2 ORDER BY insertdate DESC LIMIT 0,1
  31. – returns search ID # 194

If you notice, the first query returns the CE_search_log.id row of 186. The second query returns the correct row of 194. I think it might be because of my join statement, but I’m not sure. Argh, frustrating!

Update (again): Looks like it was a JOIN problem. I needed a LEFT JOIN for the CE_dblist line. D’oh!

More SQL Madness!

Feb 13th

Posted by Chad Edge in code

No comments

OK, so here’s the new issue:
I’ve got a resultset with a screwy FilePath column (normally, the FilePath column has a full system-path (mac-style) stored in it). The screwy results need to have their path replaced with the correct data, without losing the actual filename that’s stored in the FilePath column.
Here’s what I’m looking at:

PMS_1106:Photos:001P1106PM.JPG

What I need to do is strip out everything but the filename (001P1106PM.JPG) and replace it with the new full system-path data.The filenames are unique, but not consistent in naming (length, letters, underscores, etc). I suppose I could work some regex magic (any a-z or A-Z 0-9 and _, – character(s), followed by a ‘.’ and [JPG|EPS|ext?]).
Hrm. might just have to experiment with this one.

Mathematics for “Beer Goggles”

Feb 9th

Posted by Chad Edge in Personal

No comments

From How Stuff Works:

Many of us have heard of the so-called “beer goggle” effect. It’s the phenomenon that occurs when someone’s had a few alcoholic drinks and suddenly, all of those people who looked semi-attractive on entering the bar look really, really appealing. Scientists have shown that it’s not just a lowering of standards — alcohol actually stimulates the part of the brain that judges facial attractiveness.

The formula:

β =
(An)2 x d(S + 1)

√L x (Vo)2
    where:

  • An is the number of servings of alcohol
  • S is the smokiness of the area on a scale of 0 – 10
  • L is the lighting level of the area, measured in candelas per square meter, in which 150 is normal room lightning
  • Vo is Snellen visual acuity, in which 6/6 is normal and 6/12 is the lower limit at which someone is able to drive
  • d is the distance between the observer and the observed, measured in meters

Source: BBC News

The formula works out a “beer goggle” score ranging from 1 to 100+. When β = 1, the observer is perceiving the same degree of beauty he or she would perceive in a sober state. At 100+, everybody in the room is a perfect 10.

Searching multiple databases

Feb 9th

Posted by Chad Edge in code

No comments

So I’m working on some code that will allow me to search multiple databases in a single query, returning a resultset.

Note: this is MySQL

  1. – Searching the "clipper" database
  2. – for any image that has ‘dog%’ as a keyword
  3. SELECT
  4.         S.Filename,
  5.         S.Record_ID,
  6.         S.Path,
  7.         S.File_Description
  8. FROM
  9.         clipper.Item_Table S,
  10.         clipper.Keyword C1,
  11.         clipper.Item_Keyword O1
  12. WHERE
  13.         S.Record_ID = O1.Item_ID
  14.     AND
  15.         O1.Keyword_ID = C1.Record_ID
  16.     AND
  17.         C1.Keyword LIKE ‘dog%’
  18. GROUP BY
  19.         S.Record_ID
  20. LIMIT   0,30

What I’m having a tough time with is the clipper.columnName part. Each search may have many, many C[n] and O[n] values (these are the keyword items as they relate to the joins). If I have clipper.THIS and clipper.THAT, then how do I add image_server.THIS and image_server.THAT*?

I’m thinkink I might just loop the queries and pool all the results together into one XML resultset.

There should be a more efficient way, however, right?

* “clipper” and “image_server” are two databases where images are stored.

Could this be the answer?

  1. – Searching the "clipper" AND "image_server"
  2. – databases for any image that has ‘dog%’ as a keyword
  3. SELECT
  4.         S1.Filename,
  5.         S1.Record_ID,
  6.         S1.Path,
  7.         S1.File_Description,
  8.         S2.Filename,
  9.         S2.Record_ID,
  10.         S2.Path,
  11.         S2.File_Description
  12. FROM
  13.         clipper.Item_Table S1,
  14.         clipper.Keyword C1,
  15.         clipper.Item_Keyword O1,
  16.         image_server.Item_Table S2,
  17.         image_server.Keyword C2,
  18.         image_server.Item_Keyword O2
  19. WHERE
  20.         S1.Record_ID = O1.Item_ID
  21.   AND
  22.         S2.Record_ID = O2.Item_ID
  23.   AND
  24.         O1.Keyword_ID = C1.Record_ID
  25.   AND
  26.         O2.Keyword_ID = C2.Record_ID
  27.   AND
  28.         C1.Keyword LIKE ‘dog%’
  29.   AND
  30.         C2.Keyword LIKE ‘dog%’
  31. GROUP BY
  32.         S1.Record_ID,
  33.         S2.Record_ID

Solved:
Thanks to Greg Miley, the problem has been solved:

  1. SELECT
  2. cit.Filename,
  3. cit.Record_ID AS Record_ID,
  4. cit.Path,
  5. cit.File_Description,
  6. "clipper" AS database_name
  7. FROM
  8. clipper.Item_Table cit
  9. JOIN
  10. clipper.Item_Keyword cik
  11.     ON cik.Item_ID = cit.Record_ID
  12. JOIN
  13. clipper.Keyword ck
  14.     ON ck.Record_ID = cik.Keyword_ID
  15. WHERE
  16. ck.Keyword LIKE ‘dog%’
  17. OR
  18. ck.Keyword LIKE ‘cat%’
  19.  
  20. UNION
  21. SELECT
  22. isit.Filename,
  23. isit.Record_ID AS Record_ID,
  24. isit.Path,
  25. isit.File_Description,
  26. "image_server" AS database_name
  27. FROM
  28. image_server.Item_Table isit
  29. JOIN
  30. image_server.Item_Keyword isik
  31.     ON isik.Item_ID = isit.Record_ID
  32. JOIN
  33. image_server.Keyword isk
  34.     ON isk.Record_ID = isik.Keyword_ID
  35. WHERE
  36. isk.Keyword LIKE ‘dog%’
  37. OR
  38. isk.Keyword LIKE ‘cat%’
12»