Search Results

Search found 5520 results on 221 pages for 'compound primary'.

Page 35/221 | < Previous Page | 31 32 33 34 35 36 37 38 39 40 41 42  | Next Page >

• Putting indexes in separate filegroup kills our queries

  - by womp

  Can anyone shed some light on this? On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine. On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was performing slower than it should. On this machine, there are two filegroups - PRIMARY and INDEXES. Both filegroups contain 1 file per logical volume, one logical volume per CPU, (and each logical volume is a RAID 10 of 4 physical disks). We isolated a few queries that were performing fast on the dev boxes and slow (up to 40x slower) on the production machine. Turned out these queries were using the non-clustered indexes that resided in the INDEXES filegroup. Tweaking some of the queries to only use clustered indexes that were in the PRIMARY filegroup dropped their times back to normal. As a final confirmation, we redeployed the same database on the same machine to have everything in PRIMARY, and things went back to normal! Here's the statistics output of one of the queries, run identically on the machine with different filegroup configurations (table names changed to protect the innocent): FAST (everything in PRIMARY filegroup): (3 row(s) affected) Table '0'. Scan count 2, logical reads 14, ... Table '1'. Scan count 0, logical reads 0, ... Table '1'. Scan count 0, logical reads 0, ... Table '2'. Scan count 2, logical reads 7, ... Table '3'. Scan count 2, logical reads 1012, ... Table '4'. Scan count 1, logical reads 3, ... SQL Server Execution Times: CPU time = 437 ms, elapsed time = 445 ms. SLOW (indexes split into their own filegroup): (3 row(s) affected) Table '0'. Scan count 209, logical reads 428, ... Table '1'. Scan count 0, logical reads 0,... Table '2'. Scan count 1021, logical reads 9043,.... Table '3'. Scan count 209, logical reads 105754, .... Table '4'. Scan count 0, logical reads 0, .... Table '5'. Scan count 1, logical reads 695, ... **Table '#46DA8CA9'. Scan count 205, logical reads 205, ...** Table '6'. Scan count 6, logical reads 436, ... Table '7'. Scan count 1, logical reads 12,.... SQL Server Execution Times: CPU time = 17581 ms, elapsed time = 17595 ms. Notice the weird temp table and extra tables involved in the slow query. It seems clear that having a second file group is making SQL Server batty with choosing an execution plan. What the heck is going on?

  Read the article

• Oracle GoldenGate Active-Active Part 1

  - by Nick_W

  My name is Nick Wagner, and I'm a recent addition to the Oracle Maximum Availability Architecture (MAA) product management team.  I've spent the last 15+ years working on database replication products, and I've spent the last 10 years working on the Oracle GoldenGate product.  So most of my posting will probably be focused on OGG.  One question that comes up all the time is around active-active replication with Oracle GoldenGate.  How do I know if my application is a good fit for active-active replication with GoldenGate?   To answer that, it really comes down to how you plan on handling conflict resolution.  I will delve into topology and deployment in a later blog, but here is a simple architecture: The two most common resolution routines are host based resolution and timestamp based resolution. Host based resolution is used less often, but works with the fewest application changes.  Think of it like this: any transactions from SystemA always take precedence over any transactions from SystemB.  If there is a conflict on SystemB, then the record from SystemA will overwrite it.  If there is a conflict on SystemA, then it will be ignored.  It is quite a bit less restrictive, and in most cases, as long as all the tables have primary keys, host based resolution will work just fine.  Timestamp based resolution, on the other hand, is a little trickier. In this case, you can decide which record is overwritten based on timestamps. For example, does the older record get overwritten with the newer record?  Or vice-versa?  This method not only requires primary keys on every table, but it also requires every table to have a timestamp/date column that is updated each time a record is inserted or updated on the table.  Most homegrown applications can always be customized to include these requirements, but it's a little more difficult with 3rd party applications, and might even be impossible for large ERP type applications.  If your database has these features - whether it’s primary keys for host based resolution, or primary keys and timestamp columns for timestamp based resolution - then your application could be a great candidate for active-active replication.  But table structure is not the only requirement.  The other consideration applies when there is a conflict; i.e., do I need to perform any notification or track down the user that had their data overwritten?  In most cases, I don't think it's necessary, but if it is required, OGG can always create an exceptions table that contains all of the overwritten transactions so that people can be notified. It's a bit of extra work to implement this type of option, but if the business requires it, then it can be done. Unless someone is constantly monitoring this exception table or has an automated process in dealing with exceptions, there will be a delay in getting a response back to the end user. Ideally, when setting up active-active resolution we can include some simple procedural steps or configuration options that can reduce, or in some cases eliminate the potential for conflicts.  This makes the whole implementation that much easier and foolproof.  And I'll cover these in my next blog. 

  Read the article

