问:
Bulletin board - Database optimisation

This question is a follow on from this
Question

The project and problem

The project I am currently working on is a bulletin board for a large non-profit organisation. The bulletin board will be used to allow inter-office communication within the organisation.

I am building the application and have been having trouble extracting the results that I need from my database because I don't think it is properly normalized and because of limitations in my knowledge of relational database theory and mysql.

I would appreciate input into the design of the board in general and in particular, ways that the database structure can be improved to facilitate efficient queries and help me develop this application and future application faster

Business Logic

The bulletin board will be used in the following way

 Posting bulletins and responses to bulletins

Employees or 'users' in offices around the country will be able to post messages to the bulletin board.Bulletins must be posted to a location and categorised- i'll call these "bulletins".

Users will be able to post any number of replies to any one bulletin and users will be able to reply to their own bulletin - i'll call these 'replies'.

Rating bulletins and replies

Users will be able to either 'like' or 'dislike' a bulletin or a reply and the total number of likes or dislikes will be shown for each bulletin or reply.

Viewing the bulletin board and responses

Bulletins can be displayed chronologically.

 Users can sort bulletins chronologically or chronologically by the latest reply to that bulletin(let me know if you need more explanation)

When a particular bulletin is selected, replies to that bulletin will be displayed chronologically

```<code>-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 16, 2011 at 06:44 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bulletinboard`
--

-- --------------------------------------------------------

--
-- Table structure for table `bbs`
--

CREATE TABLE IF NOT EXISTS `bbs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_locations_id` int(11) NOT NULL,
  `bb_categories_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_date` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=87 ;

--
-- Dumping data for table `bbs`
--

INSERT INTO `bbs` (`id`, `bb_locations_id`, `bb_categories_id`, `users_id`, `title`, `content`, `created_date`, `rank`) VALUES
(83, 8, 28, 44, 'sdaf', 'asdfasdf', 1292712797, 0),
(84, 8, 28, 44, 'asdf', 'asdfasd', 1292875089, 0),
(86, 8, 28, 44, 'Robert is leaving', 'Robert is leaving and going back to the states ', 1294344916, 0);

-- --------------------------------------------------------

--
-- Table structure for table `bb_categories`
--

CREATE TABLE IF NOT EXISTS `bb_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `list_order` varchar(255) NOT NULL,
  `admin` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

--
-- Dumping data for table `bb_categories`
--

INSERT INTO `bb_categories` (`id`, `title`, `description`, `list_order`, `admin`) VALUES
(28, 'Travel', 'Rideshares, proposed trips etc', '1', 1);

-- --------------------------------------------------------

--
-- Table structure for table `bb_locations`
--

CREATE TABLE IF NOT EXISTS `bb_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `post_code` int(11) NOT NULL,
  `list_order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `bb_locations`
--

INSERT INTO `bb_locations` (`id`, `title`, `description`, `address`, `post_code`, `list_order`) VALUES
(8, 'Washington DC', 'asdkf', 'dsf', 0, 1);

-- --------------------------------------------------------

--
-- Table structure for table `bb_ratings`
--

CREATE TABLE IF NOT EXISTS `bb_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bbs_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `like_id` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

--
-- Dumping data for table `bb_ratings`
--

-- --------------------------------------------------------

--
-- Table structure for table `bb_replies`
--

CREATE TABLE IF NOT EXISTS `bb_replies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_id` int(11) NOT NULL,
  `bbs_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_date` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=158 ;

--
-- Dumping data for table `bb_replies`
--

INSERT INTO `bb_replies` (`id`, `users_id`, `bbs_id`, `content`, `created_date`, `rank`) VALUES
(156, 44, 86, 'good ridance i say\r\n', 1294788444, 0),
(157, 44, 86, 'And stay away\r\n', 1294892751, 0);

-- --------------------------------------------------------

--
-- Table structure for table `bb_reply_ratings`
--

CREATE TABLE IF NOT EXISTS `bb_reply_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_replies_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `like_id` tinyint(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=115 ;

--
-- Dumping data for table `bb_reply_ratings`
--

-- --------------------------------------------------------

--
-- Table structure for table `bb_sort_bys`
--

CREATE TABLE IF NOT EXISTS `bb_sort_bys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bb_sort_bys`
--

INSERT INTO `bb_sort_bys` (`id`, `title`, `description`) VALUES
(1, 'Newest', 'Posts are sorted by their creation date'),
(2, 'Popular', 'Posts are sorted by the date of their lates reply, or by post date if they have now replies');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(10) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `permission` int(1) NOT NULL,
  `bb_sort_bys_id` varchar(10) NOT NULL,
  `bb_locations_csv` varchar(255) NOT NULL,
  `defaultLocation` int(11) NOT NULL,
  `bb_categories_csv` varchar(255) NOT NULL,
  `total_bulletins` int(5) NOT NULL,
  `bulletins_per_page` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;
</code>```

答:
Part I

Revised 09 Dec 10 01:00 EST

Looked at your DDL.  Ok. We need to take a step back and organise your database first.  That will solve half your problems (your SQL will be straight-forward; and fast; less indices; no temp tables required).  For a while I thought, aha, you have your columns, it must be stable, but there is no chance.  Top down from scratch, ok.  Have a look at this Entity Relation Diagram (no use working on the Data Model, which is Entities, Relations and Attributes, until we get the ERs right), and check that it is correct.

The way to do that is, answer the following questions (short answers are fine).  These questions are clarifying the Entities and Business Rules.  How you understand databases in general, and your data in particular is crucial.  You have come a long way, on your own, so we can take it from there.

I think ▶this post◀ might be helpful to you, in order to understand the formal stages that should be followed; which we are short-circuiting here.

Most important, totally, and completely, forget about the function and any coding requirements.  Data has to be modelled independent of the application, simply as Data.  Function Modelling is a different science.  First get one right; then get the other right; and the two together play beautiful tunes.  Try jamming them together; doing both tasks at the same time, and they won't even make a suburban garage band.

For brevity, and the sake of anyone reading this, I with use a Closed and Open Section; when an Open item (discussion) is closed, I will make it concise, and move it to the Closed section.  Maintain the numbering, because things sometimes come back to haunt us.  You may wish to do the same, or even delete the discussion on your side.

The links for the pretty pictures are at the end.

Apologies: the editing does not work; sub-numbering is inconsistent

Closed Issues

users.bb_locations_csv is a many-to-many relation between users and locations:

Each of those elements should be an entry in a discrete column, in a discrete row

One users can have many locations and 1 location can have many users is many-to-many

Read ▶this post◀ for a discussion of how that is treated and what stage it is dealt with

At this Logical Stage, that is just a n::n relation, as I have drawn, you can forget about it for now, it will be supplied, simply, when we get to the physical Stage.

Trust me, I will provide code that in no more complex than ...WHERE IN () for your declared purpose.

On second thought, if I break your fingers, you will type even slower, so I better not

Ok, your app is browser based, and the page is dynamic (my advice was for static pages that need to be touched up); go ahead with check boxes.

.

users.bb_categories_csv is many-to-many relation between users and categories

Ditto.

.

Confirmed: a bulletin (bbs) does not exist without an user; an user issues a bulletin, and that starts the whole cycle; then invites replies and ratings.

3.1 Confirmed: There is really only one bulletin board and it does not exist as a Thing in the database.

3.2 Confirmed: that the org will never have more than one bulletin board, and the classifications and categorisations are all adequately handled by the Category table/function

Deleted.

Confirmed: The difference between bulletins and replies is that replies are dependent on a bulletin to exist, they do not have a title and they are not categorised by location or category because they are dependent on the bulletin itself to exist.

Deleted.

Comments noted. Resolved.

7.1. For each single bulletin submitted by another user, each user can post more than one reply.

7.2. For each single bulletin submitted by an user, that user can post one, or more than one reply.

7.3. Deleted.

7.4. Deleted.

The Data Model now allows more than one reply per user per bulletin; including the User who submitted the bulletin.

.

8. Confirmed: each user can post at most one rating to a bulletin (which can be revoked/changed)

.

9. Confirmed: each user can post at most one rating to a reply (ditto)

10.1. Given: username comes from the organisation and is the unique name that identifies employees. For example emails are username@organisation.org - authentication is done with ldap and this is required in order to connect an retrieve other information about the employees

Confirmed: UserName is an excellent Identifier

10.2. Confirmed: FirstName, LastName ... BirthPlace, etc remain as (the traditional) columns for ensuring People are not duplicated.

.

11. Given: At the moment we can Identify our offices by casual names which are generally know within the organisation, since we only have about 3 main offices and many field offices. So examples would be Washington DC or virginia field office. In total I think we will try and keep the total below 20. I want to record the exact address of each location as well because that could be used to uniquely identify offices to users.

Provided: StateCode+Town as PK; IsMainOffice as boolean.

.

12. Confirmed: Description and Name for Category are required.

.

13. Given:Users will not be able to post to some categories. Only users with sufficiently high rights will have the right to post to certain categories.

Provided: Permission in User, Location, Category is a method of evaluating such rights.

.

14. Confirmed: Location.Administrator is UserId of admin for the Location.

.

15. Given: There will only ever be a need for a like or a dislike. I don't think there needs to be a neutral position because this is the same as just not voting? Liking seems more relevant to bulletin replies that posts to be honest. Ie 'i see your response and instead of writing my own I will just agree with you - the existing bulletin board is somewhat of a social aspect in the orgainsation and I think liking and disliking/agreeing and dissagreeing creates a level of controversy that encourages participation. However liking or disliking a bulletin may not always be entirely appropriate.

15.1  Provided: Like as boolean in BulletinRating and ResponseRating. This will require interpretation on every access.

15.2. When it is no longer a boolean, it can be changed to a RatingCode, and implemented as a Lookup table.  The names are then determined by Joins, and interpretation is eliminated.  I drew this in the First Data Model, so that you could see what I meant
15.3. Removed in the Second Data Model.

.

16. Confirmed: each user has a home Location (other than the list of Locations that they are interested in).

.

17. Confirmed: Permission as per (13).

.

18. Confirmed: Further Permissions may be be required, as per Data Model.

18.1. If you do this now, you won't have to worry about when organisation decides to prevent a certain Person from posting Responses or Bulletins, or Rating them; and wants that feature implemented yesterday.

18.2. Even if you do not implement it, leave gaps between the values you do implement.

.

19 Confirmed: a Bulletin is about a Location.

19.1. Confirmed: There are no Bulletins without a Location

19.2. Confirmed: There are no Bulletins without a Location.

19.3 Confirmed: There are no Bulletins without a User (declarative).  But so far we have no way of constraining that User; therefore any User can inset a Bulletin for any Location ( you could constrain it in code, eg. to Locations each User Is Interested In.

19.4 Confirmed: There are no BulletinRatings without a Bulletin and a rating User.

19.5 Confirmed: There are no Responses without a Bulletin.

19.4 Confirmed: There are no ResponseRatings without a Response and a rating User.

19.7. But, there can be Users, Locations, andCategories`, independently.

.

20. If you do not mind, I will provide naming conventions, etc.  They should be self explanatory, and the value will show up only when you start coding SQL.  Please ask, if anything isn't.  For starters, all names are singular. Mixed Case is easier to read (you are supposed to use capitals for SQL language).

20.1. My experience is table_name as opposed to tableName are really technie forms, and users do not like them; Consistent mixed case is liked by everyone.  It is one of those things that is impossible to change, so choose carefully.

.

21.  For your need to group tables together, which is good, keep in mind that that is a Physical issue.  At the Logical Data Model level, the tables have normal names, uncluttereded by physical issues.  Imagine that the physical tables are prefixed with something like (and please use capitals for this):

- REF_ for reference (such as User) and lookup tables

- BUL_ for Bulletin system

.

I am not able to name tables with uppercase letters? Im not sure why.  I don't know why I can't have uppercase table names. Is it to do with using MyIsam database tables?

The universal convention is that SQL Language is expressed in upper case; every report and admin tool I have ever used generates such SQL code.  So we can't use upper case.  Lower case or mixed case only.  So the choices boil down to table_name or TableName; we need a separator of some kind.  For reasons already provided, I strongly recommend mixed case, capiatlised, and not the OO style with the leading letter uncapitalised.

.

22. rank (all) can be derived directly from the database (remember, do not worry about the code during Data Modelling).  If you store it, it is a Normalisation error; a duplicated column; which has to be kept up-to-date; which can get out of synch with the derived value; which is called an Update Anomaly.  Fifth Normal Form eliminates Update Anomalies.  That is my minimum level of Normalisation, so that is what you will get from me.

22.1.  I am not interfering with the sort order or popularity issue at all; in fact, by the sounds of it, you haven't closed that functionality.  I am only taking redundant data, the rank column, out, as part of the Normalisation process.

22.2.  Here's a ▶Quick Tutorial◀ on the RANK() operator (as it is commonly known).  It is not ANSI SQL; it is an Oracle and MS extension.  However it is not required if you understand Subqueries, which is why Sybase does not have it. I doubt MySQL has it, so you need to get your head around it.  Understanding Scalar Subqueries is a pre-requisite.  Sybase syntax, so whack your semi-colons in, etc.  Feel free to ask specific questions.

.

I have never seen that approach of writing Rank = (SELECT.... Is that the same as (SELECT ...) as Rank?

I have posted a separate Answer for that.

.

22.3.  Needing to understand why, is no problem at all.  Only children blindly follow simple rules, and you are certainly not one of them.

.

23. Confirmed: users.total_bulletins is redundant; it can be derived. Removed.

.

24. All your PKs are Ids.  Haven't you gotten tired of getting lost in the code yet ? Forget about sticking Idiot PKs on everything that moves, let's find out How your users Identify their Entities; what Entities are truly Independent, and the other which depend on Independent Entities.

24.1. Never use Id or any such form.  Where it is a PK, use the full form.

24.2. Call location_id, location_id, wherever it is, including the PK table.  The exception is when you need to show the role.  This will become clear in the Data Model.

.

25. You have no Declarative Referential Integrity, no Defined Foreign keys.  That is bad news for many different reasons.  Once these questions are clairified, please add them in.  DRI means that as much as possible, if not all, Integrity is Declared in SQL.  ISO/IEC/ANSI SQL standard allows for this, but the freeware end of the market does not provide the standard, and is slowly catching up.  It means the server will not allow a row in the FK table to be added unless the PK exists in the parent table.  MySQL recently provided DRI for Foreign Keys.  For FKs, refer to ▶this article◀.

25.1. For CHECK constraints and RULES, you will have to implement those in code.

my foreign keys are like, users-id(fk) = users.id(pk) Im not sure how to add them other that what I have done but will certainly do so once I know how to.

That's not adding them into your db; that's merely referencing columns in a WHERE clause in Data Manipulation Language, not Data Definition Languge. Adding them, so that they function at the db/server level, means declaring them in DDL, as per the linked article.  Then MySQL will stop a row from being inserted to a child table (FK) where the parent PK does not exist.  That is Referential Integrity.  If it is declared in DDL, it is Declarative Referential Integrity.

In addition to enforcement of RI, everyone can see the definition: report tools can be used by the users to access and report from the db, without having to get someone to code a report.

Yes, as far as I know.  Confirmed at  ▶this site◀.  The code I have provided for the subquery uses DRI, so we can test that and get it out of the road early. You have to check for your specific version of MySQL.

Twenty-Five. Comments Noted. I ama not a MySQL specialist.  Yes, those are the issues you have to figure out for yourself.  In general, from my perusing, MySQL is legless; for anything SQL-ish, you need InnoDB.

But do not let that hold you back.  Use Engine=MySQL for now, without the Declarative SQL, and keep going with both the Data Model and the Subquery.  Work on InnoDB in the background.

To be clear, the DDL I have provided should work for MyISAM (and "do nothing" in the DRI department, until you get InnoDB).

.

27. Given: I have rethought the sorting requirements for bulletin. Users could sort chronologically- easy,makes sense. Users could sort bulletins by the date of the latest reply to the bulletin. Then we can forget about rank and it should be really easy to sort bulletins chronologially by the time of their last response? What are your thoughts.

Yes.  that is sensible and quite common, most people understand chronological order.  You will have to mess with the filters they choose in the search window (choose: Location or list; choose: Category or list; choose: My Bulletins or all).

Open Issues

(Nil)

Data Model

Ok, assuming you do not have issues with the ERD, and implementing all Closed Issues, I have modelled the data, and prepared a Fifth Data Model 09 Dec 10 for your review.  I definitely need much more feedback, questions, etc, on this.  I am experiencing difficulty accepting that it is done.  Probably best to start writing real code for your problem areas.

Links

▶Link to IDEF1X Notation◀ You really need to read and understand this, before you read the Data Model.

▶Link to Fifth Bulletin Data Model◀ The Entity Relation Diagram is on the first page, followed by the Data Model.

The Keys are pretty much straight IDEF1X (except for UserId which I provided as a counterpoint); which means purse Relational Keys.  Un-enhanced and not optimised for Physical considerations. Before you baulk at them, first notice them, register them, and evaluate them.  Of course we can add Idiot keys, but before we do that, let's make sure we understand what we are going to lose.

Notice the Identifiers (solid lines) as per the Notation document.  The spine, the vertebrae of the system is Location ... Bulletin ... Response.

Notice that Keys actually implement many Business Rules.

Notice the Natural Hierarchy that I have rendered.  See if there is any meaning in it for you.

The VerbPhrases are really important; see if they mean anything.

Comments re First Data Model and Responses

One question I have is that the primary key of the location will be used to form the child primary key?(they are joined by a solid line) I don't really understand that concept

Yes. the PK for Location (above the line) is (StateCode, Town). That PK the two columns together, a compound key,  is migrated from Location to Bulletin anyway, as an FK (bold). We are additionally using it to form the Bulletin PK (above the line).

If and when we need a Surrogate key, we will add it.  For now, we are working out the Identifiers.  So the question to contemplate is:

What is a good Identifier for Bulletin ?, what do your users naturally use to Identify a Bulletin ...

"have you seen the bulletin from Virginia FO yesterday ?",

"Sally from Washington sure writes good bulletins", etc.

or why that relationship does not exist between the user and the bulletin?

Well, that relation cannot exist between User and Bulletin, but a relation exists, the dotted line, meaning UserId is an FK in Bulletin(bold), but not used it to form its PK (below the line).

Or do you mean: the User is a strong Identifier for Bulletin (and therefore should be used to form the BulletinPK, therefore the line should be solid) ?

Fine. Excellent. That is what modelling re Identifiers is all about. That clears up an area that I did not like, in that we had non-unique indices.  That resolves my issue as well.

As per intention stated further above, since I have now shown Rating as a table and what the rendering would be, once, I shall remove it

I think Permission should be an Entity.

Bulletin PK is now (StateCode, Town, UserId, SequenceNo).  To be clear, SequenceNo is within StateCode, Town, UserId: it will be 5 for Sally's 5th bulletin re MO/Billngs FO.

Note that user Settings BulletinsPerPage,etc, are 1::1 with User, so they are in User; child table would be incorrect.

Typographical errors corrected.

Comments re Second Data Model and Responses

The PKs for both Bulletin and Response have been changed to reflect (7). BulletinNo and ResponseNo have been replaced with BulletinDate and ResponseDate (which used to be CreatedDate), in order to allow multiple replies per User per Bulletin.

Comments re Third Data Model and Responses

Trust you had a good break.

At least 30 years ago (that I am aware of), the giants in the industry had this debate.  Names are always singular.  Tables are nouns.  VerbPhrases are verbs. This is not limited to db naming conventions, it applies to documents, theses, dissertations, etc.  You may have 5 conclusions at the end of the the doc, but the section or chapter title, in both the ToC and the top of the page is "Conclusion".

After fighting them all the way through Uni, as soon as I started my first paid programming job, and saw the importance of the rules in the real world, as opposed to the theoretical arguments we had in college, I gave it up as a waste of time.  All that time and energy I wasted was released to do productive work.  Since then, I don't question the giants; I just accept.  That their minds are greater than mine.  It is like accepting Standards, or behaving within the law, or God.  I have no really, really good reasons for doing anything illegal.

Anyway, the ease of languaging (discussion, SQL, documentation) that is supported by such rules cannot be adequately explained; as you write more and more SQL code, it will become clear.

You are always free to use whatever you want.  I deliver singular only.

Fine with me.

But you need to keep in mind, those two elements, in the identified sequence (ala non-PK Unique Index, or Alternate Key) are universally required to establish Uniqueness for a Person.  Removing them will result in two things. First, you will no longer be able to identify uniqueness across Users (and thus you may have duplicate rows). Second, the AK becomes non-unique, an Inversion Entry.

The point is (contrary to one of the posts), any column that is 1::1 with theUserPK, should reside inUser.  All preference settings.  Since we cleaned up theInterestedLocationsandInterestedCategories, I know only of onlyBulletinsPerPageremaining; but I am sure there are others. IsPreference2is an eg. of a boolean;NumPreference3is an eg. of an Integer.  Etc.  You can tell me what the real Preferences are.

(Let's try that in plural: ... any column that is 1::1 with theUsersPK, should reside inUsers.  Just doesn't do it for me, I get hung up on the broken English, and I am a bit precious about my mother tongue.)

Data Model Updated.

Excellent.  Let me know when you are comfortable with that, and I will give you the Physical Model.

How about the VerbPhrases ?

Comments re 06 Dec 10 20:38 EST (Small Updates)

.

28. Where there is only one occurrence of PK as an FK, of course, the FK column name is the same as the PK column name.  However, when there is more than one occ of the FK (take a look at ResponseRating), there are three UserIds), we need to differentiate them.  In IDEF1X terminology this is called Roles.  The Role of the User who issued the Bulletin is Issuer, and so on.  Obviously it is better to use that name, and keep it consistent throughout the hierarchy (not UserId in Bulletin and then when we get to Response, where there are two, and a differentiation is demanded, change it to IssuerId.  I thought you might have a problem with that; in the early stages, the usage is Issuer.UserId so that it is absolutely clear the it is UserId as an FK, and the Role is Issuer; when we get to the physical model, it gets simplified to IssuerId.

Likewise, we have many DateTime columns (Date for short if you like; otherwise Dtm), that need to be differentiated.

.

29. Did the IDEF1X Notation doc not make sense ?

The PK for each table is above the line, in the specified order.

Remember we are carrying the PKs of the parent tables anyway, and if there is meaning, using those FKs to form the child PK.

For Bulletin:

The Location FK (StateCode, Town) for which it is Issued

The UserId of the Issuer

and DateTime it was Issued, to make it unique.

therefore (StateCode, Town, IssuerId, BulletinDate)`

To delete all ResponseRatings for this Bulletin, use WHERE = on those four Bulletin columns.

.

30.  Because (State, Town) is the PK of Location, carrying wherever. And it forms part of the Bulletin PK, so any dependent tables carry those columns because they are carrying the Bulletin PK.

Look for the coloured Tabs (This version only)

  .

  32.  Those are Verb Phrases.  The way to read them is detailed in the Notation doc.  It appears you have a good handle on it.  It is really important to get the table names (and the Verb Phrases) right, because change is difficult after implementation. If you tell me Office is better than Location, that's fine with me.

Read: Office Is Activated By Bulletin

Feel free to supply another Verb Phrase.

  AFAIC, the Office is dead to the rest of the org, and only comes alive on their radar (is activated by) the issue of a Bulletin.

  I realise it sounds silly here, but ignore that for a moment, something along the lines of "Office expresses its aliveness; advertises its activity, by issuing a Bulletin".

Have a quiz at Mark's Sensor Data Model, for some nice Verb Phrases.

We had previously identified that (State, Town) is the PK, I will leave that as is Refer to (38) for change.

.

33. Worth discussion.  Yes, if you are going to display it when (eg) displaying Responses, and the users understand UserName. No, if it is 30 bytes, and there is also an unique 4 byte UserId.  The idea is to make these choices consciously, aware of what you are giving up, when you eventually decide that some 6 column 30-byte key is too cumbersome to migrate to the children.

I did state at the outset, I would use UserId as a typical Id Pk, because it is carried/migrated to several child tables.

We can leave how that is created for later.  But it is a pure Surrogate PK.

.

34. No problem. Category already has it. I'll change Order to ListOrder.

.

35.  Sure.  Based on what I have read and heard, I am quite happy with it. But I would like more back-and-forth to achieve some confidence, before you write code.  Alternately, view it as a learning experience, and accept that the model and code may change later.   Would you like me to produce the Physical now ?  If you give me any and all corrections, I will publish the next version.  I am expecting preferences in User.  Also, quickly run through the functions and check that you have all the columns you need.

Do look at some of the other answers, for the purpose of learning, and interest.

.

36. Joins.  You just join on four three columns as opposed to one.  SQL is cumbersome with joins, and the new syntax which was supposed to make it easier, is actually more cumbersome.  My coders never write joins: we save time and typos.  I have a proc that given two or more tables, will generate the code with all the columns and joins.  I don't know enough of MySQL to convert that for you.

Data Model Updated.

.

Comments re 08 Dec 10 20:49, Fourth Data Model and Responses

.

Check the previous section immediately above, there are small updates.

IDEF1X: Your speed is fine.

Note the child always "inherits" the Parent PK, as an FK (either solid or broken line), otherwise there is no Relation between them.  By using these columns that exist in the child anyway, to form the child PK, we carry the meaning (and that is the difference between solid and broken).  And thus we do not need to look for an independent Identifier for the child.  The Relational power in this method will become clear later, when you are coding.

The section we are dealing with is about Identifiers: natural vs unatural; meaningful vs meaningless.  Later you will see how we can use the Relational capability of the engine, when the child PK is formed from the parent PK. (Isn't your surname the same as your father's ?)

It is also important to understand Relational databases and their capability.  That is lost when we approach the database (eg) from an OO perspective, and treat it as a location to make our classes "persistent".  Therefore, we will try to learn and use Relational terms.  It gets difficult when you go to France and expect that they speak American, and use the same currency; learn to speak 10 words of French, and they welcome you with open arms, and you'll have quite a different experience with the locals.

Anyway, go ahead with implementing the model.  Just realise we will probably make a change at some point.  Save all your DDL.  Save all your test data as insert statements or as a table backup or character format export (no idea what MySQL can/cannot do in this area).
.

37.1. Handled, the n::n Relation with Office & Category.  You will only "see" that when we get to the Physical Model.

37.2. Done.

37.3  Done.

.

38.  Excellent.  Shorter as well. Note they will never be able to have two Offices in the same Zip Code.  NUMERIC(5,0) is good, but I thought the US was moving towards 7 digits.  Doesn't matter, you can figure it out; it is an excellent PK for Office.  Now this column, which was part of Address, probably ZipCode, has been elevated to a higher purpose, without duplication; since we are carrying it in 5 child tables, and we want the PK name to be clear, as per previously explained conventions, we will call it OfficeCode; OfficeZipCode might be silly.

We need an Unique Index on Name to ensure they do not add two Offices with the same name.  Note, for explanation purposes, this is is actually the logical key of Office, replacing (StateCode, Town), and it remains so.

I still think you may need StateCode and Town as a quick reference (other than sitting somewhere in Address)

Data Model updated, Fifth now available for review.  You did not state your preference, for ...Date vs ...Dtm.  I am going with the latter, as it is more spceific, identifying the time component as well.  Easy to change.

This Answer has reached maximum length.  Continued in "Part II"

Title: Bulletin board - Database optimisation

Body:
This question is a follow on from this
Question

The project and problem

The project I am currently working on is a bulletin board for a large non-profit organisation. The bulletin board will be used to allow inter-office communication within the organisation.

I am building the application and have been having trouble extracting the results that I need from my database because I don't think it is properly normalized and because of limitations in my knowledge of relational database theory and mysql.

I would appreciate input into the design of the board in general and in particular, ways that the database structure can be improved to facilitate efficient queries and help me develop this application and future application faster

Business Logic

The bulletin board will be used in the following way

 Posting bulletins and responses to bulletins

Employees or 'users' in offices around the country will be able to post messages to the bulletin board.Bulletins must be posted to a location and categorised- i'll call these "bulletins".

Users will be able to post any number of replies to any one bulletin and users will be able to reply to their own bulletin - i'll call these 'replies'.

Rating bulletins and replies

Users will be able to either 'like' or 'dislike' a bulletin or a reply and the total number of likes or dislikes will be shown for each bulletin or reply.

Viewing the bulletin board and responses

Bulletins can be displayed chronologically.

 Users can sort bulletins chronologically or chronologically by the latest reply to that bulletin(let me know if you need more explanation)

When a particular bulletin is selected, replies to that bulletin will be displayed chronologically

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 16, 2011 at 06:44 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bulletinboard`
--

-- --------------------------------------------------------

--
-- Table structure for table `bbs`
--

CREATE TABLE IF NOT EXISTS `bbs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_locations_id` int(11) NOT NULL,
  `bb_categories_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_date` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=87 ;

--
-- Dumping data for table `bbs`
--

INSERT INTO `bbs` (`id`, `bb_locations_id`, `bb_categories_id`, `users_id`, `title`, `content`, `created_date`, `rank`) VALUES
(83, 8, 28, 44, 'sdaf', 'asdfasdf', 1292712797, 0),
(84, 8, 28, 44, 'asdf', 'asdfasd', 1292875089, 0),
(86, 8, 28, 44, 'Robert is leaving', 'Robert is leaving and going back to the states ', 1294344916, 0);

-- --------------------------------------------------------

--
-- Table structure for table `bb_categories`
--

CREATE TABLE IF NOT EXISTS `bb_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `list_order` varchar(255) NOT NULL,
  `admin` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;

--
-- Dumping data for table `bb_categories`
--

INSERT INTO `bb_categories` (`id`, `title`, `description`, `list_order`, `admin`) VALUES
(28, 'Travel', 'Rideshares, proposed trips etc', '1', 1);

-- --------------------------------------------------------

--
-- Table structure for table `bb_locations`
--

CREATE TABLE IF NOT EXISTS `bb_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `post_code` int(11) NOT NULL,
  `list_order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

--
-- Dumping data for table `bb_locations`
--

INSERT INTO `bb_locations` (`id`, `title`, `description`, `address`, `post_code`, `list_order`) VALUES
(8, 'Washington DC', 'asdkf', 'dsf', 0, 1);

-- --------------------------------------------------------

--
-- Table structure for table `bb_ratings`
--

CREATE TABLE IF NOT EXISTS `bb_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bbs_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `like_id` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=68 ;

--
-- Dumping data for table `bb_ratings`
--

-- --------------------------------------------------------

--
-- Table structure for table `bb_replies`
--

CREATE TABLE IF NOT EXISTS `bb_replies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `users_id` int(11) NOT NULL,
  `bbs_id` int(11) NOT NULL,
  `content` text NOT NULL,
  `created_date` int(11) NOT NULL,
  `rank` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=158 ;

--
-- Dumping data for table `bb_replies`
--

INSERT INTO `bb_replies` (`id`, `users_id`, `bbs_id`, `content`, `created_date`, `rank`) VALUES
(156, 44, 86, 'good ridance i say\r\n', 1294788444, 0),
(157, 44, 86, 'And stay away\r\n', 1294892751, 0);

-- --------------------------------------------------------

--
-- Table structure for table `bb_reply_ratings`
--

CREATE TABLE IF NOT EXISTS `bb_reply_ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bb_replies_id` int(11) NOT NULL,
  `users_id` int(11) NOT NULL,
  `like_id` tinyint(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=115 ;

--
-- Dumping data for table `bb_reply_ratings`
--

-- --------------------------------------------------------

--
-- Table structure for table `bb_sort_bys`
--

CREATE TABLE IF NOT EXISTS `bb_sort_bys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bb_sort_bys`
--

INSERT INTO `bb_sort_bys` (`id`, `title`, `description`) VALUES
(1, 'Newest', 'Posts are sorted by their creation date'),
(2, 'Popular', 'Posts are sorted by the date of their lates reply, or by post date if they have now replies');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(10) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `permission` int(1) NOT NULL,
  `bb_sort_bys_id` varchar(10) NOT NULL,
  `bb_locations_csv` varchar(255) NOT NULL,
  `defaultLocation` int(11) NOT NULL,
  `bb_categories_csv` varchar(255) NOT NULL,
  `total_bulletins` int(5) NOT NULL,
  `bulletins_per_page` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=45 ;


来源: stackexchange

问:
Error when executing an ionic 2 Android app on prod mode , cannot find module ngfactory
We are having this error when executing and ionic2 app using the --prod flag

And the screen gets white after the splashscreen load.

The command we are launching is :

ionic run android --prod

We are using a device to the deployment, a Nexus 5x (the same behaviour with emulators)

ionic info:

```<code>apache cordova 6.4.0
Ionic Framework Version: 2.1.0
Ionic CLI Version: 2.2.1
Ionic App Lib Version: 2.2.0
Ionic App Scripts Version: 1.2.0
ios-deploy version: Not installed
ios-sim version: Not installed
OS: Windows 7
Node Version: v6.5.0
Xcode version: Not installed
</code>```

Cordova plugins:

Those are our dependencies on package.json:

```<code>"dependencies": {
"@angular/common": "2.1.1",
"@angular/compiler": "2.1.1",
"@angular/compiler-cli": "2.1.1",
"@angular/core": "2.1.1",
"@angular/forms": "2.1.1",
"@angular/http": "2.1.1",
"@angular/platform-browser": "2.1.1",
"@angular/platform-browser-dynamic": "2.1.1",
"@angular/platform-server": "2.1.1",
"@ionic/storage": "1.1.6",
"angular2-google-maps": "0.16.0",
"ionic-angular": "2.1.0",
"ionic-native": "2.5.1",
"ionicons": "3.0.0",
"localforage": "1.4.3",
"localforage-cordovasqlitedriver": "1.5.0",
"rxjs": "5.0.0-beta.12",
"saml2-js": "1.11.0",
"tslint": "4.0.0",
"zone.js": "0.6.26"
}
</code>```

And our devDependencies:

```<code>"devDependencies": {
"@ionic/app-scripts": "^1.2.2",
"@types/jasmine": "2.5.38",
"@types/node": "7.0.4",
"angular-cli": "1.0.0-beta.21",
"codelyzer": "2.0.0-beta.1",
"electron": "1.4.12",
"electron-builder": "11.2.3",
"electron-packager": "8.5.0",
"jasmine-core": "2.5.2",
"karma": "1.3.0",
"karma-chrome-launcher": "2.0.0",
"karma-cli": "1.0.1",
"karma-jasmine": "1.0.2",
"karma-mocha-reporter": "2.2.1",
"karma-remap-istanbul": "0.2.1",
"sw-toolbox": "3.5.1",
"typescript": "^2.0.9"
}
</code>```

tsconfig.json content:

```<code>{
  "compilerOptions": {
    "allowSyntheticDefaultImports": true,
    "declaration": false,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "lib": [
      "dom",
      "es6"
    ],
    "module": "es2015",
    "moduleResolution": "node",
    "sourceMap": true,
    "target": "es5",
    "baseUrl": ".",
    "paths": {
      "@app/*": [ "src/*" ]
    },
    "types": [ "node" ]
  },
  "include": [
    "src/**/*.ts"
  ],
  "exclude": [
    "node_modules"
  ],
  "compileOnSave": false,
  "atom": {
    "rewriteTsconfig": false
  }
}
</code>```

As you can see, we have updated ionic-script to the latests , just to check if the problem has solved, but with no luck.

With the same stack and the 1.2.0 version of @ionic/app-scripts, seems that the ngfactory files are generated, but we have another error (at runtime):

Zoom:

We saw on github an issue regarding this error, but the bug was fixed recommending to update to the 1.2.1 version, and again appeared the error of "Cannot find module ..ngfactory"

https://forum.ionicframework.com/t/error-after-updating-app-scripts-to-1-2-0/84207

https://github.com/driftyco/ionic-app-scripts/issues/836

Any idea of why aren't the ngfactory files being generated inside the bundle with those versions?

Any help should be appreciated, as this is the latests step to put an awesome and very complete app in production, to reduce the load time at the beggining.
Thanks in advance for your inestimable help.

Regards

答:
We have found the problem. Has been solved just doing a couple of things:

1) Update package.json with these dependencies:

```<code>  "dependencies": {
    "@angular/common": "2.4.8",
    "@angular/compiler": "2.4.8",
    "@angular/compiler-cli": "2.4.8",
    "@angular/core": "2.4.8",
    "@angular/forms": "2.4.8",
    "@angular/http": "2.4.8",
    "@angular/platform-browser": "2.4.8",
    "@angular/platform-browser-dynamic": "2.4.8",
    "@angular/platform-server": "2.4.8",
    "@ionic/storage": "1.1.6",
    "ionic-angular": "2.3.0",
    "ionic-native": "2.5.1",
    "ionicons": "3.0.0",
    "localforage": "1.4.3",
    "localforage-cordovasqlitedriver": "1.5.0",
    "angular2-google-maps": "0.16.0",
    "saml2-js": "1.11.0",
    "rxjs": "5.0.1",
    "sw-toolbox": "3.4.0",
    "zone.js": "0.7.2"
  },
  "devDependencies": {
    "@ionic/app-scripts": "1.2.2",
    "typescript": "2.0.9",
    "tslint": "4.0.0"
  },
</code>```

2) Removing this configuration we had on package.json:

```<code>"config": {
    "ionic_bundler": "webpack",
    "ionic_source_map_type": "#inline-source-map"
  }
</code>```

By default ionic 2 uses webpack, so was redundant.
The only problem is that removing this line:

```<code>"ionic_source_map_type": "#inline-source-map"
</code>```

we can't debug using typescript directly on the browser, but we will generate different config based on the environment.

The good news are that our app runs on Android Nexus 5X in only 3 seconds instead of 12 , the javascript bundle is reduced from 14Mb to 2Mb, so it's worth it! Thanks ionic 2 guys for that awesome framework and those awesome scripts. The application performs very well on a medium-class mobile device, so for us it's close to native, and only developed in 3 months for ios (not tested yet) and android, we are very happy with the result.

Hope this post will help somebody

Have a Good day!

ContentLicense: CC BY-SA 3.0

Body:
We are having this error when executing and ionic2 app using the --prod flag

And the screen gets white after the splashscreen load.

The command we are launching is :

ionic run android --prod

We are using a device to the deployment, a Nexus 5x (the same behaviour with emulators)

ionic info:

apache cordova 6.4.0
Ionic Framework Version: 2.1.0
Ionic CLI Version: 2.2.1
Ionic App Lib Version: 2.2.0
Ionic App Scripts Version: 1.2.0
ios-deploy version: Not installed
ios-sim version: Not installed
OS: Windows 7
Node Version: v6.5.0
Xcode version: Not installed

