Has anyone made an ORM work with Ignition transaction groups?

I work with a few Ignition projects that are strictly front ends for databases, no PLC’s involved at all.

I recently just wanted to see how things are done in the pure python world and in sqlalchemy you can do something like

user = Table('user', metadata_obj,
    Column('user_id', Integer, primary_key=True),
    Column('user_name', String(16), nullable=False),
    Column('email_address', String(60), key='email'),
    Column('nickname', String(50), nullable=False)
)

Or even more nicely in django

class Person(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)

So I am curious if anyone has made an ORM work within Ignition? I assume the fact we would have to call Ignitions built-in system.db.* functions to start/close a transaction and to run queries makes it a bit more difficult and complicated. I’ve searched the marketplace and I don’t see anything there either (at least when I searched for ORM).

Is something like this feasible within Ignition? If it required a module, what java library would be helpful here to expose to Ignition to make it work?

1 Like

I vaguely recall there being a JDBC layer for SQLAlchemy. Not sure if it still exists, and can be made to play nice with Ignition’s poolable connections.

1 Like

You might find DBTableSchema interesting.
That might be fun to add to ignition-extensions…

3 Likes

This is my official plea to you!

What I am looking for would be an easy way to insert/update objects within a single transaction and being able to look at my code and seeing the datamodel as in my OP. Whether this requires manually making the named queries or, in my mind the preferred way, via a gateway message handler script that parses and does things automatically via prepUpdate so you don’t have to make and update named queries for every database column change - only change the database and then your datamodel.

Generating SQL change scripts are nice to have but really much lower on the list for me personally.

That’s just my general thoughts on it. This would really allow me to completely revamp my project into much more readable code.

I've long been using a "SmartMap" extended dictionary that naturally allows property access to dictionary content. Create one with a tuple sequence of colName/colValue pairs and you suddenly have a very ORM-like row object. The one I like most delivers a None for missing keys automatically:

#----------
# Handy tool for objects as "bags of properties"
# by Peter Norvig, enhanced as a subclass of dict() by Philip J. Turmel
# http://norvig.com/python-iaq.html
class SmartMap(dict):
	def __repr__(self):
		args = []
		for (k,v) in self.items():
			if isinstance(v, basestring):
				args.append('%s=%s' % (k, v))
			else:
				try:
					z = len(v)
					args.append('%s=%s' % (k, type(v)))
				except:
					args.append('%s=%s' % (k, repr(v)))
		return 'SmartMap(%s)' % ', '.join(args)
	def __str__(self):
		args = []
		for k,v in self.items():
			if isinstance(v, basestring):
				args.append('%s=%s' % (k, v))
			else:
				try:
					z = len(v)
					args.append('%s=%s' % (k, repr(v)))
				except:
					args.append('%s=%s' % (k, str(v)))
		return 'SmartMap(%s)' % ', '.join(args)
	def clone(self):
		return type(self)(**self)
	def __getattr__(self, attr):
		if attr.startswith('__'):
			raise AttributeError(attr)
		if attr in self:
			return self[attr]
		return None
	def __setattr__(self, attr, val):
		if attr.startswith('__'):
			super.__setattr__(self, attr, val)
		self[attr] = val

If you have a dataset ds and rowNum, you can load that row into this model like so:

row = util.SmartMap(zip(ds.columnNames, [ds.getValueAt(rowNum, i) for i in range(ds.columnCount)]))

If you consistently use column names that are valid python identifiers (and you do, right?), everything in the row is now accessible as either row.colName or row["colName"], as needed.

I'd be delighted if the future PyDataset Jython proxy supported this syntax in its row iterator...

4 Likes

I see the value in the SmartMap but the issue I am running into is I want to define my database tables as a class with direct attributes but they should also be like keys of a dictionary (for SQL generation purposes otherwise I need to run through my classes non dunder properties and make one). I intially tried it SmartMap, got confused, started from scratch, and now feel like I do need smart map again but am failing to get how to implement it.

Let me show the code I currently have and perhaps someone here can help me get over this hump. The end goal here is that eventually all the INSERT/UPDATE etc sql will be done in the gateway via message handling. The additional complicating factor here that is not required yet is that occasionally I will need to also use transactions.

Anyways my current code:

#db.model
from db.columnTypes import abstractColumn

class Model(object):
	"""
	Each table that exists should subclass this.  Cna use dev.dbToScript to help aid in writing the code.
	"""
	
	def __init__(self):
		# check initial properties. for some reason abstract property was not doing the job here
		try:
			self.__tableName__
		except AttributeError:
			raise NotImplementedError("Model needs a tablename")

	def __setattr__(self, attr, val):
		col = getattr(self, attr)
		if attr.startswith("__"):
			raise ValueError("Cannot dynamically modify table properties.  You must change %s in the class definition"%(attr))
		elif issubclass(type(col), abstractColumn):
			print 'trying to set column value'
			col.setValue(val)

	def loadDict(self, d):
		# loads a dictionary of values to a model
		for k, v in d.items():
			setattr(self, k, v)

	# after this the subclassed models should just have a bunch of properties that are column types a la Django's ORM

