Affichage de terrain intelligents en Xna à l’aide de la technique BilLOD

Je réalise en ce moment même un article pour la MSDN sur l’affichage de terrains intelligents en Xna à l’aide d’une technique que j’ai “inventé”.


Le système fonctionne plutôt pas mal et répond à tous mes besoins. J’en profite donc pour le partager avec vous ici.


Je donnerais l’url de l’article sous peu. Pour l’heure je peux donner les grandes lignes du fonctionnement de cet algo.


Principe :


J’ai tenté de comprendre à quoi correspondait une forme en Xna. Pourquoi est ce ci important de comprendre ça ? Tout simplement parceque la forme est l’élément visuel le plus important d’un terrain. Si on veut pouvoir afficher un terrain de manière intelligente, c’est à dire avec peu de resources en gardant au maximum son aspect il faut comprendre au mieux ce qui le caractérise. La forme est un ensemble de courbes. C’est à dire un ensemble de triangles côtes à côtes qui ne sont pas coplanaires. A partir de là j’ai cherché à comprendre comment on pouvait définir le fait que deux triangles appartiennent à deux plans qui forment un “angle” entre eux. En fait, c’est tout simple : on le déduit à l’aide de leur normale. Quand deux plans ne sont pas parallèles leurs normales forment un angle. Il suffit alors de calculer le produit scalaire de leurs normales (qui donne la force de leur angle) pour déterminer l’intersité de la forme.


Maintenant que je connait l’intensité de la forme, je suis en mesure de déterminer quelles sont les formes le plus caractéristique de mon terrain pour le garder visible et quelles sont celles qui ne sont pas pertinentes pour les supprimer.


J’ajoute à celà un critère primordial : la distance de la forme à la caméra. Une forme peut être non pertinente si vue de loin. Mais à proximité il est important de la voir. Vous pouvez voir le résultat à l’aide de l’image ci-dessous :



A peu près 100 fois moins de ressources prises et le même aspect. Regardez de même ce cône que j’affiche avec ma technique en Wireframe :



On remarque que la ou il y’a une courbe (à la base du cone et au sommet) le nombre de primitive est important. Mais là ou les courbes sont inexistantes (sur le plan principal et sur les coté du cône), le nombre de primitive est réduit au maximum.


L’image suivante explicite encore mieux le fait d’afficher les détails proche de la caméra et réduire les détails à mesure qu’on s’en éloigne :



J’avais placé la caméra au centre de l’écran (là où se trouve un cratère) et j’ai mis en pause l’application de l’algorithme. Le mesh du terrain n’est alors plus modifié. J’ai alors pris de l’altitude pour comtempler le terrain. On voit que près de la caméra les détails sont au maximum et à mesure qu’on s’en éloigne ils se réduisent mais gardent l’aspect du terrain. De cette façon on peut très bien imaginer l’affichage de terrains eloignés avec très peu de détail pour simuler une ligne montagne comme la ligne bleue des vosges :



L’affichage du terrain est alors ultra leger. J’ai appellé cette technique BilLOD, je vous laisse réflechir pouquoi :)


Vous pouvez télécharger le code source ici.


Attention il est en Xna 3.0.


 


A bientôt sur ce blog !


Valentin Billotte

Smart Terrain Rendering with Xna using Billod algorithm

Version Française ici.


If you’re a game developer you’ve already worked on terrain rendering. It is, in my opinion the critical point of a game. Not because conceptually speaking it is the most important visible thing on the screen but because it is the main factor playing on the performance and quality of your application. It is therefore important to use techniques designed to minimize the burden borne by your game to view the ground while preserving its quality display. This article shows a way that attempts to answer this question in XNA.


Conducting a study of game engine designed to help game developers in the Imagine Cup competition, I naturally preferred this important aspect in seeking a way to display a 3D world unlimited real size with an acceptable level of detail.


There are many techniques for displaying world explained by interesting articles. These are often based on a compromise uncomfortable wearing a choice between the size of land or quality level. In addition, they are often prohibitive algorithms.


This article does not appear as the only possible response to the display ground XNA but would just provide an effective solution running for this technology knowing that most of the examples existing on the Web are made in DirectX and OpenGL.


We will split our study in several phases:


·          First we describe the problems posed by the display of land and explain the need for optimization.


·          We then describe the method used to cut our land into a QuadTree.


·          We will explain how we reduce the retail field at distance while keeping up its appearance nearby.


·          We will describe a more technical implementation of this technique


·          Finally we will see possible optimizations offered to the reader.


 


The problem


Let’s say we wanted to model a land about 10 km by  10km with a degree of detail about 1 meter (1 kilometer = 1000 meters). So we have (without optimization)  a grid of 100000 * 100000 to show. We have to specify that that each vertex of the grid has a memory size equal to approximately 40 bytes. It reaches   about 4 gigabytes of data to display. The hardware of a lambda PC cannot handle such loads memory. Another problem, such a field modeled in a grid rendering static demand of 200 million triangles per frame. This is a disaster. From the moment the size of the field becomes important and where we add the ground ingredients of usual games, as 3D models, an user interface, animations,   performance of a game can no longer follow. This is here, that we have to manage a smart rendering.


It is therefore important that the CPU and / or GPU taken by the display field is as low as possible to keep processing time for other needs of the game. The virtual world offered by most modern games are huge and cannot be done with a simple static grid


The need for optimization is therefore evident. I invite readers to visit http://ww.vterrain.org to discover some presentations techniques other than that presented here.


If we had a specification to comply, we would have:


·          Keep the appearance of the ground up by reducing as much as possible the burden with its display.


·          Make the management of land by the code clean and accessible.


·          Using a technique efficient, simple and fast.


·          Respecting a QuadTree to optimize performance and meet the needs of treatment games.



Keep appearance with 100 times less resources, is a beautiful goal!.


 


The QuadTree


The first step is to manage our future terrain through a QuadTree.


A QuadTree is a data type tree in which each node can have up to four children. The Quadtrees are most often used to split a two-dimensional (3D abscissa and depth) by recursively subdivided into four knots. Each node has 4 children and so on. The route of such a tree is simple and takes on average the same time : for each level, there is a maximum of 4 tests to be “allowed” to navigate into lower level.   This is important given the task before us here: “Show ground with maximum details using minimal resources.”. The goal is to minimize the course of such a tree through its hubs in making these tests smart and simple.


