![]() ![]() IF (thisfield = "") returns TRUE to a zero-length string and FALSE to any concrete value, but returns NULL to a null value in the field. IF ISBLANK(this field) returns TRUE to fields with null values and FALSE to any other content (including a field with a zero-length string) Having detected this inconsistency, I was able to pin it down in the calculated fields, by use of two different conditional expressions: (I suspect that different databases may behave differently, allowing only one or the other but not both.) Visually, this distinction was completely hidden in the LO forms and tables. ![]() ![]() I discovered in due course - using SQLite Studio to examine the data files - that in the “old” data, “empty” text fields were represented by zero-length strings, while in the new configuration, such fields in new records were stored as NULL values. The problem presented itself in certain reports, in which, inexplicably, calculated fields for certain records came out blank when they should not have done. One of the more puzzling I have resolved only recently, and the discovery may be of use to others. As I have progressively replicated what it used to do on another platform, all sorts of glitches have had to be ironed out. In its current incarnation the data is stored in an SQLite database, with LibreOffice Base as the front-end. csv files and then imported to another platform. I have been working on a database that has passed through various transformations, including being exported as. After doing it manually this first time, I plan on then writing a script to do it for me so that the next time I update the database to get the most recent data from PokéAPI, I won't have to do any manual entries.This is not a question, but an observation - and I hope it is the right place to post it. Since there are hundreds of Pokémon, I gave them all the default form Pokémon id ( where p.is_default = 1) and then manually went through and updated the Pokémon ids for the entries for alternate forms. This SQL query below is what I used to add the new column. That way there is a way to see which Pokémon specifically the entry is for, rather than just the species id. To help combat this, I added a new column into the pokemon_v2_pokemonspeciesflavortext table called pokemon_id, which is a foreign key tied to the id in the pokemon_v2_pokemon table. Hence when trying to retrieve the Pokédex entries for, say, Alolan Raichu, you will get back all of the entries for both Kantonian Raichu and Alolan Raichu, with no way to differentiate which entry is for which form. While they all have their own unique Pokémon ids, they all share the same Pokémon species id. The issue arises for Pokémon species who have multiple different forms (i.e. Instead, the entries are tied to a Pokémon species. With the updates from Let's Go and Sword/Shield, I found that trying to fetch the different Pokédex entry descriptions, or "flavor text" were missing some important information for tying the entry to its associated Pokémon. Now you can write your SQL queries! Click on the "Play" icon to run them. To make a query, click on the "Open SQL Editor" button. You can explore the tables by clicking on the drop down arrows for the database you just opened in SQLiteStudio. This will just be the name that it's under in the SQLiteStudio sidebar. Optionally give the database a "name" input.Navigate to the db.sqlite3 file that you're using for your server. For the "File" input, click on the folder icon to browse your computer for an existing database file.Make sure the "Database Type" is set to SQLite 3.Open a new database by clicking on the "Add a database" button (you might have to hover over the icons to see the labels).If you choose to go this route, you'll need to make sure you have SQLiteStudio installed. Using SQLiteStudioįor the SQL queries, I used SQLiteStudio, a free desktop application for browsing and editing SQLite database files. As a result, the data for those games in this database are still partial and incomplete. PokéAPI typically relies on Veekun for the data, but since no data for the Let's Go or Sword/Shield games has been added, PokéAPI has begun to gradually add the data from those games themselves. **This post assumes a basic knowledge of SQL
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |