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>.