In this recipe how to create tables in db? Manually or pony can?
If you create three empty schemata inside a single postgresql server, and specify two-element _table_ name for each entity, Pony should be able to create all tables automatically
Alexander
Alexander
Hi. How i can create unique index for column by clause UPPER(column)?
You can add this manually by executing corresponding CREATE INDEX command. When we release migrations it will be possible to put this code inside custom migration
stsouko
Thank you very much!
Alexander
Sure
Святослав
Why not allow use raw_sql expressions for indexes? This way to got overloaded entity model in Python?
Alexander
It's a good idea, we probably can implement it. You can open a corresponding issue. I think it may take some time to implement, because other tasks (like migrations or new JSON serialization API) right now have a bigger priority
Святослав
Lucky
Postgres has a plugin to have a select over multible databases (&hosts)
Lucky
Hey a new stickerpack.
The 2859th I came across.
Alexander
Lucky
Alexander
That's cool
Lucky
Flagging NSFW stickers is a lot of work :(
Lucky
Anyone has experience with all that social stuff?
Currently it is only crawling and displaying, but I want to add some user experience, like voting and stuff
Micaiah
For 'Recently Used' you could probably get away with a displaying them in a grid and then adding the emoji, open button, and flag button to the bottom of each sticker image
Lucky
Micaiah
Also you might want to display page+1 so that users first page is page 1
Lucky
Also you might want to display page+1 so that users first page is page 1
you are absolutely right!
Lucky
The discovering process is funny, I need to use a regular Telegram client to look up the sticker pack, the Telegram Bot API doesn't feature that :S
Micaiah
Wow, you should request that haha
Lucky
To summarize how it works:
Lucky
Sending the stickers.
Micaiah
what would an example query look like
Micaiah
Oh I guess I was trying to use it wrong
Micaiah
Like, if I was in a convo and I wanted to use @StickerTagBot to send a sticker to that person
Micaiah
Although you might want to add limits to prevent getting blocked like that haha
Lucky
Like, if I was in a convo and I wanted to use @StickerTagBot to send a sticker to that person
Problem is currently, that the Database is too slow to answer telegram in times, so I basically get "Error: you are to slow" back when trying the inline query.
Micaiah
Oh
Micaiah
Would it be faster if you swapped out Flask for something faster? I hear Sanic (however stupid the name) is freaaaky fast
Micaiah
And its async which sounds good for something like this
Lucky
Oh, never mind, I killed the query somehow.
TypeError: Function 'startswith' cannot be used this way: query_text_lower.startswith(p_t.string.lower())
Micaiah
Hmm
Alexander
Shouldn't it be the other way around?
p_t.string.lower().startswith(query_text_lower)?
Lucky
Lucky
for text search http://blog.scoutapp.com/articles/2016/07/12/how-to-make-text-searches-in-postgresql-faster-with-trigram-similarity
I am trying a trigram index, see if that speeds up str in clolumn, and I can use the in parameter again
Alexander
By the way, do you currently have index on lower(p_t.string)?
Lucky
I was thinking about deploying a elasticsearch instance to make that string based querying faster, and also get a relevance factor
but keeping the data in sync is a problem of it's own.
Lucky
By the way, do you currently have index on lower(p_t.string)?
Following the article linked above, I uses
CREATE INDEX customer_names_on_last_name_idx ON customer_names USING GIN(last_name gin_trgm_ops);
Lucky
To be specific:
-- http://blog.scoutapp.com/articles/2016/07/12/how-to-make-text-searches-in-postgresql-faster-with-trigram-similarity
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- index on tag.string
CREATE INDEX IF NOT EXISTS tag_string_trigram_idx ON tag USING GIN(string gin_trgm_ops);
Lucky
Article says, it is case insensitive anyway, so that's not a problem
Lucky
Indeed, it was not timeouting, but the query was wrong.
Micaiah
Thats better
Lucky
I thought I had the PonyORM sticker in my database as well
Lucky
Lucky
This one
Alexey
You should)
But may be you can add ponyorm.com under it?
Alexey
What do you think?
Lucky
How can I use ILIKE?
Alexander
You can write raw sql fragment
Alexander
something like
and raw_sql('table_name.column_name ILIKE $(python_expr)')
Lucky
There is nothing built-in?
Alexander
Python does not have equivalent operation, I think it should be easy to write raw sql fragment inside a generator query
Lucky
My current line with that is
orm.exists(p_t for p_t in st.sticker.sticker_pack.tags if query_text_lower in p_t.string)
I was thinking there could be a modifyer like orm.desc(...)
Like
orm.ignorecase(query_text_lower in p_t.string)
or
query_text_lower in orm.ignorecase(p_t.string)
Lucky
something like
and raw_sql('table_name.column_name ILIKE $(python_expr)')
Do I have to make the '%' escaping myself there?
Lucky
My current line with that is
orm.exists(p_t for p_t in st.sticker.sticker_pack.tags if query_text_lower in p_t.string)
I was thinking there could be a modifyer like orm.desc(...)
Like
orm.ignorecase(query_text_lower in p_t.string)
or
query_text_lower in orm.ignorecase(p_t.string)
Or maybe even a function orm.ilike(query_text, p_t.string)
Alexander
Curretly we don't support such modifiers.
You can process escapes manually outside of a query:
like_template = '%' + query_string.replace('!', '!!').replace('%', '!%').replace('_', '!_') + '%'
select(p_t for p_t in st.sticker.sticker_pack.tags if raw_sql("p_t.string ILIKE $like_template ESCAPE '!'"))
Lucky
Maybe I can modify my index to have LOWER(string) instead of string:
CREATE INDEX IF NOT EXISTS tag_string_trigram_idx ON tag USING GIN(LOWER(string) gin_trgm_ops);
And just query query_string.lower() in p_t.string.lower()
instead of query_string in p_t.string
Alexander
Yes, it is possible as well
Lucky
I'll try that, not sure if the index part will work
Lucky
Curretly we don't support such modifiers.
You can process escapes manually outside of a query:
like_template = '%' + query_string.replace('!', '!!').replace('%', '!%').replace('_', '!_') + '%'
select(p_t for p_t in st.sticker.sticker_pack.tags if raw_sql("p_t.string ILIKE $like_template ESCAPE '!'"))
With the raw_sql I always am missing the options to include the parameters I want as actual arguments,
like "{} {}".format(arg1, arg2) which feels safer to sql injection and also IDE's won't mark variables as unused.
Alexander
When you use $expr inside raw_sql it should convert it to actual parameter. About IDEs, maybe we need to allow to explicitly pass variables to raw_sql, like in
python_expr = 'hello'
raw_sql("column ILIKE $expr", expr=python_expr)
Lucky
Lucky
Lucky
Alexander
Cool :)
Lucky
pony.orm.core.DBSchemaError: Index for column 'file_id' already exists
Note about which table would be great
Lucky
Also I now try dropping every Index there is
Alexander
This error is from the database, we have no control on how it looks. If you turn on sql output by using sql_debug(True) you can see which sql command was executed last
Lucky
Is just printing
GET NEW CONNECTION
RELEASE CONNECTION
Alexander
ok, it is a bug that should be fixed in the next release...
Alexander
Do you create index for file_id column manually, or it is the index that Pony generates automatically?
Lucky
I tried to rename the StickerPack table to simply Pack in the database
Lucky
After changing the python code accordinly
Lucky
Lucky
Lucky
Do you create index for file_id column manually, or it is the index that Pony generates automatically?
ORM created a Primary key on (file_id, emoji) before, Tried to change that to file_id only