Entities not used

[Steve James]

As part of our 4.5 - 5.0 upgrade I want to tidy up our repository.

Is there any easy way to establish which entities are not used (either via sql query on a 4.5 repository or a 5.0 workspace). I know in 5.0 I can check individual entities by checking the 'uses'.

As 5.0 is so easy to have copies/subsets of Workspaces it makes some sense for us to upgrade a copy of our Production repository.

Thanks

[Hovik]

I'm afraid we don't Steve.

I can see that this would be a useful facility.
Writing sql to query V4 repository would be quite complex I think.

I will investigate alternatives and post them here if I find any.

[Hovik]

V5.0 uses a Data Dictionary database for cross referencing, usage information etc.
Potentially this database can be queried to find out which entities are not used. It's an enhancement we will consider in a future release.

The Data Dictionary database is embedded Apache Derby in folder C:\Users\your-user-id\.Ebase\designer\index. In a standard installation, the DB folder is called C-Ebase_5-ebaseXi_5.0-Workspace. As it's embedded, you need to shut down Ebase before you can connect to the database using any other tool such as SQuirrel sql client.

You can of course right click each entity within the designer and select 'references' to answer the same question.

[Steve James]

Excellent, that's pretty easy then from within Ebase

I'm happy to work on a simple eform if you can point me at the tables / logic behind right click 'references'

I've just created a new workspace and then connected it to the embedded derby database of the workspace I want to review and found the entities in APP.ENTITY

The obvious thing is an Ebase form that connects to the appropriate database; loop through the entities (ignoring types x/y/z) and check references. If no reference exist for a given entity return in a list for review.

[Steve James]

The below sql gives the correct results, any flaw in the logic?

I ran it and then checked the first couple. Right clicking the object and selecting references confirms that the object is an orphan. At this stage I happy enough that we have to go through the list and confirm which resources we want to delete.

It returns just database resources but is easy enough to tweak to review other entity types.

Code: Select all

select * from app.entity
where type = 'ET_RESOURCE' and subtype = 'D'
and path not in 
(select distinct destinationentitypath from app.associations)
order by displayname

[Hovik]

Our developers think it would be extremely difficult to do that as a single sql statement.
I guess going the sql route, you may end up writing different queries for each type of entity.

[Steve James]

Thanks Hovik, I'm not bothered about doing it in a single statement. It makes some sense to work through type by type.

Code:
select * from app.entity
where type = 'ET_RESOURCE' and subtype = 'D'
and path not in
(select distinct destinationentitypath from app.associations)
order by displayname

Are you confirming that the logic above is correct / there is no better query? Reading between the lines I think you are but just want to be sure.

Thanks