Re: [sqlite] Date Dimension

[P Kishor]

On Wed, Apr 21, 2010 at 3:36 PM, Adam DeVita  wrote:
> Good day,
>
> I've been looking at doing that, but am having problems converting
> backwards.
>
> The idea of a date dimension is to have one row for every possible date in
> the time span of interest
>
> For example, I'm tracking product histories, so I know that there will be no
> activity before January 1, 1990 (a date well before manufacture of the first
> product)
>
> and I won't care about what happens well into the future retire (say around
> year 2030   )
>
> This yeilds (20+20)*365.25 =14,610 maximum potential rows.  If all the
> possibilities are pre-calculated, then if a user wants to express a roll up
> of the facts by any grouping
>
> select count(somthing_interesting) , dd.day_of_week
> from fact_table ft
> inner join date_dimension dd on ft.dateid = dd.dateid
> group by dd,day_of_week
>
> Then
> a) the user doesn't have to worry about converting dates by some group
> because it is all done for them.
> b) we store the date of the event in our fact_table as an int
> c) we don't have to run much of calculation of dates, just a join.
>


Yea, in my view, if you are going to do a lot of math on parts of the
date, you are probably better of storing those parts separately
anyway.

Your db size is trivial though, so you should have no problem either way.


> (I've been reading "The Data Warehoust Toolkit, Second Edition  by Kimball
> and Ross)
>
>
> If I have to generate the date dimension on my own, I'm hoping to use
> something like
> create table date_dimension (
>  [Dateid] integer primary key,
>  [Real_Year] int ,
>  [Month_name] text,
>  [Day] int ,
>  [QuarterNumber] int,
>  [DayofWeek_name] text,
>  [dayofYear] int,
>  [epoch_day] int,
>  [julian_day] int
>  );
>
> /*populate some an auto increment so that all days are covered even if I
> have to write a loop doing*/
>  insert into date_dimension( epoch_day) select count(epoch_day) from
> date_dimension;
>
> /*then */
>  update date_dimension set julian_day = julianday('now') -
> julianday('1990-01-01') + epoch_day;
>
> /* then  uh some query that  updates the table containing the julian
> date of every day from Jan 1, 1990 through 2030, and  fill in the month
> name, year, quarter in nice user friendly strings.*/
>
> Is this approach better than generating a list of date strings for all
> possible dates, throwing away the Feb 29s from non leap years, and then
> parsing the string to get ye year, month, day, day of year, age from epoch,
> month name, etc?
>
>
> regards,
> Adam
>
>
>
>
> On Wed, Apr 21, 2010 at 3:59 PM, P Kishor  wrote:
>
>> On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita  wrote:
>> > Yes.
>> >
>> > A Date dimension  is a table that has all possible dates for your
>> > data, thus making reporting on properties of the date easy.  Something
>> > like this
>> >
>> > CREATE TABLE Date_dimension (
>> >  DateID int NOT NULL , /*an int key to match up to date fields in fact
>> > storage tables*/
>> >  [Date] datetime NOT NULL,
>> >  [Year] int NOT NULL,
>> >  [Month] int NOT NULL,
>> >  [Day] int NOT NULL,
>> >  [QuarterNumber] int NOT NULL,
>> >  [DayofWeek_name] text,
>> >  [Month_name] text,
>> >
>> > )
>> >
>>
>>
>> methinks you can calculate all of the above storing your dates as
>> strings in a single column, and using the date time functions on that
>> column. Check out the functions in the link I sent you.
>>
>> >
>> >
>> > On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov 
>> wrote:
>> >
>> >> > What is a "Date Dimension"?
>> >>
>> >> Probably OP meant this:
>> >> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)
>> 
>> >> .
>> >> But I don't have any answer to the question asked.
>> >>
>> >>
>> >> Pavel
>> >>
>> >> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
>> >> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita 
>> >> wrote:
>> >> >> Good day,
>> >> >>
>> >> >> Given the context I'm in, sqlite is going to be used for our data
>> >> >> warehousing.  (We generate about 2MB of raw data in a month, so we
>> don't
>> >> >> think we need a heavy DB engine.)
>> >> >>
>> >> >> Since most warehouses have one, which are very similar from
>> application
>> >> to
>> >> >> application, I'm wondering if  there is somewhere to download a
>> >> pre-defined
>> >> >> Date Dimension?
>> >> >
>> >> > What is a "Date Dimension"? For SQLite's date time functions, see
>> >> > http://www.sqlite.org/lang_datefunc.html
>> >> >
>> >> >>  I could write my own script, but re-invent and debug the
>> >> >> wheel?
>> >> >>
>> >> >> regards,
>> >> >> Adam
>> >> >>
>> >> >> --
>> >> >> VerifEye Technologies Inc.
>> >> >> 905-948-0015x245
>> >> >> 7100 Warden Ave, Unit 3
>> >> >> Markham ON, L3R 8B5
>> >> >> Canada
>> >> >> 

