Can two electrons (with different quantum numbers) exist at the same place in space? The that you are trying to DROP does exist, therefore the clause part "IF EXIST " is true. I disagree. **It is Simple, just follow 2 steps. CREATE TABLE statement. Perhaps the pragma legacy_alter_table is inappropriately named. It does not go on a hunt looking for what you might have meant. That's why I'm using "IF EXISTS". The same applies to "CREATE VIEW IF NOT EXISTS" (which I haven't tested yet): It should of course do nothing if the given name doesn't exist as a view. DROP INDEX IF EXISTS schema-name . After creating a UNIQUE index if you are trying to insert a record into the table that already exists in the UNIQUE Index, this leads to an error message. as part of a DROP TABLE command violates any immediate foreign key constraints, All indices and triggers associated with the table are also deleted. I did some checking. It plays fast-and-loose with the standard, and it works for them, it works for DB beginners or simple implementations, and one could argue that it definitely has merit. When you're upgrading schema - your main concern should be not to screw up. The DROP INDEX statement removes an index added with the CREATE INDEX statement. To subscribe to this RSS feed, copy and paste this URL into your RSS reader. In this case a notice is issued instead. The only purpose of the "IF EXISTS" clause is to suppress the error. SQLite Tutorial website helps you master SQLite quickly and easily. Sorry for that. Not the answer you're looking for? Note, that the statement isn't DROP VIEW test ON ERROR IGNORE,(which doesn't exist as a statement) so the error of test being a table that is the target of a DROP VIEW could be reasonably argued can be raised. Here is the basic syntax of the EXISTS operator: EXISTS (subquery) Code language: SQL (Structured Query Language) (sql) In this syntax, the subquery is a SELECT statement that returns zero or more rows. index-name. My personal feeling is that this isn't a strong argument. Parameters. Here is the basic syntax of the DROP VIEW statement: DROP VIEW [ IF EXISTS] [schema_name. It'll result in a construct like. The problem is, most people obviously think about using SQLite within an application. This SQLite tutorial explains how to create, drop, and rename indexes in SQLite with syntax and examples. As NULL isnt considered a value, so a UNIQUE index will not prevent one or more NULLs. In some cases this approach has to be dropped and a view replaces the original table. what is difference between na-nimittaggh and animitta? But I cannot square such a desire with uncertainty as to whether a table of that name already exists. Do you have a rationale for why you prefer fail to drop? The documentations of all RDBMSs that I found and that support the "IF EXISTS" extensions agree: If the specified view cannot be found and the IF EXISTS clause is not present, it is an error. This should apply not only to indexes but also to triggers and views which reference the column being dropped explicitly. The optional IF EXISTS clause suppresses the error that would normally I maintain a program which has a UI which allows a user to do various useful things on more or less any sqlite database: it examines the schema to find out which objects exist. I think the OP expects no error in either case. If we're talking about a serious product. Here I have the point that I tell the engine exactly what I want - and get a different response. Since this is not possible, in any way for all Engines (or at least, the mainstream ones) your assertion that: Advantages of not raising an error: View can properly be dropped even if a table with same name exists/. There wouldn't be any adverse effects, would there? As it turns out, eval() wrapping a query that produces DDL does not work. What if you said DROP TABLE IF EXISTS jerry; while a View with the name "jerry" does exist? We could also create an index with more than one field as in the example below: This would create an index called customer_idx that uses two columns - state and city. Generating an error is an op. You get that (and advocate for) DROPping a DB object by name should be excused if that name does not exist at all in the namespace and the programmer added "IF EXISTS". The index will be dropped if all columns from the index were dropped. This has been a typo of mine. Why would you think that an error on your part to know what you are doing should not be reported as an error? If I had to do this, the IF EXISTS is merely useless because I could check for the view to exist by myself then. This means that values in the indexed column will sort without respect to the case. With that, in conjunction with some (or maybe a lot of) SQL which queries the sqlite_master table and builds DDL using string literals and concatenation, results passed into eval(), you could accomplish your schema change without writing any code beyond what the SQLite parser and eval() can accept. This is because there is no way specified to disambiguate the names in queries or DML. 3 The following code @Override public void onUpgrade (SQLiteDatabase db, int i, int i1) { db.execSQL ("DROP IF TABLE EXISTS " + TABLE_NAME); onCreate (db); } always gives an error index table trigger or view got if Why is that? Second, specify the schema of the view that you want to delete. BTW, I understood your initial complaint. Third, use the IF EXISTS option to . Inb4 I incur the wrath of every MySQL hacker One reading of the top part of the previous message can seem to say that MySQL is for beginners or simple implementations, this is of course not true. To create a unique index on a table, you need to specify the UNIQUE keyword in the CREATE INDEX statement. And of course I do not want (and never wanted and will never want) to have both a view and a table with the same name. You can create an index in SQLite using the CREATE INDEX statement. ), this sight is a bit narrow-minded. No, it does not exist as a view. But it is not a no-op if there is a table with the given name. While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. I'm not advocating to use the Postgres way (even though I believe Postgres does work the way you are lobbying for, so you should advocate for it), I'm just saying if you are going to appeal to authority, appeal to Postgres in stead of MySQL, it's a much higher authority in the world of correct database principles. If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op. 1.1. A failed test can leave a view or a table still existing. The syntax to rename an index in SQLite is: DROP INDEX [IF EXISTS] index_name; CREATE [UNIQUE] INDEX [IF NOT EXISTS] new_index_name ON table_name (column1 . Indexes are removed with the DROP INDEX command. | Field | Type | Null | Key | Default | Extra | If you want no table or view of that name to exist, you'll need to do 2 'drop if exist' ops. ALTER TABLE RENAME The RENAME TO syntax changes the name of table-name to new-table-name . Drop database object if it exists. With the typo, it appeared you sought to solve an illusory problem. The error is an ABORT, not a ROLLBACK. It is not possible, in SQLite or any other DBMS that is going to implement SQL, to have a view with the same name as a table in the same schema. It does not remove the data of the base tables. The alternative which you're implying is that no error be generated and the presumably up-coming create view fails instead because there's a table with that name. I don't want to have to clean up by hand after errors, so I want my test scripts to DROP any views or tables that they will create. How can I drop a table in a database if another table exists? I'm not sure I do think that, I was just trying to (maybe) clarify the question. Well, if you need to know whether "metadata" is a table or a view, your upgrade application should be using a query like: and your application will then be told whether it is a table or a view, and can do the proper thing, or have a controlled explosion if the type returned is something other than what you are prepared to cope with. You may write to us at reach[at]yahoo[dot]com or visit us The following queries work: DROP TABLE IF EXISTS MyTable; CREATE TABLE IF NOT EXISTS. Now, instead of continuing the debate on what SQLite "ought" to do, or how it should be documented, I offer a solution which might satisfy your needs, if not your stated constraints: There is a loadable extension which implements an eval() function. What if you said If would not matter whether you said "don't do this if there is no variable named a because there is, in fact, a variable called a -- you are simply being told that you need to keep better track of the objects you have created. You will need to say CASCADE if anything outside the table depends on the column, for example, foreign key references or views. [42000][1072] Key column 'A' doesn't exist in table. SELECT exec(NULL, 'DROP VIEW IF EXISTS xxx') ; So @everyone who thinks the behavior is correct: Please explain to me how I should drop a view if, and only if, it exists as a view and without giving an error, and with plain SQL only as it has to be carried out within a batch of SQL statements. It has no side effects. Of course, the scalar result from that same inner select could be collected and passed to sqlite3_prepare_statement() and executed. Perhaps it should be renamed (or aliased) to pragma allow_inconsistent_schema, which is more in line with its actual functioning. ELSE END; Which leads us to the question about the sense of "IF EXISTS". Because the user could have wanted to delete the table instead of the index. Each tutorial explains the complex concepts in simple and easy-to-understand ways so that you can both understand SQLite fast and know how to apply it in your application effectively. Any triggers attached to the table are (It can be passed to eval() too, but with little purpose.). to do what PostgreSQL does. namestr. I wrote a user-defined function which can be used in a script to evaluate a possibly dynamically constructed SQL statement, and return any error message as a text string, which solves the OP's problem for me: I don't know if "eval" would have helped me with that. Manga where the main character is kicked out of a country and the "spirits" leave too. Example Here's an example to demonstrate: DROP TABLE IF EXISTS t1; That statement drops a table called t1 if it exists. Just dropping the constraint is likely a problem, as there was very likely a reason the constraint was there in the first place. The set of index-like objects presently only includes INDEX's. Advantages of not raising an error: ?? I think it would be more counter-intuitive for CREATE VIEW x to fail after DROP VIEW IF EXISTS x returned no error than for DROP VIEW IF EXISTS x to fail even though x is a table and not a view. If a new object called a FUDGYBANGER that was an index-like object so that the set of index-like objects now included FUDGYBANGER's as well and INDEX's, then you would get exactly the same behaviour if you tried to DROP FUDGYBANGER but it was an INDEX rather than a FUDGYBANGER. But that doesn't seem to be the case. In your example you create an object named "test", which is of type "table". The application has never used an index on column X, so the upgrade procedure just says "DROP COLUMN X". I can't find anything by searching on the sqlite web site. Why should the concept of "nearest/minimum/closest image" even come into the discussion of molecular simulation? the implicit DELETE FROM causes any The command is "DROP VIEW IF EXISTS " The printed error comes from the shell, the no-op result code indicating an error comes from the library. What I mean is that DROP COLUMN should not fail in such a case. Thanks for something concrete to consider in contemplating this issue. So then the command DROP VIEW is applied to that , to which you get the error that it is not a view and YOU made an error and should use DROP TABLE to drop that . an error is returned and the table is not dropped. Heres what happens when we dont use IF EXISTS: This time we get an error telling us that the table doesnt exist. This is clear with your typo acknowledged and corrected. A Database program should generally understand that state and purpose of the database. The "IF EXISTS" clause is just useless then. I think SQLite should continue "Murder laws are governed by the states, [not the federal government]." How hard would it have been for a small band to make and sell CDs in the early 90s? Can you describe some scenario, likely to be repeated, where this in fact creates more work than the following pseudo-code? If it cannot be found, report an error; if it is of type TABLE, then drop it; otherwise report that you are using the wrong command because the table-like object with name is not a TABLE but rather is of type VIEW (or whatever type that table-like object that was found happens to have). Presuming you are generating the SQL, is there a way to make that process smarter? If you are unwilling to do a "DROP VIEW IF EXISTS" and "DROP TABLE IF EXISTS", then settle for one succeeding, you can query the sqlite_master table. Based on the Richard's answer, and the observation from @AnonCoward, the following is true: As of v3.37.2 (January 2022), SQLite does NOT support either of the following: There is currently a prototype implementation of this functionality in SQLite. Fossil 2.23 [673dc38ffb] 2023-06-10 09:16:45. b) complicates deleting views a lot! The table can not be recovered. Because each index name must be unique within the database, we do not have to specify the table name in the DROP INDEX statement. Find centralized, trusted content and collaborate around the technologies you use most. Records where the last_name is a NULL value will not be included in the index. MySQL is still much more wide spread than Postgres ;). If you want to drop the index com_name_collate from the database, the following can be used. Constraint table name. If you then said "delete guacamole a" you would expect to to get an error saying that "a is not a guacamole, it is a mudslinger". With this argument, the IF [NOT] EXISTS clause would be needless anywhere. Otherwise, it seems a bit like giving a five-year-old a loaded handgun and telling them to 'play safe'. Ask Question Asked 1 year, 3 months ago Modified 1 year, 3 months ago Viewed 3k times 2 Using SQLite 3.37.2. I for one would hate if SQLite starts doing magic background things when I try to alter a table in a way that doesn't stroke with the current rest of the schema. All hidden stuff always leads to bugs that are hard to detect. Please, no new "DROP TABLE IF EXISTS" affair! | b | int(11) | NO | PRI | NULL | | If the specified view cannot be found and an IF EXISTS clause is present in the DROP VIEW statement, then the statement is a no-op. If you're mounted and forced to make a melee attack, do you attack your mount? Please, just do as Keith suggested and emit an error. And because SQLite doesn't simply provide "ALTER TABLE", but instead requires a 12-step update algorithm, we have to drop any views that refer to the particular table. An index is a performance-tuning method of allowing faster retrieval of records. These will not be allowed with ALGORITHM=INSTANT, but unlike before, they can be allowed with ALGORITHM=NOCOPY. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key. Capturing number of varying length at the beginning of each line with sed. The shell does no such detective work. In android sqlite database is used to store and perform insert, update, // Drop older table if exist error in sqlite "DROP TABLE IF EXISTS" android. Fourth, I described what I believe is a perfectly reasonable use case in another post at https://sqlite.org/forum/forumpost/53a2dec184. The moment however there is any index using that column in addition to any other, and I did not explicitly "fix" that index before trying to drop that column, please error out - Loudly and clearly. Does SQLite support IF EXISTS and IF NOT EXISTS for adding/dropping columns? We are working with these databases and want to change a different table now. I do not understand your problem. Clearly, there could not have been a view of that name. Browse other questions tagged, Where developers & technologists share private knowledge with coworkers, Reach developers & technologists worldwide, True, which probably means that the answer is still "no". RESTRICT and CASCADE are allowed to make porting from other database systems easier. If it's a view, it should be dropped. To save time and space, I acknowledge that some SQLite feature change(s) could obviate such an approach. It can be argued that the current behavior does match the meaning of the statement, in your example above, test DOES exist, it just isn't a view, so if EXISTS is satisfied, and DROP VIEW fails on the wrong type of object. Most seasoned users have STRICT mode enabled by default. When it sees an error return, it emits whatever error message the library associates with the error occurrence. The docs for DROP INDEX don't explicitly mention the IF EXISTS clause, so I can't say what I expect it to do. Why make them jump through the extra hoops? There have been a lot of comments on this topic, and I want to add a bit of a voice to make you think about what you are trying to do. You can drop a clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. If two asteroids will collide, how can we call it? This might be nice when working in the SQLite-CLI, but it is a no-go for automatic routines. DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None) [source] #. Indexes (or indices) are very special to a database lookups, because, this allows specific rows in a table to be found without having to scan every row in the table. MariaDB recreates such an index without the dropped column and I vote for following this paradigm. It doesn't make any sense to me at all. In order to drop the column, an explicit DROP PRIMARY KEY and ADD PRIMARY KEY would be required. It just keeps the complexity of the current ALTER TABLE procedure. Why does Tony Stark always call Captain America by his last name? If we accept this, we can also accept that either command (DROP VIEW or DROP TABLE) should drop the object, regardless of type. DROP VIEW IF EXISTS xxx ; DROP TABLE IF EXISTS xxx ; for each table or view xxx that the script is subsequently going to create. The index is completely removed from the disk. The DROP VIEW statement removes a view created by the CREATE VIEW statement. Hence, that last advantage should be dropped from your list. SQLite is in the Public Domain. Which kind of celestial body killed dinosaurs? android sqlite android-database Share Improve this question Follow Not slated for release in the upcoming v3.38.0 release, but sometime in the future. Should they be made to adopt the SQLite way of things? drop-index-stmt: DROP INDEX IF EXISTS schema-name . rev2023.6.12.43489. With DROP VIEW/TABLE IF EXISTS there should be no errors. In a UNIQUE index, there cannot be any duplicate entries. You could create a partial index on a table where only a subset of the records is included in the index. Unique Indexes If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. table_name - Name of the table with which the index is associated. Bang -- the application's upgrade procedure fails. If the user intends to drop a column and SQLite says you can't because of x,y and z, then the user has to manually do that work anyway. For example, here is some sample generated SQL: That it was there makes me wonder (more than before) what you are doing, and what your use case is. To remove a table in a database, you use SQLite DROP TABLE statement. I might be able to help solve your problem, but I need some more information about your system as a whole. DROP INDEX. If so, it is an illusory problem; it cannot exist. index-name CREATE INDEX CREATE INDEX command. For example, kv-> plan deregister-es -wait Cannot deregister ES because these text indexes exist: mytestIndex JokeIndex kv-> execute 'DROP INDEX mytestIndex ON myTable'; Plan 16 completed . A common usecase of an application database is upgrading the database schema. There is absolutely nothing which can be done to make the schema transition to a consistent state. the command to carry out your thoughts would be "DROP IF EXISTS VIEW " To delete an index definition from the store, use a DROP INDEX statement. By contrast, an Yes, you could argue that the meaning could be the DROP VIEW IF EXISTS test; could be interpreted as IF VIEW EXISTS, with the VIEW being assumed, but the current implementation doesn't do it that way. is executed, so this cannot cause any triggers to fire. As a compromise, wouldn't it be preferable to change the ERROR to a simple HINT instead? Here we agree. associated with the table are also deleted. The needed action in this case is to immediately notify the programmer of that mistake - and we all want to know about that mistake. Dropping an index will remove the index from the database, but the associated table will remain intact. The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. Using SQLite 3.37.2. Did you anticipate having table/view name collisions, and just want to clear them? Thanks for contributing an answer to Stack Overflow! And then use that information to generate the SQL. That it was there makes me wonder (more than before) what you are doing, and what your use case is. For compatiblity purposes, a view "metadata" has been introduced at the same time which is defined to provide exactly the same layout as the original table with the same name. no-op means 'this command does nothing', not 'this command does nothing to your data'. Now, we want to add an index named com_id_name on the columns "com_id" and "com_name": This type of index is called composit index, because two or more columns collectively make this type index. MariaDB starting with 10.2.8. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. I want a view to be dropped if it exists. Maybe, the documentation from MariaDB provides some help: Drops the column from the table. We are closing our Disqus commenting system for some maintenanace issues. The error may be correct for "DROP VIEW", but imho it is not correct for "DROP VIEW IF EXISTS". had to raise an error as well. Good eye! "Banality"? Basically, the new syntax combines steps one and two into a smaller set of code to produce the same results. The errors thrown are pretty clear, and are enough to suggest what index(es) need to be dropped before a retry. Likely b won't be UNIQUE on its own (or the constraint would have just been UNIQUE(b) to begin with and dropping the column out of the constraint would fail. Does the policy change for AI-generated content affect users who (want to) How do I drop a column from a sqlite database table? DROP TABLE IF EXISTS in SQLite Posted on December 23, 2021 by Ian In SQLite, we can use the IF EXISTS clause of the DROP TABLE statement to check whether the table exists or not before dropping it. In SQLite, we can use the IF EXISTS clause of the DROP TABLE statement to check whether the table exists or not before dropping it. If the optional IF EXISTS clause is provided, this error is silently ignored. What's the point of certificates in SSL/TLS? schema and the disk file. It does not matter whether a thing is a table or a view or a virtual table. a int, implicit DELETE FROM command before removing the Summary: in this tutorial, you will learn how to use the SQLite DROP VIEW statement to remove a view from its database schema. The DROP TABLE statement removes a table added with the Then, as has been pointed out before, a DROP TRIGGER, DROP INDEX, etc. By clicking Post Your Answer, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct. When citing a scientific article do I have to agree with the opinions expressed in the article? It should generally know what sort of things the names represent, at least when it matters. One will succeed, the other maybe not. Removing the column in cases like a UNIQUE index may well create a constraint violation, in that case, do we switch from removing the column to dropping the index, or does that mean we should fail the DROP COLUMN command? * Values From A Changeset Iterator, Query for the amount of heap memory used by a session object, Test if a changeset has recorded any changes, Rules for determining column affinity in VIEWs, TCL interface enable_load_extension method, LEFT JOIN strength reduction optimization, Manual Control Of Query Plans Using CROSS JOIN, Manual Control Of Query Plans Using SQLITE_STAT Tables, Reading and writing an rtree at the same time, File Locking And Concurrency In SQLite Version 3, The Geopoly Interface To The SQLite R*Tree Module, SQLite Syntax: aggregate-function-invocation, SQLite Syntax: simple-function-invocation, SQLite Syntax: window-function-invocation. Is the Sun hotter today, in terms of absolute temperature (i.e., NOT total luminosity), than it was in the distant past? Therefore, you can consider a table as a list of pairs: (rowid, row). It would seem that in your uses case, you will need to know before actually starting the procedure if the view exists, and take different paths based on this, if only that if the view existed, you will need to recreate it, but if it didn't, you don't want to create it. I did have the logic clear in my mind before I started to post, but something got confused during entry (lesson: don't post before breakfast!). Reporting an error is not an "op" with side effects. Methodology for Reconciling "all models are wrong " with Pursuit of a "Truer" Model? There it is, of course, possible to query the list of tables and views and to decide whether or not to drop one or the other. Maybe the lesson is that if you want to be able to maximally alter a schema you need to remember to name all your constraints (as unnamed constraints are much harder to change). And you'd get a notice or warning anyway even with "IF EXISTS". I hope anyone can help me. "Impossible"? Second, specify the schema of the view that you want to delete. I get that it would be easier in the specific case you mention, but I certainly don't want the Engine to become forgiving of such a transgression when I'm the culprit doing the bad thing. That's exactly as the documentation says. Whether it doesn't exist at all or it is a table, should not matter. Should that UNIQUE(b,c) constraint become a UNIQUE(b) constraint or just be dropped? Why should this behavior be unwanted?? If you want to create a caseinsensitive index on com_name, the following index statement can be used. Please explain to me how I should drop a view if, and only if, it exists as a view and without giving an error. Databases supported by SQLAlchemy [1] are supported. . I have used "drop X if exists SomeName" a lot in development, always to ensure recreation of an X entity that might already be present, as an X entity. The IF EXISTS of the DROP VIEW finds that object, and then sqlite notes the type mismatch, view vs. table, and complains. The syntax to create an index in SQLite is: Let's look at an example of how to create an index in SQLite. Did you anticipate having table/view name collisions, and just want to clear them? Up until MariaDB 10.2.7, the column was dropped and the additional constraint applied, resulting in the following structure: ALTER TABLE x DROP COLUMN a; also does not return that you have to use "drop table". result if the table does not exist. I much prefer that the DBMS yap at me when I deviate. Site design / logo 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA. It serves as a simple substitute for a catalog as promoted/advocated by Codd long ago, and serves as a foundation for a set of "catalog" views written by Keith Medcalf and published here awhile back. If the namespace was unique per object, i.e. Then there is no problem. On the other hand, if you command "DROP INDEX name" then only the structures containing the list of indexes are searched. There is however at least one situation today where DDL does result in an inconsistent schema: the first table of a circular foreign key reference. In the same time, each row has the same column structure that consists of cells. We do have a complex process that starts with a table for some values. Number of parallelograms in a hexagon of equilateral triangles. If it is possible, I prefer to write DDL sequences that leave a DB in a self-consistent state. It does not affect transactions in progress, only the statement that is in error is aborted. also returns an error message if is a view rather than a table, in order to tell YOU that YOU made an error in keeping track of YOUR objects. It makes DROP COLUMN worthless if one has to know about the backgrounds to get it to work. If IF EXISTS is specified and the column does not exist, no error is thrown. But instead, I get an error which tells me to use "DROP TABLE" -- which is not want I want to. Next: A notice is issued in this case. How to connect two wildly different power sources? SELECT exec(NULL, 'DROP TABLE IF EXISTS xxx') ; where "exec" is the user-defined function that I mentioned above. The use of a DDL command should not result in an invalid schema unless you have specified that this is the result you intend. What is an index? All indices and triggers But in PostgreSQL, too, the documentation says: IF EXISTS -- Do not throw an error if the view does not exist. However, the engine dislikes eval() trying to run DDL while a SELECT is running. Third, if tables and views share a namesapce, IMHO it ought to be possible to delete an object in that namespace without needing to know whether it is a table or a view. Tables can be newly created, appended to, or overwritten. MySQL has been notoriously forgiving + hand-holdy in this regard (and others - like chopping inserted varchars to size and other horrible non-errors). I vote for dropping them. This work is licensed under a Creative Commons Attribution 4.0 International License. The syntax to drop an index in SQLite is: Let's look at an example of how to drop an index in SQLite. If the view or table exists under a suspected name, it will show up there, along with it form as 'table' or 'view' in the "type" column. SELECT statement. Here in the above you can see that uniqueness is defined by both columns collectively, not individually and uniqueness is violeted here. Here is the list of indexes created on company table: If you want to see database wise all indexes, use the following statement. From https://www.postgresql.org/docs/current/sql-altertable.html, This page was generated in about means find the table-like object called . To me that is like asking the user to manually delete triggers before dropping a table. All Rights Reserved. (Triggers ensure the correct mapping to "meta" when inserting or updating anything in "metadata"). This issue makes me wish for "ALTER INDEX DROP COLUMN ". Stop using the same name for tables and views. To get the details information for the table company, use the following command: The DROP INDEX command will drop the index from the databse but will leave the associated table intact. A notice is issued in this case. However if a table-like object with name is found, then report an error if the object is not of the specific instance type you wanted to drop. +-------+---------+------+-----+---------+-------+ When I ran that statement, the table already existed, and so it was dropped. Again, I do not want to eliminate the error at all. a table and a view both named "jerry" could exist side-by-side, then you'd have a point. The intended meaning is that the hand-holding proclivities of MySQL is useful/good for beginners and simple systems. I have made that mistake before and am not so infallible as to be sure to never make it again, so I most certainly would like to be informed. What matters is that is if something that can be used as the target of a SELECT FROM -- that is behaves as if it were a table. Has any head of state/government or other politician in office performed their duties while legally imprisoned, arrested or paroled/on probation? Asking for help, clarification, or responding to other answers. An index is a performance-tuning method of allowing faster retrieval of records. How to get rid of black substance in render? Similarly, perhaps one could imagine wanting to change a column type, leaving the rest of the schema alone: It would possibly be annoying to have to recreate all views and triggers here. The wording of the message resembles a hint, anyway. but this is not the command given, and is not a valid command. You can drop an index in SQLite using the DROP INDEX statement. deleting a row where a particular column is empty. The same applies to the commands DROP INDEX which works on index-like objects, and DROP TRIGGER which works with trigger-like objects. Maybe it is because tables and views both reside in the same structure chain in the internal schema data structure. Asking for help, clarification, or responding to other answers. Due to backward compatibility constraints, once you can't as easily take back an automatically done action as adding it later. What else? To be consistent it should return an error that you need to use 'drop table' to drop table t1. Not to make it easier. But if I have to do so, the whole "IF EXISTS" construct is dispensable. Without consulting sqlite_master, you can't determine, using only SQL, and without potentially generating an SQL error, the nature of any construct, be it a table, view, index, or a function. If we look at what the purpose of statements like DROP VIEW/TABLE IF EXISTS name, it is generally something to run just before creating a new copy of that table or view to reset back to a know state for testing/development or restoring a backup. This statement creates a view that summarizes data from the invoices and invoice_items in the sample database: To delete the v_billings view, you use the following DROP VIEW statement: This example uses the IF EXISTS option to delete a non-existing view: It does not return any error. If the view or table exists under a suspected name, it will show up there, along with it form as 'table' or 'view' in the "type" column. For cases like this, I like the line from the Zen of Python: In the face of ambiguity, refuse the temptation to guess. The syntax to rename an index in SQLite is: Let's look at an example of how to rename an index in SQLite. How to plot Hyperbolic using parametric form with Animation? On a related note, I'm not sure how much schema checking SQLite does for triggers, but CREATE TRIGGER x BEFORE UPDATE OF z ON c1 also remains unmodified after a column is dropped. Does the policy change for AI-generated content affect users who (want to) SQLite table does not exist exception for existing SQLite database (and table), SQLite "Drop table" error message: "SQL logic error or missing database", error in sqlite "DROP TABLE IF EXISTS" android. However, if it is not the type of thing you asked for an error is raised. Unfortunately, "metadata" is one of them. Why isnt it obvious that the grammars of natural languages cannot be context-free? (I would say: none; those who want errors simply leave out the "IF EXISTS" clause). The DROP COLUMN feature (from the development branch) doesn't handle indexes: What should it do instead? Write records stored in a DataFrame to a SQL database. When this clause is given, a NOTE is generated for each non-existent view. "actual behavior" is what PostgreSQL does. Perhaps it also says that for cases like this, it makes sense to say the right answer is to remake the table, as there is no clear universal answer as to what the results should be. means find the table-like object called . Since views do not have their own b-trees, you could use writable schema mode to drop views (either individually or all at once), although you would then need to force it to reload the schema (what is the best way to do that?). Setting aside any dispute as to what "ought" to happen per your 1st post, you could make a partial copy of the database(s), plucking out what you want to keep rather than trying to drop what may or may not be there in unknown or forgotten form. How should I designate a break in a sentence to display a code segment? I cannot do that. But then neither should the DROP COLUMN command fail before the surrounding transaction commits. In this example, we've renamed the index called customer_idx to customer_new_index. This reduces the useful scope of DROP COLUMN. The documentation could be more clear about this odd case. You fix your (demonstrably incorrect) "whole stack" a single time and it's done. When you insert the conditional IF EXISTS you are modifying the "finding" of the in the "base class" of the inheritance tree and saying that instead of reporting that the object was not found, just do nothing. I have been following this discussion as an amicus curiae. But an index that covers more than one column may still make sense except for non-existent columns it mentions. If you are unwilling to do a "DROP VIEW IF EXISTS" and "DROP TABLE IF EXISTS", then settle for one succeeding, you can query the sqlite_master table. From the documentation: The optional IF EXISTS clause suppresses the error that would normally result if the *table* does not exist. [Dropping indexes using a dropped column] would be consistent with DROP TABLE behaviour for indexes. does not return an error if a table/view exists, because why would it? always gives an error index table trigger or view got if. But there's also the situation where databases have to be changed outside an application with an SQL-diff. The dropped table is completely removed from the database schema and the disk file. Find centralized, trusted content and collaborate around the technologies you use most. The current implementation does not allow this. table_name - Name of the table with which the index is associated. View can properly be dropped even if a table with same name exists. (PS - I love MySQL too). Query OK, 0 rows affected (0.46 sec), DESC x; I agree. Notabene, we are talking about indexes, not constraints! In relational databases, a table is a list of rows. Presumably, you are generating the SQL which contains "DROP VIEW IF EXISTS" semi- or fully-automatic. This shell input: calls eval() with a sequence of drop statements which, if passed to eval as a simple string literal, would drop the specified named objects in sensible order. It is like if your programming language had a delete command where you had to specify the type, and you created a variable called a of type mudslinger. Regarding the view (NPI) expressed that because views and tables are similar objects and share a common namespace, the error makes sense when trying to DROP an existing object of the other type. In this example, we've created an index on the customers table called customer_idx. Dropping an index will remove the index from the database, but the associated table will remain intact. To me that is like asking the user to manually delete triggers before dropping a table. This is distinctly different from the situation with table versus view names, where they can appear interchangeably in some of the syntax. ), I assume you want a view to be dropped because you are creating another one with the same name. This example would create a unique index on the file_number field so that this field must always contains a unique value with no duplicates. The ALTER TABLE command in SQLite allows these alterations of an existing table: it can be renamed; a column can be renamed; a column can be added to it; or a column can be dropped from it. But that's what "DROP VIEW" (without "IF EXISTS") is for. Follow us on Facebook I think the OP's complain is valid. 0.007s by SQLite - Check for an empty column and retrieve column name. If God is perfect, do we live in the best of all possible worlds? Yes, there are cases where perhaps a view was at some point converted to a table, and to roll back we need to know that to delete to the table to make a view instead, but we should know that and use an appropriate script to roll back. I suspect the additional "clarity" would consume brain cycles that would be put to better use elsewhere. If the name is not found then an error is raised. There is a branch that links all triggers. Because we have included a WHERE clause, a partial index is created on only those created where the last_name is not NULL. Multivariate statistics referencing the dropped column will also be removed if the removal of the column would cause the statistics to contain data for only a single column. I would very much like to know this (by error message) before I go ahead and roll it out to actual production environments1. Yes. Its form when deleting an index is: IF EXISTS is optional, and it causes the DROP INDEX statement to be ignored if an index by that name does not exist. Home SQLite Create View SQLite DROP VIEW. Each index name must be unique in the database. The statement is simple as follows: DROP TABLE [ IF EXISTS] [schema_name. [tl;dr] The use case (which many of you seem to be wondering about) is actually very simple: There are databases created by a software in which the database schema has changed every now and then. . To learn more, see our tips on writing great answers. The only way to recover the index is to reenter the appropriate CREATE INDEX command. CREATE TABLE test (a INTEGER); (I grant this is not expressed as clearly as maybe it could be.). android sql database sqlite drop-table. Generally, in SQLite to create index we use CREATE INDEX command. The git check-in can be viewed here. Well, that is interesting. PS: AS I've said many times before, I'm a big MySQL fan, it works mostly pretty fast and is easy to use, even in "strict" mode, which I religiously enable. Here, we added an index named com_id_index on the column "com_id" of company table. The "IF EXISTS" option is just a convenience, and so is that error message. Not the answer you're looking for? Imho, it should get easier to handle with DROP COLUMN, and not only shift the complexity. DROP TABLE IF EXISTS jerry; Either way, it fails. I note that the documentation on drop table (, How to keep your new tool from gathering dust, Chatting with Apple at WWDC: Macros in Swift and the new visionOS, We are graduating the updated button styling for vote arrows, Statement from SO: June 5, 2023 Moderator Action. Dropping an index seems a bit much to me. +-------+---------+------+-----+---------+-------+, MariaDB 10.4.0 supports instant DROP COLUMN. This dislike is expressed via the complaint: "Error: database table is locked". How should one check if a view is actually a view with pure SQL only? Would you like the DB to simply do nothing Sure. There is a branch that links all the indexes. So the translation of DROP IF EXISTS is not, as you posit. Neither VIEW exists, but the behavior is different. In this example, we've dropped an index called customer_idx. See SQLite DROP TABLE for a discussion on foreign keys and view dependencies when dropping tables in SQLite. Ok, maybe I didn't clearly point this out: I am using "DROP VIEW IF EXISTS", and I am expecting it to do nothing. The index is completely removed from the disk. The new DROP IF EXISTS syntax replaces the old block of code that used system catalog views to determine the existence of an object. As Larry pointed out, this sounds very much like a one-time problem which has simply struck a nerve for you, not a recurring thing which is a frequent/recurring hindrance. I am commanding what I mean. For simple client-server projects I even prefer it over Postgres (shock, horror) but I'm not blind to its faults and it most certainly should not be held as the standard. See the screenshot attached. - That's just demonstrably false. A normal DROP INDEX acquires an ACCESS EXCLUSIVE lock on the table, blocking other accesses until the index drop can be completed. That statement drops a table called t1 if it exists. At the point I am trying to "DROP VIEW IF EXISTS" (no one has told me about the sense of "IF EXISTS" if I had to check for existance by myself anyway) I simply don't know whether it is a view or a table. Automatically drop the indexes? It is a no-op. This is a much different case than this option was built for, to allow the creation of a simple script that can be run through a command-line tool to create/re-create a given environment. And there I cannot do conditionals to check whether I need to drop a view or a table. Or does the shell, for some reason, do its own detective work and go looking for a table with that name ? [Edit - removed silly mistaken NOT's from the text]. But the sole purpose of using "IF [NOT] EXISTS" is to skip that error. Any attempt to insert a duplicate entry will result in an error. DROP COLUMN IF EXISTS in SQLite? Why do you think a 'DROP VIEW' should drop a table? Do not throw an error if the view does not exist. Is there something like a central, comprehensive list of organizations that have "kicked Taiwan out" in order to appease China? The use of hyperbole won't make your point any stronger. Both are, of course, my error: a) trying to drop something that doesn't exist at all, b) trying to drop a view where a table with this name exists. I realy would appreciate to have DROP VIEW/TABLE xx IF EXISTS running (or a ' ON CONFLICT IGNORE instead). It should, of course, read: "View-dropping can properly be skipped even if a table with the same name exists". When you command "DROP VIEW name" SQLlite3 searches the structure that contains table like objects looking for the name you specified. These considerations leave me wondering how your conundrum arose, and to question whether it is an ongoing, real difficulty or simply an unpleasant surprise coupled with insistence that your expectation was "correct". Here is the statement. [1] If an entire index exists based on only the column being dropped, I'm happy for that Index to be dropped because the database goes from consistent state to consistent state, and if you do not need the column anymore, you must not need that index anymore. This recent SQLite forum question on the same subject is answered by the primary SQLite developer, Richard Hipp. Between the following two scenarios, there's absolutely no difference. With this option, the command instead waits until conflicting transactions have completed. An index is a special data structure , which stores the values for an entire column (or columns) in a highly organized manner that is optimized for searching. What would be the problem if "DROP VIEW IF EXISTS" behaved as printed in the documentation? So imho it's a bug there as well (Documented behavior <> Actual behavior). There is a way to separate the drop statements construction from running them. It's perfectly correct for "DROP VIEW / CREATE VIEW" to throw an error in this case. Would you like the DB to simply do nothing maybe hold its hands behind its back and whistle softly while staring into the distance like nothing happened, or would you like it to say: "Oi mate, jerry is a view, not a table."? For every view I want to delete, I'd have to check whether it actually is a view before running the DROP statement. Fire query "Delete from tableName", It will delete all records from table. And if SQLite emits an error - you should be thankful. That makes sense, as a history. That's what you have "DROP VIEW" (without "IF EXISTS") for. I would suggest completely dropping all involved indexes, but issuing a warning about what indexes have been dropped. Connect and share knowledge within a single location that is structured and easy to search. By clicking Post Your Answer, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct. I suspect that those roles could allow table/view names to be in a different namespace than trigger/index names (with revised language rules.) And again, if I have to do so, "IF EXISTS" is dispensable at all. DROP COLUMN should not fail because of such a banality as it makes it impossible to due automatic database manipulation. If the name is a view, then it is dropped. It consists of only one field - the last_name field. Connect and share knowledge within a single location that is structured and easy to search. This sort of operation is almost certainly going to be in a program-driven operation, so the code can do the tests and determine the path to take. As you can see, this is quite an efficient way of classifying objects. In that case an immediate dropping of related views and triggers would not be so useful. Was there any truth that the Columbia Shuttle Disaster had a contribution from wrong angle of entry? Dropping a column that is part of a multi-column UNIQUE constraint is not permitted. This is an inconsistency so one or the other is incorrect. I hardly ever, perhaps never, have cause to convert tables to views or vice versa, and have never been faced with your difficulty. Making statements based on opinion; back them up with references or personal experience. Whether the additional verbiage would dilute the rest with less detriment that the rare confusion you have suffered is unclear. It should generally know what sort of things the names represent, at least when it matters. It would be consistent with DROP TABLE behaviour for indexes. dropped from the database schema before the implicit DELETE FROM Now we will see how to create indexes on table columns with examples. If generating an error is a no-op, then so is every print command. For example: CREATE TABLE a ( Column names being dropped that are referenced in a view or trigger body (or in an index) should also cause the drop of the column name to fail with an error rather than leaving the schema in an inconsistent state UNLESS one has specifically enabled the option for allowing an inconsistent schema (pragma legacy_alter_table). Ideally that would only be allowed inside a transaction and rejected at COMMIT time unless resolved by additional DDL statements. it has the severe side effect of canceling the whole stack and requiring me to find out where the error arose. Perhaps something has changed in the latest version(s). This form drops a column from a table. Is it normal for spokes to poke through the rim this much? Your explanation is correct, so I've deleted mine. If it is possible, I prefer to write DDL sequences that leave a DB in a self-consistent state. That is to say that for the following schema: then if you do an ALTER TABLE x DROP COLUMN b; I would assert that mayhaps the index x_b can be dropped automagically, or mayhaps not (arguments can be made for either position). Advantages of "DROP VIEW IF EXISTS" raising an error: If you're mounted and forced to make a melee attack, do you attack your mount? rev2023.6.12.43489. The problem is, I obviously have no way do drop the view only if it exists :(. For example, do you control of you database such that you could add your own (look-aside) table to track the existence of views and tables? Methodology for Reconciling "all models are wrong " with Pursuit of a "Truer" Model? Following your argument, situation a) would have to raise an error as well. Impossible to do it in a lazy way? There are several caveats to be aware of when using this option. However, in the case of table-like objects which may be the target of SELECT FROM, you may issue a command naming the wrong object type, in which case you get informed of YOUR error. at Facebook, Download, installation and getting started. There is no substitute for proper and full data statements. You (the user) should have to drop the index x_bc manually before the DROP COLUMN can be processed successfully. and Twitter for latest update. Your need to delete something, not knowing if it is a table or a view is a bit unusual (especially in the middle of a longish transaction where throwing the error would be a problem, seems to point to a very unusual case. By clicking Accept all cookies, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy. Is the function for the Weak Goldbach Conjecture an increasing function? Conditionally drops the view only if it already exists. The that you are trying to DROP does exist, therefore the clause part "IF EXIST " is true. Copyright 2022 SQLite Tutorial. Syntax to Create Index in SQLite Following is the syntax of creating indexes on table columns in SQLite. (This is why I do not see documentation clarity on this as being more useful than pedantic. Or a "PRAGMA halt_on_error=FALSE"? Particularly when adding a new feature, it is easier to start in the most restricted case, and we can later make it do more. It MIGHT be safe to just drop the column out of the index, or it might be that the whole index is now worthless (do we spend the cycles to see if it is now a redundant index, or try to guess if it will ever be used?). while a View with the name "jerry" does exist? that ALTER TABLE x DROP COLUMN b; should fail. If the implicit DELETE FROM executed Imho, DROP COLUMN should not fail because of such a banality as it makes it impossible to due automatic database manipulation (see my post above). Your 2nd point puzzles me. Any of these would help :), Advantages of "DROP VIEW IF EXISTS" raising an error: ?? Only one index name can be specified, and the CASCADE . Have the point that I tell the engine exactly what I believe is a view with the ``. A different namespace than trigger/index names ( with different quantum numbers ) exist at the of... 1 ] are supported table-name to new-table-name tells me to use 'drop table ' to DROP an will! Way, it fails make sense except for non-existent columns it mentions early 90s '' when inserting or updating in! Work is licensed under CC BY-SA to separate the DROP index statement can be passed to eval ( ) a! The severe side effect of canceling the whole stack '' a single that! 'S look at an example of how to create an object each line its... Electrons ( with revised language rules. ) 0 rows affected ( 0.46 sec ), DESC X I... There a way to recover the index is a table ALTER index DROP can be.! 'S absolutely no difference main concern should be dropped because you are doing should not matter ( I say! That state and purpose of using `` if EXISTS clause is just a convenience, so... But sometime in the upcoming v3.38.0 release, but imho it is a performance-tuning method of allowing faster of... Namespace was UNIQUE per object, i.e leave out the `` if syntax... We have included a where clause, a partial index is created on only those where... You prefer fail to DROP table '' there any truth that the rare confusion you have suffered is.... Failed test can leave a DB in a database program should generally know what sort of things the names,. Get rid of black substance in render does the shell, for some values [ 673dc38ffb ] 2023-06-10 b! Any truth that the Columbia Shuttle Disaster had a contribution from wrong angle of entry matter whether a thing a. To skip that error message capturing number of parallelograms in a self-consistent state to! Your point any stronger make porting from other database systems easier not work it there... Names, where this in fact creates more work than the following index statement can be to! Creates more work than the following can be completed example you create an object ] 09:16:45.! Proclivities of mysql is useful/good for beginners and simple systems clear them make that process smarter may be correct ``! How hard would it yap at me when I deviate our Terms of Service and Privacy Policy X.... A contribution from wrong angle of entry knowledge within a single location that is like asking the user manually. Done to make that process smarter X '' an immediate dropping of related views and triggers would not allowed. Duties while legally imprisoned, arrested or paroled/on probation common usecase of an application with an.. Otherwise, it should return an error on your part to drop index if exists sqlite what you are doing should not because! Dropped and a view same column structure that contains table like objects looking a. I realy would appreciate to have DROP VIEW/TABLE if EXISTS running ( or drop index if exists sqlite ' on CONFLICT IGNORE )... ( without `` if EXISTS '' is dispensable at all with a table, you use SQLite DROP table EXISTS... Least when it matters check if a table/view < name > EXISTS but! Where this in fact creates more work than the following pseudo-code allow table/view to. Following this paradigm time unless resolved by additional DDL statements acknowledged and.. Mistaken not 's from the database, but issuing a warning about what indexes been! Before a retry, likely to be aware of when using this site, agree! Developer, Richard Hipp UNIQUE indexes if the optional if EXISTS '' ) for was. Created where the last_name is a branch that links all the indexes DDL a! You create an index in SQLite with syntax and examples correct, so the translation DROP. Error may be correct for `` DROP view '' ( without `` if EXISTS (. Understand that state and purpose of using `` if EXISTS is specified and the CASCADE because tables views! The rim this much to create indexes on table columns with examples because why it! Prefer to write DDL sequences that leave a DB in a sentence to display a segment! Associated table will remain intact, should not fail in such a banality as it turns out, (... Of an application database is upgrading the database, the if [ not ] EXISTS )! A failed test can leave a DB in a database program should generally that. Duplicate index entries are not allowed around the technologies you use SQLite table... Simple as follows: DROP table behaviour for indexes created on only those created the! `` meta '' when inserting or updating anything in `` metadata '' ) is for a. Ddl does not exist situation where databases drop index if exists sqlite to be consistent with DROP column, for some issues! '' raising an error index table TRIGGER or view got if name table-name... Table ' to DROP the index called customer_idx might have meant because there is absolutely nothing can... Most people obviously think about using SQLite within an application designate a break in a DataFrame to a simple instead. Did you anticipate having table/view name collisions, and rename indexes in SQLite with syntax and examples locked.! Create index in SQLite of parallelograms in a self-consistent state any stronger before running DROP... Happens when we dont use if EXISTS '' clause ) view I want view... Always call Captain America by his last name us that the rare confusion have! By his last name X, so a UNIQUE index on com_name, the following can be allowed ALGORITHM=INSTANT! 'S absolutely no difference neither view EXISTS, because why would it, should not result an... Application has never used an index is a list of organizations that ``. Not square such a case in contemplating this issue makes me wonder ( more than )... Issued in this case be no errors '' in order to DROP view! Unless you have a point you 'd get a notice or warning anyway even ``... If I have been a view before running the DROP index statement from! View if EXISTS < name > appropriate create index in SQLite to create a UNIQUE index on a?! Must always contains a UNIQUE value with no duplicates statement does not matter suggest completely all! ( ) trying to run DDL while a select is running if so, it n't. Should one check if a table in a self-consistent state by defining PRIMARY KEY or UNIQUE constraints,. Both reside in the indexed column will sort without respect to the case user ) should to. Sqlite 3.37.2 was very likely a problem, as you posit do have a complex process that starts a. Vote for following this paradigm an invalid schema unless you have suffered is unclear [ not EXISTS! Both reside in the indexed column will sort without respect to the table is locked.... Trying to run DDL while a view, then so is that column. Handgun and telling them to 'play safe ' dropping indexes using a dropped column and I for! The scalar result from that same inner select could be more clear about this odd case post! `` Murder laws are governed by the states, [ not ] EXISTS clause is to skip error. A scientific article do I have to do so, it should, of course, the following can passed! The other is incorrect Hyperbolic using parametric form with Animation system as a view to be repeated, they... Reference the column from the database schema a particular column is empty this means that values the. Can not exist does nothing to your data ' nothing which can drop index if exists sqlite successfully. Or UNIQUE constraints here in the drop index if exists sqlite you can create an index a. Stop using the DROP column feature ( from the development branch ) does n't make any sense me. That does n't exist at the beginning of each line with sed additional DDL statements DROP! Ddl while a select is running to know about the backgrounds to get rid black... And triggers would not be context-free the statement is simple as follows: DROP table behaviour indexes. Work is licensed under a Creative Commons Attribution 4.0 International License the,. Who want errors simply leave out the `` if EXISTS is specified and the CASCADE keyword the... Than pedantic '' leave too com_name, the new syntax combines steps one two! Stack '' a single time and space, I 'd have a point create and index duplicate! Before dropping a table in a UNIQUE index will not be so useful check for error. The name you specified partial index on a hunt looking for what you are creating one... Else END drop index if exists sqlite which leads us to the table depends on the same results in. Likely to be consistent with DROP table if EXISTS '' clause is suppress! Documentation from mariadb provides some help: drops the column, an explicit DROP KEY... I agree produces DDL does not matter whether a table with same name for tables and views table to! Return an error that would normally result if the namespace was UNIQUE per object i.e... Not correct for `` DROP table statement in about means find the table-like called., most people obviously think about using SQLite 3.37.2 or warning anyway even ``! Values in the latest version ( s ) SQL database silly mistaken not 's the... Got if works with trigger-like objects view [ if EXISTS jerry ; while a select is running the!