I have a huge codebase that has a lot of JAVA and .sql files. I intend to extract all the SQL statements from all these files. Here is the way I intend to achieve this – Build a regex file containing patterns like select, insert, delete, update etc that I intend to extract. Parse files line by line in c…
Custom fields / attributes model
I need to implement custom fields in a booking software. I need to extend some tables containing, for example, the user groups with dynamic attributes. But also, a product table where each product can have custom fields (and ideally these fields could be nested). I already made some searches about EAV but I r…
inner join with condition on joined table
I’m trying to collect data using the following query: SELECT * FROM `table1` AS `t1` INNER JOIN `table2` AS `t2` ON `t1`.`id`=`t2`.`id` WHERE `t2`.`name`=’myname’ It looks like the WHERE …
Insertion Anomaly
I am learning insertion anomaly from here. Following data is written in it, Insert Anomalies An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the …
Configuring TFS – Advanced wizard
Perhaps this was asked before but I can’t find a whole lot on this, so I would appreciate some help. Our architecture is as follows: Win 7 desktop on a domain with VS 2010. MS Sql server R2 on Win Server 2008 R2 Ent; SharePoint 2007 on Win 2003; SharePoint 2010 on Win 2008 R2 Ent; Visual Sourcesafe on y…
Join two tables only if a value is null or a specific number
I have three tables in a database: Product table – +100000 entries Attribute table (list of possible attributes of a product) Product attribtue table (which contains the value of the attribute of a product) I am looking for 8 random products and one of their attributes (attribute_id = 2), but if a produ…
How to count rows with SELECT COUNT(*) with SQLAlchemy?
I’d like to know if it’s possible to generate a SELECT COUNT(*) FROM TABLE statement in SQLAlchemy without explicitly asking for it with execute(). If I use: session.query(table).count() then it …
mysql allow invalid dates on select
For some reason, I can’t even select invalid dates in my table. How do I force select? I just receive: select * from table >> Mysql2::Error: Invalid date: 1900-00-00 I’m not trying to insert, …
setting session id variable from sql database; is this safe?
Is it unsafe to use the user_id in my sql table as the session id? is this normally what php developers do? (source: sockface.com) Also I’ve tried countless times to store the session id as the …
Query using ILIKE with IN
Is it possible to run a query using ILIKE with the IN function? For example: I want to run the results returned from the nested query through the ILIKE function. Is there a way to do this? Answer Can be simpler: You can check the resulting query plan with EXPLAIN ANALYZE. You may need to add leading and trail…