diesel-rs / diesel_full_text_search Goto Github PK

View Code? Open in Web Editor NEW

69.0 69.0 32.0 60 KB

License: MIT License

Rust 100.00%

diesel_full_text_search's People

Contributors

Stargazers

Watchers

diesel_full_text_search's Issues

did to_tsvector support different regconfig?

when I am using this code to do the full text search:

let ts_vector = to_tsvector("'dolphinzhcfg'::regconfig, coalesce(title, '')");

seems postgresql treat the 'dolphinzhcfg'::regconfig, coalesce(title, '') as plain text. What am I missing? does it support different regconfig except english?

Add `websearch_to_tsquery` and `phraseto_tsquery`

https://www.postgresql.org/docs/current/functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE

TsConfiguration integer values cause cache miss

While trying to insert a row using a struct with a TsConfiguration field, i run into the following error:

cache lookup failed for text search configuration 12832

The issue seems to be the integer value doesn't refer to any regconfig oid on my system. I used to following query to check which valid regconfig oid values exist:

SELECT oid, cfgname FROM pg_ts_config;

That returned:

3748	simple
13156	arabic
13158	armenian
13160	basque
13162	catalan
13164	danish
13166	dutch
13168	english
13170	finnish
13172	french
13174	german
13176	greek
13178	hindi
13180	hungarian
13182	indonesian
13184	irish
13186	italian
13188	lithuanian
13190	nepali
13192	norwegian
13194	portuguese
13196	romanian
13198	russian
13200	serbian
13202	spanish
13204	swedish
13206	tamil
13208	turkish
13210	yiddish

Using any of those integers directly in an INSERT like below works just fine, but if I use the integers defined on the TsConfiguration in this crate then I run into just the same issue as I get while running the query through rust & diesel.

INSERT INTO tag(name, description, lang, is_public)
VALUES ('test-lang', 'this is my english description', 13178, false);

I'm using postgres version:

psql (PostgreSQL) 15.3

I'm unsure if these oid values are expected to vary across different systems or if they can be expected to be the same, atleast with major versions, I'll try and look into it. If oid integers are not consistent or are expected to change often or , perhaps using a string value for inserting TsConfiguration would be more stable?

is there any demo to show hot to use to_tsquery_with_search_config

is there any demo to show how to use to_tsquery_with_search_config?

Add License file

Some projects require a license file in order to use the crate.

Current status of this crate

HI, What is the current status of this crate? and is it abandoned?

Add documentation, tests

Add a Readme
Add basic integration tests
Make it compile with #[deny(missing_docs)]

the trait `NonAggregate` is not implemented for `char`

when I using this code to do a full text search:

#[macro_use]
extern crate diesel;

use diesel::{QueryDsl, RunQueryDsl};
use diesel_full_text_search::{plainto_tsquery, to_tsvector};
use rust_wheel::config::db::config;
use diesel_full_text_search::TsVectorExtensions;
use crate::model::diesel::dict::dict_models::Article;

mod model;

fn main() {
    use model::diesel::dict::dict_schema::article as article_table;
    let connection = config::establish_connection();
    let mut query = article_table::table.into_boxed::<diesel::pg::Pg>();
    let tsvector = to_tsvector("'dolphinzhcfg', title");
    let a: char = char::from_u32(0xDE01).unwrap();;
    query = query.filter(&tsvector.matches(a));
    let query_result = query.load::<Article>(&connection);
}

shows error:

error[E0277]: the trait bound `char: QueryFragment<Pg>` is not satisfied
  --> src/main.rs:18:19
   |