Principle


The QuadTree (often used for rendering the field) provides an intelligent answer to the problem of optimization. First it loads the data only when needed. Then, the burden caused by the representation of a node on the screen depends on the depth of the node in the tree. Therefore, the sooner it is estimated that a node not be displayed in the course of a tree, the greater the optimization of display is realized (we limit the same time the number of triangles to display ). We will explain it all over this article.



More on sinking into the tree, more precision displayed with refined detail.


The real question to be asked to optimize the display of our tree is “what tests should be performed to determine the appropriateness of displaying a node on the screen”. We will see this as soon as possible. For now let us concentrate on how we manage the tree in our code.


Display the tree


Initially our terrain is only rendered by a single node (see picture above). The node size will be equals to the size of the terrain. A node on the screen corresponds to a surface composed of 9 square vertices:


1. Nord North


2. Nord Est North East


3. Est East


4. Sud Est South East


5. Sud South


6. Sud Ouest South West


7. Ouest West


8. Nord Ouest North West


9. Centre Center


 



A node is composed of 9 vertices


The QuadTree will be based on a heightfield (a two-dimensional array containing all the heights of each vertex Y on the screen). Each vertex of each node in the QuadTree has a height whose value is drawn from the heightfield.


Initially, only the corner of   a node are visible, namely: North East, South East, North West, South West. Its rendering is similar to the following picture:



The shaded vertices are visible vertices


These four vertices displayed allow us to draw two triangles form the surface of square knot. For each update of the terrain, for each node currently visible, we calculate the visibility of each of the vertices. Each update of a node is accompanied by a redrawing of triangles that make it up. he following picture explicit some possible configurations for a node:




The algorithm for this part would be:


/ / For each of the vertices North, East, South, West


If ( !Vertex.Visible && VertexTest())


                Vertex.Visible = true ;


Else if (Vertex.Visible && !VertexTest())


                Vertex.Visible = false;


 


The test of this algorithm takes into account the distance of the vertex from the camera and the growing need for detail as the camera approached it. We will see the nature of this test later.


In a second step it is necessary to calculate the visibility of children of current node. A node consists of four children as the next picture shows:



Here we have shown the North East of child root node, then the child South East of it, then the child of the South East last child finally North West of it.


The more one penetrates deep into the QuadTree over the mesh tightens and will display a more detailed terrain.


The algorithm for this part at this stage of our study would be:


// For each of the four children North West, North East, South West, South East


If ( !Child.Visible && ChildTest())


                Child.Visible = true ;


Else if (Child.Visible && ! ChildTest())


                Child.Visible = false;


Once again, the test of this algorithm takes into account the distance to which the child and the growing need for detail as the camera approached it. We will see the nature of this test later.


When a child is exposed, its vertices and his own children are subject to two algorithms that we have just seen. We are therefore in a principle of recursion.


We compute the triangles that’s makes a node by not taking into account the parts overshadowed by children:



We note here that the vertices North and East of the parent shared with the vertices North West and South West of the child. We will see that later.


The basic principle of the Billod algorithm is relatively simple. Vertices et enfants à afficher sont soumis à chaque mise à jour du terrain à un ensemble de tests qui déterminent leur visibilité. Vertices and children are subjected, at each updated of the terrain, to a set of tests that determine their visibility. The display of a node node is a recursive set of instructions that  goes through the  QuadTree along the root node to each leaf node of each branch.


Improvements


We talked previously shared vertices between a child and a parent. The previous image illustrates this point. This may cause an unpleasant graphic bug illustrated by the image below:




Or, with wireframe:




This problem is due to the non-activation of a vertex in the neighboring node. The nodes affected by this bug are:




So in reality:




  The West vertex of the child red is activated but not the vertex of the yellow child. There is therefore a delta between the two sides of the two children. To resolve this bug simply validate the vertex of the child is yellow:



 


Finally we get a perfect continuity:



 


  Now imagine that the southern vertex of the yellow child becomes visible. The parent node has only two children north west and north east. Activating this vertex pose the same problem as before graph:



 


  Unfortunately, unlike the previous case, there are no children in the South West. It is therefore necessary to create a child to this position (although the “ChildTest” of our algorithm for that child returns false) and to activate its North vertex:




The addition of southern vertex of the white child led to the creation of the Child South West and the activation of southern vertex parent node. The vertex South may itself cause the modification of neighboring nodes. There are, here, some cascading effects that are not really controllable but, fortunately, limited. The following image shows the impact of activation of several children deep into the QuadTree at the bottom right of the terrain.




We note that the nodes located at the opposite are impacted.


 


Managing nodes in the QuadTree


Some years ago I read an excellent article by a person named Stan Melax author of an ingenious technique for calculating a dynamic aspect of a 3D model at different levels of resolution. The articles on progressive meshes are subject to the same laws as those on land: either the reduction algorithm is too complicated or it is inappropriate. Stan Melax found a clever way to solve this problem. He studies the cost the disappearance of each vertex by studying their impact on the overall shape. He is then able to gradually reduce the resolution of the 3D model by removing  vertices in a logical order with the less impact.  Its strength lies in the calculation of the cost of a vertex which proves to be a simple and effective. We will proceed the same way.


The curve : Element to remember


To explain simply :  We measure a terrain to its forms. We measure a form to its curves. The question is then: how to define a curve ?. Answering this question would determine the locations of the terrain where we must keep an important retail level and those that can be degraded.


There are many techniques to transform a curve into a mathematical function. We return in this case to the beginning problem: Do not overload the processor too much. We have seen, it is necessary to execute the formula found at all vertices and all children nodes. A formula that generates a lot of instructions will deteriorate application performance.


The solution is simple. We have said that our land is splitted into a set of nodes belonging to a tree. Each curve of the land therefore consists of nodes that are actually surfaces square whose size varies





The terrain is a set of curves visible on the screen. Each curve is an assembly of square knots coast to coast glued.


The normal, order of magnitude of a curve


