Re: mssql clustered index on non-pk field

[Mike Bayer]

so process_revision_directives is a pathway here to the extent that
you need to modify the autogenerate process such that a table like
this is generated for you automatically.

However, Alembic's normal mode of usage is that the "autogenerate"
tool is only a time-saver, and is not intended to produce a finished
result in every case.   The files it produces contain a comment to
this effect that you should "please modify as needed" the migration
script that is created.

Whether or not you need autogenerate to automate this, the first step
would be to come up with the appropriate op.create_table and
op.alter_table directives that accomplish what you need, and then
manually work these into a migration script as a proof of concept.  If
this is just one or two tables in your whole application that need to
be generated this way, you'd just leave it at that.

The "uniqueidentifier" part of this you can get through the
sqlalchemy.dialects.mssql.UNIQUEIDENTIFIER datatype.   The "ON
[PRIMARY]" part I'm not sure if the SQL server dialect supports that
syntax right now.   If you need that syntax then we'd have to alter
SQLAlchemy to support that, unless you were to render the DDL entirely
as a string.

let me know if that gets you started.


On Mon, Mar 19, 2018 at 12:08 PM, marc beirne  wrote:
> From issue here, closed as usage question:
>
> https://bitbucket.org/zzzeek/alembic/issues/485/mssql-clustered-index-non-primary
>
> Original question:
>
> Is there a solution for autogenerating in a single pass MSSQL tables with a
> primary key which isn't the clustered index, and a clustered index on a
> different column.
>
> Just defining a second clustered index column creates an exception because
> MSSQL is automatically setting the primary key to be the clustered index,
> setting index=False on that column definition doesn't help.
>
> If you manually run two revisions, one without the pks and then add the pks
> in later everything is fine but I can't see a way to configure the
> autogeneration to push constraint definitions to later statements, which
> would work, or force MSSQL not to create the clustered index on the PK.
>
>
> Original Response:
>
> if you can show me the DDL you want to emit I can help get you there.
>
> this is a usage question so far so let's pick it up on
> https://groups.google.com/forum/#!forum/sqlalchemy-alembic. thanks!
>
>
>
> This is an example of the DDL that  think someone would need to do this
>
>> CREATE TABLE [dbo].[test_temp_table](
>> [test_temp_table_clustered_id] [uniqueidentifier] NULL,
>> [test_temp_table_id] [int] NOT NULL,
>> [test_temp_column] [nvarchar](100) NULL
>> ) ON [PRIMARY]
>>
>>
>> CREATE CLUSTERED INDEX [arbitrary_name] ON [dbo].[test_temp_table]
>> (
>> [test_temp_table_clustered_id] ASC
>> )
>>
>> ALTER TABLE test_temp_table
>> ADD CONSTRAINT pk_test_temp_table
>> PRIMARY KEY (test_temp_table_id)
>
>
> The point is that instead of the primary key being declared inline in the
> table definition, it needs to be added after the clustered index is created
> because otherwise MSSQL will automatically add a clustered index on the pk
> column..
>
> Looking at the documentation, I'd think this might be possible through
>
> "process_revision_directives"
>
> But when I try and use this to split out the table generation and primary
> key addition, I need to add to the UpgrtadeOps.ops list:
> ops.CreatePrimaryKeyOp(*stuff)
>
> if I do this, the generation fails later at
> alembic.autogenerate.render._render_primary_key
> with a NotImplemented written there.
>
> I don't really understand how this project fits together so I'm not sure
> whether that is really something that's not implemented or just a crossed
> wire, or how to hack it either way.
>
> Best Regards,
>
> Marc
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

mssql clustered index on non-pk field

[marc beirne]

>From issue here, closed as usage question:

https://bitbucket.org/zzzeek/alembic/issues/485/mssql-clustered-index-non-primary

Original question:

Is there a solution for autogenerating in a single pass MSSQL tables with a 
primary key which isn't the clustered index, and a clustered index on a 
different column.

Just defining a second clustered index column creates an exception because 
MSSQL is automatically setting the primary key to be the clustered index, 
setting index=False on that column definition doesn't help.

If you manually run two revisions, one without the pks and then add the pks 
in later everything is fine but I can't see a way to configure the 
autogeneration to push constraint definitions to later statements, which 
would work, or force MSSQL not to create the clustered index on the PK.

Original Response:

if you can show me the DDL you want to emit I can help get you there. 

this is a usage question so far so let's pick it up on 
https://groups.google.com/forum/#!forum/sqlalchemy-alembic. thanks!


This is an example of the DDL that  think someone would need to do this

CREATE TABLE [dbo].[test_temp_table](
> [test_temp_table_clustered_id] [uniqueidentifier] NULL,
> [test_temp_table_id] [int] NOT NULL,
> [test_temp_column] [nvarchar](100) NULL
> ) ON [PRIMARY]
>
>
> CREATE CLUSTERED INDEX [arbitrary_name] ON [dbo].[test_temp_table]
> (
> [test_temp_table_clustered_id] ASC
> )
>
> ALTER TABLE test_temp_table 
> ADD CONSTRAINT pk_test_temp_table 
> PRIMARY KEY (test_temp_table_id)
>

