Auto-increment problem in back-ups

Hi,

Bearing in mind I know next to nothing about SQLite databases, I think there may be a problem with Networx back-ups which are now in SQLite database format.

Manually adding a record to a Networx back-up sometimes fails because the day number and hour number auto-increment (as opposed to the record number). When the hour number exceeds 23 an error message prevents the manual addition of further records because the record has breached the restriction placed on the data.

Is this a problem with the Networx back-up or with the database program?

Regards,
Rod

I don't see the problem. The software stores its data in Day-Hour format.

Day is the date is stored as the number of days since 30 Dec 1899. Hour is the hour on that day between 0 and 23. There's no 24th hour. For example:

USER_NAME	DAY	HOUR
Andrew          42404   0
Andrew          42404   1
...
Andrew          42404   22
Andrew          42404   23
Andrew          42405   0
Andrew          42405   1
...

Hi again Andrew,

I am finding that if the previous record was for hour 23 and I then want to add a further record, an error message tells me I cannot add the record because the hour number is 24 (which breaches the data restriction for that field). At this poinr I haven't entered an hour number myself but it has been incremented from the last record.

Regards,
Rod

First, you shouldn't be updating your backup file, you should be updating the main file (or a copy).

There's only one record per user + day + hour, i.e. that's a 'unique key', you can't have multiple records with the same key so what you're doing is wrong.

If you want to 'update' a specific record/unique key then you can do that with sqlite very easily by clicking on the value and then editing the cell value for down or up.

Make a copy of the networx file to play with and gain experience using sqlite...

J

Quote

Praxis

First, you shouldn't be updating your backup file, you should be updating the main file (or a copy).
. . .
Make a copy of the networx file to play with and gain experience using sqlite...

That's exactly what I'm doing. I don't know what you mean by the "main file" - do you mean an exported .csv file?

Quote

Bearing in mind I know next to nothing about SQLite databases, I think there may be a problem with Networx back-ups which are now in SQLite database format.

well it wasn't clear which file that you meant, you said your 'backup' which is a static file concept i.e. at a specific time.
and you commented that it was now in the sqlite format. So I assumed that you weren't talking about a 'copy'.

the 'main' file is the networx.db file that's getting updated throughout the day.

a 'copy' of the 'main' file is a backup at a specific time, etc. and is always in the same format because you use the Copy/Paste option in Windows.

J

Quote

That's exactly what I'm doing.

are you really sure?

"backup.DB" is smaller than "networx.db" on my system

Why would you want to modify the backup? Were you intending to import it back into the "main" file?

Quote

Praxis

Quote

That's exactly what I'm doing.

are you really sure?

"backup.DB" is smaller than "networx.db" on my system

Why would you want to modify the backup? Were you intending to import it back into the "main" file?

Long story but essentially I created a back-up (.db file) before I reset Nerworx at the end of my monthly bandwidth allowance but I did it a day late so just wanted to restore the previous day's data back into Networx for the current month.

I had done this in the past by editing the .xml back-up file but the back-up format has recently changed into a .db file which cannot be edited by a text editor.

Enter the SQLite browser, about which I knew absolutely nothing. Playing around with a copy of the back-up (.db) file I tried to add in the previous day's usage to create a new back-up file which I could restore into Networx.

Hope you're still with me.

Anyway, I tried to enter the data manually using the SQLite browser but it would only go so far - each time I pressed the "New Record" button a new record would be created already pre-loaded with day number and an hour number. But once the hour number reached 23 it wouldn't let me create a new record but, instead, issued a message that a data restriction had been breached. Clearly, it would not allow an hour number 24 (Reasonable) but I had not entered any hour number myself because it had been auto-incremented by something in the program or database.

I got round the issue in the end by importing all the previous day's data from .csv file. So problem solved but I just wondered why the database file auto-incremented fields when creating new records.

I'm learning more about the SQLite browser as I go and expect to resolve the problem in due course but it does seem to me odd that fields are being incremented, out of my control, during manual data entry. If I create a new database file, this sort of thing doesn't happen when manually entering records. Hence my question regarding the Networx back-up file.

Hope all this makes sense.

I guess this is something SQLite browser does by auto-incrementing the hour field. It may do so for convenience, but it's unaware of the restrictions we put on the data.

Like Praxis explained, the user-day-hour combination is a unique key and on top of that there are constraints to maintain the DB integrity. One of these constraints requires any hour to be between 0 and 23.

Quote

Hope all this makes sense.

Yes, now it does.

As Andrew points out, sqlite doesn't know about networx and that Netwrx is using hour is a 'time' field, after all the values are simple integers, there's no tell-tale format such as "yyyy.mm.dd.hh.mm.ss". Same for the day field

So it simply adds 1 to the hour value in the 'last' record, and that's not wrong for it to do that.

As said, the extra usage being 'moved' from one month to another month has to be by modifying the appropriate records in both months (decease one, increase the other.

Now of course if there hadn't been a record for the 23rd hour, e.g. the last record was hour 22, then the Add a record would have worked and produced Hour 23.....
but if it had been 15 then the added one would be 16, so we have to be flexible, etc.

Glad you've fixed everything up now!

J

Quote

Andrew

I guess this is something SQLite browser does by auto-incrementing the hour field. It may do so for convenience, but it's unaware of the restrictions we put on the data.

Like Praxis explained, the user-day-hour combination is a unique key and on top of that there are constraints to maintain the DB integrity. One of these constraints requires any hour to be between 0 and 23.

Thanks Andrew.

The point I am trying to make is that the SQLite browser itself does not auto-increment data fields, only record numbers. With a new database created in the browser, I can manually add records ad infinitum (or until the database has reached its data limit) - it's only the Networx back-up which exhibits this behaviour.

Regards,
Rod

Quote

Praxis

. . . So it simply adds 1 to the hour value in the 'last' record, and that's not wrong for it to do that. . .

Please see my reply to Andrew.

And many thanks for your input.

Regards,
Rod

Quote

With a new database created in the browser, I can manually add records ad infinitum

true if you haven't specified a unique index on the database table

but

not true when there's a unique index specified for the database table... which is what networx is doing with user day hour.

Unique means that there cannot be multiple records with the same values in all key fields.

J

I decided to see what the sqlitebrowser can do and found that when I tried to add a record to the usage table by clicking on the [New Record] button that it tries to add/insert a record with incorrect values for record number, day and hour.
I have no idea how it picks those values, it certainly doesn't open up an input window to allow me to specify the values which is what I'd expect, so in my opinion the browser is at fault.
Note: it decided to increment both the day and hour field and make the record number = 1 when there was already a record #1. Crazy.
It should have inserted record 3104 and let me fill in the rest, then checked for 'uniqueness'.

CREATE TABLE "usage" (USER_NAME VARCHAR(64) NOT NULL, DAY INTEGER NOT NULL,HOUR INTEGER DEFAULT 0 NOT NULL CHECK (HOUR BETWEEN 0 AND 23), DATA_IN BIGINT NOT NULL, DATA_OUT BIGINT NOT NULL, DIALUP_DURATION INTEGER NOT NULL,PRIMARY KEY (USER_NAME, DAY, HOUR))


So, instead of this 'browser', try sqlitespy. It doesn't let you add records but it does allow you to change values for IN & OUT for records that already exist, which is really what you want to do. Of course you have to do a bit of math to get the right values and you may not have the desired hour but there should be one with the correct day (but maybe not).

However there's a question still bugging me, you said this problem was limited to only the backup. Were you able to get this to work with the networx.db file (the 'main' one that networx.exe updates all day long... ) ?

Quote

Praxis

. . . However there's a question still bugging me, you said this problem was limited to only the backup. Were you able to get this to work with the networx.db file (the 'main' one that networx.exe updates all day long... ) ?

I'm a newbie to SQLite so I haven't experimented with any .db files except the Networx back-up file. I have only compared it with the behaviour of a newly created (blank) database in the SQLite browser and there is no such problem arising there. Sorry if what I said was misleading.

I haven't played with a copy of Networx.db yet but that seems like a good next move. I'll report back when I've done that.

Thanks for the suggestion.

Regards,
Rod

Hi again,

I opened a copy of Networx.db in the browser and pressed the [New Record] button and the same thing is happening as with the back-up file.

Table: 'usage'
Last Record No.: 30, Name, 42404, 22, 0, 0, 0

--------------------------------
Pressed [New Record] button and got the following error message:
Error adding record:
CHECK constraint failed: usage (INSERT INTO
'usage' ('USER_NAME','DAY','HOUR','DATA_OUT','DIALUP_DURATION')
VALUES (1,42405,24,0,0,0);)
---------------------------------

So, as you found, something is auto-incrementing both the day and the hour before even entering any data for the record.

Any more thoughts?

Regards,
Rod

Like I said it's something SQLite browser does by auto-incrementing the hour field. It may do so for convenience, but it's unaware of the constraints we put on the data. If you want to know what constraints are, you can start here.

Bottom line: in order to maintain the database integrity those constraints will not let you enter an hour above 23 or add more than one unique combination of user-day-hour.

Quote

Rod

Hi again,

I opened a copy of Networx.db in the browser and pressed the [New Record] button and the same thing is happening as with the back-up file.

Table: 'usage'
Last Record No.: 30, Name, 42404, 22, 0, 0, 0

--------------------------------
Pressed [New Record] button and got the following error message:
Error adding record:
CHECK constraint failed: usage (INSERT INTO
'usage' ('USER_NAME','DAY','HOUR','DATA_OUT','DIALUP_DURATION')
VALUES (1,42405,24,0,0,0)
---------------------------------

So, as you found, something is auto-incrementing both the day and the hour before even entering any data for the record.

Any more thoughts?

Regards,
Rod

and it also reset the record number to 1 rather than 31 so its the browser that's the 'something' that's screwing up, is that not clear?

Quote

Praxis

. . . and it also reset the record number to 1 rather than 31 so its the browser that's the 'something' that's screwing up, is that not clear?

Well, as I understand it, it attempted to set the 'USER_NAME' to 1 (not the record number), the 'DAY' to 42405 (05/02/16), the 'HOUR' to 24 - and this is the entry which would breach the restriction.

It is not clear to me that the browser is to blame because a blank database (i.e. not restrictions imposed on any field) does NOT demonstrate this behaviour.

However, the issue has become academic now because I know I can export the 'user' table, edit it and then re-import it using .csv files before restoring it back to Networx.

I expect I'll get to the bottom of it as I learn a bit more about SQLite but, for now, it's just a curiosity.

Thanks very much for your input on this.

Regards,
Rod

Well it might make sense to ask the author of the browser why his program does what it does. Right now all we know is it doesn't work properly and we can guess all kinds of things but he's the one to debug his program

I think I'll have look for some non-Networx databases to see if they behave better.

I just smacks to me of two (or more) restrictions clashing with each other to confuse the browser.

It's all a bit new to me at the moment so I could be way off the mark. We'll see.

Thanks again.

Regards,
Rod

You shouldn't attribute human characteristics to inanimate things by saying that databases behave and programs get confused. Programs simply do what their author has defined/designed them to do and databases simply exist as defined by the person creating them.

All browsers are programs but not all programs are browsers. Do you say 'browser' when refering to Excel, since Excel lets you browse through a spreadsheet? Do you say 'browser' when refering to Word, since Word lets you browse through a text document? sqlitebrowser has the word browser in it's name but it's still just a program used to define, look at and maintain (some simple) files called sql databases.

This sqlitebrowser program may very well work properly when working with databases created using sqlitebrowser. Why? Because that would be the objective of the author, else 'bugs' would be only too obvious. However the authors database concepts incorporated into sqlitebrowser appear to be a limited subset of the total specs of sql and therefore cannot be used properly/fully 'work' with databases defined/created by other programs that use the full set.

Quite likely the sqlitebrowser author didn't attempt to make his program work with databases created by other programs that use other features that he didn't. Why would he? So the author was satisfied that his program worked properly.

Then you try to use the sqlitebrowser program to maintain networx.db, you have a problem and you conclude that networx.db must be the cause. Your justification for your conclusion is that "if you create a database with sqlitebrowser you can add records ad infinitum...". Is your conclusion correct? No. You're incorrect in blaming the database (networx.db) for the sqlitebrowser limitations.

networx.db can be maintained not only by networx.exe but also by the sqlitespy and sqlite3 programs without any 'bugs'.

sqlite3 is a command line only utility (that has 'browser like' functions) that allows full maintenance including a way to add records to databases, including networx.db. You may have problems if you don't follow the rules but that's your problem, not sqlite3's problem.

sqlitespy has a UI (which acts like a browser) that allows limited maintenance to existing records but doesn't allow additions to the database. Not adding records may be a problem but only in the sense that the function just isn't there, it doesn't pop up a screen screaming "Error..."


So it may be wise quit wasting time testing other databases not created by networx.
Sure, you may find simple databases that 'work', but that's not the point, sqlitebrowser is limited and therefore doesn't work with the more complex networx.db.

Problem solved.

In the Edit Programs tab there is an option, selected by default, which allows you to "Ignore Check Constraints". Deselecting this option allows the manual addition of a new record. It will often still be pre-loaded with constraint-breaking data in the fields but at least you get the chance to correct the field data. Obviously, the Ignore Check Constraints option will need to be deselected before closing the database.

It seems to me that adding records manually in this way may only be useful for adding the odd record if that should be required. For multiple records it would be quicker to export a .csv file for editing.

Thanks again Praxis and Andrew for your input.

Regards,
Rod

Sorry, that have read: the Ignore Check Constraints option is DESELECTED by default.

That's good that you kept at it until you found the solution.

I have to admit that I had no motivation to get to know that program in depth since I knew of two others that could do what's required.

Did you contact the author or discover the solution yourself?

Quote

Praxis

. . . Did you contact the author or discover the solution yourself?

Just nosing around in the browser's "Edit Pragmas" tab. No idea what a "pragma" was but there seemed to be a number of settings there to play with. Imagine my surprise to see it, more or less, staring me in the face. A quick attempt to add a record both before and after I had changed the option and Hey Presto!

Ho Hum

Regards,
Rod

Quote

Rod

No idea what a "pragma" was

Neither did I even though those setting looked like "Preferences" or "Settings", however it actually has a dictionary definition:

programming: (pragmatic information) A standardised form of comment which has meaning to the compiler or some other program. It may use a special syntax or a specific form within the normal comment syntax. A pragma usually conveys non-essential information, often intended to help the compiler to optimise the program or to generate formatted documentation.

The 'non-essential' tag seems at odds with what you discovered.

My hat's off to you for your perseverance in discovering both the solution and that the problem wasn't the fault of NetWorx.

I'll keep my copy of the program even though I've only needed to modify the networx.db twice in many years of using NetWorx.

J

Quote

Praxis

My hat's off to you for your perseverance in discovering both the solution and that the problem wasn't the fault of NetWorx.

Not so much a solution, I think, but more a workaround.

Aw gee, I disagree.
I think you did find a solution, but I also think you found a 'bug' in the author's implementation/thought processes.

Currently, when the [New Record] button is pressed, the program populates the input fields (incorrectly) and then throws an error without any option to "Ignore Check Constraints", and without providing a Help 'tip' on how to edit the Pragmas to ignore the constraints.

Instead,
the author should present the input fields without 'guessing' the values, ever.
Once the first addition is successfully added, those values can/should be left 'as is' for subsequent records (but with at least one field changed by the user depending on constraints).
The error pop up should only throw an error when the values entered will create a duplicate key or if the check constraints are violated. It should also clearly explain why the record will not be added, i.e. a duplicate key or a constraint such as HOUR > 23 and leave the input fields to allow a correction or to exit the [New Record] mode/function/method.

And,
he should not provide a Pragma to Ignore Check Constraints.
The Constraints are there for a good reason and should never be violated since the database may be used by a different program which depends on the constraints.

Lastly, he should allow the user to enter data into the fields and press [Enter] to add that record and get ready to enter the next record or press a new button, [Exit New Record Mode] to exit the "New Record" mode.

J

I still think it's more of a workaround than it is a solution. The problem still exists but can be circumvented by ticking an option.

The responsibility for the problem is, for me, difficult to assess because I really don't know enough about databases and their programs/browsers to give any authoritative opinion.

For me, the objective was to be able to modify a back-up which I could then restore into Networx and I can now do that. But the original problem still exists even though I have found a way round it.