SQLite Forum


7 forum posts by user DasGoravani

21:37 Reply: Database Growing by itself to huge sizes (artifact: 083ec0849b user: DasGoravani)
I have discovered by trial and error that the table is corrupted. I can't insert to it, nor update, nor delete. My edits to the table are done as a delete then an insert.. the deletes are not happening, and the inserts are inserting ghost records that don't respond to selects and have no data in them but they do have size..

There were 500 records according to the analyzer but only 3 that could be selected and viewed, that had data in them.

I am working within Omnis, the language, that most have never heard of, it does the SQL for you, it calls SQLite's API's for me.. I am shielded from real SQL and all.. so I can't be certain where any fault lay...

Just thought I'd log on and try to wrap this thread up.

Everything says my table is corrupted.. logic says that 350 columns with 40 of them being pictures and another 40 are lists.. logic says it's too big, so I am today breaking out the pictures at least to their own table.. then remake this table fresh and hope that without the pictures it can function correctly.. 

If that isn't enough then I'll do the lists next.. break them out into their own table.. do this breakup of the central record until it and other new tables all behave correctly

I really appreciate this forum, it is invaluable.. SQLite is awesome, and this forum rocks it. 

Peace out,

Das Goravani
00:45 Reply: Database Growing by itself to huge sizes (artifact: 8a7112b98c user: DasGoravani)

Thank you for your reply. I found it helpful.

The thing that is odd is that there were once when I checked only 3 records in that table. They are small and cannot account for the many gigabytes they were taking up according to SQLite analyzer, the command line tool. That is what has me baffled. I would take responsibility if I had a repeat loop of inserts going and if at least I could see those records, but they aren't there, only inflated size that VACUUM fails to remove, oddly.

That is why I am baffled.. do you have any insights on that? Thank you very much.

17:36 Post: Database Growing by itself to huge sizes (artifact: 41e8709f00 user: DasGoravani)
Hello everyone,

I use Macs. On one it's Catalina and on the other its Mojave OS's.  Use is single user.  I have a few places where I use the SQLite db. In both places it is growing by itself quite fast and quite large.  My actual data is 350 MB and after a fresh import this is how big the db is. But it just sits there.. nothing is being done, and it grows.. now it's up to 8 GB in size.. at this point I am not live, so it's not a problem to just replace it with the fresh after import db.. but once I go live, this will be a problem.

Does anyone have any ideas about what would make a SQLite db just simply grow?  I have used the analyzer and am now aware of which table it is that is doing it.. and it makes sense.. that is my largest record.. it's a record that is my constants file.. there is only one record, supposedly, in that table.. it holds many pictures and lists too.. it is a huge record, I dont know how big but lets say it's a MB large.. one MB.. maybe 2, but not 8 GB

The odd thing too is that when I use VACUUM to try to shrink the db it grows a little.. instead of shrinking. 

So I'm at a loss as to what is going on.  Otherwise than this I am loving SQLite. 

Thank you

Das Goravani
18:39 Reply: Best way to handle unique column needs (artifact: c9834b789d user: DasGoravani)

My app is Astrology, it is not a business app, it's always single user and desktop, so the CPU cycles needed for auto increment are probably not a problem.. this is not mission critical or multi user or LAN or WAN, it's non of that, it's local, on a desktop machine, Mac or Windows, single user.. at home in peace.. so to speak :-)

To be clear: I have about 50 tables that already have an Integer Primary Key column which is an old column that was used to contain my unique key values that connect records in other tables, if these would assign unique values higher than the preexisting values in that column, I would be fine already, but I'm getting zeros back from fetches after inserts.. it appears the field is not inserting any number, rowid et al.. I'm not getting it back.

So I remain with my problem, which my previous post makes clear. My data sizes in some tables reach a quarter million records, in most it's just a few thousand.

If I am to add any column constraints such as auto increment or mumbleID I realize I have to recreate my tables correct? I have to copy old aside, recreate, copy data back, delete unneeded copy, I have to do that to add these constraints correct? Wish that alter table could do it but whatever.

So before I recreate with mumbleId and auto increment just wanted to get one last reassurance that that is a good plan and find out what mumbleID means or does..

18:27 Reply: Best way to handle unique column needs (artifact: 5545e7279c user: DasGoravani)
I’m more informed now.

Some answers refer me back to the trick of having your primary key be an integer, and then it will reflect the rowed. However in my attempts I have found that it doesn’t return any number. When I save a record then fetch it back that field is zero. That’s my first problem. The main trick used for this purpose doesn’t seem to work. I realize that it should, but I find I only get zeros. That’s problem one. Which leads to my main problem still needing resolution… that is I need for new records a number to be assigned in the old column which contains 4000 records that already have a number assigned, which may be used for stored connections with other tables. So I want to preserve the old, and have new records get a new number. Higher than the last number used. 

To achieve this the one poster Larry Brasfield appears to be most on top of it. However, he says some things that confuse me. He says to recreate my tables and use MUMBLEID AND AUTOINCREMENT. 

My first question is what does MUMBLEID do or mean? 

Then I’m not clear.. on new records, do I have to pack the unique field myself, or will it get packed automatically?  One line refers to me fetching the last rowid and makes it seem like I have to do that to pack the field with that data.. which I can do, but I thought auto increment would do it for me. Or do you mean I have to fetch it only because that is my unique number and it will be inserted but if I want it sooner than a new fetch I need to fetch it after an insert.

