Search Results

Search found 8664 results on 347 pages for 'rails postgresql'.

Page 192/347 | < Previous Page | 188 189 190 191 192 193 194 195 196 197 198 199  | Next Page >

• No route matches - after login attempt - even though the route exists?

  - by datorum

  I am working on a rails application and added a simple login system according to a book. I created the controller admin: rails generate controller admin login logout index It added the following routes to routes.db get "admin/login" get "admin/logout" get "admin/index" I can got to http://localhost:3000/admin/login there is no problem at all. But when I try to login I get: No route matches "/admin/login"! Now, the first confusing part is that the "login" method of my AdminController is not executed at all. The second confusing part is that this code works like a charm - redirects everything to /admin/login: def authorize unless User.find_by_id(session[:user_id]) flash[:notice] = "you need to login" redirect_to :controller => 'admin', :action => 'login' end end Sidenotes: I restarted the server several times. I tried a different browser - to be sure there is no caching problem.

  Read the article

• nested has_many

  - by Nick Vanderbilt

  I am using Rails 2.3.5. Class User < ActiveRecord::Base has_many :phones end class Phone < ActiveRecord::Base has_many :frequency_bands end I want to get all the frequency_bands for a user. I know I can write a method def freq_bands for User but I would like to know if it is possible to have has_many freq_bands for a User. In this way I can chain the call. What I would like to have is class User < ActiveRecor::Base has_many :frequence_bands, :through => phones end I think it is possible to have nested has_many using this plugin http://github.com/ianwhite/nested_has_many_through However if possible I would like to avoid using another plugin and rely solely on rails.

  Read the article

• nested has_many

  - by dorelal

  I am using Rails 2.3.5. Class User < ActiveRecord::Base has_many :phones end class Phone < ActiveRecord::Base has_many :frequency_bands end I want to get all the frequency_bands for a user. I know I can write a method def freq_bands for User but I would like to know if it is possible to have has_many freq_bands for a User. In this way I can chain the call. What I would like to have is class User < ActiveRecor::Base has_many :frequence_bands, :through => phones end I think it is possible to have nested has_many using this plugin http://github.com/ianwhite/nested_has_many_through However if possible I would like to avoid using another plugin and rely solely on rails.

  Read the article

• Module.new with class_eval

  - by dorelal

  This is a large commit. But I want you to concentrate on this change block. http://github.com/rails/rails/commit/d916c62cfc7c59ab6411407a05b946d3dd7535e9#L2L1304 Even without understanding the full context of the code I am not able to think of a scenario where I would use include Modue.new { class_eval <<-RUBY def foo puts 'foo' end RUBY } Then end result is that in the root context (self just before include Moduel.new) a method call foo has been added. If I take out the Module.new code and if I only leave class_eval in that case also I will have a method called foo in self. What am I missing.

  Read the article

