Question
· May 31, 2019

How do I drop a table created by a Objectscript class?

I can't do it from the Management portal due. I get a message that DDL is not enabled. So how else do I drop the table?

Discussion (39)3
Log in or sign up to continue

Now, I am really confused. I only want to "drop" the SQL table created by the class, not the actual global (data). For example, in my testing, I created 3 tables for the same data (table, table2, table3), all pull data from the global (^PTG). I want to drop table 2 and table3. I did one by DDL and one by deleting the class. However, I seem to have lost data (global), but the timing was not when I did this, so I'm not sure when the data loss occurred.

Globals store data. That's the only way data can be stored inside Intersystems products.

Tables and classes are projections of this data. In your created classes (at the end) there's a Storage element.

It contains mappings of class properties (and table columns) to global nodes.

So if you delete this representation it does not delete the underlying global data, because class is essentially just a description of how to read and write into global.

That was my initial understanding, but then how does DDL drop the "table" (the global node data), since it is just a key-value store? There is no 1 to 1 relationship between the class and the global node data. How does a read definition get interpreted in the inverse? Makes no sense to me.

Also, it did not drop the global, only truncated it (dropped the data only). 

BTW, thanks to everyone in this thread (and others) for the time you are taking helping me. I had no idea this community existed and have been learning this on my own for the last 4 years and only found this forum about a month ago. 

Let's say you created class Point:

Class try.Point Extends %Persistent [DDLAllowed]
{

Property X;

Property Y;

}

You can also create it via DDL:

CREATE Table try.Point ...

It would create the same class.

After compilation our new class would have autogenerated Storage structure which is a mapping of global to data to columns and properties:

Storage Default
{
<Data name="PointDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>X</Value>
</Value>
<Value name="3">
<Value>Y</Value>
</Value>
</Data>
<DataLocation>^try.PointD</DataLocation>
<DefaultData>PointDefaultData</DefaultData>
<IdLocation>^try.PointD</IdLocation>
<IndexLocation>^try.PointI</IndexLocation>
<StreamLocation>^try.PointS</StreamLocation>
<Type>%Library.CacheStorage</Type>
}

What is going on here?

From the bottom and up (bolded are important, ignore the rest):

  • Type: type of generated storage, in our case the default storage for persistent objects
  • StreamLocation - name of global where we store streams
  • IndexLocation - name of global for indices
  • IdLocation - name of global where we store ID autoincremental counter
  • DefaultData - name of storage XML element which maps global value to columns/properties
  • DataLocation - name of global to store data

Now our default data is PointDefaultData so let's see it.  Essentially it says that global node has this structure:

  • 1 - %%CLASSNAME
  • 2 - X
  • 3 - Y

So we might expect our global to look like this:

^try.PointD(id) = %%CLASSNAME, X, Y

But if we output our global it would be empty because we didn't add any data:

zw ^try.PointD

Let's add one object:

set p = ##class(try.Point).%New()
set p.X = 1
set p.Y = 2
write p.%Save()

And here's our global

zw ^try.PointD
^try.PointD=1
^try.PointD(1)=$lb("",1,2)

As you see our expected structure %%CLASSNAME, X, Y is set with $lb("",1,2) which corresponds to X and Y properties of our object (%%CLASSNAME is system property, ignore it).

We can also add a row via SQL:

INSERT INTO try.Point (X, Y) VALUES (3,4)

Now our global looks like this:

zw ^try.PointD
^try.PointD=2
^try.PointD(1)=$lb("",1,2)
^try.PointD(2)=$lb("",3,4)

So the data we add via objects or sql is stored in globals according to storage definitions (you can modify manually storage definition by replacing X and Y in PointDefaultData  - check what happens to new data!)

Now, what happens when we want to execute SQL query?

SELECT * FROM try.Point

It is translated into ObjectScript code that iterates over ^try.PointD global and populates columns based on storage definition - PointDefaultData  part of it precisely.

Now for modifications. Let's delete all the data from the table

DELETE FROM try.Point

And let's see our global after it:

zw ^try.PointD
^try.PointD=2

Note that only ID counter is left, so new object/row would have an ID=3. Also our class and table continue to exist.

But what happens on:

DROP TABLE try.Point

It would destroy the table, class and delete the global.

zw ^try.PointD

It's all about multi-model nature of IRIS and Caché.

Everything in Caché and InterSystems IRIS is being stored in globals - key-value sparse stored arrays.

Every persistent class (a class which stores data on disk) has the map of how the data should be stored in a particular global (globals).

ObjectScript Class definition provides the API of how the data will be stored and read from a global (globals) via Object Script or SQL access.

You can specify standard or custom storage strategy for a class. 

The persistent 'SomeClass' class with standard storage strategy will provide the API to store data in ^SomeClassD data for data itself and ^SomeClassI global for indexes.