• SQL SERVER – Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – T-SQL Example – Part 2 of 2

  - by pinaldave

  Yesterday I wrote a real world story of how a friend who thought they have an issue with intrusion or virus whereas the issue was really in the code. I strongly suggest you read my earlier blog post Curious Case of Disappearing Rows – ON UPDATE CASCADE and ON DELETE CASCADE – Part 1 of 2 before continuing this blog post as this is second part of the first blog post. Let me reproduce the simple scenario in T-SQL. Building Sample Data USE [TestDB] GO -- Creating Table Products CREATE TABLE [dbo].[Products]( [ProductID] [int] NOT NULL, [ProductDesc] [varchar](50) NOT NULL, CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ( [ProductID] ASC )) ON [PRIMARY] GO -- Creating Table ProductDetails CREATE TABLE [dbo].[ProductDetails]( [ProductDetailID] [int] NOT NULL, [ProductID] [int] NOT NULL, [Total] [int] NOT NULL, CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED ( [ProductDetailID] ASC )) ON [PRIMARY] GO ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT [FK_ProductDetails_Products] FOREIGN KEY([ProductID]) REFERENCES [dbo].[Products] ([ProductID]) ON UPDATE CASCADE ON DELETE CASCADE GO -- Insert Data into Table USE TestDB GO INSERT INTO Products (ProductID, ProductDesc) SELECT 1, 'Bike' UNION ALL SELECT 2, 'Car' UNION ALL SELECT 3, 'Books' GO INSERT INTO ProductDetails ([ProductDetailID],[ProductID],[Total]) SELECT 1, 1, 200 UNION ALL SELECT 2, 1, 100 UNION ALL SELECT 3, 1, 111 UNION ALL SELECT 4, 2, 200 UNION ALL SELECT 5, 3, 100 UNION ALL SELECT 6, 3, 100 UNION ALL SELECT 7, 3, 200 GO Select Data from Tables -- Selecting Data SELECT * FROM Products SELECT * FROM ProductDetails GO Delete Data from Products Table -- Deleting Data DELETE FROM Products WHERE ProductID = 1 GO Select Data from Tables Again -- Selecting Data SELECT * FROM Products SELECT * FROM ProductDetails GO Clean up Data -- Clean up DROP TABLE ProductDetails DROP TABLE Products GO My friend was confused as there was no delete was firing over ProductsDetails Table still there was a delete happening. The reason was because there is a foreign key created between Products and ProductsDetails Table with the keywords ON DELETE CASCADE. Due to ON DELETE CASCADE whenever is specified when the data from Table A is deleted and if it is referenced in another table using foreign key it will be deleted as well. Workaround 1: Design Changes – 3 Tables Change the design to have more than two tables. Create One Product Mater Table with all the products. It should historically store all the products list in it. No products should be ever removed from it. Add another table called Current Product and it should contain only the table which should be visible in the product catalogue. Another table should be called as ProductHistory table. There should be no use of CASCADE keyword among them. Workaround 2: Design Changes - Column IsVisible You can keep the same two tables. 1) Products and 2) ProductsDetails. Add a column with BIT datatype to it and name it as a IsVisible. Now change your application code to display the catalogue based on this column. There should be no need to delete anything. Workaround 3: Bad Advices (Bad advises begins here) The reason I have said bad advices because these are going to be bad advices for sure. You should make necessary design changes and not use poor workarounds which can damage the system and database integrity further. Here are the examples 1) Do not delete the data – well, this is not a real solution but can give time to implement design changes. 2) Do not have ON CASCADE DELETE – in this case, you will have entry in productsdetails which will have no corresponding product id and later on there will be lots of confusion. 3) Duplicate Data – you can have all the data of the product table move to the product details table and repeat them at each row. Now remove CASCADE code. This will let you delete the product table rows without any issue. There are so many things wrong this suggestion, that I will not even start here. (Bad advises ends here)  Well, did I miss anything? Please help me with your suggestions. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Subsonic : Can’t decide which property to consider the Key? foreign key issue.

  - by AJ

  Hi I am trying to select count of rows from a table which has foreign keys of two tables. The C# code threw the error mentioned below. So, I added a primary key column to the table (schema as follows) and regenerated the code. But still the same error is coming. Error : Can't decide which property to consider the Key - you can create one called 'ID' or mark one with SubSonicPrimaryKey attribute sqLite Table schema CREATE TABLE "AlbumDocuments" ("Id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "AlbumId" INTEGER NOT NULL CONSTRAINT fk_AlbumId REFERENCES Albums(Id) , "DocumentId" INTEGER NOT NULL CONSTRAINT fk_DocumentId REFERENCES Documents(Id)) C# code int selectAlbumDocumentsCount = new SubSonic.Query.Select() .From<DocSafeDB.DataLayer.AlbumDocumentsTable>() .Where(DocSafeDB.DataLayer.AlbumDocumentsTable.AlbumIdColumn).In(request.AlbumId) .Execute(); Not sure what I should be doing next as I can't do where against primary key because I don;t have that info. So my questions are: How do I select count of rows against foreign key column? Is primary key required in this scenario? I have several things but not sure why its not working. To me it looks like a very normal use case. Please advise. Thanks AJ

  Read the article

• Displaying Many-To-Many Database relationship in VB.NET 2008 with DataGrid, MS SQL 2008

  - by user337501

  Computer bombed while posting this, couldnt find a duplicate question but if there is one, forgive me. So, I've run into a wall. And rather than use a ladder to avoid it, I'd like go through it. I'm setting up what I can best describe as a many-to-many relationship in a database. To examplify, imagine I have three primary tables: Items, Categories, Sections(nevermind the potential redundancy) Then I have another table, Properties. Items, Categories, and Sections can be associated with many properties. A single property can be associated with one, all, or none of the other tables. The best way I can figure to do this is to have join tables make the relationship. i.e. tblItems----(Foreign Key)----tblItems_To_Properties----(Foreign Key)----tblProperties In this example, tblItems simply has an "ItemID" Primary Key. tblItems_To_Properties has its own Primary Key(tblItems_To_PropertiesID), a Foreign Key to the Item(ItemID) and a Foreign key to the Property(PropertyID). The Properties table simply has its primary key(PropertyID) I hope this example isnt too confusing...if I have to I can find a way to put a diagram up or something. My problem is, I want to display this in a DataGrid using the Master-Detail method(DevExpress GridControl). I use the tblItems as a test, and I can see the Items in the parent view, but in the child view I see(understandably) the join table and that is it. My goal is to make it so the Grid ignores the join table and shows the Properties table as the only child. Any help on this method or insight into another solution would be muuuuuuuch appreciat

  Read the article

• optimizing an sql query using inner join and order by

  - by Sergio B

  I'm trying to optimize the following query without success. Any idea where it could be indexed to prevent the temporary table and the filesort? EXPLAIN SELECT SQL_NO_CACHE `groups`.* FROM `groups` INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id WHERE ((`memberships`.user_id = 1) AND (`memberships`.`status_code` = 1 AND `memberships`.`manager` = 0)) ORDER BY groups.created_at DESC LIMIT 5;` +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ | 1 | SIMPLE | memberships | ref | grp_usr,grp,usr,grp_mngr | usr | 5 | const | 5 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | groups | eq_ref | PRIMARY | PRIMARY | 4 | sportspool_development.memberships.group_id | 1 | | +----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+ 2 rows in set (0.00 sec) +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | groups | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | groups | 1 | index_groups_on_name | 1 | name | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_privacy_setting | 1 | privacy_setting | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_created_at | 1 | created_at | A | 6 | NULL | NULL | YES | BTREE | | | groups | 1 | index_groups_on_id_and_created_at | 1 | id | A | 6 | NULL | NULL | | BTREE | | | groups | 1 | index_groups_on_id_and_created_at | 2 | created_at | A | 6 | NULL | NULL | YES | BTREE | | +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | memberships | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | memberships | 0 | grp_usr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 0 | grp_usr | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | usr | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp_mngr | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | grp_mngr | 2 | manager | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 1 | group_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 2 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 3 | status_code | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | complex_index | 4 | manager | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 1 | user_id | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 2 | status_code | A | 2 | NULL | NULL | YES | BTREE | | | memberships | 1 | index_memberships_on_user_id_and_status_code_and_manager | 3 | manager | A | 2 | NULL | NULL | YES | BTREE | | +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

  Read the article

• Problem building STLport NDK r5/ Android

  - by user558299

  Hi all, I'm trying to build STLport for Android. I got the following steps, but they are not working: 1 - Clone STLport repository using: git clone git://stlport.git.sourceforge.net/gitroot/stlport/stlport 2 - Configure environment using : ./configure --target=arm-eabi --with-extra-cxxflags="-fshort-enums" --with-extra-cflags="-fshort-enums" 3 - From src directory build it using make SYSROOT"{MY NDK path}/platforms/android-5/arch-arm/" release-static But I got the following errors: In file included from ../stlport/stl/_alloc.h:45, from ../stlport/memory:29, from dll_main.cpp:41: ../stlport/stl/_new.h:45:24: error: new: No such file or directory In file included from ../stlport/stl/_limits.h:36, from ../stlport/limits:29, from dll_main.cpp:48: ../stlport/stl/_cwchar.h:26:30: error: cstddef: No such file or directory In file included from ../stlport/stl/_utility.h:35, from ../stlport/utility:35, from dll_main.cpp:40: ../stlport/type_traits:889: error: 'declval' was not declared in this scope ../stlport/type_traits:889: error: expected primary-expression before '>' token ../stlport/type_traits:889: error: expected primary-expression before ')' token ../stlport/type_traits:889: error: 'declval' was not declared in this scope ../stlport/type_traits:889: error: expected primary-expression before '>' token ../stlport/type_traits:889: error: expected primary-expression before ')' token ../stlport/type_traits:889: error: ISO C++ forbids declaration of 'decltype' with no type ../stlport/type_traits:889: error: ISO C++ forbids in-class initialization of non-const static member 'decltype' ../stlport/type_traits:889: error: template declaration of 'int std::tr1::detail::decltype' ../stlport/type_traits:942: error: ISO C++ forbids declaration of 'decltype' with no type ../stlport/type_traits:942: error: ISO C++ forbids in-class initialization of non-const static member 'decltype' ../stlport/type_traits:942: error: template declaration of 'int std::tr1::detail::decltype' make: *** [obj/arm-eabi-gcc/so/dll_main.o] Error 1 Is there any include dir or configuration I´m missing? Thanks, Sergio

  Read the article

• What's the fastest way to bulk insert a lot of data in SQL Server (C# client)

  - by Andrew

  I am hitting some performance bottlenecks with my C# client inserting bulk data into a SQL Server 2005 database and I'm looking for ways in which to speed up the process. I am already using the SqlClient.SqlBulkCopy (which is based on TDS) to speed up the data transfer across the wire which helped a lot, but I'm still looking for more. I have a simple table that looks like this: CREATE TABLE [BulkData]( [ContainerId] [int] NOT NULL, [BinId] [smallint] NOT NULL, [Sequence] [smallint] NOT NULL, [ItemId] [int] NOT NULL, [Left] [smallint] NOT NULL, [Top] [smallint] NOT NULL, [Right] [smallint] NOT NULL, [Bottom] [smallint] NOT NULL, CONSTRAINT [PKBulkData] PRIMARY KEY CLUSTERED ( [ContainerIdId] ASC, [BinId] ASC, [Sequence] ASC )) I'm inserting data in chunks that average about 300 rows where ContainerId and BinId are constant in each chunk and the Sequence value is 0-n and the values are pre-sorted based on the primary key. The %Disk time performance counter spends a lot of time at 100% so it is clear that disk IO is the main issue but the speeds I'm getting are several orders of magnitude below a raw file copy. Does it help any if I: Drop the Primary key while I am doing the inserting and recreate it later Do inserts into a temporary table with the same schema and periodically transfer them into the main table to keep the size of the table where insertions are happening small Anything else? -- Based on the responses I have gotten, let me clarify a little bit: Portman: I'm using a clustered index because when the data is all imported I will need to access data sequentially in that order. I don't particularly need the index to be there while importing the data. Is there any advantage to having a nonclustered PK index while doing the inserts as opposed to dropping the constraint entirely for import? Chopeen: The data is being generated remotely on many other machines (my SQL server can only handle about 10 currently, but I would love to be able to add more). It's not practical to run the entire process on the local machine because it would then have to process 50 times as much input data to generate the output. Jason: I am not doing any concurrent queries against the table during the import process, I will try dropping the primary key and see if that helps. ~ Andrew

  Read the article

• Need help to create database schema for wholesale online tee store

  - by techiepark

  Hi, I'm currently working on wholesale online t-shirt shop. I have done this for fixed quantity and price, and its working fine. Now i need to do this for variable quantity and price. Here is the reference link, like what i have to do. Basic tables i have created are - CREATE TABLE attribute ( attribute_id int(11) NOT NULL auto_increment, name varchar(100) NOT NULL, PRIMARY KEY (attribute_id) ); CREATE TABLE attribute_value ( attribute_value_id int(11) NOT NULL auto_increment, attribute_id int(11) NOT NULL, value varchar(100) NOT NULL, PRIMARY KEY (attribute_value_id), KEY idx_attribute_value_attribute_id (attribute_id) ); CREATE TABLE product ( product_id int(11) NOT NULL auto_increment, name varchar(100) NOT NULL, description varchar(1000) NOT NULL, price decimal(10,2) NOT NULL, image varchar(150) default NULL, thumbnail varchar(150) default NULL, PRIMARY KEY (product_id), FULLTEXT KEY idx_ft_product_name_description (name,description) ); CREATE TABLE product_attribute ( product_id int(11) NOT NULL, attribute_value_id int(11) NOT NULL, PRIMARY KEY (product_id,attribute_value_id) ); I'm not getting how to store the price based on variable quantity. Please help me to create product and its related tables. my requirement is same as above reference link.

  Read the article

• Doctrine 1.2: How do i prevent a contraint from being assigned to both sides of a One-to-many relati

  - by prodigitalson

  Is there a way to prevent Doctrine from assigning a contraint on both sides of a one-to-one relationship? Ive tried moving the definition from one side to the other and using owning side but it still places a constraint on both tables. when I only want the parent table to have a constraint - ie. its possible for the parent to not have an associated child. For example iwant the following sql schema essentially: CREATE TABLE `parent_table` ( `child_id` varchar(50) NOT NULL, `id` integer UNSIGNED NOT NULL auto_increment, PRIMARY KEY (`id`) ); CREATE TABLE `child_table` ( `id` integer UNSIGNED NOT NULL auto_increment, `child_id` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY (`child_id`), CONSTRAINT `parent_table_child_id_FK_child_table_child_id` FOREIGN KEY (`child_id`) REFERENCES `parent_table` (`child_id`) ); However im getting something like this: CREATE TABLE `parent_table` ( `child_id` varchar(50) NOT NULL, `id` integer UNSIGNED NOT NULL auto_increment, PRIMARY KEY (`id`), CONSTRAINT `child_table_child_id_FK_parent_table_child_id` FOREIGN KEY (`child_id`) REFERENCES `child_table` (`child_id`) ); CREATE TABLE `child_table` ( `id` integer UNSIGNED NOT NULL auto_increment, `child_id` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY (`child_id`), CONSTRAINT `parent_table_child_id_FK_child_table_child_id` FOREIGN KEY (`child_id`) REFERENCES `parent_table` (`child_id`) ); I could just remove the constraint manually or modify my accessors to return/set a single entity in the collection (using a one-to-many) but it seems like there should built in way to handle this. Also im using Symfony 1.4.4 (pear installtion ATM) - in case its an sfDoctrinePlugin issue and not necessarily Doctrine itself.

  Read the article

• MySQL DDL error creating tables

  - by Alexandstein

  I am attempting to create tables for a MySQL database, but I am having some syntactical issues. It would seem that syntax checking is behaving differently between tables for some reason. While I've gotten all the other tables to go through, the table, 'stock' doesn't seem to be working, despite seeming to use the same syntax patterns. CREATE TABLE users ( user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(30) NOT NULL, password CHAR(41) NOT NULL, date_joined DATETIME NOT NULL, funds DOUBLE UNSIGNED NOT NULL, PRIMARY KEY(user_id), UNIQUE KEY(username) ); CREATE TABLE owned_stocks ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id SMALLINT UNSIGNED NOT NULL, paid_price DOUBLE UNSIGNED NOT NULL, quantity MEDIUMINT UNSIGNED NOT NULL, purchase_date DATETIME NOT NULL, PRIMARY KEY(id) ); CREATE TABLE tracking_stocks ( ticker VARCHAR(5) NOT NULL, user_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(ticker) ); CREATE TABLE stocks ( ticker VARCHAR(5) NOT NULL, last DOUBLE UNSIGNED NOT NULL, high DOUBLE UNSIGNED NOT NULL, low DOUBLE UNSIGNED NOT NULL, company_name VARCHAR(30) NOT NULL, last_updated INT UNSIGNED NOT NULL, change DOUBLE NOT NULL, percent_change DOUBLE NOT NULL, PRIMARY KEY(ticker) ); Am I just missing a really obvious syntactical issue? ERROR: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change DOUBLE NOT NULL, percent_change DOUBLE NOT NULL, last DOUBLE' at line 4

  Read the article

