docker run -p 3306:3306  --name mysql -e MYSQL_ROOT_PASSWORD=password -d mysql

SELECT *,
       (select count(Stories.id) from Stories where project_id=projects.id) storyCount
FROM `projects`
WHERE `projects`.`deleted_at` IS NULL
LIMIT 10

SELECT mark.note.comment, id, content
FROM mark.note
WHERE comment::text similar to '%consumers%'
   or content similar to '%consumers%'
ORDER BY updated_at DESC;

CREATE EXTENSION pgcrypto;

-- 创建序列来替代表
CREATE SEQUENCE IF NOT EXISTS snowflake_sequence;

-- 创建雪花ID生成函数
CREATE OR REPLACE FUNCTION generate_snowflake_id(node_id int)
RETURNS bigint AS $$
DECLARE
    -- 常量定义
    epoch bigint := 1640995200000;  -- 2022-01-01 作为纪元
    timestamp_bits int := 41;
    node_id_bits int := 10;
    sequence_bits int := 12;
    
    -- 计算最大值
    max_node_id bigint := (1 << node_id_bits) - 1;
    max_sequence bigint := (1 << sequence_bits) - 1;
    
    -- 变量定义
    timestamp bigint;
    sequence bigint;
    last_timestamp bigint := 0;
BEGIN
    -- 检查节点ID是否有效
    IF node_id < 0 OR node_id > max_node_id THEN
        RAISE EXCEPTION 'Invalid node ID: %', node_id;
    END IF;

    -- 获取当前时间戳（毫秒）
    timestamp := (EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::bigint - epoch;
    
    -- 如果时间戳回退，抛出异常
    IF timestamp < last_timestamp THEN
        RAISE EXCEPTION 'Clock moved backwards. Refusing to generate id for % milliseconds', last_timestamp - timestamp;
    END IF;
    
    -- 如果是同一毫秒，增加序列号
    IF timestamp = last_timestamp THEN
        sequence := (nextval('snowflake_sequence') - 1) & max_sequence;
        -- 如果序列号溢出，等待下一毫秒
        IF sequence = 0 THEN
            timestamp := timestamp + 1;
        END IF;
    ELSE
        -- 不同毫秒，重置序列号
        sequence := 0;
        last_timestamp := timestamp;
        -- 重置序列
        PERFORM setval('snowflake_sequence', 1, false);
    END IF;
    
    -- 生成并返回雪花ID
    RETURN (
        (timestamp << (node_id_bits + sequence_bits)) |
        (node_id << sequence_bits) |
        sequence
    );
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION npmvs.lucky_packages(num integer)
RETURNS SETOF npmvs.package
LANGUAGE sql STABLE AS $function$
SELECT *
FROM npmvs.package
ORDER BY random()
LIMIT num
$function$;

CREATE OR REPLACE FUNCTION npmvs.lucky_tags(num integer)
RETURNS SETOF npmvs.tag
LANGUAGE sql STABLE AS $function$
SELECT id, name
FROM npmvs.tag
ORDER BY random()
LIMIT 5
$function$;