There are 19 methods in our DAO layer, each is some variation of this: public TicketProp saveTicketProp(TicketProp prop) { EntityManager em = this.emf.createEntityManager(); try { em.getTransaction().begin(); prop = (TicketProp) em.merge(prop); em.getTransaction().commit(); return prop; } fina...
See the tables structure here. I have this sql: SELECT `feed_entries` . * FROM `feed_entries` WHERE id IN ( SELECT `e`.`id` FROM `feed_entries` AS `e` INNER JOIN `feeds` AS `f` ON e.feed_id = f.id INNER JOIN `entries_categorias` AS `ec` ON ec.entry_id = e.id INNER JOIN `categorias` AS `c` ON ec.categoria_id = c.id WHERE e.deleted ...
(I use the word var in the following because I'm not sure what it is I think it's a global variable for the class) I want to cache values that are gotten from a table in a database, to save repeated queries. What I've done is create a singleton class which has a function to check a var within the class if a key exist, if it does then ju...
Hi, How do I calculate the area of a polygon stored in a MySql database? The polygons' points are lat longs. So, degrees and minutes seem to be causing a problem. I've tried: SELECT AREA( my_polygon ) FROM `my_table` WHERE name = 'Newport' Because, the points are lat longs, I get weird results. (I'm not able to switch to Post...
Take the case of two tables: tbl_product and tbl_transaction. tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc. I need to display a web-page showing 10 products and for each product, the last 5 transactions. So far, n...
I want to get cases of wine where each bottle is between $4 and $8. The costs in the products table are all in cases of 12. Easy huh? SELECT `id` FROM `products` WHERE (`cost` / 12) > 4 AND (`cost` / 12) < 8 Now, they are beginning to sell cases of 6. Assume that this is defined only in the title string with 6 pack. How do I w...
I created an Innodb table with a id and name column and running this query insert into test (name) VALUES('test'); takes .5 seconds even SELECT id FROM test LIMIT 1 or select 1 takes .5 seconds or so Any suggestions on where I can read more about how to improve the speed would be helpful. I would imagine these queries shouldn...
Hi Everyone, I am having a frustrating issue. My web service has an email to php module. Briefly, once my user sends an email to his dedicated email address, my mail server captures the email, pipes it to a PHP script running on my server. This PHP script opens a MySQL connection and saves the email content to the database. So far so g...
I have a cron that runs through many rows, deleting the "bad" ones (according to my criteria). I'm just wondering what would be the best to optimize the script. I can do one of the following: Have the same cron instantly delete the "bad" rows upon finding them. Have the same cron instantly update the "bad" rows to status "1", meaning b...
I'm creating a Rails app that will store the opening and closing hours for a business. Originally, I thought of simply using a text data type and letting it be free-form: "Monday to Friday 9am to 5pm Saturday 11am to 4pm Closed Sundays" But, requirements have changed and I need to check the hours against the current date & time and di...
This is my directory structure: -st.ambulance ---public_html ---resources My document root is public_html. I have a script in resources that I need to execute securely (meaning: normal users shouldn't be able to execute it). How would I do this? ...
Bret Taylor discussed the SchemaLess Design in this blog post: http://bret.appspot.com/entry/how-friendfeed-uses-mysql It looks like they stored different class's Objects into only one table.Then build more index tables. my question is that how to build index on one class. for example, a user's blog is {id,userid,title,body}. A user's...
I have the below tables. create table logical_id_seq ( logical_id int auto_increment, primary key(logical_id) ); create table mytable ( physical_id int auto_increment, logical_id int not null references parent(logical_id), data varchar(20), primary key(physical_id) ); The second table uses first table auto-gen...
I am building a database that contains public, private(limited to internal) and confidential data (limited to very few). It has very specific requirements that the security of the the data is managed on the database side, but I am working in an environment where I do not have direct control of the permissions, and requests to change the...
I've managed to mess up my MySQL database (on Mac OS X 10.5) and need help recovering! I tried to add an index to a fairly large table (190 million records) and in the course of this, I ran out of disk space. Subsequently realized that the partition with the data directory is too small and so I need to move it. Initially I thought tha...
I have some MySQL database server information that needs to be shared between a Python backend and a PHP frontend. What is the best way to go about storing the information in a manner wherein it can be read easily by Python and PHP? I can always brute force it with a bunch of str.replace() calls in Python and hope it works if nobody ha...
When I print my crystal report, it will not print the correct (most recent) database view. To get it to print the correct one, i have to manually select "set datasource location" and then click on the tables i want and then click update. If i don't it will keep printing the same old view over and over. I do have the "verify on every p...
I have a file named discussion.php, where a form is located. In this form, a user will enter information, and the information will be posted to savedisc.php. Below is the code for discussion.php: <form action='savedisc.php' method='post'> <p>What would you like to discuss?</p> <textarea name='message' rows='15' cols='40'></textarea>...
I know I must be missing something simple here...but I'm having problems with writing an SQL sub query. Given the following data user_id question_id answer_text 89 1 value1 89 2 value2 80 2 value2 99 2 value2 96 1 value1 96 2 ...
I've seen posts on SO and through google stating that with Mysql you cannot have multiple foreign keys of the same name. My issue is how do I reference the same column from one table in multiple other tables. In my case I have a FAMILY table that contains FAM_ID. I want this to be a foreign key in my DOCUMENTS and CONTACT tables because ...