Gossamer Forum
Home : Gossamer Threads Inc. : Custom Modification Jobs :

*LinksSQL 2* How much to convert my text db>MySQL?

Quote Reply
*LinksSQL 2* How much to convert my text db>MySQL?
Calling all SQL folks! How much would you charge to convert my 'standard' text file/delimited database of Categories, Subcategories, and links into MySQL AND insert it into my Links SQL 2.0 program? I can give you my data in 'any' order/structure you like and formated as .txt or, .mdb.

(I have windows 98 & my data is in text or MSaccess format)

Ryan

Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
What does the .txt format look like?

Paul Wilson.
http://www.wiredon.net/gt/
http://www.perlmad.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Are you making fun of me? :-)

Okay, so I don't know the correct 'lingo'... but I do know how to create a text delimited file:
last|first|addr|phone|...
or,
"last","first","addr","phone","...

and I want this 'data' inserted into my LinksSql 2.0 database... you tell me what format/order you want me to put it in.

Ryan
HomerUSA

Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
NO I certainly was not making fun of you, I just wanted to know in what format the .txt file was.

For Links2 it needs to be pipe delimited.

If you can pipe delimit it then you can import it into Links2 and then from there into Links SQL

You will need to make sure it is in Links2 format....

ID|Title|URL|......etc

Then update linksid.txt to reflect the link total and add the URL's to url.db

I may be missing an easier way.

Paul Wilson.
http://www.wiredon.net/gt/
http://www.perlmad.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Paul,

First of all, thanks for getting back to me so quick... And I did NOT take offense to anything you wrote! I have just been 'stuck' in windows soooo long that I assumed 'everyone' knew what the .txt extension was! When you asked me about it I thought you were joking...

I'll download links2 & try what you said... AND I REALLY REALLY REALLY appreciate the help! I have been downloading and installing cgi scripts for two years (getting most of them to work) and LinksSql 2.0 is the first product I have purchased. It just seems like a great & powerful product however, I got stuck because I don't know MySql and I was amazed that there is no import function for 'linux challenged' folks like me.

Thanks for you time
Ryan
HomerUSA

Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Firstly I think you misunderstood what I said. Of course I know what .txt is - what I meant was what format was the data in, within the .txt file - ie. how was it delimited....I mustn't have made myself clear.

Secondly, there is an import feature in Links SQL.... Database > Import

You can install from Links2, Links SQL1.13 and Links SQL2, thats why I said you should add your data to Links2 and you can then use the import feature in Links SQL2 to import the data from Links2.

If you are still having trouble send me an email..

pwilson@wiredon.net

Paul Wilson.
http://www.wiredon.net/gt/
http://www.perlmad.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Paul:

I'm still looking for a way to this same thing. I have tried the import utilities under Admin/Database/Links Properties as well as in MySQLMan. Both work but both only allow you to import to one table at a time. I want to import a pipe delimited ASCII file that has a category definition in it as well. The present format is URL|TITLE|DESCRIPTION|KEYWORDS|CATEGORY|EMAIL. I can import the first four fields to the Links table just by swapping the field order on import, no problem. But Category at the same time? that's another story.

I looked at the admin frame to add new links and I see it calls db.cgi?do=add_record&db=Links&URL=http... etc. using an input select field name of CatLinks.CategoryID=n. I'm guessing that what I need is a way to add the links in the delimited ASCII using db.cgi? Does that sound right to you? Why isn't there an easier way to do this?

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
The best way to do it would be to get the information to look just like LInks 2.0, download that and have a look at how the file is formatted. Then use the Import function in Links SQL 2.0 to import from Links 2.0. The files need to have the same names as Links 2.0 as well.

Mel

Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Thank you Mel.

Do you know that, despite the half dozen or so times I've posted this question, you are the first to lend me any assistance with it at all?

I have not used Links 2.0 and, quite frankly, I've got better things to do with my time than install another software package that I will never use just to have a look at how the flat file database is organized. I've searched the 2.0 forum for "Database structure", "Table definition" etc. and can't find what I'm looking for. Is there somewhere where I can find the database structure definition without installing 2.0?

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Search for field definition.

Regards,

Eliot Lee
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
If you just download the Links 2.0 program and just untar it to a directory, then without installing it, you can get the .def files and the .db files. The two of particular interest to you will be links.db, category.db, links.def and category.def. so basically you want to use the .def files from links 2.0, create your .db files (they are just pipe delimited text files) to match the links 2.0 format. Then put the .def files in a directory and the .db files in a directory off of that called data so you will have:
directory/links.def
directory/category.def
directory/data/links.db
directory/data/category.db
and when you use the Links sql 2.0 import script, just tell it the path to the .def files.

Mel

Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Okay Mel, I'll give it a shot.