Cordova plugins:

Those are our dependencies on package.json:

"dependencies": {
"@angular/common": "2.1.1",
"@angular/compiler": "2.1.1",
"@angular/compiler-cli": "2.1.1",
"@angular/core": "2.1.1",
"@angular/forms": "2.1.1",
"@angular/http": "2.1.1",
"@angular/platform-browser": "2.1.1",
"@angular/platform-browser-dynamic": "2.1.1",
"@angular/platform-server": "2.1.1",
"@ionic/storage": "1.1.6",
"angular2-google-maps": "0.16.0",
"ionic-angular": "2.1.0",
"ionic-native": "2.5.1",
"ionicons": "3.0.0",
"localforage": "1.4.3",
"localforage-cordovasqlitedriver": "1.5.0",
"rxjs": "5.0.0-beta.12",
"saml2-js": "1.11.0",
"tslint": "4.0.0",
"zone.js": "0.6.26"
}

And our devDependencies:

"devDependencies": {
"@ionic/app-scripts": "^1.2.2",
"@types/jasmine": "2.5.38",
"@types/node": "7.0.4",
"angular-cli": "1.0.0-beta.21",
"codelyzer": "2.0.0-beta.1",
"electron": "1.4.12",
"electron-builder": "11.2.3",
"electron-packager": "8.5.0",
"jasmine-core": "2.5.2",
"karma": "1.3.0",
"karma-chrome-launcher": "2.0.0",
"karma-cli": "1.0.1",
"karma-jasmine": "1.0.2",
"karma-mocha-reporter": "2.2.1",
"karma-remap-istanbul": "0.2.1",
"sw-toolbox": "3.5.1",
"typescript": "^2.0.9"
}

tsconfig.json content:

{
  "compilerOptions": {
    "allowSyntheticDefaultImports": true,
    "declaration": false,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "lib": [
      "dom",
      "es6"
    ],
    "module": "es2015",
    "moduleResolution": "node",
    "sourceMap": true,
    "target": "es5",
    "baseUrl": ".",
    "paths": {
      "@app/*": [ "src/*" ]
    },
    "types": [ "node" ]
  },
  "include": [
    "src/**/*.ts"
  ],
  "exclude": [
    "node_modules"
  ],
  "compileOnSave": false,
  "atom": {
    "rewriteTsconfig": false
  }
}

As you can see, we have updated ionic-script to the latests , just to check if the problem has solved, but with no luck.

With the same stack and the 1.2.0 version of @ionic/app-scripts, seems that the ngfactory files are generated, but we have another error (at runtime):

Zoom:

We saw on github an issue regarding this error, but the bug was fixed recommending to update to the 1.2.1 version, and again appeared the error of "Cannot find module ..ngfactory"

https://forum.ionicframework.com/t/error-after-updating-app-scripts-to-1-2-0/84207

https://github.com/driftyco/ionic-app-scripts/issues/836

Any idea of why aren't the ngfactory files being generated inside the bundle with those versions?

Any help should be appreciated, as this is the latests step to put an awesome and very complete app in production, to reduce the load time at the beggining.
Thanks in advance for your inestimable help.

Regards

Body:
We have found the problem. Has been solved just doing a couple of things:

1) Update package.json with these dependencies:

  "dependencies": {
    "@angular/common": "2.4.8",
    "@angular/compiler": "2.4.8",
    "@angular/compiler-cli": "2.4.8",
    "@angular/core": "2.4.8",
    "@angular/forms": "2.4.8",
    "@angular/http": "2.4.8",
    "@angular/platform-browser": "2.4.8",
    "@angular/platform-browser-dynamic": "2.4.8",
    "@angular/platform-server": "2.4.8",
    "@ionic/storage": "1.1.6",
    "ionic-angular": "2.3.0",
    "ionic-native": "2.5.1",
    "ionicons": "3.0.0",
    "localforage": "1.4.3",
    "localforage-cordovasqlitedriver": "1.5.0",
    "angular2-google-maps": "0.16.0",
    "saml2-js": "1.11.0",
    "rxjs": "5.0.1",
    "sw-toolbox": "3.4.0",
    "zone.js": "0.7.2"
  },
  "devDependencies": {
    "@ionic/app-scripts": "1.2.2",
    "typescript": "2.0.9",
    "tslint": "4.0.0"
  },

2) Removing this configuration we had on package.json:

"config": {
    "ionic_bundler": "webpack",
    "ionic_source_map_type": "#inline-source-map"
  }

By default ionic 2 uses webpack, so was redundant.
The only problem is that removing this line:

"ionic_source_map_type": "#inline-source-map"

we can't debug using typescript directly on the browser, but we will generate different config based on the environment.

The good news are that our app runs on Android Nexus 5X in only 3 seconds instead of 12 , the javascript bundle is reduced from 14Mb to 2Mb, so it's worth it! Thanks ionic 2 guys for that awesome framework and those awesome scripts. The application performs very well on a medium-class mobile device, so for us it's close to native, and only developed in 3 months for ios (not tested yet) and android, we are very happy with the result.

Hope this post will help somebody

