PostgreSQL and word games

Question

In a word game similar to Ruzzle or Letterpress, where users have to construct words out of a given set of letters:

I keep my dictionary in a simple SQL table:

create table good_words (
        word varchar(16) primary key
);

Since the game duration is very short I do not want to check every entered word by calling a PHP script, which would look that word up in the good_words table.

Instead I'd like to download all possible words by one PHP script call before the round starts - since all letters are known.

My question is: if there is a nice SQLish way to find such words?

I.e. I could run a longer-taking script once to add a column to good_words table, which would have same letters as in the word columnt, but sorted alphabetically... But I still can't think of a way to match for it given a set of letters.

And doing the word matching inside of a PHP script (vs. inside the database) would probably take too long (because of bandwidth: would have to fetch every row from the database to the PHP script).

Any suggestions or insights please?

Using postgresql-8.4.13 with CentOS Linux 6.3.

UPDATE:

Other ideas I have:

1. Create a constantly running script (cronjob or daemon) which would prefill an SQL table with precompiled letters board and possible words - but still feels like a waste of bandwidth and CPU, I would prefer to solve this inside the database
2. Add integer columns a, b, ... , z and whenever I store a word into good_words, store the letter occurences there. I wonder if it is possible to create an insert trigger in Pl/PgSQL for that?

Answer 1

Nice question, I upvoted.

What you're up to is a list of all possible permutations of the given letters of a given length. As described in the PostgreSQL wiki, you can create a function and call it like this (matches highlighted letters in your screenshot):

SELECT * FROM permute('{E,R,O,M}'::text[]);

Now, to query the good_words use something like:

SELECT gw.word, gw.stamp
  FROM good_words gw
  JOIN permute('{E,R,O,M}'::text[]) s(w) ON gw.word=array_to_string(s.w, '');

Answer 2

This could be a start, except that it doesn't check if we have enough letters, only if he have the right letters.

SELECT word from
(select word,generate_series(0,length(word)) as s from good_words) as q
WHERE substring(word,s,1) IN ('t','h','e','l','e','t','t','e','r','s')
GROUP BY word
HAVING count(*)>=length(word);

http://sqlfiddle.com/#!1/2e3a2/3

EDIT:

This query select only the valid words though it seems a bit redundant. It's not perfect but certainly proves it can be done.

WITH words AS 
(SELECT word, substring(word,s,1) as sub from
(select word,generate_series(1,length(word)) as s from good_words) as q
WHERE substring(word,s,1) IN ('t','e','s','e','r','e','r','o','r','e','m','a','s','d','s','s'))

SELECT w.word FROM
(
SELECT word,words.sub,count(DISTINCT s) as cnt FROM
(SELECT s, substring(array_to_string(l, ''),s,1) as sub FROM
(SELECT l, generate_subscripts(l,1) as s FROM 
 (SELECT ARRAY['t','e','s','e','r','e','r','o','r','e','m','a','s','d','s','s'] as l) 
 as q) 
as q) as let JOIN
words ON let.sub=words.sub
GROUP BY words.word,words.sub) as let
JOIN
(select word,sub,count(*) as cnt from words
 GROUP BY word, sub)
as w ON let.word=w.word AND let.sub=w.sub AND let.cnt>=w.cnt
GROUP BY w.word
HAVING sum(w.cnt)=length(w.word);

Fiddle with all possible 3+ letters words (485) for that image: http://sqlfiddle.com/#!1/2fc66/1 Fiddle with 699 words out of which 485 are correct: http://sqlfiddle.com/#!1/4f42e/1

Edit 2: We can use array operators like so to get a list of words that contain the letters we want:

SELECT word as sub from
(select word,generate_series(1,length(word)) as s from good_words) as q
GROUP BY word
HAVING array_agg(substring(word,s,1)) <@ ARRAY['t','e','s','e','r','e','r','o','r','e','m','a','s','d','s','s'];

So we can use it to narrow down the list of words we need to check.