• Hibernate MapKeyManyToMany gives composite key where none exists

  - by larsrc

  I have a Hibernate (3.3.1) mapping of a map using a three-way join table: @Entity public class SiteConfiguration extends ConfigurationSet { @ManyToMany @MapKeyManyToMany(joinColumns=@JoinColumn(name="SiteTypeInstallationId")) @JoinTable( name="SiteConfig_InstConfig", joinColumns = @JoinColumn(name="SiteConfigId"), inverseJoinColumns = @JoinColumn(name="InstallationConfigId") ) Map<SiteTypeInstallation, InstallationConfiguration> installationConfigurations = new HashMap<SiteTypeInstallation, InstallationConfiguration>(); ... } The underlying table (in Oracle 11g) is: Name Null Type ------------------------------ -------- ---------- SITECONFIGID NOT NULL NUMBER(19) SITETYPEINSTALLATIONID NOT NULL NUMBER(19) INSTALLATIONCONFIGID NOT NULL NUMBER(19) The key entity used to have a three-column primary key in the database, but is now redefined as: @Entity public class SiteTypeInstallation implements IdResolvable { @Id @GeneratedValue(generator="SiteTypeInstallationSeq", strategy= GenerationType.SEQUENCE) @SequenceGenerator(name = "SiteTypeInstallationSeq", sequenceName = "SEQ_SiteTypeInstallation", allocationSize = 1) long id; @ManyToOne @JoinColumn(name="SiteTypeId") SiteType siteType; @ManyToOne @JoinColumn(name="InstalationRoleId") InstallationRole role; @ManyToOne @JoinColumn(name="InstallationTypeId") InstType type; ... } The table for this has a primary key 'Id' and foreign key constraints+indexes for each of the other columns: Name Null Type ------------------------------ -------- ---------- SITETYPEID NOT NULL NUMBER(19) INSTALLATIONROLEID NOT NULL NUMBER(19) INSTALLATIONTYPEID NOT NULL NUMBER(19) ID NOT NULL NUMBER(19) For some reason, Hibernate thinks the key of the map is composite, even though it isn't, and gives me this error: org.hibernate.MappingException: Foreign key (FK1A241BE195C69C8:SiteConfig_InstConfig [SiteTypeInstallationId])) must have same number of columns as the referenced primary key (SiteTypeInstallation [SiteTypeId,InstallationRoleId]) If I remove the annotations on installationConfigurations and make it transient, the error disappears. I am very confused why it thinks SiteTypeInstallation has a composite key at all when @Id is clearly defining a simple key, and doubly confused why it picks exactly just those two columns. Any idea why this happens? Is it possible that JBoss (5.0 EAP) + Hibernate somehow remembers a mistaken idea of the primary key across server restarts and code redeployments? Thanks in advance, -Lars

  Read the article

• SQL Server Long Query

  - by thormj

  Ok... I don't understand why this query is taking so long (MSSQL Server 2005): [Typical output 3K rows, 5.5 minute execution time] SELECT dbo.Point.PointDriverID, dbo.Point.AssetID, dbo.Point.PointID, dbo.Point.PointTypeID, dbo.Point.PointName, dbo.Point.ForeignID, dbo.Pointtype.TrendInterval, coalesce(dbo.Point.trendpts,5) AS TrendPts, LastTimeStamp = PointDTTM, LastValue=PointValue, Timezone FROM dbo.Point LEFT JOIN dbo.PointType ON dbo.PointType.PointTypeID = dbo.Point.PointTypeID LEFT JOIN dbo.PointData ON dbo.Point.PointID = dbo.PointData.PointID AND PointDTTM = (SELECT Max(PointDTTM) FROM dbo.PointData WHERE PointData.PointID = Point.PointID) LEFT JOIN dbo.SiteAsset ON dbo.SiteAsset.AssetID = dbo.Point.AssetID LEFT JOIN dbo.Site ON dbo.Site.SiteID = dbo.SiteAsset.SiteID WHERE onlinetrended =1 and WantTrend=1 PointData is the biggun, but I thought its definition should allow me to pick up what I want easily enough: CREATE TABLE [dbo].[PointData]( [PointID] [int] NOT NULL, [PointDTTM] [datetime] NOT NULL, [PointValue] [real] NULL, [DataQuality] [tinyint] NULL, CONSTRAINT [PK_PointData_1] PRIMARY KEY CLUSTERED ( [PointID] ASC, [PointDTTM] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_PointDataDesc] ON [dbo].[PointData] ( [PointID] ASC, [PointDTTM] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO PointData is 550M rows, and Point (source of PointID) is only 28K rows. I tried making an Indexed View, but I can't figure out how to get the Last Timestamp/Value out of it in a compatible way (no Max, no subquery, no CTE). This runs twice an hour, and after it runs I put more data into those 3K PointID's that I selected. I thought about creating LastTime/LastValue tables directly into Point, but that seems like the wrong approach. Am I missing something, or should I rebuild something? (I'm also the DBA, but I know very little about A'ing a DB!)

  Read the article

• How to map a Dictionary<string, string> spanning several tables

  - by Kim Johansson

  I have four tables: CREATE TABLE [Languages] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, [Code] NVARCHAR(10) NOT NULL, PRIMARY KEY ([Id]), UNIQUE INDEX ([Code]) ); CREATE TABLE [Words] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, PRIMARY KEY ([Id]) ); CREATE TABLE [WordTranslations] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, [Value] NVARCHAR(100) NOT NULL, [Word] INTEGER NOT NULL, [Language] INTEGER NOT NULL, PRIMARY KEY ([Id]), FOREIGN KEY ([Word]) REFERENCES [Words] ([Id]), FOREIGN KEY ([Language]) REFERENCES [Languages] ([Id]) ); CREATE TABLE [Categories] ( [Id] INTEGER IDENTITY(1,1) NOT NULL, [Word] INTEGER NOT NULL, PRIMARY KEY ([Id]), FOREIGN KEY ([Word]) REFERENCES [Words] ([Id]) ); So you get the name of a Category via the Word - WordTranslation - Language relations. Like this: SELECT TOP 1 wt.Value FROM [Categories] AS c LEFT JOIN [WordTranslations] AS wt ON c.Word = wt.Word WHERE wt.Language = ( SELECT TOP 1 l.Id FROM [Languages] WHERE l.[Code] = N'en-US' ) AND c.Id = 1; That would return the en-US translation of the Category with Id = 1. My question is how to map this using the following class: public class Category { public virtual int Id { get; set; } public virtual IDictionary<string, string> Translations { get; set; } } Getting the same as the SQL query above would be: Category category = session.Get<Category>(1); string name = category.Translations["en-US"]; And "name" would now contain the Category's name in en-US. Category is mapped against the Categories table. How would you do this and is it even possible?

  Read the article

