branch feature/spgeed updated (3b442887 -> 8150f004)
This is an automated email from the git hooks/post-receive script. New change to branch feature/spgeed in repository pollen. See https://gitlab.nuiton.org/chorem/pollen.git from 3b442887 spgeed doc : adding with desc new 8150f004 adding appearing properties The 1 revisions listed above as "new" are entirely new to this repository and will be described in separate emails. The revisions listed as "adds" were already present in the repository and have only been added to this reference. Detailed log of new commits: commit 8150f004ce524b54be0fb9cd1a2f8ed3ef91fbd1 Author: Killian <killian.herbreteau@epitech.eu> Date: Mon Nov 18 10:33:06 2019 +0100 adding appearing properties Summary of changes: spgeedDAO.md | 145 +++++++++++++++++++++++++++++++++++------------------------ 1 file changed, 86 insertions(+), 59 deletions(-) -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.
This is an automated email from the git hooks/post-receive script. New commit to branch feature/spgeed in repository pollen. See https://gitlab.nuiton.org/chorem/pollen.git commit 8150f004ce524b54be0fb9cd1a2f8ed3ef91fbd1 Author: Killian <killian.herbreteau@epitech.eu> Date: Mon Nov 18 10:33:06 2019 +0100 adding appearing properties --- spgeedDAO.md | 145 +++++++++++++++++++++++++++++++++++------------------------ 1 file changed, 86 insertions(+), 59 deletions(-) diff --git a/spgeedDAO.md b/spgeedDAO.md index 7c188e7d..a30c9007 100644 --- a/spgeedDAO.md +++ b/spgeedDAO.md @@ -1,12 +1,11 @@ # Create Spgeed Dao -There was 2 ways to create `Spgeed Dao` : +There is 2 ways to create `Dao` with Spgeed : - Using Spgeed annotation ``` java public interface PollenUserSpgeedDao { - String getUsers = "SELECT * FROM pollenuser"; - @Select(sql = getUsers) + @Select(sql = "SELECT * FROM pollenuser") PollenUser[] getUsers(); } ``` @@ -22,7 +21,7 @@ public interface PollenUserSpgeedDao implements SpgeedDao { } } ``` -We decided to use `Spgeed Annotation` to avoid overwriting line and adding complexity to Spgeed Dao to keep them easy to read. +We decided to use `Spgeed Annotation` to avoid overwriting line and overflow complexity to Spgeed Dao to keep them easy to read. ## Simple request !!! warning @@ -69,17 +68,17 @@ public interface PollenUserSpgeedDao { ``` ## Joining table -To retrieve object from database we have to connect multiple table. +To retrieve object from database we have to connect several table. -For the example PollenUser have a default emailaddress and multiple emailaddresses (reference to PollenUserEmailAddress). +For the example PollenUser have a default email address and several email addresses (reference to PollenUserEmailAddress). ``` java PollenUser user = /* getUser */ -user.getEmailAddresses(); // EmailAddresses -user.getDefaultEmailAddress(); // DefaultEmailAddress +user.getEmailAddresses(); // Email Addresses +user.getDefaultEmailAddress(); // Default Email Address ``` ### Using WITH -Before joining table we have to retrieve all the data of every table to join. -Example for user where we get every date from `pollenUserEmailAddress` : +Before joining table, we have to retrieve all data from the attached table. +Example for user where we retrieve all data from `pollenUserEmailAddress` : ``` sql WITH emails_agg AS ( SELECT e.topiaId, e.emailAddress, e.pollenuser @@ -87,11 +86,11 @@ WITH emails_agg AS ( ); ``` ### Join -After getting `emails_agg` filled with `pollenUserEmailAddress` table we have to join those tables. +After getting `emails_agg` filled with `pollenUserEmailAddress` table we have to join this table. -Every pollen join have to be `LEFT JOIN`. +Every Pollen join have to be linked with `LEFT JOIN`. -Retrieving date: +Retrieving data: * One to one `1.1` link : ``` sql json_agg(demail.*)->0 AS defaultEmailAddress @@ -103,10 +102,10 @@ COALESCE(json_agg(emails.*) FILTER (WHERE emails.pollenUser IS NOT NULL), null) Here's a complete request of pollenUser joined by pollenUserEmailAddress: ``` sql WITH emails_agg AS ( - SELECT e.topiaId, e.emailAddress, e.pollenuser + SELECT e.topiaId, e.emailAddress, e.pollenuser -- etc FROM pollenuseremailaddress e ), users_agg AS ( - SELECT pu.topiaid, pu.name, + SELECT pu.topiaid, pu.name, -- etc json_agg(demail.*)->0 AS defaultEmailAddress, COALESCE(json_agg(emails.*) FILTER (WHERE emails.pollenUser IS NOT NULL), null) AS emailAddresses FROM pollenuser pu @@ -118,59 +117,87 @@ WITH emails_agg AS ( ## Dao Structure How to structure dao : +* Giving table nickname ``` java -import static org.chorem.pollen.persistence.entity.PollenUser.*; - -public interface PollenUserSpgeedDao { +String userName = "pu"; // User nickname + +/* +** For the case of pollenUserEmailAddress that have several references +** to pollenUser, using many nickname can help +*/ +String emailsName = "pes"; // Email Addresses nickname +String demailName = "pde"; // Default Email Address nickname +``` +* Giving each Dao aggregation name +``` java +String aggName = "users_agg"; // User aggregation name, used to refer to the user aggregation +``` +* Setting some shortcut (useful to join table) +``` java +String userId = username + "." + PORPERTY_TOPIA_ID; - String userName = "pu"; // User nickname +String userForeignKeyDefaultEmail = userName + "." + PROPERTY_DEFAULT_EMAIL_ADDRESS; - String aggName = "users_agg"; // User aggregation name, used to refer to the user aggregation +String demailTable = aggName + " " + demailName; +String emailsTable = aggName + " " + emailsName; - String properties = username + "." + PROPERTY_TOPIA_ID + ", " // See below - + userName + "." + PROPERTY_TOPIA_VERSION + ", " // etc -} +String emailsForeignKey = emailsName + ".pollenuser"; ``` - -In the properties String must appear : -- Every properties no foreign key (ex : topiaId, topiaVersion, pollenUser.name) -- Every foreign key that doesn't appear in the object (ex : pollenUserEmailAddress.pollenuser), they will be reused to join table. -Exemple : +* Setting table properties +``` java +String properties = tableNickname + "." + PROPERTY_TOPIA_ID + ", " + + tableNickname + "." + PROPERTY_TOPIA_VERSION + ", " // etc +``` +You can also statically import the Dao entity to avoid overwriting. +So you will be able to use the entity `PROPERTY` as follow : ``` java -public abstract class AbstractTopiaEntity implements TopiaEntity { - protected String topiaId; // Must appear +import static org.chorem.pollen.persistence.entity.PollenUserEmailAddress.*; - protected long topiaVersion; // Must appear +public interface PollenUserEmailAddressSpgeedDao extends PollenSpgeedDao { - protected Date topiaCreateDate = new Date(); // Must appear + String properties = PROPERTY_TOPIA_ID + ", " + + PROPERTY_TOPIA_VERSION + ", " + + PROPERTY_TOPIA_CREATE_DATE + ", " + + PROPERTY_EMAIL_ADDRESS + ", " + + PROPERTY_PGP_PUBLIC_KEY + ", " + + PROPERTY_VALIDATED + ", " + + "pollenuser"; } -public abstract class PollenUserAbstract extends AbstractTopiaEntity implements PollenUser { - - protected String name; // Must appear - - protected boolean administrator; // Must appear - - protected String language; // Must appear - - protected String password; // Must appear - - protected String salt; // Must appear - - protected boolean banned; // Must appear - - protected Date gtuValidationDate; // Must appear - - protected Date premiumTo; // Must appear - - protected boolean canCreatePoll; // Must appear - - protected Collection<UserCredential> userCredential; // Can't appear (reference to userCredential table) - - protected PollenResource avatar; // Can't appear (reference to pollenResource table) +``` +In the properties String must appear every properties from the table except every reference to another table that doesn't appear in the final object. - protected PollenUserEmailAddress defaultEmailAddress; // Must not appear +To find table column type this command under your PostgreSQL data base: +``` sql +$> \d PollenUser +``` +##### Table "public.pollenuser" +| Column | Type | Appear | +|---------------------|:---------------------------:|:------:| +| topiaid | character varying(255) | $true | +| topiaversion | bigint | $true | +| topiacreatedate | timestamp without time zone | $true | +| name | character varying(255) | $true | +| administrator | boolean | $true | +| language | character varying(255) | $true | +| password | character varying(255) | $true | +| salt | character varying(255) | $true | +| banned | boolean | $true | +| gtuvalidationdate | timestamp without time zone | $true | +| premiumto | timestamp without time zone | $true | +| avatar | character varying(255) | $false | +| defaultemailaddress | character varying(255) | $false | +| cancreatepoll | boolean | $true | - protected Collection<PollenUserEmailAddress> emailAddresses; // Can't appear (reference to pollenUserEmailAddress table) -} +``` sql +$> \d PollenUserEmailAddress ``` -Those who `Can't appear` cause they are reference from another table must appear in their own request to join in those one. \ No newline at end of file +##### Table "public.pollenuseremailaddress" +| Column | Type | Appear | +|-----------------|:---------------------------:|:------:| +| topiaid | character varying(255) | $true | +| topiaversion | bigint | $true | +| topiacreatedate | timestamp without time zone | $true | +| emailaddress | character varying(255) | $true | +| pollenuser | character varying(255) | $true | +| pgppublickey | text | $true | +| validated | boolean | $true | \ No newline at end of file -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.
participants (1)
-
chorem.org scm