Issue migrating Constraints

[amit]

Hello,
                       
I am migrating the tables from one SQL Server DB to another using CQL. I am successfully able to migrate the data from one table to another, but I have the following queries:

1. My table has the following fields:

Student_ID (Primary Key), Student_Name and Student_Address

While importing, an additional ID field is generated and marked as a Primary Key instead of 'Student_ID.' Even though I tried specifying 'id_column_name=Student_ID,' still the ID field is set as the Primary Key. Is there a way to import it as is, ensuring that my Primary Key remains 'Student_ID'?

Here is the sample code snippet:
                       
                        instance J = import_jdbc ""  : schStudents {
                        sStudentMaster -> "SELECT Student_ID, Student_Name, Student_Address,Student_ID FROM StudentMaster"
                                    options           
                                    prepend_entity_on_ids = true
                                    id_column_name=Student_ID
                        }

2. Similar to the above scenario, I have two tables having a Primary Key in both tables and one table has a foreign key pointing to the Primary Key of the base table. While doing the import the newly generated ID field is marked as a Primary key in both the tables and foreign key points to the ID field of the base table instead of the original Primary key.

Here is the sample code snippet:

schema sch = literal : sql {
entities
sDependendents
sEmployees
foreign_keys
employee_id   : sDependendents -> sEmployees
   attributes     
      dependent_id : sDependendents -> Integer
      first_name  last_name relationship : sDependendents -> Varchar
      employee_id : sEmployees -> Integer
      first_name last_name email phone_number : sEmployees -> Varchar
      department_id : sEmployees -> Integer
  }

instance J = import_jdbc ""  : sch {

sEmployees -> "SELECT employee_id as id,employee_id,first_name,last_name,email,phone_number,department_id   FROM Employees"
sDependendents -> "SELECT dependent_id as id, dependent_id,employee_id,first_name,last_name,relationship FROM Dependents"
options
prepend_entity_on_ids = false
}

Kindly assist me in resolving the issue and inform me of any mistakes I might be making. Thanks in advance.

~AM  

[Ryan Wisnesky]

I’m conflicted between “the surrogate (CQL-created) primary keys have to be there and you are observing that import followed by export is not the identity, but forms a monad” and “the id_column_name options is in the wrong spot or CQL isn’t picking it up due to internal errors” but I will look into it and get back to you soon.

--
You received this message because you are subscribed to the Google Groups "Categorical Data" group.
To unsubscribe from this group and stop receiving emails from it, send an email to categoricalda...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/e4a60837-6b6d-4adb-b63b-2dc9db710763n%40googlegroups.com.

[Ryan Wisnesky]

Upon reflection, the answer seems to be the former- what you’re seeing is an artifact on the different semantics primary and foreign keys have in CQL and SQL.  Or rather, category theory and relational theory.  In any case, it’s not safe to use CQL primary/foreign keys as SQL primary/foreign keys because in CQL they are essentially row identifiers that must be treated up to isomorphism (renaming) and in SQL they are (sometimes implicit) data.  So CQL will insist upon adding its own surrogate primary and foreign keys during SQL import and export.

The fix is to use CQL “constraints” instead of CQL foreign keys, and to ignore the CQL generated surrogate key in SQL altogether (or treat it as “provenance” from the CQL export- the surrogate primary keys have meaning inside of CQL).  Anyway, in place of “is” below:

entities

adult person

foreign_keys

is : adult -> person

attributes

name : adult -> string

name : person -> string

Which “reifies” the foreign key “is" into an actual column, to handle SQL style foreign (and primary- that’s coming later) keys, which can for example be multi-part (have multiple columns participating), we can use CQL constraints:

constraints C = literal : sch {

forall a : adult -> exists p : person where a.name = p.name. #a foreign key

}

CQL constraints can be checked against an instance, and CQL instances can be “completed” or “chase” to conform to constraints (or run forever, or fail with contradiction). There are many examples of constraints in the FOAF example.  Primary keys are similar:

constraints C = literal : sch {

forall a1 a2 : adult where a1.name = a2.name -> where a1 = a2 #a primary key

}

The “Constraints” example illustrates how constraints can be used to migrate data (they are more expressive than CQL queries).  The logic that CQL uses for constraints has many names: https://en.wikipedia.org/wiki/Embedded_dependency 

Anyway, that’s a lot of info, but I’m happy to discuss further- lmk

[amit]

Thanks for the update. 

a) What I understood is that if we need to migrate with constraints (Primary, Foreign Key, etc.) similar to SQL, then we need to delete the constraints created by CQL export and create the constraints through separate commands, like this:

ALTER TABLE ExportedsStudentMaster DROP CONSTRAINT PK__Exported__3213E83F57AC9158   #Delete Primary Key (ID automatically created)
ALTER TABLE ExportedsStudentMaster ALTER COLUMN Student_ID INT Not Null   # update Student_ID field to be Not Null
ALTER TABLE ExportedsStudentMaster ADD PRIMARY KEY (Student_ID)    # Create Student_ID as a primary key

Similarly, I need to create Foreign, Unique, or any other constraint.  

Is this a correct understanding? Please confirm.

b) Is there a way to import the data from an Excel file like we can do it from a Database using
instance J = import_jdbc ""  : S1 .
If yes then please point to some examples to which I can refer.

Thanks again.

~AM  

[Ryan Wisnesky]

Hi Amit,

Depending on what you’re trying to do, you could add additional UNIQUE KEY constraints instead of deleting the PRIMARY KEY constraints that CQL generated; in many SQL systems, you can write foreign keys between unique keys, not just primary keys, and have as many unique keys as desired.

Re: Excel, if you are just interested in data, you can look at the “CSV” and “QuickCSV” example for how to import CSV data after exporting CSV data from Excel.  If you’re interested in actually integrating the formulae that make up live spreadsheets (such as we did here https://arxiv.org/abs/2209.14457) that capability is not yet available in open-source CQL.

Ryan

To view this discussion on the web visit https://groups.google.com/d/msgid/categoricaldata/ca30e43f-eca3-47d8-83ef-b5508df5a3ccn%40googlegroups.com.