duplicate primary key error on reflected model

[keinagae]

Hi i have a reflected model

class Campaigns(edgy.ReflectModel):
    uuid = edgy.UUIDField(null=False, primary_key=True,default=uuid.uuid4)
    campaign_id = edgy.BigIntegerField(null=True)
    name = edgy.CharField(max_length=255, null=True)
    campaign_type = edgy.CharField(max_length=10, null=True)
    start_date = edgy.DateField(null=True)
    end_date = edgy.DateField(null=True)
    state = edgy.CharField(max_length=50, null=True)
    targeting_type = edgy.CharField(max_length=10, null=True)
    daily_budget = edgy.DecimalField(max_digits=60, decimal_places=2, null=True)
    total_budget = edgy.DecimalField(max_digits=50, decimal_places=2, null=True)
    bidding_strategy = edgy.CharField(max_length=50, null=True)
    bidding_automatic_bid_desired_acos_percentage = edgy.DecimalField(max_digits=15, decimal_places=2, null=True)
    bidding_default_bid = edgy.DecimalField(max_digits=15, decimal_places=2, null=True)
    countries = edgy.JSONField(null=True)
    campaign_group_uuid = edgy.UUIDField(null=True)
    process_status_id = edgy.BigIntegerField(null=True)
    automation = edgy.BooleanField(null=True)
    rule_uuid = edgy.UUIDField(null=True)
    seller_account_id = edgy.BigIntegerField(null=True)
    # rule_template_id = edgy.ForeignKey(null=True, to='Ruletemplates', on_update=None, on_delete=None, related_name='rule_template_id_ruletemplates_set')
    hiddens = edgy.CharField(max_length=255, null=True)
    deleted_at = edgy.DateTimeField(null=True)
    created_at = edgy.DateTimeField(null=True)
    updated_at = edgy.DateTimeField(null=True)

    class Meta:
        registry = registry
        tablename = 'campaigns'
        unique_together = [UniqueConstraint(fields=['process_status_id'], name='campaigns_process_status_id_unique'), UniqueConstraint(fields=['campaign_id'], name='campaigns_campaign_id_unique')]
        indexes = [Index(suffix='idx', name='campaigns_end_date_index', fields=['end_date']), Index(suffix='idx', name='campaigns_campaign_group_uuid_index', fields=['campaign_group_uuid']), Index(suffix='idx', name='campaigns_start_date_index', fields=['start_date']), Index(suffix='idx', name='campaigns_rule_uuid_index', fields=['rule_uuid']), Index(suffix='idx', name='campaigns_state_index', fields=['state']), Index(suffix='idx', name='campaigns_campaign_type_index', fields=['campaign_type']), Index(suffix='idx', name='campaigns_seller_account_id_index', fields=['seller_account_id'])]

but when i run it i am getting edgy.exceptions.ImproperlyConfigured: Cannot create model Campaigns with multiple primary keys.
any idea what i might be doing wrong

[keinagae]

simply using it like this

campaigns = await Campaigns.query.all()
print(campaigns)

[tarsil]

@keinagae yes, this is being changed as we speak as we will be starting to support multiple primary keys in future releases. Is this from the inspectdb?

If you update uuid = edgy.UUIDField(null=False, primary_key=True,default=uuid.uuid4) to be uuid = edgy.UUIDField(null=False, ,default=uuid.uuid4) it should work.

Since its a reflected model, means it won't generate migrations for it and it will apply the defaults from the existing database.

[keinagae]

yes from inspectdb

[keinagae]

not sure what is going on but

await Campaigns.query.using("2").all()

giving me
edgy.exceptions.ImproperlyConfigured: Table with the name campaigns does not exist.
i have this schema under data base with table as well

[keinagae]

i tried manually running query database.fetch_all('select * from "2"."campaigns"') and it does work. Making sure that connection was successful 😅

[keinagae]

just for more reference this is actual error
https://docs.sqlalchemy.org/en/20/errors.html#error-xd2s

[tarsil]

@keinagae we will be addressing this soon and come back to you 🙂

[tarsil]

@keinagae I think I found the problem. When the inspect DB (which btw, its a reflect model) runs for a given schema, the tablename is actually not pointing to the right place.

Based on the newly generated inspect, it should be tablename = "2.campains" and it didn't generate like that, which makes it pointing to the public schema.

Can you add that 2. in the tablename and give it a try, please?

The fix was added now.

[keinagae]

edgy.exceptions.ImproperlyConfigured: Table with the name 2.campaigns does not exist.

[tarsil]

@keinagae In theory, the new release of Edgy done today (0.11.0) should cover what we talked here. Give it a try and let me know, please.

EDIT: The multiple primary keys is not yet available in this release, it will come in later on.

[keinagae]

hmm i think i am missing these

from edgy.contrib.multi_tenancy import TenantModel, TenantRegistry
from edgy.contrib.multi_tenancy.models import TenantMixin

not how to use it for reflected models though

[keinagae]

also do i need to add

class User(TenantModel):
    name: str = fields.CharField(max_length=255)

    class Meta:
        registry = models
        is_tenant = True

is_tenant = True as well?

[keinagae]

now

database = edgy.Database('postgresql+asyncpg://<user>:<pwd>@localhost:5432/marketplace')

registry = TenantRegistry(database=database)

class Campaigns(TenantModel):
    uuid = edgy.UUIDField(null=False,default=uuid.uuid4)
    campaign_id = edgy.BigIntegerField(null=True,primary_key=False)
    name = edgy.CharField(max_length=255, null=True)
    campaign_type = edgy.CharField(max_length=10, null=True)
    start_date = edgy.DateField(null=True)
    end_date = edgy.DateField(null=True)
    state = edgy.CharField(max_length=50, null=True)
    targeting_type = edgy.CharField(max_length=10, null=True)
    daily_budget = edgy.DecimalField(max_digits=60, decimal_places=2, null=True)
    total_budget = edgy.DecimalField(max_digits=50, decimal_places=2, null=True)
    bidding_strategy = edgy.CharField(max_length=50, null=True)
    bidding_automatic_bid_desired_acos_percentage = edgy.DecimalField(max_digits=15, decimal_places=2, null=True)
    bidding_default_bid = edgy.DecimalField(max_digits=15, decimal_places=2, null=True)
    countries = edgy.JSONField(null=True)
    campaign_group_uuid = edgy.UUIDField(null=True,primary_key=False)
    process_status_id = edgy.BigIntegerField(null=True,primary_key=False)
    automation = edgy.BooleanField(null=True)
    rule_uuid = edgy.UUIDField(null=True,primary_key=False)
    seller_account_id = edgy.BigIntegerField(null=True,primary_key=False)
    # rule_template_id = edgy.ForeignKey(null=True, to='Ruletemplates', on_update=None, on_delete=None, related_name='rule_template_id_ruletemplates_set')
    hiddens = edgy.CharField(max_length=255, null=True)
    deleted_at = edgy.DateTimeField(null=True)
    created_at = edgy.DateTimeField(null=True)
    updated_at = edgy.DateTimeField(null=True)

    class Meta:
        registry = registry
        tablename = 'campaigns'
        unique_together = [UniqueConstraint(fields=['process_status_id'], name='campaigns_process_status_id_unique'), UniqueConstraint(fields=['campaign_id'], name='campaigns_campaign_id_unique')]
        indexes = [Index(suffix='idx', name='campaigns_end_date_index', fields=['end_date']), Index(suffix='idx', name='campaigns_campaign_group_uuid_index', fields=['campaign_group_uuid']), Index(suffix='idx', name='campaigns_start_date_index', fields=['start_date']), Index(suffix='idx', name='campaigns_rule_uuid_index', fields=['rule_uuid']), Index(suffix='idx', name='campaigns_state_index', fields=['state']), Index(suffix='idx', name='campaigns_campaign_type_index', fields=['campaign_type']), Index(suffix='idx', name='campaigns_seller_account_id_index', fields=['seller_account_id'])]
        is_tenant = True
