Gossamer Forum: Products: Links 2.0: Customization: SQL integration

Mar 7, 2002, 9:57 PM

sponge

Veteran / Moderator (1936 posts)

Mar 7, 2002, 9:57 PM

Post #1 of 11

Shortcut

SQL integration

I've been doing a lot of stuff with SQL lately and was thinking about porting over parts of Links 2.0 to SQL.

First thing I'm working on is to make a search script for links. There was (briefly) an SQL search mod available several years ago for Links 1.x. I'm working on recreating that. So far I've written a routine that goes through your links.def file and creates your table based on %db_def, and then a routine that loads links.db into the new table. I already have a search script for my own SQL directory system that I wrote last year, so I can easily change this to work with Links. The search script allows for fulltext index as well as boolean searches. Per field searches can be added later on if anyone needs that.

The search plugin will require and extra step during a build. You'll need to run a script to wipe out the links table and reimport whenever you make changes to your database.

The second thing I'm working on is an SQL version of PortalLinks. This version will require the search plugin to function. This allows for quicker access, and sorting of saved favorites. (There is also I an version that runs off of links.db, but it isn't as flexible or fast, not did I ever really release it.)

Before anyone asks, NO I will not rewrite Links 2.0 to be fully SQL. It's not that it's difficult (it's not), it's because it would take forever, it's a waste of time, and I don't think Alex would approve of it anyway.

--Philip
Links 2.0 moderator

Mar 8, 2002, 11:20 AM

Andy

Veteran / Moderator (18441 posts)

Mar 8, 2002, 11:20 AM

Post #2 of 11

Shortcut

Re: [King Junko II] SQL integration In reply to

Wow, sounds cool. Definatly a SQL search engine would be cool. That would mean you could have more links in your directory, and not compramise the speeds at which searches/rates/jumps etc are done. Good on ya Cool

That review thing also sounds cool, can't wait to see a demo Smile

Andy (mod)
andy@ultranerds.co.uk

Mar 8, 2002, 1:46 PM

blacknight

User (144 posts)

Mar 8, 2002, 1:46 PM

Post #3 of 11

Shortcut

Re: [King Junko II] SQL integration In reply to

Sounds like an excellent idea. If you need any testers let me know..
Blacknight Solutions - Hosting
Search.ie
Armchair.ie

Mar 8, 2002, 10:24 PM

sponge

Veteran / Moderator (1936 posts)

Mar 8, 2002, 10:24 PM

Post #4 of 11

Shortcut

Re: [AndyNewby] SQL integration In reply to

The SQL can really add a handful of nice features, some of which I'd be glad to contribute: search, reviews, and favorites; some other things such as dynamic viewing of the site and building static pages from the table are up to the end user to work out (I've written these codes for my search engine script, but like I said, Alex wouldn't appreciate it too much if I gave that out)

Keep in mind that the admin side of Links is still based on a flatfile world, so you'd still have to worry about building your pages and your server not crapping out and loosing data.

Also not writing a review system (yet). I'm gonna hold off on doing one untill I finish my forum.

--Philip
Links 2.0 moderator

Mar 9, 2002, 12:21 AM

sponge

Veteran / Moderator (1936 posts)

Mar 9, 2002, 12:21 AM

Post #5 of 11

Shortcut

Re: [King Junko II] SQL integration In reply to

Okay if you'd like to give the import script a test, you can get the file here:
http://www.camelsoup.com/ftp/import.txt

Installation is pretty straitforward...

1) Change the first line to point to Perl on your server
2) Change the require line to point to links.def
3) Change the $links variable to point to links.db
4) $db, $user, $pass = database to use, username, and password
5) $table = name of table to create

rename to import.cgi and chmod 755. Run the script once to view usage instructions.

Things that could be changed but aren't important important at this stage in developement:

1) make ID field primary autoincrement
2) set up fulltext indexes
3) only insert records that don't already have a matching record in the sql table

--Philip
Links 2.0 moderator

Mar 9, 2002, 4:44 AM

blacknight

User (144 posts)

Mar 9, 2002, 4:44 AM

Post #6 of 11

Shortcut

Re: [King Junko II] SQL integration In reply to

Cool!

Maybe a stupid question, but should the variables for links.def and links.db be paths or URLs?

I've been trying both on a RAQ4, but it won't import anything. It creates the table - that's all.
Blacknight Solutions - Hosting
Search.ie
Armchair.ie

Mar 9, 2002, 4:48 AM

Paul

Veteran (19537 posts)

Mar 9, 2002, 4:48 AM

Post #7 of 11

Shortcut

Re: [blacknight] SQL integration In reply to

Paths, as shown in the example Smile

Mar 10, 2002, 1:30 AM

blacknight

User (144 posts)

Mar 10, 2002, 1:30 AM

Post #8 of 11

Shortcut

Re: [RedRum] SQL integration In reply to

I tried setting it to a path, but it doesn't import the links... nevermind...
Blacknight Solutions - Hosting
Search.ie
Armchair.ie

Mar 12, 2002, 12:16 AM

sponge

Veteran / Moderator (1936 posts)

Mar 12, 2002, 12:16 AM

Post #9 of 11

Shortcut

Re: [blacknight] SQL integration In reply to

Here's some basic codes to try out SQL fulltext searches...

create the fulltext index:

Code:
#!/usr/local/bin/perl 
use DBI 

my ($db, $user, $pass, $table) = qw(test links jack foobar); 

my $dbh = DBI->connect("DBI:mysql:$db", $user, $pass); 

$dbh->prepare("alter table $table add fulltext index (Title, Description(255))") or die DBI->errstr; 
$dbh->execute() or die DBI->errstr; 

$dbh->disconnect();

simple search (from memory while I try to find my old scrpt Frown

)

Code:
#!/usr/local/bin/perl 
use CGI qw(:standard); 
use DBI; 

my ($db, $user, $pass, $table) = qw(test links jack foobar); 
my $query = param("query"); 

my $dbh = DBI->connect("DBI:mysql:$db", $user, $pass); 

my $sth = $dbh->prepare("select * from $table where match(Title, Description) against($query) order by Title limit 100") 
$sth->execute() or die DBI->errstr; 
my $result = $sth->fetchrow_arrayref({}); 

print header(); 
print start_html(); 
print "<UL>"; 

foreach my $rec (@$result) { 
  print qq|<li><a href="$rec->{URL}">$rec->{Title}</a> -- $rec->{Description}\n|; 
} 

print "</UL>"; 
print end_html(); 

$dbh->disconnect();