• Unit Test For NpgsqlCommand With Rhino Mocks

  - by J Pollack

  My unit test keeps getting the following error: "System.InvalidOperationException: The Connection is not open." The Test [TestFixture] public class Test { [Test] public void Test1() { NpgsqlConnection connection = MockRepository.GenerateStub<NpgsqlConnection>(); // Tried to fake the open connection connection.Stub(x => x.State).Return(ConnectionState.Open); connection.Stub(x => x.FullState).Return(ConnectionState.Open); DbQueries queries = new DbQueries(connection); bool procedure = queries.ExecutePreProcedure("201003"); Assert.IsTrue(procedure); } } Code Under Test using System.Data; using Npgsql; public class DbQueries { private readonly NpgsqlConnection _connection; public DbQueries(NpgsqlConnection connection) { _connection = connection; } public bool ExecutePreProcedure(string date) { var command = new NpgsqlCommand("name_of_procedure", _connection); command.CommandType = CommandType.StoredProcedure; NpgsqlParameter parameter = new NpgsqlParameter {DbType = DbType.String, Value = date}; command.Parameters.Add(parameter); command.ExecuteScalar(); return true; } } How would you test the code using Rhino Mocks 3.6? PS. NpgsqlConnection is a connection to a PostgreSQL server.

  Read the article

• Faster way to transfer table data from linked server

  - by spender

  After much fiddling, I've managed to install the right ODBC driver and have successfully created a linked server on SQL Server 2008, by which I can access my PostgreSQL db from SQL server. I'm copying all of the data from some of the tables in the PgSQL DB into SQL Server using merge statements that take the following form: with mbRemote as ( select * from openquery(someLinkedDb,'select * from someTable') ) merge into someTable mbLocal using mbRemote on mbLocal.id=mbRemote.id when matched /*edit*/ /*clause below really speeds things up when many rows are unchanged*/ /*can you think of anything else?*/ and not (mbLocal.field1=mbRemote.field1 and mbLocal.field2=mbRemote.field2 and mbLocal.field3=mbRemote.field3 and mbLocal.field4=mbRemote.field4) /*end edit*/ then update set mbLocal.field1=mbRemote.field1, mbLocal.field2=mbRemote.field2, mbLocal.field3=mbRemote.field3, mbLocal.field4=mbRemote.field4 when not matched then insert ( id, field1, field2, field3, field4 ) values ( mbRemote.id, mbRemote.field1, mbRemote.field2, mbRemote.field3, mbRemote.field4 ) WHEN NOT MATCHED BY SOURCE then delete; After this statement completes, the local (SQL Server) copy is fully in sync with the remote (PgSQL server). A few questions about this approach: is it sane? it strikes me that an update will be run over all fields in local rows that haven't necessarily changed. The only prerequisite is that the local and remote id field match. Is there a more fine grained approach/a way of constraining the merge statment to only update rows that have actually changed?

  Read the article

• How can I convert a projection that's not part of spatial_ref_sys?

  - by Summer

  Hi, I'm importing shapefiles into a Postgres+PostGIS database. Here's my usual procedure: * Find an srid in the spatial_ref_sys table where srtext appears to match the shapefile's .prj file * Upload the data into a new table using the shp2pgsql utility, specifying the srid using the -s flag * Add the new table to my main geometry table, and on the way convert to an srid of 4269 (the Census standard projection) using ST_Transform Unfortunately, the spatial_ref_sys table doesn't include Mississippi state's standard projection. The contents of their .prj file is as follows, where I've bolded the parts I usually try to match: PROJCS["mstm",GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",500000.0],PARAMETER["False_Northing",1300000.0],PARAMETER["Central_Meridian",-89.75],PARAMETER["Scale_Factor",0.9998335],PARAMETER["Latitude_Of_Origin",32.5],UNIT["Meter",1.0]] I eventually found the ogr2ogr utility, and especially with the "peace and joy" promises, I decided to give it a try. I tried this command: ogr2ogr -update -f "PostgreSQL" PG:"Connection details" "File name.shp" -t_srs EPSG:4269 -nln Table_Name I am now getting the error "Terminating translation prematurely after failed translation of layer" -- which seems to indicate that ogr2ogr is not going to be the savior I imagined in getting arbitrary .prj files neatly into the 4269 projection. Any ideas about what to do?

  Read the article

• Using multiple aggregate functions in an (ANSI) SQL statement

  - by morpheous

  I have aggregate functions foo(), foobar(), fredstats(), barneystats() I want to create a domain specific query language (DSQL) above my DB, to facilitate using a domain language to query the DB. The 'language' comprises of algebraic expressions (or more specifically SQL like criteria) which I use to generate (ANSI) SQL statements which are sent to the db engine. The following lines are examples of what the language statements will look like, and hopefully, it will help further clarify the concept: **Example 1** DQL statement: foobar('yellow') between 1 and 3 and fredstats('weight') > 42 Translation: fetch all rows in an underlying table where computed values for aggregate function foobar() is between 1 and 3 AND computed value for AGG FUNC fredstats() is greater than 42 **Example 2** DQL statement: fredstats('weight') < barneystats('weight') AND foo('fighter') in (9,10,11) AND foobar('green') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 3** DQL statement: foobar('green') / foobar('red') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 4** DQL statement: foobar('green') - foobar('red') >= 42 Translation: Fetch all rows where the specified criteria matches Given the following information: The table upon which the queries above are being executed is called 'tbl' table 'tbl' has the following structure (id int, name varchar(32), weight float) The result set returns only the tbl.id, tbl.name and the names of the aggregate functions as columns in the result set - so for example the foobar() AGG FUNC column will be called foobar in the result set. So for example, the first DQL query will return a result set with the following columns: id, name, foobar, fredstats Given the above, my questions then are: What would be the underlying SQL required for Example1 ? What would be the underlying SQL required for Example3 ? Given an algebraic equation comprising of AGGREGATE functions, Is there a way of generalizing the algorithm needed to generate the required ANSI SQL statement(s)? I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.

  Read the article

• PLPGSQL : How to return a record from function executed by INSERT/UPDATE rule?

  - by seas

  Do the following scheme for my database: create sequence data_sequence; create table data_table { id integer primary key; field varchar(100); }; create view data_view as select id, field from data_table; create function data_insert(_new data_view) returns data_view as $$declare _id integer; _result data_view%rowtype; begin _id := nextval('data_sequence'); insert into data_table(id, field) values(_id, _new.field); select * into _result from data_view where id = _id; return _result; end; $$ language plpgsql; create rule insert as on insert to data_view do instead select data_insert(new); Then type in psql: insert into data_view(field) values('abc'); Would like to see something like: id | field ----+--------- 1 | abc Instead see: data_insert ------------- (1, "abc") Is it possible to fix this somehow? Thanks for any ideas. Ultimate idea is to use this in other functions, so that I could obtain id of just inserted record without selecting for it from scratch. Something like: insert into data_view(field) values('abc') returning id into my_variable would be nice but doesn't work with error: ERROR: cannot perform INSERT RETURNING on relation "data_view" HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause. I don't really understand that HINT. I use PostgreSQL 8.4.

  Read the article

• SQL: Speed Improvement - Cluttered union query

  - by vol7ron

  SELECT * FROM ( SELECT a.user_id, a.f_name, a.l_name, b.user_id, b.f_name, b.l_name FROM current_tbl a INNER JOIN import_tbl b ON ( a.user_id = b.user_id ) UNION SELECT a.user_id, a.f_name, a.l_name, b.user_id, b.f_name, b.l_name FROM current_tbl a INNER JOIN import_tbl b ON ( lower(a.f_name)=lower(b.f_name) AND lower(a.l_name)=lower(b.l_name) ) ) foo -- UNION -- SELECT a.user_id , a.f_name , a.l_name , '' , '' , '' FROM current_tbl a WHERE a.user_id NOT IN ( select user_id from( SELECT a.user_id, a.f_name, a.l_name, b.user_id, b.f_name, b.l_name FROM current_tbl a INNER JOIN import_tbl b ON ( a.user_id = b.user_id ) UNION SELECT a.user_id, a.f_name, a.l_name, b.user_id, b.f_name, b.l_name FROM current_tbl a INNER JOIN import_tbl b ON ( lower(a.f_name)=lower(b.f_name) AND lower(a.l_name)=lower(b.l_name) ) ) bar ) ORDER BY user_id Example of table population: current_tbl: ------------------------------- user_id | f_name | l_name ---------+----------+---------- A1 | Adam | Acorn A2 | Beth | Berry A3 | Calv | Chard | | import_tbl: ------------------------------- user_id | f_name | l_name ---------+----------+---------- A1 | Adam | Acorn A2 | Beth | Butcher <- last_name different | | Expected Output: ----------------------------------------------------------------------- user_id1 | f_name1 | l_name1 | user_id2 | f_name2 | l_name2 ----------+-----------+-----------+------------+-----------+----------- A1 | Adam | Acorn | A1 | Adam | Acorn A2 | Beth | Berry | A2 | Beth | Butcher A3 | Calv | Chard | | | Doing this method gets rid of conditions where the row would be: A2 | Beth | Berry | A2 | Beth | Butcher But it keeps the A3 row I hope this makes sense and I haven't overly simplified it. This is a continuation question from my other question. The succession of these improvements has dropped the query down from ~32000ms to where it's at now ~1200ms - quite an improvement. I supect I can optimize by using UNION ALL in the subquery and of course the usual index optimizations, but I'm looking for the best SQL optimization. FYI this particular case is for PostgreSQL.

  Read the article

• Asynchronous callback - gwt

  - by sprasad12

  Hi, I am using gwt and postgres for my project. On the front end i have few widgets whose data i am trying to save on to tables at the back-end when i click on "save project" button(this also takes the name for the created project). In the asynchronous callback part i am setting more than one table. But it is not sending the data properly. I am getting the following error: org.postgresql.util.PSQLException: ERROR: insert or update on table "entitytype" violates foreign key constraint "entitytype_pname_fkey" Detail: Key (pname)=(Project Name) is not present in table "project". But when i do the select statement on project table i can see that the project name is present. Here is how the callback part looks like: oksave.addClickHandler(new ClickHandler(){ @Override public void onClick(ClickEvent event) { if(erasync == null) erasync = GWT.create(EntityRelationService.class); AsyncCallback<Void> callback = new AsyncCallback<Void>(){ @Override public void onFailure(Throwable caught) { } @Override public void onSuccess(Void result){ } }; erasync.setProjects(projectname, callback); for(int i = 0; i < boundaryPanel.getWidgetCount(); i++){ top = new Integer(boundaryPanel.getWidget(i).getAbsoluteTop()).toString(); left = new Integer(boundaryPanel.getWidget(i).getAbsoluteLeft()).toString(); if(widgetTitle.startsWith("ATTR")){ type = "regular"; erasync.setEntityAttribute(name1, name, type, top, left, projectname, callback); } else{ erasync.setEntityType(name, top, left, projectname, callback); } } } Question: Is it wrong to set more than one in the asynchronous callback where all the other tables are dependent on a particular table? when i say setProjects in the above code isn't it first completed and then moved on to the next one? Please any input will be greatly appreciated. Thank you.

  Read the article

• Stored insert procedure in plpgsql

  - by crazyphoton

  I want to do something like this in PostgreSQL. I tried this: CREATE or replace FUNCTION create_patient(_name text, _email text, _phone text, _password text, _field1 text, _field2 text, _field3 timestamp, _field4 text, OUT _pid integer, OUT _id integer) RETURNS record AS $$ DECLARE _id integer; _type text; _pid integer; BEGIN _type := 'patient'; INSERT into patients (name, email, phone, field1, field2, field3) values (_name, _email, _phone, _field1, _field2, _field3) RETURNING id into _pid; INSERT into users (username, password, type, pid, phone, language) values (_email, _password, _type, _pid, _phone, _field4) RETURNING id into _id; END; $$ LANGUAGE plpgsql; But there are a lot of instances where I would not want to specify some of field1/field2/field3/field4 and want the unspecified fields to use the default value in the table. Currently that is not possible, because to call this function I need to specify all fields. TLDR; Is there a simple way to create a wrapper procedure for INSERT in PL/pgSQL where I can specify which fields I want to insert?

  Read the article

• While trying to set up Django on Windows: AttributeError: 'Settings' object has no attribute 'DATABA

  - by user326370

  I'm following these instructions in order to set up Django on Windows. I have installed Python 2.6, PostgreSQL 8.4, Psycopg 2.0.14 for Python 2.6 and the latest version of Django from SVN. I'm now following these instructions to run a test project (copied from the page linked to above): C:\Documents and Settings\John>cd C:\ C:\>mkdir django C:\>cd django C:\django>django-admin.py startproject testproject C:\django>cd testproject C:\django\testproject>python manage.py runserver When I run the last line, this is the output: Validating models... Unhandled exception in thread started by <function inner_run at 0x01ECB930> Traceback (most recent call last): File "J:\Python26\lib\site-packages\django\core\management\commands\runserver.py", line 48, in inn er_run self.validate(display_num_errors=True) File "J:\Python26\lib\site-packages\django\core\management\base.py", line 249, in validate num_errors = get_validation_errors(s, app) File "J:\Python26\lib\site-packages\django\core\management\validation.py", line 22, in get_validat ion_errors from django.db import models, connection File "J:\Python26\lib\site-packages\django\db\__init__.py", line 14, in <module> if not settings.DATABASES: File "J:\Python26\lib\site-packages\django\utils\functional.py", line 273, in __getattr__ return getattr(self._wrapped, name) AttributeError: 'Settings' object has no attribute 'DATABASES' Did I forget to do something with the database? Any help will be appreciated. Thank you!

  Read the article

• Importing fixtures with foreign keys and SQLAlchemy?

  - by Chris Reid

  I've been experimenting with using fixture to load test data sets into my Pylons / PostgreSQL app. This works great except that it fails to properly create foreign keys if they reference an auto increment id field. My fixture looks like this: class AuthorsData(DataSet): class frank_herbert: first_name = "Frank" last_name = "Herbert" class BooksData(DataSet): class dune: title = "Dune" author_id = AuthorsData.frank_herbert.ref('id') And the model: t_authors = sa.Table("authors", meta.metadata, sa.Column("id", sa.types.Integer, primary_key=True), sa.Column("first_name", sa.types.String(100)), sa.Column("last_name", sa.types.String(100)), ) t_books = sa.Table("books", meta.metadata, sa.Column("id", sa.types.Integer, primary_key=True), sa.Column("title", sa.types.String(100)), sa.Column("author_id", sa.types.Integer, sa.ForeignKey('authors.id')) ) When running "paster setup-app development.ini", SQLAlchemey reports the FK value as "None" so it's obviously not finding it: 15:59:48,683 INFO [sqlalchemy.engine.base.Engine.0x...9eb0] INSERT INTO books (title, author_id) VALUES (%(title)s, %(author_id)s) RETURNING books.id 15:59:48,683 INFO [sqlalchemy.engine.base.Engine.0x...9eb0] {'author_id': None, 'title': 'Dune'} The fixture docs actually warn that this might be a problem: "However, in some cases you may need to reference an attribute that does not have a value until it is loaded, like a serial ID column. (Note that this is not supported by the SQLAlchemy data layer when using sessions.)" http://farmdev.com/projects/fixture/using-dataset.html#referencing-foreign-dataset-classes Does this mean that this is just not supported with SQLAlchemy? Or is it possible to load the data without using SA "sessions"? How are other people handling this issue?

  Read the article

• IntegrityError: foreign key violation upon delete

  - by Lukasz Korzybski

  I have Order and Shipment model. Shipment has a foreign key to Order. class Order(...): ... class Shipment() order = m.ForeignKey('Order') ... Now in one of my views I want do delete order object along with all related objects. So I invoke order.delete(). I have Django 1.0.4, PostgreSQL 8.4 and I use transaction middleware, so whole request is enclosed in single transaction. The problem is that upon order.delete() I get: ... File "/usr/local/lib/python2.6/dist-packages/django/db/backends/__init__.py", line 28, in _commit return self.connection.commit() IntegrityError: update or delete on table "main_order" violates foreign key constraint "main_shipment_order_id_fkey" on table "main_shipment" DETAIL: Key (id)=(45) is still referenced from table "main_shipment". I checked in connection.queries that proper queries are executed in proper order. First shipment is deleted, after that django executes delete on order row: {'time': '0.000', 'sql': 'DELETE FROM "main_shipment" WHERE "id" IN (17)'}, {'time': '0.000', 'sql': 'DELETE FROM "main_order" WHERE "id" IN (45)'} Foreign key have ON DELETE NO ACTION (default) and is initially deferred. I don't know why I get foreign key constraint violation. I also tried to register pre_delete signal and manually delete shipment objects before delete on order is called, but it resulted in the same error. I can change ON DELETE behaviour for this key in Postgres but it would be just a hack, I wonder if anyone has a better idea what's going on here. There is also a small detail, my Order model inherits from Cart model, so it actually doesn't have id field but cart_ptr_id and after DELETE on order is executed there is also DELETE on cart, but it seems unrelated? to the shipment-order problem so I simplified it in the example.

  Read the article

• I'm trying to install psycopg2 onto Mac OS 10.6.3; it claims it can't find "stdarg.h" but I can see

  - by cojadate

  I'm desperately trying to successfully install psycopg2 but keep running into errors. The latest one seems to involve it not being to find "stdarg.h" (see code below). However I can see with my own eyes that a file called stdarg.h exists at /Developer/SDKs/MacOSX10.4u.sdk/usr/include/stdarg.h (where it claims it can't find anything) so I've no idea what to do about it. I'm running Mac OS 10.6.3 and within the last few days I've made sure I have all the latest OS developer tools. I have Python 2.6.2 and PostgreSQL 8.4 if that makes any difference. python setup.py install running install running build running build_py running build_ext building 'psycopg2._psycopg' extension creating build/temp.macosx-10.3-fat-2.6 creating build/temp.macosx-10.3-fat-2.6/psycopg gcc -arch ppc -arch i386 -isysroot /Developer/SDKs/MacOSX10.4u.sdk -fno-strict-aliasing -fno-common -dynamic -DNDEBUG -g -O3 -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.2.1 (dt dec ext pq3)" -DPG_VERSION_HEX=0x080404 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -DHAVE_PQPROTOCOL3=1 -I/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6 -I. -I/opt/local/include/postgresql84 -I/opt/local/include/postgresql84/server -c psycopg/psycopgmodule.c -o build/temp.macosx-10.3-fat-2.6/psycopg/psycopgmodule.o In file included from /Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/unicodeobject.h:4, from /Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/Python.h:85, from psycopg/psycopgmodule.c:27: /Developer/SDKs/MacOSX10.4u.sdk/usr/include/stdarg.h:4:25: error: stdarg.h: No such file or directory In file included from /Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/unicodeobject.h:4, from /Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/Python.h:85, from psycopg/psycopgmodule.c:27: /Developer/SDKs/MacOSX10.4u.sdk/usr/include/stdarg.h:4:25: error: stdarg.h: No such file or directory lipo: can't figure out the architecture type of: /var/folders/MQ/MQ-tWOWWG+izzuZCrAJpzk+++TI/-Tmp-//ccakFhRS.out error: command 'gcc' failed with exit status

  Read the article

• JPA2 Criteria API creates invalid SQL when using groupBy

  - by Stephan

  JPA2 with the Criteria API seems to generate invalid SQL for PostgreSQL. For this code: Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class); Path<DBObject> object = from.get(DBObjectAccessCounter_.object); Expression<Long> sum = builder.sumAsLong(from.get(DBObjectAccessCounter_.count)); query.multiselect(object, sum).groupBy(object); I get the following exception: ERROR: column "dbobject1_.id" must appear in the GROUP BY clause or be used in an aggregate function The generated SQL is: select dbobjectac0_.object_id as col_0_0_, sum(dbobjectac0_.count) as col_1_0_, dbobject1_.id as id1001_, dbobject1_.name as name1013_, dbobject1_.lastChanged as lastChan2_1013_, dbobject1_.type_id as type3_1013_ from DBObjectAccessCounter dbobjectac0_ inner join DBObject dbobject1_ on dbobjectac0_.object_id=dbobject1_.id group by dbobjectac0_.object_id Obviously, the first item of the select statement (dbobjectac0_.object_id) does not match the group by clause. Simplified example It does not even work for this simple example: Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class); Path<DBObject> object = from.get(DBObjectAccessCounter_.object); query.select(object).groupBy(object); which returns select dbobject1_.id as id924_, dbobject1_.name as name933_, dbobject1_.lastChanged as lastChan2_933_, dbobject1_.type_id as type3_933_ from DBObjectAccessCounter dbobjectac0_ inner join DBObject dbobject1_ on dbobjectac0_.object_id=dbobject1_.id group by dbobjectac0_.object_id Does anyone know how to fix this?

  Read the article

• Combining aggregate functions in an (ANSI) SQL statement

  - by morpheous

  I have aggregate functions foo(), foobar(), fredstats(), barneystats() I want to create a domain specific query language (DSQL) above my DB, to facilitate using using a domain language to query the DB. The 'language' comprises of boolean expressions (or more specifically SQL like criteria) which I then 'translate' back into pure (ANSI) SQL and send to the underlying Db. The following lines are examples of what the language statements will look like, and hopefully, it will help further clarify the concept: **Example 1** DQL statement: foobar('yellow') between 1 and 3 and fredstats('weight') > 42 Translation: fetch all rows in an underlying table where computed values for aggregate function foobar() is between 1 and 3 AND computed value for AGG FUNC fredstats() is greater than 42 **Example 2** DQL statement: fredstats('weight') < barneystats('weight') AND foo('fighter') in (9,10,11) AND foobar('green') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 3** DQL statement: foobar('green') / foobar('red') <> 42 Translation: Fetch all rows where the specified criteria matches **Example 4** DQL statement: foobar('green') - foobar('red') >= 42 Translation: Fetch all rows where the specified criteria matches Given the following information: The table upon which the queries above are being executed is called 'tbl' table 'tbl' has the following structure (id int, name varchar(32), weight float) The result set returns only the tbl.id, tbl.name and the names of the aggregate functions as columns in the result set - so for example the foobar() AGG FUNC column will be called foobar in the result set. So for example, the first DQL query will return a result set with the following columns: id, name, foobar, fredstats Given the above, my questions then are: What would be the underlying SQL required for Example1 ? What would be the underlying SQL required for Example3 ? Given an algebraic equation comprising of AGGREGATE functions, Is there a way of generalizing the algorithm needed to generate the required ANSI SQL statement(s)? I am using PostgreSQL as the db, but I would prefer to use ANSI SQL wherever possible.

  Read the article

• Starting with NHibernate

  - by George

  I'm having major difficulties to start off with NHiberante. Main problems: Where my hbm.xml files should reside? I create a Mappings folder but I received an error "Could not find xxx.hbm.xml file." I tried to load the specific class through the dialect cf.AddClass(typeof(xxx)); but it still gives me the same error (the files are marked as embebed resources. Also I'm having major problems in connection to it. I stopped trying to use the cfg xml file and tried a more direct approach with a library I have here. Configuration cfg = new Configuration(); cfg.AddClass(typeof(Tag)); ISessionFactory sessions = cfg.BuildSessionFactory(); AgnosticConnectionHandler agch = new AgnosticConnectionHandler("xxx","xxx","geo_biblio","localhost", 5432,DatabaseInstance.PostgreSQL); ISession sessao = sessions.OpenSession(agch.GetConnection); ITransaction tx = sessao.BeginTransaction(); Tag tag1 = new Tag(); tag1.NomeTag = "Teste Tag NHibernate!!!"; sessao.Save(tag1); tx.Commit(); sessao.Close(); Any tips for me? I'm getting the exception in line 2 of this code, and still not sure what to do. Any help is appreciated. Thanks

  Read the article

• Calculate differences between rows while grouping with SQL

  - by Guido

  I have a postgresql table containing movements of different items (models) between warehouses. For example, the following record means that 5 units of model 1 have been sent form warehouse 1 to 2: source target model units ------ ------ ----- ----- 1 2 1 5 I am trying to build a SQL query to obtain the difference between units sent and received, grouped by models. Again with an example: source target model units ------ ------ ----- ----- 1 2 1 5 -- 5 sent from 1 to 2 1 2 2 1 2 1 1 2 -- 2 sent from 2 to 1 2 1 1 1 -- 1 more sent from 2 to 1 The result should be: source target model diff ------ ------ ----- ---- 1 2 1 2 -- 5 sent minus 3 received 1 2 2 1 I wonder if this is possible with a single SQL query. Here is the table creation script and some data, just in case anyone wants to try it: CREATE TEMP TABLE movements ( source INTEGER, target INTEGER, model INTEGER, units INTEGER ); insert into movements values (1,2,1,5); insert into movements values (1,2,2,1); insert into movements values (2,1,1,2); insert into movements values (2,1,1,1);

  Read the article

• Strategy for locale sensitive sort with pagination

  - by Thom Birkeland

  Hi, I work on an application that is deployed on the web. Part of the app is search functions where the result is presented in a sorted list. The application targets users in several countries using different locales (= sorting rules). I need to find a solution for sorting correctly for all users. I currently sort with ORDER BY in my SQL query, so the sorting is done according to the locale (or LC_LOCATE) set for the database. These rules are incorrect for those users with a locale different than the one set for the database. Also, to further complicate the issue, I use pagination in the application, so when I query the database I ask for rows 1 - 15, 16 - 30, etc. depending on the page I need. However, since the sorting is wrong, each page contains entries that are incorrectly sorted. In a worst case scenario, the entire result set for a given page could be out of order, depending on the locale/sorting rules of the current user. If I were to sort in (server side) code, I need to retrieve all rows from the database and then sort. This results in a tremendous performance hit given the amount of data. Thus I would like to avoid this. Does anyone have a strategy (or even technical solution) for attacking this problem that will result in correctly sorted lists without having to take the performance hit of loading all data? Tech details: The database is PostgreSQL 8.3, the application an EJB3 app using EJB QL for data query, running on JBoss 4.5.

  Read the article

• Database frontend for multiple db engines

  - by xeroxed_yeti

  Hey Stackoverflow, yeah it's spring and a lot of things happens to me... Also changing some software things at my computer, because suddenly everything seems to be boring after starting my laptop. I even changed my wallpaper!!! Besides I'm looking for a new database frontend and after using google with serveral queries I didn't find the right software. You have to know, my laptop and me are very very special :) I'm looking for a database frontend which should have following features can access PostgreSQL and MySQL databases can handle schemata overs a nice sql query tool supports an import and export functionality (something like tab separated text files) it for free looks awesome - every time when a college come to my office he must get the feeling: oh boy, this man really knows his job and should get more money! At the moment I used phpmyadmin, phppgadmin, pgadminIII, mysqladmin and dbVisualizer. Furthermore I was a big fan of the aqua datastudio until it became commercial. This tools offers a great variety of functionalities which can simplify programmes live. However, now you have to buy a license...I'm a scientist and money for software is limited =) So it's my first time (question) here at stackoverflow please be cheerful :)

  Read the article

• Error using to_char // to_timestamp

  - by pepersview

  Hello, I have a database in PostgreSQL and I'm developing an application in PHP using this database. The problem is that when I execute the following query I get a nice result in phpPgAdmin but in my PHP application I get an error. The query: SELECT t.t_name, t.t_firstname FROM teachers AS t WHERE t.id_teacher IN (SELECT id_teacher FROM teacher_course AS tcourse JOIN course_timetable AS coursetime ON tcourse.course = coursetime.course AND to_char(to_timestamp('2010-4-12', 'YYYY-MM-DD'),'FMD') = (coursetime.day +1)) AND t.id_teacher NOT IN (SELECT id_teacher FROM teachers_fill WHERE date = '2010-4-12') ORDER BY t.t_name ASC And this is the error in PHP operator does not exist: text = integer (to_timestamp('', 'YYYY-MM-DD'),'FMD') = (courset... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. The purpose to solve this error is to use the ORIGINAL query in php with : $date = "2010"."-".$selected_month."-".$selected_day; SELECT t.t_name, t.t_firstname FROM teachers AS t WHERE t.id_teacher IN (SELECT id_teacher FROM teacher_course AS tcourse JOIN course_timetable AS coursetime ON tcourse.course = coursetime.course AND to_char(to_timestamp('$date', 'YYYY-MM-DD'),'FMD') = (coursetime.day +1)) AND t.id_teacher NOT IN (SELECT id_teacher FROM teachers_fill WHERE date = '$date') ORDER BY t.t_name ASC

  Read the article

• ISBNs are used as primary key, now I want to add non-book things to the DB - should I migrate to EAN

  - by fish2000

  I built an inventory database where ISBN numbers are the primary keys for the items. This worked great for a while as the items were books. Now I want to add non-books. some of the non-books have EANs or ISSNs, some do not. It's in PostgreSQL with django apps for the frontend and JSON api, plus a few supporting python command-line tools for management. the items in question are mostly books and artist prints, some of which are self-published. What is nice about using ISBNs as primary keys is that in on top of relational integrity, you get lots of handy utilities for validating ISBNs, automatically looking up missing or additional information on the book items, etcetera, many of which I've taken advantage. some such tools are off-the-shelf (PyISBN, PyAWS etc) and some are hand-rolled -- I tried to keep all of these parts nice and decoupled, but you know how things can get. I couldn't find anything online about 'private ISBNs' or 'self-assigned ISBNs' but that's the sort of thing I was interested in doing. I doubt that's what I'll settle on, since there is already an apparent run on ISBN numbers. should I retool everything for EAN numbers, or migrate off ISBNs as primary keys in general? if anyone has any experience with working with these systems, I'd love to hear about it, your advice is most welcome.

  Read the article

• table design for storing large number of rows

  - by hyperboreean

  I am trying to store in a postgresql database some unique identifiers along with the site they have been seen on. I can't really decide which of the following 3 option to choose in order to be faster and easy maintainable. The table would have to provide the following information: the unique identifier which unfortunately it's text the sites on which that unique identifier has been seen The amount of data that would have to hold is rather large: there are around 22 millions unique identifiers that I know of. So I thought about the following designs of the table: id - integer identifier - text seen_on_site - an integer, foreign key to a sites table This approach would require around 22 mil multiplied by the number of sites. id - integer identifier - text seen_on_site_1 - boolean seen_on_site_2 - boolean ............ seen_on_site_n - boolean Hopefully the number of sites won't go past 10. This would require only the number of unique identifiers that I know of, that is around 20 millions, but it would make it hard to work with it from an ORM perspective. one table that would store only unique identifiers, like in: id - integer unique_identifier - text, one table that would store only sites, like in: id - integer site - text and one many to many relation, like: id - integer, unique_id - integer (fk to the table storing identifiers) site_id - integer (fk to sites table) another approach would be to have a table that stores unique identifiers for each site So, which one seems like a better approach to take on the long run?

  Read the article

< Previous Page | 188 189 190 191 192 193 194 195 196 197 198 199  | Next Page >