• Database design MySQL using foreign keys

  - by dscher

  I'm having some a little trouble understanding how to handle the database end of a program I'm making. I'm using an ORM in Kohana, but am hoping that a generalized understanding of how to solve this issue will lead me to an answer with the ORM. I'm writing a program for users to manage their stock research information. My tables are basically like so: CREATE TABLE tags( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, tags VARCHAR(30), UNIQUE(tags) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE stock_tags( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, tag_id INT NOT NULL, stock_id INT NOT NULL, FOREIGN KEY (tag_id) REFERENCES tags(id), FOREIGN KEY(stock_id) REFERENCES stocks(id) ON DELETE CASCADE ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE notes( id INT AUTO_INCREMENT NOT NULL, stock_id INT NOT NULL, notes TEXT NOT NULL, FOREIGN KEY (stock_id) REFERENCES stocks(id) ON DELETE CASCADE, PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; CREATE TABLE links( id INT AUTO_INCREMENT NOT NULL, stock_id INT NOT NULL, links VARCHAR(2083) NOT NULL, FOREIGN KEY (stock_id) REFERENCES stocks(id) ON DELETE CASCADE, PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; How would I get all the attributes of a single stock, including its links, notes, and tags? Do I have to add links, notes, and tags columns to the stocks table and then how do you call it? I know this differs using an ORM and I'd assume that I can use join tables in SQL. Thanks for any help, this will really help me understand the issue a lot better.

  Read the article

• MySQL query does not return any data

  - by Alex L

  Hi, I need to retrieve data from a specific time period. The query works fine until I specify the time period. Is there something wrong with the way I specify time period? I know there are many entries within that time-frame. This query returns empty: SELECT stop_times.stop_id, STR_TO_DATE(stop_times.arrival_time, '%H:%i:%s') as stopTime, routes.route_short_name, routes.route_long_name, trips.trip_headsign FROM trips JOIN stop_times ON trips.trip_id = stop_times.trip_id JOIN routes ON routes.route_id = trips.route_id WHERE stop_times.stop_id = 5508 HAVING stopTime BETWEEN DATE_SUB(stopTime,INTERVAL 1 MINUTE) AND DATE_ADD(stopTime,INTERVAL 20 MINUTE); Here is it's EXPLAIN: +----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+ | 1 | SIMPLE | stop_times | ref | trip_id,stop_id | stop_id | 5 | const | 605 | Using where | | 1 | SIMPLE | trips | eq_ref | PRIMARY,route_id | PRIMARY | 4 | wmata_gtfs.stop_times.trip_id | 1 | | | 1 | SIMPLE | routes | eq_ref | PRIMARY | PRIMARY | 4 | wmata_gtfs.trips.route_id | 1 | | +----+-------------+------------+--------+------------------+---------+---------+-------------------------------+------+-------------+ 3 rows in set (0.00 sec) The query works if I remove the HAVING clause (don't specify time range). Returns: +---------+----------+------------------+-----------------+---------------+ | stop_id | stopTime | route_short_name | route_long_name | trip_headsign | +---------+----------+------------------+-----------------+---------------+ | 5508 | 06:31:00 | "80" | "" | "FORT TOTTEN" | | 5508 | 06:57:00 | "80" | "" | "FORT TOTTEN" | | 5508 | 07:23:00 | "80" | "" | "FORT TOTTEN" | | 5508 | 07:49:00 | "80" | "" | "FORT TOTTEN" | | 5508 | 08:15:00 | "80" | "" | "FORT TOTTEN" | | 5508 | 08:41:00 | "80" | "" | "FORT TOTTEN" | | 5508 | 09:08:00 | "80" | "" | "FORT TOTTEN" | I am using Google Transit format Data loaded into MySQL. The query is supposed to provide stop times and bus routes for a given bus stop. For a bus stop, I am trying to get: Route Name Bus Name Bus Direction (headsign) Stop time The results should be limited only to buses times from 1 min ago to 20 min from now. Please let me know if you could help.

  Read the article

• Using Hibernate with MS ACCESS 2007 Database (Free JDBC Driver)

  - by Quentin T.

  1. I want to do a reverse engineering action with the Hibernate plugin of Eclipse on a MS Access 2007 Database. I'm forced to use a existing MS Access 2007 db. A easy solution is to buy the HXTT. But I want to use a free driver to do my work. So I tried to apply this post : http://www.programmingforfuture.com/2011/06/how-to-use-ms-access-with-hibernate.html (That uses the SQL Server dialect and the driver sun.jdbc.odbc.JdbcOdbcDriver) Unfortunately I have an error that nobody seems to have been on the internet: Exception while generating code Reason : org.hibernate.exception.GenericJDBCException: Error while reading primary key meta data for `c:/myaccessdb.mdb`.TableTest1 I have try to change the primary key on my MS Access DB (deleting all primary key) or to try the reverse engineering on a MS ACCESS with only one table without primary key, but I got all times the problems. 2. The purpose of my job is to transfer daily (weekly) an Oracle 11g database with data from an existing database MS ACCESS 2007. And I thought to use a procedure (Hibernate EJB) Java to be launched automatically every week to do the data transfer. Is this is the best solution ? Configuration : sun.jdbc.odbc.JdbcOdbcDriver v??? Hibernate v3.4 Eclipse ps: If you are a HXTT developer or seller please be indulgent with my post ;). Making money by making people believe that you help, it's bad ! A solution is to use Derby Client driver, as the solution in the post: Does anyone know if Hibernate and java will work effectively with Access? But a clarification of the answer of Rich Seller is required. Could you explain your answer and explain your configuration (hibernate.cfg.xml, persistence.xml and what URL you use in the property name="hibernate.connection.url") without using paying HXTT driver but with the free Derby driver.

  Read the article

• Removing "Using temporary; Using filesort" from this MySQL select+join+group by query

  - by claytontstanley

  I have the following query: select t.Chunk as LeftChunk, t.ChunkHash as LeftChunkHash, q.Chunk as RightChunk, q.ChunkHash as RightChunkHash, count(t.ChunkHash) as ChunkCount from chunksubset as t join chunksubset as q on t.ID = q.ID group by LeftChunkHash, RightChunkHash And the following explain table: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE subsets ref PRIMARY,IDIndex,SubsetIndex SubsetIndex 767 const 522014 "Using where; Using temporary; Using filesort" 1 SIMPLE subsets eq_ref PRIMARY,IDIndex,SubsetIndex PRIMARY 771 sotero.subsets.Id,const 1 "Using where; Using index" 1 SIMPLE c ref IDIndex IDIndex 4 sotero.subsets.Id 12 "Using where" 1 SIMPLE c ref IDIndex IDIndex 4 sotero.subsets.Id 12 note the "using temporary; using filesort". When this query is run, I quickly run out of RAM (presumably b/c of the temp table), and then the HDD kicks in, and the query slows to a halt. I thought it might be an index issue, so I started adding a few that sort of made sense: Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment chunks 0 PRIMARY 1 ChunkId A 17796190 NULL NULL BTREE chunks 1 ChunkHashIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 IDIndex 1 Id A 1483015 NULL NULL BTREE chunks 1 ChunkIndex 1 Chunk A 243783 NULL NULL BTREE chunks 1 ChunkTypeIndex 1 ChunkType A 2 NULL NULL BTREE chunks 1 chunkHashByChunkIDIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 chunkHashByChunkIDIndex 2 ChunkId A 17796190 NULL NULL BTREE chunks 1 chunkHashByChunkTypeIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 chunkHashByChunkTypeIndex 2 ChunkType A 261708 NULL NULL BTREE chunks 1 chunkHashByIDIndex 1 ChunkHash A 243783 NULL NULL BTREE chunks 1 chunkHashByIDIndex 2 Id A 17796190 NULL NULL BTREE But still using the temporary table. The db engine is MyISAM. How can I get rid of the using temporary; using filesort in this query? Just changing to InnoDB w/o explaining the underlying cause is not a particularly satisfying answer. Besides, if the solution is to just add the proper index, then that's much easier than migrating to another db engine.

  Read the article

• Mysql slow query: INNER JOIN + ORDER BY causes filesort

  - by Alexander

  Hello! I'm trying to optimize this query: SELECT `posts`.* FROM `posts` INNER JOIN `posts_tags` ON `posts`.id = `posts_tags`.post_id WHERE (((`posts_tags`.tag_id = 1))) ORDER BY posts.created_at DESC; The size of tables is 38k rows, and 31k and mysql uses "filesort" so it gets pretty slow. I tried to use different indexes, no luck. CREATE TABLE `posts` ( `id` int(11) NOT NULL auto_increment, `created_at` datetime default NULL, PRIMARY KEY (`id`), KEY `index_posts_on_created_at` (`created_at`), KEY `for_tags` (`trashed`,`published`,`clan_private`,`created_at`) ) ENGINE=InnoDB AUTO_INCREMENT=44390 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci CREATE TABLE `posts_tags` ( `id` int(11) NOT NULL auto_increment, `post_id` int(11) default NULL, `tag_id` int(11) default NULL, `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`), KEY `index_posts_tags_on_post_id_and_tag_id` (`post_id`,`tag_id`) ) ENGINE=InnoDB AUTO_INCREMENT=63175 DEFAULT CHARSET=utf8 +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+ | 1 | SIMPLE | posts_tags | index | index_post_id_and_tag_id | index_post_id_and_tag_id | 10 | NULL | 24159 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | posts | eq_ref | PRIMARY | PRIMARY | 4 | .posts_tags.post_id | 1 | | +----+-------------+------------+--------+--------------------------+--------------------------+---------+---------------------+-------+-----------------------------------------------------------+ 2 rows in set (0.00 sec) What kind of index I need to define to avoid mysql using filesort? Is it possible when order field is not in where clause?

  Read the article

• Do I need to write a trigger for such a simple constraint?

  - by Paul Hanbury

  I really had a hard time knowing what words to put into the title of my question, as I am not especially sure if there is a database pattern related to my problem. I will try to simplify matters as much as possible to get directly to the heart of the issue. Suppose I have some tables. The first one is a list of widget types: create table widget_types ( widget_type_id number(7,0) primary key, description varchar2(50) ); The next one contains icons: create table icons ( icon_id number(7,0) primary key, picture blob ); Even though the users get to select their preferred widget, there is a predefined subset of widgets that they can choose from for each widget type. create table icon_associations ( widget_type_id number(7,0) references widget_types, icon_id number(7,0) references icons, primary key (widget_type_id, icon_id) ); create table icon_prefs ( user_id number(7,0) references users, widget_type_id number(7,0), icon_id number(7,0), primary key (user_id, widget_type_id), foreign key (widget_type_id, icon_id) references icon_associations ); Pretty simple so far. Let us now assume that if we are displaying an icon to a user who has not set up his preferences, we choose one of the appropriate images associated with the current widget. I'd like to specify the preferred icon to display in such a case, and here's where I run into my problem: alter table icon_associations add ( is_preferred char(1) check( is_preferred in ('y','n') ) ) ; I do not see how I can enforce that for each widget_type there is one, and only one, row having is_preferred set to 'y'. I know that in MySQL, I am able to write a subquery in my check constraint to quickly resolve this issue. This is not possible with Oracle. Is my mistake that this column has no business being in the icon_associations table? If not where should it go? Is this a case where, in Oracle, the constraint can only be handled with a trigger? I ask only because I'd like to go the constraint route if at all possible. Thanks so much for your help, Paul

  Read the article

• How to Map Two Tables To One Class in Fluent NHibernate

  - by Richard Nagle

  I am having a problem with fluent nhiberbate mapping two tables to one class. I have the following database schema: TABLE dbo.LocationName ( LocationId INT PRIMARY KEY, LanguageId INT PRIMARY KEY, Name VARCHAR(200) ) TABLE dbo.Language ( LanguageId INT PRIMARY KEY, Locale CHAR(5) ) And want to build the following class definition: public class LocationName { public virtual int LocationId { get; private set; } public virtual int LanguageId { get; private set; } public virtual string Name { get; set; } public virtual string Locale { get; set; } } Here is my mapping class: public LocalisedNameMap() { WithTable("LocationName"); UseCompositeId() .WithKeyProperty(x => x.LanguageId) .WithKeyProperty(x => x.LocationId); Map(x => x.Name); WithTable("Language", lang => { lang.WithKeyColumn("LanguageId"); lang.Map(x => x.Locale); }); } The problem is with the mapping of the Locale field being from another table, and in particular that the keys between those tables don't match. Whenever I run the application with this mapping I get the following error on startup: Foreign key (FK7FC009CCEEA10EEE:Language [LanguageId])) must have same number of columns as the referenced primary key (LocationName [LanguageId, LocationId]) How do I tell nHibernate to map from LocationName to Language using only the LanguageId field?

  Read the article

• PHP: parse $_FILES[] data in multidimesional array

  - by superUntitled

  I having been looking around for an answer to this and have not found an answer anywhere, I am hoping someone has done this before! I have a form that allows for dynamic duplication of the form fields. The form allows for file uploads and text input, so the data is sent in both $_POST and $_FILES arrays. The the initial set of inputs look like this: <input type="text" name="primary[1][text]" /> <input type="file" name="primary[1][file]" /> <input type="text" class="a" name="secondary[1][text][]" /> <input type="file" name="secondary[1][file][]" /> When duplicated the fields are incremented, they look like this: <input type="text" name="primary[2][text]" /> <input type="file" name="primary[2][file]" /> <input type="text" class="a" name="secondary[2][text][]" /> <input type="file" name="secondary[2][file][]" /> To complicate matters, the "secondary" form fields can also be duplicated (thus the [] at the end of the secondary name array. How can I parse the posted $_FILES array? I have tried something like this: foreach ($_FILES['question'] as $f_num) { echo $f['file']['name']; } but I get an "Undefined index: file... " error.

  Read the article

• Social Networking & Network Affiliations

  - by Code Sherpa

  Hi. I am in the process of planning a database for a social networking project and stumbled upon this url which is a (crude) reverse engineered guess at facebook's schema: http://www.flickr.com/photos/ikhnaton2/533233247/ What is of interest to me is the notion of "Affiliations" and I am trying to fully understand how they work, technically speaking. Where I am somewhat confused is the NetworkID column in the FacebookGroups", "FacebookEvent", and "Affiliations" tables (NID in Affiliations). How are these network affiliations interconnected? In my own project, I have a simple profile table: CREATE TABLE [dbo].[Profiles]( [profileid] [int] IDENTITY(1,1) NOT NULL, [userid] [uniqueidentifier] NOT NULL, [username] [varchar](255) COLLATE Latin1_General_CI_AI NOT NULL, [applicationname] [varchar](255) COLLATE Latin1_General_CI_AI NOT NULL, [isanonymous] [bit] NULL, [lastactivity] [datetime] NULL, [lastupdated] [datetime] NULL, CONSTRAINT [PK__Profiles__1DB06A4F] PRIMARY KEY CLUSTERED ( [profileid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [PKProfiles] UNIQUE NONCLUSTERED ( [username] ASC, [applicationname] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] One profile can have many affiliations. And one affiliation can have many profiles. And I would like to design it in such a way that relationships between affiliations tells me something about the associated profiles. In fact, based on the affiliations that users select, I would like to know how to infer as many things as possible about that person. My question is, how should I be designing my network affiliation tables and how do they operate per my above requirements? A rough SQL schema would be appreciated in your response. Thanks in advance...

  Read the article

• How to set up a functional macro with parameters in C++?

  - by user1728737

  is there a way that I can make this work? Or do I need to use separate files? #include <iostream> // Necessary using namespace std; long double primary, secondary, tertiary; #define long double mMaxOf2(long double min, long double max) { return ((max > min) ? (max) : (min)); } #define long double mMaxOf3(long double Min, long double Max, long double Mid) { long double Mid = (long double mMaxOf2(long double Min, long double Mid)); long double Max = (long double mMaxOf2(long double Mid, long double Max)); return (Max); } int main() { cout << "Please enter three numbers: "; cin << primary << secondary << tertiary; cout << "The maximum of " << primary << " " << secondary << " " << tertiary; cout << " using mMaxOf3 is " << long double mMaxOf3(primary, secondary, tertiary); return 0; } This is the error that I am getting. |20|error: expected unqualified-id before '{' token|

  Read the article

• SQL - Query to display average as either "longer than" or "shorter than"

  - by user1840801

  Here are the tables I've created: CREATE TABLE Plane_new (Pnum char(3), Feature varchar2(20), Ptype varchar2(15), primary key (Pnum)); CREATE TABLE Employee_new (eid char(3), ename varchar(10), salary number(7,2), mid char(3), PRIMARY KEY (eid), FOREIGN KEY (mid) REFERENCES Employee_new); CREATE TABLE Pilot_new (eid char(3), Licence char(9), primary key (eid), foreign key (eid) references Employee_new on delete cascade); CREATE TABLE FlightI_new (Fnum char(4), Fdate date, Duration number(2), Pid char(3), Pnum char(3), primary key (Fnum), foreign key (Pid) references Pilot_new (eid), foreign key (Pnum) references Plane_new); And here is the query I must complete: For each flight, display its number, the name of the pilot who implemented the flight and the words ‘Longer than average’ if the flight duration was longer than average or the words ‘Shorter than average’ if the flight duration was shorter than or equal to the average. For the column holding the words ‘Longer than average’ or ‘Shorter than average’ make a header Length. Here is what I've come up with - with no luck! SELECT F.Fnum, E.ename, CASE Length WHEN F.Duration>(SELECT AVG(F.Duration) FROM FlightI_new F) THEN "Longer than average" WHEN F.Duration<=(SELECT AVG(F.Duration) FROM FlightI_new F) THEN 'Shorter than average' END FROM FlightI_new F LEFT OUTER JOIN Employee_new E ON F.Pid=E.eid GROUP BY F.Fnum, E.ename; Where am I going wrong?

  Read the article

< Previous Page | 31 32 33 34 35 36 37 38 39 40 41 42  | Next Page >