商店系统示例

本示例展示如何使用 Sleet ORM 构建一个功能完整的 FiveM 商店系统，包括商品管理、库存控制、购买逻辑和交易记录。

Schema 设计

-- server/schema.lua (商店相关表)
local sl = Sleet

-- 商店表
local shops = sl.table('shops', {
    id = sl.serial().primaryKey().comment('商店ID'),
    name = sl.varchar(255).notNull().comment('商店名称'),
    description = sl.text().comment('商店描述'),
    location = sl.json().comment('位置信息'),
    owner_type = sl.varchar(50).default('npc').comment('所有者类型: npc/player'),
    owner_id = sl.int().comment('所有者ID'),
    is_active = sl.boolean().default(true).comment('是否营业'),
    markup_rate = sl.decimal(5, 4).default(1.0).comment('加价率'),
    created_at = sl.timestamp().defaultNow()
})

-- 商品分类表
local categories = sl.table('categories', {
    id = sl.serial().primaryKey(),
    name = sl.varchar(100).notNull().unique().comment('分类名称'),
    label = sl.varchar(255).notNull().comment('显示名称'),
    icon = sl.varchar(255).comment('图标'),
    sort_order = sl.int().default(0).comment('排序')
})

-- 商品表
local items = sl.table('items', {
    id = sl.serial().primaryKey(),
    name = sl.varchar(255).notNull().unique().comment('商品名称'),
    label = sl.varchar(255).notNull().comment('显示名称'),
    description = sl.text().comment('商品描述'),
    category_id = sl.int().references(categories.id).comment('分类ID'),
    base_price = sl.decimal(10, 2).notNull().comment('基础价格'),
    weight = sl.decimal(5, 2).default(0.1).comment('重量'),
    stack_limit = sl.int().default(1).comment('堆叠上限'),
    is_usable = sl.boolean().default(false).comment('是否可使用'),
    metadata = sl.json().comment('商品属性'),
    created_at = sl.timestamp().defaultNow()
})

-- 商店库存表
local shop_inventory = sl.table('shop_inventory', {
    id = sl.serial().primaryKey(),
    shop_id = sl.int().notNull().references(shops.id).comment('商店ID'),
    item_id = sl.int().notNull().references(items.id).comment('商品ID'),
    stock = sl.int().default(0).comment('库存数量'),
    max_stock = sl.int().comment('最大库存'),
    restock_rate = sl.int().default(0).comment('补货速度/小时'),
    custom_price = sl.decimal(10, 2).comment('自定义价格'),
    last_restock = sl.timestamp().defaultNow().comment('最后补货时间'),
    is_available = sl.boolean().default(true).comment('是否可售')
})

-- 交易记录表
local transactions = sl.table('transactions', {
    id = sl.serial().primaryKey(),
    player_id = sl.int().notNull().comment('玩家ID'),
    shop_id = sl.int().references(shops.id).comment('商店ID'),
    item_id = sl.int().notNull().references(items.id).comment('商品ID'),
    quantity = sl.int().notNull().comment('数量'),
    unit_price = sl.decimal(10, 2).notNull().comment('单价'),
    total_price = sl.decimal(12, 2).notNull().comment('总价'),
    transaction_type = sl.varchar(20).notNull().comment('交易类型: buy/sell'),
    created_at = sl.timestamp().defaultNow().comment('交易时间')
})

return {
    shops = shops,
    categories = categories,
    items = items,
    shop_inventory = shop_inventory,
    transactions = transactions
}

商店管理器

-- server/shop_manager.lua
local sl = Sleet
local s = require 'server.schema'
local db = sl.connect()

local ShopManager = {}

-- 获取商店信息
function ShopManager.GetShop(shopId)
    return db.select()
        .from(s.shops)
        .where(sl.and_(
            sl.eq(s.shops.id, shopId),
            sl.eq(s.shops.is_active, true)
        ))
        .limit(1)
        .execute()[1]
end

-- 获取商店库存
function ShopManager.GetInventory(shopId, categoryId)
    local query = db.select({
        s.shop_inventory.id,
        s.shop_inventory.stock,
        s.shop_inventory.max_stock,
        s.shop_inventory.custom_price,
        s.items.id,
        s.items.name,
        s.items.label,
        s.items.description,
        s.items.base_price,
        s.items.weight,
        s.categories.label
    })
        .from(s.shop_inventory)
        .leftJoin(s.items, sl.eq(s.shop_inventory.item_id, s.items.id))
        .leftJoin(s.categories, sl.eq(s.items.category_id, s.categories.id))
        .where(sl.and_(
            sl.eq(s.shop_inventory.shop_id, shopId),
            sl.eq(s.shop_inventory.is_available, true),
            sl.gt(s.shop_inventory.stock, 0)
        ))
    
    if categoryId then
        query = query.where(sl.eq(s.items.category_id, categoryId))
    end
    
    return query.orderBy(s.categories.sort_order)
        .orderBy(s.items.label)
        .execute()