Have a Good day!


问:
What is the highest Ruby version supported by Rails v4.1.16?
In other words, can I run Rails 4.1.16 on Ruby 2.4? If not, can I run it on Ruby 2.3?</p>

答:
seems 2.4 should work with rails 4.x

Rails generally stays close to the latest released Ruby version when
it's released:

```<code>....
Rails 4 prefers Ruby 2.0 and requires 1.9.3 or newer.
...
</code>```

http://guides.rubyonrails.org/upgrading_ruby_on_rails.html#ruby-versions

Body:
seems 2.4 should work with rails 4.x

Rails generally stays close to the latest released Ruby version when
it's released:

....
Rails 4 prefers Ruby 2.0 and requires 1.9.3 or newer.
...

http://guides.rubyonrails.org/upgrading_ruby_on_rails.html#ruby-versions


问:
Mismatch of date while using date format for specific date in swift 3
If the date is Mar 31, 2017 11:59:59 PM , If I use

```<code>    var dateFormatter: DateFormatter =
    {
        let formatter = DateFormatter()
        formatter.dateFormat = "MMM d,yyyy HH:mm:ss a"
        return formatter
    }()
</code>```

above format and after converting seconds, that seconds represent the date Mar 31, 2017 12:59:59 PM

```<code>cellbidEndseconds  = (Int)((dateFormatter.date(from:arrMap_biddingDateEnd[indexPath.row])?.timeIntervalSince1970)!)
</code>```

Where was the error, Can someone figure it out and update?

答:
That's not how you convert a Double to Int. Further, timeIntervalSince1970 returns the number of seconds since midnight Jan 1, 1970. You need to multiply it by 1000 to get miliseconds:

```<code>if let date = dateFormatter.date(from: arrMap_biddingDateEnd[indexPath.row]) {
    cellbidEndMillis = Int(date.timeIntervalSince1970 * 1000)
} else {
    print("Cannot convert date")
}
</code>```

Also: if you call print(date), it always prints the date in GMT, which may appear to be a few hours off compared to your local time. You need to mentally convert it to your local timezone (or write a method to do that for you). This is the a big source of confusion I see on StackOverflow.

Body:
If the date is Mar 31, 2017 11:59:59 PM , If I use

    var dateFormatter: DateFormatter =
    {
        let formatter = DateFormatter()
        formatter.dateFormat = "MMM d,yyyy HH:mm:ss a"
        return formatter
    }()

above format and after converting seconds, that seconds represent the date Mar 31, 2017 12:59:59 PM

cellbidEndseconds  = (Int)((dateFormatter.date(from:arrMap_biddingDateEnd[indexPath.row])?.timeIntervalSince1970)!)

Where was the error, Can someone figure it out and update?

Body:
That's not how you convert a Double to Int. Further, timeIntervalSince1970 returns the number of seconds since midnight Jan 1, 1970. You need to multiply it by 1000 to get miliseconds:

if let date = dateFormatter.date(from: arrMap_biddingDateEnd[indexPath.row]) {
    cellbidEndMillis = Int(date.timeIntervalSince1970 * 1000)
} else {
    print("Cannot convert date")
}

Also: if you call print(date), it always prints the date in GMT, which may appear to be a few hours off compared to your local time. You need to mentally convert it to your local timezone (or write a method to do that for you). This is the a big source of confusion I see on StackOverflow.


问:
Git Config Error?
Found a weird issue that I can't find on Google.

If I'm working on Branch-A and the origin is on Branch-A, doing a push/pull shows the updated files as modified files on origin. Weirder yet, the modified files are different to the update, it shows the old code (or if it's a new file it says on origin that the file is deleted).

Meaning I have to do a git pull, reset HEAD [file] then checkout -- said file. I'm guessing I'm missing something from the conf file for updating branches when origin branch = local branch?

Thanks

答:
You can try to clear your cache</p>

```<code>git rm -r --cached .
git add .
git commit -am 'clear git cache'
git push
</code>```

Title: Git Config Error?

Body:
You can try to clear your cache

git rm -r --cached .
git add .
git commit -am 'clear git cache'
git push


问:
Writing a best fit line function in Python
I'm using python and scikitlearn for linear regression and visualisation purposes.

Instead of using the linear_model.LinearRegression() from sklearn, I would need to create my own function to return the gradient and the best-fit line y-intercept.

m = (μ(x) * μ(y) − μ(x * y))/((μ(x))2 − μ(x2))

b = μ(y) − m * μ(x)

Where μ is a mean function

I am struggling to do this in python.

Any assistance would be greatly appreciated.

```<code>import matplotlib.pyplot as plt
import numpy as np
from sklearn import datasets, linear_model

diabetes = datasets.load_diabetes()

diabetes_X = diabetes.data[:, np.newaxis, 2]

diabetes_X_train = diabetes_X[:-20]
diabetes_X_test = diabetes_X[-20:]

diabetes_y_train = diabetes.target[:-20]
diabetes_y_test = diabetes.target[-20:]

regr = linear_model.LinearRegression()

regr.fit(diabetes_X_train, diabetes_y_train)

print('Coefficients: \n', regr.coef_)

print("Mean squared error: %.2f"
      % np.mean((regr.predict(diabetes_X_test) - diabetes_y_test) ** 2))

print('Variance score: %.2f' % regr.score(diabetes_X_test, diabetes_y_test))

plt.scatter(diabetes_X_test,  diabetes_y_test,  color='green', label="Test")
plt.scatter(diabetes_X_train,  diabetes_y_train,  color='red', label ='Training')
plt.plot(diabetes_X_test, regr.predict(diabetes_X_test), color='blue',
     linewidth=3, label='Best fit line')

plt.xticks(())
plt.yticks(())
plt.legend()

plt.show()
</code>```

答:
you can use numpy to calculate the mean...

```<code>import numpy as np

m = (np.mean(x) * np.mean(y) − np.mean(x * y))/((np.mean(x))**2 − np.mean(x**2))
b = np.mean(y) − m * np.mean(x)
</code>```

but send some code you have done to get more specific help !

Body:
I'm using python and scikitlearn for linear regression and visualisation purposes.

Instead of using the linear_model.LinearRegression() from sklearn, I would need to create my own function to return the gradient and the best-fit line y-intercept.

m = (μ(x) * μ(y) − μ(x * y))/((μ(x))2 − μ(x2))

b = μ(y) − m * μ(x)

Where μ is a mean function

I am struggling to do this in python.

Any assistance would be greatly appreciated.

import matplotlib.pyplot as plt
import numpy as np
from sklearn import datasets, linear_model

diabetes = datasets.load_diabetes()

diabetes_X = diabetes.data[:, np.newaxis, 2]

diabetes_X_train = diabetes_X[:-20]
diabetes_X_test = diabetes_X[-20:]

diabetes_y_train = diabetes.target[:-20]
diabetes_y_test = diabetes.target[-20:]

regr = linear_model.LinearRegression()

regr.fit(diabetes_X_train, diabetes_y_train)

print('Coefficients: \n', regr.coef_)

print("Mean squared error: %.2f"
      % np.mean((regr.predict(diabetes_X_test) - diabetes_y_test) ** 2))

print('Variance score: %.2f' % regr.score(diabetes_X_test, diabetes_y_test))

plt.scatter(diabetes_X_test,  diabetes_y_test,  color='green', label="Test")
plt.scatter(diabetes_X_train,  diabetes_y_train,  color='red', label ='Training')
plt.plot(diabetes_X_test, regr.predict(diabetes_X_test), color='blue',
     linewidth=3, label='Best fit line')

plt.xticks(())
plt.yticks(())
plt.legend()

plt.show()

Body:
you can use numpy to calculate the mean...

import numpy as np

m = (np.mean(x) * np.mean(y) − np.mean(x * y))/((np.mean(x))**2 − np.mean(x**2))
b = np.mean(y) − m * np.mean(x)

but send some code you have done to get more specific help !


问:
C# how to Regex.Replace "\r\n" (the actual characters, not the line break)
I've got some horrible text that I'm cleaning up using several c# regular expressions. One issue that has me stumped is there are a number of '\r\n' strings in the text, the actual characters not the line breaks.

I've tried:

```<code>content = Regex.Replace(content, "\\r\\n", "");
</code>```

and:

```<code>content = Regex.Replace(content, "\r\n", "");
</code>```

but neither of them work. In the end I had to use:

```<code>content = content.Replace("\\r\\n", "\r\n");
</code>```

to get the project finished, but not being able to do it in a regex annoys me.

答:
Wild guess here:</p>

```<code>var bslash = System.IO.Path.DirectorySeparatorChar.ToString();

content = content.Replace(bslash + "r" + bslash + "n", "");
</code>```

ContentLicense: CC BY-SA 2.5

Body:
I've got some horrible text that I'm cleaning up using several c# regular expressions. One issue that has me stumped is there are a number of '\r\n' strings in the text, the actual characters not the line breaks.

I've tried:

content = Regex.Replace(content, "\\r\\n", "");

and:

content = Regex.Replace(content, "\r\n", "");

but neither of them work. In the end I had to use:

content = content.Replace("\\r\\n", "\r\n");

to get the project finished, but not being able to do it in a regex annoys me.

Body:
Wild guess here:

var bslash = System.IO.Path.DirectorySeparatorChar.ToString();

content = content.Replace(bslash + "r" + bslash + "n", "");


问:
Choosing a GUI framework
I'm in an environment where applications have to be partitioned. I'm taking on a project that will involve building a GUI and I don't want to waste team time or IT team by getting the wrong framework and supporting applications.

Already I've built a Tkinter GUI and it was somewhat painful, but it was included.

Just doing some quick googling, and some say never use Tkinter and use PyQt, don't use PyQt due to licensing use PySide, etc...

Any recommendations? Doesn't have to be aesthetically pleasing, I am more interested in the backend complexity and structure of the code the GUI framework uses.

答:
I personally would recommend PyQt, which is both aesthetically pleasing and is simple to implement. It is well structured, well documented and cross-platform. The syntax is pretty clean, the classes are easily extendable. There is a nice GUI editor called Qt Designer, which allows fast prototyping. The .ui files it creates can be directly loaded into PyQt (checkout PyQt4.uic.loadUi).

I cannot say anything about licensing, though - you have to figure out yourself.

Title: Choosing a GUI framework


问:
How to Convert UIView to PDF within iOS?
There are a lot of resources about how to display a PDF in an App's UIView. What I am working on now is to create a PDF from UIViews.

For example, I have a UIView, with subviews like Textviews, UILabels, UIImages, so how can I convert a big UIView as a whole including all its subviews and subsubviews to a PDF?

I have checked Apple's iOS reference. However, it only talks about writing pieces of text/image to a PDF file.

The problem I am facing is that the content I want to write to a file as PDF is a lot. If I write them to the PDF piece by piece, it is going to be huge work to do.
That's why I am looking for a way to write UIViews to PDFs or even bitmaps.

I have tried the source code I copied from other Q/A within Stack Overflow. But it only gives me a blank PDF with the UIView bounds size.