Re: [sqlite] Date Dimension

[Simon Slavin]

On 21 Apr 2010, at 9:36pm, Adam DeVita wrote:

> /*then */
> update date_dimension set julian_day = julianday('now') -
> julianday('1990-01-01') + epoch_day;
> 
> /* then  uh some query that  updates the table containing the julian
> date of every day from Jan 1, 1990 through 2030, and  fill in the month
> name, year, quarter in nice user friendly strings.*/
> 
> Is this approach better than generating a list of date strings for all
> possible dates, throwing away the Feb 29s from non leap years, and then
> parsing the string to get ye year, month, day, day of year, age from epoch,
> month name, etc?

You don't have to do all that.  You can use the existing SQLite functions to 
generate all possible dates for you.  Taking a look at the Examples section on 
this page

http://www.sqlite.org/lang_datefunc.html

something like the following

SELECT date(julianday('1990-01-01')','+0 days')

will give you one day after the start, and you should be able to generate 
further days by composing further strings like '+1 days'.  Another way to do it 
is to know that 1 day = 86400 seconds, and just continually add that number for 
each next day.

Of course, these functions return a number, and it's the numbers which will be 
stored in your database, presumably in a column with REAL affinity.  You might 
instead prefer to process the numbers you get, producing and storing a string 
like '19900101' which means you'll be able to read your dates with the naked 
eye and understand them without processing.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ExecuteNonQuery() consuming memory

[Radcon Entec]

Greetings!

I have a Windows service application written in C# using Visual Studio 2008 
that uses the ADO.Net 2.0 provider for SQLite downloaded from SourceForge.  The 
application writes one row consisting of three values to each of 124 SQLite 
databases once a minute.  The service uses the SQLiteDatabase.ExecuteNonQuery() 
method to write the data into the databases.  As services usually are, this one 
is designed to run forever.  The problem is that it slowly increases memory 
consumption.  When it starts, Task Manager reports that it uses 34 megabytes of 
memory.  That number goes up by about 3 megabytes per hour.  

When I comment out the call to ExecuteNonQuery(), the memory consumption of the 
service remains constant.

This behavior is not acceptable.  I can't have a service whose memory footprint 
grows without limit, no matter how slowly it grows.  I would have expected that 
the amount of memory consumed by my application before the call to 
ExecuteNonQuery() would be nearly the same is the amount consumed after the 
call finishes, with possibly some difference because I can't be sure when C#'s 
garbage collection will do its work.  What am I doing wrong?  Is this a problem 
in the provider?  Is this a problem with SQLite itself? Should I be using ADO 
.Net more correctly?

Here's the function:

public int Execute(string query)
{
int rowsAffected;

   if (m_command == null)
{
System.Data.SQLite.SQLiteException ex = new 
SQLiteException("Attempt to execute a query on a closed or broken database.");
throw ex;
}
m_command.CommandType = CommandType.Text;
m_command.CommandText = query;
rowsAffected = 0;
// rowsAffected = m_command.ExecuteNonQuery();
return rowsAffected;
}

Thanks for your help!

RobR


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[D. Richard Hipp]

On Apr 21, 2010, at 4:37 PM, Pavel Ivanov wrote:

> I don't know anything about internal support of pages bigger than 32k.
> But I want to warn you: each table and each index in SQLite occupy at
> least 1 database page. So let's say you have 4 tables with 1
> additional index each (besides 'integer primary key' one). With 256k
> pages this schema will result in a database of more than 2 Mb without
> any data stored. Is your embedded FS okay with this storage amount?

Furthermore, SQLite changes whole pages at a time.  So in a database  
with 256kB pages, if you change a single byte, you still have to write  
256kB both to the rollback journal and to the database file.

>
>
> Pavel
>
> On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent
>  wrote:
>> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k  
>> (below).  is this limitation still valid?
>>
>> we have an embedded FS that is _very_ slow and performs best w/ a  
>> write page size of 256k.  will bad things happen if i configure  
>> SQLite w/ 256k pages?
>>
>> thanks
>> tom
>>
>> Maximum Database Page Size
>>
>> An SQLite database file is organized as pages. The size of each  
>> page is a power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The  
>> default value for SQLITE_MAX_PAGE_SIZE is 32768. The current  
>> implementation will not support a larger value.
>>
>> It used to be the case that SQLite would allocate some stack  
>> structures whose size was proportional to the maximum page size.  
>> For this reason, SQLite would sometimes be compiled with a smaller  
>> maximum page size on embedded devices with limited stack memory.  
>> But more recent versions of SQLite put these large structures on  
>> the heap, not on the stack, so reducing the maximum page size is no  
>> longer necessary on embedded devices. There is no longer any real  
>> reason to lower the maximum page size.
>>
>> __
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email
>> __
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] page_size

