+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！ >>>>>>>>>> >>>>>>>>> >>>>>>>>
