Hi

I have a task to find out, how we can add a new datatype in ElevateDB. We have done it with DBISAM, and the company have decided to upgrade to ElevateDb now. When I look in the source code, and inside the tabledlg form, I can see, that elevatedb make a select on the table datatypes. If I want to add a new type to this table structure, how can I do this. And were is the table comes from. I can not find it in the database, wonder if it is inside the configuration file.

Best Regards


Peter Beyer
Admind

PeterBeyer

>I have a task to find out, how we can add a new datatype in ElevateDB. We have done it with DBISAM, and the company have decided to upgrade to ElevateDb now. When I look in the source code, and inside the tabledlg form, I can see, that elevatedb make a select on the table datatypes. If I want to add a new type to this table structure, how can I do this. And were is the table comes from. I can not find it in the database, wonder if it is inside the configuration file.

If I'm understanding correctly I think you're best taking this up with Tim directly. I, and I doubt any other of the users on these newsgroups, has a clear understanding of the full implications of adding a new datatype. I'd be fascinated as to what datatype you want to add.

If, on the other hand, you're simply talking about altering the table structure you can do so either using EDBManager's Alter Table dialog, or directly through SQL using the ALTER TABLE command.

Roy Lambert

Hi Roy,

Yes, I think you are right about talking with Tim directly. I just want to go through the normal channel first.
No, it is not just table structure. We use a Alpha numeric datatype, and it should be sorted correctly. Meaning, when we use numeric numbers it should be sortet like that, and when we use alpha characters, that should also be sortet. And all will be in the same datatype. (we have made the implementation in DBISAM, so my job is to port this to elevatedb)

Best Regards


Peter

Roy Lambert wrote:

PeterBeyer

>I have a task to find out, how we can add a new datatype in ElevateDB. We have done it with DBISAM, and the company have decided to upgrade to ElevateDb now. When I look in the source code, and inside the tabledlg form, I can see, that elevatedb make a select on the table datatypes. If I want to add a new type to this table structure, how can I do this. And were is the table comes from. I can not find it in the database, wonder if it is inside the configuration file.

If I'm understanding correctly I think you're best taking this up with Tim directly. I, and I doubt any other of the users on these newsgroups, has a clear understanding of the full implications of adding a new datatype. I'd be fascinated as to what datatype you want to add.

If, on the other hand, you're simply talking about altering the table structure you can do so either using EDBManager's Alter Table dialog, or directly through SQL using the ALTER TABLE command.

Roy Lambert

And again, guess that you are right, that we do not need to make it as a new datatype. I need to dig more into it, so I can ask the right questions. sorry.

PeterBeyer


With that extra bit of information I think I can at least point you in the right direction. I assume you're talking about having fields such as "A1" "B5" "78" "B02" and them all sorting in a natural order.

If so I'd probably add an extra column (VARCHAR or CHAR) onto the table and use a trigger to format the data in the real column so it was like

"    A00001"
"    B00005"
"      00078"
"    B00002"
"XYZ00000"

You might be able to get away with a computed column (think like calculated fields in Delphi) and if you can give me the spec I'll have a look to see if I can come up with something.


Roy Lambert

Hi Roy,

Thanks for your support I will try to explain the specifications. I will give you some general info about what the field is for, hopefully with your knowledge from elevatedb, you can guide us to build the right solution.

Admind is a POS system, that is very generic, you can design your own needs as a customer. We have partners that design all kind of solutions, it do not have to be a POS solutions, it just use the same basic core, with very little coding. DBISAM is one of the basic components that makes Admind very unique and powerful. The reason why we need to change from DBISAM to elevatedb is because elevatedb have support for OS X in the future. We have being very satified with the DBISAM solution so far.

In Admind we have a special field, we use for different use. It can be text for a combobox, or it can be a number ids for invoices. Actuallys, In Admind it is just a special field that is available to pick, when we design a solutions for a customer. We have more than 1500 solutions running, and many of them use this (lets call it) field type, in the solutions. It is very useful.

The field is sorting values and character letters, lets says we have a list like this.

1
11
2
DKK
EURO
NOK
USD

As you can see, the numbers is not sorted correct, because the field is a string field. With the Admind field type, it will be sorted this way

1
2
11
DKK
EURO
NOK
USD

That makes this fieldtype very powerful, because you can use it for many things (and we do that).

