SQLite Forum

Serious error emptied my complete database!
Login

Serious error emptied my complete database!

(1) By Marcel (schuitem) on 2021-12-28 09:52:42 [link] [source]

Hello,

I am using sqlitexpert 5.4.6.545 64bit to work with sqlite database. I created a few weeks ago a new version and added tables, views and data. I am using all strict tables. sqliteexpert uses 3.37.0 sqlite dll. My laptop is using w10, recent version. Since I have a whole set of db checks I frequently get consistency errors when adding data (which is the intention). Yesterday I tried to insert a new column in an existing table and I got some sort of serious error. Sadly I clicked it away in the assumption I made a mistake myself. But I think it was a very serous error message: afterwards my complete database data was gone!!! All my tables are empty. The structure seems to be intact still.

I opened the db with the sqlite console but also this tool does not find any data in the db. :-( So it looks to me like a very serious bug. Maybe a regression bug with the new strict tables? The database file itself is still "large", I did not do a vacuum yet. A repair action also did not help to recover my data.

I am unaware if I can do something else to supply more information? I don't think there is some sort of logfile which I can supply to you?

I don't have the exact scenario sadly. I will try to recreate the situation and pay more attention to the error messages in the hope I can catch the problem again and provide a more detailed error message.

Kind regards, Marcel

(2) By Larry Brasfield (larrybr) on 2021-12-28 10:22:02 in reply to 1 [link] [source]

First, (and despite any attitude that seems to appear below), I sympathize with your data loss. I wish these scenarios were less common, as I have lost work-product myself due to inadequate safeguards against such eventualities.

Your report provides no clue whatsoever as to what "emptied" your database.

... So it looks to me like a very serious bug.

Yes, it does. But with what subset of the systems involved? To my eye, this looks like a PBK. In your "bug" diagnosis, how can you rule that bug out when you cannot remember what you did?

As you recreate your earlier work, you may find the SQLite CLI shell's .backup command useful. You should be using that, or some equivalent, regularly and with versioned or differently named backup destination files.

I don't have the exact scenario sadly. I will try to recreate the situation and pay more attention to the error messages in the hope I can catch the problem again and provide a more detailed error message.

The error messages are less important than reproducible replication steps. But before you report those here, you should study the docs for your tools to see what, precisely, those steps should do. I think you are likely to find that they should produce just the result which has dismayed you.

One final suggestion: If you are serious about a technical career, you will find it advantageous to develop the mental habit of looking first to your own role in inducing "problems" to occur before assigning blame to your tools. And when ready to assign such blame, you should investigate their documented operation carefully. That habit, along with safeguarding your work-product, will save you much time and embarrassment over the years you try to master technical systems, particularly computers and software.

BTW: Until you cease being subject to moderation, please wait a few hours before duplicating your posts here. They do not show up instantly because a moderator must approve them. (Your duplicate was rejected, not lost in the ether like your demised data.)

(3) By Marcel (schuitem) on 2021-12-28 12:59:55 in reply to 2 [link] [source]

Hi Larry,

I don't know where you are reading "my attitude", but you're responses make me feel as being attacked although I have the best intentions to supply the best info I have and let it know that there might be a (regression) problem with the introduction of the new strict tables features which I can imagine had a large impact on the internals of sqlite.

With regard to your remark that my report does not provide any clues to what scenario triggered the problem: I have written down my environment, the tools I use, the versions I use, that I use the new strict tables features for all my tables, that I have a lot of check constraints and foreign keys because all the "normal" errors I get when entering incomplete data.

You're suggestion that it is an operator error is also wrong: The only way to get data out of a db is to use some sort of drop command which might trigger cascading deletes, am I right? If so, I can ensure you that I did not do a drop command. Also no delete action from the Sqliteexpert GUI. As I have written down: I was in the process of inserting a new column to a non-empty strict table with already a few columns in it. Even tables which do not have any relation to the table I was modifying, were completely emptied! So a cascading delete can be ruled out.

You acknowledge that it looks like a serious bug and then ask me (an outside user, no sqlite developer) in what subsystem the problem is? How should I know this? Do I first have to learn all the internal details of sqlite before I can report a problem?

Wrt the exact scenario, yes I know how important this is. I am a sw developer for over 25 years last august in a highly advanced technical environment. Here we receive much worse problem reports from our customers I can tell you. Problems for which we have to search/test for weeks/months. You're remark reads to me like you only accept problem reports in which the exact problem is already spelled-out to the exact wrong code line.

Yes I agree with you that I could have made even more frequent backups. I have daily backups and I am in the process of restoring my backup. But this bug arrised after I have made many changes to my db and data after working a few hours straight, at the end of my day. I was used to be working with a high quality product, sqlite as well as sqliteexpert for years and years now. This is the first time I encountered a real serious bug. But this major issue made a big bump in my trust in sqlite.

My problem report was serious and honest and calm and as I wrote I am even willing to try to reproduce it if I can, but if I am treated like this, then I think this will be my last post and attempt to help the sqlite team/product.

Kind regards, Marcel

(4) By Stephan Beal (stephan) on 2021-12-28 13:09:47 in reply to 3 [link] [source]

I don't know where you are reading "my attitude",

That's a misunderstanding. He was proactively warning you that his response might be construed as having "attitude," in order to avoid that...

you're responses make me feel as being attacked

(5) By Larry Brasfield (larrybr) on 2021-12-28 14:25:12 in reply to 3 [link] [source]

I don't know where you are reading "my attitude",

The "attitude" I mentioned was what you might infer as mine.

but you're responses make me feel as being attacked although I have the best intentions to supply the best info I have

I do not question, nor have I questioned, your intention. No kind of attack was intended, and my criticism of your "bug" report is not an attack upon you; it is an assessment of your report on its own merits. More on this later.

...there might be a (regression) problem ... new strict tables features which I can imagine had a large impact on the internals of sqlite.

The impact was very slight. A perusal of checkins for the feature would show this.

Yes, there might be problem. And it is conceivable that you have encountered such a problem. However, the set of alternative explanations for your experience, which are consistent with the evidence you provide, is so large that this "might" cannot merit investigation by anybody who does not know a lot more of what you were doing.

With regard to your remark that my report does not provide any clues to what scenario triggered the problem: I have written down my environment, the tools I use, the versions I use, that I use the new strict tables features for all my tables, that I have a lot of check constraints and foreign keys because all the "normal" errors I get when entering incomplete data.

My "no clue whatsoever" was a slight exaggeration. If your dog was lost, and you were to tell me what on continent it was last seen, I would likely say the same. The environment does not narrow it down significantly. If somebody could repeat your problematic scenario, the software versions might be some clue. That you were using STRICT is the most potent clue you provided (and it implies SQLite v3.37), but there are still galactic numbers of inputs that would fit that "clue", far too many to begin an investigation. Your use of check constraints (and foreign keys which presumably involve similar constraints) is the beginning of a clue, and could be a whole clue if you had mentioned how they interact with your apparently changing schema.

You acknowledge that it looks like a serious bug ...

Yes, it is a serious problem when things seem so out of control. Expectations are not met, or are dissapointed. Whether the expectations or their subjects are awry was (and remains) unclear.

and then ask me (an outside user, no sqlite developer) in what subsystem the problem is?

No, I did not ask that.

How should I know this? Do I first have to learn all the internal details of sqlite before I can report a problem?

I never had nor stated any such expectation.

I expect anybody reporting a "bug", among other minima, to state what they did to induce the problematic behavior. To say "typed and clicked at tool X" does not begin to reduce the input space to a practically useful dimension.

... You're remark reads to me like you only accept problem reports in which the exact problem is already spelled-out to the exact wrong code line.

That is a serious exaggeration. Also untrue. What do you expect anybody to do with your "bug" report? I could spend weeks or months fooling around with SQLiteExpert with no confidence whatsoever that I had done what you did to induce the behavior you saw. If I was so willing to squander my time, I would focus on things that are supposed to delete data, and try to explore the boundary between what should and should not do such deletion. Then I would have to come back to you and ask: Is this what you did? But that would be futile since you claim to have forgotten what you did.

... I have daily backups ...

Well, good. I had assumed that we might have seen a schema if you still had one.

... I encountered a real serious bug.

The evidence does not yet support that assertion. Is there some you have not shared yet? It is clear that something surprising to you happened. If we knew what you had done, it might not be surprising at all. It could even be documented behavior.

But this major issue made a big bump in my trust in sqlite.

The SQLite library is pretty good at keeping data and not capriciously deleting it. Yet it does have some features which, under control of the schema designer, can cause particular data to be deleted. After several years of participating in this forum, I can say that your report is the first I've seen to the effect: "My data just vanished! It must be a bug!" If that could be substantiated, (by more than "my DB is now empty"), it would be investigated quickly and, if the report represented unintended behavior, would be fixed promptly.

if I am treated like this, then I think this will be my last post and attempt to help the sqlite team/product.

As a developer with decades of experience, surely you are aware that your report has not reached the level of an actual bug report. To be blunt: Your report is not helpful. We appreciate bug reports which state: (1) What software was used; (2) What was done to induce misbehavior; (3) What results occurred that are regarded as misbehavior; and (4) What results were expected instead that would represent correct behavior. Of these, 2 is essential. From your report, item 1 is clear, item 3 is inferrable, and item 4 can be guessed. But without item 2, there is no reasonable action to be taken.

(7) By Marcel (schuitem) on 2021-12-28 15:35:49 in reply to 5 [link] [source]

It seems like there are a lot of misunderstandings (both ways) and it seems my expectations were too high. Yes I don't expected a quick fix, that is indeed almost "impossible" from my single report alone. I submitted it to share the knowledge. Maybe more people can recognize it or encountered the same problem. In my work we also collect such vague signals and monitor them and take actions in case of e.g. the seriousness of the report or if more customers report some kind of the same issue. But I will leave it at this.

(6) By Gunter Hick (gunter_hick) on 2021-12-28 14:27:24 in reply to 1 [link] [source]

AFAICT SQLite Expert has it's own support forum at http://www.sqliteexpert.com/forum.

I suspect that whatever happened occured inside SQLite expert after you dismissed the error message, causing it to update the schema while deleting the data.

(9) By Marcel (schuitem) on 2021-12-28 15:38:55 in reply to 6 [link] [source]

Hi Gunter, thanks for the suggestion. I will see if they maybe recognize the problem I encountered.

(8) By Marcel (schuitem) on 2021-12-28 15:37:24 in reply to 1 [link] [source]

My db schema after the problem occurred:

Database: [Marcel Schuitema]
    Connection: read-write
    File name: C:\Marcel-Stack\Documents\Database\Marcel Schuitema.sqlite
    File size: 237568 bytes
    Page size: 4096
    Encoding: UTF-8
    Auto vacuum: 0
    Tables: 22
    Views: 13
    Virtual Tables: 0
------------------------------------------------------------
Table [addresses]
    Fields: 8
        [address_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [street_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [housenr]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [postalcode]: TEXT
            COLLATE 'BINARY'
        [latitude]: REAL
            COLLATE 'BINARY'
        [longitude]: REAL
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [addresses_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [address_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([street_id]) REFERENCES [streets]([street_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 2
            [] Fields: [street_id];[housenr] ON CONFLICT ROLLBACK
            [] Fields: [postalcode];[housenr] ON CONFLICT ROLLBACK
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [addresses] end
------------------------------------------------------------
Table [addresslist_diny]
    Fields: 3
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [addresslist_diny]
    Table constraints: 
        Primary Key: 
            Fields: [person_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [addresslist_diny] end
------------------------------------------------------------
Table [addresslist_marcel]
    Fields: 3
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [addresslist_marcel_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [person_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [addresslist_marcel] end
------------------------------------------------------------
Table [bankaccounts]
    Fields: 5
        [bankaccount_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [iban]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [bankaccounts_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [bankaccount_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [bankaccounts] end
------------------------------------------------------------
Table [births]
    Fields: 8
        [birth_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [birthdate]: TEXT
            COLLATE 'BINARY'
        [father_id]: INTEGER
            COLLATE 'BINARY'
        [mother_id]: INTEGER
            COLLATE 'BINARY'
        [city_id]: INTEGER
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [births_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [birth_id]
            On Conflict: ROLLBACK
        Foreign Keys: 4
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([father_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([mother_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([city_id]) REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 4
            [] Expression: '[person_id] <> [father_id]'
            [] Expression: '[person_id] <> [mother_id]'
            [] Expression: '[modified] >= [created]'
            [] Expression: '[father_id] <> [mother_id]'
Table [births] end
------------------------------------------------------------
Table [cities]
    Fields: 7
        [city_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [homepage]: TEXT
            COLLATE 'BINARY'
        [latitude]: REAL
            COLLATE 'BINARY'
        [longitude]: REAL
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [cities_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [city_id]
            On Conflict: ROLLBACK
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [cities] end
------------------------------------------------------------
Table [companies]
    Fields: 4
        [company_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: [company_id]
            On Conflict: ROLLBACK
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [companies] end
------------------------------------------------------------
Table [contacts]
    Fields: 7
        [contact_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [phone]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [email]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [homepage]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [contacts_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [contact_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [contacts] end
------------------------------------------------------------
Table [continents]
    Fields: 5
        [continent_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_eng]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_nld]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [continents_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [continent_id]
            On Conflict: ROLLBACK
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [continents] end
------------------------------------------------------------
Table [countries]
    Fields: 23
        [country_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [iso2]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [iso3]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_local]: TEXT
            COLLATE 'BINARY'
        [name_nld]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_long_nld]: TEXT
            COLLATE 'BINARY'
        [name_eng]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_long_eng]: TEXT
            COLLATE 'BINARY'
        [name_deu]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_long_deu]: TEXT
            COLLATE 'BINARY'
        [name_fra]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_long_fra]: TEXT
            COLLATE 'BINARY'
        [name_spa]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_ita]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [web]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [idd]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [isonr]: TEXT
            COLLATE 'BINARY'
        [continent_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [latitude]: REAL
            COLLATE 'BINARY'
        [longitude]: REAL
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [countries_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [country_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([continent_id]) REFERENCES [continents]([continent_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [countries] end
------------------------------------------------------------
Table [deaths]
    Fields: 6
        [death_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [diedate]: TEXT
            COLLATE 'BINARY'
        [city_id]: INTEGER
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [deaths_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [death_id]
            On Conflict: ROLLBACK
        Foreign Keys: 2
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([city_id]) REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [deaths] end
------------------------------------------------------------
Table [firstnames]
    Fields: 4
        [name]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [gender_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [firstnames_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [name]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([gender_id]) REFERENCES [genders]([gender_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [firstnames] end
------------------------------------------------------------
Table [genders]
    Fields: 3
        [gender_id]: INT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_eng]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name_nld]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: [gender_id]
            On Conflict: ROLLBACK
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 0
Table [genders] end
------------------------------------------------------------
Table [households]
    Fields: 9
        [household_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [address_id]: INTEGER
            COLLATE 'BINARY'
        [address_active]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT '1'
            COLLATE 'BINARY'
        [phone]: TEXT
            COLLATE 'BINARY'
        [homepage]: TEXT
            COLLATE 'BINARY'
        [addressing]: TEXT
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [households_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [household_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([address_id]) REFERENCES [addresses]([address_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 2
            [] Expression: '[modified] >= [created]'
            [] Expression: '[address_active] == 0 OR [address_active] == 1'
Table [households] end
------------------------------------------------------------
Table [housing]
    Fields: 5
        [housing_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [household_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [housing_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [housing_id]
            On Conflict: ROLLBACK
        Foreign Keys: 2
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([household_id]) REFERENCES [households]([household_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [housing] end
------------------------------------------------------------
Table [jobs]
    Fields: 12
        [job_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [company_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [startdate]: TEXT
            COLLATE 'BINARY'
        [enddate]: TEXT
            COLLATE 'BINARY'
        [active]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT '1'
            COLLATE 'BINARY'
        [main]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT '1'
            COLLATE 'BINARY'
        [phone]: TEXT
            COLLATE 'BINARY'
        [email]: TEXT
            COLLATE 'BINARY'
        [manager_id]: INTEGER
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [jobs_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [job_id]
            On Conflict: ROLLBACK
        Foreign Keys: 2
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([company_id]) REFERENCES [companies]([company_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 1
            [] Fields: [person_id];[company_id] ON CONFLICT ROLLBACK
        Check constraints: 5
            [] Expression: '[modified] >= [created]'
            [] Expression: '[active] == 0 OR [active] == 1'
            [] Expression: '[main] == 0 OR [main] == 1'
            [] Expression: '[enddate] >= [startdate]'
            [] Expression: '[person_id] <> [manager_id]'
Table [jobs] end
------------------------------------------------------------
Table [municipalities]
    Fields: 4
        [municipality_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [municipalities_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [municipality_id]
            On Conflict: ROLLBACK
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [municipalities] end
------------------------------------------------------------
Table [persondetails]
    Fields: 4
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [bsn]: TEXT
            UNIQUE ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [persondetails_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [person_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([person_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [persondetails] end
------------------------------------------------------------
Table [persons]
    Fields: 13
        [person_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [nickname]: TEXT
            COLLATE 'BINARY'
        [firstname]: TEXT
        [allfirstnames]: TEXT
            COLLATE 'BINARY'
        [middlename]: TEXT
        [lastname]: TEXT
        [maidenname]: TEXT
            COLLATE 'BINARY'
        [initials]: TEXT
            COLLATE 'BINARY'
        [title]: TEXT
            COLLATE 'BINARY'
        [addressing]: TEXT
            COLLATE 'BINARY'
        [gender_id]: INTEGER
            NOT NULL
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [persons_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [person_id]
            On Conflict: ROLLBACK
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [persons] end
------------------------------------------------------------
Table [relations]
    Fields: 9
        [relation_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person1_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [person2_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [active]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT '1'
            COLLATE 'BINARY'
        [married]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT '0'
            COLLATE 'BINARY'
        [weddingdate]: TEXT
            COLLATE 'BINARY'
        [weddingcity_id]: INTEGER
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [relations]
    Table constraints: 
        Primary Key: 
            Fields: [relation_id]
            On Conflict: ROLLBACK
        Foreign Keys: 3
          [] ([person1_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([person2_id]) REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
          [] ([weddingcity_id]) REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 0
        Check constraints: 4
            [] Expression: '[person1_id] <> [person2_id]'
            [] Expression: '[active] == 0 OR [active] == 1'
            [] Expression: '[married] == 0 OR [married] == 1'
            [] Expression: '[modified] >= [created]'
Table [relations] end
------------------------------------------------------------
Table [sqlite_master]
    Fields: 5
        [type]: TEXT
        [name]: TEXT
        [tbl_name]: TEXT
        [rootpage]: INTEGER
        [sql]: TEXT
    Indexes: 0
    Triggers: 0
    Table constraints: 
        Primary Key: 
            Fields: 
            On Conflict: 
        Foreign Keys: 0
        Unique constraints: 0
        Check constraints: 0
Table [sqlite_master] end
------------------------------------------------------------
Table [streets]
    Fields: 8
        [street_id]: INTEGER
            UNIQUE ON CONFLICT ROLLBACK
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [name]: TEXT
            COLLATE 'BINARY'
        [area]: TEXT
            COLLATE 'BINARY'
        [city_id]: INTEGER
            NOT NULL ON CONFLICT ROLLBACK
            COLLATE 'BINARY'
        [latitude]: REAL
            COLLATE 'BINARY'
        [longitude]: REAL
            COLLATE 'BINARY'
        [created]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
        [modified]: TEXT
            NOT NULL ON CONFLICT ROLLBACK
            DEFAULT 'CURRENT_TIMESTAMP'
            COLLATE 'BINARY'
    Indexes: 0
    Triggers: 1
        [streets_update_modified]
    Table constraints: 
        Primary Key: 
            Fields: [street_id]
            On Conflict: ROLLBACK
        Foreign Keys: 1
          [] ([city_id]) REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION
        Unique constraints: 1
            [] Fields: [name];[city_id] ON CONFLICT ROLLBACK
        Check constraints: 1
            [] Expression: '[modified] >= [created]'
Table [streets] end
------------------------------------------------------------
View  [addresses_list]
    Fields: 2
        [address_id]: INTEGER(0, -1)
        [name]: (0, -1)
    Triggers: 0
View  [addresses_list] end
------------------------------------------------------------
View  [addresses_marcel]
    Fields: 7
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
        [birthdate]: TEXT(0, -1)
        [age]: (0, -1)
        [mobile]: TEXT(0, -1)
        [phone]: TEXT(0, -1)
        [address]: (0, -1)
    Triggers: 0
View  [addresses_marcel] end
------------------------------------------------------------
View  [birthday_list]
    Fields: 6
        [month]: (0, -1)
        [day]: (0, -1)
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
        [birthdate]: TEXT(0, -1)
        [age]: (0, -1)
    Triggers: 0
View  [birthday_list] end
------------------------------------------------------------
View  [continents_list]
    Fields: 2
        [continent_id]: INTEGER(0, -1)
        [name]: TEXT(0, -1)
    Triggers: 0
View  [continents_list] end
------------------------------------------------------------
View  [femalenames_list]
    Fields: 1
        [name]: TEXT(0, -1)
    Triggers: 0
View  [femalenames_list] end
------------------------------------------------------------
View  [females_list]
    Fields: 2
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
    Triggers: 0
View  [females_list] end
------------------------------------------------------------
View  [firstnames_list]
    Fields: 1
        [name]: TEXT(0, -1)
    Triggers: 0
View  [firstnames_list] end
------------------------------------------------------------
View  [malenames_list]
    Fields: 1
        [name]: TEXT(0, -1)
    Triggers: 0
View  [malenames_list] end
------------------------------------------------------------
View  [males_list]
    Fields: 2
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
    Triggers: 0
View  [males_list] end
------------------------------------------------------------
View  [persons_alive_list]
    Fields: 2
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
    Triggers: 0
View  [persons_alive_list] end
------------------------------------------------------------
View  [persons_deceased_list]
    Fields: 5
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
        [diedate]: TEXT(0, -1)
        [city]: TEXT(0, -1)
        [ago]: (0, -1)
    Triggers: 0
View  [persons_deceased_list] end
------------------------------------------------------------
View  [persons_list]
    Fields: 2
        [person_id]: INTEGER(0, -1)
        [name]: (0, -1)
    Triggers: 0
View  [persons_list] end
------------------------------------------------------------
View  [relations_list]
    Fields: 3
        [relation_id]: INTEGER(0, -1)
        [person1]: (0, -1)
        [person2]: (0, -1)
    Triggers: 0
View  [relations_list] end
------------------------------------------------------------

(10) By Marcel (schuitem) on 2021-12-28 15:46:33 in reply to 1 [link] [source]

Maybe the DDL is easier:

CREATE TABLE [cities](
  [city_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name] TEXT NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY, 
  [homepage] TEXT COLLATE BINARY, 
  [latitude] REAL COLLATE BINARY, 
  [longitude] REAL COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([city_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [streets](
  [street_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name] TEXT COLLATE BINARY, 
  [area] TEXT COLLATE BINARY, 
  [city_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [latitude] REAL COLLATE BINARY, 
  [longitude] REAL COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([street_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created]), 
  UNIQUE([name] COLLATE [BINARY] ASC, [city_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK) STRICT, WITHOUT ROWID;

CREATE TABLE [addresses](
  [address_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [street_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [streets]([street_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [housenr] TEXT NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY, 
  [postalcode] TEXT COLLATE BINARY, 
  [latitude] REAL COLLATE BINARY, 
  [longitude] REAL COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([address_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created]), 
  UNIQUE([street_id] COLLATE [BINARY] ASC, [housenr] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  UNIQUE([postalcode] COLLATE [BINARY] ASC, [housenr] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK) STRICT, WITHOUT ROWID;

CREATE TABLE [persons](
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [nickname] TEXT COLLATE BINARY, 
  [firstname] TEXT, 
  [allfirstnames] TEXT COLLATE BINARY, 
  [middlename] TEXT, 
  [lastname] TEXT, 
  [maidenname] TEXT COLLATE BINARY, 
  [initials] TEXT COLLATE BINARY, 
  [title] TEXT COLLATE BINARY, 
  [addressing] TEXT COLLATE BINARY, 
  [gender_id] INTEGER NOT NULL COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([person_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [addresslist_diny](
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([person_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [addresslist_marcel](
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([person_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [bankaccounts](
  [bankaccount_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [iban] TEXT NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([bankaccount_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [births](
  [birth_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [birthdate] TEXT COLLATE BINARY, 
  [father_id] INTEGER COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [mother_id] INTEGER COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [city_id] INTEGER COLLATE BINARY REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([birth_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([person_id] <> [father_id]), 
  CHECK([person_id] <> [mother_id]), 
  CHECK([modified] >= [created]), 
  CHECK([father_id] <> [mother_id])) STRICT, WITHOUT ROWID;

CREATE TABLE [companies](
  [company_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([company_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK(modified >= created)) STRICT, WITHOUT ROWID;

CREATE TABLE [contacts](
  [contact_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [phone] TEXT UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [email] TEXT UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [homepage] TEXT UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([contact_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [continents](
  [continent_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_eng] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_nld] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([continent_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [countries](
  [country_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [iso2] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [iso3] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_local] TEXT COLLATE BINARY, 
  [name_nld] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_long_nld] TEXT COLLATE BINARY, 
  [name_eng] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_long_eng] TEXT COLLATE BINARY, 
  [name_deu] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_long_deu] TEXT COLLATE BINARY, 
  [name_fra] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_long_fra] TEXT COLLATE BINARY, 
  [name_spa] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_ita] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [web] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [idd] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [isonr] TEXT COLLATE BINARY, 
  [continent_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [continents]([continent_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [latitude] REAL COLLATE BINARY, 
  [longitude] REAL COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([country_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [deaths](
  [death_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [diedate] TEXT COLLATE BINARY, 
  [city_id] INTEGER COLLATE BINARY REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([death_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [genders](
  [gender_id] INT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_eng] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name_nld] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  PRIMARY KEY([gender_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK) STRICT, WITHOUT ROWID;

CREATE TABLE [firstnames](
  [name] TEXT NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [gender_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [genders]([gender_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([name] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [households](
  [household_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name] TEXT NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY, 
  [address_id] INTEGER COLLATE BINARY REFERENCES [addresses]([address_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [address_active] INTEGER NOT NULL ON CONFLICT ROLLBACK DEFAULT 1 COLLATE BINARY, 
  [phone] TEXT COLLATE BINARY, 
  [homepage] TEXT COLLATE BINARY, 
  [addressing] TEXT COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([household_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created]), 
  CHECK([address_active] == 0 or [address_active] == 1)) STRICT, WITHOUT ROWID;

CREATE TABLE [housing](
  [housing_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [household_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [households]([household_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([housing_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [jobs](
  [job_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [company_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [companies]([company_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [startdate] TEXT COLLATE BINARY, 
  [enddate] TEXT COLLATE BINARY, 
  [active] INTEGER NOT NULL ON CONFLICT ROLLBACK DEFAULT 1 COLLATE BINARY, 
  [main] INTEGER NOT NULL ON CONFLICT ROLLBACK DEFAULT 1 COLLATE BINARY, 
  [phone] TEXT COLLATE BINARY, 
  [email] TEXT COLLATE BINARY, 
  [manager_id] INTEGER COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([job_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created]), 
  CHECK(active == 0 or active == 1), 
  CHECK(main == 0 or main == 1), 
  CHECK(enddate >= startdate), 
  CHECK(person_id <> manager_id), 
  UNIQUE([person_id] COLLATE [BINARY] ASC, [company_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK) STRICT, WITHOUT ROWID;

CREATE TABLE [municipalities](
  [municipality_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [name] TEXT NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([municipality_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [persondetails](
  [person_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [bsn] TEXT UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([person_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE TABLE [relations](
  [relation_id] INTEGER NOT NULL ON CONFLICT ROLLBACK UNIQUE ON CONFLICT ROLLBACK COLLATE BINARY, 
  [person1_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [person2_id] INTEGER NOT NULL ON CONFLICT ROLLBACK COLLATE BINARY REFERENCES [persons]([person_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [active] INTEGER NOT NULL ON CONFLICT ROLLBACK DEFAULT 1 COLLATE BINARY, 
  [married] INTEGER NOT NULL ON CONFLICT ROLLBACK DEFAULT 0 COLLATE BINARY, 
  [weddingdate] TEXT COLLATE BINARY, 
  [weddingcity_id] INTEGER COLLATE BINARY REFERENCES [cities]([city_id]) ON DELETE NO ACTION ON UPDATE NO ACTION, 
  [created] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  [modified] TEXT NOT NULL ON CONFLICT ROLLBACK DEFAULT CURRENT_TIMESTAMP COLLATE BINARY, 
  PRIMARY KEY([relation_id] COLLATE [BINARY] ASC) ON CONFLICT ROLLBACK, 
  CHECK([person1_id] <> [person2_id]), 
  CHECK([active] == 0 OR [active] == 1), 
  CHECK([married] == 0 OR [married] == 1), 
  CHECK([modified] >= [created])) STRICT, WITHOUT ROWID;

CREATE VIEW [addresses_list]
AS
SELECT 
       [address_id], 
       [streets].[name] || ' ' || [housenr] || ', ' || [postalcode] || ', ' || [cities].[name] AS [name]
FROM   [addresses]
       LEFT JOIN [streets] ON [addresses].[street_id] == [streets].[street_id]
       LEFT JOIN [cities] ON [streets].[city_id] == [cities].[city_id];

CREATE VIEW [persons_list]
AS
SELECT 
       [person_id], 
       CASE WHEN [firstname] IS NULL
         AND [middlename] IS NOT NULL
         AND [lastname] IS NOT NULL THEN [middlename] || ' ' || [lastname] WHEN [firstname] IS NOT NULL
         AND [middlename] IS NOT NULL
         AND [lastname] IS NOT NULL THEN [firstname] || ' ' || [middlename] || ' ' || [lastname] WHEN [firstname] IS NOT NULL
         AND [middlename] IS NULL
         AND [lastname] IS NOT NULL THEN [firstname] || ' ' || [lastname] END AS [name]
FROM   [persons]
ORDER  BY
          [lastname],
          [middlename],
          [firstname];

CREATE VIEW [persons_alive_list]
AS
SELECT 
       [persons_list].[person_id], 
       [persons_list].[name]
FROM   [persons_list]
       LEFT JOIN [deaths] ON [persons_list].[person_id] == [deaths].[person_id]
       LEFT JOIN [persons] ON [persons_list].[person_id] == [persons].[person_id]
WHERE  [deaths].[death_id] IS NULL
ORDER  BY
          [persons].[lastname],
          [persons].[middlename],
          [persons].[firstname];

CREATE VIEW [addresses_marcel]
AS
SELECT 
       [addresslist_marcel].[person_id], 
       [persons_alive_list].[name], 
       [birthdate], 
       CAST ((JULIANDAY (DATE ('now')) - JULIANDAY ([birthdate])) / 365.25 AS [INT]) AS [age], 
       [contacts].[phone] AS [mobile], 
       [households].[phone], 
       [addresses_list].[name] AS [address]
FROM   [addresslist_marcel]
       JOIN [persons_alive_list] ON [addresslist_marcel].[person_id] == [persons_alive_list].[person_id]
       LEFT JOIN [persons] ON [persons].[person_id] == [addresslist_marcel].[person_id]
       LEFT JOIN [births] ON [persons].[person_id] == [births].[person_id]
       LEFT JOIN [contacts] ON [addresslist_marcel].[person_id] == [contacts].[person_id]
       LEFT JOIN [housing] ON [addresslist_marcel].[person_id] == [housing].[person_id]
       LEFT JOIN [households] ON [housing].[household_id] == [households].[household_id]
       LEFT JOIN [addresses_list] ON [households].[address_id] == [addresses_list].[address_id]
ORDER  BY
          [persons].[lastname],
          [persons].[middlename],
          [persons].[firstname];

CREATE VIEW [birthday_list]
AS
SELECT 
       STRFTIME ('%m', [birthdate]) AS [month], 
       STRFTIME ('%d', [birthdate]) AS [day], 
       [births].[person_id], 
       [name], 
       [birthdate], 
       CAST ((JULIANDAY (DATE ('now')) - JULIANDAY ([birthdate])) / 365.25 AS [INT]) AS [age]
FROM   [persons_alive_list]
       LEFT JOIN [births] ON [births].[person_id] == [persons_alive_list].[person_id]
WHERE  [birthdate] IS NOT NULL
ORDER  BY
          STRFTIME ('%m', [birthdate]),
          STRFTIME ('%d', [birthdate]);

CREATE VIEW [continents_list]
AS
SELECT 
       [continent_id], 
       [name_nld] AS [name]
FROM   [continents]
ORDER  BY [name_nld];

CREATE VIEW [femalenames_list]
AS
SELECT [name]
FROM   [firstnames]
WHERE  [gender_id] = 3
ORDER  BY [name];

CREATE VIEW [females_list]
AS
SELECT 
       [person_id], 
       CASE WHEN [firstname] IS NULL
         AND [middlename] IS NOT NULL
         AND [lastname] IS NOT NULL THEN [middlename] || ' ' || [lastname] WHEN [firstname] IS NOT NULL
         AND [middlename] IS NOT NULL
         AND [lastname] IS NOT NULL THEN [firstname] || ' ' || [middlename] || ' ' || [lastname] WHEN [firstname] IS NOT NULL
         AND [middlename] IS NULL
         AND [lastname] IS NOT NULL THEN [firstname] || ' ' || [lastname] END AS [name]
FROM   [persons]
WHERE  [gender_id] = 3
ORDER  BY
          [lastname],
          [middlename],
          [firstname];

CREATE VIEW [firstnames_list]
AS
SELECT [name]
FROM   [firstnames]
ORDER  BY [name];

CREATE VIEW [malenames_list]
AS
SELECT [name]
FROM   [firstnames]
WHERE  [gender_id] = 2
ORDER  BY [name];

CREATE VIEW [males_list]
AS
SELECT 
       [person_id], 
       CASE WHEN [firstname] IS NULL
         AND [middlename] IS NOT NULL
         AND [lastname] IS NOT NULL THEN [middlename] || ' ' || [lastname] WHEN [firstname] IS NOT NULL
         AND [middlename] IS NOT NULL
         AND [lastname] IS NOT NULL THEN [firstname] || ' ' || [middlename] || ' ' || [lastname] WHEN [firstname] IS NOT NULL
         AND [middlename] IS NULL
         AND [lastname] IS NOT NULL THEN [firstname] || ' ' || [lastname] END AS [name]
FROM   [persons]
WHERE  [gender_id] = 2
ORDER  BY
          [lastname],
          [middlename],
          [firstname];

CREATE VIEW [persons_deceased_list]
AS
SELECT 
       [persons_list].[person_id], 
       [persons_list].[name], 
       [diedate], 
       [cities].[name] AS [city], 
       CAST ((JULIANDAY (DATE ('now')) - JULIANDAY ([diedate])) / 365.25 AS [INT]) AS [ago]
FROM   [persons_list]
       LEFT JOIN [deaths] ON [persons_list].[person_id] == [deaths].[person_id]
       LEFT JOIN [cities] ON [deaths].[city_id] == [cities].[city_id]
       LEFT JOIN [persons] ON [persons_list].[person_id] == [persons].[person_id]
WHERE  [deaths].[death_id] IS NOT NULL
ORDER  BY
          [persons].[lastname],
          [persons].[middlename],
          [persons].[firstname];

CREATE VIEW [relations_list]
AS
SELECT 
       [relation_id], 
       [p1].[name] AS [person1], 
       [p2].[name] AS [person2]
FROM   [relations]
       LEFT JOIN [persons_list] AS [p1] ON [relations].[person1_id] == [p1].[person_id]
       LEFT JOIN [persons_list] AS [p2] ON [relations].[person2_id] == [p2].[person_id];

CREATE TRIGGER [addresses_update_modified] BEFORE UPDATE ON [addresses]
BEGIN
  UPDATE
    [addresses]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [address_id] = [old].[address_id];
END;

CREATE TRIGGER [addresslist_diny] BEFORE UPDATE ON [addresslist_diny]
BEGIN
  UPDATE
    [addresslist_diny]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [person_id] = [old].[person_id];
END;

CREATE TRIGGER [addresslist_marcel_update_modified] BEFORE UPDATE ON [addresslist_marcel]
BEGIN
  UPDATE
    [addresslist_marcel]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [person_id] = [old].[person_id];
END;

CREATE TRIGGER [bankaccounts_update_modified] BEFORE UPDATE ON [bankaccounts]
BEGIN
  UPDATE
    [bankaccounts]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [bankaccount_id] = [old].[bankaccount_id];
END;

CREATE TRIGGER [births_update_modified] BEFORE UPDATE ON [births]
BEGIN
  UPDATE
    [births]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [birth_id] = [old].[birth_id];
END;

CREATE TRIGGER [cities_update_modified] BEFORE UPDATE ON [cities]
BEGIN
  UPDATE
    [cities]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [city_id] = [old].[city_id];
END;

CREATE TRIGGER [contacts_update_modified] BEFORE UPDATE ON [contacts]
BEGIN
  UPDATE
    [contacts]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [contact_id] = [old].[contact_id];
END;

CREATE TRIGGER [continents_update_modified] BEFORE UPDATE ON [continents]
BEGIN
  UPDATE
    [continents]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [continent_id] = [old].[continent_id];
END;

CREATE TRIGGER [countries_update_modified] BEFORE UPDATE ON [countries]
BEGIN
  UPDATE
    [countries]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [country_id] = [old].[country_id];
END;

CREATE TRIGGER [deaths_update_modified] BEFORE UPDATE ON [deaths]
BEGIN
  UPDATE
    [deaths]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [death_id] = [old].[death_id];
END;

CREATE TRIGGER [firstnames_update_modified] BEFORE UPDATE ON [firstnames]
BEGIN
  UPDATE
    [firstnames]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [name] = [old].[name];
END;

CREATE TRIGGER [households_update_modified] BEFORE UPDATE ON [households]
BEGIN
  UPDATE
    [households]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [household_id] = [old].[household_id];
END;

CREATE TRIGGER [housing_update_modified] BEFORE UPDATE ON [housing]
BEGIN
  UPDATE
    [housing]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [housing_id] = [old].[housing_id];
END;

CREATE TRIGGER [jobs_update_modified] BEFORE UPDATE ON [jobs]
BEGIN
  UPDATE
    [jobs]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [job_id] = [old].[job_id];
END;

CREATE TRIGGER [municipalities_update_modified] BEFORE UPDATE ON [municipalities]
BEGIN
  UPDATE
    [municipalities]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [municipality_id] = [old].[municipality_id];
END;

CREATE TRIGGER [persondetails_update_modified] BEFORE UPDATE ON [persondetails]
BEGIN
  UPDATE
    [persondetails]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [person_id] = [old].[person_id];
END;

CREATE TRIGGER [persons_update_modified] BEFORE UPDATE ON [persons]
BEGIN
  UPDATE
    [persons]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [person_id] = [old].[person_id];
END;

CREATE TRIGGER [relations] BEFORE UPDATE ON [relations]
BEGIN
  UPDATE
    [relations]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [relation_id] = [old].[relation_id];
END;

CREATE TRIGGER [streets_update_modified] BEFORE UPDATE ON [streets]
BEGIN
  UPDATE
    [streets]
  SET
    [modified] = CURRENT_TIMESTAMP
  WHERE
    [street_id] = [old].[street_id];
END;

(11) By TripeHound on 2021-12-29 00:28:21 in reply to 10 [link] [source]

My first thought, on seeing ON CONFLICT ROLLBACK throughout the schema, is that perhaps what you were doing was – intentionally or unintentionally – all happening within one big transaction? Doing something to cause a conflict would then "lose" all data added in that transaction.

Although "Yesterday I tried to insert a new column" suggests you might have inserted the data that was lost on a previous day, if a transaction had (somehow) been started in SQLiteExpert (or the SQLite CLI) before inserting data, and you happened to have left the program open, the transaction could still be "active" several days later.

(13) By Marcel (schuitem) on 2021-12-30 08:28:13 in reply to 11 [link] [source]

Hi TripeHound,

Thanks for your constructive questions. I have those rollbacks indeed to prevent wrong data in my db. Yes I agree that all the data which was inserted in the transaction would be lost, this is also what I would expect. But the situation was indeed different: I had about 20 strict tables with data in them, then I wanted to insert a new column with sqliteexpert. This not only made the table empty which I was editing, but also all my other tables which don't have any relation with the table I was editing, were also empty. This is what I would not expect.

At the moment of the schema changes / data entries / problem, I was only using sqliteexpert alone. I know of the CLI, but this tool I was not using that day. I used it after the problem occurred to verify that the CLI also saw my complete empty db, and sadly it did also see the entire empty db.

Regards, Marcel

(16) By Tim Streater (Clothears) on 2021-12-30 09:31:45 in reply to 13 [link] [source]

But the situation was indeed different: I had about 20 strict tables with data in them, then I wanted to insert a new column with sqliteexpert. This not only made the table empty which I was editing, but also all my other tables which don't have any relation with the table I was editing, were also empty. This is what I would not expect.

What do the sqliteexpert people have to say about this?

(12) By Donald Griggs (dfgriggs) on 2021-12-29 18:57:12 in reply to 10 [link] [source]

Hi, Marcel,

Is there even a small chance that, when the error occurred, you were unwittingly using an older version of sqliteexpert (or a current version but loading an older sqlite dll) and that sqliteexpert didn't show the error of:

Error: malformed database schema (tablename) - near "STRICT": syntax error

That would explain the symptom of all your tables appearing empty.

Any chance your database (or wal file) was encumbered by antivirus software at the time?

Any chance your database was moved, but not the associated active WAL file or might the WAL file have been deleted? (if using WAL mode, obviously)

On a copy of the problem database, and using sqlite3.exe utility program version 3.37.0:

  1. Did "pragma integrity_check" return with good status?
  2. Did the sqlite analyzer utility return anything interesting?
    https://www.sqlite.org/sqlanalyze.html
  3. Does ".vacuum into" result in a database containing data?
  4. As a last resort, there's always the ".recover" command. https://www.sqlite.org/cli.html#recover

Don't know if any of this is helpful,
Donald

(14) By Marcel (schuitem) on 2021-12-30 09:10:09 in reply to 12 [link] [source]

Hi Donald,

Thank you for thinking with me! At the moment of the problem I was using the newest version of sqliteexpert which was mentioned on the download page. I just upgraded my version and saw that the integrated sqlite was 3.37, so also the newest version. In the release notes of sqlite 3.37 I saw the mentioning of the strict tables which I wanted to use so I was happy that the newest version of sqliteexpert was already updated to support it. Also the "strict" option was in the GUI when creating a new table.

After the problem occurred I was looking for if there might have already been an update of sqliteexpert and the "Check for updates" already showed me a newer version, but according to their support (I have a license) that was only a very small unrelated fix which was not yet announced at their download page. This would be fixed in short notice on their website. So as far as I know I was using all the newest software and not a mix-up of incompatible versions. Hence my idea of starting this thread since their might be a regression bug somewhere.

I have indeed antivirus software (Bitdefender), but it is hard to determine if that might have interfered with my wal file. This suggestion triggered me to think of the following: Could it be that my db have become completely empty due to a problem with it being stored on a kind of "OneDrive" directory? Everything in this directory is uploaded automatically to my cloud storage which also makes my daily backups.

The integrity check in sqliteexpert mentions my db is ok, but my data is gone, only my schema is still there.

It looks to me that the analyzer utility still sees some "consumed bytes", but I will attach the output in a separate reply.

I wanted to try the .vacuum but it is not recognized anymore in the sqlite3.exe? I thought it was in there. I tried the vacuum my db in sqliteexpert and it report "Ok". The entire db (22 tables) were still empty. The physical db remained the same size on disk so to me it looks like the data is still "available" in the db since it is not cleaned up by vacuum? Am I right?

Thanks for the suggestion to try .recover! I will try that a bit later, I have to go now. Hopefully I can recover some data that way :-)

Thanks, Marcel

(17) By Rowan Worth (sqweek) on 2021-12-30 09:55:36 in reply to 14 [source]

Note this forum is specifically for sqlite the library, and support for other software such as sqliteexpert cannot be expected here. Looking at the docs I see little to no scope for an "ALTER TABLE ADD COLUMN" command to result in data loss.

https://sqlite.org/lang_altertable.html#alter_table_add_column

However if sqliteexpert is using the more generic approach to implement your requested schema change (see section 6), there is clearly potential for rows to not be transferred to the new table. The overall option should still be "safe" in the sense that you can roll back the entire transaction, but if an error in the process is ignored/overlooked and it proceeds to COMMIT instead of ROLLBACK I can see this being a result.

It looks to me that the analyzer utility still sees some "consumed bytes", but I will attach the output in a separate reply.

The database that you ran the analyzer on has no data -- it is gone, kaput. The only table containing any bytes of payload is SQLITE_SCHEMA (the contents of which are what they sound like).

Could it be that my db have become completely empty due to a problem with it being stored on a kind of "OneDrive" directory? Everything in this directory is uploaded automatically to my cloud storage which also makes my daily backups.

I certainly wouldn't rule it out. Without a thorough analysis of how the cloud drives interact with file locking primitives I wouldn't trust any of them to be sufficiently robust to guarantee atomicity and durability for an sqlite database.

I wanted to try the .vacuum but it is not recognized anymore in the sqlite3.exe?

It's a proper SQL command, not a shell dot-command. ie. it is spelled "VACUUM;" rather than ".vacuum"

(15) By Marcel (schuitem) on 2021-12-30 09:11:04 in reply to 12 [link] [source]

The analyzer results:
/** Disk-Space Utilization Report For Marcel Schuitema.sqlite

Page size in bytes................................ 4096      
Pages in the whole file (measured)................ 58        
Pages in the whole file (calculated).............. 58        
Pages that store data............................. 58         100.0% 
Pages on the freelist (per header)................ 0            0.0% 
Pages on the freelist (calculated)................ 0            0.0% 
Pages of auto-vacuum overhead..................... 0            0.0% 
Number of tables in the database.................. 22        
Number of indices................................. 28        
Number of defined indices......................... 0         
Number of implied indices......................... 28        
Size of the file in bytes......................... 237568    
Bytes of user payload stored...................... 0            0.0% 

*** Page counts for all tables with their indices *****************************

COUNTRIES......................................... 12          20.7% 
SQLITE_SCHEMA..................................... 9           15.5% 
CONTACTS.......................................... 5            8.6% 
ADDRESSES......................................... 3            5.2% 
CONTINENTS........................................ 3            5.2% 
GENDERS........................................... 3            5.2% 
BIRTHS............................................ 2            3.4% 
COMPANIES......................................... 2            3.4% 
DEATHS............................................ 2            3.4% 
HOUSING........................................... 2            3.4% 
JOBS.............................................. 2            3.4% 
PERSONDETAILS..................................... 2            3.4% 
STREETS........................................... 2            3.4% 
ADDRESSLIST_DINY.................................. 1            1.7% 
ADDRESSLIST_MARCEL................................ 1            1.7% 
BANKACCOUNTS...................................... 1            1.7% 
CITIES............................................ 1            1.7% 
FIRSTNAMES........................................ 1            1.7% 
HOUSEHOLDS........................................ 1            1.7% 
MUNICIPALITIES.................................... 1            1.7% 
PERSONS........................................... 1            1.7% 
RELATIONS......................................... 1            1.7% 

*** Page counts for all tables and indices separately *************************

SQLITE_SCHEMA..................................... 9           15.5% 
ADDRESSES......................................... 1            1.7% 
ADDRESSLIST_DINY.................................. 1            1.7% 
ADDRESSLIST_MARCEL................................ 1            1.7% 
BANKACCOUNTS...................................... 1            1.7% 
BIRTHS............................................ 1            1.7% 
CITIES............................................ 1            1.7% 
COMPANIES......................................... 1            1.7% 
CONTACTS.......................................... 1            1.7% 
CONTINENTS........................................ 1            1.7% 
COUNTRIES......................................... 1            1.7% 
DEATHS............................................ 1            1.7% 
FIRSTNAMES........................................ 1            1.7% 
GENDERS........................................... 1            1.7% 
HOUSEHOLDS........................................ 1            1.7% 
HOUSING........................................... 1            1.7% 
JOBS.............................................. 1            1.7% 
MUNICIPALITIES.................................... 1            1.7% 
PERSONDETAILS..................................... 1            1.7% 
PERSONS........................................... 1            1.7% 
RELATIONS......................................... 1            1.7% 
SQLITE_AUTOINDEX_ADDRESSES_2...................... 1            1.7% 
SQLITE_AUTOINDEX_ADDRESSES_3...................... 1            1.7% 
SQLITE_AUTOINDEX_BIRTHS_2......................... 1            1.7% 
SQLITE_AUTOINDEX_COMPANIES_2...................... 1            1.7% 
SQLITE_AUTOINDEX_CONTACTS_2....................... 1            1.7% 
SQLITE_AUTOINDEX_CONTACTS_3....................... 1            1.7% 
SQLITE_AUTOINDEX_CONTACTS_4....................... 1            1.7% 
SQLITE_AUTOINDEX_CONTACTS_5....................... 1            1.7% 
SQLITE_AUTOINDEX_CONTINENTS_2..................... 1            1.7% 
SQLITE_AUTOINDEX_CONTINENTS_3..................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_10..................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_11..................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_12..................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_2...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_3...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_4...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_5...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_6...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_7...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_8...................... 1            1.7% 
SQLITE_AUTOINDEX_COUNTRIES_9...................... 1            1.7% 
SQLITE_AUTOINDEX_DEATHS_2......................... 1            1.7% 
SQLITE_AUTOINDEX_GENDERS_2........................ 1            1.7% 
SQLITE_AUTOINDEX_GENDERS_3........................ 1            1.7% 
SQLITE_AUTOINDEX_HOUSING_2........................ 1            1.7% 
SQLITE_AUTOINDEX_JOBS_2........................... 1            1.7% 
SQLITE_AUTOINDEX_PERSONDETAILS_2.................. 1            1.7% 
SQLITE_AUTOINDEX_STREETS_2........................ 1            1.7% 
STREETS........................................... 1            1.7% 

*** All tables and indices ****************************************************

Percentage of total database...................... 100.0%    
Number of entries................................. 81        
Bytes of storage consumed......................... 237568    
Bytes of payload.................................. 29781       12.5% 
Bytes of metadata................................. 994          0.42% 
Average payload per entry......................... 367.67    
Average unused bytes per entry.................... 2553.00   
Average metadata per entry........................ 12.27     
Average fanout.................................... 36.00     
Maximum payload per entry......................... 1944      
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 1         
Primary pages used................................ 57        
Overflow pages used............................... 0         
Total pages used.................................. 58        
Unused bytes on index pages....................... 3935        96.1% 
Unused bytes on primary pages..................... 202858      86.9% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 206793      87.0% 

*** All tables ****************************************************************

Percentage of total database......................  51.7%    
Number of entries................................. 81        
Bytes of storage consumed......................... 122880    
Bytes of payload.................................. 29781       24.2% 
Bytes of metadata................................. 770          0.63% 
Average payload per entry......................... 367.67    
Average unused bytes per entry.................... 1139.86   
Average metadata per entry........................ 9.51      
Average fanout.................................... 8.00      
Maximum payload per entry......................... 1944      
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 1         
Primary pages used................................ 29        
Overflow pages used............................... 0         
Total pages used.................................. 30        
Unused bytes on index pages....................... 3935        96.1% 
Unused bytes on primary pages..................... 88394       74.4% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 92329       75.1% 

*** All indices ***************************************************************

Percentage of total database......................  48.3%    
Number of entries................................. 0         
Bytes of storage consumed......................... 114688    
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 224          0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 28        
Overflow pages used............................... 0         
Total pages used.................................. 28        
Unused bytes on primary pages..................... 114464      99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 114464      99.80% 

*** Table ADDRESSES and all its indices ***************************************

Percentage of total database......................   5.2%    
Number of entries................................. 0         
Bytes of storage consumed......................... 12288     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 24           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 3         
Overflow pages used............................... 0         
Total pages used.................................. 3         
Unused bytes on primary pages..................... 12264       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 12264       99.80% 

*** Table ADDRESSES w/o any indices *******************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Indices of table ADDRESSES ************************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Index SQLITE_AUTOINDEX_ADDRESSES_2 of table ADDRESSES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_ADDRESSES_3 of table ADDRESSES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table ADDRESSLIST_DINY ****************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table ADDRESSLIST_MARCEL **************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table BANKACCOUNTS ********************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table BIRTHS and all its indices ******************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table BIRTHS w/o any indices **********************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_BIRTHS_2 of table BIRTHS ***************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table CITIES **************************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table COMPANIES and all its indices ***************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table COMPANIES w/o any indices *******************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COMPANIES_2 of table COMPANIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table CONTACTS and all its indices ****************************************

Percentage of total database......................   8.6%    
Number of entries................................. 0         
Bytes of storage consumed......................... 20480     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 40           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 5         
Overflow pages used............................... 0         
Total pages used.................................. 5         
Unused bytes on primary pages..................... 20440       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 20440       99.80% 

*** Table CONTACTS w/o any indices ********************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Indices of table CONTACTS *************************************************

Percentage of total database......................   6.9%    
Number of entries................................. 0         
Bytes of storage consumed......................... 16384     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 32           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 4         
Overflow pages used............................... 0         
Total pages used.................................. 4         
Unused bytes on primary pages..................... 16352       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 16352       99.80% 

*** Index SQLITE_AUTOINDEX_CONTACTS_2 of table CONTACTS ***********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_CONTACTS_3 of table CONTACTS ***********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_CONTACTS_4 of table CONTACTS ***********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_CONTACTS_5 of table CONTACTS ***********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table CONTINENTS and all its indices **************************************

Percentage of total database......................   5.2%    
Number of entries................................. 0         
Bytes of storage consumed......................... 12288     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 24           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 3         
Overflow pages used............................... 0         
Total pages used.................................. 3         
Unused bytes on primary pages..................... 12264       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 12264       99.80% 

*** Table CONTINENTS w/o any indices ******************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Indices of table CONTINENTS ***********************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Index SQLITE_AUTOINDEX_CONTINENTS_2 of table CONTINENTS *******************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_CONTINENTS_3 of table CONTINENTS *******************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table COUNTRIES and all its indices ***************************************

Percentage of total database......................  20.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 49152     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 96           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 12        
Overflow pages used............................... 0         
Total pages used.................................. 12        
Unused bytes on primary pages..................... 49056       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 49056       99.80% 

*** Table COUNTRIES w/o any indices *******************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Indices of table COUNTRIES ************************************************

Percentage of total database......................  19.0%    
Number of entries................................. 0         
Bytes of storage consumed......................... 45056     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 88           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 11        
Overflow pages used............................... 0         
Total pages used.................................. 11        
Unused bytes on primary pages..................... 44968       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 44968       99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_10 of table COUNTRIES ********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_11 of table COUNTRIES ********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_12 of table COUNTRIES ********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_2 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_3 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_4 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_5 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_6 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_7 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_8 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_COUNTRIES_9 of table COUNTRIES *********************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table DEATHS and all its indices ******************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table DEATHS w/o any indices **********************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_DEATHS_2 of table DEATHS ***************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table FIRSTNAMES **********************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table GENDERS and all its indices *****************************************

Percentage of total database......................   5.2%    
Number of entries................................. 0         
Bytes of storage consumed......................... 12288     
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 24           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 3         
Overflow pages used............................... 0         
Total pages used.................................. 3         
Unused bytes on primary pages..................... 12264       99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 12264       99.80% 

*** Table GENDERS w/o any indices *********************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Indices of table GENDERS **************************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Index SQLITE_AUTOINDEX_GENDERS_2 of table GENDERS *************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_GENDERS_3 of table GENDERS *************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table HOUSEHOLDS **********************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table HOUSING and all its indices *****************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table HOUSING w/o any indices *********************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_HOUSING_2 of table HOUSING *************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table JOBS and all its indices ********************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table JOBS w/o any indices ************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_JOBS_2 of table JOBS *******************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table MUNICIPALITIES ******************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table PERSONDETAILS and all its indices ***********************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table PERSONDETAILS w/o any indices ***************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_PERSONDETAILS_2 of table PERSONDETAILS *************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table PERSONS *************************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table RELATIONS ***********************************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Table SQLITE_SCHEMA *******************************************************

Percentage of total database......................  15.5%    
Number of entries................................. 81        
Bytes of storage consumed......................... 36864     
Bytes of payload.................................. 29781       80.8% 
Bytes of metadata................................. 602          1.6% 
B-tree depth...................................... 2         
Average payload per entry......................... 367.67    
Average unused bytes per entry.................... 80.01     
Average metadata per entry........................ 7.43      
Average fanout.................................... 8.00      
Non-sequential pages.............................. 6           75.0% 
Maximum payload per entry......................... 1944      
Entries that use overflow......................... 0            0.0% 
Index pages used.................................. 1         
Primary pages used................................ 8         
Overflow pages used............................... 0         
Total pages used.................................. 9         
Unused bytes on index pages....................... 3935        96.1% 
Unused bytes on primary pages..................... 2546         7.8% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 6481        17.6% 

*** Table STREETS and all its indices *****************************************

Percentage of total database......................   3.4%    
Number of entries................................. 0         
Bytes of storage consumed......................... 8192      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 16           0.20% 
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 2         
Overflow pages used............................... 0         
Total pages used.................................. 2         
Unused bytes on primary pages..................... 8176        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 8176        99.80% 

*** Table STREETS w/o any indices *********************************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Index SQLITE_AUTOINDEX_STREETS_2 of table STREETS *************************

Percentage of total database......................   1.7%    
Number of entries................................. 0         
Bytes of storage consumed......................... 4096      
Bytes of payload.................................. 0            0.0% 
Bytes of metadata................................. 8            0.20% 
B-tree depth...................................... 1         
Average payload per entry......................... 0.0       
Average unused bytes per entry.................... 0.0       
Average metadata per entry........................ 0.0       
Maximum payload per entry......................... 0         
Entries that use overflow......................... 0         
Primary pages used................................ 1         
Overflow pages used............................... 0         
Total pages used.................................. 1         
Unused bytes on primary pages..................... 4088        99.80% 
Unused bytes on overflow pages.................... 0         
Unused bytes on all pages......................... 4088        99.80% 

*** Definitions ***************************************************************

Page size in bytes

    The number of bytes in a single page of the database file.  
    Usually 1024.

Number of pages in the whole file

    The number of 4096-byte pages that go into forming the complete
    database

Pages that store data

    The number of pages that store data, either as primary B*Tree pages or
    as overflow pages.  The number at the right is the data pages divided by
    the total number of pages in the file.

Pages on the freelist

    The number of pages that are not currently in use but are reserved for
    future use.  The percentage at the right is the number of freelist pages
    divided by the total number of pages in the file.

Pages of auto-vacuum overhead

    The number of pages that store data used by the database to facilitate
    auto-vacuum. This is zero for databases that do not support auto-vacuum.

Number of tables in the database

    The number of tables in the database, including the SQLITE_SCHEMA table
    used to store schema information.

Number of indices

    The total number of indices in the database.

Number of defined indices

    The number of indices created using an explicit CREATE INDEX statement.

Number of implied indices

    The number of indices used to implement PRIMARY KEY or UNIQUE constraints
    on tables.

Size of the file in bytes

    The total amount of disk space used by the entire database files.

Bytes of user payload stored

    The total number of bytes of user payload stored in the database. The
    schema information in the SQLITE_SCHEMA table is not counted when
    computing this number.  The percentage at the right shows the payload
    divided by the total file size.

Percentage of total database

    The amount of the complete database file that is devoted to storing
    information described by this category.

Number of entries

    The total number of B-Tree key/value pairs stored under this category.

Bytes of storage consumed

    The total amount of disk space required to store all B-Tree entries
    under this category.  The is the total number of pages used times
    the pages size.

Bytes of payload

    The amount of payload stored under this category.  Payload is the data
    part of table entries and the key part of index entries.  The percentage
    at the right is the bytes of payload divided by the bytes of storage 
    consumed.

Bytes of metadata

    The amount of formatting and structural information stored in the
    table or index.  Metadata includes the btree page header, the cell pointer
    array, the size field for each cell, the left child pointer or non-leaf
    cells, the overflow pointers for overflow cells, and the rowid value for
    rowid table cells.  In other words, metadata is everything that is neither
    unused space nor content.  The record header in the payload is counted as
    content, not metadata.

Average payload per entry

    The average amount of payload on each entry.  This is just the bytes of
    payload divided by the number of entries.

Average unused bytes per entry

    The average amount of free space remaining on all pages under this
    category on a per-entry basis.  This is the number of unused bytes on
    all pages divided by the number of entries.

Non-sequential pages

    The number of pages in the table or index that are out of sequence.
    Many filesystems are optimized for sequential file access so a small
    number of non-sequential pages might result in faster queries,
    especially for larger database files that do not fit in the disk cache.
    Note that after running VACUUM, the root page of each table or index is
    at the beginning of the database file and all other pages are in a
    separate part of the database file, resulting in a single non-
    sequential page.

Maximum payload per entry

    The largest payload size of any entry.

Entries that use overflow

    The number of entries that user one or more overflow pages.

Total pages used

    This is the number of pages used to hold all information in the current
    category.  This is the sum of index, primary, and overflow pages.

Index pages used

    This is the number of pages in a table B-tree that hold only key (rowid)
    information and no data.

Primary pages used

    This is the number of B-tree pages that hold both key and data.

Overflow pages used

    The total number of overflow pages used for this category.

Unused bytes on index pages

    The total number of bytes of unused space on all index pages.  The
    percentage at the right is the number of unused bytes divided by the
    total number of bytes on index pages.

Unused bytes on primary pages

    The total number of bytes of unused space on all primary pages.  The
    percentage at the right is the number of unused bytes divided by the
    total number of bytes on primary pages.

Unused bytes on overflow pages

    The total number of bytes of unused space on all overflow pages.  The
    percentage at the right is the number of unused bytes divided by the
    total number of bytes on overflow pages.

Unused bytes on all pages

    The total number of bytes of unused space on all primary and overflow 
    pages.  The percentage at the right is the number of unused bytes 
    divided by the total number of bytes.

*******************************************************************************
The entire text of this report can be sourced into any SQL database
engine for further analysis.  All of the text above is an SQL comment.
The data used to generate this report follows:
*/
BEGIN;
CREATE TABLE space_used(
   name clob,        -- Name of a table or index in the database file
   tblname clob,     -- Name of associated table
   is_index boolean, -- TRUE if it is an index, false for a table
   is_without_rowid boolean, -- TRUE if WITHOUT ROWID table  
   nentry int,       -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   depth int,        -- Depth of the b-tree
   payload int,      -- Total amount of data stored in this table or index
   ovfl_payload int, -- Total amount of data stored on overflow pages
   ovfl_cnt int,     -- Number of entries that use overflow
   mx_payload int,   -- Maximum payload size
   int_pages int,    -- Number of interior pages used
   leaf_pages int,   -- Number of leaf pages used
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int,      -- Number of gaps in the page layout
   compressed_size int  -- Total bytes stored on disk
);
INSERT INTO space_used VALUES('sqlite_schema','sqlite_schema',0,0,88,81,2,29781,0,0,1944,1,8,0,3935,2546,0,6,36864);
INSERT INTO space_used VALUES('genders','genders',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_genders_2','genders',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_genders_3','genders',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('firstnames','firstnames',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('persons','persons',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('addresslist_marcel','addresslist_marcel',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('addresslist_diny','addresslist_diny',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('deaths','deaths',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_deaths_2','deaths',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('births','births',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_births_2','births',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('continents','continents',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_continents_2','continents',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_continents_3','continents',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('contacts','contacts',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_contacts_2','contacts',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_contacts_3','contacts',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_contacts_4','contacts',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_contacts_5','contacts',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('relations','relations',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('housing','housing',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_housing_2','housing',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('countries','countries',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_2','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_3','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_4','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_5','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_6','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_7','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_8','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_9','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_10','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_11','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_countries_12','countries',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('persondetails','persondetails',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_persondetails_2','persondetails',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('companies','companies',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_companies_2','companies',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('jobs','jobs',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_jobs_2','jobs',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('bankaccounts','bankaccounts',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('streets','streets',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_streets_2','streets',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('addresses','addresses',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_addresses_2','addresses',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('sqlite_autoindex_addresses_3','addresses',1,0,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('households','households',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('cities','cities',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
INSERT INTO space_used VALUES('municipalities','municipalities',0,1,0,0,1,0,0,0,0,0,1,0,0,4088,0,0,4096);
COMMIT;

(18) By Donald Griggs (dfgriggs) on 2021-12-31 21:32:21 in reply to 15 [link] [source]

You probably know this, but you'd want to be sure to run the analyzer, .recover, etc on a copy of your database made just after you found your problem.

The vacuum command will destroy any deleted content.

If you had not changed "pragma secure_delete" from its default of 'off' (0)
https://www.sqlite.org/pragma.html#pragma_secure_delete
and you have not vacuumed or further manipulated data, I believe you would find your data in a hex dump of the database -- though maybe in a form not so trivial to restore. That would imply that you, sqliteExpert, or sqlite itself (unlikely) decided to delete your data.

If you do not find your data in a hex dump, and it passed the "pragma integrity_check" then I suspect you can conclude the data was never present in that file. (If that suspicion is wrong, I hope more knowledgeable folks will correct me.)