Home

Version 3.1 User Manual

1. Table 8 Relation Operators Examples for the use of the operators listed in Table 8 are given in Section 6 4 2 6 4 Extended Relation Algebra 6 4 1 Extended Relation Operators Beyond the operators provided by the relational algebra module a set of more sophisticated opera tors for relations is contained in the Extended Relation Algebra This module adds no new types but operators for modification extension sorting and grouping of relations instead The operator table is structured similarly to the table above an tn x bl x bj x Operator Signature Syntax Semantics extract stream tuple a1 t1 BE AA Returns the value of a specified aa ee ai attribute of the first tuple in the gt input stream extend stream tuple al t1 Extends each input tuple by new Tuple gt ul Tuple gt uj gt stream tuple al tl an tn Blzul bj u3 attributes The second argument is a list of pairs each pair con sists ofa name for a new attribute and an expression to compute the value of that attribute Result tuples contain the original attributes and the new attributes Refer to example 2 in Table 10 Table 9 Extended Relation Operators loopjoin stream Tuplel x Tuplel gt stream Tuple2 gt stream Tuple3 Join operator performing a nested loop join Each tuple of the outer stream is passed as an argument to the second argument f
2. the operator parentheses have to be put as shown Operator Signature Syntax Semantics tgs Seip E int x int gt int A addition subtraction multipli int x real gt real cation real x int gt real real x real gt real int real x int real gt real _ division div mod int x int gt int E 2 integer division and modulo operation lt lt int real x int real gt bool _ _ comparison operators gt gt string x string gt bool starts string x string gt bool E HEY TRUE if arg begins with arg contains string x string gt bool gt A TRUE if arg contains arg not bool gt bool _ logical not and or bool x bool gt bool A logical and Table 6 Standard Operators 6 2 3 Query Examples Standard Algebra Notice that queries can only be processed after a database was opened by the user In the query command query lt value expression gt value expression is a term over the active algebra s Some examples for queries using the standard algebra are given in Table 7 Note that no operator takes priority over another operator Therefore a multiplication is not computed before an addition as the examples 3 and 4 show Parentheses must be used in this case Text Syntax Nested List Syntax uery 5 query 5 uery 5 0 7 query 5 0 7 uery 3 4 9 query 3 4 9 quer
3. postfixbrackets prefix 1 postfix 1 infix 2 secondoOp windowclippingin secondoOp windowclippingout secondoOp windowintersects secondoOp windowintersectsS secondoOp within secondoOp within2 secondoOp whiledo secondoOp writeFile secondoOp zero End of file opsyntax pl E 60 prefix 2 prefix 2 postfixbrackets postfixbrackets postfixbrackets postfixbrackets FEN 2 special 1 TODO prefix 2 prefix 0 End of file opsyntax pl special syntax Be B Grammar ofthe Query Language We use the following notational conventions Words written in normal font are grammar symbols non terminals words in bold face are terminal symbols The symbols gt and are meta symbols denoting derivation in the grammar and separation of alternatives Other characters like ce 99 cc or are also terminals The notation x 1ist refers to a PROLOG list with elements of type x as mentioned already the square brackets can be omitted if the list has just one element The notation x expr refers to an expression built from elements of type x constants and operations available on x values Hence attr expr is an expression involving attributes denoted in one of the two forms attrname or var attrname Similarly a predicate pred is a boolean expression over attributes X constant denotes a SECON
4. Displays all names of type constructors together with their specification and an example in a formatted mode on the screen 1 The information can also queried using the system relation SEC2TYPEINFO see Section 3 6 e list operators Nearly the same as the command above but information about operations is presented instead e list algebras Displays a list containing all names of active algebra modules e list algebra lt identifier gt Displays type constructors and operators of the specified algebra e list databases Displays a list of names for all known databases e list types Displays a list of type names defined in the currently opened database e list objects Displays a list of objects present in the currently opened database 3 2 Databases Database commands are used to manage entire databases e create database lt identifier gt Creates anew database A database name may have only up to 15 characters and no distinc tion between uppercase and lowercase letters is made e delete database lt identifier gt Destroys the database lt identifier gt e open database lt identifier gt Opens the database lt identifier gt e close database Closes the currently open database The state diagram in Figure 3 shows how database commands are related to the two states OPEN and CLOSED of a database 3 3 Import and Export An entire database can be exported into an ASCII
5. In queries given to the optimizer one uses atomic operators in predicates and expressions in the select clause like lt gt lt starts contains div mod In this section we explain how new operators of this kind can be made available in the optimizer For using operators in queries there are two conditions 1 We must be able to write the operator in PROLOG 2 The optimizer must know how to translate the operator application to SECONDO syntax PROLOG Syntax Any operator can be written in PROLOG in prefix syntax For example length X theDate 2004 5 9 These are just standard terms in PROLOG If we want to write a binary operator in infix nota tion either this operator is defined already in PROLOG This is the case for standard operators BU es like lt etc Otherwise one can explicitly define it in the file opsyntax p1 in directory Optimizer For example in the file we find definitions 800 xfx inside 800 xfx intersects op 800 xfx adjacent 800 xfx or 800 fx not Here inside intersects adjacent and or are defined to be binary infix operators and not is defined to be a unary prefix operator New operators can be made available in the same way SECONDO Syntax Translation to SECONDO is controlled firstly by a few defaults depending on the number of argu 2 ments one argument translated to prefix notation op arg e two arguments translated t
6. Version 3 1 User Manual 8 3 2012 Ralf Hartmut Giiting Dirk Ansorge Thomas Behr Christian D ntgen Simone Jandt Markus Spiekermann W FernUniversit t in Hagen Faculty for Mathematics and Computer Sience Database Systems for New Applications 58084 Hagen Germany 1 This work was partially supported by a grant Gu 293 8 2 from the Deutsche Forschungsgemeinschaft DFG project Datenbanken f r bewegte Objecte Databases fro Moving Objects Table of Contents 1 Introduction and Overview 2 te dee ea m tee 1 2 Command Syntax A A 4 Del OVERVIEW ia A ak AAA eo eee AR 4 2 2 Nested LIS MAR atras 4 a LEVEL o O a ee aaah es Se Biter aac uate Ghana a Aes 3 3 Secondo COMMANGS an es 9 3 1 QUES Be ee Eee 9 3 2 Databases sa sehe 10 3 3 Import and EXPO LAA E A ds A Ba 10 374 Database States He a Mos Bin ahs ad ar 11 3 3 Basic Commands dota a nina a ne I IA 12 3 6 Querying System Tables sur esse rodeos 13 De SEATS ACTING a es a dla ds y a e sos N kM 14 3 8 Hints to avoid Name Conflicts Haar ale aa 14 4 Configuration Issues 4 2 2 A reed 15 4 1 Algebra Module Configuration u ea aa eed 15 4 2 Startup Confieutation e Ei Noir 15 3 User Interfaces osito ae er een 16 A eisen Raab ew aw eek cas 16 5 2 Single Threaded User Interfaces 2228 ace wets da oh edel 16 5 3 Multi User Operation ae ee ES EA a E 17 6 Algebra Modules ya Mi u Kent 32 Rs ee ee een ae 32 6 2 Standard Algebra ge za ss a a a as Bi 32 6 3 Re
7. gt Renames an object lt new name gt gui onlyViewer Hides the command panel and the object manager To show the hidden components use the viewers entry in the menubar gui executeFile i lt filename gt Batch processing of the file If i is set then file processing is continued even if an error occured gui status Prints out information about the connection to SECONDO as well as the name of currently open database gui set Can be used for changing the values of some Javagui settings The complete list of the vari ables can be obtained by the he1p menu entry The effect of the variables is described in the configu ration file of Javagui Table 5 Gui Commands Each non empty query result requested in the command panel is sent to the object manager and shown in a viewer according to the priority settings If no viewer is found which is capable to dis play the requested object a message is shown to inform the user about this If the standard Viewer is loaded this case will never occur If the optimizer is enabled queries and updates in SQL syntax are possible All queries beginning with select or sql are send to the OptimizerServer to get a query plan Embedded SQL queries can be used for a postprocessing of the result of such a query An example is query select Sname from staedte where Bev gt 500000 feed head 3 consume Each select clause in brackets is optimized separately Note that the r
8. 800 xf getPictureDate 800 xfx present 800 xf rect2region 800 xf relcount 800 xf relcount2 800 xfx satisfies 800 xfx simpleequals 800 xfx starts 800 xfx touchpoints 800 xfx union p p p p p p p p p p p p p p p p p p p p p p p p p p p p p p p p p p 800 xfx when oooo oo 0 000 00 00 00 0 0 002 00 000 0 000000 0 0 Section Start opSyntaxPreference 3 e Section End opSyntaxPreference 3 e o Je 2 Executable Language Operator Syntax secondoOp 0p Syntax NoArgs Op is a Secondo operator written in Syntax with NoArgs arguments Quick Reference OperatorSyntax Use OperatorType NoArgs prefix 0 prefix 1 ur prefix 2 i r BER prefix n postfix 1 infix 2 _ La a Pup ps al Pe ar any using list any using funlist all others Pil dynamic secondo0p 3 secondoOp secondoOp secondoOp lt secondoOp abs secondoOp addcounter secondoOp addid secondoOp addtupleid secondoOp aggregate secondoOp aggregateB secondoOp aggregates secondoOp adjacent secondoOp always secondoOp and secondoOp approximate secondoOp arccos secondoOp arcsin secondoOp arctan secondoOp arctan2 secondoOp at secondoOp atinstant secondoOp atperio
9. Here in the description of signatures type constructors are denoted in lower case whereas words starting with a capital denote type variables Note that type variables occurring several times in a signature must be instantiated with the same type Operator Signature Syntax Semantics feed rel Tuple gt stream Tuple Produces a stream of tuples from a relation consume stream Tuple gt rel Tuple _ Produces a relation from a stream of tuples filter stream Tuple x Tuple gt bool _ 1 Lets pass those input tuples for mean which the parameter function evaluates to TRUE Table 8 Relation Operators 39 attr tuple al tl an tn x ai _ _ retrieves an attribute value from gt ti a tuple project stream Tuplel x attrname relational projection operator on gt stream Tuple2 streams no duplicate removal product stream Tupl x stream Tup2 a relational Cartesian product gt stream Tup3 operator on streams count stream Tuple gt int _ Counts the number of tuples in a rel Tuple gt int stream or a relation rename stream Tuplel x id E Changes only the type not the gt stream Tuple2 value of a stream by appending the characters supplied in arg to Cla syntax each attribute name The first opera character of arg must be a letter med name Used to avoid name conflicts no oe nal e g in joins
10. The format of the inputfile is described in the file example test which is located in the bin directory as well 5 3 Multi User Operation 5 3 1 SecondoMonitor and SecondoListener Before the client server user interfaces can be used the database server process SecondoLis tener waiting for client requests must be started The host name and the port address can be changed in the file SecondoConfig ini Start the SecondoListener by typing SecondoMoni tor At the prompt startup should be entered By using the s option with the SecondoMonitor command the SecondoListener is started automatically without the additional startup com mand After SecondoListener is started it waits for requests from clients HELP shows a list of additional commands 5 3 2 OptimizerServer If one intends to use the optimizer within Javagui also an optimizer server has to be started Because this server acts as a client for SECONDO the SecondoListener has to be started before executing the optimizer server To start the optimizer server navigate into the Optimizer direc Se IR tory of SECONDO and enter StartOptServer Port Without any argument the default port 1235 is used Ensure to use the same port in the optimizer settings of Javagui The available commands of the optimizer server are Command Description help Prints out the available commands quit Quits the server clients Prints out the number of connected clients trace on
11. open database opt Res This is executed some SECONDO messages appear and then the PROLOG interpreter shows the result of binding variable Res Res This is the empty list that SECONDO returns on executing successfully such a command converted to a PROLOG list As usual with a PROLOG interpreter we can type lt return gt to accept the first solution or lt return gt to see more solutions if any exist After typing lt return gt the inter preter responds Yes 2 Let us try another command 2 secondo query Staedte feed filter Bev gt 500000 head 3 consume R R First _ Here at the end of the first line we typed lt return gt the interpreter then put at the begin ning of the next line The PROLOG goal is complete only with the final symbol only then interpretation is started Here as a result we get after some SECONDO messages the result of the query shown in variable R and the first element in variable First This illustrates that of course we can process SECONDO results further in the PROLOG environment By the way when you later want to quit the running SecondoPL program just type at the prompt either halt or 2 Guat The first is the standard PROLOG termination the second has been introduced to be consistent with other SECONDO interfaces In the sequel we omit the PROLOG prompt in the examples There is also a version of the secondo predicate that has only one
12. var attrname attr list gt attr attr attr list attrname gt id rel gt relname relname as var rel clause gt rel rel list rel list gt rel rel rel list relname gt id var gt id where claus gt where pred list where pred amp pred gt attr boolexpr pred list gt pred pred pred list We use the following notational conventions Words written in normal font are grammar symbols non terminals words in bold face are terminal symbols The symbols gt and are meta symbols denoting derivation in the grammar and separation of alternatives e denotes the empty 6699 cc word Other characters like or are also terminals id is any valid SECONDO identifier spelled in lower case letters The notation x 1ist refers to a non empty PROLOG list with elements of type x as mentioned already the square brackets can be omitted if the list has just one element The notation x expr refers to an expression built from elements of type x constants and opera tions available on x values Hence attr expr is an expression involving attributes denoted in one of the two forms attrname or var attrname Similarly a predicate pred is a boolean expression over attributes attr boolexpr X constant denotes a SECONDO constant of type x Finally e denotes the empty alternative Hence the where clause or the distinct keyword are optional From the grammar one can see that
13. x y So the expression rel tuple Name string Pop int 1s transformed into the nested list form shown in Section 2 2 The relation constant can now be written as const rel tuple Name string Pop int value New York 7322000 Paris 2175000 Hagen 212000 2 3 4 Value Expressions Value expressions are terms consisting of operator applications to database objects or constants For each operator a specific syntax can be defined The parser is built at compile time taking these specifications into account The syntax for an operator can be looked up in a running system by one ofthe commands list operators list algebra lt algebra name gt These commands provide further information such as the meaning of the operator For example the entry appearing on list operators for the operator year of is Name year of Signature instant gt int Syntax year of _ Meaning return the year of this instant Example query year of Tl This specifies prefix syntax for the operator year_of Be aware that what appears in such a listing is a comment written by the algebra implementor which occasionally may be wrong More details on the specification of an operator can be found in the SECONDO Programmer s Guide Parameter Functions The following operator demonstrates another general concept for query formulation anonymous function definition Its main purpose is to define predicate functions for operators like filter taki
14. KeyattrName gt is the name itentifier of the attribute that is used to generate the keys stored within the index lt LogicalIndexTypeCode gt is an alphanumerical code used to describe the logical index type While the base type of any index rtee rtree3 btree xtree hash etc can be looked up in the sys tem catalog the meaning of the index is coded using this tag E g an rtree index can be described as a temporal index built on single units of moving objects or on the objects complete movement here This informatioon is used within optimization rules and required to construct small sample objects used to determine selectivities and other parameters used during optimization If this tag is se Bf left out the logical index type is assumed to match the standard index type for the key attribute Logical index types are registered in file database pl using facts logicalIndexType 8 The names lt RelName gt and lt KeyattrName gt must be spelled as in the SECONDO kernel Hence a ordi nary B tree index on attribute Bev of relation Staedte must be called Staedte Bev btree to be recognized by the optimizer Such an index can be created by the command let staedte Bev_btr Staedte createbtree Bev Inquiries on the Optimizer s Knowledge Base The optimizer provides several commands for inquiries on its knowledge base showStoredRels lists all known relations and their attributes e showStoredAttrSizes lists types a
15. Tuple attr_i x dir i gt stream Tuple x Operator for sorting a stream lex icographically by one or more attributes For each attribute the direction of sorting can be specified as either ascending asc or descending desc If neither asc nor desc is spec ified that attribute is sorted in ascending order Sort rdup stream Tuple stream 1 Puple gt stream Tuple gt stream Tuple Sorts the input tuple stream lexi cographically ascending by all attributes Removes duplicates from a totally ordered input stream min max sum stream 1 gt int stream T gt real Tuple up x x intattrname _ gt realattrname Returns the minimum maxi mum or sum value of the speci fied column The second argument must be the name of an integer or real attribute avg stream Tuple gt real x numattrname Returns the average value of the specified column The second argument must be the name of an integer or real attribute head stream 1 stream 1 Tuple Tuple x int gt Returns the first n elements of the input stream Table 9 Extended Relation Operators 38 6 4 2 Query Examples Relation Algebra and Extended Relation Algebra To make it easier to understand how to use the operations of the relational algebra a small database is available for this purpose It is
16. and that is the symbol The same mechanism is available for opera tors with parameter functions taking two arguments e g for implementing join operators refer ring to the tuple types of the first and the second argument separately i e two input streams in that case the symbol can be used to refer to the second argument Instead of the symbol one can also write tuple or group as this makes sense for many operators it is translated by the 66 99 parser in the same way as the symbol Finally attribute access is very frequently needed therefore notations lt attrname gt lt attrname gt are provided equivalent to the expressions 1 How a stream of tuples is created from a relation will be the subject of the subsequent chapter attr lt attrname gt attr lt attrname gt So the use of the attr operator has been hard coded into the parser Finally we can write the application of filter as filter No gt 5 3 SECONDO Commands There is a fixed set of commands implemented at the SECONDO application programming inter face These commands can be called from one of the user interfaces described in Section 5 An overview is given in Table 1 Basic Commands Inquiries type lt identifier gt lt type expression gt list type constructors delete type lt identifier gt list operators create lt identifier gt lt type expression gt list algebras update lt ide
17. but all effects of commands executed within the loop remain valid The result of the command is a list resultse quence lt result gt lt resul t gt with each result of the executed command lt command gt lt command gt 15 lt command gt lt command gt je This construct is the sequence command It consists of an arbitrary number of commands separated by the pipe character enclosed in round paranthesis The commands are executed from left to right The result of the command is a list resultsequenc lt result gt lt resul E with the results of the executed commands The version using single braces executes all commands independently on errors occured during a single command The success of the sequences is the success of the last command The double braces version will stop the execution of the sequence immediately when a command fails The available commands allow for complete programs being written as command scripts Using operators from the FTextAlgebra and the ImExAlgebra it is possible to write scripts for conve nient data import or export Here some example commands type myrel rel tuple Name string Age int create x int update x 5 let place Hagen let rel2 const rel tuple Name string Age int value Peter 17 Klaus 31 derive rel2 Age rel2 createbtree Age query x 7 5 query rel2 feed filter Age gt 20 proj
18. called testqueries and can be created and loaded as follows from the secondo bin directory create database testqueries restore database testqueries from testqueries Now the database is loaded It provides among others the following simple relations tenTest rel tuple No int twentyTest rel tuple No int EmployeeTest rel tuple EName string EmpNr int DeptNr int DeptTest rel tuple Leader string DeptNr int StaedteTest rel tuple SName string Bev int PLZ int Vorwahl string Kennzeichen string A first query example in text syntax is the following query tenTest feed filter No gt 5 consume query is the keyword to start a query Next tenTest selects the relation used in the query feed and consume are operators to produce a stream of tuples from a relation and produce a relation from a stream of tuples respectively The filter operation has a parameter function no gt 5 and removes all tuples from the stream which evaluate to FALSE As we can see the query in text syntax can be read from the beginning to the end first the rela tion is selected and a tuple stream is generated For every single tuple the filter operation is evaluated After that the remaining tuples are collected and a new relation is built As a second example we take query tenTest feed twentyTest feed A product project No A sort rdup count Now we use two different relations tenTest and
19. chosen in the Settings menu If the manual selection is choosen for each object or for each graphical attribute of a rela tion a selection window pops up Auto selection creates for each graphical object a new random category If the selection by name is used two cases are distinguished First if the name of the object attribute is equal to the name of a category this category is chosen automatically Other wise the user is asked for a category Query Representation In this window the user can make settings for displaying a query result with graphical content This can be a single graphical object or a relation with one or more graphical attributes At the top Set Representation of GraphObjects Attribute p view Category Deraunvo Y Label Text Labelattribute Y Label Offset X Y o le Reference dependent Rendering Reference attribute Tuple No Y Rendering Type white gt red r manual link OK Cancel Figure 7 Query Representation the user can choose an existing category for all graphical objects of this query with the same attribute name The button labeled with invokes the category editor to create or change categories A graphical object may have a label The label content can be entered as Label Text If the object is part of a relation the value of another attribute can be used as label This fe
20. file and loaded from such a file Similarly a single object within a database can be saved to a file or restored from a file A database file is a nested list with four elements of the following structure DATABASE lt identifier gt TYPES lt a sequence of types gt 2 The information can also queried using the system relation SEC2OPERATORINFO see Section 3 6 3 Currently user defined types cannot be used in any meaningful way ve Pil OBJECTS lt a sequence of objects gt Each of the mentioned sequences may be an empty list Each type is a list of the form TYPE lt identifier gt lt type expression gt and each object is a list of length five with the following structure OBJECT lt identifier gt lt type identifier gt lt type expression gt lt value expression gt An object does not need to have a named type In that case the third element is an empty list A file storing a single object contains only one object list save database to lt file gt Write the entire contents of the currently open database in nested list format into the file lt file gt Ifthe file exists it will be overwritten otherwise it will be created e restore database lt identifier gt from lt file gt Read the contents of the file lt file gt into the database lt identifier gt Previous contents of the database are lost If the database is not yet present it will be created e sav
21. in a viewer The viewer is determined by the priority settings gui showAll Shows all objects listed in the object manager in the current viewer whose types are supported by this viewer gui hideObject lt ObjectName gt Removes the object with the specified name from the current viewer hideAll Removes all objects from the current viewer removeObject lt ObjectName gt Removes the object with the given name from the object manager and from all viewers clearObjectList Removes all objects from the object manager saveObject lt ObjectName gt Opens a file dialog to save the object with the given object name loadObject Opens a file dialog to load an object setObjectDirectory lt directory gt Sets the object directory This directory is initially shown when a load or save command is executed gui loadObjectFrom lt Filename gt Loads the object with the specified filename The file must be located in the objectdirectory gui storeObject lt ObjectName gt Stores an object into the currently open database The object name must not contain spaces gui connect Connects Javagui to SECONDO gui disconnect Disconnects Javagui from SECONDO gui serverSettings Opens the server setting dialog to change the default settings for host name and port Table 5 Gui Commands Command Description gui renameObject lt old name gt
22. relation p1z Here duplicate removal was done at the executable level However it can also be done directly by saying let orte3 select distinct ort from orte plz as p where ort p ort orderby ort Just Optimizing For experimenting with the optimizer it is useful to optimize queries without executing them This is provided by the optimize predicate optimize Term This returns the query plan and the expected cost 50 8 5 The Optimizer s Knowledge of Databases The optimizer and the SECONDO kernel are only loosely coupled In particular one can use the kernel independently create and delete databases and objects within databases out of control of the optimizer The optimizer maintains knowledge about the existing database contents within a number of dynamic predicates while the optimizer is running and in files between sessions It obtains such knowledge from the SECONDO kernel by sending commands or queries to it for example list objects Currently there are the following such predicates and corresponding files e storedRels relations and their attributes e storedSpells spellings of relation and attribute names e storedIndexes for which attributes do and do not exist indexes e storedCards cardinalities of relations e storedTupleSizes average tuple sizes in bytes of relations e storedSels selectivities of selection and join predicates e storedPETs predicate evaluation times for select
23. secondo databases is used When you plan to restore big databases you should switch off the usage of transactions since otherwise mega or giga bytes of log files Berkeley DB does physical logging after each write operation on a page are written to disk This can be done by uncommenting the line RTFlags SMI NoTransactions If you have already produced gigabytes of log files and want to get rid of them call the script rmlogs in the secondo bin directory This will delete all log files that are not needed for recovery any more 5 User Interfaces 5 1 Overview SECONDO comes with five different user interfaces SecondoTTYBDB SecondoTTYCS SecondoPL SecondoPLCS and Javagui For testing a further programm named TestRunner is available The shell based interfaces without optimizer support can be found in the bin directory All programs related to the optimizer are in the Optimizer directory Javagui is located in the Javagui direc tory SecondoTTYBDB is a simple single user textual interface implemented in C It is directly linked with the system frame It is mainly used for debugging and testing the system without relying on client server communication SecondoPL is the single user version of the optimizer SecondoT TYCS SecondoPLCS and Javagui are multi user client server interfaces They exchange mes sages with the system frame running as a server process via TCP IP Provided that the database server process has been st
24. selected object can be moved to another layer using the Object menu Here the user also can change the display settings for a single selected object The menu Settings gt Projections offers the possibility to enable one of a set of projections This is helpful if data containing geographical coordinates longitude latitude should be dis played The usual view of such data is obtained using the Mercator or the Gauss Krueger projec tion Sessions A session is a snapshot of the viewer s state It contains all objects and the display settings You can save load or start an empty session from the File menu Categories A category contains information how an object is to be displayed Such information is color or texture of the interior color and thickness of the borderline or size and shape of a point Catego Category Editor aussen E ax Point Forms 8 as Circle as Rectangle Size 16 0 OubLine Selection wiatn 1 0 Cotorf can stie rouna w Type y join style round gt Fill Style 0 0 Transparency in Texture en Icon O resize soa M Gradient No Filling Figure 6 Category Editor MM a ries can be loaded and saved via the File menu To edit an exististing category the category edi tor available via the Settings menu has to be invoked There are several possibilities to assign a category to an object or to attributes of a relation The method can be
25. signature defines operations over the types of the first signature An algebra module provides a collection of type constructors implementing a data structure for each ofthem A small set of support functions is needed to register a type constructor within an algebra Similarly the algebra module offers operators implementing support functions for them such as type mapping evaluation resolution of overloading etc The query processor evaluates queries by building an operator tree and then traversing it calling operator implementations from the algebras The framework allows algebra operations to have parameter functions and to handle streams More details can be found in DG00 The SECONDO kernel manages databases A database is a set of SECONDO objects A SECONDO object is a triple of the form name type value where type is a type term of the implemented algebras and value a value of this type Databases can be created deleted opened closed exported to and imported from files In files they are represented as nested lists like in LISP ina text format Currently there exist about thirty algebras implemented within SECONDO All algebras include appropriate operations Some examples are e StandardAlgebra Provides data types int real bool string e RelationAlgebra Relations with all operations needed to implement an SQL like relational language e BTreeAlgebra B Trees e RTreeAlgebra R Trees e SpatialAlgebra Spatial d
26. the testrunner lt filename gt arguments to the sgui script The file has to be in the same format as the TTY based TestRunner files The only differ ence is that in Javagui testfiles also queries in soL like Optimizer syntax are allowed The Viewers In this section some of the available viewers are presented The Standard Viewer The StandardViewer simply shows a SECONDO object as a string representing the nested list of this object In the text area only one object is displayed at the same time To show another object in this viewer it must be selected in the combobox at the top of this viewer You can remove the current or all object s in the extension of the menubar Make sure to load the StandardViewer by default to be able to display any SECONDO object The Relation Viewer This viewer displays SECONDO relations as a table The relation that shall be displayed can be selected in the combobox at the top of this viewer The viewer is not suitable for displaying rela tions with many attributes or relations containing large objects The Formatted Viewer and the Inquiry Viewer The Formatted Viewer shows the results of inquiries sent to SECONDO in a similar way as the SecondoTTy does The Inquiry Viewer shows objects of the same types as a colorized table In the default configuration file this viewer is not included It has to be loaded first by using by the gui addViewer command Another way to get this viewer is to insert it i
27. the chosen file name ends with obj then the object is saved as a SECONDO object load Opens a file dialog to load an object Supported file formats are nested list files shape files or dbase3 files In the current version restrictions for shape and dbf files exist store Stores the selected object into the currently open database rename Replaces the object manager with a dialog to rename the selected object Using Javagui for Test Purposes Javagui can be used to make tests including client server communication and the optimizer The different test modes provided by Javagui are presented in this section Remember to start the SecondoListener and the optimizer server if needed The Simple Test Mode The simple test mode is used if Javagui is started with the option s testmode lt file name gt Here all user interaction is switched off If the optional filename is given it will be exe cuted as if a script is executed via the file menu The Extended Test Mode The extended test mode is started using the testmode2 lt filename gt arguments The test file has to contain a nested list with the commands to execute as well as the expected results The for gt Dir mat is described in detail in the Gui Testmodes paf file which is part of a standard SECONDO dis tribution After executing this file Javagui will halt for 5 seconds and then exit The Testrunner Mode This test mode is enabled by adding
28. trace off Enables disables tracing If tracing is enabled the query the used database and the computed query plan are printed out Table 3 Commands of the optimizer server 5 3 3 SecondoTTYCS SecondoTTYCs is a client version of the single threaded SecondoTTYBDB described in Section 5 2 The main difference is that all user queries are transmitted to the database server via TCP IP which is capable to serve multiple clients simultaneously rather than calling system frame proce dures directly For the user of a SecondoTTYCS client appearance and functionality are pretty much the same as those of SecondoTTYBDB All commands work in the same way as with the sin gle threaded user interface To start SecondoTTYCS change to the bin directory and type SecondoTTYCS Remember that SecondoListener Section 5 3 1 must be running 5 3 4 SecondoPLCS The text based client version of the optimizer of SECONDO is SecondoPLCs It is started by enter ing SecondoPLCs in the Optimizer directory of SECONDO The functionality is the same as in SecondoPL Because the optimization is done within this client the optimizer server is not required for using SecondoPLcs However the SecondoListener Section 5 3 1 must be run ning 5 35 Javagui Javagui is a window oriented user interface implemented in Java Among its main features are e Javagui can be executed in any system in which a Java virtual machine Ver 1 5 0 or higher is install
29. twentyTest Since the attribute names of both relations are the same they both have the attribute no we have to use the rename operation which renames the attribute in the second relation by appending the attribute s name with the passed character string After that the product of both relations is computed An attribute name the new name No Aj is passed to the project operator after the projection to this attribute the tuples of the stream are sorted and duplicates are removed Finally the number of tuples is coun ted by the count operation The accurate result is 20 Some more example queries are given in Table 10 semicolon at the end of a line is inter Text Syntax Nested List Syntax query StaedteTest feed query avg Bev avg feed StaedteTest Bev query tenTest feed query extend Mod2 No mod 2 consume head 3 head consume extend feed tenTest mod2 fun tuplel TUPLE mod attr tuplel no 2 3 query StaedteTest feed query filter SName contains consume burg project project SName Kennzeichen filter consume feed StaedteTest fun tuplel TUPLE contains attr tuplel SName burg SName Kennzeichen query EmployeeTest feed query sortby DeptNr asc consume groupby DeptNr groupby Anz group feed count sortby consume feed EmployeeTest DeptNr asc Attention Avoid semicolons followed DeptNr by a linebreak within querie
30. without duplicate removal mergesec stream Tuple x stream Tuple u Intersection Both streams must gt stream Tupls be ordered lexicographically by all attributes achieved by apply ing a sort operator before mergediff stream Tuple x stream Tuple Difference on two ordered gt stream Tuple streams Table 9 Extended Relation Operators aggregate stream tuple al t1 tn x ti gt ti al x ti x ti gt ti x an Given an input stream aggre gates all values of a selected attribute ai of all tuples of that stream using the given aggrega tion function and an initial value See also aggregates in the oper ator list in the system and the example in Table 10 groupby stream Tuple al bl x bj x al ti ail rel Tuple rel Tuple gt stream tuple al tl bl ul x x gt UL cas gt uj bj uj Groups a stream by the attributes glven in the second argument The third argument is a list of pairs each pair consists ofa name for a new attribute and an expression to compute the value of that attribute from a subrela tion obtained by grouping Result tuples contain the grouping attributes and the new attributes See the example in Table 10 It is possible to group by no attribute second argument remains empty In that case the com plete input stream forms a single group sortby stream
31. 2 3 it implements a fixed set of notations for type expressions and it also implements for each operator of an active algebra a specific syntax defined by the algebra implementor 2 3 1 Commands Commands can be written without parentheses for example list type constructors query cities which is translated to list type constructors query cities 2 3 2 Constants Constants are written in text syntax in the form const lt type expression gt value lt value expression gt This is translated to the list lt type expression gt lt value expression gt which is the list form of constants explained above Of course simple constants for integers etc can be written directly For example const int value 5 or 5 const string value secondo or secondo const bool value TRUE Or TRUE const rectangle value 12 0 16 0 2 5 50 0 might be notations for constants 2 3 3 Type Expressions Type constructors can be written in prefix notation that is lt type constructor gt lt arg 1 gt lt arg_n gt This is translated into the nested list format lt type constructor gt lt arg 1 gt lt arg_n gt An example type expression is rel tuple Name string Pop int This example uses notations for lists and pairs which are translated by the parser as follows rerem ship iy lem n gt elem 1 elem n x y gt x y translated by the parser into elem 1 elem_n
32. DO constant of type x Finally se denotes the empty alternative For example the where clause is optional sql clause gt let objectname mquery let objectname mquery secondo rest query sql mquery sql mquery secondo rest query aggr gt groupattr groupattr as newname aggr2 aggr2 gt count distinct clause as newname aggrop ext attr expr as newname aggregate ext attr expr aggrfun datatype datatype constant as newname aggrop gt min max sum avg extract count aggr clause gt aggr aggr aggr list aggr fun gt union_new intersection_new any name fun of a binary SECONDO operator or function object with syntax fun T x T gt T which should be associative and commutative Infix operators must be inclosed in round paranthesis aggr list gt aggr aggr aggr list attr gt attrname var attrname rowid attr list gt attr attr attr list attrname gt id column gt newname datatype column list gt column column column list create query gt create table newname columns column list create index on relname columns index clause datatype gt int real bool string line points mpoint uregion any name of a SECONDO datatype delete query gt delete from rel clause where claus distinct clause gt all distinct drop query gt drop table relname drop index indexname drop index on relname index cla
33. ECONDO An Extensible DBMS Architecture and Prototype Fernuniversitat Hagen Informatik Report 313 2004 G 93 G ting R H Second Order Signature A Tool for Specifying Data Models Query Processing and Optimization In Proc ACM SIGMOD Conference Washington USA 1993 277 286
34. For example one can write assuming database opt is open sql select from staedte where bev gt 500000 Note that in this environment all relation names and attribute names are written in lower case let ters only Remember that words starting with a capital are variables in PROLOG therefore we cannot use such words The optimizer on its own gets information from the SECONDO kernel about the spellings of relation and attribute names and sends query plans to SECONDO with the correct spelling Some messages appear that tell you something about the inner workings of the optimizer Possi bly the optimizer sends by itself some small queries to SECONDO then it says Destination node 1 reached at iteration 1 Height of search tree for boundary is 0 The best plan is Staedte feed filter Bev gt 500000 0 20669 3 27586 consume Estimated Cost 310 64 After that appear evaluation messages and the result of the query If you are interested in under standing how the optimizer works please read the paper GBA 04 If you wish to understand the working of the optimizer in more detail you can also read the source code documentation that is say in the directory Optimizer pdview optimizer pl Almost all prolog sourcefiles having filename extension p1 from the Optimizer directory can be processed in this way In the following we describe the currently implemented query language in detail Whereas the syntax resembles SQL no a
35. Vorwahl string Kennzeichen string plz PLZ int Ort string ten No int thousand No int Furthermore there are the two indexes plz _Ort_btree and plz PLZ btree which index on the plz relation the attributes ort and PLZ respectively All relations are small except for plz which is a bit larger having 41267 tuples 8 2 Using SECONDO in a PROLOG environment In Section 5 it was already discussed how the optimizer can be called In this section we assume that the single user version SecondoPL is used the client server interface SecondoPLcs behaves similarly Hence switch to the directory Optimizer and call the optimizer by the command SecondoPL After some messages there appears a PROLOG prompt Le 1 There is also a further relation SEC_DERIVED OBJ used internally to restore indexes and other derived objects and some more system tables starting SEC2 System tables can be queried like any relation but they are not persistent and will get lost between two sessions AA When the optimizer is used for the first time after installing SECONDO some error messages appear these can safely be ignored The reason is that some files generated by the running opti mizer are not yet there We now have a PROLOG interpreter running which understands an additional predicate secondo Command Result xecute the Secondo command Command and get the result in Result So at the command line one can type 1 secondo
36. abase system the system frame from the data model dependent parts e Provide a formalism to describe the implemented data model in order to be able to provide clean interfaces between system frame and contents This formalism is second order signature explained below e Structure the implementation of a data model into a collection of algebra modules each pro viding specific data structures and operations SECONDO was intended originally as a platform for implementing and experimenting with new kinds of data models especially to support spatial spatio temporal and graph database models We now feel SECONDO has a clean architecture and it strikes a reasonable balance between sim plicity and sophistication In addition the central parts are well documented with a technique developed specifically for this system the so called PD system Since all the source code is accessible and to a large extent comprehensible for students we believe it is also an excellent tool for teaching database architecture and implementation concepts SECONDO uses BerkeleyDB as a storage manager runs on Windows Linux and Mac OS X plat forms and consists of three major components written in different languages e The SECONDO kernel implements specific data models is extensible by algebra modules and provides query processing over the implemented algebras It is implemented on top of BerkeleyDB and written in C e The optimizer provides as its c
37. ammar can be found in Appendix B 8 4 Further Ways of Querying The basic form of querying is using the sql predicate in prefix notation as explained in the previ ous section hence sql Term Hybrid Queries A second form of the sql predicate allows one to further process the result of a query by SEC ONDO operators sql Term SecondoQueryRest Here SecondoQueryRest contains a character string with SECONDO operators applicable to a stream of tuples returned by the optimized and evaluated Term For example sql select from orte where bevt gt 300 project Ort consume Note that in the second argument attribute names have to be spelled correctly as in writing exe cutable queries to the SECONDO kernel In this example the same effect could have been achieved by a pure SQL query but there are cases when this facility is useful Creating Objects The let command of SECONDO allows one to create SECONDO objects as the result of an execut able query There is a let predicate in the optimizer that allows one to do the same for the result of an optimized query There are two forms the second one corresponding to a hybrid query let ObjectName Term let ObjectName Term SecondoQueryRest For example let orte2 select ort from orte plz as p where ort p ort orderby ort rdup consume This query creates a relation orte2 with the names of places Orte that also occur in the postal code
38. argument secondo Command xecute the Secondo command Command and pretty print the result if any Hence we can say secondo query Staedte The result is printed in a similar format as in SecondoTTYBDB Or SecondoTTYCS 4 In addition a number of predicates are available that mimic some frequently used SECONDO com mands namely open create update let delete query They all take a character string as a single argument containing the rest of the command and are defined in PROLOG to be prefix operators hence we can write secondo close database open database opt create x int update x Staedte feed count let double fun n int 2 n query double x delete x In the remainder of this chapter we assume that the standard version of the optimizer is active To ensure this type setOption standard after starting SecondoPL This command will do some output If you type showOptions an overview of all available options is printed A marked checkbox in front of option standard indicates that it is active By now only option standard should be marked 8 3 An SQL like Query Language The optimizer implements a part of an SQL like language by a predicate sql to be written in pre fix notation and provides some operator definitions and priorities e g for select from where that allow us to write an SQL query directly as a PROLOG term
39. arted multiple user interface clients can access a SECONDO database concurrently 5 2 Single Threaded User Interfaces 5 2 1 SecondoTTYBDB SecondoTTYBDB Is a Straightforward interface implementation Both input and output are textual Since SecondoTTYBDB materializes in the shell window from which it has been started existence and usage of features like scrolling cut copy and paste etc depend on the shell and window man ager environment A command ends with a or an empty line Thus multi line commands are possible SecondoTTYBDB provides some special commands described in the following table Command Description 06 HELP Displays all user interface commands lt filename gt Starts batch processing of the specified file All lines are subse quently passed to the system just as if they were typed in manually by the user After the last command line was executed SecondoT TYBDB returns to the interactive mode lt filename gt Works similar as the lt filename gt command but stops the execu tion of the specified file immediately when a command fails DEBUG lt n gt Sets the debug mode See online help for further information q or quit Quits the session A final abort transaction is executed automat ically After that SecondoTTYBDB is terminated Table 2 Commands of SecondoTTYBDB ye IR es For the algebra modules SecondoTTYBDB is extended by support functions for
40. ata types point points line region e DateAlgebra A small algebra providing a date type e MidiAlgebra Providing a data type to represent the contents of Midi files including inter esting operations like searching for a particular sequence of keys As a conclusion of this introduction an example is shown in order to demonstrate how SECONDO can be used to formulate queries on spatial objects This example involves some objects in the open database 1 a relation Kreis with type schema rel tuple KName string Gebiet region containing the regions of 439 counties Kreise in Germany ii an object magdeburg of type region containing the geometry of county Magdeburg and iii an object kreis Gebiet of type rtree tuple KName string Gebiet region which is an R tree on the Gebiet attribute of relation Kreis The following query finds neighbour counties of magdeburg query kreis Gebiet Kreis windowintersects bbox magdeburg filter Gebiet adjacent magdeburg project KName consume The query uses the R tree index to find tuples for which the bounding box MBR of the Gebiet attribute overlaps with the bounding box of the magdeburg region The qualifying stream of tuples is filtered by the condition that the region of the tuple is adjacent to the region of magde burg Tuples are then projected on their KName attribute and the stream is collected into a result relation The following sections of
41. ature is available in the Labelattribute combobox In this case the user can also make graphical set tings for objects contained in the relation If Single Tuple is selected for each single tuple in the relation an own category can be chosen Another possibility is to choose the category depending on an attribute in the relation Thus the point size the line width or the color can be chosen to be dependent on the value of another attribute The possible values for these features are distributed in a linear way over the values of the selected attribute For a non linear distribution or for attri bute values which do not support this function a manual link between value and used category can be created Animating Temporal Objects If a spatial temporal object is loaded you can start the animation by clicking on the play button left of the time line The speed can be chosen in the Settings menu The speed can also be halved doubled by clicking on the lt lt speed gt gt buttons The other buttons are play play backwards go start go end and stop You can also use the time scrollbar to select a desired point in time Displaying Special Objects Some objects can be displayed in a separate window These objects are marked by a special color in the textual representation By double clicking on the object an additional window is opened and the selected object is displayed Figure 8 shows such a window for the text type Ss NS Anot
42. ause from rel clause where clause groupby clause orderby clause first clause aggr clause gt aggr2 aggr2 aggr list aggr2 gt count distinct clause as newname aggrop ext attr expr as newname arbitrary aggr as newname aggr gt groupattr groupattr as newname aggr2 aggr list gt aggr aggr aggr list aggrop gt min max sum avg extract count aggr fun gt union _new intersection_new o any name fun of a binary SECONDO operator or function object with syntax fun T x T gt T which should be associative and commutative Infix operators must be inclosed in round paranthesis arbitrary aggr gt aggregate ext attr expr aggrfun datatype datatype constant datatype gt int real bool string line points mpoint uregion o any name of a SECONDO datatype groupattr gt attr groupattr list gt groupattr groupattr groupattr list 48 groupby clause gt groupby groupattr list groupby groupattr For example one can say select ort min plz as minplz max plz as maxplz count as cntplz from plz where plz gt 40000 groupby ort orderby cntplz desc first 10 Entries in the select clause are either attributes used in the grouping or definitions of derived attri butes which are obtained by evaluating aggregate functions on the group Again one can order by such derived values An aggregate operator like sum cannot onl
43. ause from rel clause where clause orderby clause first clause select aggr clause from rel clause where clause groupby clause orderby clause first clause query query query list relname relname as var rel rel list rel rel rel list id attr attr expr as newname result result result list text result result list count distinct clause aggrop ext attr expr aggregate ext attr expr aggrfun datatype datatype constant text gt transform gt transform clause gt transform list gt update expression gt update query gt var gt value gt value list gt where clause gt E 63 any sequence of characters that completes the optimized query to a valid expression in SECONDO executable language attrname update expression transform transform list transform transform transform list o a fixed value or an operation calculating a value update relname set transform clause where clause id a PROLOG integer boolen or string value value value value list where pred list where pred amp C References DG00 Dieker S and R H G ting Plug and Play with Query Algebras SECONDO A Generic DBMS Development Environment Proc Int Database Engineering and Applications Symposium IDEAS Yokohama Japan 2000 380 392 GBA 04 G ting R H T Behr V T de Almeida Z Ding F Hoffmann and M Spiekermann S
44. cial display functions for the object s type it will be displayed as a nested list kill lt identifier gt Removes the object whose name is lt identifier gt from the database catalog without remo ving its datastructures Generally the delete command should be used to remove database objects but this command may be useful if delete would crash the database due to corrupted persistent data structures for this object 13 if lt value expr gt then lt command gt else lt command gt 2 gt endif This is the conditional command lt bool value expression gt is a value expression of type bool It is evaluated by the query processor This is only possible if a database is currently open If the result is bool TRUE command is executed If the result is bool FALSE nothing happens unless the optional else part is used in which case command is executed If the evaluation of the predicate does not return a defined bool value the complete command fails The result of the executed command is forwarded as the conditional command s reult while lt value expression gt do lt command gt endwhile This implements a loop construct As long as the evaluation of lt value expression gt returns bool TRUE evaluation is only possible if a database is currently open the command is executed If the expression value is bool FALSE the loop is terminated For any other result the loop command terminates with an error
45. configure the option settings to what we call the optimizer s standard behaviour Option debug will activate debugging code within the optimizer This will produce lots of debug messages and will slow down query processing When option autosave is selected the option settings will be saved during optimizer sessions When the next session is started the settings of the previous settings will be restored It is also possible to manually save the current option settings using saveOptions and to restore them using defaultOptions Option alloff means that all options are unselected other than standard that may activate some options 8 8 Getting Help on the Optimizer The optimizer also has a built in help function It can be accessed by typing helpMe After typing this command a list of all user level predicates is presented which has been abbrevi ated here Help is available on the following user level predicates Predicate Arity Meaning allCards 0 Compare estimated and actual cardinalities for the current POG cdb 0 Close current database closeDB 0 Close current database cmdHist2File Dump the command history to a file create Prepend create and send to DBMS kernel createIndex 3 Create an index of a specified type over a given attribute for a relation createSamples 3 Create but do not replace samples for a relation debugLevel Add a given level to the debug list defaultOptions 0 Choose the
46. ct statements rewriteCSE Extend with attributes for CSE values rewriteCSEall Extend with attributes for_ALL_ CSEs rewriteRemove Remove attributes as early as possible debug Execute debugging code Also use toggleDebug autosave Autosave option settings on halt noprogress Do not send predicate data for progress estimation subqueries Process subqueries subqueryUnnesting Apply unnesting algorithms to subqueries entropy Use entropy maximization together with an exploration query on a small sample database Type loadOptions to load the saved option configuration Type saveOptions to save current option configuration to disk Type defaultOptions to restore the default options Type setOption X to select option X Type delOption X to unselect option X Type showOptions to view this option list Type helpMe to get an overview on user level predicates Yes Some of these options have been mentioned before To select activate an option say setOption OptionName to deselect deactivate an option delOption OptionName where OptionName is a term listed by showOptions Several options will automatically deactivate incompatible options and or activate other options it depends on Some options are suboptions that change the behaviour of their super option There are four options with a special meaning standard debug autosave and alloff Option standard will
47. cts Currently the optimizer uses the underscore symbol to encode additional information about objects into their names for example an object named myrel_Attr will be considered to be an index for attribute Attr in relation myrel Therefore we recommend to use attribute names and relation names which are short enough since the name of an index object can only have 48 characters and to avoid usage of the underscore Another problem can be name conflicts between database objects and identifiers which may be parameters for operators To avoid such name conflicts we require attribute names to start with an upper case letter and recommend that object identifiers including relation names start with a lower case letter Type constructors and operators in the system should start with a lower case letter anyway Moreover as usual in programming languages the usage of object or attribute identifiers which are keywords used in commands or operator names or the boolean constants TRUE or FALSE will result in a parse error For example the command let myrell const rel tuple TRUE bool value FALSE returns parse error unexpected ZZBOOLEAN in line 1 4 Configuration Issues 4 1 Algebra Module Configuration As described in Section 1 a running SECONDO system consists of the kernel extended by several algebra modules These algebra modules can arbitrarily be included or excluded when compiling and linking the
48. default option setting delOption Unset a given option delete Prepend delete and send to DBMS kernel deleteHistory 0 Delete the query history relation SqlHistory from the current DB derive Prepend derive and send to DBMS kernel writePlanEdgesX 0 List executable and internal sub plans for all POG edges writeSizes 0 List estimated cardinalities and selectivities for the current POG Use helpMe Pred or helpMe Pred Arity for help on a certain predicate It is also possible to ask for help on a certain predicate say createIndex helpMe createIndex After typing this command you can read Help on predicate createIndex 3 Create an index of a specified type a or over a given attribute for a relation createlndex DCRel The relation the index is for DCAttr The key attribute LogicallndexType The logical index type This says that the predicate createIndex has arity three requires 3 arguments Calling this predicate will create an index The meaning of the three arguments is explained A in front of an argument means that the argument must be bound to a value A means that the parameter is an output parameter A variable used here will be bound during the evaluation of the predicate A gt gt means that this parameter may be bound e i acts as an input parameter or unbound i e acts as a output parameter For some predicates there are versions with
49. different arities e g helpMe We can get an inroduc tory overview on all variant 34 helpMe helpMe There are several arities for predicate helpMe Help is available on the following arities Predicate Arity Meaning helpMe 0 List available user level predicates helpMe 1 Show help on a given user level predicate helpMe 2 Show help on a user level predicate with a given arity Use helpMe Pred Arity for help on a certain arity of that predicate Yes To get information on the predicate with a given arity you can type e g 36 helpMe helpMe 2 Help on predicate helpMe 2 Show help on a user level predicate with a given arity helpMe PredicateName The predicate to get information about Arity Chooses amongh predicates with more than one arity Yes By this way it is possible to browse information on all important user level predicates Operator Syntax 10 Defining Operator Syntax File opsyntax pl 2 800 xfx lt 800 xfx 800 xfx adjacent 800 xfx always 800 xfx and 800 xfx at 800 xfx atinstant 800 xfx atperiods 800 xfx attached 800 xfx before 800 xf isgrayscale 800 xf isportrait 800 xf getHeight 800 xf leapyear 800 xf line2region 800 xfx mod 800 xfx never 800 fx not 800 xfx onborder 800 xfx overlaps 800 Xfx Or 800 xfx p_intersects 800 xfx passes
50. ds secondoOp attached secondoOp attr secondoOp attrsize secondoOp attr2text secondoOp avg secondoOp bbox secondoOp before secondoOp transpose secondoOp tuplesize secondoOp union secondoOp union new secondoOp updatebtree secondoOp updatebyid secondoOp updatehash secondoOp updatertree secondoOp updatesearch secondoOp updatedirectsave secondoOp updatesearchsave secondoOp use secondoOp use2 secondoOp var secondoOp vec secondoOp getWidth secondoOp when 59 postfixbrackets 1 postfixbrackets 1 postfixbrackets 1 postfix 2 postfixbrackets 2 postfixbrackets 2 postfixbrackets 2 postfix 3 postfixbrackets 3 postfixbrackets 3 postfixbrackets 3 special special special infix 2 infix Ja infix 2 prefix 1 postfixbrackets 1 postfix 1 postfix 1 NNN special 1 special syntax special 1 TODO special syntax special 1 TODO special syntax infix 2ye RETA 2 InfiXx 2 postfixbrackets 1 prefix 1 prefix 1 prefix 1 prefix 2 infix infix 2 infix infix prefix 2 postfixbrackets 1 prefix 1 postfixbrackets 1 prefix 1 IN TXS 2 prefix 1 postfix 1 infix p 2 4 prefix 2 postfixbrackets postfixbrackets postfixbrackets postfixbrackets postfixbrackets postfixbrackets postfixbrackets postfixbrackets postfixbrackets PHRF WWNHNN DNDN DY
51. e By choosing a new viewer the current viewer is replaced by the selected one Set priorities Opens a dialog to define priorities for the loaded viewers see Figure 5 Add Viewer Opens a file input dialog for adding a new viewer at runtime Show only viewer Hides the command panel and the object manager to have more space to display objects The menu entry is replaced by show al1 which displays all hidden components Table 4 Menubar of Javagui Setting Viewer Priorities There are different viewers which can display the same data type s To select one of these view ers priorities are used In the priority dialog you can define priorities of the loaded viewers depending on your personal preferences The viewer at the top has the highest priority To change the position of a viewer select it and use the up or down button Javagui ask the viewers about gt J a their display capabilities for a specific object and uses this information if depending from _ depending from object lv try to keep the current Viewer Th Hoese Viewer RelationsViewer Standard Fuzzy_Viewer up down ok Figure 5 The Priority Dialog object is selected The Viewer with the highest value is used to display an object If you have selected the box try to keep the current viewer the current viewer is only replaced by another one if the current viewer cannot display the object The Command Pane
52. e lt identifier gt to lt file gt Writes the object list for object lt identifier gt to file lt file gt The content of an existing file will be deleted e restore lt identifier gt from lt file gt Creates a new object with name lt identifier gt possibly replacing a previous definition Type and value of the object are read from file lt file gt In the commands above a lt file gt can be an identifier or a nested list text atom which can hold character sequences of arbitrary length and is recognized by the nested list parser by enclosing it with the tags lt text gt and lt text gt or in single quotes Moreover it is possible to use environment variables Examples save cities to cities obj save cities to S HOME cities obj save cities to lt text gt media usb memorystick cities obj lt text gt On a MS Windows system you have to use the backslash as directory separator e g save database to C msys mydb db 3 4 Database States Figure 3 shows how commands depend on and change the state of a database The commands referred to all have the keyword database in their syntax All commands accessing objects only work in an open database create open restore gt CLOSED OPEN q Save close delete Figure 3 Database Commands and States 3 5 Basic Commands These are the fundamental commands executed by SECONDO They provide creation and manipu lation of types and obj
53. e anima tion When objects having geographic coordinates longitude latitude are displayed it s also possible to use maps from Open Street Map or Google as a background For this an internet connection is required Although this kind of background works with many projections it s recommended to choose the Mercator or even better the OSM Mercator projection from the projection menu to avoid distortions of the maps After choosing TiledMap as background see Figure 9 a new frame appears see Figure 10 Here one of several predefined map backgrounds can be selected Because the Google servers limit access to theirs map tiles we recommend to use one of the Open Street Map backgrounds E secondo GUI Hoese iewer Projections Category manual O Category auto Category by name Isec gt oquery Hagen query Hagen successful lsee result in object list Sec gt query bbox Hagen Query representation query bbox Hagen successful Category editor lsee result in object list Sec gt query bbox Hagen query bbox Hagen successful see result in object list Sec gt Alt Minus Alt Plus Color Default Color Choose Image Import Image Capture All Image Capture Visible BIEICIEN E speed E Jove tboxtogen_t rect Select Bordersize 7 48333329895045 51 366666682 Figure 9 Choosing the TiledMap background You can also edi
54. ect Name consume delete type myrel delete rel2 open database opt query deleteObject twenty let twenty intstream 1 20 namedtransformstream No consume query twenty query deleteObject mycounter let mycounter 0 while mycounter lt 10 do update mycounter mycounter 1 query mycounter endwhile 3 6 Querying System Tables There are some system tables which provide internal information The most interesting are S EC2COMMANDS and SEC2OPERATORINFO The first one contains a command history and the second v E n contains descriptions of the operators Since they are relation objects they can by queried by the means of the relational algebra e g query SEC2OPERATORINFO feed filter Signature contains stream tuple consume displays all operators which process a stream of tuples 3 7 Transactions Each of the basic commands of SECONDO is encapsulated into its own transaction and committed automatically If you want to put several commands into one single transaction the following commands have to be used e begin transaction Starts a new transaction all commands until the next commit command are managed as one common unit of work commit transaction Commits a running transaction all changes to the database will be effective e abort transaction Aborts a running transaction all changes to the database will be revoked 3 8 Hints to avoid Name Confli
55. ects as well as querying within an open database type lt identifier gt lt typ xpression gt Creates a new type named lt identifier gt for the given type expression delete type lt identifier gt Deletes the user defined type named lt identifier gt create lt identifier gt lt type expression gt Creates an object called lt identifier gt of the type given by lt type expression gt The value is still undefined update lt identifier gt lt value expression gt Assigns the result value of the right hand side to the object lt identifier gt let lt identifier gt lt value expression gt Assign the result value right hand side to a new object called lt identifier gt The object is not allowed to exist yet it is created by this command and its type is defined as the one of the value expression The main advantage vs using create and update is that the type is determined automatically derive lt identifier gt lt value expression gt This is a variant of the let command which can be useful to construct objects which use other objects as input and have no external list representation e g indexes When restoring a database those objects are reconstructed automatically delete lt identifier gt Destroys the object whose name is lt identifier gt query lt value expression gt Evaluates the given value expression and returns the result object If the user interface pro vides no spe
56. ed e It provides a large set of viewers to display a lot of different types e g spatial data types e Data of different formats can be imported e Query results can be saved into a file e New viewers can be added e Javagui supports the SECONDO optimizer Secondo GUl Hoese Viewer Program Server Optimizer Command Help Viewers File Settings Object Creation Object k l show hide remove clear save load store rename list objects query Kreis query Flu See Object Manager Sec gt E gt EN E speed no tine 3 4296641148792912 48 970583992 AName A6 ANr 1 Averlauf line Autob AName A 656 ANr 2 AVerlaurf line Autob AName AB Hee jAVerlaur line Autob fee Current Viewer Progress Bar line Autob AName A 445 ANr 6 AVerlauf line Autob AName A 5 ANr 7 AVerlauf line Autob AName AS ANr 8 AVerlauf line Autob search go Figure 4 The four Parts of Javagui The Configuration File In the configuration file variables used by Javagui can be changed to non default values In a standard SECONDO installation it is not necessary to change this file Starting the Javagui The easiest way to start Javagui is to call the sgui script Remember to start the SecondoLis tener process before exec
57. efined as rel tuple Name string Pop int now the symbol cityrel is a valid type expression too Writing cityrel has exactly the same effect as writing its complete definition Value expressions are constants object names or terms of the query algebra defined by the cur rent collection of active algebra modules Constants in general are two element lists of the form lt type gt lt value gt For standard data types int real bool string just giving the value is sufficient 17 3 14159 TRUE Secondo Thus 5 cities 4 5 count head feed cities 4 or the constant relation rel tuple Name string Pop int New York 7322000 Paris 2175000 Hagen 212000 are valid value expressions provided an object with name cities and appropriate operators feed head and count exist Prefix notation is mandatory for specifying operator application in nested list syntax 2 3 User Level Syntax The user level syntax also called text syntax is more comfortable to use It is implemented by a tool called the SECONDO parser which just transforms textual commands into the nested list for mat in which they are then passed to execution This parser is not aware of the contents of a data base so any errors with respect to a database e g objects referred to do not exist are only dis covered at the next level when lists are processed However the parser knows the SECONDO com mands described in Section
58. er any relations and or indexes have been added or removed and update its knowledge base accordingly Hence this can be used after creating or destroying an index by third party without losing all the other information collected for relation Rel For example after deleting the index plz Ort _btree one should inform the optimizer by saying updateCatalog once updateDB DB To update the metadata on a distinct and closed database this predicate can be called It will retract all metadata on objects within that database When the database is opened for the next time the optimizer will need to recollect metadata Creating Sample Relations Manually As mentioned above the optimizer uses small sample relations to determine selectivities of selec tion or join predicates before actually optimizing the query These sample relations are normally created automatically with default sizes There is one sample relation called lt rel name gt sample s and another one called lt relname gt sample 3 to be used for selection and join predicates and with default sizes 2000 and 500 tuples respectively If a relation has less tuples than that the sample will be the full relation However in some cases this default value is not appropriate This is mainly the case when a rela tion has few tuples containing large objects Assuming there is a database germany providing a 53 relation County with 439 tuples each containing a region value with
59. esult of an optimized query is always a relation exceptions are count queries For this reason the feed operator is required in the example In contrast to an embedded select clause a single select clause adds the lead ing query automatically If the command starts with insert into delete from Or update lt identifier gt set also the OptimizerServer is used to get an executable plan for that command The received plan is sent to the SecondoServer for execution The keyword optimizer enables communication with the OptimizerServer at a lower level The text after this keyword is transfered to the OptimizerServer converted into a Prolog predicate and evaluated afterwards For example you can type optimizer current prolog flag version Version to get the currently used Prolog version The Object Manager This window manages all objects resulting from queries or file input operations The manager provides a set of buttons described below Button Description show Shows the selected object in the viewer depending on priority set tings hide Removes the selected object from the current viewer remove Removes the selected object from all viewers and from the object manager clear Removes all objects from all viewers and also from the object man ager save Opens a file dialog to save the selected object to a file If the selected object is a valid SECONDO object consisting of a nested list type value and
60. f a nested list consists of a left parenthesis followed by an arbitrary number of elements termi nated by a right parenthesis Elements are either nested lists again or atomic elements like num bers symbols etc The list expression a b c d e represents a nested list of 3 elements a is the first element b is the second one and c a e is the third one Thus the third element of the top level list in turn is a nested list Its two elements are again nested lists the first one con sisting of the single element c the other one containing the two elements a and e Since a single user command must be given as a single nested list a command like list type constructors has to be transformed to a nested list before it can be passed to the system list type constructors In addition to commands with fixed contents there are also commands containing identifiers type expressions and value expressions While identifiers are restricted to be atomic symbols type expressions and value expressions may either be atomic symbols or nested lists again For instance assuming there are type constructors rel and tuple and attribute data types int and string the nested list term rel tuple Name string Pop int isa valid type expres sion defining a relation type consisting of tuples whose attributes are called name of type string and pop of type int Additionally SECONDO supports the definition of new types Consider the new type cityrel d
61. her picture on the wall Another life used to live Another meaning once untrue With eve of you Broken ir Another mor st in time You talked of love and agreed So many chances passed us by And now I ve lost the strength to try Your love was never mine to keep We can t hold on we re losing track So where s the road to take us back And how did we forget what we believed If I can t have you xj il C case sensitive search Lamarca show as mmi ne par Close Save Figure 8 Special representation of the text type Managing Backgrounds The background of the graphic window can be changed by the user The color can be chosen via the settings menu This color is used if no background image is given and for all areas not cov ered by the background image A background image can be used to show the context of other objects For positioning the image the bounding box of this image must be defined together with the image For simplifying the positioning so called tfw files can be used Such files are also used in geographic information systems Another possibility to set the background is to capture the current display as background This may be useful if many non moving objects are displayed and additional moving objects are animated After capturing the static objects as background these objects can be removed from the display to reduce the computation effort during th
62. ing argument returns the number of cities starting with that string let Staedte with fun s string Staedte feed filter SName starts s count Then query Staedte with B returns on our little example relation the value 7 8 The Optimizer The optimizer component of SECONDO is written in PROLOG and allows one to formulate SEC ONDO commands as well as queries in an SQL like language within a PROLOG environment Commands are passed directly to the SECONDO kernel for execution Queries are translated to query plans which are then also sent to the kernel for exection One can also experiment with the optimizer and just see how queries are translated without executing them In the following sections after some preparations we discuss the PROLOG environment the query language hybrid queries combining SQL with SECONDO operations and creation of objects from query results the optimizer s knowledge about databases and how the optimizer can be informed about new operators available in SECONDO 8 1 Preparations In the following examples we work with the database opt Hence enter at any of the user inter faces e g in SecondoTTYBDB the commands create database opt restore database opt from opt Now the database is in good shape When you type list objects you can see that it has the fol lowing relations Orte Kennzeichen string Ort string Vorwahl string BevT int Staedte SName string Bev int PLZ int
63. ing the mouse A point is created just by clicking on its location For the creation of other objects a sequence of points has to be created by left mouse button clicks To finish the creation of such more complex objects the object creation button has to be pressed again If an object is defined it is stored into the currently open database and inserted into the object manager Instead of creating a lot of single objects it is also possible to write a set of objects having the same type into a single relation To use this feature as a first step a relation with schema rel tuple Name string T type must be created in the currently opened database The type is the same that is to be created It is also possible to let the gui create this relation choose this from the Object Creation menu To automatically insert newly created objects into this relation the corresponding checkbox must be activated within the Object Creation menu 6 Algebra Modules 6 1 Overview Included in the full SECONDO release is a large set of different algebra modules Three of these algebras are somehow fundamental and therefore are described here in detail They are e the standard algebra StandardAlgebra in the algebra list e the relation algebra RelationAlgebra in the algebra list e an extension to the relation algebra ExtRelationAlgebra in the algebra list All three algebras are located in the Algebras directory of the SECONDO installation Algebra
64. ion and join predicates e storedAttrSizes average sizes of attributes in bytes e storedOrderings known orderings within stored relations The optimizer distinguishes between different databases by saving the database name with all facts of its knowledge base The general principle is that the optimizer retrieves information from SECONDO when it is needed and then stores it for later use For example when a relation is mentioned for the first time in a query the optimizer sends list objects to the kernel to check whether the relation exists and to get attribute names with their spelling It also determines whether there are indexes available and creates a small sample relation if there is none yet It sends a query lt relname gt count to get the cardinality and another lt relname gt tuplesize to get the average tuple size in byte It also gets average attribute sizes by queries of the form lt relname gt attrsize lt attrname gt When in a query a selection or join predicate occurs for which the selectivity is not yet known the optimizer sends a corresponding query on the small sample relation s to determine the selectivity and predicate evaluation time PET Note The optimizer recognizes indexes by a name convention The name of an index must have the form lt RelName gt lt KeyattrName gt lt LogicalIndexTypeCode gt where lt RelName gt is the name of the base relation the index is built for and lt
65. it is also possible to compute derived attributes in the select clause For example select sname bev div 1000 as bevt from staedte Order One can add an orderby clause and a first clause see below hence the syntax of a query is more completely query gt select distinct clause sel claus from rel clause where clause orderby clause first clause orderby claus gt orderby orderattr list orderby orderattr amp orderby claus gt orderby orderattr list orderby orderattr amp orderattr gt attrname attrname asc attrname desc orderattr list gt orderattr orderattr orderattr list For example we can say select o ort pl plz p2 plz from orte as o plz as pl plz as p2 where o ort pl ort p2 plz pl plz 1 o ort contains dorf orderby o ort asc p2 plz desc It is possible to mention derived attributes in the orderby clause Taking Only the First n Elements Sometimes one is interested in only the first few tuples of a query result This can be achieved by using a first clause first clause gt first int constant amp For example select from plz orderby ort desc first 3 This is also a convenient way to see the beginning of a large relation Only the first few tuples are processed Grouping and Aggregation Aggregation queries have a groupby clause in addition to what is known already and a different form of the select clause query gt select aggr cl
66. l Using the command panel the user can communicate with the SecondoServer and the Optimiz erServer After the prompt Sec gt commands terminated by return can be entered The com mand is stored in the history A history entry can be selected by cursor up and cursor down keys Similar to the TTY based interface you can use shift tab keys to extend known word All SECONDO commands are available Additionally some Gui commands exist to control the behav iour of Javagui Command Description gui exit Closes the connection to SECONDO and quits Javagui gui clearAll Removes all objects from Javagui and clears the history gui addViewer lt viewer name gt Adds a new viewer at runtime If the viewer was already loaded then the current viewer is replaced by this viewer gui selectViewer lt viewer name gt Replaces the current viewer by the viewer with the given name gui clearHistory Removes all entries from the history Table 5 Gui Commands Command Description gui loadHistory r Shows a file input dialog and reads the history from this file Used with the r option this command replaces the current history with the file content Without the r option this command appends the file con tent to the current history gui saveHistory Opens a file dialog to save the content of the cur rent history gui showObject lt ObjectName gt Shows an object from the object manager
67. lation Algebras art nove ae la A wed 34 6 4 Extended Relation Algebra ss das anna ta aa 35 7 Functions and Function Objects a a a nn 40 8 gt Ihe Opimizer ee a ne er 42 Sol Preparations sesiet een ea ee ea da E ake 42 8 2 Using SECONDO in a PROLOG environment 0 0 00 c eee eee eee 42 8 3 An SQL like Query Language its Dada ar a RL Med OWES 44 8 4 Further Ways of Querying 22 22 vies sda dates Mad a kip ea a aS ve eg 49 8 5 The Optimizer s Knowledge of Databases 2 00 0 cece eee 50 8 6 Operator Syntax s tia bor nae ra UHR I a 53 8 7 Optimizer Options oreesa a Re aka re RER E dae eee ag eed 55 8 8 Getting Help on the Optimizer 1 0 0 0 ccc eens 56 A Operator OV OLA os e id Ce Gee ete se Gackt cet y as eae Er 58 B Grammar of the Query Language i ee ara cede eee THe eee 61 1 Introduction and Overview The goal of SECONDO is to provide a generic database system frame that can be filled with implementations of various DBMS data models For example it should be possible to implement relational object oriented temporal or XML models and to accomodate data types for spatial data moving objects chemical formulas etc Whereas extensibility by data types is common now e g as data blades cartridges etc the possibility to change the core data model is rather special to SECONDO The strategy to achieve this goal is the following e Separate the data model independent components and mechanisms in a dat
68. lttype Flags For more information on operator signatures and properties please read the comments within the file operators p1 itself 8 7 Optimizer Options The optimizer includes several extensions and optional features that can be selected and dese lected at runtime This is realized using optimizer options When the optimizer starts up a list of all option settings is displayed This list can be redisplayed using the command showOptions The option list looks like the following Optimizer options and sub options x standard Adopt options for standard optimization process useCounters Insert counters into the computed plan nolndex Disables the utilization of indexes allOff Turn off really ALL options earlyproject Project before sort and groupby improvedcosts Use improved cost functions determinePredSig Send queries to investigate predicate argument types immediatePlan Immediately create a path rather than the POG intOrders on Consider interesting orders on variant intOrders path Consider interesting orders path variant intOrders quick Consider interesting orders quick variant intOrders test Consider interesting orders test variant autoSamples Automatically determine sample sizes rewriteInference Add inferred predicates to where clause rtreelndexRules Infer predicates to exploit R tree indices rewriteNonempty Handle nonempty in sele
69. modules can be activated and deactivated by changing the configuration of SECONDO makefile algebras see Section 4 1 However the denoted three algebras are activated by default 6 2 Standard Algebra 6 2 1 Standard Type Constructors The standard algebra module provides four constant type constructors thus four types for standard data types e int for integer values The domain is that of the int type implemented by the C compiler typically 2147483648 to 2147483647 on 32 bit wide platforms e real for floating point values The domain is that of the float type implemented by the C compiler e bool The value is either TRUE or FALSE e string A value consisting of a sequence of up to 48 characters As all attribute types these data types allow each value to be undefined Thus for instance integer division by zero does not cause a runtime error but the result is an undefined integer value 6 2 2 Standard Operators Table 6 shows the most important operators provided by the standard algebra module Most of the operations like are overloaded and work for both types int and real For more informa tion about specific operations type list algebra StandardAlgebra in your SECONDO interface In Table 6 we use the following notations For signatures an expression like int real means that either of the types int or real can be used as an argument In the syntax column denotes an argument and
70. n shown in Figure 1 GUI Optimizer Y SECONDO Kernel Figure 1 Cooperation of SECONDO Components In this configuration the GUI can ask the kernel directly to execute commands and queries que ries written as query plans i e terms of the implemented algebras Or it can call the optimizer to get a plan for a given SQL query The optimizer when necessary calls the SECONDO kernel to get information about relation schemas cardinalities of relations and selectivity of predicates Here the optimizer acts as a server for the GUl and as a client to the kernel A very rough description of the architecture of the SECONDO kernel is shown in Figure 2 A data model is implemented as a set of data types and operations These are grouped into algebras Command Manager Query Processor Catalog Alg Alg gt Alg Storage Manager amp Tools Figure 2 Rough architecture of the kernel The definition of algebras is based on the concept of second order signature G 93 The idea is to use two coupled signatures Any signature provides sorts and operations Here in the first sig nature the sorts are called kinds and represent collections of types The operations of this signa ture are type constructors The signature defines how type constructors can be applied to given types The available types in the system are exactly the terms of this signature The second
71. nd sizes for known attributes e showStoredOrders list known orderings showSels lists the known selectivities e showPETs lists all known PETs The command e showDatabase will show a summary of all relation based information gathered for the open database including information on attribute types and sizes cardinalities and average tuple sizes known indexes and orderings Last the command e showDatabaseSchema will list all relations available in the open database not only the ones known to the optimizer from past queries together with their attributes and attribute types Reinitializing One can reinitialize the optimizer s knowledge of databases by deleting the files storedRels etc mentioned above from the directory Optimizer when the optimizer is not running In this case all information needed will be collected afresh on further queries If you somehow run into unexplainable problems with the optimizer it is usually a good idea to quit the optimizer delete all these stored p1 files and restart the optimizer Creating and Deleting Relations When new relation objects are created the optimizer should recognize them automatically as soon as they are used in a query It will also notice if a relation is deleted by the optimizer of course and between two optimizer sessions However the optimizer will not automatically be aware that a relation has been deleted during a optimizer session by third party and will s
72. ng a stream of tuples and a boolean function as parameters The function is applied to each tuple and filter lets the tuple pass if the function returns true filter fun tuplel TUPLE attr tuplel No gt 5 In this example fun is the keyword for the function tuple1 is the name which is assigned to the current tuple of the stream By using this name a tuple can be accessed attr is an operator which extracts from the tuple passed as first argument the value of the attribute whose name is given in the second argument Finally the gt operator is the binary infix operator which returns a boolean value that decides whether the tuple is passed through or not For the given example on a relation with an attribute no that contains numbers the filter expression lets pass all tuples which have a No attribute with a value greater than 5 However this expression is a bit lengthy to write For parameter functions SECONDO allows one to use an abbreviated form When using this form a variable name and the correct type are generated automatically This allows one to write the expression above as follows filter attr No gt 5 The parser translates this to the form shown above and translates it then to the appropriate nested list form Of course when writing the expression one does not know which parameter name is generated by the parser in particular the parser assigns distinct numbers hence there must be another way to refer to it
73. ntifier gt lt value expression gt list algebra lt identifier gt let lt identifier gt lt value expression gt list databases derive lt identifier gt lt value expression gt list types delete lt identifier gt list objects ki11 lt identifier gt query lt value expression gt if lt p gt then lt command gt else lt command gt endif while lt p gt do lt command gt endwhile lt command gt lt command gt 1 lt command gt lt command gt 1 Databases Transactions create database lt identifier gt delete database lt identifier gt open database lt identifier gt close database begin transaction commit transaction abort transaction Import and Export save database to lt file gt restore database lt identifier gt from lt file gt save lt identifier gt to lt file gt restore lt identifier gt from lt file gt Table 1 SECONDO Commands a User defined data types are currently not supported by the kernel b lt p gt means predicate an alias for lt boolean value expression gt An identifier is defined by the regular expression a z A 2 a z A 2 0 9 _ with a maximal length of 48 characters e g lineitem employ cities pop butnot x or10times 3 1 Inquiries Inquiry commands are used to inspect the actual system and database configuration They can be called even without a database e list type constructors
74. nto the gui cfg config uration file The Hoese Viewer This viewer is very powerful and is able to display a lot of different SECONDO object types The viewer consists of several different parts to display textual graphical and temporal data If an object in the textual part is selected then the corresponding graphical representation is also selected if it exists and vice versa The Textual Representation of an Object Using the combobox at the top of the text panel you can choose another object query result to display A string in the text representation of the selected object can be searched by entering the search string in the field at the bottom of the text panel and clicking on the go button If the end of text is reached the search continues at the beginning of the text The Graphical Representation of Objects The graphic panel contains geometric spatial objects Press the right mouse button and drag the mouse holding the right mouse button for zoom in Stepwise zoom in zoom out is available in the Settings menu or by pressing Alt Alt To get an overview of all objects click on Zoom out in the Settings menu or press Alt z Each query result is displayed in a single layer Using layers the order in which the objects are displayed can be changed To hide show a layer use the green gray buttons on the left of the graphic panel The order of the layers can be set in the layer management located in the settings menu A
75. nu entry but cre ates a snapshot of the whole screen instead of only the Javagui window Exit Closes the connection to SECONDO and quits Javagui Server Connect Connects Javagui to SECONDO Disconnect Disconnects the user interface from SECONDO Settings Shows a dialog to change the address and port used for communication with SECONDO For a perma nent change of these values the configuration file should be used User settings If authorization is enabled off by default the username and the password can be entered here Optimizer Enable Connects Javagui to an OptimizerServer Disable Closes the connection to an OptimizerServer Table 4 Menubar of Javagui Menu Submenu Menuitem Description Command In this menu the update functions of the optimizer for relations and indexes can be called Settings Opens a dialog to change the settings of hostname and portnumber of the OptimizerServer Command This menu contains all available SECONDO com mands Menu entries beginning with a require additional information If such an entry is selected a template of the command is printed out to the command panel Other commands are processed directly without further user input Help Show gui commands Opens a new window containing all Gui com mands see Table 5 Show secondo com Shows a list of all known SECONDO commands mands Viewers lt name list gt All known loaded viewers are listed her
76. o infix notation argl op arg2 e three arguments translated to prefix notation op argl arg2 arg3 An infix operator requires the following rule witih file opsyntax pl secondoOp Op infix 2 Prefix notation will be used if a rule like the following exists in that file N is the minimum num ber of arguments to operator Op secondoOp Op prefix N For example to define a distance operator with two arguments to be written in prefix notation we can specify secondoOp distance prefix 2 Reload the file after modifying it opsyntax Examples from the current contents of the file are shown in Appendix A For example we can now use the distance operator in a query on a database germany select sname distance ort s2 ort as dist from stadt stadt as s2 where s2 sname Dortmund distance ort s2 ort lt 0 3 SECONDO TypeMapping Some of the optional optimizer extensions rely on exact information of the type of expressions and subexpressions e g single computed arguments within a predicate To this end the type mapping functions from within the SECONDO kernel have an equivalent within the optimizer 2 The default syntax translation rules are deprecated Using them will prompt you with an according warning we ED Also it is possible to assign properties to operators For each operator at least one predicate is defined in file operators pl opSignature 0perator Algebra ArgTypeList Resu
77. on objects can be applied to arguments in the usual syntax and mixed with other operations query prod 5 double 7 6 50 It is also possible to apply anonymous functions to arguments this works only in nested list syn tax lt anonymous function gt lt argl gt lt arg_n gt For example we can write query fun n int n 1 70 and get as a result 71 It is allowed to define function objects with zero arguments this can be used to define views For example given a relation Staedte similar to StaedteTest from Sec tion 6 3 we can define a view to get cities with more than 500000 inhabitants 41 let Grossstaedte fun Staedte feed filter Bev gt 500000 consume Then the command query Grossstaedte yields the definition of the function Function type map rel tuple SName string Bev int PLZ int Vorwahl string Kennzeichen string Function value fun consume filter feed Staedte fun tuplel TUPL gt El lt attr tuplel Bev 500000 Observe that the map constructor has only one argument the result type The function is applied evaluated by writing query Grossstaedte Such a view can be used in further processing for example query Grossstaedte feed project SName consume and it can even be used in further views let Grosse fun Grossstaedte feed project SName consume As a final example let us define a function that for a given str
78. ore capability conjunctive query optimization currently for a relational environment Conjunctive query optimization is however needed for any kind of data models In addition it implements the essential part of SQL like languages in a nota tion adapted to PROLOG The optimizer is written in PROLOG e The graphical user interface GUI is on the one hand an extensible interface for an extensi ble DBMS such as SECONDO It is extensible by viewers for new data types or models On the other hand there is a specialized viewer available in the GUI for spatial types and mov ing objects providing a generic and rather sophisticated spatial database interface includ ing animation of moving objects The GUI is written in Java The three components can be used together or independently in several ways The SECONDO ker nel can be used as a single user system or in a client server mode As a stand alone system it can be linked together with either a simple command interface running in a shell or with the opti 1 It should also run on other UNIX platforms mizer In client server mode the kernel can serve clients running the command interface an opti mizer client or the GUI The optimizer can be used separately to transform SQL like queries into query plans that would be executable in SECONDO The GUI can be used separately to browse spatial or spatio temporal data residing in files All three components can be used together in a configuratio
79. pretty printed out put of tuples and relations Note that the implementation of these functions is optional As a con sequence there might be algebra modules having types for which no pretty printing can be per formed In this case objects having such a type are displayed in nested list format If the readline functionality is enabled see Installation Guide some additional features are avail able The command history can be stepwise passed by pressing the cursor up and cursor down keys respectively The history remains available even after termination of SECONDO By pressing the tab key the input is extended to the next matching keyword Keywords are all words from the SECONDO commands list database etc and some frequently used operators feed con sume A double tab prints out all possible extensions of the current word 5 2 2 SecondoPL SecondoPL is the text based interface of the SECONDO optimizer To start this interface navigate into the Optimizer directory of SECONDO and enter SecondoPL At the first run of SecondoPL some error messages regarding non existing files are shown They can be ignored On Linux machines you will have the advantages of the readline library if it is installed 5 2 3 TestRunner The TestRunner can be used for automatic testing of operators including checks for the correct expected results For using the TestRunner navigate into SECONDO s bin directory and enter TestRunner i lt inputfile gt
80. s The fanz fun groupl GROUP count preted as the end of the command ER query StaedteTest feed query max SName max feed StaedteTest SName query tenTest feed query aggregate No fun il int aggregate 127 168 TLS 127 1 feed tenTest no fun 11 int 12 int il i2 1 Table 10 Query Examples Relation Algebra 7 Functions and Function Objects A fundamental facility in SECONDO is the possibility to treat functions as values We have already seen anonymous parameter functions to operators such as select or filter The type of a func tion fun lt arg 1 gt lt type 1 gt Karg n gt lt type n gt lt expr gt map lt typel gt lt type n gt lt resulttype gt where lt resulttype gt is the type of lt expr gt For example the type of the function fun n int n 1 map int int It is possible to create named SECONDO objects whose values are functions technically the type constructor map is provided by the FunctionAlgebra Hence we can say create double map int int update double fun n int n n It is easier to create a function object through the 1et command let prod fun n int m int m n We can ask for the value of a function object query prod As a result the type and the value of the function are displayed in nested list syntax Function type map int int int Function value fun m ant m int m n Functi
81. several hundred or thousand edges In this case a sample with 10 of the tuples is quite sufficient and preferable as on the geometries often expensive predicates are evaluated hence in such a case one would like to have samples of size 50 say for both selection and joins Unfortunately it is difficult to build a general rule for such samples into the optimizer and this has not been done Instead the optimizer complains if by default it would create a sample relation of size more than 2 MB and asks the user to manually create samples This can be done as follows The predicates createSamples RelName SizeSel SizeJoin resizeSamples RelName SizeSel SizeJoin create samples with the desired sizes While the first one only creates samples if they are not yet present the second one will also replace existent samples with new ones Note that here the rela tion name has to be given in lower case letters only For example resizeSamples kreis 50 50 If you do not want to be bothered by manual creation of samples there is an option helpful to you setOption autoSamples This will automatically create all samples needed using some heuristics to determine sample sizes However with a database containing relations with large attribute values like moving object or multi media data you might prefer manual mode You can deactivate automatic sample creation by writing delOption autoSamples 8 6 Operator Syntax
82. system by running the make utility The file makefile algebras contains two entries for every algebra The first defines the direc tory name and the second the name of the algebra module like in the example below ALGEBRA DIRS Polygon ALGEBRAS PolygonAlgebra ALGEBRA DIRS BTree ALGEBRAS BTreeAlgebra Currently there is no mechanism which detects dependencies between algebra modules Hence read the comments in the file In case of trouble you have to switch on or off more algebras than the single one you wanted to change Finally you need to recompile the system by calling the make command 4 2 Startup Configuration The SECONDO applications SECONDOTTYBDB SECONDOPL SECONDOTTYCS SECONDOMONI TOR will read their configuration parameters from a file called SecondoConfig ini which is searched for in the current directory Optionally if the environment variable SECONDO_CONFIG is defined its value will be used as an absolute file name for the configuration file instead On most installations this will be already defined as SHOME secondo bin SecondoConfig ini There are many possible options which are documented in the file itself hence only some important options will be mentioned here The parameter SecondoHome home databasesl defines the node in the directory tree where SECONDO could store its databases If this parameter is not defined or if it points to a non existing directory the directory HOME
83. t the map server properties e g to use a different map style and some display settings like showing tile boundaries FA Secondo GUI Hoe Choose preset type Open Street Map Osmarender mA query bbox Hagen Name OSM Osmarender Program Server Optin see result in objec Sever Elings Sec gt query Hagen tri Base Settings query Hagen transl jsee result in objec Protocol ka Sec gt query Hagen tri query Hagen transl see result in objec Sec gt query Hagen tri query Hagen transl Port 80 Max Parallel Downloads 2 Mapper Class viewer hoese StaticOSMMapper Zoom Levels jsee result in objec Sec gt ile Dimensions spee Minimum 1 Width X 256 pixels query Hagen translate 0 Display Options Show tile frames C Show tile names CI Show complete map Server tah openstreetmap org Directory Tilesitile License s URL y wiki Tile_usage_policy Prefix Maximum 17 Height Y 256 pixels Set background color E lelx Figure 10 Setting properties for the TiledMap background Creating Objects The HoeseViewer offers the possibility to create simple graphical objects An object type can be chosen in the Object Creation menu After pressing the unlabeled button right of the time line the object creation starts For creating a rectangle the rectangle can be drawn by holding the left mouse button pressed and dragg
84. this manual describe the use of SECONDO in detail 2 Command Syntax 2 1 Overview SECONDO offers a fixed set of commands for database management catalog inquiries access to types and objects queries and transaction control Some of these commands require type expres sion value expression or identifier arguments used for object names database names etc Whether a type expression or value expression is valid or not is determined by means of the spec ifications provided by the active algebra modules while validity of an identifier depends on the content of the actual database see Chapter 3 for details The SECONDO kernel accepts two different syntax levels for entering commands and queries nested list syntax and text syntax Nested list syntax is directly processed by the SECONDO kernel and it is uniform over all operators However queries in nested list syntax tend to contain a lot of parentheses thereby getting hard to formulate and read This is the motivation for offering the second level of query syntax with two important features e Reading and writing type expressions is simplified e For each operator of an algebra module the algebra implementor can specify syntax proper ties like infix or postfix notation If this feature is used carefully value expressions can be much more understandable 2 2 Nested List Syntax Using nested list syntax each command is a single nested list For short the textual representation o
85. till create query plans for it which will then be refused by the SECONDO kernel We explain below how the optimizer can be informed about the deletion Creating and Deleting Indexes The optimizer checks for indexes when a relation is mentioned for the first time in a query Hence it automatically recognizes indexes created together with a relation before querying It will also notice creation and deletion of indexes by the optimizer and between different optimizer sessions It will not notice changes inflicted by third party during an optimizer session but can be instructed to rebuild its information base Informing the Optimizer If the optimizer opens a database it will automatically update its catalog on relations and indexes Additionally three commands predicates are available to explicitly inform the optimizer about changes to relations and indexes updateRel Rel A call of this predicate causes the optimizer to delete all information it has about the relation Rel including selectivities of predicates An existing sample is also destroyed A query afterwards involving this relation collects all information from scratch Existing or non existing indexes are also discovered For example updateRel plz resets all information for relation plz The second predicate is updateCatalog This predicate lets the optimizer perform the same procedure as if the currently open database was reopened In particular it will check wheth
86. ttempt is made to be consistent with any partic ular SQL standard Basic Queries The SQL kernel implemented by the optimizer basically has the following syntax select lt attr list gt from lt rel list gt where lt pred list gt Each of the lists has to be written in PROLOG syntax i e in square brackets entries separated by comma If any of the lists has only a single element the square brackets can be omitted Instead of an attribute list one can also write Hence one can write don t forget to type sql before all such queries and end them with a select sname bev from staedte where bev gt 270000 sname starts S To avoid name conflicts one can introduce explicit variables In this case one refers to attributes in the form lt variable gt lt attr gt For example one can perform a join between relations Orte and plz select from orte as o plz as p where o ort p ort o ort contains dorf p plz mod 13 0 In the sequel we define the syntax precisely by giving a grammar For the basic queries described so far we have the following grammar rules query gt select distinct clause sel claus from rel clause where clause distinct clause gt all distinct sel clause gt result result list count distinct clause result gt attr attr expr as newname result list gt result result result list Abe attr gt attrname
87. unction which computes an inner stream of tuples The oper ator returns the concatenation of each tuple of the outer stream with each tuple produced in the inner stream mergejoin stream Tuplel x stream Tuple2 x attri x attr2 gt stream Tuple3 m Join operator performing merge join on two streams w r t attr1 of the first and attr2 ofthe sec ond stream The first argument stream must be ordered by attr1 the second by attr2 both either ascending or descending sortmerge join stream Tuplel x stream Tuple2 x attrl x attr2 gt stream Tuple3 m Join operator performing merge join on two streams w r t attr1 of the first and attr2 ofthe sec ond stream hashjoin stream Tuplel x stream Tuple2 x attrl x attr2 x int gt stream Tuple3 Join operator performing hash join on two streams w r t attr1 of the first and attr2 of the sec ond stream The number of buck ets used is specified by the fifth argument symmjoin stream tuple al an x stream tuple bl bm x tuple al an x tuple bl bm gt bool gt stream tuple al an bl bm m Join operator performing sym metric join on two streams by computing a Cartesian product stream from its argument streams and filtering by the third argu ment concat stream Tuple x stream Tuple _ Concatenates two streams Can gt stream Tuple be used to implement relational union
88. us ext attr gt ext attr expr gt first clause gt groupattr gt groupattr list gt groupby clause gt id gt indexname gt indextype gt index clause gt insert query gt mquery gt newname 7 orderattr gt orderattr list gt orderby clause gt pred gt pred list gt query gt query list gt rel gt rel clause gt rel list gt relname gt result gt result list gt secondo rest query sel clause gt gt 62 distinct clause attr distinct clause attr expr first int constant last int constant attr groupattr groupattr groupattr list groupby groupattr list groupby groupattr o any valid Prolog constant identifier without any underscore character that is also a valid Secondo identifier id btree rtree hash any valid logical index typ xpression attrname attrname indextype indextype insert into rel values value list insert into rel query query create query drop query insert query update query delete query union query list intersection query list id where id is not already defined within the database or current query attrname attrname asc attrname desc distance id id orderattr orderattr orderattr list L orderby orderattr list orderby orderattr attr boolexpr pred pred pred list select distinct clause sel cl
89. uting the script see Section 5 3 1 For optimizer functionality ensure that the OptimizerServer is also running see Section 5 3 2 After some licence information a window will appear on the screen This window has four main parts the current viewer the object manager the command panel and a progress bar see Figure 4 The Menubar The Javagui menubar consists of two parts one depending on the current viewer and another one which is independent from it The following description includes only viewer independent parts Menu Submenu Menuitem Description Program New Clears the history and removes all objects from Javagui The state of SECONDO opened databases etc is not changed Fontsize Here the fontsize of the command panel and object manager can be changed Execute File Opens a file input dialog to choose a file Then the batch mode is started to process the content of the selected file It can be chosen how errors are han dled Note there exist two different script styles which are described and can be selected in the con figuration file History In this menu the current history can be manipu lated Favoured Queries Here you can manage frequently used queries for easy access Snapshot Stores a Picture of the Javagui window into a file as png image The key combination lt alt c gt can also be used to make a snapshot Screen snapshot Works similar to the Snapshot me
90. y 3 4 9 uery 6 lt 8 query lt 6 8 q q q query 3 4 9 q q uery Secondo contains cond and TRUE query and contains Secondo cond TRUE Table 7 Query Examples Standard Algebra 6 3 Relation Algebra 6 3 1 Relation Algebra Type Constructors The relational algebra module provides two type constructors rel and tuple The structural part of the relational model can be described by the following signature kinds IDENT DATA TUPLE REL type constructors gt DATA int real string bool from standard algebra IDENT x DATA gt TUPLE tuple TUPLE gt REL rel Therefore a tuple is a list of one or more pairs identifier attribute type A relation is built from such a tuple type For instance rel tuple Name string Pop int is the type of a relation containing tuples consisting of two attribute values namely Name of type string and Pop of type int A valid value of this type in nested list representation is a list containing lists of attributes of values e g New York 732200 Paris 2175000 Hagen 212000 6 3 2 Relation Operators Table 8 shows a selection of the operators provided by the relational algebra module Some of the operators are overloaded For more information about the operators and a full list of operators type list algebra RelationAlgebra in the SECONDO user interface
91. y be applied to an attribute name but also to an expression built over attributes There is one restriction imposed by the current implementation and not visible in the grammar the select clause in an aggregate query must contain a derived attribute definition Hence select ort from plz groupby ort will not work This will be optimized but not executed by SECONDO Union and Intersection It is possible to form the union or intersection of a set of relations each of which is the result of a separate query The queries are written in a PROLOG list All result relations must have the same schema mquery gt query union query list intersection query list query list gt query query query list For example union select from plz where ort contains dorf select from plz where ort contains stadt Note that in this case each of the subqueries in the list is optimized separately One interesting application is to find tuples in a relation fulfilling a very large set of conditions The optimizer s effort in optimizing a single query is exponential in the number of predicates It works fine roughly up to 10 predicates Beyond that optimization times get long However it is no problem to use for example an intersection query on 30 subqueries each of which has only one or a few conditions The query processed by the optimizer is an mquery 1 e the query command is of the form sql mquery The complete gr

Version 3.1 User Manual

Contents

Download Pdf Manuals

Related Search

Related Contents