nächstes »

[ShadowDrakken]

It appears that Auto-increment just increments based on the last line of the data file, it should keep a continuous increment instead.

I recommend altering it to use the default value field as a way of tracking the last value used, that way you don't end up with duplicate entries, or other anomolies caused when INC fields are removed or reordered.

For instance, if the default value is 0 (no records have been created yet) then it knows the next record is a value of 1... if the default is 26, then it knows the next record to create is 27, etc...

[SuperMario]

Hi,

You are right, the Txt-Db-API takes the number of the last line and adds 1. Your solution would be good, but then we are not compatible to older versions anymore. Users with existing tables which want to use the new version of Txt-Db-API with your AUTO_INC solution, have to adjust there existing tables and set the dafault value of inc column to the max number.

I think the better solution would be to simply find out the maximum number in the inc column (with max() or something else) and take this number + 1 for the next auto-increment number. I will look into this problem and try to implement a solution.

Best Regards,
Mario

[Laubi]

Why don't you add a check to make sure there is a "control file". Something like auto update.. If there is no control file execute a max command and write the highest value to the control file..?

Executing a max on each insert would be quite slow...

[SuperMario]

@Laubi:
We want to keep the structure simple. A Controlfile would mean an additional file for each table. Also if we have one file with all auto_increment values for each table, then this would mean we have to parse this file every time a INSERT will be done. And I don't think that opening and reading a file (with all file locking stuff, because of multiuser safety) and parsing the content, will be faster then doing max() on e.g. 10000 numbers. I will do benchmark tests. This max finding will also only needed if there is an INSERT into the table and this is not so often as a SELECT.

Regards,
Mario

[Laubi]

Well that could be! I look forward to see the benchmark results as I'm very interested in that.. Global application variables would be nice  :wink:

[ShadowDrakken]

Hi,

You are right, the Txt-Db-API takes the number of the last line and adds 1. Your solution would be good, but then we are not compatible to older versions anymore. Users with existing tables which want to use the new version of Txt-Db-API with your AUTO_INC solution, have to adjust there existing tables and set the dafault value of inc column to the max number.

I think the better solution would be to simply find out the maximum number in the inc column (with max() or something else) and take this number + 1 for the next auto-increment number. I will look into this problem and try to implement a solution.

Best Regards,
Mario

Actually, it could maintain backwards compatibility rather simply. If the default is still 0, and there's at least 1 row, then it would know "hey, this is an older version file" and do a quick scan of the file to find the last value used and update the default field to be current. That way it maintains backwards compatibility, only has to search the file if there's a default of 0 while having data, and fixes the problem correctly.

Scanning the file every insert for the existing maximum means that if you insert a 100, then immediately delete it, the next insert will still be 100... this is improper behavior, especially when you're talking about catalogs and inventory numbers.

[Laubi]

@ShadowDrakken: Are you building a complete product data managment system on top of text db api or something similar? But of course you're right in such a situation this behaviour is bad.

[ShadowDrakken]

Nah, just have a small business that is refusing to use SQL but wants dynamic content. The website I've built for them only has about 45 rows, so it's not something huge. But still, if you're going to make something like this, best to get it as close as possible

I've considered going in and writing a fix to the problem myself, as I described, but then I have to fix it again every time a new version comes out

[Laubi]

Okay I got the point.. I was asking because pdm is my business and of course I'm not using text db api as we have a little bit more than 45 rows. Actually we have more than a million articles :?  Unfortunatly I have to wait until the computers are much faster when I want to use text db api :roll:

[SuperMario]

The idea with the usage of the default value row as storage for the next auto-inc number is good. BUT while implementing this, I faced a big problem. The number of the default value in the inc column can not be updated after the INSERT is made. c-worker implements a really good performance improvement for the INSERT Query. Means, if a INSERT is executes only the last line will be appended to the file instead of reading the complete file and writing it back to the disk. So, it's not possible to update the default value with the last insert id in this case.
I don't want remove this performance improvement. Here a benchmark:
Table file is 2 MB, 30000 rows:
INSERT of 1 row without improvement (reading/writing complete table) -> 4.3 secs
INSERT of 1 row with improvement (only appending the new line) -> 0.03 secs

So, perhaps the solution with the extra file for all auto-increment values would be the only solution. But then I have to think about the stuff with the file locking, because I don't want to destroy the current stable file locking concept in Txt-Db-API. Because Txt-Db-API is multiuser-safe. I analyzed a lot of text database classes in php, but no one is multiuser safe.

Perhaps I will talk to c-worker and probably he has another idea with this auto-increment improvement.

Regards,
Mario

[ShadowDrakken]

Woops, sorry that was me. realized I wasn't logged in when I tried to make an edit.

Even though the streams would be lost copying the file to an incompatible file system, the engine could rebuild the data on an as-needed basis since the table header would still be in the data file. This should keep things neat and tidy, while allowing additional power without using extra files.

Yeah it's a bit of a pain when those streams get lost, taking a few extra seconds to "fix" the file, but at least it's an option.

[Laubi]

I'm not sure but it is possible that just php 5 supports streams? If so what about users with php 4? They would need another solution...

[SuperMario]

I already have a solution for this auto-increment problem. This solution is also downward compatible, so users can easily switch from old version to the new version. I had to add a new file in each database directory named ".autoinc" which contains all current auto-inc values for of the tables. There is almost no performance lost, only the first time when the auto-inc must be find out of the table. (2 seconds for 30000 rows file).
And in my tests there is no multi-user problem, it's thread-safe.
Unfortunately, I had a harddisk crash, but I could restore 80 percent of the disk. I built up my development environment again and then I could post here the code with the solution, so you can test it.

Best Regards,
Mario

[ShadowDrakken]

cool, I look forward to giving it a test run

[SuperMario]

I uploaded the two changed files (resultset.php und database.php) in a zip to:
http://www.soulspring.de/temp/autoinc_fix_beta.zip

Please backup the original files and replace them with the new one.
This autoinc hotfix is only a beta version. No warranty for your data !

The auto-increment should now work for all INSERT Statements. Try it.
I just noticed, that I also have to implement it in CREATE TABLE statement. I will add this soon, so stay tuned.

Best Regards,
Mario