Plus la courbe est importante, plus les nœuds qui la composent forment un angle important. The greater the curve is, the greater the angles made by its nodes are important. Take for example a curve :




It shows all nodes glued side by side. At the place where the curve is really important we find nodes that form an angle. In contrast to where there is no curve, but a simple slope, nodes coast to coast appear to belong to the same plane. We noticed even more if we show each normals nodes:




We are now close to finding the formula! All the students know that the mathematical function that measures the angle between two vectors is the dot product.



The inner product of two vectors represented by non-zero vectors A and B is the actual number ABcos (θ) if the angle θ is the angle formed between the two lines whose direction are represented by the two vectors.


The maximum angle that can be found in a curve of land as we conceive it tends to 90 degrees (90 degrees value cannot be achieved because nodes can’t overlap). Cos 90 equal 0 so we can infer that when the curve formed by the vectors is important, their dot product approach 0 (A * B * Cos (90) = A * B * 0 = 0). On the contrary if the value of inner product increases, is that the curve formed by the two nodes tend toward the “right” (A * B * Cos (0) = A * B * 1 = AB).   See this picture:




The inner product between the normal parent and child will return here a value far away from 0 : we are on a surface, certainly inclined, but plane. Needless to cut the node.


Consider now a pronounced curve :




The angle between the three normals is more pronounced. The original node (symbolized by the black dotted line) should be divided into sub-nodes. We can found in this process a certain logic: a flat surface is easily modeled with few polygons. A curved surface ask for much. Here to complete the wireframe curve:



Where the curve is more pronounced, our technique uses more primitives


  Activation


Child Test method should compare the normal of the children to test with the normal of the parent node to which it belongs. If the angle between the two normal exceeds a threshold when the method returns true.


Disabling


Disabling a child occurs when a series of factors are met:


·          The child has no longer visible vertices


·          The child has more sub-children


·          The test method returns false.


 


Enable/Disable switch node in the vertex


Interpolated to real


La gestion de la visibilité des vertices ne suit pas la même logique que celle décrite précédemment. The management of the vertices visibility does not follow the same logic as that described above. Here there is no curves but delta.   Only vertices on the sides of a node are to be tested. So the North, East, South, West vertices. When a node is created, this vertices are not visible. But they are located between two vertices on node edge and so, we can deduce their height easily. Here is an example of node viewed from the side:




It shows the vertices North West and South West enabled (visible)) and West   vertex disabled.


The West vertex seems to be on the right along the two summits. In real, it may not. For now we have interpolated (deducted) its position (If South West is at a height of 10 and North West at a height of 20, it is reasonable to assume that the West vertex will be at a height of 15). Yet in reality, it is may be at another height:




There is a distance between the delta position of vertex West interpolated its actual position. This difference in height measures the degree of reality of the node currently displayed with a more detailed version.


View node with only the 4 vertices summits displayed therefore does not adequately reflect the reality on the ground displayed by the node.


Way of activation


The activation method is very simple, we only need to determine the Delta value (a simple difference of heights) and determine if it exceeds a threshold too.


Test of disabling


Disabling a vertex is only when a series of factors are met:


·          The vertex is used by a child node


·          The test method returns false.


 


Progressive Details


 It was one of the prerequisites for our technology, we must optimize the display according to the camera position. At this point, our two functions tests (for vertices and children nodes) do not take into account the camera position and the position of the element to test.


We have seen in the two algorithms dedicated to the vertices and nodes, that test is performed to determine if a vertex or child should be displayed or not. The land must be displayed according to the camera position for the comfort of the player. The more an area of land is close to the camera the more it have to be detailed. It also aims to preserve up to the appearance of the terrain away with a minimum of vertices and gradually detail when the camera comes.




Detail for progressive QuadTree


For the time the testing method is based on the angle made between the normal of a child with its parent.  This procedure is not necessarily relevant distance.


If a few degrees angle is visible when you are close to the nodes that make up this angle is not necessarily detectable at a distance It is not necessary to activate the child.


Here is the implementation of this test method:


private bool ChildTest(Vector3 childNormal, BoundingBox boundingBox, Vector3 cameraPosition)


{


    //by default, the four childs of the root node are visible.


    if (this.Depth < 1)


        return true;


 


    //get the closest point to the camera and check the distance


    float distanceCameraToPoint = Vector3.Distance(GetBoundingBoxClosestPointToPoint(boundingBox, cameraPosition), cameraPosition);


    //compute the dot product between parent normal and child normal


    float dotprod = 1 – Vector3.Dot(childNormal, this.CenterVertex.Value.Normal);


 


    //check with the threshold


    return (distanceCameraToPoint / this.ParentTree.QuadTreeDetail) < (dotprod);


}


By default nodes at the top of the tree are made visible. We then determine the distance between the camera position and the nearest point of it in the BoundingBox encompassing child. The dot product is then calculated. We test the division of this distance by an adjustable threshold is below the dot product.


Note that this method of testing may well be adapted to submit the QuadTree to another technique optimization.


Progressive detail for the vertex


Again, the testing method for vertices does not take into account the distance to the camera. We must determine if the distance between the actual position of the vertex and its position interpolated exceeds a threshold configurable.


Here is the testing method:


        public bool VertexTest(Vector3 vertexPosition, Sides side, Vector3 cameraPosition)


        {


            //get the distance between interpolated height position and real height position


            float lengthToTest = this._realToInterpolatedVertexHeight[(int)side];


            //get the distance from the camera position to the vertex position


            float distanceCameraToPoint = Vector3.Distance(vertexPosition, cameraPosition);


 


            //check with the threshold


            return lengthToTest * this.ParentTree.VertexDetail > distanceCameraToPoint;


        }


 


The delta between the two vertices (interpolated and real) is stored in memory. We then determine the distance between the vertex and the camera. A test is then conducted to determine if the Delta exceeds the threshold distance to the camera. Like earlier this test method may well be adapted to submit the vertices of each node to another technique optimization.



A field at different level of detail, from left to right: 7500, 3000, 1500, 300 150 vertices


 


Improvements


Our algorithm at this stage works perfectly: it displays the details where they are relevant, frees up resources and uses a quadtree.


