Using PostgreSQL in modern enterprise web applications
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 20 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - NonCommercial - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/19039 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Producer |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 201218 / 20
1
3
6
7
9
11
12
14
15
16
19
20
00:00
Stiff equationVideoconferencingTupleConditional-access moduleWorld Wide Web ConsortiumEnterprise architectureOpen sourceEnterprise resource planningCustomer relationship managementProduct (business)Multitier architectureFunction (mathematics)View (database)DatabaseIntrusion detection systemGraphical user interfaceClient (computing)ArchitectureElectric currentSoftware frameworkVector potentialComputing platformPresentation of a groupTablet computerRich clientBusiness modelTable (information)Problemorientierte ProgrammierspracheDatabase transactionScripting languageSocial classHierarchySpacetimeScale (map)Integrated development environmentProcedural programmingMethodenbankRow (database)Field (computer science)Data structureGateway (telecommunications)Interface (computing)Set theoryPolymorphism (materials science)Attribute grammarMultiplicationInheritance (object-oriented programming)Object-oriented programmingComputer networkSystem callComa BerenicesWeb 2.0Validity (statistics)Software frameworkState observerSimilarity (geometry)Characteristic polynomialKey (cryptography)Enterprise architectureCartesian coordinate systemInformationTable (information)Projective planeClassical physicsService (economics)BitClient (computing)Right angleDecision theoryCustomer relationship managementNamespaceQuicksortExtension (kinesiology)Row (database)Object-oriented programmingBookmark (World Wide Web)Field (computer science)Line (geometry)Business modelProblemorientierte ProgrammierspracheDatabaseSet theoryData structureWeb browserSocial classGateway (telecommunications)Interface (computing)Object modelMethodenbankPoint (geometry)Open sourceOrder (biology)Product (business)Object-oriented programmingFunctional programmingGame controllerComputer configurationSystem callSoftwareMathematicsReading (process)Inheritance (object-oriented programming)Tablet computerScaling (geometry)Business objectMultitier architectureFront and back endsPhysical systemHierarchyDifferent (Kate Ryan album)Database transactionDiagramScripting languageMultiplication signPolymorphism (materials science)Procedural programmingEmailOrientation (vector space)Computer programmingPerspective (visual)Attribute grammarPerformance appraisalRootSequelEqualiser (mathematics)1 (number)Descriptive statisticsType theoryComputing platformFrame problemMereologyParticle systemFamilyNeuroinformatikMachine codeCASE <Informatik>Service-oriented architecturePattern languageXMLUMLComputer animation
08:51
TupleObject-oriented programmingRelational databaseWellenwiderstand <Strömungsmechanik>Problemorientierte ProgrammierspracheTexture mappingObject-oriented programmingSocial classPolymorphism (materials science)Inheritance (object-oriented programming)Enterprise architectureAxiom of choiceFunction (mathematics)Data structureBusiness modelTable (information)Variety (linguistics)Query languageHierarchyVertex (graph theory)Symmetry (physics)Database normalizationType theoryQuery languageComputer clusterMetropolitan area networkWide area networkComa BerenicesConvex hullStatement (computer science)3 (number)SineSpecial unitary groupConditional-access moduleSummierbarkeitSatelliteInterior (topology)EmulationDirected graphField (computer science)Multiplication signEnterprise architectureType theoryDatabaseAddress spaceMobile appObject-oriented programmingInheritance (object-oriented programming)Cartesian coordinate systemRelational databaseTerm (mathematics)Table (information)VelocityStatement (computer science)Variable (mathematics)Fitness functionNumberPerspective (visual)Social classQuicksortOpen sourceOrder (biology)Volume (thermodynamics)VideoconferencingQuery languageDifferent (Kate Ryan album)Variety (linguistics)HierarchyData structureFunction (mathematics)Object modelRepresentation (politics)BuildingMereologyObject-oriented programmingProcess (computing)Complete metric spaceMappingPoint (geometry)CASE <Informatik>Integrated development environmentSelectivity (electronic)Device driverPolymorphism (materials science)Wellenwiderstand <Strömungsmechanik>Theory of relativityRow (database)SequelAmenable groupPhysical systemArithmetic meanFood energyOpen setStability theoryRight angleBitProgrammer (hardware)Formal grammarSemiconductor memoryGroup actionLine (geometry)Design by contractSet theorySound effectData typeGravitationContext awarenessPresentation of a groupWritingTraffic reportingComputer animation
17:37
Conditional-access moduleMaxima and minimaUniform resource nameSummierbarkeitConvex hullType theory3 (number)Moment of inertiaUser interfaceEmailPhysical lawSineTupleMoving averageTotal S.A.Interior (topology)MathematicsOptical disc driveInclusion mapSpecial unitary groupObject-oriented programmingCASE <Informatik>Business modelProblemorientierte ProgrammierspracheIdeal (ethics)ArchitectureProgramming languageWeb browserWorld Wide Web ConsortiumClient (computing)Process (computing)Real numberObject-oriented programmingSocial classConstructor (object-oriented programming)Inheritance (object-oriented programming)Variable (mathematics)Mass flow rateWide area networkFile formatParameter (computer programming)Condition numberSource codeService (economics)Run time (program lifecycle phase)Computing platformScalabilityComputer networkEvent horizonMobile appPoint cloudDevice driverSoftware testingBinary decision diagramProblemorientierte ProgrammierspracheOcean currentObject-oriented programmingDevice driverAddress spaceService (economics)Software frameworkKey (cryptography)View (database)Client (computing)Programming languageServer (computing)Set theoryPresentation of a groupProcess (computing)Row (database)Web 2.0ResultantWeb browserHash functionTable (information)Cartesian coordinate systemNumberMarginal distributionObject-oriented programmingCASE <Informatik>Condition numberRun time (program lifecycle phase)MultiplicationGraph (mathematics)EmailClassical physicsQuicksortPositional notationEnterprise architectureCharacteristic polynomialDirection (geometry)Slide ruleRight angleType theoryIntegrated development environmentPoisson-KlammerFile formatFood energyExecution unitProjective planeBitMobile appEntire functionInheritance (object-oriented programming)Constructor (object-oriented programming)Query languageGraphical user interfaceDifferent (Kate Ryan album)Statement (computer science)Multitier architectureWebsiteInstance (computer science)Web serviceDecision theoryData storage deviceAngleGroup actionScripting languagePhysical lawAxiom of choiceSocial classSource codeReal numberDesign by contract2 (number)Open setMultiplication signSoftware testingParameter (computer programming)Fundamental theorem of algebraWeb applicationService-oriented architectureArmOffice suitePrototypeMeasurementVector potentialVector spaceWordPattern languageOpen sourceSingle-precision floating-point formatStress (mechanics)Forcing (mathematics)Convex setComputer animation
26:22
Conditional-access moduleTupleInheritance (object-oriented programming)Decision theoryGateway (telecommunications)WritingData conversionSource codeGoogolSoftwarePresentation of a groupComa BerenicesConvex hullVarianceMenu (computing)Software engineeringMetropolitan area networkRelational databaseQueue (abstract data type)View (database)DatabaseRule of inferenceVideo game consoleQuery languageTexture mappingData structureTable (information)Default (computer science)Extension (kinesiology)Data storage deviceWindows RegistryObject-relational mappingUniform resource nameRAIDObject-oriented programmingCustomer relationship managementClient (computing)Level (video gaming)Row (database)Maxima and minimaContent (media)Object-oriented programmingSocial classSpacetimeGroup actionObject-oriented programmingMappingNetwork topologySystem callRegular graphTupleSequelTable (information)Dependent and independent variablesCategory of beingInformation retrievalFunction (mathematics)Row (database)Mobile appSet theoryInformationComputer fileFunctional programmingBuildingDampingStatement (computer science)NumberScripting languageField (computer science)SummierbarkeitView (database)File formatLevel (video gaming)Type theoryAddress spaceCASE <Informatik>Web serviceMathematical optimizationArithmetic meanString (computer science)Right angleChemical equationHypermediaLibrary catalogContext awarenessProcedural programmingNamespaceObject-relational mappingGateway (telecommunications)EmailCodeForm (programming)Process (computing)1 (number)DatabaseGrand Unified TheoryData typeInformation securityPresentation of a groupCartesian coordinate systemParameter (computer programming)Square numberInstance (computer science)Physical systemAreaRevision controlPrincipal idealPoint cloudRule of inferenceQuery languageWindows RegistryClient (computing)Data structureWritingInheritance (object-oriented programming)AuthorizationSoftwareAttribute grammarCommitment schemeDefault (computer science)Video game consoleMultiplication signLetterpress printingOpen set2 (number)Computer animation
35:08
StatisticsSummierbarkeitTupleComa BerenicesConditional-access moduleQuantum mechanicsSource codeGoogle ChromeBeta functionQuery languagePointer (computer programming)Metropolitan area networkExecution unitInfinityServer (computing)Function (mathematics)Social classFluid staticsFunctional programmingAddress spaceUniform resource namePhysical systemBusiness modelView (database)Multiplication signFunctional programmingCountingComputer clusterComputer configurationAddress spaceEmailCASE <Informatik>Object-oriented programmingLimit (category theory)Type theorySystem callFunction (mathematics)ResultantLevel (video gaming)Statement (computer science)Set theoryDatabasePresentation of a groupInterface (computing)Object-oriented programmingQuery languageRow (database)Web 2.0Object-relational mappingProcedural programming2 (number)Social classDatabase transactionClient (computing)Server (computing)CalculationRight angleContext awarenessCondition numberMusical ensembleScripting languageMessage passingFlow separationParameter (computer programming)Point (geometry)Service (economics)Process (computing)Latent heatNumberNamespaceStaff (military)Equaliser (mathematics)Program flowchart
38:55
Google ChromeSource codeServer (computing)Query languageBeta functionSurjective functionWindowTupleConditional-access moduleGrass (card game)Fluid staticsSocial classFunction (mathematics)Function (mathematics)Type theorySystem callSocial classFunctional programmingParameter (computer programming)Address spaceClient (computing)ResultantEnterprise architecturePhysical systemMessage passingComputer animationSource code
39:38
Moving averageWeb browserProduct (business)MereologyCartesian coordinate systemCustomer relationship managementObject-oriented programming1 (number)Video cardPersonal computerFluid staticsCASE <Informatik>Maxima and minimaProcess (computing)AreaSource code
40:24
Wechselseitige InformationDemonExecution unitWide area networkSummierbarkeitPhysical lawMoving averageArmUniform resource nameInfinityValue-added networkLocation-based serviceMetropolitan area networkContext awarenessCircleIncidence algebraSoftware bugMobile appWeb browserOpen setPrice indexStorage area networkPattern language1 (number)Open sourceTablet computerWeb applicationSource code
41:22
GoogolComa BerenicesTupleDatabaseSource codeClient (computing)InformationDatabaseData storage deviceQuicksortArtistic renderingDifferent (Kate Ryan album)Set theoryMobile appTablet computerMaxima and minimaWeb applicationLink (knot theory)PrototypeStructural loadRevision controlWeb browserClient (computing)Type theoryView (database)Entire functionProjective planeResultantCASE <Informatik>Source codePixelTerm (mathematics)Form (programming)Right angleObject-oriented programmingAddress spaceAreaGraphical user interfaceRow (database)WindowAuthorizationMereologySynchronizationObject-oriented programmingMultiplication signSocial classOcean currentBeta functionSoftware testingInternet forumVariety (linguistics)Stability theoryCartesian coordinate systemInterrupt <Informatik>Table (information)Electronic mailing listRepository (publishing)Computer programmingWebsiteWeb 2.0Core dumpCodeLevel (video gaming)BuildingState of matterLine (geometry)Forcing (mathematics)SubsetSoftware developerDependent and independent variablesNoise (electronics)SequelPartial derivativeCategory of beingHypermediaComputer animation
51:14
Metropolitan area networkRow (database)TupleComa BerenicesHand fanVariety (linguistics)Form factor (electronics)Form (programming)Programming languageObject-oriented programmingQuery languageProjective planeView (database)Functional programmingSoftware testingCodeBinary decision diagramBitClient (computing)TesselationSpacetimeTouchscreenMobile appEncapsulation (object-oriented programming)Statement (computer science)Equaliser (mathematics)Image resolutionWindowLine (geometry)Cartesian coordinate systemPlastikkarteMereologySystem callData storage deviceMultiplication signObject-relational mappingCuboidPoint (geometry)Normal (geometry)Data modelText editorObject modelAbstractionFeedbackLevel (video gaming)Extreme programmingDecision theorySequelMethodenbankLogische ProgrammierspracheRight angleSoftware frameworkSource codeGreatest elementText miningPlanningWeb pageComputer programmingBuildingObject-oriented programmingAreaSoftware developerMathematicsPosition operatorPhysical systemSound effectCentralizer and normalizerStreaming mediaStructural loadSource codeProgram flowchart
Transcript: English(auto-generated)
00:25
I hope you're here to attend the Modern Enterprise Web Application Seminar, because that's what I'm talking about. So I'll just give you a little bit of info about Xtupo. We are an open source ERP company.
00:41
We're about 10 years old. We're a profitable, growing company. We have about 300 commercial users. We've got an open source project called Postbooks. We think there's about 30,000 folks using that. Right now, our product is two-tier. We've used Postgres exclusively since the inception of the company.
01:04
That's the back end. And then we've got kind of your classic desktop client front end built with Qt. So users have to install the client to get that to work. You can do some extension with it currently with JavaScript.
01:23
So that's just a real brief bit on who we are. So we're making basically accounting systems and things like that. So what I wanted to do is describe to you kind of this journey we've been through, looking at new technologies as they've been coming out. I wanted to describe the current architectural crossroads that we're at
01:40
and just pass along experiences we've had with new JavaScript tools. These could give you ideas for your own framework. And if you're interested in an ERP-style platform, maybe you'd be interested in getting involved in our community. I'm not here to say that this is the right way or the only way to use these tools.
02:01
It's just solutions that seem to be working for us. And we don't think our problems are unique. So hopefully you'll find some value in them. So as I said at the beginning, we make a C++-based client. And we've resisted going to a web architecture for a long time
02:23
because if you've ever worked with accounting, ERP-type software, it's a struggle to do it on the web because these are transaction-intensive applications. People expect them to be very snappy and have a traditional desktop experience. So we've waited a long time to look at this.
02:43
But with the advent of HTML5, we thought that web technology is finally getting to a place where we can provide a user experience that's equal to or better than a traditional rich desktop client. So that means that we kind of get a start from a clean slate. And starting from a clean slate, we ask ourselves first,
03:01
well, what can we do better than what we're doing now? So on the left is a diagram with what I'm going to call our classic client. It's the C++ one built with QT. It talks directly to Postgres. With the web client, we're basically forced to go to a three-tier architecture
03:20
where we've got the client, and then we need an application and data service that talks to Postgres. Since we're an open-source company, the tools all have to be open-source. And of course, the whole point of this is the web client has to perform as well or better than the native application that we have today. So I just did a little rereading on this topic.
03:42
Martin Fowler has a great book on this called Patterns of Enterprise Application Architecture. And I'm pulling out a little bit of it that I thought was useful for us in sort of framing our thoughts about this change. He describes three different approaches to building large enterprise-style applications.
04:01
The first is a scripted model, another one called, actually, it's table module. I have table model up there. And then a rich domain model. So a transaction script model, just about everything that happens in the application is just some kind of straight SQL call or SQL transaction.
04:21
Just a little example there. We have a couple functions in our database. One's called save contact, and another's called delete the contact. We've got over a thousand functions like this in our database. The reason this is an attractive option is it's simple to get started and procedural. And when we started the company years ago, this is what we went with
04:43
because it was easy to get started. And that's always important when you're trying to start something new. But there's no notion in this sort of architecture of a namespace or class hierarchy. And what happens is when the application starts to get larger, this sort of thing is really hard to scale. It's challenging to manage thousands of different transaction functions.
05:05
So for quite a while, I was actually wanting to pursue what's called the table module approach. In the Microsoft world, you hear this a lot. It's called active record. I think it's popular in the Ruby world too. In this situation, basically the tables become classes.
05:22
So you have one class per table, and you have some kind of gateway interface between your application and the database that organizes this. You manipulate the data in what are called record sets. And the nice thing about this is at least if you're starting fresh,
05:41
it's easy to just spin your whole object hierarchy right off the table structure in the database. It doesn't really do polymorphism that well, and the data field structure can be really limiting. And that was the struggle I had. Because we have this legacy application, it didn't really turn out that the table,
06:00
the relationship between the tables and our business objects were one to one. And trying to force it into that arrangement just never quite worked out. But when we decided to go down this new path, we had to start making some decisions. And then I picked up on this rich domain model.
06:21
So this is a fully object-oriented approach. And when you're using the rich domain model, at least from the client's perspective, you've got data and functions, methods encapsulated in objects. So anybody who's worked on any kind of object-oriented programming is pretty familiar with this.
06:43
It takes advantage of inheritance. You can have multivalued attributes. So any given data field might have more information than just the data itself. It might be including required fields and other faults and other behaviors like that. You make a call on a function in a rich domain model,
07:01
and it may call on a whole network of objects to get an answer. It's not just a simple procedural function like the first one I spoke about. So the idea of this is it scales well when things get complicated. And when I say complicated, I'm thinking of an invoice. An invoice has got for us a header, line items, pricing,
07:23
customer records and things in it. And then, boy, one of my favorites is taxes. I don't know if you've ever dealt with taxes, but taxes are just maddening. They're just terribly complicated. So an object-oriented structure offers the hope of handling that in an elegant way. So the tricky thing about the rich domain model is
07:42
tables and classes are not necessarily the same thing, so you have to have a data mapper. This is just kind of an abbreviated example of what an object model would look like in an application, an enterprise-style application.
08:01
At the root, we've got sort of a general object. In our case, it's used for handling getters and setters and key value observing. And then there's a record object that inherits from that, which has a lot of similar characteristics to a database table, although not exactly. That record management object can do validation
08:21
and privilege control and so on, handles parent-child relationships. And then in our case, we have a document object that inherits from that, which represents sort of high-level documents like customers and orders and things. It handles numbering specifically. Remember I said taxes a minute ago? We have a tax class that inherits from all these other things
08:42
that deals with the tricky business of taxes. And then finally, that all flows into an invoice object, which itself has many objects, right? Remember I mentioned it has a customer, ship to object, invoice line, pricing, money objects, and things like that.
09:03
So you can see how quickly an object model can get pretty sophisticated. But in the case of an enterprise app, that's great, because that's what we need. Now, fortunately, I didn't spend too much time building this part of the presentation, because a lot of it was in the keynote. But I will just make a mention that we spent some time
09:22
looking at NoSQL databases ourselves. And the main reason is at first it looks attractive, because I'm saying we want to go to an object model system, and it's well known that it's kind of tricky business sometimes to get a relational database to work well in an object-oriented environment.
09:43
And since a lot of these NoSQL databases are basically working with objects, it just seemed like a nice fit. So there's this technical term, probably a fair number of you might have heard, called object-relational impedance mismatch,
10:00
that describes what makes it attractive to use, at least from our perspective. I know there were a lot of other reasons mentioned in the keynote. The data mapping I was just talking about, and I'll talk about more in a minute. Actually, most of this is about data mapping. That's a lot of hard work. And, gee, if you could avoid that hard work, it'd sure be nice. Most relational databases lack object features,
10:23
such as sort of this notion of classes and the polymorphism and the inheritance. And then, again, data mapping is hard work with relational databases. So for all those reasons, you think, maybe NoSQL is the way to go.
10:40
Well, in our case, after digging into this a bit, we finally determined that we're fine with the relational database, at least for our application. One of the things I heard in an OSBC conference last year is that there's a couple people talked about three big drivers.
11:00
OSBC is the Open Source Business Conference. Three big drivers behind NoSQL databases are a problem of velocity, volume, and variability. Velocity being, these are to deal with databases where they just get huge volumes of data, like videos. Think about YouTube, getting a large amount of videos.
11:22
Variability is the problem where the data is, you don't know exactly what the structure is going to be, so they want to be able to just throw any kind of structure in the database at will. And then, velocity is similar to volume. It's just coming really fast. Well, those aren't problems that we have to deal with so much.
11:42
Our big problems are having to do more so with output, query output. So in an enterprise application, the data structures are fairly predictable. We know what a contact is. We know what a customer is. We know what a sales order is.
12:00
So it's no problem for us to define these things ahead of time. What we don't know is how people want to report on these things. Every company's got their own way that they want to see the data. So it's more important to us that we be able to query across tables in a variety of ways and that that be easy. I don't know how many of you have played with trying to query across
12:21
nodes and hierarchical models, but it's difficult. It's not something you got to put a lot of work into that. And if you're working with a really big app that's changing all the time with customers always wanting new ways of looking at things, it's not attractive at all. Also, I like this term that C.J. Date came up with in one of his writings,
12:45
that relational databases are application neutral. And this is really the clincher for us. We have this notion of contacts in our database, right? And they're highly normalized. They're all over the application. Contacts belong to customers and vendors and accounts and ship-to addresses.
13:04
So we ask ourselves this question, well, do the contacts belong to an account? An account is like an organization in our application. Or do the accounts belong to contacts? Well, if you're dealing with an object-oriented structure or database, you have to choose. And that's a real problem for us because, as I said a minute ago,
13:24
people are always wanting to see the data in different ways. And we don't want to choose. We want to be able to see the data both ways, where the contacts are members of accounts and accounts are members of contacts. And, of course, the relational theory solves that problem because they're related.
13:42
They're not necessarily parents or children of each other. So one thing that's really neat about the fact that we just happen to be using Postgres already is the fact that it's relational, but it already has some object-oriented features. It's had these for a very long time. Actually from the beginning, really.
14:03
It supports custom data types, object inheritance, and the query language and the schema support these things. Because it does these things, this greatly simplifies our mapping problem. And I'll show you why. How many of you have worked with compound types?
14:22
Anybody? Really? Just a couple? Okay, this is, I think, one of the greatest features about Postgres, and it's really made a huge difference in the way we're using the database now. So typically, when you run a select statement, it looks like this, right?
14:41
Select star from, and cnct is our contact table. Select star from contact, and you just get a basic table, columns and rows. It's what you're used to seeing. Now remember, I said we wanted to go to a rich object model, so we want to start thinking of these things as objects. As it turns out, in Postgres, the contact is not just a table,
15:05
but it is itself a type. So if you do this, select contact from contact, you get all the contacts returned as a single column, and this is Postgres representation of an object here.
15:24
And as it turns out, if you really dig into it, the notion of a class and type in a database, or class in an object system and type in a database, are more or less the same thing. So this is really helpful.
15:41
Now here's a select statement joining on another table. I'm selecting contact, first name, last name, and then I'm picking up everything, address star, everything from an associated address table, right? So again, this is just traditional columns and rows,
16:00
and I'm sure you've all worked with data like this, and we're doing a left join, so we get the addresses if they exist. And this is where it really starts to get interesting. If I do that same SQL statement and take off the dot star, what I get is, so I'm just pulling the address as an object,
16:23
I get my columns for the contact, just like I asked for, and I get the address as objects, or types from a Postgres point of view, they're types. And notice one of the reasons I'm using pgAdmin here is it does a nice job of showing you, see, its address is a type.
16:49
Now this is interesting. Even though they are objects, you can still filter on them. There's a syntax to filter on the types that you've pulled,
17:03
so in this case, this is how the syntax works. You wrap the table in parentheses and then dot and a field you want to filter on. So this is the same query as before, pulling the contact fields, pulling address as a complete type, and I'm filtering on only the contacts where the address is in Denver.
17:27
And the syntax is really simple for these types. It's just open, close, parentheses, and then comma separated values.
17:40
You do if you're querying on this kind of syntax. This is specific to the use of compound types, which is what I'm doing. This is compound types. It would not work. Right.
18:04
Okay, so here, looking at this from another angle, I'm selecting on a too many relationship where I'm trying to get all my contacts and their email addresses and they may have multiple email addresses. There's actually a contact email table. So you can see here, Mike Johnson.
18:23
He's got two different email addresses, so I get two records for Mike Johnson, actually right here. There's actually more than one Mike Johnson as well. Two Mike Johnsons down here, one for each address. If I change my query and cast this select on the email table
18:45
as a subquery wrapped in this array clause, then what I get is, again, the email, contact email as a type. And notice it's got the double brackets. So what it's doing is it's giving me an array.
19:00
So where's, let's see, my Mike, Mike Johnson's right here. And you can see I've got both those email address records wrapped in these curly braces, which is how Postgres represents an array. So this is all pretty good news for us because this starts to get us in a direction
19:21
that can help us get to the rich object domain we want. Oh, and this is just one more slide that shows these last two things combined. So now I'm asking for contact first name, last name, the entire address type, and then the contact email as a too-many relationship.
19:52
So great, we've learned something new that we can do with Postgres. And we're going back to visit a real-world use case. So we've got this classic client on the left,
20:02
and we want to go to this three-tier architecture on the right. Now we made a decision to go with a JavaScript architecture. Specifically, we're using a framework for our client called Blossom, which is brand new, brand spanking new. It's actually a fork of SproutCore.
20:22
I don't know if you've all heard of SproutCore, but it's a rich client framework that was developed by Apple, and it's spun off now as an open source project. So Blossom is SproutCore with a special view layer, and I'll show you that a little bit near the end.
20:40
In the middle, we've chosen Node.js as a tool to build our application and data service that we need. And then we've got some special JavaScript and Postgres I'll be showing you. And you're probably asking yourselves, at least some of you, I know are asking yourselves or crossing your arms and saying, why JavaScript?
21:01
I'll tell you, I work in a C++ shop. JavaScript was not welcome as a first language for our application, at least at first. One thing that was helpful is Qt allows us to extend our current app with JavaScript, so at least we had some experience with it, which opened minds a little bit. But the big driver here is we're trying to make a web app,
21:23
and JavaScript is the language of the web. All the browsers support it natively, and if you're not been paying attention, there is a lot of energy going into JavaScript toolkits now. They're becoming very mature, and they're very powerful. And one of the most important things about it is
21:41
it allows us to do a lot of the processing, the presentation processing on the client side instead of the server generating HTML and processing all these requests. And as you recall at the beginning, I said client-side performance is critical for an application like ours. And as you can tell, you're all staring at the graph here, it happens to be the number one language used on GitHub,
22:02
and notice it's a pretty good margin. There's a lot of work being done with JavaScript. One objection is, yeah, you're trying to build a rich domain, but JavaScript doesn't really have classes, does it? I picked up these quotes from Douglas Crawford
22:20
in his little essay on JavaScript, the world's most misunderstood language. I guess I'll read it to you. I don't need to, but basically it has the characteristics we need to build an object or an environment. I mean, it has objects with data and methods. They can contain other objects. It has constructors and prototype-oriented inheritance.
22:42
So as it turns out, even though it doesn't have objects in the classic sort of C++ definition that we're used to, you can get the behaviors we need to build the domain we want to build. So a lot of talk about JSON these days, right?
23:03
Actually, Douglas Crawford specked that out. Didn't know, but it's the de facto format if you're working with JavaScript, because JSON is JavaScript object notation. So a minute ago, we were just looking at a contact record, right? Well, here's one of those contact records
23:22
that is represented in JSON. You've got the contact at the top, and then an address indented beneath it with the curly braces, and then an email. The email addresses are inside an array here that's bracketed by the two square braces, and here's our two addresses.
23:44
So this is the format we really need to work with if we're going to be using a JavaScript environment. Now, the Blossom data source that we're working with, and when I say Blossom data source, the Blossom client has a data store that's built into it, and that's one of the reasons we chose it.
24:00
It was actually developed for Sprout Core, but it's very, very powerful. So it has this data store, and it expects to talk to a data source, a service, a data service somewhere with an API that looks like this, and there's actually not a lot to it. It's surprisingly simple. There is more to it than I'm showing you,
24:21
but these are the main things. You need to be able to retrieve a record where you ask for the type of record you want. Is it a contact? Is it a customer? What is it? And a specific ID, some kind of primary key. And then we also need to be able to commit a changed record as a unit of work where, again, this is the type of record it is,
24:43
and you pass a data hash with all the pieces of the object. In the case of that invoice, it would include all the objects that are in that invoice. And then we also need to do this fetch call, which is similar to a query, where you want to get a result set with potentially a whole bunch of records.
25:03
Again, pass a type, and in this case you also pass some conditions and parameters, and you get that result set. So the question is, how do we get this out of Postgres? Because Postgres, you use SQL statements. You don't use an API like this. So first we need a data service.
25:20
We chose Node.js. It's built on Chrome's JavaScript runtime. This again, I just pulled this right off the nodejs.org website. It describes what it is. It's a non-IO blocking web service that's designed for serving up applications and data just like this.
25:43
The node in the name nodejs is all about being able to scale this thing up by basically adding more and more instances of it to a server, and they all coordinate well. And of course, everything for Node.js is written in JavaScript.
26:02
One cool thing about Node is it's got a whole bunch of nice packages with really cool stuff, including a Postgres driver that we're using. There's OpenSSL and WebSockets, VDDs, behavioral driven design testing, and these are just a few examples. There are dozens and dozens, actually I think hundreds,
26:22
of packages already written for Node.js. So there's lots of tools already. So you don't have to build everything from scratch. A lot of things are already built. So we took a lot of those tools and put together a web service. It's super high performance, and as I said before, it's designed for distributed cloud apps.
26:43
It's got this non-blocking IO, which means that it runs through the scripts and it doesn't necessarily wait for an answer. For example, if it makes a SQL call, if you're expecting an answer to some particular call for value in a SQL statement, it just keeps running through the whole script
27:01
even without getting that answer. You have to write your applications in Node.js in a way that it responds to the information when it gets it. So you might ask a question, and then you have to put a listener out to wait for the response and process it when you get the answer.
27:20
It means it's super fast, but it's kind of hard to write procedural type code, which we still need some of that. In our first prototype, we tried to use Node.js to do an ORM mapper, and it was just way, way too difficult. Node was really good at some things,
27:41
but this was not one of them. So we decided to just make Node a gateway for data. And we decided to take a look at how we could actually get this stuff out of Postgres directly, taking advantage of the object-oriented attributes
28:01
that Postgres already has. So we found PLV8 hosted on Google code. It was written by Hitoshi Hirata, who's in here somewhere. There he is in the back. So this is the first time I've met him. It's kind of cool. You all get to see me meeting the author of this cool thing we're using. The author is the author of the PC itself. Oh, okay, it's up there now. I knew you said it was coming,
28:22
and that's what it says over here, soon to be in PGXN. I guess it's already there. So he's principal software at Greenplum, and he's going to be doing a presentation here right after mine, so you can hear more from him then. So here's how some of our work
28:40
in Postgres using the V8 engine works. What you get is when you install PLV8, every connection to Postgres can get its own instance of the Google V8 engine. And you can write functions that look something like this. So this function here, it's just that I'm calling example, returns text.
29:02
Now this is an important thing to note that I hadn't been thinking too much about until I started here in the chatter here today. All the responses we get are text. They're not actually JSON objects with the new JSON type that's being worked on, but for what we're doing, the text is fine.
29:20
So this function returns text. I just create a simple, or actually this is that exact SQL statement I did before where it joined on address and picked up the emails. Create a SQL statement. We execute it, and then we use the JSON stringify function call to turn it into a text,
29:40
and I actually use a couple pretty print arguments, so it looks nice, so I can show you what the output looks like. Now I hate having to deviate from the PowerPoint, but it's just really hard to show this without actually running it. Plus I kind of wanted you to see that I'm really doing it. It's not just smoke and mirrors.
30:04
So this is what the output looks like. As I said, it's a text string, but it's formatted like JSON object. In this particular case, it's an array of JSON objects. You see the open square braces at the top.
30:22
This is awesome. I don't know if you picked up one I just did, but I just took a basic SQL statement using built-in Postgres object functionality, and PLV8 spit it out in exactly the format I needed it. This is a huge, huge time saver for data mapping.
30:45
You see the contact on top here. We got an address, and then there's an email. Here's the next contact, address and email, and so on. So it's almost just like free work. I love it.
31:05
So now we've got a way to get objects in the format we need them out of Postgres. So the next question is, great, we can write these queries. We can get them in the format we need them. Where do we put them?
31:22
We're going to have to build this. There's still maps, right? We still have to do the data mapping I talked about at the beginning. So where do we put them? How do we define them? How do we access them with that API I showed you earlier? Well, what we actually are doing here is we're saving those queries in the database with views
31:40
that have CRUD rules. And the really nice thing about storing them as views is they're cataloged and validated, and they're easy to access from any SQL console. You can just run select star from these views and see what's going on with those things. We define them with a JSON mapping structure.
32:02
It maps the tables and relationships and certain rules like required and default values. They can be extended, and they're stored in a database registry. And here's what one looks like. This is our contact mapping.
32:23
So at the top it's got a context of xtuple. The namespace is like our class namespace. And then the type, which is the actual class itself, is contact, and it maps to the contact table. It's got some other stuff that's special to our app.
32:42
Here's where I define privileges. This is a really big deal for us. We have some complicated security enforcement that just does not map directly to tables at all. But we can map it here. And this is the guts of it, really. You've got an array of properties. So I can map for each property on the JavaScript object
33:02
that I'm looking for to a table name. So here's, say, let's pick something obvious, the first name of the contact. It's going to be a string, and it's going to map to the field contact first name. Where it gets really interesting is down below here.
33:22
There's some 2-1 relationships. So these are ones where I want to actually call some other data type as an object. So the SQL will be written, like I showed you earlier, using that table as a type to get me that thing as an object. So that one is defined as a 2-1.
33:41
And then there's also some of them here. Here's the email one. Too many. And what happens is when you load these JSON definitions through some of our function calls, it actually creates the views. All right, I've got to zip along here.
34:04
So the third question is how do we access the data? So in PLV8, we created a whole set of functions in a class called xtorm, where I can install those JSON files, like the one I just showed you. It creates the views. I was talking about, and then there's a fetch function so that I can inspect the properties
34:24
of an object relational map. And then I've got a data layer that mirrors that client API I was talking about. So I can make a request to retrieve record, commit record, and fetch, just like the client asked for.
34:40
And what happens is Node just passes these things through to Postgres. So here's what that looks like in action. Again, I'm going to run it over here, the same thing that was in the presentation.
35:01
And what this is is this is our retrieve record call. Notice it's being called basically with a regular SQL statement. And this is the output. So that's the full output of my contact object based on the ORM map that I just showed you. Here's the address. Here's the emails.
35:26
You can do... That fetch statement that I was talking about earlier is... Sorry about that. That fetch statement I was talking about earlier gives you kind of a NoSQL style of querying. And this is our interface to Postgres to get that kind of querying.
35:41
So I'm calling a function called xtFetch. I pass it the record types and parameters, conditions, role limits and whatnot, some other options. And I know I'm going kind of fast here. Like the presenter said, we'll have this posted up on the web so you guys can really study it a little more closely.
36:05
I don't want to delete it. I just want to run it. Okay. So this is my fetch statement where I said I wanted first name equal to Jake or last name starting with F.
36:22
And I get a big old result set. Again, it's an array. It starts with the braces. And I get several contacts that match the result. So this is really nice. Now I have an interface that looks like something JavaScript can work with because, well, it's written with JavaScript on the Postgres side of things.
36:46
And then there's this really cool awesome bonus which is that we can take those JSON files that I just showed you. We create these JSON ORMs for each of the objects that we want to set up. We pass them into this cruncher
37:01
that pushes them into Postgres and makes the views. And we also have a function that will generate the models for the client side to match. So that's a huge time saver. One definition does both the client and the server side. We're real excited about that. And then finally, there's this question about server side procedures.
37:22
Sometimes, you know, just a regular basic object isn't enough. You need to do something really complicated or specific. Like, we want to check the database to see if there's any records that already have this number. Or we want to do some crazy tax calculation that can really only be done on the server side. Or we want to post an invoice
37:40
which is a huge multi-step transaction that has to be one of these acid-compliant kind of transactions. So we've got this function called dispatch which is implemented to do class-specific function calls. And the way that works is we have what we're calling dispatchable classes.
38:01
We've created a function called xtInstallJavaScript where it takes a namespace, a class, a context, and then some kind of JavaScript. Here what I'm doing is we're defining an address object. We're saying that object is dispatchable which means the client can call it directly.
38:21
And then in this particular case the function is, I want a use count which is how many places is this address being used in the system. And remember I said we have 1200 functions in the database? Well, we don't really have time to rewrite all those right now. So what we can do is just wrap those things up. We already had an address use count function using PL SQL.
38:41
We just wrap it in here and voila, we got a JavaScript exposure of the results of that function. So if I run it, is this the right one? No, it should be four. There it is.
39:06
How many addresses are in the system? Two. That's the output of that function. And you see how it's called. The type of a call is dispatch. I pass in a class name. I pass in the function I want to call and any parameters
39:22
and I get the result. And the really thing about this is it works. So this is our architecture. And I have a client running. So we've built a couple, a start and a couple of the pieces of our product.
39:44
One is the CRM part and the other is the billing part. You can jump into CRM. So what's interesting about this is if you notice this is running in a browser. This is in Chrome. It's kind of fun to talk to you guys because you're actually hopefully interested
40:01
in some of the stuff I was just talking about. I suspect no one's going to care about that. They're all going to care about this. This is an application written in the browser that uses the HTML5 canvas so there's no DOM. This is all being rendered with the PC's graphic. Or in my case, the Mac's graphic engine.
40:22
So I can see all these different objects we have. Incidents are like our bug tracker. Accounts are kind of like companies. And here's our contacts. You can scroll through them. And the neat thing about all this is it feels like a native app
40:44
even though it's in a browser. And you see it's pretty snappy too. Again, that was one of our real important things. It needs to be snappy. HTML5 lets us make a snappy app in a browser. And this also is set up to work on tablets. So you're actually the first ones to ever hear about this outside of Xtuple.
41:04
We're going to be making a more splashy announcement next week at the Open Source Business Conference in San Francisco. So you might hear more about this. We're calling it No HTML Web App.
41:20
So that's the app. And we just made the code public yesterday. So if you're interested in what I'm talking about here, you can go to GitHub. And everything that I was just showing you is in the database repository, Xtuple database.
41:41
And then the other parts, the data source is the node part. You can look at that. And the client is the web app I was just showing you. And then the build tools are how the client gets put together. And then here's links to the other projects. This is probably out of date already, right, Hitoshi? This is on.
42:01
Yeah, that's a fast-moving project. The API kept changing on us. We're like, wait a minute, oh, it's changed again. But it is pretty stable. It's been really stable and solved for us. We're very happy with it. It's just a great project. Node is here. And if you're interested in Blossom, it's here. I think you'll probably be hearing more about Blossom in the next few months.
42:20
We're the first company building an app on Blossom. We sponsored its development. So that's what you'll probably be hearing a lot about in the next few weeks. That's all I have. And it looks like I even made the short time clock here. I have a couple minutes for questions. And then Hitoshi, yours is starting now, isn't it?
42:42
11. Oh, OK, we've got lots of time then. So any questions that I can or can't answer? So when you were selecting the entire table, so when you say select, type, is it possible to?
43:08
If you make a, yes, if you make a type that is a subset. So that's a great question. What we actually have is we've got a set of these objects or views that are the full record. And then we have another set that's sort of a partial view.
43:24
So there's XM address. And then the partial view is XM address info. And that doesn't have everything. And there are lots of cases where you don't want the entire object because it's heavy. It has all the comments, everything about it. And maybe if you just want to browse lists, you just want to see some of it.
43:43
Exactly, yes. The info are designed not to have some of the sensitive information. They're less privilege restrictive. The full views are privilege restricted, so you have to have full privileges to see the whole thing. That's a great question. And the client supports lazy loading.
44:02
So there's two ways you can access an object. One is defining it as a 2-1 regular or 2-1 nested. If you say it's nested, that means Postgres will return the whole object. If you just say 2-1 and it's not nested, it'll just return the key. And our client will actually ask for
44:21
the rest of the data when it needs it. So we have a lot of knobs we can tweak there for performance issues if they come up to decide whether to have Postgres do the work or have the client do the work. No other questions?
44:46
Working with this? The fact that nobody's done it before, so there's no one to talk to, no one to ask, nothing on the forums. I hope with PLV8 going on PGXN now, you all will start looking at this
45:01
and some collective wisdom and knowledge will be building up. Maybe we'll get some feedback. We've got a big working example project now out in public that people can look at and critique and please be kind. I'm sure some people will find things and go, what are you doing?
45:20
So that's the main thing. It's been just a great thing to work with, just have to figure it all out on our own. Did you try going on HTML, CSS before going to Canvas? Yes. Our first prototype was with SproutCore, which is HTML-based. The trouble with HTML DOM rendering
45:43
is when you get in all the different browsers, they're all real persnickety about the DOM. They all deal with it a little differently. So it's really hard to make an app that works in all the browsers well. When you're drawing directly to Canvas, you're drawing pixel by pixel, so you're guaranteed to get the same result in every browser.
46:01
And then the other really big thing was you cannot use the DOM on tablet devices because most of them don't have the horsepower to handle a huge complicated DOM layout like we have. So the beauty of this is that it's designed to work on tablets. It works on my iPad. I don't know if we'll have it ready by our Monday show,
46:22
but we have all the pieces ready for it to work on Explorer 10 for Windows 8 and Mozilla, and it works on Safari too.
46:43
Yes, so this is what differentiates Blossom from SproutCore. Blossom is all DOM-based, and what the author of Blossom did is he ripped the DOM part off and replaced it with the Canvas view layer. So that's what makes Blossom blossom.
47:01
It's SproutCore with the Canvas view layer. Nope, you've got to have the latest and greatest. Like I said, Explorer 10, the latest Chrome. I know this would be a non-starter for a lot of companies doing sort of public customer-facing things, but remember, we're making an application, right?
47:21
And it's no big deal for us to tell prospective customers or users, look, you've got to have the latest and greatest browser. So it's not for everybody, but eventually these technologies will be ubiquitous anyway, so in the long run it won't matter.
47:41
You mentioned WebSockets. What's your experience? You mentioned WebSockets. WebSockets, yeah. What's your experience with it? So this is an area that I didn't program at all. I didn't write any of the Node part. One of my coworkers did. But what I do love about it is it's so forgiving.
48:00
Our current C++ application, if there's any kind of interruption in the connection, you're done. You're dead. You lose whatever you were doing. It just says log out and start over. So with WebSockets, you can lose the connection and come back, and it'll pick up where it left off. So it's pretty nice.
48:20
What's that? They're working very stable. We're using the stable version of Node and all the packages. It has been stable for us. This is all new though, right? We aren't even in beta stage. Our goal is to have a beta in the next few weeks. So we'll be doing a lot more testing, load testing, and just general user testing in the next few weeks.
48:43
But so far, so good.
49:01
This version is not set up to do that. We know we can, and it's sort of in our long term nice to have plans, but the first thing's first. We've just got to get our app reconstructed in this environment. But yeah, absolutely. That's something we're very interested in is offline.
49:22
Well, the nice thing is it ought to be minimal because we're already using this JavaScript API. The idea of Blossom slash SproutCore is the data store can connect to a variety of data sources. So we could just have it basically switch from the live data source to an offline one that runs the SQLite database or whatever,
49:48
and then just sync it up later. So that's definitely in our sights, but not for a little while. Do you think that the browser is really an HTML rendering engine?
50:01
I think that you're throwing away all that. Does Blossom have class app just as a stand-alone client? That's Canvas and not all of the other baggage that HTML brings. That's the whole, it already has thrown out all the baggage. Everything in Blossom is the Canvas package. So usually I run your application in a browser. Oh, right, yeah. You could theoretically run it in something like WebGap,
50:22
and one of the original goals was to actually be able to compile it as a native app. I'm not sure if we're going to get to that or pull that off, but theoretically that should work. It's just, again, there's so many things to do and only so much time. But yeah, the guy who wrote Blossom really wants to do that.
50:41
He wants to have native compiled apps too. But at a minimum it would be really easy to port it into WebGap. And in fact, even on my iPad, if I just save the link to home, it opens up just like an app. It's pretty cool. It's like it's not in a browser at all.
51:04
Blossom, you know, form factors, even if I'm running out of my iPad versus my 20th monitor, does it have a different view for them? They're different styles, so to speak. Right. So that's largely a design issue. We've tried to design this in a way so it will fit on a variety of form factors, even a phone.
51:24
Do you use CSS or descriptive? No. Do you use descriptive? What kind of language do you use? Do I have to code each pixel? You can use CSS, but we're mostly trying to cover it by designing things in a modular way
51:41
so that the form factor is not really relevant as long as you can get it all in there. So like these tiles, it's kind of pulling a page out of the Windows playbook. And this is tricky because I don't have all the normal screen space I would have here. Yeah, low resolution.
52:04
But... Yeah, I can't get to the... I can't get to the scroller at the bottom, which gets to your form factor problem. We're doing work on that area, but the idea is you can scroll the tiles over,
52:21
and then the tiles will try and fit as many on the screen as it can for the device, and then you just scroll through them. Yeah.
52:53
Well, our current project is a quarter million lines of C++ and probably an equal amount of SQL, and we're at about 150,000 so far with this.
53:03
I don't know that it's going to save lines of code one for one, but I think the levels of abstraction and encapsulation is way, way better. So it ought to be much easier to add on to this infrastructure than the current one we have, just because it's object-oriented.
53:22
So easier, not necessarily less lines. I'm just using text editors. I mean, I know you can run JavaScript and Eclipse and all that, but most of my guys just use Vim. I use TextMate.
53:41
It's pretty straightforward. So I mentioned in the JS Node bit that there's a package called vows, and actually there's another one called Jasmine, but we're using vows, that is based on the BDD behavioral design,
54:00
behavioral design development, something like that. But anyway, the idea is we're running these data models through vows. We've built a couple test runs of this, and the idea is we want to run all of our data models through that vows application. So you basically build the test,
54:20
and it just goes through and runs, pushes the data through, looks for expected results, and shows you what passed and what failed, and it should be pretty quick. So that runs on the Node.js part. And there's ways of writing tests for just the views and everything too here on the client.
54:41
I haven't got to that yet, but we're fast approaching that point. But it should all be done with vows.
55:27
Well, what I liked about this is there's an ORM layer built in PLV8 that creates the queries. And what's kind of neat about this is whenever I've had a problem with the queries not working that well,
55:44
I just go change it in that one, how they're defined in that one place, and they all kind of get the fix. Where in our current system, when we decide we've been doing something wrong, we have to go fix it in a thousand places. So the nice thing is it's centralized code, and it's easy to fix.
56:01
And then a lot of it, you just have to put careful thought into the design of your ORMs. Like I said, there are different extremes you can go to. Do you want to load everything as one giant object or just pieces of it and then lazily load the rest of the objects? These are design decisions you'd have to make depending on what you're trying to do and what kind of performance feedback you're getting
56:21
when you try to actually run it. Yeah, the idea is you don't have to write any SQL, right? The only place there's SQL is in those dispatch functions. And even those, I would like to rewrite them so they just use the ORMs themselves and not SQL statements.
56:47
Oh, yeah. There's a lot of testing in a CF. Oh, it's completely non-translatable. We have to rebuild everything. But I will say this. The thing that blows us away the most is this is way faster than our C++ app.
57:03
Way faster. Which is strange for us because we've always been kind of C++. Yeah, this is the only way to write a real program, but I have to admit this is faster and it's just going to spur us to finish rebuilding the framework as fast as possible
57:21
because we think people are probably going to start complaining why is your older classic app so slow? It's because a lot of it's done in the client. This has a data store. That whole object model is in the data store so it caches a lot more than our current client does
57:41
and that saves on a lot of query calls. It's also just hyper. Like I said, this thing started at Apple and there's some pretty smart guys at Apple as you know so it's pretty highly tuned out of the box. Anybody else?
58:02
Okay, well I guess it's about time to switch rooms. Thank you all for coming.