async def main():
    campaigns = await Campaigns.query.using("2").all()
    print(campaigns)

with above receiving

assert self._database._pool is not None, "DatabaseBackend is not running"
AssertionError: DatabaseBackend is not running

[tarsil]

No no my friend. That is only if you want to use the contrib tenancy from Edgy. Similar to Django tenants. That is for a different purpose. I explained before what is happening. This has to do with greenlet of SQLAlchemy and Edgy when reflecting on multi tenancy needs to do some extras. No worries, it will be out soon as I was able to replicate the issue. Normal multi tenancy on models without reflection work like a charm but with reflection apparently the last SQLAlchemy update broke something when connecting

[tarsil]

Here (your last reply) it's because the in the main() you need to add a "await database.connect()" and after the query, the "await database.disconnect()"

[tarsil]

@keinagae found the problem. So the multi tenancy in fact exists and works like a charm. When reflecting the models for multi tenancy that is where the errors occur but from a SQLA side. I'm working on this and a fix should be out soon, hopefully. I added an extra functionality to allow the easy creation of tenancy schemas programmatically and those tests prove that tenancy actually works as intentionally should. More to come soon. Thank you for this report.

[tarsil]

@keinagae We did a new release where we refactored the internal of the reflection and according to the tests it should be ok and that greenlet error should disappear.

Although the InspectDB will continue to evolve internally as mentioned before as we are making Edgy even better and faster in the upcoming releases For now we also added a new function that you might find useful, the create_schema 👍🏼

https://edgy.tarsild.io/release-notes/

Give it a try and let me know, please.

I was able to replicate your error and change the implementation.

[keinagae]

also do i need to call database.connect before each query is there a nicer way?

[keinagae]

with primary key renamed on model if i query i am getting this

idx, datatype = self._column_map_full[str(key)]
                    ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
KeyError: 'campaigns.id'

is it possible to provide internal name or some thing to tell database column name ?

[tarsil]

This will be also coming in the next releases yes. We are refactoring internally some processes. Because now we automatically inject the id if none is provided and that is not good for multiple primary keys. More to come soon . No worries, it's almost here

[keinagae]

on e more thing if we need to connect to cockroach db is it supported?

[keinagae]

usually its supported where ever postgresql is supported but i think

[keinagae]

Also is there a technical doc for databasez package that you wrote because i need to use cockeoachdb and will try to write a provider for it there

[tarsil]

I need more details of your operation. What DB? Because that is also important

[keinagae]

its not with edgy its for databasez my provider for cockroach db is working using https://github.com/cockroachdb/sqlalchemy-cockroachdb as base and using postgree provider as base from databasez it works bu only on that line it takes too much time so any guide what shoul i look for

[keinagae]

i have added cockroach support on fork here https://github.com/keinagae/databasez and all query like

await database.connect()
query = """SELECT * FROM "208".campaigns"""
rows = await database.fetch_all(query=query)
print(rows)
await database.disconnect()

above queries dont take any time other that what is expected but if i use it with edgy model it takes too much time on
run_sync(cls.reflect(registry, tablename, metadata, schema_name))
let me know if you have any idea what i am doing wrong

[tarsil]

We don't support cockroach dB directly but it would be great to have that supported by databasez. Would you mind opening a PR and add that support? Testing including of course.

Regarding edgy, I will need to have a look for that same reason why I'm asking for the PR. In theory with that properly done, means we need to tune edgy but I suspect it's because we need to add that native support to databasez first. Test it properly with a cockroach db testing tables and then see the results there. Edgy only transpiles it.

[devkral]

it should be now possible to use cockroachdb within the main databasez.
You can just use sqlalchemy-cockroach package.