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