Archive for Geek

LINQ to SQL multi-keyword search

Thursday, April 15th, 2010

how-to search mutiple keywords using LINQ to SQL

this bit of LINQ code will let you search for records using any number of keywords

it’s a bit limited, but it illustrates the starting point for something more.

the key is to get results for the first keyword into a disconnected object (in this case, a simple List<String>) and then search for the remaining keywords in there. clearly you could up the ante, and select more fields and spin up a collection of custom objects (i.e. List<MyClass>) but this suited my needs for an autocomplete for country names.

// GetMatchesIntersect(string csv, int maxMatches)
// mainly developed for auto-complete scenarios
//
// searches single [n]varchar|text|char field for multiple keywords
//
// returns array of field values containing ALL keywords
//
// NOTE: i decided on array because i am implementing this a webservice
//       to be consumed by other languages/platforms)
public string[] GetMatchesIntersect(string csv, int maxMatches)
{

    // keywords come in as comma-separated values
    string[] keywords = csv.Split(new char[] { ' ', ',' });

    // this list will hold the matches
    List<string> matches;

    using (DbDataContext db = new DbDataContext())
    {

     // build list of matches for first keyword into generic List<string>
     matches = (from s in db.TableToSearch
                where s.FieldToSearch.Contains(keywords[0])
                select s.FieldToSearch).ToList<string>();

     // if any were found
     if (null != matches && matches.Count > 0)
     {
         // start searching at the 2nd keyword (if any)
         for (int i = 1; i < keywords.Length && matches.Count > 0; i++)
         {
          // using .ToUpper() because matching in a List<string> is case-sensitive
          // set matches equal to the query results for each succesive keyword.
          matches = (from match in matches
                  where match.ToUpper().Contains(keywords[i].ToUpper())
                  select match).ToList<string>();
         }
     }
    }
    // ship it out as array
    return matches.Take(maxMatches).ToArray<string>();
}

How to get only changed values using SQL

Monday, September 29th, 2008

So i have a log table that records server status, and what I needed was a query that returns to me only those rows that had status change. That is to say, over time, just show me the times when the status changed.

this query gets me just exactly that — only the rows that represent a change in status from the most recent previous status.

/* main select clause */
SELECT
    A.LocationId, A.ConnectionStatus, A.Comments,
        A.TimeRecorded AS StatusChangeTime

FROM
    /* self-join the table to have a second date field */
    tblNetworkConnectionStatusLog AS A JOIN
    tblNetworkConnectionStatusLog AS B
        ON A.LocationId = B.LocationId
WHERE
    /* only interested in rows of different status */
    B.ConnectionStatus <> A.ConnectionStatus
        /* limited to just the most recent different status */
        AND B.TimeRecorded =
        (
            SELECT
                MAX(C.TimeRecorded) AS StatusChangeTime
            FROM
                dbo.tblNetworkConnectionStatusLog  AS C
            WHERE
                C.LocationId = A.LocationId AND
                C.TimeRecorded < A.TimeRecorded
        )
   
/* this just gets the first status recorded*/
UNION SELECT
    D.LocationId, D.ConnectionStatus, D.Comments,
        D.TimeRecorded AS StatusChangeTime
FROM
    tblNetworkConnectionStatusLog AS D LEFT OUTER JOIN
        tblNetworkConnectionStatusLog AS E
            ON E.LocationId = D.LocationID
                AND E.TimeRecorded < D.TimeRecorded
WHERE
    E.LocationID IS NULL

ORDER BY
    StatusChangeTime DESC

purdy sweet.

damn that sounds familiar

Wednesday, June 20th, 2007

haven’t we all heard this claim before?

MIT neuroscientists explain deja vu

i am certain that i have heard claims to scientifically explain this eerie sensation that is ironically so banal, but apparently “forming memories of places and contexts engages a part of the brain called the hippocampus.” duh, but these guys “have been exploring how each of the three hippocampal subregions — the dentate gyrus, CA1 and CA3 — contribute to learning and memory”

yeah, yeah, yeah

but honestly, i was disappointed by this guy’s weak-ass example:

“[He] described his own occasional experience of finding the airport in a new city uncannily familiar. That occurs, he said, because of the similarity of modules — gates, chairs, and ticket counters — that comprise airports worldwide. It is only by seeking unique cues that the specific airport can be identified”

of course all airports look similar — there are entire international agencies whose raison d’etre is to ensure just that, so we cattle don’t get lost and so planes don’t need to figure a dozen sets of signage

duh — that ain’t deja vu

i recently experienced a chain of events (noticing a scientific article) that motivated me to post to a blog i maintain and while i was composing the the post i noticed the strange sensation that i have done this before.

weird, hunh?

linky

artificial star clears the sky

Wednesday, June 20th, 2007

the blurring effect of the earth’s atmosphere is in large part the reason the hubble space telescope is well, a space telescope. land based telescopes have had to rely on a technology known as adaptive optics in which a reference star is used as a point of reference to adjust for the rivers of air they must see through to observe the heavens beyond.

the drawback to this is the relative shortage of good reference stars in the skies — in particular over the southern hemisphere.

enter the good folks of european souther observatory over at paranal observatory in chile — they have developed a new technique by which they create their own reference star using lasers.

The Laser Guide Star System installed at Paranal uses the PARSEC dye laser developed by MPE-Garching and MPIA-Heidelberg. The laser beam takes advantage of the layer of sodium atoms that is present in Earth’s atmosphere at an altitude of 90 kilometres. Shining at a well-defined wavelength the laser makes it glow. Despite this star being about 20 times fainter than the faintest star that can be seen with the unaided eye, it is bright enough for the adaptive optics to measure and correct the atmosphere’s blurring effect.

Full article at ESO here
Full adaptation from Science Daily here