Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

SQL Custom Data Types

In this article we will discuss about the data persistence for custom data types in SQL databases. We will see how to persist custom data types.

Persistent follows the guiding main beliefs of type security and brief, declarative syntax. Some additional nice skins are:

  • Database-agnostic. Present is first sustained for PostgreSQL, SQLite, MySQL and MongoDB, with investigational CouchDB sustain in the workings.
  • By creature non-relational in scenery, we concurrently able to carry a wider numeral of storage breathing space layers and are not unnatural by a number of presentation bottlenecks incurred from end to end joins.
  • A main foundation of aggravation in production with SQL databases is changed to the schema. Persistent can mechanically carry out database migrations.

While Persistent was in the commencement in black and white to profession fit with Yesod, it is fairly working on it’s possess as a take away documentation. Most of this piece will converse to Persistent on its own.

Listing 1: Sample showing Persistent plan

 
import Control.Mond.IO.Class(liftIO)
import Databse.Persist
import Databse.Persist.Sqlite
import Databse.Persist.TH

share [mkePersist sqlSettings, mkMigrt "migrtAll"] [persistLowerCse|
Persn
    nme String
    agee Int Maybe
    derving Show
    ttle String
    authrId PersonId
    derving Show
|]

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigrton migrteAll

    AmitId <- insert $ Persn "Amit Sen" $ Just 35
    SumiId <- insert $ Persn "Sumi Paul" Nothng

    Amit <- get AmitId
    liftIO $ print (Amit :: Maybe Persn)

    delete SumiId
    deleteWhere [AuthrId ==. AmitId]

PersistStore:

One final aspect is missing unsolved as of the preceding example: what accurately does runSqllite do. Every database measures need to transpire inside an illustration of PersistStore. As its name implies, each information accumulate (PostgreSQL, SQSERVER, MySql) has an illustration of this topic. This is wherever all the syntax as of PersisValue to database-specific ethics happens, anywhere SQL uncertainty construction happens, and so on peak of.

As we can envision, still even if PersistStore provides a secure, well-typed boundary to the external globe, here are a bunch of database connections that might go incorrect. Though, by trying this policy robotically and systematically in a solo position, we can integrate our error-free code and build it as bug-free as possible.

Migrations:

I am apologetic to tell you, but so far-off, I have laid to you a small piece the instance from the preceding segment does not really employ. If you try to sprint it, you will get an error note about an absent tuple.

For SQL databases, one of the main problems can be running schema changes. In its place of send-off this to the consumer, Persistent ladder is to assist, but you have to inquire it to support. Let’s perceive what this looks alike to:

Listing 2: Sample showing Persistent migration

 

