We’re creating tables programatically and I found a need to have a GUID. I noticed that the DataType enumeration doesn’t have an entry for GUID, the closest thing seems to be String. Is there a reason for leaving it out?
Historically, com.inductiveautomation.ignition.common.sqltags.model.types.DataType is supposed to model the datatype of a SQLTag, the source of which was an OPC-DA connection of some sort. OPC-DA didn’t have GUID datatype.
OPC-UA introduces GUID datatypes, as well as a bunch of others, none of which have been compelling enough to introduce as a datatype directly supported by a SQLTag.
Maybe Carl or Colby can step in and correct me or add more though. I may be getting some of this wrong. 
Yeah, I figured based off seeing sqltags in the package that the enumeration values weren’t driven by database data types.
So, I guess what I’m asking for is a feature request. Right now, we’re creating database tables using DBTableSchema.addRequiredColumn, passing in a DataType to define the database table column’s data type. I’ve come across three scenarios where I couldn’t get exactly what I wanted, (I’m using Microsoft SQL Server BTW) …
[ul]
-
I have Boolean objects whose values I wish to store and was hoping to use SQL’s BIT datatype. DataType doesn’t have BIT listed, so I resorted to using DataType.Int1.
-
I have UUID objects whose values I wish to store and was hoping to use SQL’s GUID datatype. DataType doesn’t have GUID listed, haven’t worked around this one yet, (rather not resort to VARCHAR).
-
I have some foreign keys I’d like to have established, don’t see any hooks to implement them programatically.
[/ul]
So, two requests, one being a request to beef up the DataType enumeration a bit and another being the ability to generate foreign keys programatically. Do these sound like realistic, feasible requests? If there’s a different way to approach what I’m asking (that doesn’t involve me managing Strings of CREATE queries), I’m all ears. I’m a big fan of being able to create these tables programatically, makes the deployment of my module much, much smoother.
Thanks!
Hmm, sounds like you need to just create the tables yourself.
Ack! Ok... unfortunately, though, what you're really asking is for us to take a system that was built more or less for a specific purpose (sqltags), and turn it in to a full feature ORM system. Not that there's anything wrong with that, we are trying to build a platform that enables rapid development, after all, but it just might not happen right away.
The system, as it is, is pretty simple because we've found it very hard to make sure everything works across all DBs. This, unfortunately, is a very limiting constraint we have to work with. For example, the GUID type you mentioned doesn't exist in java.sql.Types. Various databases may have some version of it, but it's going to be near impossible to abstract out to the point we need. There are various other problems we've run into while trying to support multiple databases (like jdbc drivers implementing their own versions of data types, which is horrible, because we can't easily coerce, or serialize them to the client).
Anyhow, there may be a way to handle many of these issues so the module author doesn't have to, but for right now, if you really need to do this kind of stuff, you'll probably have to build your own abstraction layer.
Regards,
I figured I’d get a response like that … can’t blame a guy for asking! I’ll explore other options, see where they get me. I appreciate the quick, honest response!
Along those lines - has anyone used Hibernate or a similar ORM within Ignition? I’m a bit hesistant to head that route due to the ClassLoader issues we’ve already seen.