qb userguide 1.8 eng
DESCRIPTION
PetroVision III Query Builder ManualTRANSCRIPT
QueryBuilder v.1.8
User guide
© Geoleader, 2009
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 2
From developers
QueryBuilder developers hope that this software product will be useful for the end users. Any remarks will be taken into consideration.
Contact our developers by e-mail: [email protected] Documentation
This user guide provides the description of QueryBuilder. Later versions can differ slightly from this one and they will be published as modification or additions to the present documentation.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 3
Contents
Introduction .......................................................... 4 Purpose ............................................................... 4 Intended audience............................................... 4 System requirements .......................................... 4
Launch................................................................... 5 Reconnection to database...................................... 7 Query building ....................................................... 8 Sset relationship.................................................. 15 Fisld Filtering....................................................... 16 Functions............................................................. 20 Query execution .................................................. 22 Distinct rows ....................................................... 26 Saving query and result....................................... 27 File selection dialog box ...................................... 28 Creating views for PetroVision ............................ 29
Pick types selection........................................... 30 Create view ....................................................... 33
Multiwindow user interface ................................. 35
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 4
INTRODUCTION
Purpose QueryBuilder (hereinafter QB) – is an interactive application with
user-friendly graphical interface. It is designed for database query building (hereinafter DB).
Intended audience The “elementary level” programmers who have an experience working
with UNIX-similar operational environment and a general understanding of relational DB and SQL language.
System requirements
• Operational environment: SUN Solaris 7 (or above) for SPARC work stations and Red Hat Linux 7.3 (or above) for IBM PC compatible stations;
• PetroVision database shall be launched in the network where QB will be used;
• QB works with DB Oracle 8.0.x (or above); • X Windows system shall be installed on the work station; • ILOG libraries (version 4 for OS SUN Solaris and version 5 for
GNU Linux).
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 5
LAUNCH Give “qb_home/QB” command to launch the program, where qb_home
– path to QueryBuilder installed copy. Connecting to… window will appear.
Type user name and password.
Select required DB in the list of available databases.
Click “Ok”. If you connect to DB successfully, the main window appears. In information window (at the bottom of main window) appears the message informing about successful connection to DB. During you work with QueryBuilder the messages describing the program reactions to your action will appear in information window.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 6
Otherwise this window appears.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 7
RECONNECTION TO DATABASE To reconnect to DB or change the user click «Reconnect» button on
the toolbar.
Connecting to … window appears.
If the identification is successful, then new connection will be used in
the created query windows (see MULTIWINDOW USER INTERFACE).
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 8
QUERY BUILDING To build new query click “New” button on the toolbar.
In working zone the query builder window appears.
At first, in pop up list, select the scheme containing the table from the
query.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 9
The selected scheme tables will be listed.
On the left of table name there is an icon identifying the type:
– Table; – View; – Snapshot.
Select required table and add it to the query by drag and drop or click
“Add table” button.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 10
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 10
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 11
In the query zone appears table and its structure.
The structure is displayed as table field name and icon on the left, which indicates the data type defined for this field:
– Char; – Numeric; – Date; – Blob. Do the same to add the second table.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 12
If some table fields are hidden (showed by «+» symbol in the table), drag
the table to see them. To do this, select the table by clicking its header and drag by one side.
To add the table field to the query, click the button on the left of the required field name.
As a result, corresponding field description appears in the bottom of
query window.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 13
Using «Add all fields» button, you can add all selected table fields to
the query.
To delete field from query, click one more time the corresponding button in the table structure. Attention: when you delete the field, all parameter values are reset.
Also you can select the table and click «Delete field» button thereby
you delete selected table fields from the query.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 14
Each query field has several parameters, which characterize field
behavior in the query.
Alias* – field name to be displayed in the result header. (Alias must be unique!)
Table name – table where the field is; Field name – field name in the table; Sorted* – sort the result of query execution for this field; Filtered* – field filtering (for more detailed description refer to “Field
filtering” section) Link – link with other fields (link – filtering by the following characteristic:
current field is equal to linked field) Visible* – if the field is visible, then these field data will be visible Function* - function (for more detailed information see «Functions» section) * – to modify these parameters, click the mouse
To link two fields in the different tables, click Ctrl and «drag» required
field to the field to be linked. You can link only one-type field in different tables.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 15
SET RELATIONSHIP By default the fields are linked as filter A.P1 = B.P2. To modify this
parameter, double click the arrow representing the link. As a result, “Set Relationship” window appears.
The available option is “Show unmatched rows”. The default option is “Suppress mismatch”. If you change this to “B not found in A”, then the query results will be the lines where value from A.P1, which doesn’t have match B.P2, will have the opposite null value (NULL).
Click “OK”. As a result, above the arrow representing the link appears the symbol showing its type.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 16
FIELD FILTERING To filter the field, click Filtrated button at the required field.
The Fields filter window appears for this field.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 17
To add a new filter, type it to text field at the bottom and click «Add»
button
The filtering condition can be selected in the list; to do this, you should click the button with corresponding condition. If you don’t want to type another field name manually, click «Add field» button and select it in the list.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 18
You can delete the existing filter, select it in the list and click «Delete» button.
You can modify the existing filter, double click it in the list and it appears in the text field, change it and click «Modify» button to validate.
You can select another field for filtering in the list without closing this window.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 19
Click «Functions» button to select the data processing function. See the following section for more detailed function description.
To complete filter adjustment, click «Оk»
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 20
FUNCTIONS To use SQL-function, click Functions button at required field (the
functions can be used only for visible fields).
Or use it in the filtering condition.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 21
As a result, Function window appears.
In the right part - select function type, and in the left - select the function in the list (to do this double click its name).
In the middle you can see the description. To apply the modifications, click «Оk»
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 22
QUERY EXECUTION After you build the query, click «Generate query» button on the
toolbar.
The program switches to «Result» zone and build SQL-query text using the scheme.
After SQL-query is built, you can execute it. To do this, click “Execute query” button on the toolbar.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 23
The data appear in the table.
If the selected data quantity is too large and DBMS supports asynchronous operation mode, then in some time the query stop window appears.
If you click “Query interrupt” button, then the data receipt will stop.
Otherwise this window disappears from the screen after all data are received.
To show the line number (and evaluate its complexity) click “count
rows” button.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 24
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 24
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 25
After in the query data zone, one cell containing the number of lines
appears. This method doesn’t require the data transfer and executes faster than complete query.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 26
DISTINCT ROWS To distinct the rows from the result, in «Query» menu, select «Distinct
rows» and generate query again.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 27
SAVING QUERY AND RESULT To save the query scheme, click “Save” button.
To save generated SQL-text of the query, click “Export query text”
To save the data obtained as the query result, click “Export query
result” button
In all cases, “Save to file” window will open where you have to select
required file path and name.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 28
FILE SELECTION DIALOG BOX When you click «Save», «Save as», «Export query result», «Export
query text», Save to file window appears.
In «Files filter:» field the directory and file types to be displayed are
indicated. You can select file name from the existing files or type a new name in
the field below «Select file name:» line. To create the file with a new name, make sure that path for new file is
typed correctly.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 29
CREATING VIEWS FOR PETROVISION After the query text is generated, you can create view for PetroVision
program. To do this, enter «Query» menu and select «Create view»
If the query is built correctly, “Create View” window appears
When you fill the fields, take in consideration that the view name
(VIEW_NAME) should be uppercase with scheme name. The following fields are mandatory: VIEW_NAME, scheme, password, MAP_FEAT_NAME, FRST_COL_NAME.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 30
PICK types selection
You can select standard PICK type in the list Also you can load PICK types from the file, edit them and create new
PICK types. To do this, click “PICK types editor”
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 31
“PICK types editor” window opens
Select PICK types file to work with. To do this click “…” button
After that PICK types loaded from the selected file appear in PICK
types list.
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 32
When PICK type is selected, you can add, modify or delete its
attributes.
Warning: you cannot edit standard PICK types After PICK types are edited, click “Save” button to save the
modifications in the current file. Click «Close» button. To load additional PICK types when you create the view, click «Load
PICK types…» and select file. After this loaded PICK types appear in the list.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 33
When PICK type is selected, it is necessary to set the attributes to
PICK fields, to do this click
“PICK columns…” button. “PICK columns” window opens
Selecting in the list, set the attributes to PICK columns After all PICK attributes are set, click «Оk» to complete. PICK attributes must not repeat!
Create view After all necessary fields are set, click «Preview» button, to display the
query for view creation
User Guide QueryBuilder _____________________________________________________________________________________
© Geoleader 34
If the fields are filled correctly, “View SQL-script” window opens.
You can create the view, click «Create view», or save the query to file
– «Save» button. When you click «Create view», the script is executed with the progress
display.
QueryBuilder User Guide _____________________________________________________________________________________
© Geoleader 35
MULTIWINDOW USER INTERFACE QueryBuilder has MDI-interface; several queries can be edited
simultaneously in one run application. Click “Create” button to open a new window, it is possible to work in
the previous window because it isn’t closed. Access that window through the menu.
Besides, there are menu options to put the windows in order.