WITH words AS 
(SELECT word, substring(word,s,1) as sub from
(select word,generate_series(1,length(word)) as s from 
(
  SELECT word from
(select word,generate_series(1,length(word)) as s from good_words) as q
GROUP BY word
HAVING array_agg(substring(word,s,1)) <@ ARRAY['t','e','s','e','r','e','r','o','r','e','m','a','s','d','s','s']
)as q) as q)
SELECT DISTINCT w.word FROM
(
SELECT word,words.sub,count(DISTINCT s) as cnt FROM
(SELECT s, substring(array_to_string(l, ''),s,1) as sub FROM
(SELECT l, generate_subscripts(l,1) as s FROM 
 (SELECT ARRAY['t','e','s','e','r','e','r','o','r','e','m','a','s','d','s','s'] as l) 
 as q) 
as q) as let JOIN
words ON let.sub=words.sub
GROUP BY words.word,words.sub) as let
JOIN
(select word,sub,count(*) as cnt from words
 GROUP BY word, sub)
as w ON let.word=w.word AND let.sub=w.sub AND let.cnt>=w.cnt
GROUP BY w.word
HAVING sum(w.cnt)=length(w.word) ORDER BY w.word;

http://sqlfiddle.com/#!1/4f42e/44

We can use GIN indexes to work on arrays so we probably could create a table that would store the arrays of letters and make words point to it (act, cat and tact would all point to array [a,c,t]) so probably that would speed things up but that's up for testing.

Answer 3

Create a table that has entries (id, char), be n the number of characters you are querying for.

select id, count(char) AS count from chartable where (char = x or char = y or char = z ...) and count = n group by id;

OR (for partial matching)

select id, count(char) AS count from chartable where (char = x or char = y or char = z ...) group by id order by count;

The result of that query has all the word-id's that fit the specifications. Cache the result in a HashSet and simple do a lookup whenever a word is entered.

Answer 4

Does not work in 8.4. Probably 9.1+ only. SQL Fidlle

select word
from (
    select unnest(string_to_array(word, null)) c, word from good_words
    intersect all
    select unnest(string_to_array('TESTREROREMASDSS', null)) c, word from good_words
) s
group by word
having
    array_agg(c order by c) = 
    (select array_agg(c order by c) from unnest(string_to_array(word, null)) a(c))

Answer 5

You can add the column with sorterd letters formatted like '%a%c%t%'. Then use query:

 select * from table where 'abcttx' like sorted_letters

to find words that can be built from letters 'abcttx'. I don't know about performance, but simplicity probably can't be beaten :)

Answer 6

Here is a query that finds the answers that can be found by walking through adjacent fields.

with recursive
input as (select '{{"t","e","s","e"},{"r","e","r","o"},{"r","e","m","a"},{"s","d","s","s"}}'::text[] as inp),
dxdy as(select * from (values(-1,-1),(-1,0),(-1,1),(0,1),(0,-1),(1,-1),(1,0),(1,1)) as v(dx, dy)),
start_position as(select * from generate_series(1,4) x, generate_series(1,4) y),
work as(select x,y,inp[y][x] as word from start_position, input
union
select w.x + dx, w.y + dy, w.word || inp[w.y+dy][w.x+dx]   
   from dxdy cross join input cross join work w 
   inner join good_words gw on gw.word like w.word || '%'
)
select distinct word from work
where exists(select * from good_words gw where gw.word = work.word)

(other answers don't take this into account).

Sql fiddle link: http://sqlfiddle.com/#!1/013cc/14 (notice You need an index with varchar_pattern_ops for the query to be reasonably fast).

Answer 7

My own solution is to create an insert trigger, which writes letter frequencies into an array column:

create table good_words (
        word varchar(16) primary key,
        letters integer[26]
);

create or replace function count_letters() returns trigger as $body$
    declare
        alphabet varchar[];
        i integer;
    begin

        alphabet := regexp_split_to_array('abcdefghijklmnopqrstuvwxyz', '');
        new.word := lower(new.word);

        for i in 1 .. array_length(alphabet, 1)
        loop
                -- raise notice '%: %', i, alphabet[i];
                new.letters[i] := length(new.word) - length(replace(new.word, alphabet[i], ''));
        end loop;
        return new;
    end;
$body$ language plpgsql;

create trigger count_letters
    before insert on good_words
    for each row execute procedure count_letters();

Then I generate similar array for the random board string tesereroremasdss and compare both arrays using the array contains operator @>

Any new ideas or improvements are always welcome!