Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

GT::SQL::Sets -- Need Some Help

Quote Reply
GT::SQL::Sets -- Need Some Help
I quickly grep'd through several different versions the LinksSQL distributions and do not see any references to the GT::SQL::Sets module anywhere. This module was included in (at least) the RC2 through 2.04 releases of LinksSQL, and appears to implement some of the multiple select queries that MySQL lacks (i.e. union, intersect, aggregate, etc.). (I never downloaded RC1, so I can not verify if it was in that version too)

There is no documentation on its interface, and from examination of the code it appears that one must pass in the name of a (primary?) key column (in most cases) and two GT::SQL::Table objects. I am confused about where it gets the name for the (temporary?) table it dumps each of its query results into. It looks like it is meant to be subclassed by GT::SQL::Table or something.

I really do not have a reason to use the module for anything right now, I am just playing with it, and trying to keep myself aware of what my options are, should I ever need the provided functionality. I have not been able to successfully do anything at all with it really. I am not even sure that the subroutines contained therein are complete, and working implementations.

Was the GT::SQL::Sets module included in the RC2 through 2.04 releases by accident?
($Id: Sets.pm,v 1.1 2000/12/20 22:00:56 aki Exp $)

Is it intended to objectize and hence provide a common interface for some of the related bits (i.e. _union_query, _intersect_query) in the GT::SQL::Search module?

Has anyone successfully made the version that came with Links RC2 through 2.04 do anything as of yet?

If so, can you provide an example?

Thanks in advance,

Quote Reply
Re: GT::SQL::Sets -- Need Some Help In reply to

GT::SQL::Sets is currently being used by the Spider plugin only, and was developed to be able to do bulk operations easily. i.e. You could select a list of links with title like '%perl%', then remove from that set all those that had host like '%gossamer%' and then do an action on the remaining set.

There isn't any documentation on this one, because it's still relatively new. =)



Gossamer Threads Inc.
Quote Reply
Re: GT::SQL::Sets -- Need Some Help In reply to
<Edited: 12-Jun-01 10:05 AM PST>

Thanks for the reply Alex. I'm still confused about the calling convention though. Lets say that I wanted to get a list of Links that are in categories 1, 2, 3, and their corresponding sub-categories. And I wanted to exclude category 4 links, where category 4 is a subcategory of category 1.

How could I do that using your SQL::Sets interface, rather than writing the queries myself, having to take all of the table relationships into consideration?

I could do it like this:
Table->do("DELETE FROM tmp ...");
But this would be a kludge compared to the SQL::Sets interface. What if the relationships change one day, then the above code must be hunted down, and revised. Also, the statements that I come up with might not be portable to other SQL servers. Seems like the most readable/portable solution is to use the Sets module, and let the SQL::Driver and friends figure out how to talk to the server for us, depending on the backend.


Quote Reply
Re: GT::SQL::Sets -- Need Some Help In reply to
Okay, so that's a bad example, I suppose, because one could do something like that this way:

Category.ID, Category.Name, Links.*
Links, CatLinks, Category
Links.ID = CatLinks.LinkID
CatLinks.CategoryID = Category.ID
Category.ID IN (1,2,3)
Category.FatherID IN (1,2,3)
Category.ID != 4
or if using SQL::Condition, something like:

$condition = GT::SQL::Condition->new(
'Category.ID', 'IN', \"(1,2,3)",
'Category.FatherID', 'IN', \"(1,2,3)",
'Category.ID', '!=', 4,
$table = GT::SQL->table('Links','CatLinks','Category');
@results = @{$table->select($condition,['Category.ID', 'Category.Name', 'Links.*'])->fetchall_hashref};
But my previous question is still a valid one as you should still be able to do it with SQL::Sets::subtract somehow, it would just be a lot slower.

I just can't figure out what tpyes of parameters it is expecting:

sub subtract {
# takes two sets, A and B, and subtracts B from A. results enter current set
my $self = shift;
my $key = shift or return;
my $seta = shift or return;
my $setb = shift or return;
Are $seta, and $setb GT::SQL::Table objects, or are they other GT::SQL::Set objects?

They almost have to be other Sets, with $key being a primary key type of column in each set.

If so, then how does set A and B get filled with data to begin with so that they can be compared..?

Maybe I'm losing my place somewhere and the answer is some methods in the base class?

Thanks for any help which you are willing to provide.

Quote Reply
Re: GT::SQL::Sets -- Need Some Help In reply to
Hi Shetland,

Hopefully this will help, the start of the documentation for GT::Sets.



This module allows you to manipulate blocks of records in a database using set operation.

To initialize, you will need to create one GT::SQL::Table object.


my $entries = $DB->table('user_record_list');


my $first_set = GT::Sets->new({ db => $entries });

Will create a new Sets object for use.

Two of the simplest functions, copy and remove:

sub cp : copies entries into your table

This function can take different types of parameters. hashref, arrayref, STH, and even a "select" query. You may pass as many arguments as you would like as the method iterates through the parameter list.

1. Hashref

Expected here is a container with all the columns mapped to values. Insert will be called and the record will be added to the table.

2. Arrayref (of hashrefs)

A collection of Hashrefs that will be added to the table.

3. STH

An active sth which will be iterated, where each record retrieved will be entered into the table. Column names must match, though if there are more columns then required, they will be ignored.

4. Query

If a string starting with "select ..." is passed in, it will be appended to a "insert into my_table select ...". You must ensure that the appropropriate SQL syntax is adhered.

sub rm : deletes entries from your table

Like cp, this method can take many different types of arguments: hashref, arrayref, sth.

1. Hashref

Just passes the hash reference to $table->delete()

2. Arrayref (of hashrefs)

Each element will be passed to $table->delete()

3. STH

An active sth which will be iterated, where each record will be passed into $table->delete()

There are also 5 other methods which are a bit more complex and allow even more operations.

sub disjoin

This will take two GT::Sets objects, a and b, and based on the column to compare upon, will remove all matches to b from a and store the result in the local object.

This does require three identically structured tables to exist.., (maybe I should put add a clone table function to GT::SQL). All of the tables must be registered in a GT::Sets object.

Say we have three tables, A, B, and C. C will be an empty table. They each only have one integer column named SIN. How about putting some stuff into A and B like so...

A: 1, 2, 3, 4, 8
B: 1, 5, 8, 9

And C is empty so...,


We have three GT::Sets objects. $a, $b, and $c. If we do the following:

$c->disjoin( 'SIN', $a, $b );

this will disjoin $b from $a and put the results into $c. When this function is completed, $c will contain.

C: 2, 3, 4

If there were records already found in C, the new records will simply be added to the list.

sub subtract
sub intersect
sub union
sub aggregate