```<code>-(void)createPDFfromUIView:(UIView*)aView saveToDocumentsWithFileName:(NSString*)aFilename
{
    // Creates a mutable data object for updating with binary data, like a byte array
    NSMutableData *pdfData = [NSMutableData data];

    // Points the pdf converter to the mutable data object and to the UIView to be converted
    UIGraphicsBeginPDFContextToData(pdfData, aView.bounds, nil);
    UIGraphicsBeginPDFPage();

    // draws rect to the view and thus this is captured by UIGraphicsBeginPDFContextToData
    [aView drawRect:aView.bounds];

    // remove PDF rendering context
    UIGraphicsEndPDFContext();

    // Retrieves the document directories from the iOS device
    NSArray* documentDirectories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);

    NSString* documentDirectory = [documentDirectories objectAtIndex:0];
    NSString* documentDirectoryFilename = [documentDirectory stringByAppendingPathComponent:aFilename];

    // instructs the mutable data object to write its context to a file on disk
    [pdfData writeToFile:documentDirectoryFilename atomically:YES];
    NSLog(@"documentDirectoryFileName: %@",documentDirectoryFilename);
}
</code>```

答:
This will generate PDF from UIView and open print dialog, objective C.
Attach - (IBAction)PrintPDF:(id)sender to your button on screen.
Add #import <QuartzCore/QuartzCore.h> framework

H File

```<code>    @interface YourViewController : UIViewController <MFMailComposeViewControllerDelegate,UIPrintInteractionControllerDelegate>

    {
    UIPrintInteractionController *printController;
    }

- (IBAction)PrintPDF:(id)sender;
</code>```

M File

```<code>-(void)createPDFfromUIView:(UIView*)aView saveToDocumentsWithFileName:(NSString*)aFilename

{
    NSMutableData *pdfData = [NSMutableData data];

    UIGraphicsBeginPDFContextToData(pdfData, aView.bounds, nil);
    UIGraphicsBeginPDFPage();
    CGContextRef pdfContext = UIGraphicsGetCurrentContext();

    [aView.layer renderInContext:pdfContext];
    UIGraphicsEndPDFContext();

    NSArray* documentDirectories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);

    NSString* documentDirectory = [documentDirectories objectAtIndex:0];
    NSString* documentDirectoryFilename = [documentDirectory stringByAppendingPathComponent:aFilename];
    NSString *file = [documentDirectory stringByAppendingPathComponent:@"yourPDF.pdf"];
    NSURL *urlPdf = [NSURL fileURLWithPath: file];

    [pdfData writeToFile:documentDirectoryFilename atomically:YES];
    NSLog(@"documentDirectoryFileName: %@",documentDirectoryFilename);

}

- (IBAction)PrintPDF:(id)sender
{
    [self createPDFfromUIView:self.view saveToDocumentsWithFileName:@"yourPDF.pdf"];

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"yourPDF.pdf"];
    NSData *myData = [NSData dataWithContentsOfFile: path];

    UIPrintInteractionController *pic = [UIPrintInteractionController sharedPrintController];
    if(pic && [UIPrintInteractionController canPrintData: myData] ) {

        pic.delegate = self;

        UIPrintInfo *printInfo = [UIPrintInfo printInfo];
        printInfo.outputType = UIPrintInfoOutputGeneral;
        printInfo.jobName = [path lastPathComponent];
        printInfo.duplex = UIPrintInfoDuplexLongEdge;
        pic.printInfo = printInfo;
        pic.showsPageRange = YES;
        pic.printingItem = myData;

        void (^completionHandler)(UIPrintInteractionController *, BOOL, NSError *) = ^(UIPrintInteractionController *pic, BOOL completed, NSError *error) {
            //self.content = nil;
            if(!completed && error){

                NSLog(@"Print Error: %@", error);
            }
        };

        [pic presentAnimated:YES completionHandler:completionHandler];

    }

}
</code>```

ContentLicense: CC BY-SA 4.0

Body:
There are a lot of resources about how to display a PDF in an App's UIView. What I am working on now is to create a PDF from UIViews.

For example, I have a UIView, with subviews like Textviews, UILabels, UIImages, so how can I convert a big UIView as a whole including all its subviews and subsubviews to a PDF?

I have checked Apple's iOS reference. However, it only talks about writing pieces of text/image to a PDF file.

The problem I am facing is that the content I want to write to a file as PDF is a lot. If I write them to the PDF piece by piece, it is going to be huge work to do.
That's why I am looking for a way to write UIViews to PDFs or even bitmaps.

I have tried the source code I copied from other Q/A within Stack Overflow. But it only gives me a blank PDF with the UIView bounds size.

-(void)createPDFfromUIView:(UIView*)aView saveToDocumentsWithFileName:(NSString*)aFilename
{
    // Creates a mutable data object for updating with binary data, like a byte array
    NSMutableData *pdfData = [NSMutableData data];

    // Points the pdf converter to the mutable data object and to the UIView to be converted
    UIGraphicsBeginPDFContextToData(pdfData, aView.bounds, nil);
    UIGraphicsBeginPDFPage();

    // draws rect to the view and thus this is captured by UIGraphicsBeginPDFContextToData
    [aView drawRect:aView.bounds];

    // remove PDF rendering context
    UIGraphicsEndPDFContext();

    // Retrieves the document directories from the iOS device
    NSArray* documentDirectories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);

    NSString* documentDirectory = [documentDirectories objectAtIndex:0];
    NSString* documentDirectoryFilename = [documentDirectory stringByAppendingPathComponent:aFilename];

    // instructs the mutable data object to write its context to a file on disk
    [pdfData writeToFile:documentDirectoryFilename atomically:YES];
    NSLog(@"documentDirectoryFileName: %@",documentDirectoryFilename);
}

Body:
This will generate PDF from UIView and open print dialog, objective C.
Attach - (IBAction)PrintPDF:(id)sender to your button on screen.
Add #import <QuartzCore/QuartzCore.h> framework

H File

    @interface YourViewController : UIViewController <MFMailComposeViewControllerDelegate,UIPrintInteractionControllerDelegate>

    {
    UIPrintInteractionController *printController;
    }

- (IBAction)PrintPDF:(id)sender;

M File

-(void)createPDFfromUIView:(UIView*)aView saveToDocumentsWithFileName:(NSString*)aFilename

{
    NSMutableData *pdfData = [NSMutableData data];

    UIGraphicsBeginPDFContextToData(pdfData, aView.bounds, nil);
    UIGraphicsBeginPDFPage();
    CGContextRef pdfContext = UIGraphicsGetCurrentContext();

    [aView.layer renderInContext:pdfContext];
    UIGraphicsEndPDFContext();

    NSArray* documentDirectories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);

    NSString* documentDirectory = [documentDirectories objectAtIndex:0];
    NSString* documentDirectoryFilename = [documentDirectory stringByAppendingPathComponent:aFilename];
    NSString *file = [documentDirectory stringByAppendingPathComponent:@"yourPDF.pdf"];
    NSURL *urlPdf = [NSURL fileURLWithPath: file];

    [pdfData writeToFile:documentDirectoryFilename atomically:YES];
    NSLog(@"documentDirectoryFileName: %@",documentDirectoryFilename);

}

- (IBAction)PrintPDF:(id)sender
{
    [self createPDFfromUIView:self.view saveToDocumentsWithFileName:@"yourPDF.pdf"];

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"yourPDF.pdf"];
    NSData *myData = [NSData dataWithContentsOfFile: path];

    UIPrintInteractionController *pic = [UIPrintInteractionController sharedPrintController];
    if(pic && [UIPrintInteractionController canPrintData: myData] ) {

        pic.delegate = self;

        UIPrintInfo *printInfo = [UIPrintInfo printInfo];
        printInfo.outputType = UIPrintInfoOutputGeneral;
        printInfo.jobName = [path lastPathComponent];
        printInfo.duplex = UIPrintInfoDuplexLongEdge;
        pic.printInfo = printInfo;
        pic.showsPageRange = YES;
        pic.printingItem = myData;

        void (^completionHandler)(UIPrintInteractionController *, BOOL, NSError *) = ^(UIPrintInteractionController *pic, BOOL completed, NSError *error) {
            //self.content = nil;
            if(!completed && error){

                NSLog(@"Print Error: %@", error);
            }
        };

        [pic presentAnimated:YES completionHandler:completionHandler];

    }

}


问:
Calculate percentage based on row_number in sql
I'm trying to write a sql query that for each returned row, will also return a percentage (0-100) based on its row position. To further complicate this, the query is also currently grouped. Here's some example data to play with:

```<code>create table #test(StockId int)
insert into #test values (101), (101), (202), (202), (303), (404), (505)

select
StockId,
count(*) as BinMovements
from #test
group by StockId
order by BinMovements desc
</code>```

This query currently returns:

StockId, BinMovements

  101, 2

  202, 2

  303, 1

  404, 1

  505, 1

(Though obviously, because BinMovements is either 2 or 1 it would be equally correct to return the StockIds in a different order such as):

202, 2

  101, 2

  404, 1

  303, 1

  505, 1

I'd like to add a 'percentage' column, but this is really just based on row position, so I'd like to see values like:

101, 2, 100

  202, 2, 80

  303, 1, 60

  404, 1, 40

  505, 1, 20

I imagine the solution may involve ROW_NUMBER, and I started down this path thinking I could just get ROW_NUMBER / total rows * 100, but this isn't working for some reason. Possibly because of the group by clause?

```<code>select
StockId,
count(*) as BinMovements,
ROW_NUMBER() OVER(order by (count(*))) as RowNumber,
count(*) over () as TotalCount,
(ROW_NUMBER() OVER(order by (count(*)))) / (count(*) over ()) * 100 as Percentage
from #test
group by StockId
order by RowNumber desc
</code>```

returns:

```<code>StockId BinMovements    RowNumber   TotalCount  Percentage
202     2               5           5           100
101     2               4           5           0
505     1               3           5           0
404     1               2           5           0
303     1               1           5           0
</code>```

I'd prefer to do this in a single select if possible, though if not wrapping it in an outer select may be a solution. Thanks

答:
Using count() over() and row_number() similar to your question, just reordering equation.

Note that the order by for the row_number() is the inverse of the order by for the statement, because we want to start the percentage at 100. Otherwise you can end up with 80 as the first row, 100 as the second, etc.

```<code>select
    StockId
  , BinMovements = count(*)
  , Percentage = 100/count(*) over ()
    * row_number() over (order by count(*) asc, stockid desc)
from #test
group by StockId
order by BinMovements desc, StockId asc
</code>```

rextester demo: http://rextester.com/TCPE10348

```<code>+---------+--------------+------------+
| StockId | BinMovements | Percentage |
+---------+--------------+------------+
|     101 |            2 |        100 |
|     202 |            2 |         80 |
|     303 |            1 |         60 |
|     404 |            1 |         40 |
|     505 |            1 |         20 |
+---------+--------------+------------+
</code>```

If you want a decimal percentage, change 100 to 100.0 in the equation.