18 |     query = query.filter(&tsvector.matches(a));
   |                   ^^^^^^ the trait `QueryFragment<Pg>` is not implemented for `char`
   |
   = help: the following other types implement trait `QueryFragment<DB>`:
             <&'a T as QueryFragment<DB>>
             <() as QueryFragment<DB>>
             <(A, B) as QueryFragment<__DB>>
             <(A, B, C) as QueryFragment<__DB>>
             <(A, B, C, D) as QueryFragment<__DB>>
             <(A, B, C, D, E) as QueryFragment<__DB>>
             <(A, B, C, D, E, F) as QueryFragment<__DB>>
             <(A, B, C, D, E, F, G) as QueryFragment<__DB>>
           and 186 others
   = note: required because of the requirements on the impl of `QueryFragment<Pg>` for `diesel_full_text_search::dsl::predicates::Matches<to_tsvector_t<diesel::expression::bound::Bound<Text, &str>>, char>`
   = note: 1 redundant requirement hidden
   = note: required because of the requirements on the impl of `QueryFragment<Pg>` for `&diesel_full_text_search::dsl::predicates::Matches<to_tsvector_t<diesel::expression::bound::Bound<Text, &str>>, char>`
   = note: required because of the requirements on the impl of `query_builder::where_clause::WhereAnd<&diesel_full_text_search::dsl::predicates::Matches<to_tsvector_t<diesel::expression::bound::Bound<Text, &str>>, char>>` for `query_builder::where_clause::BoxedWhereClause<'_, Pg>`
   = note: required because of the requirements on the impl of `FilterDsl<&diesel_full_text_search::dsl::predicates::Matches<to_tsvector_t<diesel::expression::bound::Bound<Text, &str>>, char>>` for `diesel::query_builder::BoxedSelectStatement<'_, (BigInt, BigInt, Text, Text, Text, BigInt, BigInt, diesel::sql_types::Nullable<Text>, BigInt, diesel::sql_types::Nullable<Text>, Integer, diesel::sql_types::Nullable<Integer>), table, Pg>`

For more information about this error, try `rustc --explain E0277`.
warning: `rust-learn` (bin "rust-learn") generated 2 warnings

this is the dict_schema.rs:

table! {
    article (id) {
        id -> Int8,
        user_id -> Int8,
        title -> Varchar,
        author -> Varchar,
        guid -> Varchar,
        created_time -> Int8,
        updated_time -> Int8,
        link -> Nullable<Varchar>,
        sub_source_id -> Int8,
        cover_image -> Nullable<Varchar>,
        channel_reputation -> Int4,
        editor_pick -> Nullable<Int4>,
    }
}

and this is the dict_models.rs:

// Generated by diesel_ext

#![allow(unused)]
#![allow(clippy::all)]

use std::io::Write;
use diesel::deserialize::FromSql;
use diesel::pg::Pg;
use diesel::serialize::{Output, ToSql};
use diesel::sql_types::Jsonb;
use rocket::serde::Serialize;
use serde::Deserialize;
use chrono::DateTime;
use chrono::Utc;
use crate::model::diesel::dict::dict_schema::*;

#[derive(Queryable,QueryableByName,Debug,Serialize,Deserialize,Default,Clone)]
#[table_name = "article"]
pub struct Article {
    pub id: i64,
    pub user_id: i64,
    pub title: String,
    pub author: String,
    pub guid: String,
    pub created_time: i64,
    pub updated_time: i64,
    pub link: Option<String>,
    pub sub_source_id: i64,
    pub cover_image: Option<String>,
    pub channel_reputation: i32,
    pub editor_pick: Option<i32>,
}

this is the dependencies of the demo:

[package]
name = "rust-learn"
version = "0.1.0"
edition = "2018"

[dependencies]
rocket = { version = "=0.5.0-rc.2", features = ["json"] }

serde = { version = "1.0.64", features = ["derive"] }
serde_json = "1.0.64"
serde_derive = "1.0"
# database
diesel = { version = "1.4.7", features = ["postgres","serde_json"] }
dotenv = "0.15.0"
jsonwebtoken = "7"
chrono = "0.4"
config = "0.11"
ring = "0.16.20"
md5 = "0.7.0"
data-encoding = "2.3.2"
bigdecimal = "0.3.0"
# reddwarf public component
rust_wheel = { git = "https://github.com/jiangxiaoqiang/rust_wheel.git" }
diesel_full_text_search = "1.0.1"