Remember that time, the relevance of viewing a node is measured using the dot product of its normal with normal direct its parent. This is rather good, but we can improve. The following pictures illustrate a graphic unpleasant bug that modifies the appearance of ground within walking distance: 



In front of us: a slight bump on the ground



The hump has been truncated


 


Note that the bump on the first visible image is greatly reduced by the mere fact of having moved slightly camera. But our algorithm has worked here perfectly. We simply validated a threshold requiring a node to be cut. So we need to reduce the implementation of our algorithm close to the camera. We will therefore introduce a second threshold that will represent what we mean by “proximity”.


The new test function is thus as follows:


        private bool ChildTest(Vector3 childNormal, BoundingBox childBoundingBox, Vector3 cameraPosition)


        {


            //by default, the four childs of the root node are visible.


            if (this.Depth < this.ParentTree.MinimalDepth)


                return true;


 


            //get the closest point to the camera and check the distance


            float distanceCameraToPoint = Vector3.Distance(GetBoundingBoxClosestPointToPoint(childBoundingBox, cameraPosition), cameraPosition);


            //compute the dot product between parent normal and child normal


            float dotprod = 1 – Vector3.Dot(childNormal, this.CenterVertex.Value.Normal);


 


            //check with the threshold


             return ((distanceCameraToPoint - this.ParentTree.QuadTreeDetailAtFront ) / this.ParentTree.QuadTreeDetailAtFar) < (dotprod);


        }


We now have two thresholds, a threshold of visibility for “near displays” to maximize the details close to the camera and a threshold of “far display” that manages the details beyond the previous threshold according to a affine function as we did until now.


The advantage is undeniable. For proof, just take a look at the picture below:




I went to the center of the terrain, I stopped the implementation of the algorithm (similar to a pause in the Update of the QuadTree) and take some the altitude. We notice that the details seem to diminish when away from the center. This is indeed the case:




At the center of the screen near the crater, the details are at maximum. If we were on the ground, we do not see any ground near bump be changed by moving the camera. On the contrary, the far away details keeps the overall look of the land.


It’s not all, we are now able to generate a climate and geographical well known: a mountain line (sorry I do not know the English term for “ligne bleue”). Look at this picture:




It is the blue line Vosges clearly visible from the north of “Franche-Comte” in France. Plus on regarde. The more one looks far more mist arises in a bluish tint. By applying our algorithm and a modified fog we are able to generate a skyline purified up to cut a new notch resources taken by the displayed terrain. Return on our field, but this time by being near the ground: 




 


We’ve added a blue fog to simulate this. The remote mountains are visible thanks to the difference in color the fog gives them. Now the same view but with a threshold of visibility at distance greatly reduced:




We get a display similar but with 1300 primitives less!


 


Code and optimization


The code given with this article is deliberately sketchy for the sake of understanding. Son but est de tester la technique Billod Its purpose is to test the Billod technique described here  with no optimization.


Description of the “business” classes


Four classes are really business:


·          The terrain is naturally managed by a class called Terrain. We will return later on its possible usefulness. Terrain is a container for Quadtrees. It represents the whole ground.


·          The tree is represented by an instance of QuadTree class. QuadTree represents a part of Terrain. It is a container of QuadNodes.


·          The nodes of the tree are created using the QuadNode class. It is a container of TerrainVertex. QuadNode represents a node.


·          Finally the 9 vertices of a node are TerrainVertex.


Most treatment is within the class QuadNode. This has two important methods: Initialize and Update.   The Initialize method handles load the node with all information related to its position in the QuadTree. It makes visible its four edges, it determines the neighboring nodes (for sharing vertices). It also calculates the deltas, normals, BoundingBox. It is called once every creation of a node.


The Update method performs tests on the four vertices “sides” (North, East, South, West) and four children on the potential (North West, North East, South West, South East) to each update of the tree .


This is the class QuadTree which initializes the first node and start treatment on the tree. Every x seconds (adjustable) it launches an asynchronous update on all the nodes instantiated to update the tree depending on the position of the camera.


The instantiation of a QuadTree is achieved by specifying the size of it, its depth and its location:


QuadTree tree = new QuadTree(treeDepth, rootNodeSize, location);


 


The structure TerrainVertex contains the vertex to display. A vertex can be shared on multiple nodes.


The code of this four major classes has been simplified in the extreme and widely commented. If there are still black spots do not hesitate to contact me.


Update Thread


A quick and easy (dirty ?) solution has been  implemented to update the field. A BackgroundWorker is launched when you start the application and handle the updating of the field asynchronously.


 This procedure avoids any slowdown of the game during the update. The data displays (VertexBuffer and IndexBuffer) are stored in a stack and used in the Render method. In the example provided with this article, we do an update that every 4 seconds.


Optimizations


Apart from improvements to the code, here are some ideas to increase the quality of reporting and the power of technology Billodes.


·          We do two courses of the shaft each Update (one to update the tree, the other to extract the list to display triangles). There ‘may be a point to improve here.


·          The updated every X seconds is not necessarily the best solution. There are several other possible avenues to update, for example when the camera is moved or predicting the movement of the player and update accordingly. Finally, it should be noted that the final status of a tree is not achieved in a single Update It may be preferable in some cases to await the final version of the tree by calling the Update method as many times as necessary. You shlould prefer update the tree by little part each frame.


·          Some thresholds for activation or deactivation of a child or a vertex are very low. We can consider that they will never be activated.


·          When a node is cut or merged, or when a vertex changes state (visible / hidden), the transition is visible.It may be useful to cut this transition more gradual steps to soften the visual changes.


·          Relying on the Terrain class. In this sample we render only a single tree. The Terrain class field can be a serious springboard to try to manage several trees that will be linked one to another.  


·          We load the entire Heightfield from the start by analyzing a black and white. It is an effective way to proceed to achieve an example but not to make a  game. It might be wise to store the array of heights in a file and read what the QuadTree needs at each Update.


·          The source code has not been optimized for readability.


 


The example program


This program is just one example of a rapid implementation of the land Billodes. The business classes are quickly adaptable to individual needs. The project is XNA 3.0. Il est toutefois adaptable au 2.0. However, it is adaptable to 2.0.


