veloren_server/persistence/character/
mod.rs

1//! Database operations related to character data
2//!
3//! Methods in this module should remain private to the persistence module -
4//! database updates and loading are communicated via requests to the
5//! [`CharacterLoader`] and [`CharacterUpdater`] while results/responses are
6//! polled and handled each server tick.
7extern crate rusqlite;
8
9use super::{error::PersistenceError, models::*};
10use crate::{
11    comp::{self, Inventory},
12    persistence::{
13        EditableComponents, PersistedComponents,
14        character::conversions::{
15            convert_active_abilities_from_database, convert_active_abilities_to_database,
16            convert_body_from_database, convert_body_to_database_json,
17            convert_character_from_database, convert_hardcore_from_database,
18            convert_hardcore_to_database, convert_inventory_from_database_items,
19            convert_items_to_database_items, convert_loadout_from_database_items,
20            convert_recipe_book_from_database_items, convert_skill_groups_to_database,
21            convert_skill_set_from_database, convert_stats_from_database,
22            convert_waypoint_from_database_json, convert_waypoint_to_database_json,
23        },
24        character_loader::{CharacterCreationResult, CharacterDataResult, CharacterListResult},
25        character_updater::PetPersistenceData,
26        error::PersistenceError::DatabaseError,
27    },
28};
29use common::{
30    character::{CharacterId, CharacterItem, MAX_CHARACTERS_PER_PLAYER},
31    comp::Content,
32    event::{PermanentChange, UpdateCharacterMetadata},
33    npc::NPC_NAMES,
34};
35use core::ops::Range;
36use rusqlite::{Connection, ToSql, Transaction, types::Value};
37use std::{num::NonZeroU64, rc::Rc};
38use tracing::{debug, error, trace, warn};
39
40/// Private module for very tightly coupled database conversion methods.  In
41/// general, these have many invariants that need to be maintained when they're
42/// called--do not assume it's safe to make these public!
43mod conversions;
44
45pub(crate) type EntityId = i64;
46
47pub(crate) use conversions::convert_waypoint_from_database_json as parse_waypoint;
48
49const CHARACTER_PSEUDO_CONTAINER_DEF_ID: &str = "veloren.core.pseudo_containers.character";
50const INVENTORY_PSEUDO_CONTAINER_DEF_ID: &str = "veloren.core.pseudo_containers.inventory";
51const LOADOUT_PSEUDO_CONTAINER_DEF_ID: &str = "veloren.core.pseudo_containers.loadout";
52const OVERFLOW_ITEMS_PSEUDO_CONTAINER_DEF_ID: &str =
53    "veloren.core.pseudo_containers.overflow_items";
54const RECIPE_BOOK_PSEUDO_CONTAINER_DEF_ID: &str = "veloren.core.pseudo_containers.recipe_book";
55const INVENTORY_PSEUDO_CONTAINER_POSITION: &str = "inventory";
56const LOADOUT_PSEUDO_CONTAINER_POSITION: &str = "loadout";
57const OVERFLOW_ITEMS_PSEUDO_CONTAINER_POSITION: &str = "overflow_items";
58const RECIPE_BOOK_PSEUDO_CONTAINER_POSITION: &str = "recipe_book";
59const WORLD_PSEUDO_CONTAINER_ID: EntityId = 1;
60
61#[derive(Clone, Copy)]
62struct CharacterContainers {
63    inventory_container_id: EntityId,
64    loadout_container_id: EntityId,
65    overflow_items_container_id: EntityId,
66    recipe_book_container_id: EntityId,
67}
68
69/// Load the inventory/loadout
70///
71/// Loading is done recursively to ensure that each is topologically sorted in
72/// the sense required by convert_inventory_from_database_items.
73///
74/// For items with components, the parent item must sorted so that its
75/// components are after the parent item.
76pub fn load_items(connection: &Connection, root: i64) -> Result<Vec<Item>, PersistenceError> {
77    let mut stmt = connection.prepare_cached(
78        "
79        WITH RECURSIVE
80        items_tree (
81            item_id,
82            parent_container_item_id,
83            item_definition_id,
84            stack_size,
85            position,
86            properties
87        ) AS (
88            SELECT  item_id,
89                    parent_container_item_id,
90                    item_definition_id,
91                    stack_size,
92                    position,
93                    properties
94            FROM item
95            WHERE parent_container_item_id = ?1
96            UNION ALL
97            SELECT  item.item_id,
98                    item.parent_container_item_id,
99                    item.item_definition_id,
100                    item.stack_size,
101                    item.position,
102                    item.properties
103            FROM item, items_tree
104            WHERE item.parent_container_item_id = items_tree.item_id
105        )
106        SELECT  *
107        FROM    items_tree",
108    )?;
109
110    let items = stmt
111        .query_map([root], |row| {
112            Ok(Item {
113                item_id: row.get(0)?,
114                parent_container_item_id: row.get(1)?,
115                item_definition_id: row.get(2)?,
116                stack_size: row.get(3)?,
117                position: row.get(4)?,
118                properties: row.get(5)?,
119            })
120        })?
121        .filter_map(Result::ok)
122        .collect::<Vec<Item>>();
123
124    Ok(items)
125}
126
127/// Load stored data for a character.
128///
129/// After first logging in, and after a character is selected, we fetch this
130/// data for the purpose of inserting their persisted data for the entity.
131pub fn load_character_data(
132    requesting_player_uuid: String,
133    char_id: CharacterId,
134    connection: &Connection,
135) -> CharacterDataResult {
136    let character_containers = get_pseudo_containers(connection, char_id)?;
137    let inventory_items = load_items(connection, character_containers.inventory_container_id)?;
138    let loadout_items = load_items(connection, character_containers.loadout_container_id)?;
139    let overflow_items_items =
140        load_items(connection, character_containers.overflow_items_container_id)?;
141    let recipe_book_items = load_items(connection, character_containers.recipe_book_container_id)?;
142
143    let mut stmt = connection.prepare_cached(
144        "
145        SELECT  c.character_id,
146                c.alias,
147                c.waypoint,
148                c.hardcore,
149                b.variant,
150                b.body_data
151        FROM    character c
152        JOIN    body b ON (c.character_id = b.body_id)
153        WHERE   c.player_uuid = ?1
154        AND     c.character_id = ?2",
155    )?;
156
157    let (body_data, character_data) = stmt.query_row(
158        [requesting_player_uuid.clone(), char_id.0.to_string()],
159        |row| {
160            let character_data = Character {
161                character_id: row.get(0)?,
162                player_uuid: requesting_player_uuid,
163                alias: row.get(1)?,
164                waypoint: row.get(2)?,
165                hardcore: row.get(3)?,
166            };
167
168            let body_data = Body {
169                body_id: row.get(0)?,
170                variant: row.get(4)?,
171                body_data: row.get(5)?,
172            };
173
174            Ok((body_data, character_data))
175        },
176    )?;
177
178    let (char_waypoint, char_map_marker) = match character_data
179        .waypoint
180        .as_ref()
181        .map(|x| convert_waypoint_from_database_json(x))
182    {
183        Some(Ok(w)) => w,
184        Some(Err(e)) => {
185            warn!(
186                "Error reading waypoint from database for character ID
187    {}, error: {}",
188                char_id.0, e
189            );
190            (None, None)
191        },
192        None => (None, None),
193    };
194
195    let mut stmt = connection.prepare_cached(
196        "
197        SELECT  skill_group_kind,
198                earned_exp,
199                spent_exp,
200                skills,
201                hash_val
202        FROM    skill_group
203        WHERE   entity_id = ?1",
204    )?;
205
206    let skill_group_data = stmt
207        .query_map([char_id.0], |row| {
208            Ok(SkillGroup {
209                entity_id: char_id.0,
210                skill_group_kind: row.get(0)?,
211                earned_exp: row.get(1)?,
212                spent_exp: row.get(2)?,
213                skills: row.get(3)?,
214                hash_val: row.get(4)?,
215            })
216        })?
217        .filter_map(Result::ok)
218        .collect::<Vec<SkillGroup>>();
219
220    #[rustfmt::skip]
221    let mut stmt = connection.prepare_cached("
222        SELECT  p.pet_id,
223                p.name,
224                b.variant,
225                b.body_data
226        FROM    pet p
227        JOIN    body b ON (p.pet_id = b.body_id)
228        WHERE   p.character_id = ?1",
229    )?;
230
231    let db_pets = stmt
232        .query_map([char_id.0], |row| {
233            Ok(Pet {
234                database_id: row.get(0)?,
235                name: row.get(1)?,
236                body_variant: row.get(2)?,
237                body_data: row.get(3)?,
238            })
239        })?
240        .filter_map(Result::ok)
241        .collect::<Vec<Pet>>();
242
243    // Re-construct the pet components for the player's pets, including
244    // de-serializing the pets' bodies and creating their Pet and Stats
245    // components
246    let pets = db_pets
247        .iter()
248        .filter_map(|db_pet| {
249            if let Ok(pet_body) =
250                convert_body_from_database(&db_pet.body_variant, &db_pet.body_data)
251            {
252                let pet = comp::Pet::new_from_database(
253                    NonZeroU64::new(db_pet.database_id as u64).unwrap(),
254                );
255                let npc_names = NPC_NAMES.read();
256                // TODO: use proper name here when pet names will be added
257                let pet_stats = comp::Stats::new(
258                    npc_names
259                        .get_default_name(&pet_body)
260                        .unwrap_or(Content::Plain("".to_owned())),
261                    pet_body,
262                );
263                Some((pet, pet_body, pet_stats))
264            } else {
265                warn!(
266                    "Failed to deserialize pet_id: {} for character_id {}",
267                    db_pet.database_id, char_id.0
268                );
269                None
270            }
271        })
272        .collect::<Vec<(comp::Pet, comp::Body, comp::Stats)>>();
273
274    let mut stmt = connection.prepare_cached(
275        "
276            SELECT  ability_sets
277            FROM    ability_set
278            WHERE   entity_id = ?1",
279    )?;
280
281    let ability_set_data = stmt.query_row([char_id.0], |row| {
282        Ok(AbilitySets {
283            entity_id: char_id.0,
284            ability_sets: row.get(0)?,
285        })
286    })?;
287
288    let (skill_set, skill_set_persistence_load_error) =
289        convert_skill_set_from_database(&skill_group_data);
290    let body = convert_body_from_database(&body_data.variant, &body_data.body_data)?;
291    let hardcore = convert_hardcore_from_database(character_data.hardcore)?;
292    Ok((
293        PersistedComponents {
294            body,
295            hardcore,
296            stats: convert_stats_from_database(character_data.alias, body),
297            skill_set,
298            inventory: convert_inventory_from_database_items(
299                character_containers.inventory_container_id,
300                &inventory_items,
301                character_containers.loadout_container_id,
302                &loadout_items,
303                character_containers.overflow_items_container_id,
304                &overflow_items_items,
305                &recipe_book_items,
306            )?,
307            waypoint: char_waypoint,
308            pets,
309            active_abilities: convert_active_abilities_from_database(&ability_set_data),
310            map_marker: char_map_marker,
311        },
312        UpdateCharacterMetadata {
313            skill_set_persistence_load_error,
314        },
315    ))
316}
317
318/// Loads a list of characters belonging to the player. This data is a small
319/// subset of the character's data, and is used to render the character and
320/// their level in the character list.
321///
322/// In the event that a join fails, for a character (i.e. they lack an entry for
323/// stats, body, etc...) the character is skipped, and no entry will be
324/// returned.
325pub fn load_character_list(player_uuid_: &str, connection: &Connection) -> CharacterListResult {
326    let mut stmt = connection.prepare_cached(
327        "
328            SELECT  character_id,
329                    alias,
330                    waypoint,
331                    hardcore
332            FROM    character
333            WHERE   player_uuid = ?1
334            ORDER BY character_id",
335    )?;
336
337    let characters = stmt
338        .query_map([player_uuid_], |row| {
339            Ok(Character {
340                character_id: row.get(0)?,
341                alias: row.get(1)?,
342                player_uuid: player_uuid_.to_owned(),
343                waypoint: row.get(2)?,
344                hardcore: row.get(3)?,
345            })
346        })?
347        .map(|x| x.unwrap())
348        .collect::<Vec<Character>>();
349    drop(stmt);
350
351    characters
352        .iter()
353        .map(|character_data| {
354            let char = convert_character_from_database(character_data);
355
356            let mut stmt = connection.prepare_cached(
357                "
358                SELECT  body_id,
359                        variant,
360                        body_data
361                FROM    body
362                WHERE   body_id = ?1",
363            )?;
364            let db_body = stmt.query_row([char.id.map(|c| c.0)], |row| {
365                Ok(Body {
366                    body_id: row.get(0)?,
367                    variant: row.get(1)?,
368                    body_data: row.get(2)?,
369                })
370            })?;
371            drop(stmt);
372
373            let char_body = convert_body_from_database(&db_body.variant, &db_body.body_data)?;
374
375            let hardcore = convert_hardcore_from_database(character_data.hardcore)?;
376
377            let loadout_container_id = get_pseudo_container_id(
378                connection,
379                CharacterId(character_data.character_id),
380                LOADOUT_PSEUDO_CONTAINER_POSITION,
381            )?;
382
383            let loadout_items = load_items(connection, loadout_container_id)?;
384
385            let loadout =
386                convert_loadout_from_database_items(loadout_container_id, &loadout_items)?;
387
388            let recipe_book_container_id = get_pseudo_container_id(
389                connection,
390                CharacterId(character_data.character_id),
391                RECIPE_BOOK_PSEUDO_CONTAINER_POSITION,
392            )?;
393
394            let recipe_book_items = load_items(connection, recipe_book_container_id)?;
395
396            let (recipe_book, _) = convert_recipe_book_from_database_items(&recipe_book_items)?;
397
398            Ok(CharacterItem {
399                character: char,
400                body: char_body,
401                hardcore: hardcore.is_some(),
402                inventory: Inventory::with_loadout(loadout, char_body)
403                    .with_recipe_book(recipe_book),
404                location: character_data.waypoint.as_ref().cloned(),
405            })
406        })
407        .collect()
408}
409
410pub fn create_character(
411    uuid: &str,
412    character_alias: &str,
413    persisted_components: PersistedComponents,
414    transaction: &mut Transaction,
415) -> CharacterCreationResult {
416    check_character_limit(uuid, transaction)?;
417
418    let PersistedComponents {
419        body,
420        hardcore,
421        stats: _,
422        skill_set,
423        inventory,
424        waypoint,
425        pets: _,
426        active_abilities,
427        map_marker,
428    } = persisted_components;
429
430    // Fetch new entity IDs for character, inventory, loadout, overflow items, and
431    // recipe book
432    let mut new_entity_ids = get_new_entity_ids(transaction, |next_id| next_id + 5)?;
433
434    // Create pseudo-container items for character
435    let character_id = new_entity_ids.next().unwrap();
436    let inventory_container_id = new_entity_ids.next().unwrap();
437    let loadout_container_id = new_entity_ids.next().unwrap();
438    let overflow_items_container_id = new_entity_ids.next().unwrap();
439    let recipe_book_container_id = new_entity_ids.next().unwrap();
440
441    let pseudo_containers = vec![
442        Item {
443            stack_size: 1,
444            item_id: character_id,
445            parent_container_item_id: WORLD_PSEUDO_CONTAINER_ID,
446            item_definition_id: CHARACTER_PSEUDO_CONTAINER_DEF_ID.to_owned(),
447            position: character_id.to_string(),
448            properties: String::new(),
449        },
450        Item {
451            stack_size: 1,
452            item_id: inventory_container_id,
453            parent_container_item_id: character_id,
454            item_definition_id: INVENTORY_PSEUDO_CONTAINER_DEF_ID.to_owned(),
455            position: INVENTORY_PSEUDO_CONTAINER_POSITION.to_owned(),
456            properties: String::new(),
457        },
458        Item {
459            stack_size: 1,
460            item_id: loadout_container_id,
461            parent_container_item_id: character_id,
462            item_definition_id: LOADOUT_PSEUDO_CONTAINER_DEF_ID.to_owned(),
463            position: LOADOUT_PSEUDO_CONTAINER_POSITION.to_owned(),
464            properties: String::new(),
465        },
466        Item {
467            stack_size: 1,
468            item_id: overflow_items_container_id,
469            parent_container_item_id: character_id,
470            item_definition_id: OVERFLOW_ITEMS_PSEUDO_CONTAINER_DEF_ID.to_owned(),
471            position: OVERFLOW_ITEMS_PSEUDO_CONTAINER_POSITION.to_owned(),
472            properties: String::new(),
473        },
474        Item {
475            stack_size: 1,
476            item_id: recipe_book_container_id,
477            parent_container_item_id: character_id,
478            item_definition_id: RECIPE_BOOK_PSEUDO_CONTAINER_DEF_ID.to_owned(),
479            position: RECIPE_BOOK_PSEUDO_CONTAINER_POSITION.to_owned(),
480            properties: String::new(),
481        },
482    ];
483
484    let mut stmt = transaction.prepare_cached(
485        "
486        INSERT INTO item (item_id,
487                          parent_container_item_id,
488                          item_definition_id,
489                          stack_size,
490                          position,
491                          properties)
492        VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
493    )?;
494
495    for pseudo_container in pseudo_containers {
496        stmt.execute([
497            &pseudo_container.item_id as &dyn ToSql,
498            &pseudo_container.parent_container_item_id,
499            &pseudo_container.item_definition_id,
500            &pseudo_container.stack_size,
501            &pseudo_container.position,
502            &pseudo_container.properties,
503        ])?;
504    }
505    drop(stmt);
506
507    let mut stmt = transaction.prepare_cached(
508        "
509        INSERT INTO body (body_id,
510                          variant,
511                          body_data)
512        VALUES (?1, ?2, ?3)",
513    )?;
514
515    let (body_variant, body_json) = convert_body_to_database_json(&body)?;
516    stmt.execute([
517        &character_id as &dyn ToSql,
518        &body_variant.to_string(),
519        &body_json,
520    ])?;
521    drop(stmt);
522
523    let mut stmt = transaction.prepare_cached(
524        "
525        INSERT INTO character (character_id,
526                               player_uuid,
527                               alias,
528                               waypoint,
529                               hardcore)
530        VALUES (?1, ?2, ?3, ?4, ?5)",
531    )?;
532
533    stmt.execute([
534        &character_id as &dyn ToSql,
535        &uuid,
536        &character_alias,
537        &convert_waypoint_to_database_json(waypoint, map_marker),
538        &convert_hardcore_to_database(hardcore),
539    ])?;
540    drop(stmt);
541
542    let db_skill_groups =
543        convert_skill_groups_to_database(CharacterId(character_id), skill_set.skill_groups());
544
545    let mut stmt = transaction.prepare_cached(
546        "
547        INSERT INTO skill_group (entity_id,
548                                 skill_group_kind,
549                                 earned_exp,
550                                 spent_exp,
551                                 skills,
552                                 hash_val)
553        VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
554    )?;
555
556    for skill_group in db_skill_groups {
557        stmt.execute([
558            &character_id as &dyn ToSql,
559            &skill_group.skill_group_kind,
560            &skill_group.earned_exp,
561            &skill_group.spent_exp,
562            &skill_group.skills,
563            &skill_group.hash_val,
564        ])?;
565    }
566    drop(stmt);
567
568    let ability_sets =
569        convert_active_abilities_to_database(CharacterId(character_id), &active_abilities);
570
571    let mut stmt = transaction.prepare_cached(
572        "
573        INSERT INTO ability_set (entity_id,
574                                 ability_sets)
575        VALUES (?1, ?2)",
576    )?;
577
578    stmt.execute([
579        &character_id as &dyn ToSql,
580        &ability_sets.ability_sets as &dyn ToSql,
581    ])?;
582    drop(stmt);
583
584    // Insert default inventory and loadout item records
585    let mut inserts = Vec::new();
586
587    get_new_entity_ids(transaction, |mut next_id| {
588        let inserts_ = convert_items_to_database_items(
589            loadout_container_id,
590            &inventory,
591            inventory_container_id,
592            overflow_items_container_id,
593            recipe_book_container_id,
594            &mut next_id,
595        );
596        inserts = inserts_;
597        next_id
598    })?;
599
600    let mut stmt = transaction.prepare_cached(
601        "
602        INSERT INTO item (item_id,
603                          parent_container_item_id,
604                          item_definition_id,
605                          stack_size,
606                          position,
607                          properties)
608        VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
609    )?;
610
611    for item in inserts {
612        stmt.execute([
613            &item.model.item_id as &dyn ToSql,
614            &item.model.parent_container_item_id,
615            &item.model.item_definition_id,
616            &item.model.stack_size,
617            &item.model.position,
618            &item.model.properties,
619        ])?;
620    }
621    drop(stmt);
622
623    load_character_list(uuid, transaction).map(|list| (CharacterId(character_id), list))
624}
625
626pub fn edit_character(
627    editable_components: EditableComponents,
628    trusted_change: Option<PermanentChange>,
629    transaction: &mut Transaction,
630    character_id: CharacterId,
631    uuid: &str,
632    character_alias: Option<&str>,
633) -> CharacterCreationResult {
634    let (body,) = editable_components;
635    let mut char_list = load_character_list(uuid, transaction);
636
637    if let Ok(char_list) = &mut char_list
638        && let Some(char) = char_list
639            .iter_mut()
640            .find(|c| c.character.id == Some(character_id))
641        && let (comp::Body::Humanoid(new), comp::Body::Humanoid(old)) = (body, char.body)
642    {
643        let allow_change = match trusted_change {
644            Some(change) => change.expected_old_body == char.body,
645            None => new.species == old.species && new.body_type == old.body_type,
646        };
647        if !allow_change {
648            warn!(
649                "Character edit rejected due to failed validation - Character ID: {} Alias: {:?}",
650                character_id.0, character_alias
651            );
652            return Err(PersistenceError::CharacterDataError);
653        } else {
654            char.body = body;
655        }
656    }
657
658    let mut stmt = transaction
659        .prepare_cached("UPDATE body SET variant = ?1, body_data = ?2 WHERE body_id = ?3")?;
660
661    let (body_variant, body_data) = convert_body_to_database_json(&body)?;
662    stmt.execute([
663        &body_variant.to_string(),
664        &body_data,
665        &character_id.0 as &dyn ToSql,
666    ])?;
667    drop(stmt);
668
669    if let Some(character_alias) = character_alias {
670        let mut stmt = transaction
671            .prepare_cached("UPDATE character SET alias = ?1 WHERE character_id = ?2")?;
672
673        stmt.execute([&character_alias, &character_id.0 as &dyn ToSql])?;
674        drop(stmt);
675    }
676
677    char_list.map(|list| (character_id, list))
678}
679
680/// Permanently deletes a character
681pub fn delete_character(
682    requesting_player_uuid: &str,
683    char_id: CharacterId,
684    transaction: &mut Transaction,
685) -> Result<(), PersistenceError> {
686    debug!(?requesting_player_uuid, ?char_id, "Deleting character");
687
688    let mut stmt = transaction.prepare_cached(
689        "
690        SELECT  COUNT(1)
691        FROM    character
692        WHERE   character_id = ?1
693        AND     player_uuid = ?2",
694    )?;
695
696    let result = stmt.query_row([&char_id.0 as &dyn ToSql, &requesting_player_uuid], |row| {
697        let y: i64 = row.get(0)?;
698        Ok(y)
699    })?;
700    drop(stmt);
701
702    if result != 1 {
703        // The character does not exist, or does not belong to the requesting player so
704        // silently drop the request.
705        return Ok(());
706    }
707
708    // Delete skill groups
709    let mut stmt = transaction.prepare_cached(
710        "
711        DELETE
712        FROM    skill_group
713        WHERE   entity_id = ?1",
714    )?;
715
716    stmt.execute([&char_id.0])?;
717    drop(stmt);
718
719    let pet_ids = get_pet_ids(char_id, transaction)?
720        .iter()
721        .map(|x| Value::from(*x))
722        .collect::<Vec<Value>>();
723    if !pet_ids.is_empty() {
724        delete_pets(transaction, char_id, Rc::new(pet_ids))?;
725    }
726
727    // Delete ability sets
728    let mut stmt = transaction.prepare_cached(
729        "
730        DELETE
731        FROM    ability_set
732        WHERE   entity_id = ?1",
733    )?;
734
735    stmt.execute([&char_id.0])?;
736    drop(stmt);
737
738    // Delete character
739    let mut stmt = transaction.prepare_cached(
740        "
741        DELETE
742        FROM    character
743        WHERE   character_id = ?1",
744    )?;
745
746    stmt.execute([&char_id.0])?;
747    drop(stmt);
748
749    // Delete body
750    let mut stmt = transaction.prepare_cached(
751        "
752        DELETE
753        FROM    body
754        WHERE   body_id = ?1",
755    )?;
756
757    stmt.execute([&char_id.0])?;
758    drop(stmt);
759
760    // Delete all items, recursively walking all containers starting from the
761    // "character" pseudo-container that is the root for all items owned by
762    // a character.
763    let mut stmt = transaction.prepare_cached(
764        "
765        WITH RECURSIVE
766        parents AS (
767            SELECT  item_id
768            FROM    item
769            WHERE   item.item_id = ?1 -- Item with character id is the character pseudo-container
770            UNION ALL
771            SELECT  item.item_id
772            FROM    item,
773                    parents
774            WHERE   item.parent_container_item_id = parents.item_id
775        )
776        DELETE
777        FROM    item
778        WHERE   EXISTS (SELECT 1 FROM parents WHERE parents.item_id = item.item_id)",
779    )?;
780
781    let deleted_item_count = stmt.execute([&char_id.0])?;
782    drop(stmt);
783
784    if deleted_item_count < 3 {
785        return Err(PersistenceError::OtherError(format!(
786            "Error deleting from item table for char_id {} (expected at least 3 deletions, found \
787             {})",
788            char_id.0, deleted_item_count
789        )));
790    }
791
792    Ok(())
793}
794
795/// Before creating a character, we ensure that the limit on the number of
796/// characters has not been exceeded
797pub fn check_character_limit(
798    uuid: &str,
799    transaction: &mut Transaction,
800) -> Result<(), PersistenceError> {
801    let mut stmt = transaction.prepare_cached(
802        "
803        SELECT  COUNT(1)
804        FROM    character
805        WHERE   player_uuid = ?1",
806    )?;
807
808    #[expect(clippy::needless_question_mark)]
809    let character_count: i64 = stmt.query_row([&uuid], |row| Ok(row.get(0)?))?;
810    drop(stmt);
811
812    if character_count < MAX_CHARACTERS_PER_PLAYER as i64 {
813        Ok(())
814    } else {
815        Err(PersistenceError::CharacterLimitReached)
816    }
817}
818
819/// NOTE: This relies heavily on serializability to work correctly.
820///
821/// The count function takes the starting entity id, and returns the desired
822/// count of new entity IDs.
823///
824/// These are then inserted into the entities table.
825fn get_new_entity_ids(
826    transaction: &mut Transaction,
827    mut max: impl FnMut(i64) -> i64,
828) -> Result<Range<EntityId>, PersistenceError> {
829    // The sqlite_sequence table is used here to avoid reusing entity IDs for
830    // deleted entities. This table always contains the highest used ID for
831    // each AUTOINCREMENT column in a SQLite database.
832    let mut stmt = transaction.prepare_cached(
833        "
834        SELECT  seq + 1 AS entity_id
835        FROM    sqlite_sequence
836        WHERE   name = 'entity'",
837    )?;
838
839    #[expect(clippy::needless_question_mark)]
840    let next_entity_id = stmt.query_row([], |row| Ok(row.get(0)?))?;
841    let max_entity_id = max(next_entity_id);
842
843    // Create a new range of IDs and insert them into the entity table
844    let new_ids: Range<EntityId> = next_entity_id..max_entity_id;
845
846    let mut stmt = transaction.prepare_cached("INSERT INTO entity (entity_id) VALUES (?1)")?;
847
848    // SQLite has no bulk insert
849    for i in new_ids.clone() {
850        stmt.execute([i])?;
851    }
852
853    trace!(
854        "Created {} new persistence entity_ids: {}",
855        new_ids.end - new_ids.start,
856        new_ids
857            .clone()
858            .map(|x| x.to_string())
859            .collect::<Vec<String>>()
860            .join(", ")
861    );
862    Ok(new_ids)
863}
864
865/// Fetches the pseudo_container IDs for a character
866fn get_pseudo_containers(
867    connection: &Connection,
868    character_id: CharacterId,
869) -> Result<CharacterContainers, PersistenceError> {
870    let character_containers = CharacterContainers {
871        loadout_container_id: get_pseudo_container_id(
872            connection,
873            character_id,
874            LOADOUT_PSEUDO_CONTAINER_POSITION,
875        )?,
876        inventory_container_id: get_pseudo_container_id(
877            connection,
878            character_id,
879            INVENTORY_PSEUDO_CONTAINER_POSITION,
880        )?,
881        overflow_items_container_id: get_pseudo_container_id(
882            connection,
883            character_id,
884            OVERFLOW_ITEMS_PSEUDO_CONTAINER_POSITION,
885        )?,
886        recipe_book_container_id: get_pseudo_container_id(
887            connection,
888            character_id,
889            RECIPE_BOOK_PSEUDO_CONTAINER_POSITION,
890        )?,
891    };
892
893    Ok(character_containers)
894}
895
896fn get_pseudo_container_id(
897    connection: &Connection,
898    character_id: CharacterId,
899    pseudo_container_position: &str,
900) -> Result<EntityId, PersistenceError> {
901    let mut stmt = connection.prepare_cached(
902        "
903        SELECT  item_id
904        FROM    item
905        WHERE   parent_container_item_id = ?1
906        AND     position = ?2",
907    )?;
908
909    #[expect(clippy::needless_question_mark)]
910    let res = stmt.query_row(
911        [
912            character_id.0.to_string(),
913            pseudo_container_position.to_string(),
914        ],
915        |row| Ok(row.get(0)?),
916    );
917
918    match res {
919        Ok(id) => Ok(id),
920        Err(e) => {
921            error!(
922                ?e,
923                ?character_id,
924                ?pseudo_container_position,
925                "Failed to retrieve pseudo container ID"
926            );
927            Err(DatabaseError(e))
928        },
929    }
930}
931
932/// Stores new pets in the database, and removes pets from the database that the
933/// player no longer has. Currently there are no actual updates to pet data
934/// since we don't store any updatable data about pets in the database.
935fn update_pets(
936    char_id: CharacterId,
937    pets: Vec<PetPersistenceData>,
938    transaction: &mut Transaction,
939) -> Result<(), PersistenceError> {
940    debug!("Updating {} pets for character {}", pets.len(), char_id.0);
941
942    let db_pets = get_pet_ids(char_id, transaction)?;
943    if !db_pets.is_empty() {
944        let dead_pet_ids = Rc::new(
945            db_pets
946                .iter()
947                .filter(|pet_id| {
948                    !pets.iter().any(|(pet, _, _)| {
949                        pet.get_database_id()
950                            .load()
951                            .is_some_and(|x| x.get() == **pet_id as u64)
952                    })
953                })
954                .map(|x| Value::from(*x))
955                .collect::<Vec<Value>>(),
956        );
957
958        if !dead_pet_ids.is_empty() {
959            delete_pets(transaction, char_id, dead_pet_ids)?;
960        }
961    }
962
963    for (pet, body, _stats) in pets
964        .iter()
965        .filter(|(pet, _, _)| pet.get_database_id().load().is_none())
966    {
967        let pet_entity_id = get_new_entity_ids(transaction, |next_id| next_id + 1)?.start;
968
969        let (body_variant, body_json) = convert_body_to_database_json(body)?;
970
971        #[rustfmt::skip]
972        let mut stmt = transaction.prepare_cached("
973            INSERT
974            INTO    body (
975                    body_id,
976                    variant,
977                    body_data)
978            VALUES  (?1, ?2, ?3)"
979        )?;
980
981        stmt.execute([
982            &pet_entity_id as &dyn ToSql,
983            &body_variant.to_string(),
984            &body_json,
985        ])?;
986
987        #[rustfmt::skip]
988        let mut stmt = transaction.prepare_cached("
989            INSERT
990            INTO    pet (
991                    pet_id,
992                    character_id,
993                    name)
994            VALUES  (?1, ?2, ?3)",
995        )?;
996
997        // TODO: use pet names here, when such feature will be implemented
998        let pet_name = "";
999        stmt.execute([&pet_entity_id as &dyn ToSql, &char_id.0, &pet_name])?;
1000        drop(stmt);
1001
1002        pet.get_database_id()
1003            .store(NonZeroU64::new(pet_entity_id as u64));
1004    }
1005
1006    Ok(())
1007}
1008
1009fn get_pet_ids(
1010    char_id: CharacterId,
1011    transaction: &mut Transaction,
1012) -> Result<Vec<i64>, PersistenceError> {
1013    #[rustfmt::skip]
1014        let mut stmt = transaction.prepare_cached("
1015        SELECT  pet_id
1016        FROM    pet
1017        WHERE   character_id = ?1
1018    ")?;
1019
1020    #[expect(clippy::needless_question_mark)]
1021    let db_pets = stmt
1022        .query_map([&char_id.0], |row| Ok(row.get(0)?))?
1023        .map(|x| x.unwrap())
1024        .collect::<Vec<i64>>();
1025    drop(stmt);
1026    Ok(db_pets)
1027}
1028
1029fn delete_pets(
1030    transaction: &mut Transaction,
1031    char_id: CharacterId,
1032    pet_ids: Rc<Vec<Value>>,
1033) -> Result<(), PersistenceError> {
1034    #[rustfmt::skip]
1035    let mut stmt = transaction.prepare_cached("
1036            DELETE
1037            FROM    pet
1038            WHERE   pet_id IN rarray(?1)"
1039    )?;
1040
1041    let delete_count = stmt.execute([&pet_ids])?;
1042    drop(stmt);
1043    debug!(
1044        "Deleted {} pets for character id {}",
1045        delete_count, char_id.0
1046    );
1047
1048    #[rustfmt::skip]
1049    let mut stmt = transaction.prepare_cached("
1050            DELETE
1051            FROM    body
1052            WHERE   body_id IN rarray(?1)"
1053    )?;
1054
1055    let delete_count = stmt.execute([&pet_ids])?;
1056    debug!(
1057        "Deleted {} pet bodies for character id {}",
1058        delete_count, char_id.0
1059    );
1060
1061    Ok(())
1062}
1063
1064pub fn update(
1065    char_id: CharacterId,
1066    char_skill_set: comp::SkillSet,
1067    inventory: Inventory,
1068    pets: Vec<PetPersistenceData>,
1069    char_waypoint: Option<comp::Waypoint>,
1070    active_abilities: comp::ability::ActiveAbilities,
1071    map_marker: Option<comp::MapMarker>,
1072    transaction: &mut Transaction,
1073) -> Result<(), PersistenceError> {
1074    // Run pet persistence
1075    update_pets(char_id, pets, transaction)?;
1076
1077    let pseudo_containers = get_pseudo_containers(transaction, char_id)?;
1078    let mut upserts = Vec::new();
1079    // First, get all the entity IDs for any new items, and identify which
1080    // slots to upsert and which ones to delete.
1081    get_new_entity_ids(transaction, |mut next_id| {
1082        let upserts_ = convert_items_to_database_items(
1083            pseudo_containers.loadout_container_id,
1084            &inventory,
1085            pseudo_containers.inventory_container_id,
1086            pseudo_containers.overflow_items_container_id,
1087            pseudo_containers.recipe_book_container_id,
1088            &mut next_id,
1089        );
1090        upserts = upserts_;
1091        next_id
1092    })?;
1093
1094    // Next, delete any slots we aren't upserting.
1095    trace!("Deleting items for character_id {}", char_id.0);
1096    let mut existing_item_ids: Vec<_> = vec![
1097        Value::from(pseudo_containers.inventory_container_id),
1098        Value::from(pseudo_containers.loadout_container_id),
1099        Value::from(pseudo_containers.overflow_items_container_id),
1100        Value::from(pseudo_containers.recipe_book_container_id),
1101    ];
1102    for it in load_items(transaction, pseudo_containers.inventory_container_id)? {
1103        existing_item_ids.push(Value::from(it.item_id));
1104    }
1105    for it in load_items(transaction, pseudo_containers.loadout_container_id)? {
1106        existing_item_ids.push(Value::from(it.item_id));
1107    }
1108    for it in load_items(transaction, pseudo_containers.overflow_items_container_id)? {
1109        existing_item_ids.push(Value::from(it.item_id));
1110    }
1111    for it in load_items(transaction, pseudo_containers.recipe_book_container_id)? {
1112        existing_item_ids.push(Value::from(it.item_id));
1113    }
1114
1115    let non_upserted_items = upserts
1116        .iter()
1117        .map(|item_pair| Value::from(item_pair.model.item_id))
1118        .collect::<Vec<Value>>();
1119
1120    let mut stmt = transaction.prepare_cached(
1121        "
1122        DELETE
1123        FROM    item
1124        WHERE   parent_container_item_id
1125        IN      rarray(?1)
1126        AND     item_id NOT IN rarray(?2)",
1127    )?;
1128    let delete_count = stmt.execute([Rc::new(existing_item_ids), Rc::new(non_upserted_items)])?;
1129    trace!("Deleted {} items", delete_count);
1130
1131    // Upsert items
1132    let expected_upsert_count = upserts.len();
1133    if expected_upsert_count > 0 {
1134        let (upserted_items, _): (Vec<_>, Vec<_>) = upserts
1135            .into_iter()
1136            .map(|model_pair| {
1137                debug_assert_eq!(
1138                    model_pair.model.item_id,
1139                    model_pair.comp.load().unwrap().get() as i64
1140                );
1141                (model_pair.model, model_pair.comp)
1142            })
1143            .unzip();
1144        trace!(
1145            "Upserting items {:?} for character_id {}",
1146            upserted_items, char_id.0
1147        );
1148
1149        // When moving inventory items around, foreign key constraints on
1150        // `parent_container_item_id` can be temporarily violated by one
1151        // upsert, but restored by another upsert. Deferred constraints
1152        // allow SQLite to check this when committing the transaction.
1153        // The `defer_foreign_keys` pragma treats the foreign key
1154        // constraints as deferred for the next transaction (it turns itself
1155        // off at the commit boundary). https://sqlite.org/foreignkeys.html#fk_deferred
1156        transaction.pragma_update(None, "defer_foreign_keys", "ON")?;
1157
1158        let mut stmt = transaction.prepare_cached(
1159            "
1160            REPLACE
1161            INTO    item (item_id,
1162                          parent_container_item_id,
1163                          item_definition_id,
1164                          stack_size,
1165                          position,
1166                          properties)
1167            VALUES  (?1, ?2, ?3, ?4, ?5, ?6)",
1168        )?;
1169
1170        for item in upserted_items.iter() {
1171            stmt.execute([
1172                &item.item_id as &dyn ToSql,
1173                &item.parent_container_item_id,
1174                &item.item_definition_id,
1175                &item.stack_size,
1176                &item.position,
1177                &item.properties,
1178            ])?;
1179        }
1180    }
1181
1182    let db_skill_groups = convert_skill_groups_to_database(char_id, char_skill_set.skill_groups());
1183
1184    let mut stmt = transaction.prepare_cached(
1185        "
1186        REPLACE
1187        INTO    skill_group (entity_id,
1188                             skill_group_kind,
1189                             earned_exp,
1190                             spent_exp,
1191                             skills,
1192                             hash_val)
1193        VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
1194    )?;
1195
1196    for skill_group in db_skill_groups {
1197        stmt.execute([
1198            &skill_group.entity_id as &dyn ToSql,
1199            &skill_group.skill_group_kind,
1200            &skill_group.earned_exp,
1201            &skill_group.spent_exp,
1202            &skill_group.skills,
1203            &skill_group.hash_val,
1204        ])?;
1205    }
1206
1207    let db_waypoint = convert_waypoint_to_database_json(char_waypoint, map_marker);
1208
1209    let mut stmt = transaction.prepare_cached(
1210        "
1211        UPDATE  character
1212        SET     waypoint = ?1
1213        WHERE   character_id = ?2
1214    ",
1215    )?;
1216
1217    let waypoint_count = stmt.execute([&db_waypoint as &dyn ToSql, &char_id.0])?;
1218
1219    if waypoint_count != 1 {
1220        return Err(PersistenceError::OtherError(format!(
1221            "Error updating character table for char_id {}",
1222            char_id.0
1223        )));
1224    }
1225
1226    let ability_sets = convert_active_abilities_to_database(char_id, &active_abilities);
1227
1228    let mut stmt = transaction.prepare_cached(
1229        "
1230        UPDATE  ability_set
1231        SET     ability_sets = ?1
1232        WHERE   entity_id = ?2
1233    ",
1234    )?;
1235
1236    let ability_sets_count = stmt.execute([
1237        &ability_sets.ability_sets as &dyn ToSql,
1238        &char_id.0 as &dyn ToSql,
1239    ])?;
1240
1241    if ability_sets_count != 1 {
1242        return Err(PersistenceError::OtherError(format!(
1243            "Error updating ability_set table for char_id {}",
1244            char_id.0,
1245        )));
1246    }
1247
1248    Ok(())
1249}