I know that I can export a database’s table definitions/schema via the Conductor:
http://extranet.igods.com/conductor/databases.vspx
… how do I also export the index definitions for those same tables?
I’ve completely lost my file with all my table and index definitions. my life’s work, 20 years. gone.
When you goto the Conductor Database -> SQL Database Objects tab, when you select a table along with the Definitions Action is their not also an Indexes action that can be selected to get a list of available indexes on the table ? They can’t be exported but can at least get a list of them which can then be recreated manually …
ugh. very lame. so I’m completely screwed, then.
Less emotion and more technical detail on what is your issue needed …
Is your database not still functional or at least a know good backup of it ?
If you have a list of the indexes on the the table(s), why is that not enough information to recreate them or are there too many ?
We maybe able to provide an old program that can dump a table indexes as SQL commands, thus what OS are you running on macOS ?
I do have a working version of my database, but you hit it, there are way too many tables and indexes to manually extract that way. 258 tables, average say, 1.5 indexes per table, I’m looking at 387 indexes. If the tedium didn’t kill me, the manual errors would.
I figured there would be a way to access the indexes the same was as the database structure. The indexes must be in a system table somewhere, for both function and for displaying in the SQL Database Objects screen. Is that table only accessible internally, i.e. from the Conductor VSPX container code?
If I could access that table, I could query it and reassemble my indexes that way. I can wrangle the text fields once I have them extracted, and I’d be more than happy to share the code once I’ve finished to help others rebuild from an active DB in the future.
Unfortunately, my text file with table and index definitions was somehow zeroed out 2 months ago, so the daily backup I keep just has 30 days of empty files. Oddly, the only other copy I have of the file dates back 13 years. Enough to reference my earliest coding style, but no where near good enough to rebuild from scratch.
Ironically, I was just about to try building a new server, and this is the file that starts it all…
I’m on Virtuoso 05.11.3039-pthreads on Mac OS X 10.6.5; the current server is running on an ancient Mac OS version (thus the intended migration to a new server). I tried that a year or 2 ago, but my solution failed under Virtuoso 7.2.6-rc1.3230-pthreads on Mac OS X 10.12.6. I was hoping to try again.
The indexes are maintained in a number of tables mainly DB.DBA.SYS_KEYS it seems, you can enable Virtuoso tracing (trace_on()) if you want to see what tables are being queried.
This old dbdump program for macOS can be used to automatically dump the SQL for creating the indexes of a table with the command dbdump hostname:portno uid pwd tablename={table-name} -i -r, for example:
dbdump localhost:1111 dba dba tablename=test..hugh -i -r
-- ./dbdump: dumping datasource "localhost:1111", username=dba
-- tablequalifier=NULL tableowner=NULL tablename=is given, one or more tabletype=NULL
-- Connected to datasource "OpenLink Virtuoso", Driver v. 06.01.3127 OpenLink Virtuoso ODBC Driver.
-- get_all_tables: tablepattern="test..hugh",10
-- Definitions of 1 tables were read in.
CREATE UNIQUE INDEX hughindx ON test.DBA.hugh(c1,c2);
CREATE UNIQUE INDEX hughindxc2 ON test.DBA.hugh(c2);
EXIT;
Seems I’m not even able to get a clean list of tables from the Virtuoso/Database/SQL Database Objects -> Export Schema tool. I’ve requested only Table Definitions and Foreign Keys (disabling the checkboxes for Stored Procedures and Views), but I’m still getting my Procedure Views being exported.
That’s a LOT of noise in my export. Is there a way to eliminate Procedure Views from that export? Have I missed an obvious step?
An example might help… this is the Procedure View that I’ve created for accessing a Stored Procedure that parses out analytics system:
create procedure view WEBSTATS.EXTRANET.REPORT_01 as WEBSTATS.EXTRANET.PARSE_REPORT_01(
query,
userfilter,
querymode,
sortorder,
extranetname,
baseURL,
subreport,
port,
statspassword)
(reportline integer,
itemdate varchar,
pages varchar,
errors varchar,
visits varchar,
trackedVisits varchar,
searchVisits varchar,
goal2 varchar,
goal2PercentVisit varchar,
goal1PercentVisit varchar,
goal1 varchar,
duration varchar,
bounceRate varchar,
visitors varchar
);
grant execute on WEBSTATS.EXTRANET.REPORT_01 to EXTRANET;
When I export the Table Definitions from the Export Schema utility in Conductor, I see this definition:
create table "WEBSTATS"."EXTRANET"."REPORT_01"
(
"reportline" INTEGER,
"itemdate" VARCHAR,
"pages" VARCHAR,
"errors" VARCHAR,
"visits" VARCHAR,
"trackedVisits" VARCHAR,
"searchVisits" VARCHAR,
"goal2" VARCHAR,
"goal2PercentVisit" VARCHAR,
"goal1PercentVisit" VARCHAR,
"goal1" VARCHAR,
"duration" VARCHAR,
"bounceRate" VARCHAR,
"visitors" VARCHAR
);
That’s not an actual table, it’s just a Procedure View. The table def shown above is the mirror image of the Procedure View’s output columns.
I see what you mean creating the procedure view from the example in the docs, which get dumped as if a table.
$ cat test.sql
create table "test"."DBA"."hugh"
(
"c1" INTEGER,
"c2" INTEGER,
PRIMARY KEY ("c1")
);
create table "test"."DBA"."n_range"
(
"n1" INTEGER,
"n2" DECIMAL
);
$
Although the docs also state a procedure view can be referenced like a table, which may be why they are dumped as such, but I agree should not as in the Conductor they are listed as a “Procedure”, thus will check this behaviour with development.
Thank you, Hugh, dbdump worked extremely well, I have my indexes now! dbdump wouldn’t run on the server’s Mac OS 10.6.5 via localhost (no surprise), but it worked wonderfully on my OS X 10.13 client Mac calling the server via a remote domain address.
I ran this query against each of my custom databases (e.g. TOOLS), using the % wildcard to match all tables within each database:
./dbdump www.example.com:1111 dba dbapassword tablename=TOOLS..% -i -r
Conductor was helpful in exporting my handful of Views as well, which helped recover a couple that were defined in the same missing file as my Tables.
Now I just need to separate my Tables and my Procedure Views exported from Conductor. There’s a long way that gets me there, but I’m hoping to find the shortcut. I can imagine this isn’t the first time that recovering from an existing environment will come in handy.
no workarounds to export on the tables?
I thought u were going to strip out the unnecessary procedure views ?
If u want just tables the dbdump can be used to dump just tabletype=TABLE ie
$ ./dbdump localhost:1111 dba dba -q "tablename=test..%%" tabletype=TABLE
DROP TABLE test.DBA.hugh;
CREATE TABLE test.DBA.hugh(c1 INTEGER NOT NULL,c2 INTEGER,PRIMARY KEY(c1));
CREATE UNIQUE INDEX hughindx ON test.DBA.hugh(c1,c2);
CREATE UNIQUE INDEX hughindxc2 ON test.DBA.hugh(c2);
That is extremely helpful, thank you Hugh. That call exports my tables and their associated indexes (which pretty much restores my missing file that did the same). It even includes the inserts for my lookup tables (state codes, etc.), and all other existing records. That could be extremely helpful in the future.
Thank you for saving my project.