Hi Guo, Do you think it would make sense to add a fourth keyword to add after the WITH for Constraints? (See CEP-42)
Copying a table without the defined constraints may be useful. Bernardo > On Sep 29, 2024, at 11:00 PM, guo Maxwell <cclive1...@gmail.com> wrote: > > Hello, everyone , > I have finished the doc for CEP-43 for CREATE_TABLE_LIKE > <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-43++Apache+Cassandra+CREATE+TABLE++LIKE> > as said before, looking forward to your suggestions. > > Štefan Miklošovič <smikloso...@apache.org <mailto:smikloso...@apache.org>> > 于2024年9月25日周三 03:51写道： >> I am sorry I do not follow what you mean, maybe an example would help. >> >> On Tue, Sep 24, 2024 at 6:18 PM guo Maxwell <cclive1...@gmail.com >> <mailto:cclive1...@gmail.com>> wrote: >>> >>> If there are multiple schema information changes in one ddl statement, will >>> there be schema conflicts in extreme cases? >>> For example, our statement contains both table creation and index creation. >>> >>> guo Maxwell <cclive1...@gmail.com <mailto:cclive1...@gmail.com>>于2024年9月24日 >>> 周二下午8:12写道： >>>> +1 on splitting this task and adding the ability to copy tables through >>>> different keyspaces in the future. >>>> >>>> Štefan Miklošovič <smikloso...@apache.org <mailto:smikloso...@apache.org>> >>>> 于2024年9月23日周一 22:05写道： >>>>> If we have this table >>>>> >>>>> CREATE TABLE ks.tb2 ( >>>>> id int PRIMARY KEY, >>>>> name text >>>>> ); >>>>> >>>>> I can either specify name of an index on my own like this: >>>>> >>>>> CREATE INDEX name_index ON ks.tb2 (name) ; >>>>> >>>>> or I can let Cassandra to figure that name on its own: >>>>> >>>>> CREATE INDEX ON ks.tb2 (name) ; >>>>> >>>>> in that case it will name that index "tb2_name_idx". >>>>> >>>>> Hence, I would expect that when we do >>>>> >>>>> ALTER TABLE ks.to_copy LIKE ks.tb2 WITH INDICES; >>>>> >>>>> Then ks.to_copy table will have an index which is called >>>>> "to_copy_name_idx" without me doing anything. >>>>> >>>>> For types, we do not need to do anything when we deal with the same >>>>> keyspace. For simplicity, I mentioned that we might deal with the same >>>>> keyspace scenario only for now and iterate on that in the future. >>>>> >>>>> On Mon, Sep 23, 2024 at 8:53 AM guo Maxwell <cclive1...@gmail.com >>>>> <mailto:cclive1...@gmail.com>> wrote: >>>>>> Hello everyone, >>>>>> >>>>>> Cep is being written, and I encountered some problems during the >>>>>> process. I would like to discuss them with you. If you read the >>>>>> description of this CASSANDRA-7662 >>>>>> <https://issues.apache.org/jira/browse/CASSANDRA-7662>, we will find >>>>>> that initially the original creator of this jira did not intend to >>>>>> implement structural copying of indexes, views, and triggers only the >>>>>> column and its data type. >>>>>> >>>>>> However, after investigating some db related syntax and function >>>>>> implementation, I found that it may be necessary for us to provide some >>>>>> rich syntax to support the replication of indexes, views, etc. >>>>>> >>>>>> In order to support selective copy of the basic structure of the table >>>>>> (columns and types), table options, table-related indexes, views, >>>>>> triggers, etc. We need some new syntax, it seems that the syntax of pg >>>>>> is relatively comprehensive, it use the keyword INCLUDING/EXCLUDING to >>>>>> flexibly control the removal and retention of indexes, table >>>>>> information, etc. see pg create table like >>>>>> <https://www.postgresql.org/docs/8.1/sql-createtable.html> , the new >>>>>> created index name is different from the original table's index name , >>>>>> seenewly copied index names are different from original >>>>>> <https://github.com/postgres/postgres/blob/master/doc/src/sgml/ref/create_table.sgml#L749> >>>>>> , the name is based on some rule. >>>>>> Mysql is relatively simple and copies columns and indexes by default. >>>>>> see mysql create table like >>>>>> <https://dev.mysql.com/doc/refman/8.4/en/create-table-like.html> and the >>>>>> newly created index name is the same with the original table's index >>>>>> name. >>>>>> >>>>>> So for Casandra, I hope it can also support the information copy of >>>>>> index and even view/trigger. And I also hope to be able to flexibly >>>>>> decide which information is copied like pg. >>>>>> >>>>>> Besides, I think the copy can happen between different keyspaces. And >>>>>> UDT needs to be taken into account. >>>>>> >>>>>> But as we know the index/view/trigger name are all under keyspace level, >>>>>> so it seems that the newly created index name (or view name/ trigger >>>>>> name) must be different from the original tables' ,otherwise names >>>>>> would clash . >>>>>> >>>>>> So regarding the above problem, one idea I have is that for newly >>>>>> created types, indexes and views under different keyspaces and the same >>>>>> keyspace, we first generate random names for them, and then we can add >>>>>> the ability of modifying the names(for types/indexes/views/triggers) so >>>>>> that users can manually change the names. >>>>>> >>>>>> >>>>>> guo Maxwell <cclive1...@gmail.com <mailto:cclive1...@gmail.com>> >>>>>> 于2024年9月20日周五 08:06写道： >>>>>>> No，I think still need some discuss on grammar detail after I finish the >>>>>>> first version >>>>>>> >>>>>>> Patrick McFadin <pmcfa...@gmail.com >>>>>>> <mailto:pmcfa...@gmail.com>>于2024年9月20日 周五上午2:24写道： >>>>>>>> Is this CEP ready for a VOTE thread? >>>>>>>> >>>>>>>> On Sat, Aug 24, 2024 at 8:56 PM guo Maxwell <cclive1...@gmail.com >>>>>>>> <mailto:cclive1...@gmail.com>> wrote: >>>>>>>>> Thank you for your replies, I will prepare a CEP later. >>>>>>>>> >>>>>>>>> Patrick McFadin <pmcfa...@gmail.com <mailto:pmcfa...@gmail.com>> >>>>>>>>> 于2024年8月20日周二 02:11写道： >>>>>>>>>> +1 This is a CEP >>>>>>>>>> >>>>>>>>>> On Mon, Aug 19, 2024 at 10:50 AM Jon Haddad <j...@jonhaddad.com >>>>>>>>>> <mailto:j...@jonhaddad.com>> wrote: >>>>>>>>>>> Given the fairly large surface area for this, i think it should be >>>>>>>>>>> a CEP. >>>>>>>>>>> >>>>>>>>>>> — >>>>>>>>>>> Jon Haddad >>>>>>>>>>> Rustyrazorblade Consulting >>>>>>>>>>> rustyrazorblade.com <http://rustyrazorblade.com/> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Mon, Aug 19, 2024 at 10:44 AM Bernardo Botella >>>>>>>>>>> <conta...@bernardobotella.com >>>>>>>>>>> <mailto:conta...@bernardobotella.com>> wrote: >>>>>>>>>>>> Definitely a nice addition to CQL. >>>>>>>>>>>> >>>>>>>>>>>> Looking for inspiration at how Postgres and Mysql do that may also >>>>>>>>>>>> help with the final design (I like the WITH proposed by Stefan, >>>>>>>>>>>> but I would definitely take a look at the INCLUDING keyword >>>>>>>>>>>> proposed by Postgres). >>>>>>>>>>>> https://www.postgresql.org/docs/current/sql-createtable.html >>>>>>>>>>>> https://dev.mysql.com/doc/refman/8.4/en/create-table-like.html >>>>>>>>>>>> >>>>>>>>>>>> On top of that, and as part of the interesting questions, I would >>>>>>>>>>>> like to add the permissions to the mix. Both the question about >>>>>>>>>>>> copying them over (with a WITH keyword probably), and the need for >>>>>>>>>>>> read permissions on the source table as well. >>>>>>>>>>>> >>>>>>>>>>>> Bernardo >>>>>>>>>>>> >>>>>>>>>>>>> On Aug 19, 2024, at 10:01 AM, Štefan Miklošovič >>>>>>>>>>>>> <smikloso...@apache.org <mailto:smikloso...@apache.org>> wrote: >>>>>>>>>>>>> >>>>>>>>>>>>> BTW this would be cool to do as well: >>>>>>>>>>>>> >>>>>>>>>>>>> ALTER TABLE ks.to_copy LIKE ks.tb WITH INDICES; >>>>>>>>>>>>> >>>>>>>>>>>>> This would mean that if we create a copy of a table, later we can >>>>>>>>>>>>> decide that we need indices too, so we might "enrich" that table >>>>>>>>>>>>> with indices from the old one without necessarily explicitly >>>>>>>>>>>>> re-creating them on that new table. >>>>>>>>>>>>> >>>>>>>>>>>>> On Mon, Aug 19, 2024 at 6:55 PM Štefan Miklošovič >>>>>>>>>>>>> <smikloso...@apache.org <mailto:smikloso...@apache.org>> wrote: >>>>>>>>>>>>>> I think this is an interesting idea worth exploring. I >>>>>>>>>>>>>> definitely agree with Benjamin who raised important questions >>>>>>>>>>>>>> which needs to be answered first. Also, what about triggers? >>>>>>>>>>>>>> >>>>>>>>>>>>>> It might be rather "easy" to come up with something simple but >>>>>>>>>>>>>> it should be a comprehensive solution with predictable behavior >>>>>>>>>>>>>> we all agree on. >>>>>>>>>>>>>> >>>>>>>>>>>>>> If a keyspace of a new table does not exist we would need to >>>>>>>>>>>>>> create that one too before. For the simplicity, I would just >>>>>>>>>>>>>> make it a must to create it on same keyspace. We might iterate >>>>>>>>>>>>>> on that in the future. >>>>>>>>>>>>>> >>>>>>>>>>>>>> UDTs are created per keyspace so there is nothing to re-create. >>>>>>>>>>>>>> We just need to reference it from a new table, right? >>>>>>>>>>>>>> >>>>>>>>>>>>>> Indexes and MVs are interesting but in theory they might be >>>>>>>>>>>>>> re-created too. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Would it be appropriate to use something like this? >>>>>>>>>>>>>> >>>>>>>>>>>>>> CREATE TABLE ks.tb_copy LIKE ks.tb WITH INDEXES AND VIEWS AND >>>>>>>>>>>>>> TRIGGERS .... >>>>>>>>>>>>>> >>>>>>>>>>>>>> Without "WITH" it would just copy a table with nothing else. >>>>>>>>>>>>>> >>>>>>>>>>>>>> On Mon, Aug 19, 2024 at 6:10 PM guo Maxwell >>>>>>>>>>>>>> <cclive1...@gmail.com <mailto:cclive1...@gmail.com>> wrote: >>>>>>>>>>>>>>> Hello， everyone： >>>>>>>>>>>>>>> As Jira CASSANDRA-7662 >>>>>>>>>>>>>>> <https://issues.apache.org/jira/browse/CASSANDRA-7662> has >>>>>>>>>>>>>>> described , we would like to introduce a new grammer " CREATE >>>>>>>>>>>>>>> TABLE LIKE " ,which simplifies creating new tables duplicating >>>>>>>>>>>>>>> the existing ones . >>>>>>>>>>>>>>> The format may be like : CREATE TABLE <new_table> LIKE >>>>>>>>>>>>>>> <old_table> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Before I implement this function, do you have any suggestions >>>>>>>>>>>>>>> on this? >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Looking forward to your reply！ >>>>>>>>>>>>