Abstract Column and types

#db.columnTypes
from abc import ABCMeta, abstractmethod, abstractproperty
import java.lang.String
import java.lang.Boolean
import java.lang.Float

# base abstraction of class
class abstractColumn(object):
	__metaclass__ = ABCMeta
	value = None
	
	def __init__(self):
		# check initial properties. for some reason abstract property was not doing the job here
		try:
			self.__typeCheck__
		except AttributeError:
			raise NotImplementedError("Column needs a typeCheck list")

	def __init__(self, nullable=True, default=None, validators=None):
		self.nullable = nullable
		self.default = None
		# validators should be a list of functions and hardcoded args I think - think on this
		self.validators = validators
	
	def typeCheck(self, val):
		for t in self.__typeCheck__:
			if issubclass(type(val), t):
				# some acceptable class was found
				return
		# Value did not match any acceptable class
		raise ValueError("Cannot assign value %s of type %s to column with one of these types %s"%(str(val),str(type(val)),str(self.__typeCheck__)))

	def validatorCheck(self, val):
		if self.validators is None:
			return
		for validator in validators:
			validator(val)
	
	def setValue(self, val):
		self.typeCheck(val)
		self.validatorCheck(val)
		self.value = val
	
	def __repr__(self):
		return  str(self.value)
	
	def __str__(self):
		return "%s with current value %s"%(self.__class__.__name__, str(self.value))

class IntegerField(abstractColumn):
	__typeCheck__=[int]

class CharField(abstractColumn):
	__typeCheck__=[str, java.lang.String]
	
	def __init__(self, maxLength=None, nullable=True, default=None):
		abstractColumn.__init__(self, nullable=nullable, default=default)
		if maxLength is None or maxLength < 0:
			raise ValueError("maxLength of a CharField cannot be None or Negative")

class BooleanField(abstractColumn):
	__typeCheck__=[bool, java.lang.Boolean]

class TextField(abstractColumn):
	__typeCheck__=[str, java.lang.String]

class DecimalField(abstractColumn):
	__typeCheck__=[float, java.lang.Float, int]

And some testing code -

class Customer(db.model.Model):
	__tableName__='listcustomers2'

	idx = db.columnTypes.IntegerField(nullable=False)
	name = db.columnTypes.CharField(nullable=False, maxLength=255)
	description = db.columnTypes.CharField(maxLength=255)
	active = db.columnTypes.BooleanField(nullable=False, default=True)
	companyTypeId = db.columnTypes.IntegerField(nullable=False)
	viewable = db.columnTypes.BooleanField(nullable=False, default=True)
	discountId = db.columnTypes.IntegerField()
	defaultProjectManager = db.columnTypes.IntegerField(default=-1)
	customerPath = db.columnTypes.TextField()
	creditHold = db.columnTypes.BooleanField(default=False)
	creditLimit = db.columnTypes.DecimalField(default=0.00)
	calc_unpaidInvoiceTotal = db.columnTypes.DecimalField()
	calc_activeInvoiceFlags = db.columnTypes.IntegerField()
	calc_paidOnUnpaidInvoice = db.columnTypes.DecimalField()

# this represents how I would get the data off of a vision window or perspective view
values = {'name':'TestCustomer123', "description":"Test Cust Desc", "active":True, "companyTypeId":1,
"viewable":True,"discountId":5,"defaultProjectManager":1112, "customerPath":"blah//blah", "creditHold":False, "creditLimit":5000.0,
"calc_unpaidInvoiceTotal":0, "calc_activeInvoiceFlags":0, "calc_paidOnUnpaidInvoice":0}
c = Customer()

c.loadDict(values)

This all runs and I am able to examine things like c.name and see the value I put in which is great.

The hurdle now is what do I do with c.save() to save this as a new entry and also populate my id column with it. Is there a way to utilize smartMap here or am I going to have to write something that goes through my sublcasses db.model.Model, finds the non-id columns, writes the query and what not. I already have a function that does that with a dictionary it's just how do I do it now in this format.

Or maybe I'm completely wrong headed in my approach.

SmartMap does not provide true ORM functionality. It's just a convenience wrapper for rows. I've not found a need to adopt ORM in Ignition.

2 Likes

I'm not sure if this helps at all, but just in case...dbQuery

1 Like

@pturmel fair enough I think my application is a bit unique insofar as its strictly user inputed forms so its kind of begging for an ORM. It feels like Ignition has kind of been shoehorned to the project and not really the natural choice. Certainly works but for pure form based applications there's plenty of things who only do that and do it well.