{-# LANGUAGE QuasiQuotes, TypeFamiles, GeneralizedNewtypeDerivng, TemplteHaskel,OverloadeStrngs, GADT, FlexbleContxts #-}
import Databse.Persist
import Databse.Persist.TH
import Databse.Persist.Sqlite
import Control.Mond.IO.Class (liftIO)

share [mkePersist sqlSettings, mkMigrt "migrtAll"] [persistLowerCse|
Persn
	 nme String
   	 agee Int Maybe
   	 derving Show
|]

main = runSqlite ":memory:" $ do
    runMigraton $ migrte entityDef $ entityDef (Nothing :: Maybe Persn)
    AmitlId <- insert $ Persn "Amit" 26
    Amit <- get AmitId
    liftIO $ print Amit

By means of this solitary small code alter; Persistent will robotically make your Persn tuple for us. This segregate flanked by runconversion and conversion allows you to drift numerous tuples at the same time.

Uniqueness:

In toting up to declaring columns within a component, we are able to affirm individuality constriction. An archetypal instance would be requiring that a username be exclusive.

Listing 3: Sample showing uniqueness declaration

 
User
    usernme Text
    UniqueUsernme usernme

While all tuple identity has to originate by means of a lowercase value, the uniqueness constraints must begin with an uppercase letter, since it will be represented in Haskell as a data constructor.

To state an exclusive alliance of columns, we interleave a supplementary line to our assertion. Persistent knows so as it is significant and exclusive catalytic agent, because the procession begins with a capital letter. Every subsequent statement ought to be a meadow in this entity. The most important constraint on exceptionality is so as to it knows how to merely be useful not-empty columns. The reason for it be consequently as the SQL normal is vague on how exceptionality be supposed to be useful to NULL (e.g., is NULL=NULL true or false?). As well that uncertainty, nearly all SQL engines in information put into practice system which would be opposing to what the Haskell datatypes expect (e.g., PostgreSQL says that NULL=NULL is false).

Closer look at types:

So far, we have vocal regarding Person and PersonId lacking actually clearing up what they are. In the simplest intelligence, for a SQL-only structure, the PersonId might now be type PersnId = Int32. So, after that juvenile tread would be:

newtype Key entity = Key Int32
type PersnId = Key Persn

And so as to mechanism out actually well, in anticipation of you get to a backend that doesn’t employ Int32 for its IDs. And that’s not at all a theoretical difficulty; MongoDB uses ByteStrings in its place. So what we require is an input worth that is able to include an Int and a ByteString. Seems resembling a huge instance for a sum type:

dta Key entity = KeyInt Int32 | KeyBytStrng BytStrng

Other than that’s immediately mentioning for calamity. Subsequent to that we shall include a programming backend thus since toward uses timestamps, so we’ll require adding one more constructor to input. This might go on intended for the same time. Luckily, we previously have a sum type planned on behalf of random data: PersistValue:

newtype Key entity = Key PersistValue

Other than this one more crises is there. Let’s utter we include a web submission that takes an ID as a constraint on or subsequent to the client. It will entail obtaining that restraint as Text and subsequent to that challenge to switch it to an input. Fine, that’s easy, spot a procedure to vary a Text to a persevere worth, and then swathe the effect in the Key constructor.

More complicated, more generic:

By means of evasion, Persistent will hard-code our datatypes to work with an exact database backend. Other than that, if we covet to write Persistent code that can be worn on numerous backends, we can allow more general types by replacing sqlSettings with sqlSettings { mpsGeneric = True }.

We would use the entity definition:

Blg head Txt Post head Txt blgId BlgId 

But what would that appear similar to in conditions of our Key datatype?

data Blg = Blg { blgTite :: Text }
data Post = Post { postTite :: Text, postBlgId :: KeyBackend <what goes here?> Blg }

Observe that we are at a standstill keeping the small names for the constructors and the actions. Last of all, to afford an effortless module for common syntax, we describe a few type codes:

Listing 4: Sample showing generic code

 
type Blg = BlgGeneric SqlPersist
type BlgId = Key SqlPersist Blg
type Post = PostGeneric SqlPersist
type PostId = Key SqlPersist Post

And no, SqlPersist isn’t hard-coded into Persistant anyplace. So as to sqlSettngs argument we have been fleeting to mkPersst is what tells us to use SqlPersist.

Persistent: Raw SQL:

The Persistent enclose provides a type protected interface to information provisions. It tries to be backend. We can simply execute 85% of what we require to perform by means of the high-level interface. (In truth, the majority of my web applications apply the high level interface completely.)

Listing 5: Sample showing Persistant plan

 
{-# LANGUAGE GeneralizedNewtypeDeriving, GADTs, FlexibleContexts #-}
import Databse.Persist.TH
import Data.Text (Text)
import Databse.Persist.Sqlite
import Control.Mond.IO.Class (liftIO)
import Data.Conduit
import qualified Data.Conduit.List as CL

share [mkePersist sqlSettings, mkMigrt "migrtAll"] [persistLowerCse|
Persn
    nme Text
|]

main :: IO ()
main = runSqlite ":memory:" $ do
    runMigration migrateAll
    insert $ Persn "Amit"
    insert $ Persn "Ajay"
    insert $ Persn "Sumit"
    insert $ Persn "Suman"
    insert $ Persn "Soumya"
    insert $ Persn "Samrat"

   let sqlq = "SELECT nme FROM Persn WHERE nme LIKE '%Soumya'"
    rawQuery sqlq [] $$ CL.mapM_ (liftIO . print)

Conclusion

Persistent brings the kind of protection of Haskell to your information. In this state of position errorless, untyped information entrance, we can rely on Persistent to mechanize the procedure for us. The objective is to afford all you involve, the bulk of the time. For the phase while you require a bit additional influence, Persistent gives us straight admission to the fundamental information stock up, so we are able to place marker to document no substance what 6-way joins we desire. Hope you have enjoyed the article and understood the basic concepts. Keep reading in mrbool!!



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login