This is an automated email from the git hooks/post-receive script. New commit to branch bow-v2-go in repository bow. See https://gitlab.nuiton.org/chorem/bow.git commit 5353481ae896cc18c6a534a3a1a1e6fcea005dff Author: Benjamin <poussin@codelutin.com> Date: Sat Apr 18 14:02:37 2020 +0200 ajout de valeur par default dans la base ajout du support de orderby, orderdesc et first en parametre de recherche --- migrate/001_init_schema.sql | 10 +++++----- pkg/constant/const.go | 26 +++++++++++++++++++++++--- pkg/http/bookmarkResource.go | 15 +++++++++++++-- pkg/model/bookmark.go | 2 +- pkg/model/user.go | 4 ++-- pkg/repository/bookmarkRepository.go | 25 ++++++++++++++++++++++--- pkg/repository/database.go | 19 +++++++++++++++++++ web/src/components/SearchInput.vue | 34 +++++++++++++++++++++++++--------- web/src/router/index.js | 13 ++++++++----- web/src/views/BookmarkEdit.vue | 23 +++++++++++++++-------- web/src/views/Home.vue | 17 +++++++++++++---- 11 files changed, 146 insertions(+), 42 deletions(-) diff --git a/migrate/001_init_schema.sql b/migrate/001_init_schema.sql index ba0b1a0..40500a2 100644 --- a/migrate/001_init_schema.sql +++ b/migrate/001_init_schema.sql @@ -47,10 +47,10 @@ CREATE TABLE bowUser ( emails TEXT[], unconfirmedEmails jsonb, -- [{email: string, token: string, creationDate: date}] authenticationInfo jsonb, -- AuthenticationInfo, - autoScreenshot BOOLEAN, - autoFavicon BOOLEAN, - maxTagInCloud SMALLINT, - maxResult SMALLINT, + autoScreenshot BOOLEAN DEFAULT false, + autoFavicon BOOLEAN DEFAULT false, + maxTagInCloud SMALLINT DEFAULT 25, + maxResult SMALLINT DEFAULT 25, actions jsonb -- [{"action": string, "prefix": string, "suggest": string}] ); @@ -91,7 +91,7 @@ CREATE TABLE bookmark ( authenticationInfo jsonb, -- AuthenticationInfo, favicon BYTEA, screenshot BYTEA, - visit INTEGER, + visit BIGINT DEFAULT 0, lang regconfig NOT NULL DEFAULT 'english'::regconfig ); diff --git a/pkg/constant/const.go b/pkg/constant/const.go index 85b46a9..5f3ce80 100644 --- a/pkg/constant/const.go +++ b/pkg/constant/const.go @@ -39,14 +39,34 @@ const WithCount = "with-count" /* Tags query parameter name for tags search */ -const Tags = "t" +const Tags = "tags" /* Fulltext query parameter name for fulltext search */ -const Fulltext = "f" +const Fulltext = "fulltext" /* -Fulltext query parameter name for id search +Fulltext query parameter name for fulltext search +*/ +const Query = "query" + +/* +ID query parameter name for id search */ const ID = "id" + +/* +OrderBy query parameter name for select order sorting +*/ +const OrderBy = "orderby" + +/* +OrderDesc query parameter name for select order sorting direction descending +*/ +const OrderDesc = "orderdesc" + +/* +First query parameter name first result +*/ +const First = "first" \ No newline at end of file diff --git a/pkg/http/bookmarkResource.go b/pkg/http/bookmarkResource.go index 56ed3dd..df8c0a5 100644 --- a/pkg/http/bookmarkResource.go +++ b/pkg/http/bookmarkResource.go @@ -22,7 +22,18 @@ func getBookmarks(w http.ResponseWriter, r *http.Request) { tags := query.Get(constant.Tags) fulltext := query.Get(constant.Fulltext) - json, err := repository.BookmarkJSON(currentUserID, id, tags, fulltext) + log.Println("query: ", query) + orderBy := query.Get(constant.OrderBy) + orderDesc, err := strconv.ParseBool(query.Get(constant.OrderDesc)) + if err != nil { + orderDesc = false + } + first, err := strconv.ParseInt(query.Get(constant.First), 10, 16) + if err != nil { + first = 0 + } + + json, err := repository.BookmarkJSON(currentUserID, id, tags, fulltext, orderBy, orderDesc, int(first)) if err != nil { if utils.Is404(err) { // on a rien retrouve, on renvoie une collection vide @@ -41,7 +52,7 @@ func getBookmark(w http.ResponseWriter, r *http.Request) { currentUserID := r.Context().Value(constant.User).(model.BowUser) id := mux.Vars(r)["id"] - json, err := repository.BookmarkJSON(currentUserID, id, "", "") + json, err := repository.BookmarkJSON(currentUserID, id, "", "", "", false, 0) if err != nil { utils.Throw(w, err) return diff --git a/pkg/model/bookmark.go b/pkg/model/bookmark.go index 3adaa7e..6280a7c 100644 --- a/pkg/model/bookmark.go +++ b/pkg/model/bookmark.go @@ -21,6 +21,6 @@ type Bookmark struct { AuthenticationInfo AuthenticationInfo `json:"authenticationinfo,omitempty"` Favicon []byte `json:"favicon,omitempty"` Screenshot []byte `json:"screenshot,omitempty"` - Visit int `json:"visit,omitempty"` + Visit int64 `json:"visit,omitempty"` Lang string `json:"lang,omitempty"` } diff --git a/pkg/model/user.go b/pkg/model/user.go index 8eb2de9..235fda6 100644 --- a/pkg/model/user.go +++ b/pkg/model/user.go @@ -22,8 +22,8 @@ type BowUser struct { AuthenticationInfo AuthenticationInfo `json:"authenticationinfo,omitempty"` AutoScreenshot bool `json:"autoscreenshot"` AutoFavicon bool `json:"autofavicon"` - MaxTagInCloud int8 `json:"maxtagincloud,omitempty"` - MaxResult int8 `json:"maxresult,omitempty"` + MaxTagInCloud int16 `json:"maxtagincloud,omitempty"` + MaxResult int16 `json:"maxresult,omitempty"` Actions []Action `json:"actions,omitempty"` } diff --git a/pkg/repository/bookmarkRepository.go b/pkg/repository/bookmarkRepository.go index f67577e..1ea4e0a 100644 --- a/pkg/repository/bookmarkRepository.go +++ b/pkg/repository/bookmarkRepository.go @@ -15,17 +15,36 @@ import ( /* BookmarkJSON retourne le bookmark au format json -FIXME mettre la limit a la limit de recherche souhaite par l'utilisateur */ -func BookmarkJSON(currentUser model.BowUser, id string, tags string, fulltext string) (string, error) { +func BookmarkJSON(currentUser model.BowUser, id string, tags string, fulltext string, orderBy string, orderDesc bool, first int) (string, error) { var result string var err error + + maxResult := currentUser.MaxResult + if maxResult == 0 || maxResult > 1000 { + maxResult = 100 + } + + // normalize orderBy to prevent SQL injection + orderBy = bookmakFields[orderBy] + if orderBy == "" { + orderBy = "creationdate" + } + + orderDirection := "" + if orderDesc { + orderDirection = "desc" + } + + log.Printf("search bookmark id: %v, tags: '%v', fulltext: '%v', orderBy: %v, orderDesc: %v, first:%v", id, tags, fulltext, orderBy, orderDesc, first) if id != "" { q := &query{sql: `WITH __all AS (select * from bookmark where id=$1) SELECT json_agg(__all.*) as j FROM __all`} result, err = q.QueryString(currentUser, id) } else { tagsJSON := "{" + strings.Join(strings.Fields(tags), ",") + "}" - q := &query{sql: `WITH __all AS (select * from bookmark where tags @> $1::text[] LIMIT 10) SELECT json_agg(__all.*) as j FROM __all`} + q := &query{sql: fmt.Sprintf(` + WITH __all AS (select * from bookmark where tags @> $1::text[] order by %s %s OFFSET %d LIMIT %d) SELECT json_agg(__all.*) as j FROM __all + `, orderBy, orderDirection, first, maxResult)} result, err = q.QueryString(currentUser, tagsJSON) } diff --git a/pkg/repository/database.go b/pkg/repository/database.go index 221c313..8f1e3e7 100644 --- a/pkg/repository/database.go +++ b/pkg/repository/database.go @@ -6,6 +6,7 @@ import ( "log" "os" "os/signal" + "reflect" "strings" "time" @@ -25,10 +26,16 @@ type errorLineExtract struct { Text string // Text of the line without a new line character. } +// key: struct field name or database field name in lower case +// value: database field name (to use in query) +var bookmakFields = make(map[string]string) + /* Init initialise la connexion a la base en utilisant */ func Init(databaseURL string, doMigration bool) { + computeFieldAvailable() + if doMigration { migrateDatabase(databaseURL) } @@ -50,6 +57,18 @@ func Init(databaseURL string, doMigration bool) { } +func computeFieldAvailable() { + val := reflect.Indirect(reflect.ValueOf(&model.Bookmark{})) + + for i := 0; i < val.Type().NumField(); i++ { + field := val.Type().Field(i) + fieldName := strings.ToLower(field.Name) + jsonName := strings.ToLower(strings.Split(field.Tag.Get("json"), ",")[0]) + bookmakFields[fieldName] = jsonName + bookmakFields[jsonName] = jsonName + } +} + // extractErrorLine takes source and character position extracts the line // number, column number, and the line of text. // diff --git a/web/src/components/SearchInput.vue b/web/src/components/SearchInput.vue index 5ff28e7..ebabd94 100644 --- a/web/src/components/SearchInput.vue +++ b/web/src/components/SearchInput.vue @@ -30,10 +30,16 @@ class SearchInput extends Vue { tags = '' fulltext = '' query = '' + orderby = 'creationdate' + orderdesc = false + first = 0 mTags = '' mFulltext = '' mQuery = '' + mOrderby = 'creationdate' + mOrderdesc = false + mFirst = 0 sameValue(a, b) { let result = a === b @@ -48,11 +54,19 @@ class SearchInput extends Vue { console.log('search', this.mTags, this.mFulltext) if ( !this.sameValue(this.mTags, this.tags) || - !this.sameValue(this.mFulltext, this.fulltext) + !this.sameValue(this.mFulltext, this.fulltext) || + !this.sameValue(this.mQuery, this.query) || + !this.sameValue(this.mOrderby, this.orderby) || + !this.sameValue(this.mOrderdesc, this.orderdesc) || + !this.sameValue(this.mFulltext, this.first) ) { let query = {} this.mTags && (query.t = this.mTags) this.mFulltext && (query.f = this.mFulltext) + this.mQuery && (query.q = this.mQuery) + this.mOrderby && (query.orderBy = this.mOrderby) + this.mOrderdesc && (query.orderDesc = this.mOrderdesc) + this.mFulltext && (query.first = this.mFirst) this.$router.push({ name: 'Home', query }) } } @@ -62,24 +76,26 @@ class SearchInput extends Vue { } update() { - this.tags = this.$route.query.t - this.fulltext = this.$route.query.f - this.query = this.$route.query.q + this.tags = this.$route.query.tags + this.fulltext = this.$route.query.fulltext + this.query = this.$route.query.query + this.orderby = this.$route.query.orderby + this.orderdesc = this.$route.query.orderdesc + this.first = this.$route.query.first this.mTags = this.tags || '' this.mFulltext = this.fulltext || '' this.mQuery = this.query || '' + this.mOrderby = this.orderby || '' + this.mOrderdesc = this.orderdesc || false + this.mFirst = this.first || 0 } beforeMount() { - console.log('beforeMounted SearchInput') + console.log('beforeMount SearchInput') this.update() } - beforeMUpdate() { - console.log('beforeMUpdate SearchInput') - this.update() - } } export default SearchInput diff --git a/web/src/router/index.js b/web/src/router/index.js index e8fd93a..6357cd7 100644 --- a/web/src/router/index.js +++ b/web/src/router/index.js @@ -18,9 +18,12 @@ const routes = [ component: Home, props: route => ({ id: route.query.id, - tags: route.query.t, - fultext: route.query.f, - query: route.query.q + tags: route.query.tags, + fulltext: route.query.fulltext, + query: route.query.query, + orderby: route.query.orderby, + orderdesc: route.query.orderdesc, + first: route.query.first }) }, { @@ -32,8 +35,8 @@ const routes = [ uri: route.query.uri, description: route.query.description, tags: route.query.tags, - privateAlias: route.query.privateAlias, - publicAlias: route.query.publicAlias, + privatealias: route.query.privatealias, + publicalias: route.query.publicalias, lang: route.query.lang }) }, diff --git a/web/src/views/BookmarkEdit.vue b/web/src/views/BookmarkEdit.vue index 2c98a6a..d94ecd4 100644 --- a/web/src/views/BookmarkEdit.vue +++ b/web/src/views/BookmarkEdit.vue @@ -29,8 +29,8 @@ import { Component, Prop, Vue } from 'vue-property-decorator' 'uri', 'description', 'tags', - 'privateAlias', - 'publicAlias', + 'privatealias', + 'publicalias', 'lang' ], components: {} @@ -40,8 +40,8 @@ class BookmarkEdit extends Vue { @Prop uri @Prop description @Prop tags - @Prop privateAlias - @Prop publicAlias + @Prop privatealias + @Prop publicalias @Prop lang errorMsg = '' @@ -49,8 +49,8 @@ class BookmarkEdit extends Vue { uri: this.uri, description: this.description, tags: this.tags, - privateAlias: this.privateAlias, - publicAlias: this.publicAlias, + privatealias: this.privatealias, + publicalias: this.publicalias, lang: this.lang } @@ -73,9 +73,16 @@ class BookmarkEdit extends Vue { save() { let promise - if (!Array.isArray(this.bookmark.tags)) { - this.bookmark.tags = this.bookmark.tags.split(/[,\s]/) + if (this.bookmark.tags && !Array.isArray(this.bookmark.tags)) { + this.bookmark.tags = this.bookmark.tags.split(/[,\s*]/) } + if (this.bookmark.privatealias && !Array.isArray(this.bookmark.privatealias)) { + this.bookmark.privatealias = this.bookmark.privatealias.split(/[,\s*]/) + } + if (this.bookmark.publicalias && !Array.isArray(this.bookmark.publicalias)) { + this.bookmark.publicalias = this.bookmark.publicalias.split(/[,\s*]/) + } + if (this.bookmark.id) { promise = this.$fetch.put(`/bookmarks/${this.bookmark.id}`, this.bookmark) } else { diff --git a/web/src/views/Home.vue b/web/src/views/Home.vue index 830d9fd..3cd7e0d 100644 --- a/web/src/views/Home.vue +++ b/web/src/views/Home.vue @@ -18,7 +18,7 @@ import Bookmark from '@/components/Bookmark' @Component({ name: 'Home', - props: ['id', 'tags', 'fulltext', 'query'], + props: ['id', 'tags', 'fulltext', 'query', 'orderby','orderdesc','first'], components: { Bookmark } }) class Home extends Vue { @@ -26,10 +26,16 @@ class Home extends Vue { @Prop tags @Prop fulltext @Prop query + @Prop orderby + @Prop orderdesc + @Prop first mTags = this.tags || '' mFulltext = this.fulltext || '' mQuery = this.query || '' + mOrderby = this.orderby || '' + mOrderdesc = this.orderdesc || false + mFirst = this.first || 0 errorMsg = '' bookmarks = [] @@ -37,9 +43,12 @@ class Home extends Vue { fetchBookmark() { let searchParams = new URLSearchParams() this.id && searchParams.append('id', this.id) - this.mTags && searchParams.append('t', this.mTags) - this.mFulltext && searchParams.append('f', this.mFulltext) - this.mQuery && searchParams.append('q', this.mQuery) + this.mTags && searchParams.append('tags', this.mTags) + this.mFulltext && searchParams.append('fulltext', this.mFulltext) + this.mQuery && searchParams.append('query', this.mQuery) + this.mOrderby && searchParams.append('orderby', this.mOrderby) + this.mOrderdesc && searchParams.append('orderdesc', this.mOrderdesc) + this.mFirst && searchParams.append('first', this.mFirst) this.$fetch.get(`/bookmarks?${searchParams.toString()}`).then( data => { -- To stop receiving notification emails like this one, please contact chorem.org SCM administrator <admin+scm@chorem.org>.