Commands to interact with the world are:


·          Mouse: Click to turn the camera.


·          Arrow keys: moving the camera in the current direction.


·          Key W: Wireframe Mode On / Off.


·          Page Up / Page Down: Change the Far Threshold Test.


·          Key left Shift + Page Up / Page Down: Near Threshold Test.


·          Left Key Shift + arrow keys: fast move.


·          Left Key Alt + arrow keys: moving slow.


To test the Billod algorithm, simply move on the ground and studying the evolution of the field.


Note that it is possible to modify some parameters affecting the display using the configuration file of the application.


You can reuse the source code as you want. I just ask to refer me in your “credits”.


 


References / Credits


The Web site of Stan Melax: http://www.melax.com . A very pragmatic man at the root of a technical Progressive Mesh very smart on which I based my algorithm. I take this article to thank him for his kindness and his exchange at the time or I was working on progressive meshes in XNA.


“Continuous LOD Terrain Meshing Using Adaptive Quadtrees” by Thatcher Ulrich. A method quite similar to mine but more research-oriented to deltas. This is one of the few methods available on the web that can be used for a game. My algorithm has a QuadTree quite similar to his “Adaptive QuadTree” but his management heightfield is much more intelligent than mine. His details management is, in my opinion less  good than mine.


The page Hugues Hoppe: http://research.microsoft.com/ ~ hoppe. One of the masters of the terrain rendering. Everything is so great but illegible for novices and not necessarily adapted to the world of video gaming.


Virtual Terrain Project: http://www.vterrain.org/ . Website with a great set of publications around the management of terrain.


And on a more personal way Mathieu Laussel, a French friend 3D modeler (engineering) with whom I realize an editor of the world and who kindly wait as I write this article. And Boris Driss student that like game development and with who, talking is very interresting.


 

Downloads


 


 


You can downalod the C# project here.


 


 


 

Google Chrome : Quand le navigateur devient OS.

Encore une news qui va dans le sens de mon analyse sur l’utilité à court terme des OS et des applications lourdes. Google a encore une fois tout compris et prend le train en marche puisqu’il offre en béta son futur navigateur Google Chrome. Un navigateur particulièrement profilé pour optimiser le fonctionnement d’applications riches sur internet et concurrencer Windows.


Je vous invite à lire une très bonne présentation de ce navigateur : http://www.vnunet.fr/news/google_chrome___une__plate_forme_moderne_pour_pages_web_et_applications_-2028531


Et / ou à le télécharger :
http://gears.google.com/chrome/?hl=en
(à l’heure où j’ecris ces lignes, le navigateur n’est pas encore disponible, le lien devrait être activé dans la journée).


 


 

Base de données School (sur laquelle sont basés les exemples)

Revenir au sommaire


 


Dans cet article nous installerons sous l’environnement de gestion de SQLServeur une base de données que nous utiliserons dans nos apprentissages.
Vous devez disposer du SQL Server Management Studio ou du Sql Server Management Studio Express pour executer le code Transact-SQL affiché dans ce tutoriel.
Après installation et verification de la base. Nous l’expliciterons quelque peu pour la comprendre et maitriser ses concepts.
A noter que cette base est héritée de la base Microsoft du même nom utilisée pour les exemples de la MSDN sur laquelle j’ai réalisé quelques menues modifications.
 


Installation de la base


  1. Dans le menu Fichier/File allez sur Nouveau/New puis cliquez sur Requête de moteur de base de données/Database Engine Query.
  2. Dans la fenêtre Se connecter au moteur de base de données/Connect to Database Engine spécifiez soit localhost ou le nom de l’instance Sql Server que vous ciblez, puis cliquez sur Connect.
  3. Collez le code Transact-SQL suivant dans la fenêtre qui s’est ouverte et appuyez sur F5 pour lancer le script.

 


                                                                          


 


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


USE [master];
GO


IF EXISTS (SELECT * FROM sys.databases WHERE name = ‘School’)
 DROP DATABASE School;
GO


– Create the School database.
CREATE DATABASE School;
GO


– Specify a simple recovery model
– to keep the log growth to a minimum.
ALTER DATABASE School
 SET RECOVERY SIMPLE;
GO


USE School;
GO


