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?

  • 0
  • 0
  • 330
  • 33
  • 2

Answers

In your case, you have to KILL the underlying globals manually.
As seen in Storage definition

eg. <Global>^SVK</Global>  and all others too that you didn't publish

What I've seen so far DDL enabling might not fit your expectations because of that ancient mapping.
(looks like late 80ties or before) 

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.

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

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

This confuses me. 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. 

" 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 

Well, I thought the Objectscript classes were more akin to views than tables since the data is stored as a global, not as a table.

Comments

I figured it out. Just delete the class from the server. Though, seems like there should be a better way.

Why do you want to do that?

Can you describe what do you want to achieve?

Because I have 3 copies of the same class / table, 2 were for testing and I am done with them.

I don't understand. How do I remove a class then? I mean, I have 3 classes that are basically duplicates of each other, how do I remove the duplicates, without removing the data?

you can delete the class in Studio or from SMPortal.
so you delete the class DEFINITION and the related table DEFINITION. but no  data.

BUT.

DROP TABLE in SQL also deletes the data  AND the definition because this is part of SQL Standard. (Se also MySQL)

I'd suggest you take some time to really read the documentation or consume some online training.
 

I've read tons of documentation and I've been a developer for 20 years, but the correlation between these classes and the globals still eludes me.

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?

DROP. I understand how it reads the global values using the class definition, I don't understand how it drops (or any other write operation, for that matter).

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.

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.

Are there any performance concerns with enabling this?

not at all. It's just a readable formatted picture of the executed code.

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?

Classes and tables are mappings that are read "on the fly". That's exactly it.

So how can it be both "read on the fly" and be "a class which stores data on disk"? 

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.

That's the thing, I don't want views, I want tables, and I need indexing (which was how this all got started, the indexing is not working). I was not aware that the data could be written to (or dropped) without add how that was to happen in the class (still don't understand how that could possibly correlate).