Messages

1

General Board / Re: Oracle 10.2 unable to obtain table info

« on: August 07, 2009, 06:57:24 pm »

Hi Martin,

Did you install the oracle 10g express or standard edition ?

2

General Board / Import DB schema from ODBC  ... Oracle

« on: August 07, 2009, 12:09:39 am »

Hi,

I am trying to reverse oracle views.

Those views ared defined in one oracle schema but tables used for building those views are defined in another oracle schema.

How can I reverse properly the dependancies, that is a table list for each view.

Moreover I didn't succeed to reverse the sql script used for building the views.
I always get the same sql order, that is:

CREATE OR REPLACE VIEW "VUE_PIVI_CAL"
AS
S

View example [tables are defined in a so called "robust" schema and the view in another schema]

CREATE OR REPLACE VIEW VUE_PIVI_CAL AS
SELECT
DISTINCT
    substr(tm.nom || tm.versiontm,1,36),
to_char(caltmanticipejournee.jourcalendaire,'DD/MM/YYYY'),
to_char(tm.validitedebut,'DD/MM/YYYY'),
vunitegestion.objet_de_reference
FROM
    robust.tm JOIN
    robust.categorietm ON
        tm.id_categorietm = categorietm.id JOIN
    robust.caltmanticipejournee ON
        tm.id = caltmanticipejournee.id_tm JOIN
    robust.rvligne ON
        tm.id_ligneprincipale = rvligne.id JOIN
    robust.rvligne_vunitegestion ug_offre ON
        rvligne.id = ug_offre.id_rvligne
AND ug_offre.role = 'com.ratp.rrs.om.organisationtransport.VersionUniteConceptionOffre' JOIN
    bsx.vunitegestion ON
        ug_offre.id_vunitegestion = vunitegestion.id_version
WHERE
    categorietm.identifiant = 1 -- TM Type Jour
AND CalTManticipejournee.jourcalendaire >= sysdate
AND caltmanticipejournee.estactif = 1
WITH READ ONLY

3

Automation Interface, Add-Ins and Tools / Re: SQL query :: Returns Node Path Elements

« on: May 23, 2013, 06:14:18 pm »

Hi Geert,

Thank you for your answer.

There a great post on reccursive query with postgresql.
http://old.storytotell.org/blog/2009/08/11/postgresql84-recursive-queries.html

The following sequence works fine, but I should admit it's a bit complex !

-- All packages are defined in the t_object table except the root node of the model
select P.name from t_package P where P.name not in
(select O.name from t_object O where O.object_type='Package')

--[1]
CREATE TABLE t_nodepath_tmp (ea_guid VARCHAR,name VARCHAR, parent_ea_guid VARCHAR);

--[2]                          
INSERT INTO t_nodepath_tmp  (
-- child is an element, parent is an object
select C.ea_guid as "id", 'E::' || C.name as "name", P.ea_guid as "parent_id"
from t_object C, t_object P
where C.parentid=P.object_id
union
-- child is an element, parent is a package
select C.ea_guid as "id", 'E::' || C.name as "name", P.ea_guid as "parent_id"
from t_object C, t_package P
where C.package_id=P.package_id
and C.object_type != 'Package'
and C.parentid='0'
union
-- child is a package, parent is a package
select C.ea_guid as "id", 'P::' || C.name as "name", P.ea_guid as "parent_id"
from t_object C, t_package P
where C.package_id=P.package_id
and C.object_type='Package'
and C.parentid='0'
union
-- child is the root node of the model, no parent
select C.ea_guid as "id", 'P::' || C.name as "name", '0' as "parent_id"
from t_package C
where C.parent_id='0'
union
-- child is a diagram, parent is an object
select C.ea_guid as "id", 'D::' || C.name as "name", P.ea_guid as "parent_id"
from t_diagram C, t_object P
where C.parentid=P.object_id
union
-- child is a diagram, parent is an object
select C.ea_guid as "id", 'D::'|| C.name as "name", P.ea_guid as "parent_id"
from t_diagram C, t_package P
where C.package_id=P.package_id
and C.parentid='0'
order by name)

-- [3]

CREATE TABLE t_nodepath (id SERIAL PRIMARY KEY, ea_guid VARCHAR,name VARCHAR, parent_ea_guid VARCHAR);

-- [4]
INSERT INTO t_nodepath (ea_guid, name, parent_ea_guid) select ea_guid, name, parent_ea_guid from t_nodepath_tmp

-- [5]

INSERT INTO fs (id, name, parent_id)
SELECT c.id as 'id', c.name as 'name', p.id as 'parent_id'
FROM t_nodepath c, t_nodepath p
where c.parent_ea_guid=P.ea_guid

INSERT INTO fs (id, name, parent_id)
SELECT c.id as "id", c.name as "name", NULL as "parent_id"
FROM t_nodepath c
WHERE c.parent_ea_guid is NULL

-- [6]

WITH RECURSIVE path(name, path, parent, id, parent_id) AS (
          SELECT name, '/', NULL, id, parent_id FROM fs WHERE id = (SELECT id from fs where parent_id is NULL)
          UNION
          SELECT
            fs.name,
            parentpath.path ||
              CASE parentpath.path
                WHEN '/' THEN ''
                ELSE '/'
              END || fs.name,
            parentpath.path, fs.id, fs.parent_id
          FROM fs, path as parentpath
          WHERE fs.parent_id = parentpath.id)
        SELECT * FROM path;

4

Automation Interface, Add-Ins and Tools / SQL query :: Returns Node Path Elements

« on: May 16, 2013, 07:16:10 pm »

Hi,

I am looking for an SQL query that returns the Node Path for all element defined in a model.

This Node Path is the one returned by the "Right Click / Copy Reference / Copy Node Path to Clipboard" feature from the Project Browser View.

Thanks !
Alexandre Tallec

5

Automation Interface, Add-Ins and Tools / SQL query :: Returns author for each connector

« on: May 16, 2013, 07:18:19 pm »

Hi,

I am looking for an SQL query that returns two colums:
1] the author name, 2] the connector name

The t_object table has an author column whereas the t_connector doesn't.

Any idea ? Thanks !

Alexandre Tallec

6

Automation Interface, Add-Ins and Tools / Re: Get DiagramObject objects

« on: May 16, 2013, 07:07:37 pm »

Hi Sven,

This request should work fine.

select D.name, D.diagram_id, O.name, O.object_id
from t_object O, t_diagram D, t_diagramobjects OD
where OD.object_id=O.object_id and OD.diagram_id=D.diagram_id
order by D.name, O.name

Alexandre Tallec