Disscussed with Bernardo on slack，and +1 with his advice on adding a fourth keyword.
The keyword would be CONSTRAINTS ， any more suggestion ？ guo Maxwell <cclive1...@gmail.com>于2024年10月16日 周三上午9:55写道： > Hi yifan, > Thanks for bringing this up. The SELECT permission on the original table > is needed. Mysql and PG all have mentioned this, and I also specifically > noticed this in my code. > > I probably missed this in the cep documentation. 😅 > > Yifan Cai <yc25c...@gmail.com> 于2024年10月16日周三 07:46写道： > >> Thanks for creating the CEP! I think it is missing Bernardo's comment on >> "the need for read permissions on the source table". >> >> CreateTableStatement does not check the permissions outside of the >> enclosing keyspace. Having the SELECT permission on the original table is a >> requirement for CREATE TABLE LIKE. >> >> - Yifan >> >> On Sun, Sep 29, 2024 at 11:01 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> 于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> >>>> 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>于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> 于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> >>>>>>> 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> 于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>于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> >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> Thank you for your replies, I will prepare a CEP later. >>>>>>>>>>> >>>>>>>>>>> Patrick McFadin <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> >>>>>>>>>>>> wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Given the fairly large surface area for this, i think it >>>>>>>>>>>>> should be a CEP. >>>>>>>>>>>>> >>>>>>>>>>>>> — >>>>>>>>>>>>> Jon Haddad >>>>>>>>>>>>> Rustyrazorblade Consulting >>>>>>>>>>>>> rustyrazorblade.com >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> On Mon, Aug 19, 2024 at 10:44 AM Bernardo Botella < >>>>>>>>>>>>> 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> 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> 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> 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！ >>>>>>>>>>>>>>>> >>>>>>>>>>>>>>> >>>>>>>>>>>>>>