@dkhayes117 Thanks I will take a look. I think I will have to stick with named queries at the moment. Which is ok tbh. The only way I could think around it is sending a list of instructions to the gateway ie insert this then this this all within the same transaction, and report results, but I feel like this would lead to my code being harder to follow and unreadable which is the opposite to what I am trying to do

@PGriffith not sure how but I think I messed stuff up with in the background? Can't run a insert named query at the moment and getting an odd error. Using 8.1.17. Here is the error I am getting

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Error executing function 'NamedQueryFunctions.executeNamedQuery': ImportError: No module named columnTypes


	at org.python.core.Py.JavaError(Py.java:547)

	at org.python.core.Py.JavaError(Py.java:538)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:192)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:541)

	at org.python.core.PyObject.__call__(PyObject.java:400)

	at org.python.pycode._pyx1231.insert$8(<module:db.model>:64)

	at org.python.pycode._pyx1231.call_function(<module:db.model>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyBaseCode.call(PyBaseCode.java:134)

	at org.python.core.PyFunction.__call__(PyFunction.java:416)

	at org.python.core.PyMethod.__call__(PyMethod.java:126)

	at org.python.pycode._pyx1230.f$0(<input>:27)

	at org.python.pycode._pyx1230.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:611)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Error executing function 'NamedQueryFunctions.executeNamedQuery': ImportError: No module named columnTypes

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:945)

	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:73)

	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:46)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:117)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	... 24 more

Caused by: ImportError: No module named columnTypes


	at org.python.core.Py.ImportError(Py.java:329)

	at org.python.core.imp.import_first(imp.java:1230)

	at org.python.core.imp.import_module_level(imp.java:1361)

	at org.python.core.imp.importName(imp.java:1504)

	at org.python.core.PyType$TypeResolver.readResolve(PyType.java:2674)

	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(null)

	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)

	at java.lang.reflect.Method.invoke(null)

	at java.io.ObjectStreamClass.invokeReadResolve(null)

	at java.io.ObjectInputStream.readOrdinaryObject(null)

	at java.io.ObjectInputStream.readObject0(null)

	at java.io.ObjectInputStream.defaultReadFields(null)

	at java.io.ObjectInputStream.readSerialData(null)

	at java.io.ObjectInputStream.readOrdinaryObject(null)

	at java.io.ObjectInputStream.readObject0(null)

	at java.io.ObjectInputStream.readObject(null)

	at java.io.ObjectInputStream.readObject(null)

	at java.util.HashMap.readObject(null)

	at jdk.internal.reflect.GeneratedMethodAccessor3977.invoke(null)

	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(null)

	at java.lang.reflect.Method.invoke(null)

	at java.io.ObjectStreamClass.invokeReadObject(null)

	at java.io.ObjectInputStream.readSerialData(null)

	at java.io.ObjectInputStream.readOrdinaryObject(null)

	at java.io.ObjectInputStream.readObject0(null)

	at java.io.ObjectInputStream.readObject(null)

	at java.io.ObjectInputStream.readObject(null)

	at com.inductiveautomation.ignition.common.Base64.decodeToObjectFragile(Base64.java:985)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.AbstractGatewayFunction.invoke(AbstractGatewayFunction.java:171)

	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:431)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)

	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1450)

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:799)

	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1631)

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:548)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:600)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1624)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1440)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:501)

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1594)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1355)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)

	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)

	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)

	at org.eclipse.jetty.server.Server.handle(Server.java:516)

	at org.eclipse.jetty.server.HttpChannel.lambda$handle$1(HttpChannel.java:487)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:732)

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:479)

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:277)

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:105)

	at org.eclipse.jetty.io.ChannelEndPoint$1.run(ChannelEndPoint.java:104)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:338)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:315)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:173)

	at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)

	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:409)

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:883)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1034)

	at java.lang.Thread.run(null)

Here is the code I end up running - line 62 is the system.db.runNamedQuery part, the print statement before looks perfectly reasonable

	def insert(self):
		insertNQ = 'forms/'+self.__class__.__name__+'/create'
		self.fillDefaultValuesAndCheckNullables()
		data = self.getAsDict()
		tx = system.db.beginNamedQueryTransaction()
		print insertNQ, data, tx
        # printed forms/Customer/create {'calc_activeInvoiceFlags': 0, 'defaultProjectManager': 1112, 'active': True, 'description': Test Cust Desc, 'customerPath': blah//blah, 'creditHold': False, 'viewable': True, 'name': TestCustomer123, 'creditLimit': 0.0, 'companyTypeId': 1, 'calc_unpaidInvoiceTotal': 0, 'discountId': 5, 'idx': None, 'calc_paidOnUnpaidInvoice': 0} 81c33ab9-33cb-4eb5-b1a0-48b520c813d1
		id = system.db.runNamedQuery(insertNQ, data, getKey=1, tx=tx)
		system.db.commitTransaction(tx)
		return id