end

-- 计算商品价格
function ShopManager.GetItemPrice(shopId, itemId, quantity)
    local result = db.select({
        s.shop_inventory.custom_price,
        s.items.base_price,
        s.shops.markup_rate
    })
        .from(s.shop_inventory)
        .leftJoin(s.items, sl.eq(s.shop_inventory.item_id, s.items.id))
        .leftJoin(s.shops, sl.eq(s.shop_inventory.shop_id, s.shops.id))
        .where(sl.and_(
            sl.eq(s.shop_inventory.shop_id, shopId),
            sl.eq(s.shop_inventory.item_id, itemId)
        ))
        .limit(1)
        .execute()[1]
    
    if not result then
        return nil, '商品不存在'
    end
    
    local unitPrice = result.custom_price or (result.base_price * result.markup_rate)
    return math.floor(unitPrice * quantity), unitPrice
end

return ShopManager

购买逻辑实现

-- server/purchase.lua
local sl = Sleet
local s = require 'server.schema'
local db = sl.connect()

local PurchaseManager = {}

-- 购买商品
function PurchaseManager.BuyItem(playerId, shopId, itemId, quantity)
    -- 验证参数
    if quantity <= 0 then
        return false, '数量必须大于0'
    end
    
    -- 获取商店和商品信息
    local inventory = db.select({
        s.shop_inventory.stock,
        s.shop_inventory.custom_price,
        s.items.base_price,
        s.items.label,
        s.items.weight,
        s.shops.markup_rate
    })
        .from(s.shop_inventory)
        .leftJoin(s.items, sl.eq(s.shop_inventory.item_id, s.items.id))
        .leftJoin(s.shops, sl.eq(s.shop_inventory.shop_id, s.shops.id))
        .where(sl.and_(
            sl.eq(s.shop_inventory.shop_id, shopId),
            sl.eq(s.shop_inventory.item_id, itemId),
            sl.eq(s.shop_inventory.is_available, true),
            sl.eq(s.shops.is_active, true)
        ))
        .limit(1)
        .execute()[1]
    
    if not inventory then
        return false, '商品不可购买'
    end
    
    -- 检查库存
    if inventory.stock < quantity then
        return false, string.format('库存不足，仅剩 %d 个', inventory.stock)
    end
    
    -- 计算价格
    local unitPrice = inventory.custom_price or (inventory.base_price * inventory.markup_rate)
    local totalPrice = math.floor(unitPrice * quantity)
    
    -- 检查玩家金钱（这里简化处理，实际应该从玩家系统获取）
    local PlayerManager = require 'server.player'
    local player = PlayerManager.GetById(playerId)
    
    if not player or player.money < totalPrice then
        return false, string.format('金钱不足，需要 $%d', totalPrice)
    end
    
    -- 执行购买（使用事务确保数据一致性）
    local success, error = pcall(function()
        -- 扣除玩家金钱
        local moneyUpdated = db.update(s.players)
            .set({ 
                money = sl.sql('money - ?', { totalPrice }),
                updated_at = sl.sql('NOW()')
            })
            .where(sl.and_(
                sl.eq(s.players.id, playerId),
                sl.gte(s.players.money, totalPrice)
            ))
            .execute()
        
        if moneyUpdated == 0 then
            error('扣除金钱失败')
        end
        
        -- 减少商店库存
        local stockUpdated = db.update(s.shop_inventory)
            .set({ 
                stock = sl.sql('stock - ?', { quantity }),
                last_restock = sl.sql('NOW()')
            })
            .where(sl.and_(
                sl.eq(s.shop_inventory.shop_id, shopId),
                sl.eq(s.shop_inventory.item_id, itemId),
                sl.gte(s.shop_inventory.stock, quantity)
            ))
            .execute()
        
        if stockUpdated == 0 then
            error('库存扣除失败')
        end
        
        -- 记录交易
        db.insert(s.transactions)
            .values({
                player_id = playerId,
                shop_id = shopId,
                item_id = itemId,
                quantity = quantity,
                unit_price = unitPrice,
                total_price = totalPrice,
                transaction_type = 'buy'
            })
            .execute()
        
        -- 给玩家添加物品（这里需要实现物品系统）
        -- InventoryManager.AddItem(playerId, itemId, quantity)
    end)
    
    if success then
        return true, {
            item_name = inventory.label,
            quantity = quantity,
            unit_price = unitPrice,
            total_price = totalPrice
        }
    else
        return false, error or '购买失败'
    end
end

return PurchaseManager