Page 1 of 1

New checkbox on field + new option in default >>Auto

Posted: 2018-06-29 10:19
by sjohn
To be able to make a composite key in Appgini, it would be nice to have an option to simulate this, even if it would create redundant data.

See this post : https://forums.appgini.com/phpbb/viewto ... f=2&t=2738

If you on a field had a checkmark : Part of composite key

Then you could check the fields you wanted to be part of the composite key.

You have a table with the following fields :
User
year
Month
Transaction text.

These fields are "normal" fields, but the User, year and Month are checked for "Part of composite key".

Then you create a new field ID
This field you specify as Primary key, and in default >> auto there is a new option: marked for composite key.

This should do, that the value put in the primary key, is the valye of the fields : user + year + month.

This way you have only one primary key for a table, and this key is automatic composed as a composite key, from other fields/ "keys".

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-04 06:12
by a.gneady
Thanks for the suggestion :)
We'll consider implementing it in future releases in as simple manner as possible.

Issues to consider:
* If one or more fields are empty
* Source fields must be unique? Or alternatively, a counter is to be added to the PK?
* What if one or more source fields change value later?

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-04 09:03
by sjohn
Hello Ahmad

* If one or more fields are empty - then the user has misunderstood the meaning. He should have the fields included, set as required. But the empty value should be allowed - there will only not be many combinations available :-)
* Source fields should not be unique. What should be unique is the combination of the source-fields.
* If one or more source-fields change value, then the key should also change value. If the key constructed then, is not unique, then the database should report the violation.
* If a user tries to change the fields checked for combined key or changes the order of the fields, then the user should be told that this is not adviseable, or it should not be allowed.

Maybe it should be possible to have a field ( other than the primary key ) to hold the combined-"key". Then this field could be set as unique and then one of the purposes with the combined-key is achieved.

This way you would maintain the structure with the present primary key - it should then still be an incremental integer - the extra field with the combined-key can be set as unique, and you can still have the combined-key to make joins.
I think this will be the simplest way. And still useful even if it is then not the primary key, and also not a real combined-key.
It will then be a surrogate combined-key used for checking uniqueness across the selected columns, and it will allow more efficient joins ( only one field and not all the selected fields ) and optimize eventual lookups, using this surrogate combined-key.

If you consider this is not a common thing, and that it is not worth the effort, then no bad feelings from me.

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-04 14:48
by grimblefritz
For multi-field keys to work properly, the individual components should be padded to a fixed length and then concatenated.

The issue of text case also has to be addressed. Is the key built using field values as is, converted to upper case, etc?

And what of date values - are they used in text representation and if so what format, or are they numeric, etc?

Are some field types - text, for example - excluded?

There is no mention of setting the order of the fields, which is essential. The key User+LastName+FirstName is a very different from LastName+User+Firstname.

I think some performance testing and practical analysis are in order. Sad to say, but for table access of up to 100K records, I can generally equal or better the performance of mysql using a text file and grep. So, I use that fact to my advantage in several apps. It's not "proper", but it works better/faster.

Similarly, mysql will find the desired records using an appropriate select, especially if the component fields are indexed, but perhaps very slightly slower than a multi-field key. In a massive database, this incremental performance might be worth it. For the typical AppGini app? My guess is, it's not.

The value of adding this as a feature in AppGini must be balanced against the complexity it will add. Is a feature with limited application and benefit - remember, AppGini is targeted at a non-developer, non-DBA user, who probably doesn't understand much more than the basics - really the best use of AppGini development time?

I have no say, of course, but if I was the manager of this project I would direct Ahmad to focus on more critical features. Such as continued report generation development or support for views. At most, I'd have a Udemy course added showing how to build, index, and use computed columns - a poor man's substitute for multi-field keys.

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-05 08:18
by sjohn
My intention was to have the order of the combined fields, be the order of the fields in table. This is why I suggest the warning if a user changes the order of these fields in table.

