Mercurial > p > roundup > code
comparison roundup/backends/rdbms_common.py @ 2733:ef396596a24e
more efficient find() in RDBMS [SF#1012781]
| author | Richard Jones <richard@users.sourceforge.net> |
|---|---|
| date | Fri, 08 Oct 2004 01:28:32 +0000 |
| parents | 7c472ed6babf |
| children | e3cd28cec23d |
comparison
equal
deleted
inserted
replaced
| 2731:7c472ed6babf | 2733:ef396596a24e |
|---|---|
| 1 # $Id: rdbms_common.py,v 1.135 2004-10-08 00:56:12 richard Exp $ | 1 # $Id: rdbms_common.py,v 1.136 2004-10-08 01:28:32 richard Exp $ |
| 2 ''' Relational database (SQL) backend common code. | 2 ''' Relational database (SQL) backend common code. |
| 3 | 3 |
| 4 Basics: | 4 Basics: |
| 5 | 5 |
| 6 - map roundup classes to relational tables | 6 - map roundup classes to relational tables |
| 1902 if not isinstance(prop, Link) and not isinstance(prop, Multilink): | 1902 if not isinstance(prop, Link) and not isinstance(prop, Multilink): |
| 1903 raise TypeError, "'%s' not a Link/Multilink property"%propname | 1903 raise TypeError, "'%s' not a Link/Multilink property"%propname |
| 1904 | 1904 |
| 1905 # first, links | 1905 # first, links |
| 1906 a = self.db.arg | 1906 a = self.db.arg |
| 1907 allvalues = (1,) | 1907 allvalues = () |
| 1908 o = [] | 1908 sql = [] |
| 1909 where = [] | 1909 where = [] |
| 1910 for prop, values in propspec: | 1910 for prop, values in propspec: |
| 1911 if not isinstance(props[prop], hyperdb.Link): | 1911 if not isinstance(props[prop], hyperdb.Link): |
| 1912 continue | 1912 continue |
| 1913 if type(values) is type({}) and len(values) == 1: | 1913 if type(values) is type({}) and len(values) == 1: |
| 1924 values.remove(None) | 1924 values.remove(None) |
| 1925 s = '_%s is NULL or '%prop | 1925 s = '_%s is NULL or '%prop |
| 1926 allvalues += tuple(values) | 1926 allvalues += tuple(values) |
| 1927 s += '_%s in (%s)'%(prop, ','.join([a]*len(values))) | 1927 s += '_%s in (%s)'%(prop, ','.join([a]*len(values))) |
| 1928 where.append('(' + s +')') | 1928 where.append('(' + s +')') |
| 1929 tables = ['_%s'%self.classname] | |
| 1930 if where: | 1929 if where: |
| 1931 o.append('(' + ' and '.join(where) + ')') | 1930 allvalues = (1, ) + allvalues |
| 1931 sql.append('''select id from _%s where __retired__ <> %s | |
| 1932 and %s'''%(self.classname, a, ' and '.join(where))) | |
| 1932 | 1933 |
| 1933 # now multilinks | 1934 # now multilinks |
| 1934 for prop, values in propspec: | 1935 for prop, values in propspec: |
| 1935 if not isinstance(props[prop], hyperdb.Multilink): | 1936 if not isinstance(props[prop], hyperdb.Multilink): |
| 1936 continue | 1937 continue |
| 1937 if not values: | 1938 if not values: |
| 1938 continue | 1939 continue |
| 1940 allvalues += (1, ) | |
| 1939 if type(values) is type(''): | 1941 if type(values) is type(''): |
| 1940 allvalues += (values,) | 1942 allvalues += (values,) |
| 1941 s = a | 1943 s = a |
| 1942 else: | 1944 else: |
| 1943 allvalues += tuple(values.keys()) | 1945 allvalues += tuple(values.keys()) |
| 1944 s = ','.join([a]*len(values)) | 1946 s = ','.join([a]*len(values)) |
| 1945 tn = '%s_%s'%(self.classname, prop) | 1947 tn = '%s_%s'%(self.classname, prop) |
| 1946 tables.append(tn) | 1948 sql.append('''select id from _%s, %s where __retired__ <> %s |
| 1947 o.append('(id=%s.nodeid and %s.linkid in (%s))'%(tn, tn, s)) | 1949 and id = %s.nodeid and %s.linkid in (%s)'''%(self.classname, |
| 1948 | 1950 tn, a, tn, tn, s)) |
| 1949 if not o: | 1951 |
| 1952 if not sql: | |
| 1950 return [] | 1953 return [] |
| 1951 elif len(o) > 1: | 1954 sql = ' union '.join(sql) |
| 1952 o = '(' + ' or '.join(['(%s)'%i for i in o]) + ')' | |
| 1953 else: | |
| 1954 o = o[0] | |
| 1955 t = ', '.join(tables) | |
| 1956 sql = 'select distinct(id) from %s where __retired__ <> %s and %s'%( | |
| 1957 t, a, o) | |
| 1958 self.db.sql(sql, allvalues) | 1955 self.db.sql(sql, allvalues) |
| 1959 # XXX numeric ids | 1956 # XXX numeric ids |
| 1960 l = [str(x[0]) for x in self.db.sql_fetchall()] | 1957 l = [str(x[0]) for x in self.db.sql_fetchall()] |
| 1961 return l | 1958 return l |
| 1962 | 1959 |