This long introductions was to give you a better understanding, that the fieldtype is used everywere in our system, and should be selected as a "Datatype" in Adminds designer. I hope of couse, that you will say. Ahaa, why you not just use computed columns or something like that - so we do not need to support this fieldtype, with a modified elevatedb solutions. I did not find so much documentation for the computed columns, I read somewhere that it is readonly, and that is not good enough for us. And if we are going to define the field everytime the user wants to use it, it is not good enough eather.

Now back to the implementation.

In our existing solution, I can see, that the basic funtions is made in dbisamlb.pas, in the procedure EvaluateBinary. The first line is a procedure we have made, that test if parameter Token2.DataType = ZString and Token2.SubType = SUBTYPE_FIXED. After that it test to see if Token1.DataSize < 11. If this is correct, we call a routine that transform the character data to something that can be sorted correctly in a index.

It makes sence - because the special Admind field have a charistica of size 10 and is a fixed char.

Unless that you say, why you not do it like this in Elevatedb, will Next step be to find out in Elevatedb, were to implement this feature.

Right now, my best guess will be to copy the funtionality into edbcommon (edbrun packages). In the procedure ReadFixedAnsiString.  I have not tried anything yet, only investigation, and hope that elevatedb have a better solution for making this

Thanks in advance


Peter Beyer
Admind

Hi Peter

I guess I don't understand the problem because I can't see why an
ordinary string field can't be used.

Create a new field (say) 10 characters long.

In a BeforePost event (or TRIGGER?) populate the field according to your
required sequence.  From your example, I assume that this would entail a
check to see if the string converts to an integer and if so pad it out
with leading zeroes to make it the correct field length.  Non integers
would be left as they are.

The new field would be used in indices and ORDER BY's but never seen by
the user

So:-

> 1
> 2
> 11
> DKK
> EURO
> NOK
> USD

becomes:-

0000000001
0000000002
0000000011
DKK
EURO
NOK
USD

Cheers

Jeff
--

Hi Jeff,

You are right that this is the solution. If we use a write trigger this is possible to do. You can do that, when you know which fields are going to use this sorting order. Our problem is, that our customers can create there own fields in the application. We don't know about the table they use, and the fields they have created, because it is possible for them to create these things on the fly.

The user have some kind of a IDE palette menu,  where they can choose this "datatype", as a fieldtype to a new field in an existing or may be new table. So they just add them, when they are going to use them.

It will be to technical for the user, to make database triggers by them self.


Cheers



Peter

PeterBeyer

>You are right that this is the solution. If we use a write trigger this is possible to do. You can do that, when you know which fields are going to use this sorting order. Our problem is, that our customers can create there own fields in the application. We don't know about the table they use, and the fields they have created, because it is possible for them to create these things on the fly.
>
>The user have some kind of a IDE palette menu, where they can choose this "datatype", as a fieldtype to a new field in an existing or may be new table. So they just add them, when they are going to use them.
>
>It will be to technical for the user, to make database triggers by them self.

Well it might be if you expected them to type the code in, but since you're providing a front end for them rather than expecting them to use EDBManager its down to your programming skills <vbg>


Roy Lambert

PeterBeyer


>The field is sorting values and character letters, lets says we have a list like this.
>
>1
>11
>2
>DKK
>EURO
>NOK
>USD
>
>As you can see, the numbers is not sorted correct, because the field is a string field. With the Admind field type, it will be sorted this way

If this is used for sorting, and the sorting is going to be carried out in SQL then you don't need anything other than a mod in the SQL. With the simple requirements you've shown so far this

SELECT                   
Original,
IF( (Original IS NOT NULL) AND (SUBSTR(Original,1,1) IN ('0','1','2','3','4','5','6','7','8','9')),
SUBSTR('0000000000',1,10-LENGTH(Original))+Original
,Original) AS Modified
FROM    
Special
ORDER BY Modified

will do the job.


It could be written into a computed column


ALTER TABLE "Special"
ADD COLUMN "Computed" VARCHAR(10) COLLATE "ANSI_CI" COMPUTED ALWAYS AS IF( (Original IS NOT NULL) AND (SUBSTR(Original,1,1) IN ('0','1','2','3','4','5','6','7','8','9')),
SUBSTR('0000000000',1,10-LENGTH(Original))+Original
,Original)

and then used everywhere and anywhere. Its still read only, and editing would be to the real field - Original in this case.

Straight SQL can cope with more complex formats but at some point you hit a situation where you will need to move to a trigger.

Roy Lambert