[Pavel Ivanov]

I don't know anything about internal support of pages bigger than 32k.
But I want to warn you: each table and each index in SQLite occupy at
least 1 database page. So let's say you have 4 tables with 1
additional index each (besides 'integer primary key' one). With 256k
pages this schema will result in a database of more than 2 Mb without
any data stored. Is your embedded FS okay with this storage amount?


Pavel

On Tue, Apr 20, 2010 at 4:51 PM, Tom Broadbent
 wrote:
> i've read in the docs that SQLITE_MAX_PAGE_SIZE can't be > 32k (below).  is 
> this limitation still valid?
>
> we have an embedded FS that is _very_ slow and performs best w/ a write page 
> size of 256k.  will bad things happen if i configure SQLite w/ 256k pages?
>
> thanks
> tom
>
> Maximum Database Page Size
>
> An SQLite database file is organized as pages. The size of each page is a 
> power of 2 between 512 and SQLITE_MAX_PAGE_SIZE. The default value for 
> SQLITE_MAX_PAGE_SIZE is 32768. The current implementation will not support a 
> larger value.
>
> It used to be the case that SQLite would allocate some stack structures whose 
> size was proportional to the maximum page size. For this reason, SQLite would 
> sometimes be compiled with a smaller maximum page size on embedded devices 
> with limited stack memory. But more recent versions of SQLite put these large 
> structures on the heap, not on the stack, so reducing the maximum page size 
> is no longer necessary on embedded devices. There is no longer any real 
> reason to lower the maximum page size.
>
> __
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> __
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Dimension

[Adam DeVita]

Good day,

I've been looking at doing that, but am having problems converting
backwards.

The idea of a date dimension is to have one row for every possible date in
the time span of interest

For example, I'm tracking product histories, so I know that there will be no
activity before January 1, 1990 (a date well before manufacture of the first
product)

and I won't care about what happens well into the future retire (say around
year 2030   )

This yeilds (20+20)*365.25 =14,610 maximum potential rows.  If all the
possibilities are pre-calculated, then if a user wants to express a roll up
of the facts by any grouping

select count(somthing_interesting) , dd.day_of_week
from fact_table ft
inner join date_dimension dd on ft.dateid = dd.dateid
group by dd,day_of_week

Then
a) the user doesn't have to worry about converting dates by some group
because it is all done for them.
b) we store the date of the event in our fact_table as an int
c) we don't have to run much of calculation of dates, just a join.

(I've been reading "The Data Warehoust Toolkit, Second Edition  by Kimball
and Ross)


If I have to generate the date dimension on my own, I'm hoping to use
something like
create table date_dimension (
 [Dateid] integer primary key,
 [Real_Year] int ,
 [Month_name] text,
 [Day] int ,
 [QuarterNumber] int,
 [DayofWeek_name] text,
 [dayofYear] int,
 [epoch_day] int,
 [julian_day] int
 );