You can see the storage strategy of a class in the source code of a persistent class immediately after successful compilation - it will be visible in a related XML block at the bottom of the class.

In your case you have a custom storage strategy which could be everything. Check @Brendan Bannon's series on that which could be really helpful for you.

As @Eduard Lebedyuk mentioned, if you delete the class as source code (e.g. via Management Portal)  it will not delete the data itself.

But! As @Robert Cemper mentioned if you delete it via SQL API with DROP statement it will delete data in a global too (because we suppose the data to be deleted if we DROP a table, right?).

HTH

I should have responded to both @Evgeny Shvarov  and @Eduard Lebedyuk at the same time, so see above, but I still don't understand what this means:

Every persistent class (a class which stores data on disk) has the map of how the data should be stored in a particular global (globals). 

Stores data on disk? Meaning, that if we create these persistent classes, the data is stored twice? Once in the global node and in some other format as defined (or not defined by the class)?  

But! As @Robert Cemper mentioned if you delete it via SQL API with DROP statement it will delete data in a global too (because we suppose the data to be deleted if we DROP a table, right?).

But just to reiterate, how does the DDL interpret a read definition into a write definition? The data stored in the global node and the definition do line up exactly (in our case, almost not at all).

Stores data on disk? Meaning, that if we create these persistent classes, the data is stored twice? Once in the global node and in some other format as defined (or not defined by the class)?

The data is stored in globals and only globals. Globals themselves are physically written on disk.

But just to reiterate, how does the DDL interpret a read definition into a write definition? The data stored in the global node and the definition do line up exactly (in our case, almost not at all).

Please expand your question. Do you mean how does SELECT or DROP or whatever SQL statement against table interacts with globals?

SQL is a declarative language (same as HTML for example). It does not DO anything. All it is is a description of what do you want to achieve. 

In the case of InterSystems IRIS (and any other dbms for that matter) we take SQL statement, parse it into AST and generate code in other imperative language from AST. Now this generated code gets executed.

In case of InterSystems IRIS we generate the code in ObjectScript language. For DROP statement it would include  this line:

kill ^globalD

If you want to you actually can see generated OjectScript code. To do that

Open SMP > System Administration > Configuration > SQL and Object Settings > General SQL Settings.
Enable "Cached Query - Save Source" — This specifies whether to save the routine and INT code that Caché generates when you execute any Caché SQL except for embedded SQL.

After that purge query from portal if it already exists and execute the query again in SMP.

You'll see something like this:

Cached query %sqlcq.<NAMESPACE>.cls<NUMBER>

Go to studio and open %sqlcq.<NAMESPACE>.cls<NUMBER> class - it would contain generated ObjectScript code that executes for your SQL query.

Stores data on disk? Meaning, that if we create these persistent classes, the data is stored twice? Once in the global node and in some other format as defined (or not defined by the class)?  

ANY data in Caché and IRIS is stored in global and it's not stored twice, everytime in a global, which is in the storage strategy of the persistent class.

Any SQL table in Caché and IRIS has the related persistent class in the metadata.

But just to reiterate, how does the DDL interpret a read definition into a write definition? The data stored in the global node and the definition do line up exactly (in our case, almost not at all).

Could you elaborate, please?

Just in case: you can deal with Caché and IRIS as with any relational DB: use DDL, SQL via ODBC/JDBC.

And yes, every time you call

CREATE TABLE A 

the new persistent class A is being created on the backend to store data in ^AD global.

ANY data in Caché and IRIS is stored in global and it's not stored twice, everytime in a global, which is in the storage strategy of the persistent class.

So a new global is made that is in this storage strategy format? The format of the global I am pulling data does not change when I create a class with a storage strategy but are you saying a new global is created in this new format? Or I am still not understanding what you mean?

For example, we have a class that converts this:

To this:

When y'all keep saying this:

Every persistent class (a class which stores data on disk) has the map of how the data should be stored in a particular global (globals). 

I don't see how that is occurring. Where is that data stored? Is that not just a mapping that is read "on the fly"? If not, then by what mechanism is the data stored in this new format?

The datamodel in COS is such that you have multi-modal access to your data in different ways. Having the ability to access your data via objects (class definitions), SQL (tables), and raw globals is what makes it so powerful. Ultimately the data is always stored in globals. The mapping for the other modes of access are done via class definitions. Class definitions are also transparently mapped to SQL tables. So any time you create a class, a corresponding table is being created and vice-versa. The SQL-concept of "views" is separate from all of this and sits on top. @Robert already pointed out the documentation for storage mapping which actually allows you to customize the relationship between the global structure and a class(/table). This is usually not recommended unless you have legacy data you need to access. The default storage strategy does a very good job to store data effectively.
Note: Just creating a class will not create the global structure just yet, it will only be created once you start putting data in.
Note2: Indices are just additional globals that get added to your class storage.
Note3: If you are having trouble with your indices, it might be a good start to open a WRC issue. They have very good people to help you sort through any issues quickly.

