public static IList<InteractiveMap> FindAllByQuery(string q)
{
string terms = QueryParser.BuildContainsTerms(q);
SimpleQuery<InteractiveMap> query = new SimpleQuery<InteractiveMap>(QueryLanguage.Sql,
String.Format(@"
SELECT *
FROM web_interactive_maps as interactive_map
WHERE CONTAINS (interactive_map.title, '{0}')
OR CONTAINS (interactive_map.abstract, '{0}')",
terms
)
);
query.AddSqlReturnDefinition(typeof(InteractiveMap), "interactive_map");
return query.Execute();
}
public void Can_delete_instances()
{
InitializeLazy();
using (new SessionScope())
CreateLazyBlog();
var query = new SimpleQuery<BlogLazy>("from BlogLazy b where b.Author = ?", "Mort");
using (new StatelessSessionScope())
{
foreach (var blog in query.Execute())
blog.Delete();
}
Assert.AreEqual(0, BlogLazy.FindAll().Length);
}
public static Template[] FindAllPublic()
{
SimpleQuery q = new SimpleQuery(typeof(Template), @"from Template where Public = true");
Template[] allpublic = (Template[]) ExecuteQuery(q);
return allpublic;
}
public void Money()
{
ActiveRecordMediator<Form>.Create(new Form {Qty = 5, Price = 29.95m});
var totals = new SimpleQuery<object>(typeof (Form), "select sum(f.Price * f.Qty) from Form f").Execute();
Assert.IsInstanceOfType(typeof (decimal), totals.First());
}
public static ScheduledEvent[] FindByDateAndUser(string datetime, User user)
{
if ((datetime.Length > 0) && (user != null))
{
/* This works on hibernate but not in nhibernate yet:
return (ScheduledEvent[]) ActiveRecordBase.FindOne(typeof(ScheduledEvent),
Expression.geProperty("StartDate", datetime),
Expression.ltProperty("EndDate", datetime),
Expression.eq("Schedule", user.Schedule));
*/
SimpleQuery q = new SimpleQuery(typeof(ScheduledEvent), @"
from ScheduledEvent S
where S.StartDate < ? and
S.EndDate > ? and
S.Schedule.Owner = ?", datetime, datetime, user);
return (ScheduledEvent[]) ExecuteQuery(q);
}
else
return null;
}
/// <summary>
/// Return all events in year and month
/// </summary>
/// <param name="sdel">Schedule where search</param>
/// <param name="year">Year where search</param>
/// <param name="month">Month to get the events</param>
public static ScheduledEvent[] GetEventsInMonth(Schedule sdle, int year, int month)
{
// DateTime sd = new DateTime(year, month, System.DateTime.DaysInMonth(year, month), 23, 59, 59);
// DateTime ed = new DateTime(year, month, 1, 0, 0, 0);
DateTime beginMonth = new DateTime(year, month, 1, 0, 0, 0);
DateTime endMonth = new DateTime(year, month, System.DateTime.DaysInMonth(year, month), 23, 59, 59);
SimpleQuery q = new SimpleQuery(typeof(ScheduledEvent), @"
FROM ScheduledEvent S
WHERE
S.Schedule = ? AND
S.StartDate < ? AND
S.EndDate > ?
", sdle, endMonth, beginMonth);
return (ScheduledEvent[]) ExecuteQuery(q);
}
// and S.StartDate <= ?
// and S.EndDate >= ?
public static ScheduledEvent[] GetOrderedEvents(Schedule sdle)
{
SimpleQuery q = new SimpleQuery(typeof(ScheduledEvent), @"
from ScheduledEvent S
where S.Schedule = ?
order by S.StartDate", sdle.Id);
return (ScheduledEvent[]) ExecuteQuery(q);
}
public void Collections_can_be_fetched_with_queries()
{
InitializeLazy();
var blog = new BlogLazy { Author = "Mort", Name = "Hourglass" };
var post = new PostLazy { Blog = blog, Title = "...", Created = DateTime.Now };
blog.Save();
post.Save();
var query = new SimpleQuery<BlogLazy>("from BlogLazy b join fetch b.Posts");
using (new StatelessSessionScope())
{
var result = query.Execute();
Assert.AreEqual(1, result.Length);
Assert.AreEqual(1, result[0].Posts.Count);
var queriedPost = result[0].Posts[0] as PostLazy;
Assert.AreEqual("...", queriedPost.Title);
}
}
public void Querying_works_with_HQL()
{
InitializeLazy();
using (new SessionScope())
CreateLazyBlog();
var query = new SimpleQuery<BlogLazy>("from BlogLazy b where b.Author = ?", "Mort");
using (new StatelessSessionScope())
Assert.AreEqual(1, query.Execute().Length);
}
public static Forum[] FindAllOrderByDateTime()
{
SimpleQuery q = new SimpleQuery(typeof(Forum), @"
from Forum F
order by F.Date");
return (Forum[]) ExecuteQuery(q);
}
public void TestExceptionNotThrownWhenOverrideProvided()
{
var results = new SimpleQuery<ProductWithGuid>(Query, new ValueAndTypeTuple(NHibernateUtil.String, Key1.ToString()[0] + "%")).Execute();
Assert.That(results.Length, Is.EqualTo(1));
}
public static Menu FindByCode(string code)
{
SimpleQuery q = new SimpleQuery(typeof(Menu), @"
from Menu M
where M.Code = ?", code);
Menu[] menus = (Menu[])ExecuteQuery(q);
if ((menus != null) && (menus.Length > 0))
return menus[0];
else
return null;
}
public static Chat[] FindByUser(IList groups)
{
IList tmp = new ArrayList();
foreach (Group g in groups)
tmp.Add(g.Id);
SimpleQuery q = new SimpleQuery(typeof(Chat), @"
from Chat C
where
C.OGroup.Id in (select Id from Acl)
");
return (Chat[])ExecuteQuery(q);
}
public static Field[] FindAllOrderByCode()
{
SimpleQuery q = new SimpleQuery(typeof(Field), @"
from Field F
order by F.Code");
return (Field[])ExecuteQuery(q);
}
public static Field FindByCode(string code)
{
SimpleQuery q = new SimpleQuery(typeof(Field), @"
from Field
where F.Code = ?", code);
Field[] fields = (Field[])ExecuteQuery(q);
if ((fields != null) && (fields.Length > 0))
return fields[0];
else
return null;
}
public void Enumerating_with_queries_doesnt_work()
{
InitializeLazy();
using (new SessionScope())
CreateLazyBlog();
var query = new SimpleQuery<BlogLazy>("from BlogLazy b where b.Author = ?", "Mort");
using (new StatelessSessionScope())
query.Enumerate();
}
public void Nonlazy_collections_can_be_fetched_with_queries()
{
ActiveRecordStarter.Initialize(GetConfigSource(), typeof(SimpleBlog), typeof(SimplePost));
Recreate();
var blog = new SimpleBlog { Name = "Blog" };
var post = new SimplePost { Blog = blog, Title = "Post" };
blog.Save();
post.Save();
var query = new SimpleQuery<SimpleBlog>("from SimpleBlog b join fetch b.Posts");
using (new StatelessSessionScope())
{
var result = query.Execute();
Assert.AreEqual(1, result.Length);
Assert.AreEqual(1, result[0].Posts.Count);
Assert.AreEqual("Post", result[0].Posts[0].Title);
Assert.AreSame(result[0], result[0].Posts[0].Blog);
}
}
public static Schedule FindByUser(User user)
{
SimpleQuery q = new SimpleQuery(typeof(Schedule), @"
from Schedule S
where S.Owner = ?", user);
Schedule[] schedules = (Schedule[])ExecuteQuery(q);
if (schedules.Length == 0)
return null;
else
return schedules[0];
}
public QueryResults ExecuteQuery(string hql)
{
var q = new SimpleQuery(typeof(ActiveRecordBase), typeof(object), hql);
return new QueryResults(q);
}
public static User[] FindAllWithoutRoot()
{
SimpleQuery q = new SimpleQuery(typeof(User), @"from User where Name != 'root'");
User[] allwithout = (User[]) ExecuteQuery(q);
return (User[]) allwithout;
}
public static ScheduledEvent[] GetEventsOverlap(Schedule s, ScheduledEvent sdle)
{
SimpleQuery q = new SimpleQuery(typeof(ScheduledEvent), @"
from ScheduledEvent S
where S.Schedule = ?
and ((S.StartDate <= ? and S.EndDate >= ?)
or (S.StartDate <= ? and S.EndDate >= ?)
or (S.StartDate >= ? and S.EndDate <= ?))
and S.Id != ?
order by S.StartDate", s, sdle.StartDate, sdle.StartDate, sdle.EndDate, sdle.EndDate, sdle.StartDate, sdle.EndDate, sdle.Id);
// order by S.StartDate", s, sdle.StartDate, sdle.StartDate, sdle.Id);
return (ScheduledEvent[]) ExecuteQuery(q);
}
public Group[] NotGroups()
{
SimpleQuery q = new SimpleQuery(typeof(Group), @"from Group where Id not in (4)");
Group[] allwithout = (Group[]) ExecuteQuery(q);
return allwithout;
}
public static ScheduledEvent[] GetNextOrderedEvents(Schedule sdle, DateTime d)
{
//string dt = Shidix.PublicAPI.Date2Postgres(d);
//Shidix.PublicAPI.Logger("Schedule id= " + sdle.Id);
//Shidix.PublicAPI.Logger("datetime = " + dt);
SimpleQuery q = new SimpleQuery(typeof(ScheduledEvent), @"
from ScheduledEvent S
where S.Schedule = ?
and S.StartDate > ?
order by S.StartDate", sdle, d);
return (ScheduledEvent[]) ExecuteQuery(q);
}
public static string FindPasswordByUsername(string username)
{
SimpleQuery q = new SimpleQuery(typeof(User), @"
from User U
where U.Name = ?", username);
User u = (User) ExecuteQuery(q);
return u.UserPassword;
}
/// <summary>
/// Return all events in a day
/// </summary>
/// <param name="sdel">Schedule where search</param>
/// <param name="dt">DateTime with the day</param>
public static ScheduledEvent[] GetEventsInDay(Schedule sdle, DateTime dt)
{
DateTime dtNextDay = dt.AddDays(1);
DateTime dtBeforeDay = dt.AddSeconds(-1);
SimpleQuery q = new SimpleQuery(typeof(ScheduledEvent), @"
FROM ScheduledEvent S
WHERE
S.Schedule = ? AND
((S.StartDate >= ?) AND (S.EndDate < ?) OR
(S.StartDate > ?) AND (S.StartDate < ?) AND (S.EndDate > ?) OR
(S.StartDate < ?) AND (S.EndDate < ?) AND (S.EndDate > ?) OR
(S.StartDate < ?) AND (S.EndDate > ?))
", sdle, dt, dtNextDay, dtBeforeDay, dtNextDay, dt, dt, dtNextDay, dtBeforeDay, dt, dt);
return (ScheduledEvent[]) ExecuteQuery(q);
}
public static MenuTranslation FindByMenuAndLang(Menu menu, string lang)
{
SimpleQuery q = new SimpleQuery(typeof(MenuTranslation), @"
from MenuTranslation T
where
T.Menu = ? and
T.Language = ?", menu, lang);
MenuTranslation[] translations = (MenuTranslation[])ExecuteQuery(q);
if (translations.Length > 0)
return translations[0];
else
return null;
}
public void TestExpressionQuerySubProperty()
{
Blog blog = new Blog();
blog.Name = "hammett's blog";
blog.Author = "hamilton verissimo";
blog.Save();
Blog blog2 = new Blog();
blog2.Name = "hammett's blog other blog";
blog2.Author = "hamilton verissimo 2";
blog2.Save();
Post post1 = new Post(blog, "title1", "contents", "category1");
Post post2 = new Post(blog, "title2", "contents", "category2");
Post post3 = new Post(blog, "title3", "contents", "category3");
Post post21 = new Post(blog2, "title21", "contents", "category21");
Post post22 = new Post(blog2, "title22", "contents", "category22");
Post post23 = new Post(blog2, "title23", "contents", "category23");
post1.Save();
post2.Save();
post3.Save();
post21.Save();
post22.Save();
post23.Save();
//no idea how to make this style of query work.
//Post[] posts = Post.FindAll(
// Expression.Eq("Blog.Name", blog2.Name)
// );
//Assert.IsTrue(posts.Length > 0);
SimpleQuery<Post> q =
new SimpleQuery<Post>(typeof(Post), "from Post p where p.Id = ? or p.Blog.Name = ?", 1, "hammett's blog other blog");
Post[] p = q.Execute();
Assert.IsTrue(p.Length > 0);
}
public static MenuTranslation[] FindByMenu(Menu menu)
{
SimpleQuery q = new SimpleQuery(typeof(MenuTranslation), @"
from MenuTranslation T
where T.Menu = ?", menu);
return (MenuTranslation[]) ExecuteQuery(q);
}
public static Category[] SearchByWord2(string s)
{
string sql;
char[] charSeparators = new char[] {',',' ',';','.'};
string[] ssplit = s.Split(charSeparators);
if (s == null)
return new Category[0];
sql = "from Category C where C.Description ~* '" + s;
sql += "' or C.Name ~* '" + s;
sql += "' or C.Information ~* '" + s;
sql += "' or C.Description ~* '" + s;
sql += "'";
foreach (string substr in ssplit)
{
if (substr != "") {
sql += " or C.Description ~* '" + substr;
sql += "' or C.Name ~* '" + substr;
sql += "' or C.Information ~* '" + substr;
sql += "'";
}
}
SimpleQuery q = new SimpleQuery(typeof(Category), sql);
Category[] category = (Category[])ExecuteQuery(q);
if (category.Length == 0) {
return new Category[0];
} else
return category;
}
public static ChatMessage[] FindLast(int chat, int id, int minutes)
{
DateTime date = System.DateTime.Now.AddMinutes(- minutes);
SimpleQuery q = new SimpleQuery(typeof(ChatMessage), @"
FROM ChatMessage C
WHERE
C.Chat = ? AND
C.Id > ? AND
C.Date > ?
ORDER BY date;", chat, id, date);
return (ChatMessage[]) ExecuteQuery(q);
}