The point is that instead of the primary key being declared inline in the 
table definition, it needs to be added after the clustered index is created 
because otherwise MSSQL will automatically add a clustered index on the pk 
column..

Looking at the documentation, I'd think this might be possible through 

"process_revision_directives"

But when I try and use this to split out the table generation and primary 
key addition, I need to add to the UpgrtadeOps.ops list:
ops.CreatePrimaryKeyOp(*stuff)

if I do this, the generation fails later at 
alembic.autogenerate.render._render_primary_key
with a NotImplemented written there.

I don't really understand how this project fits together so I'm not sure 
whether that is really something that's not implemented or just a crossed 
wire, or how to hack it either way.

Best Regards,

Marc

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] create table CheckConstraint

[Mike Bayer]

On Mon, Mar 19, 2018 at 12:52 AM, lone ois  wrote:
> Hi!
> i test sqlalchemy 1.1.12 - 1.2.5
> create table check constraint.
> ---
> class testTable(db):
> __tablename__ = 'testTable'
>
> id = Column(INT, primary_key = True, autoincrement = True, unique =
> True)
> ser = Column(INT)
>
> CheckConstraint('ser>100')
>
> #sqlalchemy echo and pgadmin4
>
> CREATE TABLE "testTable" (
> id SERIAL NOT NULL,
> ser INTEGER,
> PRIMARY KEY (id),
> UNIQUE (id)
>
> no check constraint.
> --
> class testTable(db):
> __tablename__ = 'testTable'
>
> id = Column(INT, primary_key = True, autoincrement = True, unique =
> True)
> ser = Column(INT)
> __table_args__ = (
> CheckConstraint('ser>100'),
> )
>
>
> #sqlalchemy echo
> CREATE TABLE "testTable" (
> id SERIAL NOT NULL,
> ser INTEGER,
> PRIMARY KEY (id),
> CHECK (ser>100),
> UNIQUE (id)
>
> this OK!
>
> so, i must use __table_args__ define CheckConstraint ??

when you add an object to a Python class:

class MyClass(object):

   # 

   "hello!"

that string "hello" has no way of being intercepted by the class and
it knows nothing about it.  SQLAlchemy's declarative has no way to see
this.   So with your CheckConstraint example, this suggests that yes,
you do need to use __table_args__.  However, there is an exception,
which is probably what you discovered, if you make a UniqueConstraint
or CheckConstraint that refers to the Column objects directly,
SQLAlchemy figures this out:


class MyClass(Base):
   # ...
   foo = Column(...)
   bar = Column(...)

   CheckConstraint("foo > bar")

vs.

class MyClass(Base):
   # ...
   foo = Column(...)
   bar = Column(...)

   CheckContraint(foo > bar)


That said, you should really use __table_args__ for these as that is
the idiomatic approach and will work consistently in all cases.




>
>
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] overriding/ignoring specific database records

[Mike Bayer]

since you need the relationship part also did you try a mapping to a
select() statement?The downside of that would be that your queries
would always be like "SELECT ... FROM (SELECT .. FROM..)". The
other way is to alter the query.   You can probably do pretty well on
this with a before_compile event:
http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile
 shows pretty much exactly how you'd do this (e.g. look in the
entities for the one that has special filter criteria, then add that
criteria).

On Sat, Mar 17, 2018 at 7:47 PM, Jonathan Vanasco  wrote:
> I was wondering if something were doable with SqlAlchemy in a decently
> performant manner that can be maintained cleanly.  I'm open to any
> strategies that people can suggest.
>
> We have a handful of reserved internal "system" or "control" records in our
> database. Depending on the table, anywhere from 10k to 1MM ids are reserved
> (in practice, only 10 are actually used).
>
> I'd like to exclude these items from showing up in relationships (and
> possibly even from appearing on a record as an id field).  They are only
> needed in our Admin interface (runs as a separate app, so it can be
> configured differently), but are a hindrance on the Public app.
>
> Here's a typical use case:
>
> One of our tables represents a content-graph of spidered web pages. During
> the course of development, we realized certain data wasn't being attributed
> to the right pages due to issues with redirect detection in a specific
> scenario.  Consequently, a chunk of records created before we addressed this
> case now list their 'redirected_from' id as the control record for
> "Untracked Redirect Chain".  (Due to the size of the DB and several other
> constraints, adding in a new field to track errors/data wasn't an option).
>
> That leaves me needing to hide this connection in two places on our
> clientside app:
> * don't load it into a sqlalchemy.orm.relationship
> * discard it when looking at the raw column ids (when populating the
> read-through cache)
>
> To handle this, I'm doing the latter manually and using a 'display_'
> prefixed property to wrap the real relationship - but this is ugly.
>
> I'm hoping for some suggestions from the larger community.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: create table CheckConstraint

[lone ois]

and 

CheckConstraint(ser > 10 )
is ok.





-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.