/*populate some an auto increment so that all days are covered even if I
have to write a loop doing*/
 insert into date_dimension( epoch_day) select count(epoch_day) from
date_dimension;

/*then */
 update date_dimension set julian_day = julianday('now') -
julianday('1990-01-01') + epoch_day;

/* then  uh some query that  updates the table containing the julian
date of every day from Jan 1, 1990 through 2030, and  fill in the month
name, year, quarter in nice user friendly strings.*/

Is this approach better than generating a list of date strings for all
possible dates, throwing away the Feb 29s from non leap years, and then
parsing the string to get ye year, month, day, day of year, age from epoch,
month name, etc?


regards,
Adam




On Wed, Apr 21, 2010 at 3:59 PM, P Kishor  wrote:

> On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita  wrote:
> > Yes.
> >
> > A Date dimension  is a table that has all possible dates for your
> > data, thus making reporting on properties of the date easy.  Something
> > like this
> >
> > CREATE TABLE Date_dimension (
> >  DateID int NOT NULL , /*an int key to match up to date fields in fact
> > storage tables*/
> >  [Date] datetime NOT NULL,
> >  [Year] int NOT NULL,
> >  [Month] int NOT NULL,
> >  [Day] int NOT NULL,
> >  [QuarterNumber] int NOT NULL,
> >  [DayofWeek_name] text,
> >  [Month_name] text,
> >
> > )
> >
>
>
> methinks you can calculate all of the above storing your dates as
> strings in a single column, and using the date time functions on that
> column. Check out the functions in the link I sent you.
>
> >
> >
> > On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov 
> wrote:
> >
> >> > What is a "Date Dimension"?
> >>
> >> Probably OP meant this:
> >> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)
> 
> >> .
> >> But I don't have any answer to the question asked.
> >>
> >>
> >> Pavel
> >>
> >> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
> >> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita 
> >> wrote:
> >> >> Good day,
> >> >>
> >> >> Given the context I'm in, sqlite is going to be used for our data
> >> >> warehousing.  (We generate about 2MB of raw data in a month, so we
> don't
> >> >> think we need a heavy DB engine.)
> >> >>
> >> >> Since most warehouses have one, which are very similar from
> application
> >> to
> >> >> application, I'm wondering if  there is somewhere to download a
> >> pre-defined
> >> >> Date Dimension?
> >> >
> >> > What is a "Date Dimension"? For SQLite's date time functions, see
> >> > http://www.sqlite.org/lang_datefunc.html
> >> >
> >> >>  I could write my own script, but re-invent and debug the
> >> >> wheel?
> >> >>
> >> >> regards,
> >> >> Adam
> >> >>
> >> >> --
> >> >> VerifEye Technologies Inc.
> >> >> 905-948-0015x245
> >> >> 7100 Warden Ave, Unit 3
> >> >> Markham ON, L3R 8B5
> >> >> Canada
> >> >> ___
> >> >> sqlite-users mailing list
> >> >> sqlite-users@sqlite.org
> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >>
> >> >
> >> >
> >> >
> >> > --
> >> > Puneet Kishor http://www.punkish.org
> >> > Carbon Model http://carbonmodel.org
> >> > Charter Member, Open Source Geospatial Foundation
> http://www.osgeo.org
> >> > Science Commons Fellow,
> http://sciencecommons.org/about/whoweare/kishor
> >> > Nelson Institute, UW-Madison 

Re: [sqlite] Date Dimension

[P Kishor]

On Wed, Apr 21, 2010 at 2:37 PM, Adam DeVita  wrote:
> Yes.
>
> A Date dimension  is a table that has all possible dates for your
> data, thus making reporting on properties of the date easy.  Something
> like this
>
> CREATE TABLE Date_dimension (
>  DateID int NOT NULL , /*an int key to match up to date fields in fact
> storage tables*/
>  [Date] datetime NOT NULL,
>  [Year] int NOT NULL,
>  [Month] int NOT NULL,
>  [Day] int NOT NULL,
>  [QuarterNumber] int NOT NULL,
>  [DayofWeek_name] text,
>  [Month_name] text,
>
> )
>


