spacepaste

Paste details

reply | raw

  1.  
  2. import sqlalchemy
  3. from sqlalchemy.ext.declarative import declarative_base
  4. from sqlalchemy.orm import scoped_session, sessionmaker
  6. Engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
  7. Session = scoped_session(sessionmaker(bind=Engine))
  8. Base = declarative_base()
  10. #sqlalchemy vanilla model
  11. from sqlalchemy import Column, Integer, String, Text, Date, TIMESTAMP
  13. from sqlalchemy.orm import relationship, synonym
  14. from sqlalchemy import Column, ForeignKey, Integer, String, Text, Date, DateTime, TIMESTAMP, Boolean
  17. #These models represent a Google account object
  18. class GoogleAccount_websites(Base):
  19. #SQLAlchemy Table Definition
  20. __tablename__ = 'googleAccountsWebsites'
  22. #website_id = Column('website_id', Integer, primary_key=True)
  23. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  24. type = Column('type', String, primary_key=True)
  25. value = Column('value', String)
  28. class GoogleAccount_emails(Base):
  29. #SQLAlchemy Table Definition
  30. __tablename__ = 'googleAccountsEmails'
  32. #email_id = Column('email_id', Integer, primary_key=True)
  33. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  34. address = Column('address', String, primary_key=True)
  35. primary = Column('primary', Boolean)
  38. class GoogleAccount_externalIds(Base):
  39. #SQLAlchemy Table Mapping
  40. __tablename__ = 'googleAccountsExternalIds'
  42. #external_id = Column('external_id', Integer, primary_key=True)
  43. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  44. customType = Column('customType', String, primary_key=True)
  45. type = Column('type', String)
  46. value = Column('value', String)
  49. class GoogleAccount_name(Base):
  50. #SQLAlchemy Table Mapping
  51. __tablename__ = 'googleAccountsNames'
  53. #name_id = Column('name_id', Integer, primary_key=True)
  54. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  55. familyName = Column('familyName', String)
  56. fullName = Column('fullName', String)
  57. givenName = Column('givenName', String)
  60. class GoogleAccount_phones(Base):
  61. #SQLAlchemy Table Mapping
  62. __tablename__ = 'googleAccountsPhones'
  64. #phone_id = Column('phone_id', Integer, primary_key=True)
  65. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  66. type = Column('type', String, primary_key=True)
  67. customType = Column('customType', String)
  68. value = Column('value', String)
  71. class GoogleAccount_aliases(Base):
  72. #SQLAlchemy Table Definition
  73. __tablename__ = 'googleAccountsAliases'
  75. #alias_id = Column('alias_id', Integer, primary_key=True)
  76. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  77. emailAddress = Column('emailAddress', String, primary_key=True)
  80. class GoogleAccount_nonEditableAliases(Base):
  81. #SQLAlchemy Table Definition
  82. __tablename__ = 'googleAccountsNoneditableAliases'
  84. #noneditable_alias_id = Column('noneditableAliasId', Integer, primary_key=True)
  85. googleId = Column('googleId', String, ForeignKey('googleAccounts.id'), primary_key=True)
  86. emailAddress = Column('emailAddress', String, primary_key=True)
  89. class GoogleAccountSAModel(Base):
  90. #SQLAlchemy Table Definition
  91. __tablename__ = 'googleAccounts'
  93. id = Column('id', String, primary_key=True, nullable=False)
  94. agreedToTerms = Column('agreedToTerms', Boolean)
  95. changePasswordAtNextLogin = Column('changePasswordAtNextLogin', Boolean)
  96. creationTime = Column('creationTime', String)
  97. customerId = Column('customerId', String)
  98. etag = Column('etag', String)
  99. includeInGlobalAddressList = Column('includeInGlobalAddressList', Boolean)
  100. ipWhitelisted = Column('ipWhitelisted', Boolean)
  101. isAdmin = Column('isAdmin', Boolean)
  102. isDelegatedAdmin = Column('isDelegatedAdmin', Boolean)
  103. isMailboxSetup = Column('isMailboxSetup', Boolean)
  104. kind = Column('kind', String)
  105. lastLoginTime = Column('lastLoginTime', String)
  106. orgUnitPath = Column('orgUnitPath', String)
  107. primaryEmail = Column('primaryEmail', String)
  108. suspended = Column('suspended', Boolean)
  109. thumbnailPhotoEtag = Column('thumbnailPhotoEtag', String)
  110. thumbnailPhotoUrl = Column('thumbnailPhotoUrl', String)
  112. websites = relationship('GoogleAccount_websites', collection_class=list,
  113. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  115. emails = relationship('GoogleAccount_emails', collection_class=list,
  116. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  118. externalIds = relationship('GoogleAccount_externalIds', collection_class=list,
  119. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  121. #Name is an Object, not a List type, it must have uselist=False
  122. name = relationship('GoogleAccount_name', uselist=False,
  123. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  125. phones = relationship('GoogleAccount_phones', collection_class=list,
  126. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  128. aliases = relationship('GoogleAccount_aliases', collection_class=list,
  129. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  131. nonEditableAliases = relationship('GoogleAccount_nonEditableAliases', collection_class=list,
  132. cascade='all, delete-orphan', passive_deletes=True, backref='GoogleAccountRecord_SA')
  134. Base.metadata.create_all(Engine)
  136. #Marshmallow-SQLAlchemy Schema - Builds Schema based on ORM Model
  137. from marshmallow_sqlalchemy import ModelSchema
  138. from marshmallow import fields
  140. class GoogleAccountSASchema(ModelSchema):
  141. aliases = fields.Method(deserialize='processAliases')
  142. nonEditableAliases = fields.Method(deserialize='processNonEditableAliases')
  144. class Meta:
  145. model = GoogleAccountSAModel
  147. def deserializeListType(self, value, saType):
  148. saListOut = []
  150. for item in value:
  151. newAlias = saType()
  152. newAlias.emailAddress = item
  153. saListOut.append(newAlias)
  155. return saListOut
  157. def processAliases(self, value):
  158. return self.deserializeListType(value, GoogleAccount_aliases)
  160. def processNonEditableAliases(self, value):
  161. return self.deserializeListType(value, GoogleAccount_nonEditableAliases)
  163. Base.metadata.create_all(Engine)
  165. #start of our test
  166. googleAccountJson = '''{
  167. "agreedToTerms": true,
  168. "aliases": ["jd@testd.com",
  169. "j.e.doe@testd.com",
  170. "zz.dd@testd.com",
  171. "ff.qwerty@testd.com"
  172. ],
  173. "changePasswordAtNextLogin": false,
  174. "creationTime": "",
  175. "customerId": "C02fmcj1i",
  176. "emails": [{
  177. "address": "john.doe@testd.com",
  178. "primary": true
  179. }],
  180. "etag": "XRsypGOPUmlmxokHB51cC07Vb3s/nnW4dSQR5PacZne6JsXkOrTfeBM",
  181. "externalIds": [{
  182. "customType": "GID",
  183. "type": "custom",
  184. "value": "336168"
  185. }],
  186. "id": "108488927407595610966",
  187. "includeInGlobalAddressList": true,
  188. "ipWhitelisted": false,
  189. "isAdmin": false,
  190. "isDelegatedAdmin": true,
  191. "isMailboxSetup": true,
  192. "kind": "admin#directory#user",
  193. "lastLoginTime": "",
  194. "name": {
  195. "familyName": "Doe",
  196. "fullName": "John E Doe",
  197. "givenName": "John E"
  198. },
  199. "nonEditableAliases": ["john.doe@webmail.testd.com",
  200. "doe@webmail.testd.com",
  201. "john.doe2@webmail.testd.com",
  202. "john_doe3@webmail.testd.com",
  203. "john.doe4@webmail.testd.com"
  204. ],
  205. "orgUnitPath": "/Ops",
  206. "phones": [{
  207. "type": "work",
  208. "value": "+1 839-439-7484"
  209. }, {
  210. "type": "mobile",
  211. "value": "+1 407-393-5048"
  212. }, {
  213. "customType": "Ztel",
  214. "type": "custom",
  215. "value": "4-439-7484"
  217. }],
  218. "primaryEmail": "john.e.doe@testd.com",
  219. "suspended": false,
  220. "thumbnailPhotoEtag": "XRGOPUm7Vb3s/mVS-OxFB0Dyxo8",
  221. "thumbnailPhotoUrl": "https://plus.google.com/_/focus/photos/public/AI0YzcyZjRhNWZjOWExNDkwARP7Z8ODD5GJ",
  222. "websites": [{
  223. "type": "profile",
  224. "value": "https://employees.testd.com/seto/336168"
  225. }]
  226. }'''
  228. from sqlalchemy.orm.attributes import get_history, instance_state
  231. def GetUncommittedChangesDict(saRow):
  232. #{'columnName' : {'newValue' : '2', 'oldValue' : '1'}
  233. #for key in z:
  235. changed = False
  236. changedDict = {}
  237. unchangedDict = {}
  238. outDict = {}
  240. for column in saRow._sa_instance_state.attrs:
  241. if (column.history):
  242. if (column.history.added) and (not column.history.deleted):
  243. changed = True
  244. changedDict[column.key] = {'newValue' : column.history.added, 'oldValue' : None}
  245. outDict[column.key] = {'newValue' : column.history.added, 'oldValue' : None}
  248. if (column.history.unchanged):
  249. unchangedDict[column.key] = {'newValue' : None, 'oldValue' : column.history.unchanged}
  250. outDict[column.key] = {'newValue' : None, 'oldValue' : column.history.unchanged}
  252. if (column.history.deleted):
  253. changed = True
  254. changedDict[column.key] = {'newValue' : column.history.added, 'oldValue' : None}
  255. outDict[column.key] = {'newValue' : column.history.added, 'oldValue' : column.history.deleted}
  256. return changed, changedDict, unchangedDict, outDict
  258. def hasChanged(saRow):
  259. changed, changedDict, unchangedDict, outDict = GetUncommittedChangesDict(saRow)
  261. print('Change detected: {}'.format(changedDict))
  263. return changed
  265. #First pass
  266. x = GoogleAccountSASchema(strict=True, session=Session).loads(googleAccountJson)
  267. firstRow = x.data
  269. print('1.1- Session.dirty: {}\n\n'.format(Session.dirty))
  270. print('1.1- hasChanged(firstRow): {}\n\n'.format(hasChanged(firstRow)))
  272. Session.add(firstRow)
  274. #Expected hasChanged == True
  275. print('1.2- Session.dirty: {}\n'.format(Session.dirty))
  276. print('1.2- hasChanged(firstRow): {}\n\n'.format(hasChanged(firstRow)))
  278. Session.commit()
  280. #Expected hasChanged == False
  281. print('1.3- Session.dirty: {}\n'.format(Session.dirty))
  282. print('1.3- hasChanged(firstRow): {}\n\n'.format(hasChanged(firstRow)))
  284. print()
  286. y = GoogleAccountSASchema(strict=True, session=Session).loads(googleAccountJson_CHANGED)
  287. secondRow = y.data
  289. print('2.1- Session.dirty: {}\n'.format(Session.dirty))
  290. print('2.1- hasChanged(secondRow): {}\n\n'.format(hasChanged(secondRow)))
  292. print(get_history(secondRow, 'websites'))
  294. #Session.merge(secondRow)
  296. #print('2.2- Session.dirty: {}\n'.format(Session.dirty))
  297. #print('2.2- hasChanged(firstRow): {}\n\n'.format(hasChanged(secondRow)))
  300. #z = GoogleAccountSASchema(strict=True, session=Session).loads(googleAccountJson)
  301. #thirdRow = z.data
  303. #print('3.1- Session.dirty: {}\n'.format(Session.dirty))
  304. #print('3.1- hasChanged(firstRow): {}\n\n'.format(hasChanged(secondRow)))
  307. print()
  309. """
  310. Test Output-
  312. 1.1- Session.dirty: IdentitySet([])
  315. Change detected: {'phones': {'newValue': [<__main__.GoogleAccount_phones object at 0x10a44bf50>, <__main__.GoogleAccount_phones object at 0x10a463b50>, <__main__.GoogleAccount_phones object at 0x10a44b890>], 'oldValue': None}, u'isDelegatedAdmin': {'newValue': [True], 'oldValue': None}, u'thumbnailPhotoEtag': {'newValue': [u'XRGOPUm7Vb3s/mVS-OxFB0Dyxo8'], 'oldValue': None}, u'suspended': {'newValue': [False], 'oldValue': None}, u'id': {'newValue': [u'108488927407595610966'], 'oldValue': None}, 'aliases': {'newValue': [<__main__.GoogleAccount_aliases object at 0x10a44b990>, <__main__.GoogleAccount_aliases object at 0x10a44b810>, <__main__.GoogleAccount_aliases object at 0x10a44be50>, <__main__.GoogleAccount_aliases object at 0x10a44bed0>], 'oldValue': None}, 'nonEditableAliases': {'newValue': [<__main__.GoogleAccount_nonEditableAliases object at 0x10a44bd90>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a44bcd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463bd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463dd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463c50>], 'oldValue': None}, u'thumbnailPhotoUrl': {'newValue': [u'https://plus.google.com/_/focus/photos/public/AI0YzcyZjRhNWZjOWExNDkwARP7Z8ODD5GJ'], 'oldValue': None}, u'includeInGlobalAddressList': {'newValue': [True], 'oldValue': None}, u'isAdmin': {'newValue': [False], 'oldValue': None}, u'etag': {'newValue': [u'XRsypGOPUmlmxokHB51cC07Vb3s/nnW4dSQR5PacZne6JsXkOrTfeBM'], 'oldValue': None}, u'lastLoginTime': {'newValue': [u''], 'oldValue': None}, u'orgUnitPath': {'newValue': [u'/Ops'], 'oldValue': None}, u'agreedToTerms': {'newValue': [True], 'oldValue': None}, 'externalIds': {'newValue': [<__main__.GoogleAccount_externalIds object at 0x10a463f90>], 'oldValue': None}, u'ipWhitelisted': {'newValue': [False], 'oldValue': None}, u'primaryEmail': {'newValue': [u'john.e.doe@testd.com'], 'oldValue': None}, u'isMailboxSetup': {'newValue': [True], 'oldValue': None}, 'emails': {'newValue': [<__main__.GoogleAccount_emails object at 0x10a471fd0>], 'oldValue': None}, u'kind': {'newValue': [u'admin#directory#user'], 'oldValue': None}, 'name': {'newValue': [<__main__.GoogleAccount_name object at 0x10a477bd0>], 'oldValue': None}, u'creationTime': {'newValue': [u''], 'oldValue': None}, 'websites': {'newValue': [<__main__.GoogleAccount_websites object at 0x10a47d810>], 'oldValue': None}, u'changePasswordAtNextLogin': {'newValue': [False], 'oldValue': None}, u'customerId': {'newValue': [u'C02fmcj1i'], 'oldValue': None}}
  316. 1.1- hasChanged(firstRow): True
  319. 1.2- Session.dirty: IdentitySet([])
  321. Change detected: {'phones': {'newValue': [<__main__.GoogleAccount_phones object at 0x10a44bf50>, <__main__.GoogleAccount_phones object at 0x10a463b50>, <__main__.GoogleAccount_phones object at 0x10a44b890>], 'oldValue': None}, u'isDelegatedAdmin': {'newValue': [True], 'oldValue': None}, u'thumbnailPhotoEtag': {'newValue': [u'XRGOPUm7Vb3s/mVS-OxFB0Dyxo8'], 'oldValue': None}, u'suspended': {'newValue': [False], 'oldValue': None}, u'id': {'newValue': [u'108488927407595610966'], 'oldValue': None}, 'aliases': {'newValue': [<__main__.GoogleAccount_aliases object at 0x10a44b990>, <__main__.GoogleAccount_aliases object at 0x10a44b810>, <__main__.GoogleAccount_aliases object at 0x10a44be50>, <__main__.GoogleAccount_aliases object at 0x10a44bed0>], 'oldValue': None}, 'nonEditableAliases': {'newValue': [<__main__.GoogleAccount_nonEditableAliases object at 0x10a44bd90>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a44bcd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463bd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463dd0>, <__main__.GoogleAccount_nonEditableAliases object at 0x10a463c50>], 'oldValue': None}, u'thumbnailPhotoUrl': {'newValue': [u'https://plus.google.com/_/focus/photos/public/AI0YzcyZjRhNWZjOWExNDkwARP7Z8ODD5GJ'], 'oldValue': None}, u'includeInGlobalAddressList': {'newValue': [True], 'oldValue': None}, u'isAdmin': {'newValue': [False], 'oldValue': None}, u'etag': {'newValue': [u'XRsypGOPUmlmxokHB51cC07Vb3s/nnW4dSQR5PacZne6JsXkOrTfeBM'], 'oldValue': None}, u'lastLoginTime': {'newValue': [u''], 'oldValue': None}, u'orgUnitPath': {'newValue': [u'/Ops'], 'oldValue': None}, u'agreedToTerms': {'newValue': [True], 'oldValue': None}, 'externalIds': {'newValue': [<__main__.GoogleAccount_externalIds object at 0x10a463f90>], 'oldValue': None}, u'ipWhitelisted': {'newValue': [False], 'oldValue': None}, u'primaryEmail': {'newValue': [u'john.e.doe@testd.com'], 'oldValue': None}, u'isMailboxSetup': {'newValue': [True], 'oldValue': None}, 'emails': {'newValue': [<__main__.GoogleAccount_emails object at 0x10a471fd0>], 'oldValue': None}, u'kind': {'newValue': [u'admin#directory#user'], 'oldValue': None}, 'name': {'newValue': [<__main__.GoogleAccount_name object at 0x10a477bd0>], 'oldValue': None}, u'creationTime': {'newValue': [u''], 'oldValue': None}, 'websites': {'newValue': [<__main__.GoogleAccount_websites object at 0x10a47d810>], 'oldValue': None}, u'changePasswordAtNextLogin': {'newValue': [False], 'oldValue': None}, u'customerId': {'newValue': [u'C02fmcj1i'], 'oldValue': None}}
  322. 1.2- hasChanged(firstRow): True
  325. 1.3- Session.dirty: IdentitySet([])
  327. Change detected: {}
  328. 1.3- hasChanged(firstRow): False
  331. 2.1- Session.dirty: IdentitySet([<__main__.GoogleAccountSAModel object at 0x10a47d890>, <__main__.GoogleAccount_websites object at 0x10a4d2c10>])
  333. Change detected: {'websites': {'newValue': [<__main__.GoogleAccount_websites object at 0x10a4c3d10>], 'oldValue': None}}
  334. 2.1- hasChanged(firstRow): True
  336. History(added=[<__main__.GoogleAccount_websites object at 0x106f38c10>], unchanged=[], deleted=[<__main__.GoogleAccount_websites object at 0x106f38210>])
  337. """
  338.  
Powered by spacepaste. Paste removal.