The confusion here is that you created 3 class mappings for a single global (not a good practice) and your understanding is that they're just "views", however the 3 of them are full class/tables with the same properties/ownership of the data. With that in mind, using SQL DROP statement has the same effect for any of the 3 tables/classes  (if DDL were allowed).  As others have pointed out, you can accomplish what you're trying to do with DROP %NODELDATA or by removing the class definition (either from Studio or programatically). 

If you wanted to declare views instead of tables then you need to do so via SQL CREATE VIEW statement.

if you use DROP without option %NODELDATA  your data are gone. 
https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=RSQL_droptable

Short Caché Basics:

  • all data are stored in globals, nowhere else
  • classes describe the structure how data are stored and can be accessed as Objects or Tables. 
  • classes never change, generate or delete data, but provide methods to do so.

so having 3 'tables' on the same global is just having 3 views of the same house.

Recommend readings: Long Caché Basics and Introduction to Caché SQL

" In MySQL, a "view" is just a saved SQL query - dropping a view does not drop data. 
I never imagined dropping the class  (which I thought was just a view) would empty the global. 
The global was still there, but all the data was gone. "

Caché is different from MySQL but has also VIEWS and dropping a View doesn't touch any Data

But identical as in MySQL DROP TABLE deletes the data.

from http://www.mysqltutorial.org/mysql-drop-table
The DROP TABLE statement removes a table and its data permanently from the database. 

and this was your question:
So how else do I drop the table?

and later:
 I did one by DDL 

Hey Keith, how's it going from this time last year?  Caché was my first language (actually, it was MUMPS then), and I've always worked with data stored in globals.  When I learned SQL, MySQL, and Java, I realized that I could probably never go to a language where I coudln't "see" the actual data.   Having to rely on tables and SQL and not be able to get to the actual data stored on disk? I'd feel  like I did when I was using chopsticks for the first time.  

When I tell other non-InterSystems programmers how we have access to the actual data, they're flabbergasted and amazed. But, that is the key to understanding the Storage in each class (which by now, you're probably quite familiar with).   SQL will read this Storage "map" which maps the properties to the global storage, and picks it out of the global for us.  

Back in the old days, we would create our own global structure to hold the data.  Globals often looked like ^Point(pointId,"X")=3  and ^Point(pointId,"Y")=4, or more often, ^Reg(PatientId,"Name")="Smith,Amy", or ^Patient(PatientId,1)=Name^age^DOB^ZipCode and ^PatientIndex("Smith,Amy",PatientId)="" and ^Patient("Count")=last PatientId

Any IDX programmers here?  We had a entire book just to understand the globals.

These days, we let InterSystems create the global structure for us, which appears in the Storage definition when a class is compiled, but it's still the same as before -- the data is still stored in a global, and you can edit it and delete it from the global - not recommended though because IS keeps track of counters.

Anyway, I'm sure you're all set, but I thought it might help someone reading this to know the history of data storage and how it's a completely different paradigm than other databases. 

Also, I was looking for the best way to delete the storage from a deleted property.  You know - you delete the property, but don't delete the storage element, and the node is still there in the global?  Normally, this wouldn't ever be noticed, because when using SQL, you don't see the deleted property, but in our case, we are using the global's stucture to view certain structures in the data (it's a bunch of arrays of Objects).  The deleted property's storage was getting in our way.

I decided the best way to delete the leftover data was to simply kill the nodes: ^Test.Package.ClassNameD(id,"TestProperty")=""

This would have been impossible with SQL because the property doesn't exist anymore!

Thanks,

Laura

Thanks for your reply, however, these concepts are still mostly beyond me. Due to lack of resources, I have not made much progress as all, to be honest. Our configuration is currently broken (we have huge SQL tables that cannot be indexed, so they perform terrible, for example), so we are just limping along.

I realize today that I made a mistake in the topic heading - I did not want to "drop the table",  but just delete the SQL table definition, not delete data.

Hi Keith, I am sorry! In a SQL database (MySQL, Oracle...), is there a way to drop a table (not a view; a Table) without deleting data?

If you really want to remove the SQL table but keep the data, you can merge the global that stores the actual data into a diffefrent global, such as merge ^SavedData = ^Package.TablenameD (You have to find the right global name from the Storage in the class).

This could take a few minutes to complete. Then you can delete the class.  I don't recommend this.

I suggest you contact IS to help you create a table definition that can be indexed, and perhaps populate this new table with your current data - perhaps a new table will be more efficient. IS is very helpful when it comes to support. And, let us know how it goes!

Thanks,

Laura

A View in SQL (at least in MySQL) is basically just a saved query - deleting the view has no effect on the data.

We are not the customer of IS, our vendor is, so we have no way of getting support from IS, unfortunately  (and our vendor is not any help). That plus we are on a very old version of cache, which makes it more difficult to even get community support.