methinks you can calculate all of the above storing your dates as
strings in a single column, and using the date time functions on that
column. Check out the functions in the link I sent you.

>
>
> On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov  wrote:
>
>> > What is a "Date Dimension"?
>>
>> Probably OP meant this:
>> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)
>> .
>> But I don't have any answer to the question asked.
>>
>>
>> Pavel
>>
>> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
>> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita 
>> wrote:
>> >> Good day,
>> >>
>> >> Given the context I'm in, sqlite is going to be used for our data
>> >> warehousing.  (We generate about 2MB of raw data in a month, so we don't
>> >> think we need a heavy DB engine.)
>> >>
>> >> Since most warehouses have one, which are very similar from application
>> to
>> >> application, I'm wondering if  there is somewhere to download a
>> pre-defined
>> >> Date Dimension?
>> >
>> > What is a "Date Dimension"? For SQLite's date time functions, see
>> > http://www.sqlite.org/lang_datefunc.html
>> >
>> >>  I could write my own script, but re-invent and debug the
>> >> wheel?
>> >>
>> >> regards,
>> >> Adam
>> >>
>> >> --
>> >> VerifEye Technologies Inc.
>> >> 905-948-0015x245
>> >> 7100 Warden Ave, Unit 3
>> >> Markham ON, L3R 8B5
>> >> Canada
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@sqlite.org
>> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> >
>> >
>> >
>> > --
>> > Puneet Kishor http://www.punkish.org
>> > Carbon Model http://carbonmodel.org
>> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> > ---
>> > Assertions are politics; backing up assertions with evidence is science
>> > ===
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Direct access of table data

[Nathan Biggs]

Thanks for the input.  Like everything else in life there are 
trade-offs.  I guess that the real question originates because the time 
it takes to query the data seems a lot faster when I use a simplier 
select statement.  As I add more restrictions on the where-clause it 
tends to slow down.  I realize that this is due to my indexes, but can't 
add a lot of indexes because it slows down the insert speed which is 
more important than the query speed.  There is only one instance where 
this functionality would be handy anyway.  In 48 other instances the 
ability to query the database is imperative.

Having said that SQLite is light-years faster than other databases I 
have used for this project.  Good job to everyone who helps to support 
it.  Keep up the good work!



On 4/21/2010 3:28 PM, P Kishor wrote:
> On Wed, Apr 21, 2010 at 1:19 PM, Nathan Biggs  wrote:
>
>> Is there a way to read the values of a table directly without building
>> and executing a query.  I have a function that has predefined memory
>> (counters) and increments them if the data in the record matches a hard
>> coded value.
>>
>> Since this is hard-coded, I thought it might perform much without all of
>> the memory allocations/de-allocations associated with the query engine.
>>
>>  
> Others have already responded, but in questions like this, my standard
> response is... measure. If you think it might, it is time to measure,
> do a test. Only you can do a test that is meaningful to you. Of
> course, you can and should ask for ways to make your queries more
> efficient, but in the end, a test on your hardware, in your
> environment, is the only thing that matters.
>
> That said, why choose a sql database, and then want to bypass its sql
> engine to access the data directly? Even if you could, why would you?
> Why not just begin with a more appropriate data store in the first
> place? If you don't need sql, just go ahead and choose a key-value
> db... Berkeley DB is free for the taking... try Tokyo Cabinet. I am
> sure there are others.
>
>
>
>> Thanks for your help.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Dimension

[Adam DeVita]

Yes.

A Date dimension  is a table that has all possible dates for your
data, thus making reporting on properties of the date easy.  Something
like this

CREATE TABLE Date_dimension (
 DateID int NOT NULL , /*an int key to match up to date fields in fact
storage tables*/
 [Date] datetime NOT NULL,
 [Year] int NOT NULL,
 [Month] int NOT NULL,
 [Day] int NOT NULL,
 [QuarterNumber] int NOT NULL,
 [DayofWeek_name] text,
 [Month_name] text,

)



On Wed, Apr 21, 2010 at 3:25 PM, Pavel Ivanov  wrote:

> > What is a "Date Dimension"?
>
> Probably OP meant this:
> http://en.wikipedia.org/wiki/Dimension_(data_warehouse)
> .
> But I don't have any answer to the question asked.
>
>
> Pavel
>
> On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
> > On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita 
> wrote:
> >> Good day,
> >>
> >> Given the context I'm in, sqlite is going to be used for our data
> >> warehousing.  (We generate about 2MB of raw data in a month, so we don't
> >> think we need a heavy DB engine.)
> >>
> >> Since most warehouses have one, which are very similar from application
> to
> >> application, I'm wondering if  there is somewhere to download a
> pre-defined
> >> Date Dimension?
> >
> > What is a "Date Dimension"? For SQLite's date time functions, see
> > http://www.sqlite.org/lang_datefunc.html
> >
> >>  I could write my own script, but re-invent and debug the
> >> wheel?
> >>
> >> regards,
> >> Adam
> >>
> >> --
> >> VerifEye Technologies Inc.
> >> 905-948-0015x245
> >> 7100 Warden Ave, Unit 3
> >> Markham ON, L3R 8B5
> >> Canada
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> >
> >
> > --
> > Puneet Kishor http://www.punkish.org
> > Carbon Model http://carbonmodel.org
> > Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> > Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> > Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> > ---
> > Assertions are politics; backing up assertions with evidence is science
> > ===
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Direct access of table data

[P Kishor]

On Wed, Apr 21, 2010 at 1:19 PM, Nathan Biggs  wrote:
> Is there a way to read the values of a table directly without building
> and executing a query.  I have a function that has predefined memory
> (counters) and increments them if the data in the record matches a hard
> coded value.
>
> Since this is hard-coded, I thought it might perform much without all of
> the memory allocations/de-allocations associated with the query engine.
>

Others have already responded, but in questions like this, my standard
response is... measure. If you think it might, it is time to measure,
do a test. Only you can do a test that is meaningful to you. Of
course, you can and should ask for ways to make your queries more
efficient, but in the end, a test on your hardware, in your
environment, is the only thing that matters.

That said, why choose a sql database, and then want to bypass its sql
engine to access the data directly? Even if you could, why would you?
Why not just begin with a more appropriate data store in the first
place? If you don't need sql, just go ahead and choose a key-value
db... Berkeley DB is free for the taking... try Tokyo Cabinet. I am
sure there are others.


> Thanks for your help.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Dimension

[Pavel Ivanov]

> What is a "Date Dimension"?

Probably OP meant this: http://en.wikipedia.org/wiki/Dimension_(data_warehouse).
But I don't have any answer to the question asked.


Pavel

On Wed, Apr 21, 2010 at 3:21 PM, P Kishor  wrote:
> On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita  wrote:
>> Good day,
>>
>> Given the context I'm in, sqlite is going to be used for our data
>> warehousing.  (We generate about 2MB of raw data in a month, so we don't
>> think we need a heavy DB engine.)
>>
>> Since most warehouses have one, which are very similar from application to
>> application, I'm wondering if  there is somewhere to download a pre-defined
>> Date Dimension?
>
> What is a "Date Dimension"? For SQLite's date time functions, see
> http://www.sqlite.org/lang_datefunc.html
>
>>  I could write my own script, but re-invent and debug the
>> wheel?
>>
>> regards,
>> Adam
>>
>> --
>> VerifEye Technologies Inc.
>> 905-948-0015x245
>> 7100 Warden Ave, Unit 3
>> Markham ON, L3R 8B5
>> Canada
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date Dimension

[P Kishor]

On Wed, Apr 21, 2010 at 1:50 PM, Adam DeVita  wrote:
> Good day,
>
> Given the context I'm in, sqlite is going to be used for our data
> warehousing.  (We generate about 2MB of raw data in a month, so we don't
> think we need a heavy DB engine.)
>
> Since most warehouses have one, which are very similar from application to
> application, I'm wondering if  there is somewhere to download a pre-defined
> Date Dimension?

What is a "Date Dimension"? For SQLite's date time functions, see
http://www.sqlite.org/lang_datefunc.html

