SQL Server Insertion

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T15:41:10+00:00

Take this thread as example

Daniweb Mysql forum URL --- http://www.daniweb.com/web-development/databases/mysql/126

This thread URL --- http://www.daniweb.com/web-development/databases/mysql/threads/368668

rEhSi_123 8 Junior Poster · 2011-06-17T15:57:08+00:00

Take this thread as example
Daniweb Mysql forum URL --- http://www.daniweb.com/web-development/databases/mysql/126
This thread URL --- http://www.daniweb.com/web-development/databases/mysql/threads/368668

Thanks debasisdas for your help :)
I tried as you showed above yesterday and failed :(

Still trying to figure out as to how he has inserted it?!?

Also if the forum url is .../mysql/126 then why isnt 126 included in the thread url...../mysql/threads/368668?

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T17:05:01+00:00

mysql ---- forum name
126 ---- forum id

only forum name is included in the thread URL not the forum id.

rEhSi_123 8 Junior Poster · 2011-06-17T17:59:00+00:00

mysql ---- forum name
126 ---- forum id
only forum name is included in the thread URL not the forum id.

Hmm makes sense. Perhaps you can help!
The author mentions in his report, table 'forums' which contains fields
forum name, id, URL, threadurl, forum url

threadurl - the field contains a regular expression used to determine that
a URL points to a thread on the given forum

forumurl - the field contains a regular expression used to determine that
a URL points to a page on the forum which isn't a thread, but is neccessary
to crawl to find links to threads.

So I am not sure if the information needs to be broken down to be put into these
fields?

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T18:12:13+00:00

that sounds appropriate.

and all forums on web may not follow the same naming conventions.

rEhSi_123 8 Junior Poster · 2011-06-17T18:20:02+00:00

that sounds appropriate.
and all forums on web may not follow the same naming conventions.

So by looking at the report information would you suggest entering the
data for forum and thread url like this:

http://......../mysql/129 - forum url OR just mysql

http://......../mysql/threads/355985 - thread url OR just /threads/355985

Please advice

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T18:46:03+00:00

if you check, both of the follwing points to the same page.

[url]http://www.daniweb.com/web-development/databases/mysql/threads/368668[/url]
or
[url]http://www.daniweb.com/web-development/databases/126/threads/368668[/url]

so you can use either of them.

rEhSi_123 8 Junior Poster · 2011-06-17T20:07:22+00:00

if you check, both of the follwing points to the same page.
[url]http://www.daniweb.com/web-development/databases/mysql/threads/368668[/url]
or
[url]http://www.daniweb.com/web-development/databases/126/threads/368668[/url]
so you can use either of them.

In regards to the above code, do you mean adding the above to the threadurl field in ms sql server database?

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T20:15:03+00:00

Yes, you can go with the above approach.

rEhSi_123 8 Junior Poster · 2011-06-17T20:20:31+00:00

Yes, you can go with the above approach.

I am sorry but I am confused :$

here is my insert statement, you mean like this:

USE [autocrawldb]
GO
INSERT into dbo.forums([Forum name], url, threadRegex, forumRegex)
VALUES ('daniweb', 'http://www.daniweb.com/', '<a rel="nofollow" href="http://www.daniweb.com/web-development/databases/126/threads/368668" target="_blank">http://www.daniweb.com/web-developme...threads/368668</a>','');

It give me the following error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T20:26:32+00:00

you need to store only the following

http://www.daniweb.com/web-development/databases/mysql/threads/368668

rEhSi_123 8 Junior Poster · 2011-06-17T20:30:59+00:00

you need to store only the following
http://www.daniweb.com/web-development/databases/mysql/threads/368668

As you suggested did the following:

USE [autocrawldb]
GO
INSERT into dbo.forums([Forum name], url, threadRegex, forumRegex)
VALUES ('daniweb', 'http://www.daniweb.com/', 'http://www.daniweb.com/web-development/databases/mysql/threads/368668','http://www.daniweb.com/web-development/databases/mysql');

But get the same error :( sorry

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

This is the table design:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[forums](
	[Forum name] [varchar](50) NOT NULL,
	[id] [int] IDENTITY(1,1) NOT NULL,
	[url] [varchar](50) NOT NULL,
	[threadRegex] [varchar](50) NULL,
	[forumRegex] [varchar](50) NULL,
 CONSTRAINT [PK_forums] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

debasisdas 580 Posting Genius Featured Poster · 2011-06-17T20:44:27+00:00

I think the length of the field is the problem.

i.e.---VARCHAR(50)

change it to VARCHAR(500) and check.

rEhSi_123 8 Junior Poster · 2011-06-17T21:16:36+00:00

I think the length of the field is the problem.
i.e.---VARCHAR(50)
change it to VARCHAR(500) and check.

Thanks for your reply debasis, i changed the column length and was able to insert the
data but when I ran the actual vs c++ it gave me 'Debug Assertion Failed' dailog box :(
I can see the forum name, id and url being read (in the console box) but the error pops up when it reads threadRegex and forumRegex

I think it is a lost war :(

rEhSi_123 8 Junior Poster · 2011-06-17T21:24:35+00:00

Thanks for your reply debasis, i changed the column length and was able to insert the
data but when I ran the actual vs c++ it gave me 'Debug Assertion Failed' dailog box :(
I can see the forum name, id and url being read (in the console box) but the error pops up when it reads threadRegex and forumRegex
I think it is a lost war :(

Also this is cpp code which has been used for the execution:

using namespace std;

otl_connect db; // connect object

void insertChs(list<int> chs, int id)
{
	list<int> temp;
	temp.assign(chs.begin(), chs.end());
	int index = 1;

	otl_stream i(200, // buffer size
		"INSERT INTO forumpaths VALUES( "
		":f<int>, :f1<int>, :f2<int>)",
		// SELECT statement
		db // connect object
		); 
	// create select stream
	int tempint;
	while(temp.size() > 0)
	{
		tempint = temp.front();
		temp.pop_front();
		i<<index<<tempint<<id;
		index++;
	}
}


list<int> performAnalysis(int id)
{
	list<int> retVal;

	otl_stream i(200, // buffer size
		"select threadUrl from trainingthreads "
		"where fk_forumId>=:f<int> ",
		// SELECT statement
		db // connect object
		); 
	// create select stream
	i<<id;
	
	char tmpurl[200];
	list<list<int>> tempchs;
	list<int> tempint;
	crawler crawl(0, "", "", "", retVal);

	while(!i.eof()){ // while not end-of-data
		i >> tmpurl;
		tempint = crawl.analyse(tmpurl);
		if(tempint.size() > 1)
			tempchs.push_back(tempint);
		//retVal.push_back(tmp);
	}

	retVal = helper::subList(tempchs);

	return retVal;
}

list<int> getCh(int id)
{
	list<int> retVal;

	otl_stream i(200, // buffer size
		"select nodeindex from forumpaths "
		"where fk_forumId>=:f<int> "
		"ORDER BY [order] ASC",
		// SELECT statement
		db // connect object
		); 
	// create select stream
	i<<id;

	int tmp;
	while(!i.eof()){ // while not end-of-data
		i >> tmp;
		retVal.push_back(tmp);
	}

	if(retVal.size() == 0)
	{
		retVal = performAnalysis(id);
		insertChs(retVal,id);
	}

	return retVal;
}


void select()
{ 
	otl_stream i(200, // buffer size
		"select * from forums",
		db // connect object
		); 
	char f1[200];
	int f2;
	char f3[200];
	char forumRegex[200];
	char threadRegex[200];
	list<int> ch;

	while(!i.eof()){ // while not end-of-data
		i>>f1>>f2>>f3>>threadRegex>>forumRegex;
		cout<<"f1="<<f1<<", f2="<<f2<<", f3="<<f3<<endl;
		
		ch = getCh(f2);
		
		crawler c(f2, f3, threadRegex, forumRegex, ch);
		c.crawl();
	}
}

By looking at the code, it is only reading forum name, id and url from table 'forums' and then threadurl from training threads which is a different table

rEhSi_123 8 Junior Poster · 2011-06-21T17:08:35+00:00

I might have found the problem with the error. The previous author
of the application has set the threadRegex and ForumRegex feilds as
regular expressions. So I was wondering if anybody here could
help me convert say these urls into regular expressions please! so that
I can test the system.

thread URL - http://www.daniweb.com/web-development/databases/mysql/threads/368668

forum URL - http://www.daniweb.com/web-development/databases/mysql/126

smantscheff 265 Veteran Poster · 2011-06-21T17:45:51+00:00

Reqular expressions with variable thread and forum ids are:

http://www\.daniweb\.com/web-development/databases/mysql/threads/[0-9]+
http://www\.daniweb\.com/web-development/databases/mysql/[0-9]+

rEhSi_123 8 Junior Poster · 2011-06-21T22:33:56+00:00

Reqular expressions with variable thread and forum ids are:
http://www\.daniweb\.com/web-development/databases/mysql/threads/[0-9]+
http://www\.daniweb\.com/web-development/databases/mysql/[0-9]+

The problem is the column size of both the fields are varchar(50) and thats the way the author worked it out, i mean got the system working from what I have been told :$

rEhSi_123 8 Junior Poster · 2011-06-22T03:59:13+00:00

The problem is the column size of both the fields are varchar(50) and thats the way the author worked it out, i mean got the system working from what I have been told :$

Also thought Id add what was written in the report for the threadRegex and forumRegex fields:

threadRegex: this field contains a regular expression used to determine that a URL points to a thread on the given forum.

forumRegex: this field contains a regular expression used to determine that a URL points
to a page on the forum which isn't a thread, but is necessary to crawl to find links to
threads.

The table has five fields:

forum name, id, url, threadregex and forumregex

smantscheff 265 Veteran Poster · 2011-06-22T17:02:32+00:00

The problem is the column size of both the fields are varchar(50) and thats the way the author worked it out, i mean got the system working from what I have been told

What has field size to do with regular expressions and their validity?

rEhSi_123 8 Junior Poster · 2011-06-22T17:06:20+00:00

What has field size to do with regular expressions and their validity?

Well if I enter the data you suggested in your last reply into the respective fields then I get the following error:

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

smantscheff 265 Veteran Poster · 2011-06-22T18:08:45+00:00

So how does your insert statement look like?
And if you have a crawler URL database with a limit of 50 characters per URL you're in trouble, anyway. Why can't you change the field size?

rEhSi_123 8 Junior Poster · 2011-06-22T18:23:14+00:00

So how does your insert statement look like?
And if you have a crawler URL database with a limit of 50 characters per URL you're in trouble, anyway. Why can't you change the field size?

I am attaching the db create file to show you how many tables there are and their structure. This is the way the author build the db. I have no contact with him hence
posting here. I just want to run the system for me to understand and start the GUI development of it.

I am not sure why he choose 50?

My insert statement is as follow:

USE [autocrawldb]
GO
INSERT into dbo.forums([Forum name], url, threadRegex, forumRegex)
VALUES ('daniweb', 'http://www.daniweb.com/', 'http://www\.daniweb\.com/web-develop...mysql/threads/[0-9]+','http://www\.daniweb\.com/web-develop...tabases/mysql/[0-9]+')

I also posted the c++ file which takes in information from this table in page 2 if you want to have a look?

The column size was changed to 500 but still the program was producing error.

rEhSi_123 8 Junior Poster · 2011-06-29T16:29:09+00:00

Okay if I can explain a bit more

For example, this is a thread:

http://www.boards.ie/vbulletin/showthread.php?p=51566909

and this is a forum:

http://www.boards.ie/vbulletin/forumdisplay.php?f=374

Threads contain "showthread.php" in their URL, and forums contain "forumdisplay.php" in their URL. Therefore in the fields:

threadRegex: should contain a regular expression which will match a string with "showthread.php" and

forumRegex: should contain a regular expression which will match a string with "forumdisplay.php"

Please advice guys :)

smantscheff 265 Veteran Poster · 2011-06-29T16:46:23+00:00

You need to understand the concept of regular expressions first.
A RegEx is a pattern which matches a string of characters. You cannot convert a string to a regular expression, because there are infinitely many ways to construct a regular expression which matches a given string. For example, showthread.php and forumdisplay.php might be matched by the RegEx's

(showthread\.php|forumdisplay\.php)
(showthread|forumdisplay)\.php
[^\.]+\.php
[a-z]+\.php
.*

Which of those is the RegEx suitable for your purposes is your task to decide.

For your purpose this might do: http://www\.boards\.ie/vbulletin/(showthread|forumdisplay)\.php\?(p|f)=([0-9]+)

rEhSi_123 8 Junior Poster · 2011-06-29T16:58:42+00:00

You need to understand the concept of regular expressions first.
A RegEx is a pattern which matches a string of characters. You cannot convert a string to a regular expression, because there are infinitely many ways to construct a regular expression which matches a given string. For example, showthread.php and forumdisplay.php might be matched by the RegEx's
(showthread\.php|forumdisplay\.php)
(showthread|forumdisplay)\.php
[^\.]+\.php
[a-z]+\.php
.*
Which of those is the RegEx suitable for your purposes is your task to decide.
For your purpose this might do: http://www\.boards\.ie/vbulletin/(showthread|forumdisplay)\.php\?(p|f)=([0-9]+)

smantscheff you've been a great help. You are right I need to understand the concept of regex. I am trying to lookup few tutorial on google trying to grasp the idea. The system is not been written by me so I am trying to test every possible regexs :$

In your last reply

http://www\.boards\.ie/vbulletin/(showthread|forumdisplay)\.php\?(p|f)=([0-9]+)

information has to be seperately entered into the forum and thread fields and the http://www.boards.ie/vbulletin doesnt need to be included since we are entering the url into the url feild.

So would the following be right for forum and thread regexs:

\forumdisplay\.php\?f=([0-9]+)
\showthread\.php\?p=([0-9]+)

smantscheff 265 Veteran Poster · 2011-06-29T17:58:09+00:00

Yes, but you don't need to escape the first character of the RegEx (drop the leading backslash).

rEhSi_123 8 Junior Poster · 2011-06-29T18:04:28+00:00

Yes, but you don't need to escape the first character of the RegEx (drop the leading backslash).

Thanks you mean like this:

forumdisplay\.php\?f=([0-9]+)
showthread\.php\?p=([0-9]+)