Am I missing something?

Is there any other way to define TsVector besides import_types?

Hi, I want to use diesel_full_text_search::TsVector only for a table!. But when I add it to diesel.toml in import_types, it adds to all table!s. I want to add it only for my table!. Because I have more than 150 tables. I don't want to update schema.rs manually either. So is there any other way to use TsVector?

Tsvector type name mismatch

When I ran diesel migration redo, it generated a type with the name Tsvector in the schema.rs file, while this library exposes a type called TsVector.

That seems wrong. Am I doing something wrong here?

I upgraded from an older version and can't get it to build

I've been working for a couple hours to try to figure this out. It used to work with an older version, I think like 0.8.

My code:

let query_items: Vec<&str> = query.trim().split_whitespace().collect();
let query = query_items.join(" & ");
let tsquery = to_tsquery(query);
let tsvector = to_tsvector("'english', name || ' ' || organization");
let result = try!(league::table.filter(&tsvector.matches(&tsquery)).load::<LeagueDao>(self.conn));
Ok(result)

And the first part of the not-so-obvious error

error[E0277]: the trait bound `diesel_full_text_search::dsl::predicates::Matches<diesel_full_text_search::to_tsvector_t<diesel::expression::bound::Bound<diesel::types::Text, &str>>, &diesel_full_text_search::to_tsquery_t<diesel::expression::bound::Bound<diesel::types::Text, std::string::String>>>: diesel::Expression` is not satisfied
  --> src/repo/league/league.rs:80:41
   |
80 |         let result = try!(league::table.filter(&tsvector.matches(&tsquery)).load::<LeagueDao>(self.conn));
   |                                         ^^^^^^ the trait `diesel::Expression` is not implemented for `diesel_full_text_search::dsl::predicates::Matches<diesel_full_text_search::to_tsvector_t<diesel::expression::bound::Bound<diesel::types::Text, &str>>, &diesel_full_text_search::to_tsquery_t<diesel::expression::bound::Bound<diesel::types::Text, std::string::String>>>`
   |
   = note: required because of the requirements on the impl of `diesel::Expression` for `&diesel_full_text_search::dsl::predicates::Matches<diesel_full_text_search::to_tsvector_t<diesel::expression::bound::Bound<diesel::types::Text, &str>>, &diesel_full_text_search::to_tsquery_t<diesel::expression::bound::Bound<diesel::types::Text, std::string::String>>>`
   = note: required because of the requirements on the impl of `diesel::query_dsl::filter_dsl::FilterDsl<&diesel_full_text_search::dsl::predicates::Matches<diesel_full_text_search::to_tsvector_t<diesel::expression::bound::Bound<diesel::types::Text, &str>>, &diesel_full_text_search::to_tsquery_t<diesel::expression::bound::Bound<diesel::types::Text, std::string::String>>>>` for `diesel::query_builder::SelectStatement<schema::league::table>`

why the rust diesel did not throw error when execute the error syntex sql in postgresql 13

I am using rust (v1.62) diesel to do a full text search, now I found a problem that when I pass the wrong sql to PostgreSQL, the rust diesel app did not throw error, that's makes me confusing. This is the main.rs:

#[macro_use]
extern crate diesel;

use diesel::{debug_query, ExpressionMethods, QueryDsl, QueryResult, RunQueryDsl};
use diesel::query_builder::BoxedSelectStatement;
use diesel_full_text_search::{to_tsquery, to_tsvector};
use diesel_full_text_search::TsVectorExtensions;
use rust_wheel::config::db::config;
use crate::model::diesel::dict::dict_models::Article;
use rust_wheel::common::query::pagination::PaginateForQueryFragment;
use crate::model::diesel::dict::dict_schema::article::created_time;

mod model;