About the format - the format was intended to be exactly the format for the values stored in the "original" fields. If for example the value of the first field is "Fritz" and the value in the second field is "457", then the value in the "combined-key" should be "Fritz457"
If the value in the first field is "15-09-1987" and the value in the second field is "Elmer", then the value in the "combined-key" will be "15-09-1987Elmer".
The values in the "combined-key" should be the "stringed" values from the "original" fields.
If one of the "original" fields is changed, then also the "combined-key" is changed according to the new value.

I don't think it should be fixed-length values, that should be stored.

I have come to the conclusion that, as we cannot have "real" "combined-keys" then it will be mostly for being able to specify which combinations you will only allow to be unique.

I happen to agree with GrumbleFritz, that maybe an instruction video could show how to make this as a hook. Could be a video that showed how to construct an extra field in the order_details in Northwind, where OrderID and ProductID was used for the new "combined-key" field, to make a check that a product is only used once on an order. Maybe not the most relevant example but an example to show how to construct a combined-key even if the new field is not a/the primary key.
Could be enhanced with another example that showed also constructing an extra field that was the result of manipulating/computing the values from one or two extra fields.

And I also agree on giving this a lower priority, as the reports and especially the possibility for ownership via parent-table. The ownership is of much more common interest I think.

So - sorry for the inconvience I have caused - even if I still think a "composite-key" could be useful - but maybe not worth the effort.

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-05 14:27
by grimblefritz
Have you taken the Udemy course? If not, you should. It's well worth the investment.

The course covers very nicely how to do everything needed in order to create pseudo-keys of the type you're wanting. No, there's not a specific topic on building such a key, but all the techniques needed to do so are taught.

As I've stated many times in other threads:

To do more with AppGini - something other than build a very good but simple CRUD solution - the Udemy course is an essential investment.

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-05 17:43
by sjohn
Hello Grumblefritz

I have invested in the Udemy course.
But suggesting new features has nothing with the Udemy course to do.
I will by time probably suggest other new features. And I will do this even if I have an alternative solution for the item already.

I see the "Feature suggestion" as a place to post ideas, you yourself find would be valuable also for other people if made.
If you have a problem you don't have a solution for, there is the "Getting started" where you could post your "problem" and then hopefully someone will describe a solution.
When I post here in "Feature suggestion" it is not because I want help - it is because I see a scenario that can not be solved in the existing program, and suggest a feature that would be of general interest. As I said before, I know it is up to Ahmad to decide if it is of common interest and if the work to put in it, will be worth the outcome.
I think your last post would have been more appropiate on this post : https://forums.appgini.com/phpbb/viewto ... f=2&t=2738
And if you have an alternative solution I would also like to see it desribed in detail on the same post.

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-07-06 02:01
by grimblefritz
I did offer a solution in the thread you mention.

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-08-27 11:43
by jsetzer
Please, don't get me wrong: I just would like to point out that there can be more than one unique constraint in a table.

Example
  • id
  • parent_id
  • name
  • position
  • ...
In this table I need two unique constraints:
  1. parent_id + name
  2. parent_id + position

So from my point of view it would not be enough to have a checkbox per field to include fields in the (=one) unique constraint.

Apart from that, I would be very glad and happy if AppGini offered to create unique constraints on tables.


See also:
Unique Constraints (on multiple columns)
https://forums.appgini.com/phpbb/viewto ... f=2&t=2759


Kind Regards,
Jan

Re: New checkbox on field + new option in default >>Auto

Posted: 2018-08-28 15:04
by a.gneady
I'd like to thank everyone who contributed to this insightful discussion and analysis :)

As the discussion unfolded, it became clear that there are many issues to consider and if users are given the choice to configure combined keys from the AppGini interface, it won't be very intuitive for most users ... I guess I'd better explain it as a screencast. Meanwhile, support for combined unique keys is certainly a very useful feature and would be easy to deal with for most users, so I'll vote for this one :)