Body:
I'm trying to write a sql query that for each returned row, will also return a percentage (0-100) based on its row position. To further complicate this, the query is also currently grouped. Here's some example data to play with:

create table #test(StockId int)
insert into #test values (101), (101), (202), (202), (303), (404), (505)

select
StockId,
count(*) as BinMovements
from #test
group by StockId
order by BinMovements desc

This query currently returns:

StockId, BinMovements

  101, 2

  202, 2

  303, 1

  404, 1

  505, 1

(Though obviously, because BinMovements is either 2 or 1 it would be equally correct to return the StockIds in a different order such as):

202, 2

  101, 2

  404, 1

  303, 1

  505, 1

I'd like to add a 'percentage' column, but this is really just based on row position, so I'd like to see values like:

101, 2, 100

  202, 2, 80

  303, 1, 60

  404, 1, 40

  505, 1, 20

I imagine the solution may involve ROW_NUMBER, and I started down this path thinking I could just get ROW_NUMBER / total rows * 100, but this isn't working for some reason. Possibly because of the group by clause?

select
StockId,
count(*) as BinMovements,
ROW_NUMBER() OVER(order by (count(*))) as RowNumber,
count(*) over () as TotalCount,
(ROW_NUMBER() OVER(order by (count(*)))) / (count(*) over ()) * 100 as Percentage
from #test
group by StockId
order by RowNumber desc

returns:

StockId BinMovements    RowNumber   TotalCount  Percentage
202     2               5           5           100
101     2               4           5           0
505     1               3           5           0
404     1               2           5           0
303     1               1           5           0

I'd prefer to do this in a single select if possible, though if not wrapping it in an outer select may be a solution. Thanks

Body:
Using count() over() and row_number() similar to your question, just reordering equation.

Note that the order by for the row_number() is the inverse of the order by for the statement, because we want to start the percentage at 100. Otherwise you can end up with 80 as the first row, 100 as the second, etc.

select
    StockId
  , BinMovements = count(*)
  , Percentage = 100/count(*) over ()
    * row_number() over (order by count(*) asc, stockid desc)
from #test
group by StockId
order by BinMovements desc, StockId asc

rextester demo: http://rextester.com/TCPE10348

+---------+--------------+------------+
| StockId | BinMovements | Percentage |
+---------+--------------+------------+
|     101 |            2 |        100 |
|     202 |            2 |         80 |
|     303 |            1 |         60 |
|     404 |            1 |         40 |
|     505 |            1 |         20 |
+---------+--------------+------------+

If you want a decimal percentage, change 100 to 100.0 in the equation.


问:
Angular 4 animation don't resumed the initial CSS value
I was in Angular 2 and I change for Angular 4.0.0 rc-2 and the animations behavior is not the same. In Angular 2 I was able to resumed the initial CSS value when it returned to the state "True" but this is no longer the case with Angular 4. Somebody can help me?

State on TRUE => I want to resumed the initial CSS value

State on False => Set width to 100%

I have this animation:

```<code>trigger('flyInOutX', [
    state('true', style({transform: 'translateX(0%)'})),
    state('false', style({width: '100%', transform:'translateX(-245px)'})),
    transition('* => *', animate('300ms')),
]),
</code>```

答:
Animation state needs to be a string value, so using a boolean won't work.

If you were doing [@flyInOutX]="foo" in your template, try doing [@flyInOutX]="foo ? 'true' : 'false'" instead.

Body:
I was in Angular 2 and I change for Angular 4.0.0 rc-2 and the animations behavior is not the same. In Angular 2 I was able to resumed the initial CSS value when it returned to the state "True" but this is no longer the case with Angular 4. Somebody can help me?

State on TRUE => I want to resumed the initial CSS value

State on False => Set width to 100%

I have this animation:

trigger('flyInOutX', [
    state('true', style({transform: 'translateX(0%)'})),
    state('false', style({width: '100%', transform:'translateX(-245px)'})),
    transition('* => *', animate('300ms')),
]),


问:
(How) Can I reduce socket latency?
I have written an HTTP proxy that does some stuff that's not relevant here, but it is increasing the client's time-to-serve by a huge amount (600us without proxy vs 60000us with it). I think I have found where the bulk of that time is coming from - between my proxy finishing sending back to the client and the client finishing receiving it. For now, server, proxy and client are running on the same host, using localhost as the addresses.

Once the proxy has finished sending (once it has returned from send() at least), I print the result of gettimeofday which gives an absolute time. When my client has received, it prints the result of gettimeofday. Since they're both on the same host, this should be accurate. All send() calls are with no flags, so they are blocking. The difference between the two is about 40000us.

The proxy's socket on which it listens for client connections is set up with the hints AF_UNSPEC, SOCK_STREAM and AI_PASSIVE. Presumably a socket from accept()ing on that will have the same parameters?

If I'm understanding all this correctly, Apache manages to do everything in 600us (including the equivalent of whatever is causing this 40000us delay). Can anybody suggest what might be causing this? I have tried setting the TCP_NODELAY option (I know I shouldn't, it's just to see if it made a difference) and the delay between finishing sending and finishing receiving went right down, I forget the number but <1000us.

This is all on Ubuntu Linux 2.6.31-19. Thanks for any help

答:
For a TCP proxy it would seem prudent on the LAN side to increase the TCP initial window size as discussed on linux-netdev and /. recently.

http://www.amailbox.org/mailarchive/linux-netdev/2010/5/26/6278007

http://developers.slashdot.org/story/10/11/26/1729218/Google-Microsoft-Cheat-On-Slow-Start-mdash-Should-You

Including paper on the topic by Google,

http://www.google.com/research/pubs/pub36640.html

And an IETF draft also by Google,

http://zinfandel.levkowetz.com/html/draft-ietf-tcpm-initcwnd-00

Title: (How) Can I reduce socket latency?


问:
 link is not working on the div

STEAK HOUSE
RESTAURANT

Jeddah

      Name:

Steak House Restaurant

 Location:

Jeddah

```

The  element is the parent of the class "txt" and when I try to make  the parent of the entire "gallery-cell" class it doesn't work!
I want the entire cell to be clickable, not just the text.

If you want, take a look at this website to test it out: http://urbanphenomena.net/

答:
Actually, adding a div inside anchor tag is a wrong method.

So you can add the anchor tag as the first child of the "gallery-cell" div

```<code><div class="gallery-cell">
    <a id="thumb2" href="imgs/SteakHouse/1.jpg" class="highslide"></a>
    <div class="highslide-gallery">
    </div>
</div>
</code>```

Then give position relative to the "gallery-cell" and position absolute to the  tag. You can use following style

```<code>.gallery-cell {
   position: relative;
}

a#thumb2 {
   position: absolute;
   left: 0;
   right: 0;
   top: 0;
   bottom: 0;
}
</code>```

Through this you can make entire cell to be clickable

Title: <a> link is not working on the div

Body:
STEAK HOUSE
RESTAURANT

Jeddah

      Name:

Steak House Restaurant

 Location:

Jeddah

The  element is the parent of the class "txt" and when I try to make  the parent of the entire "gallery-cell" class it doesn't work!
I want the entire cell to be clickable, not just the text.

If you want, take a look at this website to test it out: http://urbanphenomena.net/

Body:
Actually, adding a div inside anchor tag is a wrong method.

So you can add the anchor tag as the first child of the "gallery-cell" div

<div class="gallery-cell">
    <a id="thumb2" href="imgs/SteakHouse/1.jpg" class="highslide"></a>
    <div class="highslide-gallery">
    </div>
</div>

Then give position relative to the "gallery-cell" and position absolute to the  tag. You can use following style

.gallery-cell {
   position: relative;
}

a#thumb2 {
   position: absolute;
   left: 0;
   right: 0;
   top: 0;
   bottom: 0;
}

Through this you can make entire cell to be clickable


问:
swift UIImage blue square
I have a project where any image I try to call using .setImage shows a blue square instead of the image.

```<code>sender.setImage(UIImage(named: "o.png"), for: [])
print(sender.tag)
</code>```

any help is greatly appreciated

Edit: I forgot to mention this is setting an image for a button inside of an IBAction

答:
Basic : UIButton can not be set directly like as we do for UIImageView. You have to define at which state of UIButton you'r setting the image. Even if you wanted to set title for UIButton you have to define the button state like below

```<code>sender.setImage(<your_image>, for: .normal)
</code>```

so replace you'r code with below lines and try

```<code>sender.setImage(UIImage(named: "o.png"), for: .normal)
</code>```

Hope it helps.

Title: swift UIImage blue square

Body:
I have a project where any image I try to call using .setImage shows a blue square instead of the image.

sender.setImage(UIImage(named: "o.png"), for: [])
print(sender.tag)

any help is greatly appreciated

Edit: I forgot to mention this is setting an image for a button inside of an IBAction

Body:
Basic : UIButton can not be set directly like as we do for UIImageView. You have to define at which state of UIButton you'r setting the image. Even if you wanted to set title for UIButton you have to define the button state like below

sender.setImage(<your_image>, for: .normal)

so replace you'r code with below lines and try

sender.setImage(UIImage(named: "o.png"), for: .normal)

Hope it helps.


问:
How to check punctuation(like comma,period,colon etc..) followed by space or not. In a title column using SQL, REG
How to check punctuation(like comma,period,colon etc..) followed by space or not, in a title column  using  SQL, REG

Good example:

```<code>Symphony No. 4 in A Major, Op. 53: IV. Allegro molto
</code>```

Bad example:

```<code>Symphony No.4 in A Major, Op.53: IV. Allegro molto
</code>```

I need query like

```<code>SELECT COUNT (1)
  FROM actual_configs
 WHERE......
</code>```

Body:
How to check punctuation(like comma,period,colon etc..) followed by space or not, in a title column  using  SQL, REG

Good example:

Symphony No. 4 in A Major, Op. 53: IV. Allegro molto

Bad example:

Symphony No.4 in A Major, Op.53: IV. Allegro molto

I need query like

SELECT COUNT (1)
  FROM actual_configs
 WHERE......

Body:
with t (col) as
(
              select 'Symphony No. 4 in A Major, Op. 53: IV. Allegro molto' from dual
   union all  select 'Symphony No.4 in A Major, Op.53: IV. Allegro molto'   from dual
)
select  *
from    t
where   regexp_like (col,'([[:punct:]])([^ ])')
;

+----------------------------------------------------+
|                        COL                         |
+----------------------------------------------------+
| Symphony No.4 in A Major, Op.53: IV. Allegro molto |
+----------------------------------------------------+

For debugging purposes

with t (col) as
(
              select 'Symphony No. 4 in A Major, Op. 53: IV. Allegro molto' from dual
   union all  select 'Symphony No.4 in A Major, Op.53: IV. Allegro molto'   from dual
)
select  case when regexp_like (col,'([[:punct:]])([^ ])') then 0 else 1 end  as is_valid
       ,col
       ,regexp_replace (col,'([[:punct:]])([^ ])','<<<\1>>>\2')              as show_issues

from    t
;

+----------+------------------------------------------------------+----------------------------------------------------------------+
| IS_VALID |                         COL                          |                          SHOW_ISSUES                           |
+----------+------------------------------------------------------+----------------------------------------------------------------+
|        1 | Symphony No. 4 in A Major, Op. 53: IV. Allegro molto | Symphony No. 4 in A Major, Op. 53: IV. Allegro molto           |
|        0 | Symphony No.4 in A Major, Op.53: IV. Allegro molto   | Symphony No<<<.>>>4 in A Major, Op<<<.>>>53: IV. Allegro molto |
+----------+------------------------------------------------------+----------------------------------------------------------------+


问:
TypeScript filter out nulls from an array
TypeScript, --strictNullChecks mode.

Suppose I have an array of nullable strings (string | null)[]. What would be a single-expression way to remove all nulls in a such a way that the result has type string[]?

```<code>const array: (string | null)[] = ["foo", "bar", null, "zoo", null];
const filterdArray: string[] = ???;
</code>```

Array.filter does not work here:

```<code>// Type '(string | null)[]' is not assignable to type 'string[]'
array.filter(x => x != null);
</code>```

Array comprehensions could've work but they are not supported by TypeScript.

Actually the question can be generalized to the problem of filtering an array of any union type by removing entries having one particular type from the union. But  let's focus on unions with null and perhaps undefined as these are the most common usecases.

答:
I believe you have it all good except that the type checking just makes the filtered type not be different than the return type.

const array: (string | null)[] = ["foo", "bar", null, "zoo", null];
const filterdArray: string[] = array.filter(f => f !== undefined && f !== null) as any;
console.log(filterdArray);
```

