copying databases

[resume.robot]

This is 2 questions

1. Looking for a sql statement that will copy entries from one webinator database to another webinator database on the same server. Currently we are selecting urls, and then re-spidering to the new database.

2. Looking for a method to copy a complete database from a linux server to a sun server. Experiments have failed. Currently we re-spider the entire db.

Thanks
Mike Clark

[John]

To copy between databases on the same machine you would need a Vortex script, and something like:

<DB=$sourcedb>
<SQL ROW "select id, Url, ... from html">
<DB=$targetdb>
<SQL NOVARS "insert into html (id, Url, ...) values ($id, $Url, ...)">
</SQL>
</SQL>

To copy between machines of a different architecture you need a full Texis version, and the cpdb program that comes with it.

[resume.robot]

I have written a script per John's instructions, and am getting "Query would require linear search" error:

<SCRIPT LANGUAGE=vortex>
<TIMEOUT = 600>
</TIMEOUT>
<A name=main>
<DB=/u1/data/user>
<SQL ROW "select id, New, Visited, Dlsecs, Depth, Body, Meta, Url, Title from html where (Url\Title\Meta\Body like '(resume,resum\351,curriculum-vitae,cv,bio,(education experience))') and not (Title\Meta\Body like '(eoe,resume-writing,resume-guide,resume-service,resume-distribution,resume-posting,resume-scanning,resume-utility,peoplesearch,people-search,jobbankusa,job-bank-usa,sample-resume,submit-resume,email-resume,fax-resume,send-resume,jobs.offered,jobs.agency,employment-agency,your-resume,vacancies)') and Visited > '2001-08-16'">
<DB=/u2/data/user.testing>
<SQL NOVARS "insert into html (id, New, Visited, Dlsecs, Depth, Body, Meta, Url, Title) values ($id, $New, $Visited, $Dlsecs, $Depth, $Body, $Meta, $Url, $Title)">
</SQL>
</SQL>
</A>
</SCRIPT>

The same error occurs when the "like" string is reduced to the single word 'resume'

When I remove the "like" string entirely and leave only "Visited" as a filter, I get a long string of error messages:

<!-- 000 transfer:8: SQLExecute() failed with -1 in the function execntexis -->
000 Sep 3 17:40:27 transfer:8: Insufficient permissions on html in the function ipreparetree

[John]

You are probably missing appropriate indexes for the search. You can either create an index or use <apicp> to alter the query protection settings.

You need to set the user to _SYSTEM to insert into html tables, <USER _SYSTEM>

[resume.robot]

Thank you for your help so far.

I have reviewed the apicp control parameters at http://www.thunderstone.com/site/texisman/node199.html
and don't see anything there that addresses this problem. I have used <apicp alpostproc on>
in other scripts, but it does not work in this one.

As far as writing is concerned, setting <USER = _SYSTEM> worked partially.

This script apparently wrote to Url and Title fields but not to Body field -- searches

<SCRIPT LANGUAGE=vortex>
<TIMEOUT = 600>
</TIMEOUT>
<A name=main>
<USER = _SYSTEM>
<DB=/export/usr/data/user.new>
<SQL ROW "select id, New, Visited, Dlsecs, Depth, Body, Meta, Url, Title from html where Visited > '2001-08-16'">
<DB=/export/usr/data/user2>

<SQL NOVARS "insert into html (id, New, Visited, Dlsecs, Depth, Body, Meta, Url, Title) values ($id, $New, $Visited, $Dlsecs, $Depth, $Body, $Meta, $Url, $Title)">
</SQL>
</SQL>
</A>
</SCRIPT>

[mark]

try allinear
and
... Depth, convert(Body, 'varchar' ) Body,Meta, ...

[resume.robot]

Got the allinear, thanks, but with this one:

... Depth, convert(Body, 'varchar' ) Body,Meta, ...

Is this in the select or insert string or both?

[bart]

the select only

[bart]

BTW: The SQL for this copy might be faster if you just linked the table into the
other database an then coded the SQL as follows:

<sql NOVARS "insert into html select * from linkedhtml where ...."></sql>