I just don't see OPTIONS as important. When I want to copy a table, I am copying a table _with everything_. Options included, by default. Why would I want to have a copy of a table with options different from the base one?
On Mon, Oct 21, 2024 at 3:55 PM Bernardo Botella < conta...@bernardobotella.com> wrote: > Hi Guo, > > +1 for the CONSTRAINTS keyword to be added into the default behavior. > > Bernardo > > On Oct 21, 2024, at 12:01 AM, guo Maxwell <cclive1...@gmail.com> wrote: > > I think the CONSTRAINTS keyword keyword may be in the same situation as > datamask. > Maybe it is better to include constraints into the default behavior of > table copy together with column name, column data type and data mask. > > guo Maxwell <cclive1...@gmail.com> 于2024年10月21日周一 14:56写道： > >> To yifan : >> I don't mind adding the ALL keyword, and it has been updated into CEP. >> >> As all you can see, our original intention was that the grammar would not >> be too complicated, which is what I described in cep >> <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-43++Apache+Cassandra+CREATE+TABLE++LIKE> >> . >> We gave up PG-related grammar, including INCLUDING/EXCLUDING and so on . >> >> guo Maxwell <cclive1...@gmail.com> 于2024年10月21日周一 14:52写道： >> >>> Hi , >>> To sefan : >>> I may want to explain that if there is no OPTION keyword in the CQL >>> statement, then the newly created table will only have the >>> original table's column name 、column type and data mask ,I think this is >>> the most basic choice when copying tables to users. >>> Then we do some addition, we can add original table's table options >>> like compaction strategy/compress strategy、index and so on. >>> >>> Recently, I have also thought about the situation of CONSTRAINTS >>> keyword. I think it is similar to data mask. Agree that it should be >>> included in the basic options of table copy (column name, column data type >>> , column data mask and constraints). >>> >>> Dave Herrington <he...@rhinosource.com> 于2024年10月19日周六 01:15写道： >>> >>>> It seems like a natural extension of the CREATE TABLE statement. >>>> Looking forward to using it in the future. >>>> >>>> -Dave >>>> >>>> On Thu, Oct 17, 2024 at 5:11 PM Štefan Miklošovič < >>>> smikloso...@apache.org> wrote: >>>> >>>>> Right?! Reads like English, the impact on the existing CQL is minimal. >>>>> One LIKE which basically needs to be there and keywords of logical >>>>> "components" which seamlessly integrate with WITH. >>>>> >>>>> I would _not_ use WITH CONSTRAINTS because constraints will be >>>>> inherently part of a table schema. It is not an "option". We can not >>>>> "opt-out" from them. Remember we are copying a table here so if a base one >>>>> has constraints, its copy will have them too. A user can subsequently >>>>> "ALTER" them. >>>>> >>>>> On Thu, Oct 17, 2024 at 5:31 PM Dave Herrington <he...@rhinosource.com> >>>>> wrote: >>>>> >>>>>> Basing it on CREATE TABLE, the BNF definition of the simple >>>>>> implementation would look something like this: >>>>>> >>>>>> create_table_statement::= CREATE TABLE [ IF NOT EXISTS ] table_name >>>>>> LIKE base_table_name >>>>>> [ WITH included_objects ] [ [ AND ] table_options ] >>>>>> table_options::= COMPACT STORAGE [ AND table_options ] >>>>>> | CLUSTERING ORDER BY '(' clustering_order ')' >>>>>> [ AND table_options ] | options >>>>>> clustering_order::= column_name (ASC | DESC) ( ',' column_name (ASC | >>>>>> DESC) )* >>>>>> included_objects::= dependent_objects [ AND dependent_objects ] >>>>>> dependent_objects:= INDEXES | TRIGGERS | CONSTRAINTS | VIEWS >>>>>> >>>>>> >>>>>> CREATE TABLE [ IF NOT EXISTS ] [<keyspace_name>.]<table_name> LIKE >>>>>> [<keyspace_name>.]<base_table_name> >>>>>> [ WITH [ <included_objects > ] >>>>>> [ [ AND ] [ <table_options> ] ] >>>>>> [ [ AND ] CLUSTERING ORDER BY [ <clustering_column_name> (ASC | >>>>>> DESC) ] ] >>>>>> ; >>>>>> >>>>>> Examples: >>>>>> >>>>>> -- Create base table: >>>>>> CREATE TABLE cycling.cyclist_name ( >>>>>> id UUID PRIMARY KEY, >>>>>> lastname text, >>>>>> firstname text >>>>>> ); >>>>>> >>>>>> -- Create an exact copy of the base table, but do not create any >>>>>> dependent objects: >>>>>> CREATE TABLE cycling.cyclist_name2 LIKE cycling.cyclist_name; >>>>>> >>>>>> -- Create an exact copy with all dependent objects (constraints >>>>>> excluded for now): >>>>>> CREATE TABLE cycling.cyclist_name3 LIKE cycling.cyclist_name >>>>>> WITH INDEXES AND TRIGGERS AND VIEWS; >>>>>> >>>>>> -- Create a copy with LCS compaction, a default TTL and all dependent >>>>>> objects except indexes: >>>>>> CREATE TABLE cycling.cyclist_name4 LIKE cycling.cyclist_name >>>>>> WITH TRIGGERS AND VIEWS >>>>>> AND compaction = { 'class' : 'LeveledCompactionStrategy' } >>>>>> AND default_time_to_live = 86400; >>>>>> >>>>>> >>>>>> >>>>>> This seems pretty clean & straightforward. >>>>>> >>>>>> -Dave >>>>>> >>>>>> On Thu, Oct 17, 2024 at 4:05 PM Dave Herrington < >>>>>> he...@rhinosource.com> wrote: >>>>>> >>>>>>> This simple approach resonates with me. I think the Cassandra doc >>>>>>> uses "INDEXES" as the plural for index, i.e.: >>>>>>> https://cassandra.apache.org/doc/stable/cassandra/cql/indexes.html >>>>>>> >>>>>>> -Dave >>>>>>> >>>>>>> On Thu, Oct 17, 2024 at 2:39 PM Štefan Miklošovič < >>>>>>> smikloso...@apache.org> wrote: >>>>>>> >>>>>>>> Well we could do something like: >>>>>>>> >>>>>>>> CREATE TABLE ks.tb_copy LIKE ks.tb WITH INDICES AND TRIGGERS AND >>>>>>>> compaction = {'class': '.... } AND ... >>>>>>>> >>>>>>>> but I can admit it might be seen as an overreach and I am not sure >>>>>>>> at all how it would look like in the implementation because we would >>>>>>>> need >>>>>>>> to distinguish WITH INDICES from table options. >>>>>>>> >>>>>>>> I would >>>>>>>> >>>>>>>> 1. +0 on ALL. - we don't need this. If we have just INDICES, >>>>>>>> TRIGGERS, VIEWS at this point, I don't think enumerating it all is >>>>>>>> too much >>>>>>>> to ask. This is just an implementation detail and if we find it >>>>>>>> necessary >>>>>>>> we can add it later. If you feel strongly about this then add that >>>>>>>> but it >>>>>>>> is not absolutely necessary. >>>>>>>> 2. omit OPTIONS - aren't all options copied by default? That is >>>>>>>> the goal of the CEP, no? We might just use normal CQL while >>>>>>>> overriding from the base table >>>>>>>> 3. mix keywords like TRIGGERS / INDICES / CONSTRAINTS into >>>>>>>> normal table creation statement >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Oct 17, 2024 at 3:20 PM Yifan Cai <yc25c...@gmail.com> >>>>>>>> wrote: >>>>>>>> >>>>>>>>> I would second Štefan's option for functionality simplicity. It >>>>>>>>> seems to be unnecessary to have the keywords for both inclusion and >>>>>>>>> exclusion in the CEP. If needed, the exclusion (WITHOUT) can be >>>>>>>>> introduced >>>>>>>>> later. It would still be backward compatible. >>>>>>>>> >>>>>>>>> Regarding "CREATE TABLE ks.tb_copy LIKE ks.tb WITH compaction = >>>>>>>>> {'class': '.... } AND ... ", I think it only overrides the table >>>>>>>>> options. >>>>>>>>> The CEP suggests the coarse-grained keyword for each category like >>>>>>>>> table >>>>>>>>> options, indexes, etc. The functionality provided is not identical. >>>>>>>>> >>>>>>>>> I understand that the suggestions are to make operators' life >>>>>>>>> easier by achieving table creation in a single statement. What is >>>>>>>>> being >>>>>>>>> proposed in the CEP seems to be at a good balance point. Operators can >>>>>>>>> alter the table options if needed in the follow-up ALTER table >>>>>>>>> statement. >>>>>>>>> >>>>>>>>> - Yifan >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thu, Oct 17, 2024 at 1:41 PM Štefan Miklošovič < >>>>>>>>> smikloso...@apache.org> wrote: >>>>>>>>> >>>>>>>>>> I think we are starting to complicate it. For me the most >>>>>>>>>> important question is who is actually this feature for? If people >>>>>>>>>> want to >>>>>>>>>> just prototype something fast or they just want to have "the same >>>>>>>>>> table >>>>>>>>>> just under a different name", I think that is going to be used in >>>>>>>>>> 99% of >>>>>>>>>> cases. >>>>>>>>>> >>>>>>>>>> My assumption of using WITH which I think I proposed first (4th >>>>>>>>>> post in this thread) was to just blindly copy the most important >>>>>>>>>> "parts" >>>>>>>>>> logically related to a table, be it indices, materialized views, or >>>>>>>>>> triggers and enable / disable them as we wish. If no "WITH" is used, >>>>>>>>>> then >>>>>>>>>> we just get a table with nothing else. "WITH" will opt-in into that. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Seeing us contemplating using "INCLUDING" and "EXCLUDING" on >>>>>>>>>> individual options makes me sad a little bit. I think we are >>>>>>>>>> over-engineering this. I just don't see a reasonable use-case where >>>>>>>>>> users >>>>>>>>>> would need to cherry-pick what they want and what not. Isn't that >>>>>>>>>> just too >>>>>>>>>> complicated? If a table being copied drifts away too much from the >>>>>>>>>> original >>>>>>>>>> one then users would be better off with creating a brand new table >>>>>>>>>> with CQL >>>>>>>>>> as they are used to, not dealing with "copying" at all. More we >>>>>>>>>> drift from >>>>>>>>>> what the original table was like, the less useful this feature is. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Wed, Oct 16, 2024 at 10:03 PM Dave Herrington < >>>>>>>>>> he...@rhinosource.com> wrote: >>>>>>>>>> >>>>>>>>>>> Sorry that I overlooked the definition of the default in the >>>>>>>>>>> CEP. I did look for it but I didn’t see it. >>>>>>>>>>> >>>>>>>>>>> I think the default behavior you explained makes perfect sense & >>>>>>>>>>> what one would expect. >>>>>>>>>>> >>>>>>>>>>> I like the flexibility of INCLUDING and EXCLUDING that you are >>>>>>>>>>> considering. >>>>>>>>>>> >>>>>>>>>>> Would it make sense to use WITH for table options, which would >>>>>>>>>>> make it easy (and less confusing IMHO) to override the defaults >>>>>>>>>>> from the >>>>>>>>>>> source table, then use INCLUDING/EXCLUDING for all non-table >>>>>>>>>>> options such >>>>>>>>>>> as constraints and indices? >>>>>>>>>>> >>>>>>>>>>> It seems this would be easier to document as well, as it could >>>>>>>>>>> just point to the CREATE TABLE doc for the options, rather than >>>>>>>>>>> trying to >>>>>>>>>>> explain a bunch of keywords that map to table options. >>>>>>>>>>> >>>>>>>>>>> -Dave >>>>>>>>>>> >>>>>>>>>>> David A. Herrington II >>>>>>>>>>> President and Chief Engineer >>>>>>>>>>> RhinoSource, Inc. >>>>>>>>>>> >>>>>>>>>>> *Data Lake Architecture, Cloud Computing and Advanced Analytics.* >>>>>>>>>>> >>>>>>>>>>> www.rhinosource.com >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Wed, Oct 16, 2024 at 7:57 PM guo Maxwell < >>>>>>>>>>> cclive1...@gmail.com> wrote: >>>>>>>>>>> >>>>>>>>>>>> To yifan : >>>>>>>>>>>> At the beginning of the period, I also thought about adding the >>>>>>>>>>>> keyword ALL, refer to pg >>>>>>>>>>>> <https://www.postgresql.org/docs/current/sql-createtable.html> , >>>>>>>>>>>> but I give up when writing cep as I find that there may be not so >>>>>>>>>>>> many >>>>>>>>>>>> properties (only three) to copy for C* and >>>>>>>>>>>> It is possible to decide what is needed and what is not in a >>>>>>>>>>>> very simple cql, as our ALL is only three properties here. I want >>>>>>>>>>>> to keep >>>>>>>>>>>> it as simple as possible (based on the advice given by Benjamin), >>>>>>>>>>>> So I >>>>>>>>>>>> grouped >>>>>>>>>>>> the properties of the table into one category and expressed it >>>>>>>>>>>> with OPTION keyword. >>>>>>>>>>>> >>>>>>>>>>>> But if we are going to split the first keyword OPTION to >>>>>>>>>>>> COMPRESSION 、COMPACTION、COMMENT and so on. I am +1 on adding ALL >>>>>>>>>>>> back as >>>>>>>>>>>> the properties are so many and it is simple to use ALL instead of >>>>>>>>>>>> list all properties. Besides I may change my keyword WITH to >>>>>>>>>>>> INCLUDING and adding another keyword EXCLUDING to flexibly copy >>>>>>>>>>>> table >>>>>>>>>>>> properties through simple sql statements, like using 1 not 2 >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> 1. CREATE TABLE newTb like oldTb INCLUDING ALL EXCLUDING >>>>>>>>>>>> INDEXES AND COMMENTS. >>>>>>>>>>>> 2. CREATE TABLE newTb like oldTb INCLUDING COMPRESSION >>>>>>>>>>>> CONSTRAINTS GENERATED IDENTITY STATISTICS STORAGE >>>>>>>>>>>> >>>>>>>>>>>> Conclusion: If there may be more keywords to consider in the >>>>>>>>>>>> future, such as more than 4 , I am +1 on adding ALL back . >>>>>>>>>>>> >>>>>>>>>>>> To Dave : >>>>>>>>>>>> Default behavior is only copy column name, data type ,data >>>>>>>>>>>> mask , you can see more detail from CEP-43 >>>>>>>>>>>> <https://cwiki.apache.org/confluence/display/CASSANDRA/CEP-43++Apache+Cassandra+CREATE+TABLE++LIKE> >>>>>>>>>>>> . >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>>>>> Patrick McFadin <pmcfa...@gmail.com> 于2024年10月17日周四 06:43写道： >>>>>>>>>>>> >>>>>>>>>>>>> +1 That makes much more sense in my experience. >>>>>>>>>>>>> >>>>>>>>>>>>> On Wed, Oct 16, 2024 at 12:12 PM Dave Herrington < >>>>>>>>>>>>> he...@rhinosource.com> wrote: >>>>>>>>>>>>> >>>>>>>>>>>>>> I'm coming at this with both a deep ANSI SQL background as >>>>>>>>>>>>>> well as CQL background. >>>>>>>>>>>>>> >>>>>>>>>>>>>> Defining the default behavior is the starting point. What >>>>>>>>>>>>>> gets copied if we do "CREATE TABLE new_table LIKE >>>>>>>>>>>>>> original_table;" without >>>>>>>>>>>>>> a WITH clause? >>>>>>>>>>>>>> >>>>>>>>>>>>>> Then, you build on that with the specific WITH options. WITH >>>>>>>>>>>>>> ALL catches everything. >>>>>>>>>>>>>> >>>>>>>>>>>>>> -Dave >>>>>>>>>>>>>> >>>>>>>>>>>>>> On Wed, Oct 16, 2024 at 11:16 AM Yifan Cai < >>>>>>>>>>>>>> yc25c...@gmail.com> wrote: >>>>>>>>>>>>>> >>>>>>>>>>>>>>> "WITH ALL" seems to be a natural addition to the directives. >>>>>>>>>>>>>>> What do you think about adding the fifth keyword ALL to retain >>>>>>>>>>>>>>> all fields >>>>>>>>>>>>>>> of the table schema? >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> For instance, CREATE TABLE new_table LIKE original_table >>>>>>>>>>>>>>> WITH ALL, it replicates options, indexes, triggers, constraints >>>>>>>>>>>>>>> and any >>>>>>>>>>>>>>> applicable kinds that are introduced in the future. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> - Yifan >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> On Wed, Oct 16, 2024 at 7:46 AM guo Maxwell < >>>>>>>>>>>>>>> cclive1...@gmail.com> wrote: >>>>>>>>>>>>>>> >>>>>>>>>>>>>>>> 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！ >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> -- >>>>>>>>>>>>>> -Dave >>>>>>>>>>>>>> >>>>>>>>>>>>>> David A. Herrington II >>>>>>>>>>>>>> President and Chief Engineer >>>>>>>>>>>>>> RhinoSource, Inc. >>>>>>>>>>>>>> >>>>>>>>>>>>>> *Data Lake Architecture, Cloud Computing and Advanced >>>>>>>>>>>>>> Analytics.* >>>>>>>>>>>>>> >>>>>>>>>>>>>> www.rhinosource.com >>>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>> >>>>>>> -- >>>>>>> -Dave >>>>>>> >>>>>>> David A. Herrington II >>>>>>> President and Chief Engineer >>>>>>> RhinoSource, Inc. >>>>>>> >>>>>>> *Data Lake Architecture, Cloud Computing and Advanced Analytics.* >>>>>>> >>>>>>> www.rhinosource.com >>>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> -Dave >>>>>> >>>>>> David A. Herrington II >>>>>> President and Chief Engineer >>>>>> RhinoSource, Inc. >>>>>> >>>>>> *Data Lake Architecture, Cloud Computing and Advanced Analytics.* >>>>>> >>>>>> www.rhinosource.com >>>>>> >>>>> >>>> >>>> -- >>>> -Dave >>>> >>>> David A. Herrington II >>>> President and Chief Engineer >>>> RhinoSource, Inc. >>>> >>>> *Data Lake Architecture, Cloud Computing and Advanced Analytics.* >>>> >>>> www.rhinosource.com >>>> >>> >