My users are only single user always.. so fetching the last rowid inserted does make sense in my case. 

So I’m still not clear on if a number will be inserted for me, into that field, such that if I fetch the record back after an insert, that field will have a number in it. That is my aim. That when I save a NEW record a number will be put into that column such that when I fetch the record back it will have a  number in that column that is unique. 

Without full knowledge I got the FEELING that Larry;s answer was the closest… that mumbleID and auto increment are the answer. 

If however just having an INTEGER PRIMARY KEY field, which I already have, would do the trick, yes that column I’m interested in is already the Integer Primary Key.. if it would pack itself with a unique value that respects the other values already in that column in that table, then I would be set, but I just get back zeros in that column when I fetch after an insert. 

So there’s my need, unique on top of data already there, verse the problem of the primary key that is allready there not returning a value, but that system may not respect the data I already have in many records within the tables in question. 

I hope that clarifies my needs and situation. 

Thank you all for responding, I really appreciate it. 

Das Goravani

On May 5, 2020, at 5:09 PM, Larry Brasfield <noreplyf16e6c3f5@sqlite.org> wrote:

Forum post by LarryBrasfield on 2020-05-06 00:09:12

Your problem seems to be easily solved.

For your new tables, be sure to declare the unique integer value column as: " mumbleId INTEGER PRIMARY KEY AUTOINCREMENT, " with mumbleId chosen more intelligently.  This will be required to be unique as a consequence of being the primary key, and, per [this Rowid Tables](https://sqlite.org/rowidtable.html) documentation, will be an alias for the underlying rowid. (You may not care about this, but it improves efficiency somewhat.) Then, simply insert your values, specifying the mumbleId column values as the same unique numeric integer values from your old DB.  The autoincrement tracking will take care of tracking the highest value inserted so that, if ever an insert is done without specifying the mumbleId value, it can be generated. That would be the only circumstance where you would need to "fetch the last rowid after an insert."

If this does not work for some reason, or does not solve your issue, please show what goes wrong and be more specific about what shortcoming you perceive. (I had a difficult time understanding what problem you actually encountered.) It would help to show DDL and queries, together with results that seem awry.

One issue that was unclear in your post: We are discussing a primary key, right? And it is already referenced from other tables, I would expect. So you have an interest in preserving those linkages, right?
21:37 Post: Best way to handle unique column needs (artifact: 55d7c8aeea user: DasGoravani)
I have an existing application that I have brought over into SQLite, or I'm at early stages of doing so.

Right now I'm dealing with the issue of making my old unique key fields be that again but in SQLite.

So I have 50 tables which have such a column.. used to be the unique column in old database, the column has values in the data from the old database.. now I need that field to be the same.. a unique column numeric integer 32 bit value, and thus I need it to auto fill itself with a value, either overwriting my already there values, or starting after the highest present value

Can you think of a way to do the above in SQLite? 

I see that I can fetch the last rowid after an insert, so if I was starting from scratch I would fetch that and save it in a column to use as my unique identifier.  I thought having the used to be our unique field set to primary key, that it would get the rowid value in it, but actually I'm getting the reverse, I'm getting the old field values in selects where I ask for the rowid

So I'm confused as to how to get my column of choice to be a unique numeric column since the rowid won't come into it at present.. I save a new record and my numeric primary key returns null or zero even after fetching back.. it doesn't have the rowid in it.. will an auto incrementing restraint help me ?  I'd have to remake my tables to do that.. from what I understand

Any help on making my old column still be a unique numeric identifier column appreciated.  Or other comments as I am new.
23:08 Post: New Column not saving data (artifact: 80c870f2ed user: DasGoravani)

Greetings Forum,

I have an application written in Omnis Studio, the high level RAD language, which uses SQL as its backend, you get to choose which SQL, I chose SQLite.

So far so good... been saving and retrieving data to database Omnis made for me.

But now I need to add a column, so I added it to the database, and in Omnis to it's Schema and File Class for that Database Table. It has to be added in these 3 places, so I did, all align.

I got feedback from the database that indeed the column added correctly..

The datatype I put in, and this relates to Omnis, is "LIST"

Omnis has a whole bunch of data types that SQLite responds to, including picture, date fields, and lists

In the database Omnis created for me I have many lists, and the database feedback on my columns includes that word as a data type in that column that normally says CHAR TEXT REAL etc, it says LIST in those.

So I mimicked what they had already done that was working, and it didn't work.

My data is good all the way up to and through my UPDATE statement, my list is packed, before and after I issue that, and it says it went through no errors

But when I fetch the record fresh to check if my list got saved it is indeed NULL

I can't get this new column to save data.

So I switched to a database backup from before adding the field

And added it again, this time as BLOB

And tried again to save data to it and retrieve it back, and it's NULL

So now I've tried an Omnis recommended data type that matches the Omnis internal data type, and then I tried a SQLite native data type, and both did not work to save and retrieve only this new column, the rest of the data comes in just fine

this new column is not working

I added a column the other day, and it works, but it is numeric and in a different table

this table I'm adding a column to has 379 columns, many of which are picture and list, blobs so far as I know

What could possibly be stopping my new column from working?