fn main() {
    use model::diesel::dict::dict_schema::article as article_table;
    let mut query = article_table::table.into_boxed::<diesel::pg::Pg>();
    let connection = config::establish_connection();
    let ts_query = to_tsquery("历史");
    let ts_vector = to_tsvector("'dolphin1zhcfg', title");
    query = query.filter(ts_vector.matches(ts_query));
    let query = query
        .order(created_time.desc())
        .paginate(1,false)
        .per_page(10);
    let sql1 = debug_query::<diesel::pg::Pg, _>(&query);
    println!("sql:{}",sql1);
    let query_result = query.load_and_count_pages::<Article>(&connection);

    println!("ok")


}

this is the dict_models.rs:

// Generated by diesel_ext

#![allow(unused)]
#![allow(clippy::all)]

use std::io::Write;
use diesel::deserialize::FromSql;
use diesel::pg::Pg;
use diesel::serialize::{Output, ToSql};
use diesel::sql_types::Jsonb;
use rocket::serde::Serialize;
use serde::Deserialize;
use chrono::DateTime;
use chrono::Utc;
use crate::model::diesel::dict::dict_schema::*;

#[derive(Queryable,QueryableByName,Debug,Serialize,Deserialize,Default,Clone)]
#[table_name = "article"]
pub struct Article {
    pub id: i64,
    pub user_id: i64,
    pub title: String,
    pub author: String,
    pub guid: String,
    pub created_time: i64,
    pub updated_time: i64,
    pub link: Option<String>,
    pub sub_source_id: i64,
    pub cover_image: Option<String>,
    pub channel_reputation: i32,
    pub editor_pick: Option<i32>,
}

this is the dict_scheme.rs:

table! {
    article (id) {
        id -> Int8,
        user_id -> Int8,
        title -> Varchar,
        author -> Varchar,
        guid -> Varchar,
        created_time -> Int8,
        updated_time -> Int8,
        link -> Nullable<Varchar>,
        sub_source_id -> Int8,
        cover_image -> Nullable<Varchar>,
        channel_reputation -> Int4,
        editor_pick -> Nullable<Int4>,
    }
}

and this is the project full dependencies:

[package]
name = "rust-learn"
version = "0.1.0"
edition = "2018"

[dependencies]
rocket = { version = "=0.5.0-rc.2", features = ["json"] }

serde = { version = "1.0.64", features = ["derive"] }
serde_json = "1.0.64"
serde_derive = "1.0"
# database
diesel = { version = "1.4.7", features = ["postgres","serde_json"] }
dotenv = "0.15.0"
jsonwebtoken = "7"
chrono = { version = "0.4", features = ["serde"] }
diesel_full_text_search = "1.0.1"
rust_wheel = { git = "https://github.com/jiangxiaoqiang/rust_wheel.git" }

I write the wrong sql deliberately to test the full text procedure. To my surprise, the rust diesel did not capture error information and just return 0 records. I think it should throw some error from PostgreSQL 13 shows that the sql have a bad grammer. Why did this happen? what should I do to make it works as expect? when I copy the execute sql and execute in DBeaver, it shows error. the dolphin1zhcfg did not exists. This is the article table DDL:

-- public.article definition

-- Drop table

-- DROP TABLE public.article;

CREATE TABLE public.article (
	id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
	user_id int8 NOT NULL,
	title varchar(512) NOT NULL,
	author varchar(256) NOT NULL,
	guid varchar(512) NOT NULL,
	created_time int8 NOT NULL,
	updated_time int8 NOT NULL,
	link varchar(512) NULL,
	pub_time timestamptz NULL,
	sub_source_id int8 NOT NULL,
	cover_image varchar(1024) NULL,
	channel_reputation int4 NOT NULL DEFAULT 0,
	editor_pick int4 NULL DEFAULT 0,
	permanent_store int2 NOT NULL DEFAULT 0,
	CONSTRAINT article_id_seq_pk PRIMARY KEY (id),
	CONSTRAINT article_title_sub_source_id_key UNIQUE (title, sub_source_id)
);
CREATE INDEX article_sub_source_id_idx ON public.article USING btree (sub_source_id);
CREATE INDEX article_title_gin ON public.article USING gin (to_tsvector('english'::regconfig, (title)::text));
CREATE INDEX article_title_zh_gin ON public.article USING gin (to_tsvector('dolphinzhcfg'::regconfig, (title)::text));
CREATE INDEX idx_article_created_time ON public.article USING btree (created_time);

