The Heap™ – Consultancy ©®

Michael Green

06:03

@ypercubeᵀᴹ I like browsing Worldbuilding, too. Always something a bit out-of-the ordinary to think about.

Tom V

07:43

Inside the dnc leak:

hmm, their estimates for development :)

> 3. Add a simple, “Share on Facebook” link (4 hours).

> 1. Streamlined, single page set up for new fundraisers (55 hours).

Glad there is something in there for the geeks too :)

dezso

08:05

@TomV a link. That is what is missing ;)

Tom V

678.4 MB of new "DNC documents" from @Guccifer_2 Magnet: magnet:?xt=urn:btih:ED9C54D8CE543F9A45D180FD58B4C56CF2A3FC1E Pass: (GuCCif3r_2.0)

Tweeted by wikileaks on September 13, 2016 at 10:27 PM

The tech stuff seems to be in the "stuffformike" folder

whoever that Mike is

dezso

@TomV I feel old - cannot figure out a single thing from that (any?) twitter thread

Tom V

@dezso It's a magnet link you could open with transmission for example and the pass is to open the archive

dezso

lazy:

0

Q: How to create trigger for match the condition and do calculation based on the condition?

There are 2 tables isp_cdr and lcr. lcr (user_rate , digits) isp_cdr (destination_number , billsec, nibble_total_billed) The condition is check the destination_number which matches with the digits and uses the user_rate * billsec then updates nibble_total_billed .

War

@TomV that's funny

dezso

08:14

@TomV aha. I don't understand a word :D

ConcernedOfTunbridgeWells

08:36

Morning all

dezso

morning

profile 101

09:05

hi all, which is user friendly online database schema diagram design tool ?

Marian

Sep 5 at 19:01, by Lamak

@War http://draw.io is a simple one

profile 101

@Marian what you say about this : ondras.zarovi.cz/sql/demo

Andriy M

@profile101 If you like it anyway, you probably don't need others' opinions.

profile 101

@AndriyM i didt still tested that one.... so i need help from experts like you.....

Andriy M

09:21

@profile101 You are flattering me :) I'm not even good at DB design, maybe so-so.

I'm certainly not good at DB design tools. I usually like the result but most often it takes too much time for me.

profile 101

@AndriyM no you have good sense of humour , you are telling you are not good at design , can't stop laughing :-)

profile 101

09:33

when we naming columns or tables , which is better to use ? short names or long names ?

suppose for customer related information

c_id or customer_id

any advantages or disadvantages or this

ypercubeᵀᴹ

@profile101 I think most people in this room would tell you to use the long ones

profile 101

thanks @ypercubeᵀᴹ

ypercubeᵀᴹ

If applications table has a_id as the primary key, what will you choose for alerts?

There are only 26 letters in the alphabet

dezso

@ypercubeᵀᴹ except when the table is called customer, I'd go with c_id, c_name and so on

most tables have longer names, like customer_address - in this case it will be ca_village

ypercubeᵀᴹ

@dezso and if in another table, you have a foreign key to customers, it would still be c_id, right?

dezso

09:39

we use this scheme around here, collisions occur very seldom

@ypercubeᵀᴹ funnily no, it would be x_customer_id

ypercubeᵀᴹ

@dezso ok. I've not used it but if you say it works, I beleive you

dezso

one upside is that there is no need of table aliasing in queries

ypercubeᵀᴹ

@dezso totally unique columns names? Sounds good, if it is strictly enforced

dezso

previously, I used to use long names

@ypercubeᵀᴹ not necessarily enforced, but I am yet to see a conflict after 3.5 years

(i guess there is a psychological pressure on devs to come up with table names that result in different prefixes)

Phil

09:52