Body:
TypeScript, --strictNullChecks mode.

Suppose I have an array of nullable strings (string | null)[]. What would be a single-expression way to remove all nulls in a such a way that the result has type string[]?

const array: (string | null)[] = ["foo", "bar", null, "zoo", null];
const filterdArray: string[] = ???;

Array.filter does not work here:

// Type '(string | null)[]' is not assignable to type 'string[]'
array.filter(x => x != null);

Array comprehensions could've work but they are not supported by TypeScript.

Actually the question can be generalized to the problem of filtering an array of any union type by removing entries having one particular type from the union. But  let's focus on unions with null and perhaps undefined as these are the most common usecases.


问:
Totally deleting the QApplication Instance and recreating it in another thread
How can I totally remove the QApplication instance, so it can be possible to recreate it again in a sheared library.
If I have such a following code:

```<code>int main(int argc, char ** argv) {
QApplication *app = new QApplication(argc, argv);
MyWindow dialog;
dialog.show();
app->exec();
return 0
}
</code>```

I want to be able to delete the instance after quiting the app (closing the application after app.exec())
I have tried to do the following:

```<code>app->setQuitOnLastWindowClosed(false);
app->quit();
delete app;
</code>```

But non of them could be worked. I want to remove the QApplication instance like I did not create before.
The QApplication in my shared library always works if I did not define the QApplication in the main.

The library (lib) and the main application (app) are using different copy of Qt.

I am developing the app, the lib is imported to my app.

After closing the first dialog, I am using an event to trigger the thread in the library which it is waiting for this event.

答:
You don't need to do that, because QApplication can only ever be created in the main thread. If the library and the main executable share the same Qt library, presumably the main application already created the QApplication object and will run the event loop for you. So the only thing you need to do in the library is to create some objects and the event loop will process their events.

If the main executable doesn't use Qt, then you should create the application in the main thread, prime its event loop, and let the native event loop dispatch events for you.

Alas, to directly answer your question: presumably you want to do some stuff after app.exec() returns. Your code could simply be, then:

```<code>int main(int argc, char ** argv) {
  {
     QApplication app{argc, argv};
     MyWindow dialog;
     dialog.show();
     app.exec();
   }
   doSomething();
}
</code>```

If I close the first dialog, the event loop will be left. Is it possible to keep the event loop?

Yes. You could clear the Qt::WA_QuitOnClose attribute of the first dialog. Or, preferably, set it on your dialog - the application won't quit as long as there are any dialogs with QT::WA_QuitOnClose still open.

Thus, for the second dialog, call:

```<code>secondDialog->setAttribute(Qt::WA_QuitOnClose);
</code>```

Alternatively, if you don't have a pointer to the dialog, you can iterate all top-level widgets and clear (or set) the attribute on all of them:

```<code>for (auto widget : qApp->topLevelWidgets())
  widget->setAttribute(Qt::QA_QuitOnClose, false);
</code>```

Body:
How can I totally remove the QApplication instance, so it can be possible to recreate it again in a sheared library.
If I have such a following code:

int main(int argc, char ** argv) {
QApplication *app = new QApplication(argc, argv);
MyWindow dialog;
dialog.show();
app->exec();
return 0
}

I want to be able to delete the instance after quiting the app (closing the application after app.exec())
I have tried to do the following:

app->setQuitOnLastWindowClosed(false);
app->quit();
delete app;

But non of them could be worked. I want to remove the QApplication instance like I did not create before.
The QApplication in my shared library always works if I did not define the QApplication in the main.

The library (lib) and the main application (app) are using different copy of Qt.

I am developing the app, the lib is imported to my app.

After closing the first dialog, I am using an event to trigger the thread in the library which it is waiting for this event.

Body:
You don't need to do that, because QApplication can only ever be created in the main thread. If the library and the main executable share the same Qt library, presumably the main application already created the QApplication object and will run the event loop for you. So the only thing you need to do in the library is to create some objects and the event loop will process their events.

If the main executable doesn't use Qt, then you should create the application in the main thread, prime its event loop, and let the native event loop dispatch events for you.

Alas, to directly answer your question: presumably you want to do some stuff after app.exec() returns. Your code could simply be, then:

int main(int argc, char ** argv) {
  {
     QApplication app{argc, argv};
     MyWindow dialog;
     dialog.show();
     app.exec();
   }
   doSomething();
}

If I close the first dialog, the event loop will be left. Is it possible to keep the event loop?

Yes. You could clear the Qt::WA_QuitOnClose attribute of the first dialog. Or, preferably, set it on your dialog - the application won't quit as long as there are any dialogs with QT::WA_QuitOnClose still open.

Thus, for the second dialog, call:

secondDialog->setAttribute(Qt::WA_QuitOnClose);

Alternatively, if you don't have a pointer to the dialog, you can iterate all top-level widgets and clear (or set) the attribute on all of them:

for (auto widget : qApp->topLevelWidgets())
  widget->setAttribute(Qt::QA_QuitOnClose, false);


问:
allocating memory using calloc and freeing
gcc 4.4.4 c89

I have a program that I am testing. I create a struct object called devname and allocate memory so that I can fill the elements. I display them and then free the memory that was allocated.

However, I am getting the following error:

```<code>invalid operands to binary != (have ‘struct Devices_names’ and ‘void *’)
</code>```

That is in my for loop for displaying the structure elements. However, I feel I am testing for a NULL pointer.

Just a further question, is there an problem with the free?

Many thanks for any advice,

```<code>#include <stdio.h>
#include <stdlib.h>

static struct Devices_names {
#define MAX_NAME_LEN 80
    int id;
    char name[MAX_NAME_LEN];
} *devname;

static void g_create_device_names(size_t devices);
static void g_get_device_names();
static void destroy_devices();

int main(void)
{
#define DEVICES 5
    g_create_device_names(DEVICES);

    g_get_device_names();

    destroy_devices();

    return 0;
}

static void g_create_device_names(size_t devices)
{
    size_t i = 0;
    devname = calloc(devices, sizeof *devname);
    if(devname == NULL) {
        exit(0);
    }

    for(i = 0; i < devices; i++) {
        devname[i].id = i;
        sprintf(devname[i].name, "device: %d", i);
    }
}

static void g_get_device_names()
{
    size_t i = 0;

    for(i = 0; devname[i] != NULL; i++) { <-- ERROR HERE
        printf("Device id --- [ %d ]\n", devname[i].id);
        printf("Device name - [ %s ]\n", devname[i].name);
    }
}

static void destroy_devices()
{
    while(devname != NULL) {
        free(devname++);
    }
}
</code>```

答: devname[i] is not a pointer its a struct Devices_names, therefore the comparison doesn't make sense.

Body:
gcc 4.4.4 c89

I have a program that I am testing. I create a struct object called devname and allocate memory so that I can fill the elements. I display them and then free the memory that was allocated.

However, I am getting the following error:

invalid operands to binary != (have ‘struct Devices_names’ and ‘void *’)

That is in my for loop for displaying the structure elements. However, I feel I am testing for a NULL pointer.

Just a further question, is there an problem with the free?

Many thanks for any advice,

#include <stdio.h>
#include <stdlib.h>

static struct Devices_names {
#define MAX_NAME_LEN 80
    int id;
    char name[MAX_NAME_LEN];
} *devname;

static void g_create_device_names(size_t devices);
static void g_get_device_names();
static void destroy_devices();

int main(void)
{
#define DEVICES 5
    g_create_device_names(DEVICES);

    g_get_device_names();

    destroy_devices();

    return 0;
}

static void g_create_device_names(size_t devices)
{
    size_t i = 0;
    devname = calloc(devices, sizeof *devname);
    if(devname == NULL) {
        exit(0);
    }

    for(i = 0; i < devices; i++) {
        devname[i].id = i;
        sprintf(devname[i].name, "device: %d", i);
    }
}

static void g_get_device_names()
{
    size_t i = 0;

    for(i = 0; devname[i] != NULL; i++) { <-- ERROR HERE
        printf("Device id --- [ %d ]\n", devname[i].id);
        printf("Device name - [ %s ]\n", devname[i].name);
    }
}

static void destroy_devices()
{
    while(devname != NULL) {
        free(devname++);
    }
}


问:
how to hide specific div when i click div in vue js
```<code><template>
 <div v-for="(item ,index) in items" v-if="showing">
                <span @click="showing=false">{{item.name}}</span>
 </div>
</template>
    <script>

            export default{
                       showing:true,
                       items: [
                          {'name':'a'},
                          {'name':'b'},
                          {'name':'c'},
                       ],
            }
    </script>
</code>```

how to hide specific div when i click div
** when i do this way it`s all of them was hide**

答:
Not done Vue for a bit, so this may not work, but should be a good pointer.</p>

```<code><template>
    <div v-for="(item, index) in items" v-if="item.shown">
         <span @click="item.shown=false">{{item.name}}</span>
    </div>
</template>
<script>
        export default {
             items: [
                 {'name':'a', shown: true},
                 {'name':'b', shown: true},
                 {'name':'c', shown: true},
             ],
        }
</script>
</code>```

Body:
<template>

                {{item.name}}

</template>

how to hide specific div when i click div
** when i do this way it`s all of them was hide**

Body:
Not done Vue for a bit, so this may not work, but should be a good pointer.

<template>
    <div v-for="(item, index) in items" v-if="item.shown">
         <span @click="item.shown=false">{{item.name}}</span>
    </div>
</template>
<script>
        export default {
             items: [
                 {'name':'a', shown: true},
                 {'name':'b', shown: true},
                 {'name':'c', shown: true},
             ],
        }
</script>