Am I missing something?

Update sqlfunction macro usage

Unfortunately sql_function has changed its usage (https://docs.rs/diesel/1.4.3/diesel/macro.sql_function.html) when using new versions of diesel. I don't know exactly which version was the first one with this change.

If we got it right this was not supposed to be a breaking change according to Semantic Version.

I'm working on it and I'll open the PR, but I'm wondering if there is a way to make compatible with older versions of diesel.

Update / insert TsVector value?

Is it possible to update or insert a TsVector value? I would expect to be able to do something like:

    use crate::schema::mytable::dsl as my;
    insert_into(my::mytable)
       .values((
             my::some_text.eq(some_text),
             my::ts.eq(to_tsvector(some_text),
        ))
        .execute(db)?;

But I get the error that the method eq is not found in the column declaration. i'd say some trait is missing, but I don't (yet) know if my code is just missing some use statement or if TsVector is missing some impl statement. Maybe it needs to impl SingleValue?

Concat on TsVectors needs to emit extra parentheses

I'm trying to concat multiple TsVectors together to search multiple fields but the emitted SQL isn't as the database needs it to be. Roughly I'm doing:

let query = mytable.into_boxed();
let search_q = plainto_tsquery("test");
let search_fields = to_tsvector(name).concat(to_tsvector(description));
query = query.filter(search_q.matches(search_fields));

The SQL emitted is:

SELECT "mytable"."name", "mytable"."description"
FROM "mytable"
WHERE plainto_tsquery($1) @@ to_tsvector("mytable"."name") || to_tsvector("mytable"."description");

Postgres needs this last line to be:

WHERE plainto_tsquery($1) @@ (to_tsvector("mytable"."name") || to_tsvector("mytable"."description"));

Error logs from Postgres:

2018-04-11 16:29:08.072 UTC [7232] user@db ERROR:  operator does not exist: boolean || tsvector at character 12345
2018-04-11 16:29:08.072 UTC [7232] user@db HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

add to_tsvector and to_tsquery that accepts search configuration

There is a version of both functions that accepts language configuration. It's is mandatory to have a language configuration if you want to use GIN indexes.

I'm open the PR for that as well but #12 should be merged first.

Diesel Migration does not properly import diesel full text search

When I run diesel migration run with the following SQL, the Tsvector is not properly imported and initialized.

ALTER TABLE articles ADD COLUMN text_searchable_article_col tsvector NOT NULL;

UPDATE articles SET text_searchable_article_col = to_tsvector('english', title || ' ' || description);

CREATE INDEX textsearch_idx ON articles USING GIN (text_searchable_article_col);

CREATE TRIGGER tsvectorupdateproducts BEFORE INSERT OR UPDATE
ON articles FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(text_searchable_article_col,'pg_catalog.english', title, description);

Results in the schema..

      table! {
          use diesel::sql_types::*;
          /// Representation of the `articles` table.
          ///
          /// (Automatically generated by Diesel.)
          articles (urn_title) {

...
          /// The `text_searchable_article_col` column of the `articles` table.
          ///
          /// Its SQL type is `Tsvector`.
          ///
          /// (Automatically generated by Diesel.)
          text_searchable_article_col -> Tsvector,
      }

I don't know if this is a feature that has not yet been made, or a bug, but it is definitely something that I think that should be added, so that everytime I run the migration I don't have to manually do this.

diesel-rs / diesel_full_text_search Goto Github PK

diesel_full_text_search's People

Contributors

Stargazers

Watchers

Forkers

diesel_full_text_search's Issues

Recommend Projects

Recommend Topics

Recommend Org