The billing DB schema I have to put up with does things like that. They prefix with "r" (I can only assume that's short for referential) when a column is a join to a PK. I hate it personally, makes it harder to code for

dezso

such a thing we don't do

(you might want to search for chat history where COTW was pestering me (possibly multiple times) with Hungarian notation :D)

ConcernedOfTunbridgeWells

@ypercubeᵀᴹ Recently I had occasion to work on a system where they had largely enforced unique column names. It made it ever so easy to grep for references in the sprocs. 10/10 would do this on my next project.

dezso

yes, that's another advantage

I haven't had to grep source code recently, on the other hand

ypercubeᵀᴹ

@ConcernedOfTunbridgeWells Yeah, I'd love to try that.

Currently we are sticking with id in - almost - every table and is driving me nuts when I have to write queries

dezso

10:40

@ypercubeᵀᴹ

1

A: SQL - find records that are prefixes of my string

Another option would be to create all the prefixes of the selected word and test them with equality =, (or ILIKE*), against the strings in the table: select t.prefix from (select 'aaaabbbbccccdddd'::text as word) as w, generate_series(0, length(w.word)) as g, lateral (select t.prefix ...

nice try - I'd be happy to see some performance comparisons

ypercubeᵀᴹ

@dezso I'll try to add that.

Not sure about the ILIKE performance regarding index use

dezso

me neither, but the the right type of index (text_pattern_ops or varchar_pattern_ops) should be used

profile 101

in mysql productID int(12) means user can not able to enter more than 12 digits right ?

ypercubeᵀᴹ

no. The (12) is just decoration. Might be used by some applications to know that the column can have a max of 12 characters

It's usually int(11) for signed int and int(10) for unsigned int columns.

but whatever is there does not change what values you can store.

profile 101

i thought if we restrict to 12 digits through database , than no need to do any validation in php form to restrict for 12 digits ? i thought like that.... thanks.....

ypercubeᵀᴹ

10:54

int is fixed: -2147483648 to +2147483647

and unsigned int: 0 to 4294967295

profile 101

i saw this : webassist.com/tutorials/Free-eCommerce-MySQL-Database & confused that int supports 12

ypercubeᵀᴹ

If you want to restrict and allow up to 12 digits, you can use DECIMAL(12,0)

profile 101

you said "used by some applications to know that the column can have a max of 12 charactars" , can you please explain what kind of applications ?

ypercubeᵀᴹ

@profile101 any client that can connect and run queries

profile 101

when its restricting to 12 digits , than why we need to put !12)

ypercubeᵀᴹ

10:59

It's just informative.

profile 101

sorry . when its not restricting to 12 digits , than why we need to put (12

ypercubeᵀᴹ

You don't need to put it.

You can easily run:

CREATE TABLE t
(t_id INT NOT NULL PRIMARY KEY) ;

or:

CREATE TABLE t
(t_id INT(100) NOT NULL PRIMARY KEY) ;

the result is the same, regarding what value scan be stored in the column

profile 101

than i will remove those numbers from design diagram thanks..... @ypercubeᵀᴹ

Andriy M

12 is stored somewhere in metadata for an application to use it if it needs it (for instance to determine the width of the output field on the screen or in a report or something).

profile 101

its better to keep correct number right like 12 instead of 1200 ?

Andriy M

11:05

If your application is going to rely on that piece of data, then it's better to keep it correct, yes.

profile 101

also we need to use "binary" to store password right ?

Andriy M

Personally I've no idea (and I wouldn't be sure how it's usually stored in MySQL anyway).

profile 101

no problem, thanks @AndriyM

dezso

BTW this is one of the silliest 'features' of MySQL

profile 101

you are right :-)

War

11:23

Anyone here a genius with generics and reflection in .Net ... I have an architectural question

happy to take elsewhere if need be like this one perhaps ...

The Web Stack & Architecture

Awesome stuff from the minds of the crazy through careful use ...

Just need a little advice :(

profile 101

12:09

above design is 75 % completed , please check and feedback....

War

do you have 2 accounts?

profile 101

no @War different profile name in different stack exchnage sites.....

ypercubeᵀᴹ

if we click on your name, the link to your magento.se profile is broken. That's weird

profile 101

@ypercubeᵀᴹ yes moderatores mergeed accounts of me and my team mate..... but they left chat user names......

ypercubeᵀᴹ

@profile101 something was not done entirely right. Or it's a cache issue - and it will be fixed by itself.

War

12:21

ah ok lol

if only they had access to some guys that knew stuff about databases?

profile 101

@ypercubeᵀᴹ please tell what and all is wrong.....

ypercubeᵀᴹ

@profile101 in your profile you mean?

profile 101

no , i was asking about above design image

Lamak

@profile101 how could we know what's wrong if we have no idea what that design is supposed to be?

anyway, doesn't seem right

why a table for mobile cases and another for tshirts?, but they are both related with products_type and order_details?

profile 101

@Lamak there designer means sellers, they will sell products after registering to site

also mobile cases & tshirt are product types

if those products are sold in site, than we have to connect with order_details table right ?

Lamak

12:32

why even have a product type if you have one table per product type?

profile 101

we thought of displaying information related mobile_cases in mobile_cases table & t-shirts related information in t-shirts table

@Lamak

Lamak

@profile101 then what's the point of the product type table?

profile 101

just to connect designer id & product id

do you think its better to connect "dpd_id" [designer tables] directly to "mobile cases & t-shirts tables"

dezso

do you have only two product types, for ever?

otherwise you should read about subtyping

profile 101

no in future it will increase

@dezso

dezso

12:46

so many weird answers

6

Q: SQL Server Isnull Returning 1900-01-01 when field is null

The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results? isnull(convert(date,DOB,1),'')

sql-server sql-server-2012 datetime

Phil

15:00

The review queue needs some love

ypercubeᵀᴹ

15:29

@AndriyM Also DATEFROMPARTS ( SUBSTRING(@datevar,5,4), SUBSTRING(@datevar,3,2), SUBSTRING(@datevar,1,2) )

and you have an extra parenthesis in your code

Th eDATEFROMPARTS is probably worse, needing 3 implicit conversions from string to integer

Andriy M

15:50

@ypercubeᵀᴹ Thanks.

@ypercubeᵀᴹ If it's to be called just once to parse the argument, that shouldn't be a problem, I think.

bluefeet

19:09

@Lamak the Docs rep changes went live today, so you'll now get less for your docs activity - meta.stackoverflow.com/questions/334551/…

Lamak

@bluefeet yeah, I saw the post. But I'm not sure if I'll be able to notice if I lost the rep I wanted to lose

@bluefeet I've actually been visiting the SQL related docs, but I find that I have nothing to contribute

ypercubeᵀᴹ

@Lamak Really? So you are satisfied with the Docs regarding SQL?

Lamak

@ypercubeᵀᴹ not really, just don't know what to do

Kermit

20:32

any suggestions for an easy way to migrate a sql server database to hadoop?

one that doesn't use Sqoop

Lamak

@Kermit I have no idea, just wanted you to know that I read your comment and didn't just ignore it.

Kermit

Thanks Kamal

Lamak

no problem. Anyway, it's been ages since your last time here

Kermit

Yep. Don't have much time to spend on here.

Lamak

thought so. You seem busy

Kermit

20:36

just finished building my house

Lamak

nice, congrats (unless you are a shitty builder)

Kermit

20:53

thankfully i didn't do any of the manual labor

ypercubeᵀᴹ

21:54

thanks Justin. let me know where i can send the csv files with the data. — enderwigg 20 hours ago

The Web Stack & Architecture

Transcript for

The Heap™ – Consultancy ©®