Django testing MS-SQL legacy database with stored procedures

Question

In Django I would like to use Unit Test for testing a MS-SQL Server legacy database. The database is using stored procedures for adding data. The situation is as follow:

• The MS-SQL database has the following settings in Django:

  DATABASES['vadain_import'] = {
    'ENGINE': 'sql_server.pyodbc',
    'USER': 'xx',
    'PASSWORD': 'xxx',
    'HOST': '192.168.103.102',
    'PORT': '',
    'NAME': 'Vadain_import',
    'OPTIONS': {
      'driver': 'SQL Server Native Client 11.0',
      'MARS_Connection': True,
    }
  }
  

• The models of the database are made with inspectdb, example:

  class WaOrders(models.Model):
      order_id = models.IntegerField(primary_key=True, db_column='intOrderId')
      type = models.TextField(db_column='chvType', blank=True)
  
  class Meta:
      db_table = 'WA_ORDERS'
      database_name = 'vadain_import'
      managed = False
  
  # (There's a lot more of properties and models)
  

• In models is executing the stored procedures. I cann't use the save functionality of Django, like WAOrders.save(), because in the MS-SQL database the primary key's are generated in the stored procedure.

  @classmethod
  def export(cls, **kwargs):
      # Stored procedure for adding data into the db
      sql = "declare @id int \
          set @id=0\
          declare @error varchar(1000)\
          set @error=''\
          exec UspWA_OrderImport\
          @intOrderId=@id out\
          ,@chvType=N'%s'" % kwargs['type'] + " \
          ,@chvErrorMsg=@error output\
          select @id as id, @error as 'error' \
      "
      # Connection Vadain db
      cursor = connections['vadain_import'].cursor()
      # Execute sql stored procedure, no_count is needed otherwise it's returning an error
      # Return value primary key of WAOrders
      try:
          cursor.execute(no_count + sql)
          result = cursor.fetchone()
          # Check if primary key is set and if there are no errors:
          if result[0] > 1 and result[1] == '':
              # Commit SP
              cursor.execute('COMMIT')
              return result[0]
  

• There is a mapping for creating the models, because the MS-SQL database expect different data then the normal objects, like ‘order’.

  def adding_data_into_vadain(self, order):
      for curtain in order.curtains.all():
          order_id = WaOrders.export(
              type=format(curtain.type)
          )
          # relation with default and vadain db.
          order.exported_id = order_id
          order.save()
  

The function is working proper by running the program, but by running ‘manage.py test’ will be created a test databases. This is given the following problems:

• By creating test database is missing the south tables (this is also not needed in the legacy database)
• By changing the SOUTH_TESTS_MIGRATE to False I’m getting the error message that the tables are already exists of the default database.

My test is as follow:

class AddDataServiceTest(TestCase):
  fixtures = ['order']
  def test_data_service(self):
    # add data into vadain_import data
    for order in Order.objects.all():
      AddingDataService.adding_data_into_vadain(order)
    # test the exported values 
    for order in Order.objects.all():
      exported_order = WaOrders.objects.get(order_exported_id=order.exported_id)
    self.assertEqual(exported_order.type, 'Pleat curtain')

Can somebody advise me how I can test the situation?

Answer 1

maybe inside your WaOrders you can extend save() method and call there export function.