Can't be any worse than scouring the forums for "field definition" as suggested by Eliot, most of which turns up Eliot telling others to search for "field definition"!

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Mel, I did as you suggested. The initial .db files are, of course blank. The links.def file

Code:
ID => [0, 'numer', 5, 8, 1, '', ''],
Title => [1, 'alpha', 40, 75, 1, '', ''],
URL => [2, 'alpha', 40, 75, 1, 'http://', '^http|news|mailto|ftp'],
Date => [3, 'date', 15, 15, 1, \&get_date, ''],
Category => [4, 'alpha', 0, 150, 1, '', ''],
Description => [5, 'alpha', '40x3', 500, 0, '', ''],
'Contact Name' => [6, 'alpha', 40, 75, 1, '', ''],
'Contact Email' => [7, 'alpha', 40, 75, 1, '', '.+@.+\..+'],
Hits => [8, 'numer', 10, 10, 1, '0', '\d+'],
isNew => [9, 'alpha', 0, 5, 0, 'No', ''],
isPopular => [10, 'alpha', 0, 5, 0, 'No', ''],
Rating => [11, 'numer', 10, 10, 1, 0, '^[\d\.]+$'],
Votes => [12, 'numer', 10, 10, 1, 0, '^\d+$'],
ReceiveMail => [13, 'alpha', 10, 10, 1, 'Yes', 'No|Yes']
My spider creates 6 output fields, URL|TITLE|DESCRIPTION|KEYWORDS|CATEGORY|EMAIL
Do you think import will work if I append a seventh field |ID to my spider output which it
leaves blank for SQL to autoassign and I rearrange and trim the links.def file to this?

Code:
URL => [0, 'alpha', 40, 75, 1, 'http://', '^http|news|mailto|ftp'],
Title => [1, 'alpha', 40, 75, 1, '', ''],
Description => [2, 'alpha', '40x3', 500, 0, '', ''],
Keywords => [3, 'alpha', '40x3', 500, 0, '', ''],
Category => [4, 'alpha', 0, 150, 1, '', ''],
'Contact Email' => [5, 'alpha', 40, 75, 1, '', '.+@.+\..+'],
ID => [6, 'numer', 5, 8, 1, '', '']
The only reason I do not make ID the first field has to do with entry validation
inside my script. I'd rather not mess with it if I don't have to.

I exported my present category fields corresponding to the fields from the 2.0
category.def file to a file links/admin/tmp/data/categories.db. The only changes it seems
I need make are Meta_Description to 'Meta Description', ditto for Meta_Keywords,
unless you say otherwise. Does the field Related equate to FatherID in SQL?
or can I leave this field blank?

And now the tough questions: The majority of my submissions come in the form
of URL only from Submit Wolf, a VERY POPULAR meta search engine submission
software program. My spider fills in the blanks for TITLE, DESCRIPTION, KEYWORDS
and sometimes EMAIL by capturing them from the pages. Therefore, the majority
of entries will not have a CATEGORY assignment.

1) Will SQL allow me to import those records?
2) If it will, what will happen if a URL is imported without a category assignment that
matches a URL already in the SQL database that has a CatLink relation assigned to it?

I will, of course, back up my existing database and try it anyway but I would feel a lot
better if I knew it's been done and is doable.

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Well, I've answered most of my own questions. It seems the only way to use the Import feature from a Links 2.0 file forces Links SQL to attempt a full backup. It does not append the new data.

Attempting an Import from the Links Properties panel always returns a "No category selected" error. However, there appears to be no way to select a category for import even if I wanted to! Am I missing something? Is there a way to Import from the Links Properties panel AND assign a category to avoid this error?

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Hi,

>if I append a seventh field |ID to my spider output which >it
>leaves blank for SQL to autoassign and I rearrange and >trim the links.def file to this?
Yes that should be fine as the import script auto assigns the ID regardless.

>Does the field Related equate to FatherID in SQL?
>or can I leave this field blank?
You shouldn't have to worry about the FatherID because the import script is made to handle it, you just need to put the full category name for Name in the category file.

> Will SQL allow me to import those records?
You will not be able to import links without categories, it will cause errors. What I recommend then is if the link doesn't have a category, then have your spider put it in a default one.

Hope that helps,
Mel

Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
In Reply To:
Hope that helps,
It does but the fact remains that I still can not append new records using the import mechanism from Links Properties. It rewrites the entire database every time and this simply will not do.

Fact #2) Direct import to the Links table from MySQLMan without category, cumbersome as it may be, does work, and it can append new records without overwriting existing records.

Fact #3) Simultaneous addition of links with category can be accomplished via db.cgi so long as I pass all the required variables from my script along with CatLinks.CategoryID=n.

What I will probably end up doing is writing my own script to merge categorized entries through db.cgi and another for noncategorized entries through MySQLMan/Links/Import, unless I do define a default category and send it all through db.cgi but that brings up one more question.