>  I could write my own script, but re-invent and debug the
> wheel?
>
> regards,
> Adam
>
> --
> VerifEye Technologies Inc.
> 905-948-0015x245
> 7100 Warden Ave, Unit 3
> Markham ON, L3R 8B5
> Canada
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: Direct access of table data

[Griggs, Donald]

 
Briggs,

Re: "I guess I'm just getting greedy since sqlite is so much faster than our 
standard databases ("progress")."

That's a distinct possibility.;-)


Re: "When you run a query for select * from table, does it literally copy the 
contents of the table, or does it just build a structure that points to the 
data in the table?"

When you wrote "points to the data in the table" -- by "table" do you mean 
the data on your disk drive?  If so, then yes, sqlite must read the data into 
RAM, and at least one ram-to-ram copy is implied.   (The data for a row might 
span multiple db pages.) If you're using an sqlite wrapper, that may possibly 
perform a copy as well.   
If you're using multi-megabyte BLOB's then these can be an exception, as 
you may read them incrementally:
http://www.sqlite.org/c3ref/blob_read.html

If, however, you're NOT using large blobs, then considerations such as your 
synchronization level,
http://www.sqlite.org/pragma.html#pragma_synchronous
 indexing, page size, etc. are usually much more important to performance than 
a ram-to-ram copy.

Again, have you measured the time to perform the query in question?  Is it 
worrisome?   If not, don't worry.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Date Dimension

[Adam DeVita]

Good day,

Given the context I'm in, sqlite is going to be used for our data
warehousing.  (We generate about 2MB of raw data in a month, so we don't
think we need a heavy DB engine.)

Since most warehouses have one, which are very similar from application to
application, I'm wondering if  there is somewhere to download a pre-defined
Date Dimension?  I could write my own script, but re-invent and debug the
wheel?

regards,
Adam

-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FW: Direct access of table data

[Griggs, Donald]

From: Nathan Biggs [mailto:nbi...@mycfs.com]
Sent: Wednesday, April 21, 2010 2:43 PM
To: Griggs, Donald
Subject: Re: [sqlite] Direct access of table data

Griggs,

Thanks for the quick reply.  I guess I'm just getting greedy since sqlite is so 
much faster than our standard databases ("progress").
When you run a query for select * from table, does it literally copy the 
contents of the table, or does it just build a structure that points to the 
data in the table?


On 4/21/2010 2:29 PM, Griggs, Donald wrote:

Hello Biggs, this is Griggs,

Re: "Is there a way to read the values of a table directly without building and 
executing a query.  I have a function that has predefined memory..."

No easy way.   The sophistication of database abstraction is the reason you 
presumably chose to use sqlite.


Re: "I thought it might perform much [better] without all .."

If you've measured this and the query truly takes too long, you might try 
(in order):
 1) trying to optimize the query (do you have ideal indexing, for instance?)
 2) restructure your data/program
 3) use a simple file rather than a database


Regards,
  Donald Griggs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Direct access of table data

[Pavel Ivanov]

> Since this is hard-coded, I thought it might perform much without all of
> the memory allocations/de-allocations associated with the query engine.

Don't use query engine then and store the information in separate file
with your own hard-coded format.


Pavel

On Wed, Apr 21, 2010 at 2:19 PM, Nathan Biggs  wrote:
> Is there a way to read the values of a table directly without building
> and executing a query.  I have a function that has predefined memory
> (counters) and increments them if the data in the record matches a hard
> coded value.
>
> Since this is hard-coded, I thought it might perform much without all of
> the memory allocations/de-allocations associated with the query engine.
>
> Thanks for your help.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Direct access of table data

[Griggs, Donald]

Hello Biggs, this is Griggs,

Re: "Is there a way to read the values of a table directly without building and 
executing a query.  I have a function that has predefined memory..."

No easy way.   The sophistication of database abstraction is the reason you 
presumably chose to use sqlite.


Re: "I thought it might perform much [better] without all ..."

If you've measured this and the query truly takes too long, you might try 
(in order):
 1) trying to optimize the query (do you have ideal indexing, for instance?)
 2) restructure your data/program
 3) use a simple file rather than a database


Regards,
  Donald Griggs
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [csv extension] Error while reading long lines

