pip install clip-client
python -m clip_server

curl -X POST http://0.0.0.0:51000/post \ 
     -H 'Content-Type: application/json' \
     -d '{"data":[{"text": "First do it"}]}'

{"data":[{"text": "First do it"}]}

{"data":[ {"blob":"base64_string" }]}
{"data":[ {"uri":"https://clip-as-servie/static/test.jpg" }]}

CREATE EXTENSION POLAR_AI WITH SCHEMA PUBLIC;
set search_path="$user", public,polar_ai;

-- 此处输入一个文本串
CREATE OR REPLACE FUNCTION clip_text_embedding_in(model text, setence text)
    RETURNS jsonb
    LANGUAGE plpgsql
    AS $function$
    BEGIN
    RETURN ('{"data": [{"text": "' ||setence || '"}]}')::jsonb;
    END;
    $function$;

-- 图片类型
CREATE OR REPLACE FUNCTION clip_image_embedding_in(model text, image_path text)
    RETURNS jsonb
    LANGUAGE plpgsql
    AS $function$
    DECLARE 
     url text;
    BEGIN
      url = 'oss://<ak>:<sk>@<endpoint-internal>/<bucket>/' || $2;
    RETURN ('{"data": [{"blob": "' || ai_imageasbase64(ai_resizeimage(ai_loadfile(url), 336, 336), false)|| '"}]}')::jsonb;
    END;
    $function$;

CREATE OR REPLACE FUNCTION clip_embedding_out(model_id text, response_json jsonb)
    RETURNS jsonb
    AS $$ select (((((response_json)->>'data')::jsonb->0)::jsonb)->>'embedding')::jsonb as result $$
    LANGUAGE 'sql' IMMUTABLE;

-- 文本模式
SELECT polar_ai.ai_createmodel('embedding/clip_text', --模型id
'http://10.10.1.x:51000/post', --访问地址
'other', -- 模型提供商
'embedding', -- 模型类别
'clip', -- 模型名称
'{"author_type": "token", "token": "my_token"}', --认证信息
NULL, -- header函数
'clip_text_embedding_in'::regproc, -- 输入函数
'clip_embedding_out'::regproc --输出函数); 
-- 图片模式
SELECT polar_ai.ai_createmodel('embedding/clip_image', --模型id
'http://10.10.1.x:51000/post',--访问地址
'other',-- 模型提供商
'embedding',-- 模型类别
'clip',-- 模型名称
'{"author_type": "token", "token": "my_token"}', --认证信息
NULL,-- header函数
'clip_image_embedding_in'::regproc,-- 输入函数
'clip_embedding_out'::regproc --输出函数);

-- 输入文本生成embedding
CREATE OR REPLACE FUNCTION clip_text_embedding(text text)
    RETURNS float4[]
    AS $$ select array(select json_array_elements_text(polar_ai.AI_CALLMODEL('embedding/clip_text',$1)::json))::float4[] as result $$
    LANGUAGE 'sql' IMMUTABLE;
-- 输入图片路径生成embedding
CREATE OR REPLACE FUNCTION clip_image_embedding(text text)
    RETURNS float4[]
    AS $$ select array(select json_array_elements_text(ganos_ai.AI_CALLMODEL('embedding/clip_image',$1)::json))::float4[] as result $$
    LANGUAGE 'sql' IMMUTABLE;

CREATE TABLE images(id serial, 
url text,  -- 图片路径
embedding vector(512),  -- 图片向量
);

INSERT INTO images(url)
VALUES ('test_data/bdd100k/images/10k/train/0004a4c0-d4dff0ad.jpg');

UPDATE images
SET embedding=clip_image_embedding(url)::vector(512);

CREATE INDEX ON images USING hnsw (embedding vector_l2_ops);

SELECT id, url
FROM images
ORDER BY embedding <-> clip_text_embedding('white truck in a snow day')::vector(512) DESC
limit 10;

SELECT id, url
FROM images
WHERE id > 1000
ORDER BY embedding <-> clip_text_embedding('black car in the night')::vector(512) DESC
limit 10;

SELECT id, url
FROM images
ORDER BY embedding <-> (
  SELECT clip_image_embedding(url)::vector(512)::vector(512) FROM images WHERE id = 5560) DESC 
limit 10;

import psycopg
import io
from PIL import Image
import matplotlib.pyplot as plt
def connect():
    try:
        conn = psycopg2.connect(
            dbname="dbname",  # 数据库名
            host="localhost", # 数据库地址，如果是本地就是'localhost'
            port="5432"       # 数据库端口，默认是5432
        )
      
    except (Exception, psycopg2.Error) as error:
        print("连接到PostgreSQL数据库时发生错误: ", error)
    return conn
def exec_sql(sql):
    connection = connect()
    
    try:
        # 创建游标对象，用来执行SQL命令
        cursor = connection.cursor()
    
        cursor.execute(sql)
        rows = cursor.fetchall();
        image = None
        for row in rows:
            print(str(row[0]) + ":" +row[2])
            image = Image.open(io.BytesIO(row[1]))
            plt.imshow(image)
            plt.axis('off')
            plt.show()
        cursor.close()              
    except (Exception, psycopg2.Error) as error:
        print(error)
        cursor.close()
    connection.close()
'''
my_loadfile函数需要预先在数据库内创建，用于从oss上读取文件并缩放大小到512便于显示
CREATE OR REPLACE FUNCTION my_loadfile(text)
    RETURNS bytea
    AS $$ SELECT AI_RESIZEIMAGE(AI_LOADFILE('OSS://<ak>:<sk>@<endpoint-internal>/<bucket>/' || $1), 512, 0 )$$
    LANGUAGE 'sql' IMMUTABLE;
'''
## 文搜图
sql = "SELECT id, my_loadfile(url),url \
FROM images \
WHERE url 
ORDER BY embedding <-> clip_text_embedding('white truck in a snow day')::vector(512) \
limit 5"
exec_sql(sql)