Let's say I create a default category and call it Unassigned. Building this category will take a great deal of time and consume a great deal of space. When I build, is there a way to instruct Links to omit building this one category? This would solve a great deal of my problems.

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
What I'm thinking is a hack to nph-build.cgi in sub _build_category, something like this:

Code:
while (my ($id, $name) = $sth->fetchrow_array) {
unless($id = nn){ #where nn is the numeric value for category Unassigned
$clean_name = $db->as_url($name);
$page = $CFG->{build_root_path} . "/" . $clean_name . '/' . $CFG->{build_index};
$url = $CFG->{build_root_url} . "/" . $clean_name . '/' . $CFG->{build_index};
$USE_HTML ?
print "\tBuilding category <a href='$url' target='_blank'>$name</a> ... \n" :
print "\tBuilding category $name ... \n";
_build_dir ($clean_name);
}
$total = $catlink_db->count ( { 'CatLinks.CategoryID' => $id, isValidated => 'Yes' } );
print "\t\tLinks: $total\n";
Am I on the right track?

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Hi,

This would work, however you must make sure links are in there as Unvalidated, or they will still show up in search results.

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
In Reply To:
This would work, however you must make sure links are in there as Unvalidated, or they will still show up in search results.
PRESTO!!! That's EXACTLY what I want! A hidden category that is still searchable. This now gives me a way to import links WITH category even though most of my links have not been assigned a category. I simply default to the hidden category if none other was specified at the time of submission or during spidering. I can not believe that I am the only one who wants this.

I also need to apply this same type of category exclusion in page.cgi. Can you tell me where?

Also, since I have uncovered a bug in MySQLMan which allows importing duplicate records even though I instruct it to "Ignore duplicate records":

1) Is there a fix for this?
2) Is there a way to use Database/Import from Links 2.x to Append new records instead of rewriting the entire database?
3) Is there a way to use Links/Properties/Import at all? Every time I try it tells me I have not specified a category. From I can see, there is now way to specify a category. What am I missing?

Mark Brasche
http://SurfSafely.com/
Quote Reply
Re: *LinksSQL 2* How much to convert my text db>MySQL? In reply to
Okay, this much now works. Links no longer builds index pages for category 91 which I call "Default". Problem is it still puts category "Default" on the home page with a dead end hyperlink to /Default/index.shtml. Where do I look to keep it from building into the home page as well?

Code:
while (my ($id, $name) = $sth->fetchrow_array) {
unless($id eq "91"){#begin unless $id=91
$clean_name = $db->as_url($name);
$page = $CFG->{build_root_path} . "/" . $clean_name . '/' . $CFG->{build_index};
$url = $CFG->{build_root_url} . "/" . $clean_name . '/' . $CFG->{build_index};
$USE_HTML ?
print "\tBuilding category <a href='$url' target='_blank'>$name</a> ... \n" :
print "\tBuilding category $name ... \n";
_build_dir ($clean_name);
$total = $catlink_db->count ( { 'CatLinks.CategoryID' => $id, isValidated => 'Yes' } );
print "\t\tLinks: $total\n";

# Do sub-pages if requested.
if ($CFG->{build_span_pages}) {
$lpp = $CFG->{build_links_per_page} || 25;
$num_pages = int ($total / $lpp);
($total % $lpp) and ($num_pages++);

# Create the main page.
open (PAGE, "> $page") or _cant_open($page, $!);
print PAGE Links::Build::build ('category', { id => $id, nh => 1, mh => $lpp });
close PAGE;
my $perms = oct ($CFG->{build_file_per});
chmod ($perms, $page);

# Create the sub pages.
for (2 .. $num_pages) {
$page = $CFG->{build_root_path} . "/" . $clean_name . '/' . "more" . $_ . $CFG->{build_extension};
$url = $CFG->{build_root_url} . "/" . $clean_name . "/" . "more" . $_ . $CFG->{build_extension};
$USE_HTML ?
print "\t\tBuilding subpage: <a href='$url' target='_blank'>$_</a>\n" :
print "\t\tBuilding subpage: $page\n";
open (PAGE, "> $page") or _cant_open($page, $!);
print PAGE Links::Build::build ('category', { id => $id, nh => $_, mh => $lpp });
close PAGE;
chmod ($perms, $page);
}
}
else {
open (PAGE, "> $page") or _cant_open($page, $!);
print PAGE Links::Build::build ('category', { id => $id });
close PAGE;
my $perms = oct ($CFG->{build_file_per});
chmod ($perms, $page);
}
print "\tDone\n\n";
}#end unless $id=91
}
print "Finished building categories (", _time_display(), " s)\n\n";
Mark Brasche
http://SurfSafely.com/