[gwenn]

Thanks for this great extension.
It works smoothly with 500Mo files.

And it's a workaround to some shortcomings of the '.import' command:
 - no need to create a table before,
 - no need to delete the header row before/after,
 - no error if the number of columns is not homogeneous,
 - ...
It's a nightmare to work with the CSV format but I have to.

I made a quick and dirty fix to the USE_HEADER_ROW mode to replace
whitespaces, slashes or hyphens by underscores.
But I look for a better solution. Is there any way to make sure a string is
a valid column name?

Regards

On Mon Apr 19 17:32:13 GMT 2010, Shane Harrelson 
wrote:
> Thanks for the report.  The extension is still very a much a
> work-in-progress and any feedback is greatly appreciated.
>
> -Shane
>
>
> On Sun, Apr 18, 2010 at 12:51 PM, gwenn  wrote:
> > Hello,
> > There is a little bug/typo in the csv extension when lines exceed 100
> > characters:
> > *** glibc detected *** sqlite3: realloc(): invalid pointer:
> > 0x00ad1a78 ***
> > === Backtrace: =
> > /lib/libc.so.6[0x7f6dab009d16]
> > /lib/libc.so.6(realloc+0x321)[0x7f6dab00fda1]
> > ./libSqliteCsv.so[0x7f6da9ef9dbf]
> >
> > A possible patch is:
> > --- /tmp/SQLite-d474195a997b9d94/ext/csv/csv.c 2009-11-05
05:14:30.0
> > +0100
> > +++ csv.c 2010-04-18 18:48:04.0 +0200
> > @@ -160,7 +160,7 @@
> > }
> >   }
> >   if( bShrink ){
> > -pCSV->zRow = realloc( pCSV->zRow, n+1 );
> > +pCSV->zRow = sqlite3_realloc( pCSV->zRow, n+1 );
> > pCSV->maxRow = n+1;
> >   }
> >   return bEol ? pCSV->zRow : 0;
> >
> > Regards.
> > ___
> > sqlite-users mailing list
> > sqlite-users at sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Direct access of table data

[Nathan Biggs]

Is there a way to read the values of a table directly without building 
and executing a query.  I have a function that has predefined memory 
(counters) and increments them if the data in the record matches a hard 
coded value.

Since this is hard-coded, I thought it might perform much without all of 
the memory allocations/de-allocations associated with the query engine.

Thanks for your help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] a problem wih SQLITE_LOCKED

[Radu Berinde]

Hi sqlite folk!

I had a (hard to reproduce) problem with SQLITE_LOCKED errors and I
was hoping someone could help me identify the issue (I'm using sqlite3
version 3.5.7 in a C++ program running in linux).

First, no other process is using the database, and my process has only
one database connection. All accesses are serialized through a big
lock in my program.

All my SQL statments are executed by a function that looks like this:

sqlite3_exec(_db, "BEGIN IMMEDIATE", NULL, NULL, );
followed by either
sqlite3_exec(_db, code, NULL, NULL, );
or (if code is or ends in a select):
sqlite3_get_table(_db, code, rawResult, , , )
and finally:
sqlite3_exec(_db, "COMMIT", NULL, NULL, );
(and if get_table was used, copy the values and free_table).

As far as I understand the documentation, I should NEVER get
SQLITE_LOCKED when using sqlite3 this way (serialized calls to the
above function), correct?

I got the error when running statements such as the following, each
separately (i.e. four calls to the function above):

call 1: INSERT OR ROLLBACK INTO TableX (a, b) VALUES (5, 'y');

call 2: UPDATE OR ROLLBACK TableY SET c = LAST_INSERT_ROWID() WHERE d == 'x';

call 3: SELECT * FROM TableY WHERE d == 'x';

call 4: UPDATE OR ROLLBACK TableX SET u = 'mm' , v = 'kk' WHERE key == 13;

The last command fails with SQLITE3_LOCKED (I'm not sure which of the
three sqlite3_ calls returns that though). Further accesses to the
database work correctly.

Sorry for the long email. Hopefully someone can shed some light as to
what might be happening (or what I might be doing wrong).

Thank you very much!

Best,
Radu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users