– Create the Department table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[Department]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[Department](
 [DepartmentID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Budget] [money] NOT NULL,
 [StartDate] [datetime] NOT NULL,
 [Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
 [DepartmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Create the Person table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[Person]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[Person](
 [PersonID] [int] IDENTITY(1,1) NOT NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [HireDate] [datetime] NULL,
 [EnrollmentDate] [datetime] NULL,
 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
(
 [PersonID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Create the OnsiteCourse table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[OnsiteCourse]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[OnsiteCourse](
 [CourseID] [int] NOT NULL,
 [Location] [nvarchar](50) NOT NULL,
 [Days] [nvarchar](50) NOT NULL,
 [Time] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_OnsiteCourse] PRIMARY KEY CLUSTERED
(
 [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Create the OnlineCourse table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[OnlineCourse]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[OnlineCourse](
 [CourseID] [int] NOT NULL,
 [URL] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_OnlineCourse] PRIMARY KEY CLUSTERED
(
 [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


–Create the CourseGrade table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[CourseGrade]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[CourseGrade](
 [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
 [CourseID] [int] NOT NULL,
 [StudentID] [int] NOT NULL,
 [Grade] [decimal](3, 2) NULL,
 CONSTRAINT [PK_CourseGrade] PRIMARY KEY CLUSTERED
(
 [EnrollmentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Create the CourseInstructor table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[CourseInstructor]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[CourseInstructor](
 [CourseID] [int] NOT NULL,
 [PersonID] [int] NOT NULL,
 CONSTRAINT [PK_CourseInstructor] PRIMARY KEY CLUSTERED
(
 [CourseID] ASC,
 [PersonID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Create the Course table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[Course]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[Course](
 [CourseID] [int] NOT NULL,
 [Title] [nvarchar](100) NOT NULL,
 [Credits] [int] NOT NULL,
 [DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_School.Course] PRIMARY KEY CLUSTERED
(
 [CourseID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Create the OfficeAssignment table.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[OfficeAssignment]‘)
  AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[OfficeAssignment](
 [InstructorID] [int] NOT NULL,
 [Location] [nvarchar](50) NOT NULL,
 [Timestamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_OfficeAssignment] PRIMARY KEY CLUSTERED
(
 [InstructorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO


– Define the relationship between OnsiteCourse and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N’[dbo].[FK_OnsiteCourse_Course]‘)
       AND parent_object_id = OBJECT_ID(N’[dbo].[OnsiteCourse]‘))
ALTER TABLE [dbo].[OnsiteCourse]  WITH CHECK ADD 
       CONSTRAINT [FK_OnsiteCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnsiteCourse] CHECK
       CONSTRAINT [FK_OnsiteCourse_Course]
GO


– Define the relationship between OnlineCourse and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N’[dbo].[FK_OnlineCourse_Course]‘)
       AND parent_object_id = OBJECT_ID(N’[dbo].[OnlineCourse]‘))
ALTER TABLE [dbo].[OnlineCourse]  WITH CHECK ADD 
       CONSTRAINT [FK_OnlineCourse_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[OnlineCourse] CHECK
       CONSTRAINT [FK_OnlineCourse_Course]
GO


– Define the relationship between CourseGrade and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N’[dbo].[FK_CourseGrade_Course]‘)
       AND parent_object_id = OBJECT_ID(N’[dbo].[CourseGrade]‘))
ALTER TABLE [dbo].[CourseGrade]  WITH CHECK ADD 
       CONSTRAINT [FK_CourseGrade_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseGrade] CHECK
       CONSTRAINT [FK_CourseGrade_Course]
GO


–Define the relationship between CourseGrade and Student.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N’[dbo].[FK_CourseGrade_Student]‘)
       AND parent_object_id = OBJECT_ID(N’[dbo].[CourseGrade]‘))
ALTER TABLE [dbo].[CourseGrade]  WITH CHECK ADD 
       CONSTRAINT [FK_CourseGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseGrade] CHECK
       CONSTRAINT [FK_CourseGrade_Student]
GO


– Define the relationship between CourseInstructor and Course.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N’[dbo].[FK_CourseInstructor_Course]‘)
   AND parent_object_id = OBJECT_ID(N’[dbo].[CourseInstructor]‘))
ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD 
   CONSTRAINT [FK_CourseInstructor_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK
   CONSTRAINT [FK_CourseInstructor_Course]
GO


– Define the relationship between CourseInstructor and Person.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N’[dbo].[FK_CourseInstructor_Person]‘)
   AND parent_object_id = OBJECT_ID(N’[dbo].[CourseInstructor]‘))
ALTER TABLE [dbo].[CourseInstructor]  WITH CHECK ADD 
   CONSTRAINT [FK_CourseInstructor_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[CourseInstructor] CHECK
   CONSTRAINT [FK_CourseInstructor_Person]
GO


– Define the relationship between Course and Department.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
       WHERE object_id = OBJECT_ID(N’[dbo].[FK_Course_Department]‘)
       AND parent_object_id = OBJECT_ID(N’[dbo].[Course]‘))
ALTER TABLE [dbo].[Course]  WITH CHECK ADD 
       CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO


–Define the relationship between OfficeAssignment and Person.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
   WHERE object_id = OBJECT_ID(N’[dbo].[FK_OfficeAssignment_Person]‘)
   AND parent_object_id = OBJECT_ID(N’[dbo].[OfficeAssignment]‘))
ALTER TABLE [dbo].[OfficeAssignment]  WITH CHECK ADD 
   CONSTRAINT [FK_OfficeAssignment_Person] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[OfficeAssignment] CHECK
   CONSTRAINT [FK_OfficeAssignment_Person]
GO


– Create InsertOfficeAssignment stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[InsertOfficeAssignment]‘)
  AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[InsertOfficeAssignment]
  @InstructorID int,
  @Location nvarchar(50)
  AS
  INSERT INTO dbo.OfficeAssignment (InstructorID, Location)
  VALUES (@InstructorID, @Location);
  IF @@ROWCOUNT > 0
  BEGIN
   SELECT [Timestamp] FROM OfficeAssignment
    WHERE
InstructorID=@InstructorID;
  END

END
GO


–Create the UpdateOfficeAssignment stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[UpdateOfficeAssignment]‘)
  AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[UpdateOfficeAssignment]
  @InstructorID int,
  @Location nvarchar(50),
  @OrigTimestamp timestamp
  AS
  UPDATE OfficeAssignment SET
Location=@Location
  WHERE
InstructorID=@InstructorID AND [Timestamp]=@OrigTimestamp;
  IF @@ROWCOUNT > 0
  BEGIN
   SELECT [Timestamp] FROM OfficeAssignment
    WHERE
InstructorID=@InstructorID;
  END

END
GO


– Create the DeleteOfficeAssignment stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[DeleteOfficeAssignment]‘)
  AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[DeleteOfficeAssignment]
  @InstructorID int
  AS
  DELETE FROM OfficeAssignment
  WHERE
InstructorID=@InstructorID;

END
GO


– Create the DeletePerson stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[DeletePerson]‘)
  AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[DeletePerson]
  @PersonID int
  AS
  DELETE FROM Person WHERE PersonID = @PersonID;

END
GO


– Create the UpdatePerson stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[UpdatePerson]‘)
  AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[UpdatePerson]
  @PersonID int,
  @LastName nvarchar(50),
  @FirstName nvarchar(50),
  @HireDate datetime,
  @EnrollmentDate datetime
  AS
  UPDATE Person SET
LastName=@LastName,
    
FirstName=@FirstName,
    
HireDate=@HireDate,
    
EnrollmentDate=@EnrollmentDate
  WHERE PersonID=@PersonID;

END
GO


– Create the InsertPerson stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
  WHERE object_id = OBJECT_ID(N’[dbo].[InsertPerson]‘)
  AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[InsertPerson]
  @LastName nvarchar(50),
  @FirstName nvarchar(50),
  @HireDate datetime,
  @EnrollmentDate datetime
  AS
  INSERT INTO dbo.Person (LastName,
     FirstName,
     HireDate,
     EnrollmentDate)
  VALUES (@LastName,
   @FirstName,
   @HireDate,
   @EnrollmentDate);
  SELECT SCOPE_IDENTITY() as NewPersonID;

END
GO


– Create GetStudentGrades stored procedure.
IF NOT EXISTS (SELECT * FROM sys.objects
            WHERE object_id = OBJECT_ID(N’[dbo].[GetStudentGrades]‘)
            AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’
CREATE PROCEDURE [dbo].[GetStudentGrades]
            @StudentID int
            AS
            SELECT EnrollmentID, Grade FROM dbo.CourseGrade
            WHERE StudentID = @StudentID

END
GO


– Insert data into the Person table.
USE School
GO
SET IDENTITY_INSERT dbo.Person ON
GO
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (1, ‘Abercrombie’, ‘Kim’, ‘1995-03-11′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (2, ‘Barzdukas’, ‘Gytis’, null, ‘2005-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (3, ‘Justice’, ‘Peggy’, null, ‘2001-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (4, ‘Fakhouri’, ‘Fadi’, ‘2002-08-06′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (5, ‘Harui’, ‘Roger’, ‘1998-07-01′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (6, ‘Li’, ‘Yan’, null, ‘2002-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (7, ‘Norman’, ‘Laura’, null, ‘2003-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (8, ‘Olivotto’, ‘Nino’, null, ‘2005-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (9, ‘Tang’, ‘Wayne’, null, ‘2005-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (10, ‘Alonso’, ‘Meredith’, null, ‘2002-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (11, ‘Lopez’, ‘Sophia’, null, ‘2004-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (12, ‘Browning’, ‘Meredith’, null, ‘2000-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (13, ‘Anand’, ‘Arturo’, null, ‘2003-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (14, ‘Walker’, ‘Alexandra’, null, ‘2000-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (15, ‘Powell’, ‘Carson’, null, ‘2004-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (16, ‘Jai’, ‘Damien’, null, ‘2001-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (17, ‘Carlson’, ‘Robyn’, null, ‘2005-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (18, ‘Zheng’, ‘Roger’, ‘2004-02-12′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (19, ‘Bryant’, ‘Carson’, null, ‘2001-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (20, ‘Suarez’, ‘Robyn’, null, ‘2004-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (21, ‘Holt’, ‘Roger’, null, ‘2004-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (22, ‘Alexander’, ‘Carson’, null, ‘2005-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (23, ‘Morgan’, ‘Isaiah’, null, ‘2001-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (24, ‘Martin’, ‘Randall’, null, ‘2005-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (25, ‘Kapoor’, ‘Candace’, ‘2001-01-15′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (26, ‘Rogers’, ‘Cody’, null, ‘2002-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (27, ‘Serrano’, ‘Stacy’, ‘1999-06-01′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (28, ‘White’, ‘Anthony’, null, ‘2001-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (29, ‘Griffin’, ‘Rachel’, null, ‘2004-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (30, ‘Shan’, ‘Alicia’, null, ‘2003-09-01′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (31, ‘Stewart’, ‘Jasmine’, ‘1997-10-12′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (32, ‘Xu’, ‘Kristen’, ‘2001-7-23′, null);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (33, ‘Gao’, ‘Erica’, null, ‘2003-01-30′);
INSERT INTO dbo.Person (PersonID, LastName, FirstName, HireDate, EnrollmentDate)
VALUES (34, ‘Van Houten’, ‘Roger’, ‘2000-12-07′, null);
GO
SET IDENTITY_INSERT dbo.Person OFF
GO


– Insert data into the Department table.
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (1, ‘Engineering’, 350000.00, ‘2007-09-01′, 2);
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (2, ‘English’, 120000.00, ‘2007-09-01′, 6);
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (4, ‘Economics’, 200000.00, ‘2007-09-01′, 4);
INSERT INTO dbo.Department (DepartmentID, [Name], Budget, StartDate, Administrator)
VALUES (7, ‘Mathematics’, 250000.00, ‘2007-09-01′, 3);
GO



– Insert data into the Course table.
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (1050, ‘Chemistry’, 4, 1);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (1061, ‘Physics’, 4, 1);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (1045, ‘Calculus’, 4, 7);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (2030, ‘Poetry’, 2, 2);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (2021, ‘Composition’, 3, 2);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (2042, ‘Literature’, 4, 2);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (4022, ‘Microeconomics’, 3, 4);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (4041, ‘Macroeconomics’, 3, 4);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (4061, ‘Quantitative’, 2, 4);
INSERT INTO dbo.Course (CourseID, Title, Credits, DepartmentID)
VALUES (3141, ‘Trigonometry’, 4, 7);
GO


– Insert data into the OnlineCourse table.
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (2030, ‘http://www.fineartschool.net/Poetry’);
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (2021, ‘http://www.fineartschool.net/Composition’);
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (4041, ‘http://www.fineartschool.net/Macroeconomics’);
INSERT INTO dbo.OnlineCourse (CourseID, URL)
VALUES (3141, ‘http://www.fineartschool.net/Trigonometry’);


–Insert data into OnsiteCourse table.
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (1050, ‘123 Smith’, ‘MTWH’, ’11:30′);
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (1061, ‘234 Smith’, ‘TWHF’, ’13:15′);
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (1045, ‘121 Smith’,’MWHF’, ’15:30′);
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (4061, ’22 Williams’, ‘TH’, ’11:15′);
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (2042, ‘225 Adams’, ‘MTWH’, ’11:00′);
INSERT INTO dbo.OnsiteCourse (CourseID, Location, Days, [Time])
VALUES (4022, ’23 Williams’, ‘MWF’, ‘9:00′);


– Insert data into the CourseInstructor table.
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (1050, 1);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (1061, 31);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (1045, 5);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (2030, 4);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (2021, 27);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (2042, 25);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (4022, 18);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (4041, 32);
INSERT INTO dbo.CourseInstructor(CourseID, PersonID)
VALUES (4061, 34);
GO


–Insert data into the OfficeAssignment table.
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (1, ’17 Smith’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (4, ’29 Adams’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (5, ’37 Williams’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (18, ‘143 Smith’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (25, ’57 Adams’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (27, ‘271 Williams’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (31, ‘131 Smith’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (32, ‘203 Williams’);
INSERT INTO dbo.OfficeAssignment(InstructorID, Location)
VALUES (34, ‘213 Smith’);


– Insert data into the CourseGrade table.
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2021, 2, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2030, 2, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2021, 3, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2030, 3, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2021, 6, 2.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2042, 6, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2021, 7, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2042, 7, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2021, 8, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (2042, 8, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4041, 9, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4041, 10, null);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4041, 11, 2.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4041, 12, null);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4061, 12, null);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 14, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 13, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4061, 13, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4041, 14, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 15, 2.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 16, 2);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 17, null);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 19, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4061, 20, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4061, 21, 2);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 22, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4041, 22, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4061, 22, 2.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (4022, 23, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1045, 23, 1.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1061, 24, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1061, 25, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1050, 26, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1061, 26, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1061, 27, 3);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1045, 28, 2.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1050, 28, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1061, 29, 4);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1050, 30, 3.5);
INSERT INTO dbo.CourseGrade (CourseID, StudentID, Grade)
VALUES (1061, 30, 4);
GO


                                                                           



Analyse de la base



 Le diagramme suivant montre le schéma de la base :



La base à pour vocation de stocker l’ensemble des cours/niveau/professeurs (et leurs relations) d’une école. On trouve dans son modèle logique 8 tables :


  1. Course (Cours)
  2. Course Grade (Année d’étude)
  3. Course Instructor (Relation Professeur/Cours)
  4. Department (Filière)
  5. Office Assignment (Affection géographique)
  6. Online Course (Emplacement du cours en ligne)
  7. Onsite Course (Salle de classe)
  8. Person (Personne)


Nous pouvons donc voir que :



Une personne peut être un étudiant ou un professeur.
Un cours peut être visible sur internet ou être suivit dans une salle de classe à un moment précis de la semaine.
Un cours peut être instruit par un ou plusieurs professeurs (et inversement).
Un professeur est généralement affecté à un emplacement géophraphique spécifique.
Un cours fait partie d’une filière.
Un étudiant étudie dans une année précise un ensemble de cours en rapport.


 


Base de données centrale à tous nos futurs exemples


La base servira d’exemple à tous nos projets manipulant de près ou de loin des données (Entity Framework, ADO.Net Data Services, Silverlight). Il est donc important de la bien la comprendre et de bien la maitriser.
A noter que le script ci-dessus la créé la base “from scratch”. N’hesitez pas à a véroler” pour vos tests puisqu’il est aisé de la recréer.


 


 Valentin Billotte


 


Revenir au sommaire

Création d’un projet à base d’Entity Framework

Revenir au sommaire



Si vous êtes ici c’est que vous avez décidé de vous lancer dans l’aventure Entity Framework. C’est à dire de confier à une technologie intelligente maléable et pérenne la gestion technique et software de votre couche métier. Vous avez donc pret à  ne plus reposer sur du Linq To Sql, sur du Dataset, sur tout autre technologie tentant une émulation conceptuel à partir de schémas logique. Terminé  donc le développement d’objets métiers à la main ou via des outils tiers mal adaptés. Terminés les longues heures d’études de diagrammes UML à étudier les redondances, les sémantiques fonctionnelles et les entités. Terminé l’écriture de procédures stockées ou de code Sql à même le C#. Terminé l’étude d’impact de toute modification de matériel, d’infrastructure ou de couche logique. Terminés la fusion rigide Modèle / Vue / Controleur auquel on était habitué jusque là. Nous entrons dans l’ère du tout automatisé. C’est un pari qui peut sembler risqué mais nous viserons, dans cet article et dans les suivants, à être le plus clair possible en abordant la puissance de cette technologie.


Il est important avant de commencer à lire cet article d’installer et d’assimiler la base de données School dont je donne le script ici.


 Nous commencerons par apprendre à créer un modèle de données métier (ou EDM pour entity data modèle) de deux manières différentes : à l’aide de Visual Studio et à l’aide d’un outil spécialement conçu pour cette tache. Nous étudierons enfin le produit de ces génération en faisant une comparaison avec le modèle logique de la base.


 Création d’un EDM avec Visual Studio


La création d’un EDM sous Visual Studio est relativement simple. Il vous suffit d’ajouter au projet devant contenir les classes de la couche métier un nouvel élément de type Entity Data Modèle. On donne généralement au modèle un nom en rapport avec la problématique métier. Nous appelerons donc notre fichier SchoolModel.edmx.


 


 


Après validation, Visual Studio lance un Wizard qui va nous guider dans la création du modèle à partir des couches logiques issue de la source de donnée.


L’avantage de travailler avec un fichier edmx sous Visual Studio est de profiter de l’environnement de développement intégré. A savoir un Wizard simple qui aide à la création du fichier. Une série de commandes graphiques pour manipuler le modèle conceptuel, une mise à jour intuitive de la couche métier générée à chaque action sur l’edmx. Dans le cas ou le modèle logique de la base est relativement simple et proche de la conception métier, travailler avec Visual Studio et un Edmx est largement suffisant. Toutefois dans le cadre d’opérations compliquées, du besoin de mises à jours constant du modèle ou encore de l’utiisation de techniques spéciales (héritage, fusion de modèles, …) il est préférable de s’orienter vers l’outil edmgen.exe.


 


Création d’un modèle de données avec edmgen.exe


L’outil edmgen.Exe est bien plus puissant dans la génération d’un EDM que Visual Studio et on Wizard. Ici, pas de fichier edmx mais trois fichiers générés : le csdl, le ssdl et le msl (voir ici pour plus d’informations). Chacun de ces fichiers est modifiable séparement à la main. L’outil edmgen.exe permet de travailler de manière global sur ces trois fichiers ou bien de les générer en cascade. L’avantage est bien entendu une plus grande liberté de manoeuvre.


Le désavantage flagrant est qui faut générer l’EDM à la main, qu’il faut traiter les fichier séparement et qu’on ne dispose pas de designer pour visualiser le couche conceptuel générée.


 


 


Analyse du modèle conceptuel et de la couche objet générée.


 


 


 


 


 


Valentin Billotte


Revenir au sommaire