Re: [sqlite] Aggregating Forex data

[Rafael Garcia Leiva]

El 13/12/2011 17:44, Igor Tandetnik escribió:

On 12/13/2011 11:31 AM, Rafael Garcia Leiva wrote:

The problem is that is very slow. It takes nearly 24 hours to
query 1 year of Forex data in my laptop (and I have to work with 10
years periods). I will spend a couple of days learning about sqlite
optimization.


I suspect these two days would be better spent writing a script that 
just scans through the records in chronological order, with 
hand-written code to summarize them the way you need. It would be an 
order of magnitude faster than anything you could come up with in pure 
SQL.


Yes, I agree that a hand-written script will be much faster, but I would 
like to have the final aggregated data loaded into sqlite, since I have 
to work with that data (perform all kind of queries). The problem is 
that for a complete analysis I need all the aggregated time frames from 
2 minutes to 1440 minutes (1 day), since I have to search for the 
optimum time frame. For a 10 years EURUSD pair that would mean around 27 
millions of records. ¿Can sqlite manage such a big table? On the other 
side, I don't mind to wait a couple of seconds (and the question that 
I'm trying to anwer is if that it is possible) for sqlite to aggregate 
data to the current time frame being analysed since analysis time would 
be much longer than query time. But if aggregating data in 5 minutes 
intervals takes 1 day and I cannot reduce that time to order of seconds, 
I will try to find another solution, for example to have 1440 database 
files, one for each time frame, but that seems to me a very ugly solution.


Best regards

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

[sqlite] lost primary key

[YJM YAN]

source table:
DDL:
 "create table xxx_table(id interger primary key autoincrement, field1,
field2)"

int ret = sqlite3_exec(m_db,"attach './backup.db' as filedb",0,0,NULL);
ret = sqlite3_exec(m_db,"begin transaction",0,0,NULL);
ret = sqlite3_exec(m_db,"create table filedb.xxx_table as select * from
xxx_table",0,0,NULL);
ret = sqlite3_exec(m_db,"commit transaction",0,0,NULL);
ret = sqlite3_exec(m_db,"detach filedb",0,0,NULL);

destination table:
DDL:
 "create table xxx_table(id interger , field1, field2)"

use this way to back up a table, lost the primary key property.

How to configure its?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating key on related table

[BareFeetWare]

On 15/12/2011, at 2:58 PM, Jeff Matthews wrote:

> Thanks.   You are putting me on track.
> 
> Can you describe the properties, collate, nocase, restrict, etc.,

The "primary key" constraint automatically assigns a new ID to each Customer 
and Invoice when you insert a new row that doesn't specify a value for it. The 
"on delete restrict" constraint on the Invoice prevents deleting any Customer 
which has invoices. The "collate nocase" ensures that nocase is used to compare 
Customer Name, so 'tom' is treated as equal to 'Tom'.

> or better yet, point me to a reference that contains a full set of features, 
> including these?

It's just standard SQL. You can find the syntax described on the SQLite website 
here:
http://www.sqlite.org/lang_createtable.html

Here's a comparison of some GUI software that does SQLite design and data entry:
http://www.barefeetware.com/sqlite/compare/?ml

Please send replied to this mail list, not me directly.

Thanks,
Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare



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

Re: [sqlite] Updating key on related table

[BareFeetWare]

On 15/12/2011, at 2:11 PM, Jeff Matthews wrote:

> Customer Table
> 
>ID
>Name
> 
> Invoice Table
> 
>CustomerID
>InvoiceNumber
> 
> When I create a new invoice record for the selected customer, does 
> Invoice.CustomerID update with Customer.ID automatically, or do I need to do 
> this manually?  If it's automatic, can someone explain how it does it 
> automatically?


What do you mean by "the selected customer"?

It depends how you've set it up in your schema. For instance, if your schema is:

create table "Customer"
(   ID integer primary key not null
,   Name text collate nocase not null
)
;
create table "Invoice"
(   ID integer primary key not null
,   CustomerID integer references "Customer" (ID) on delete restrict on 
update cascade
)
;

then:

insert into "Invoice" (CustomerID) select max(ID) from "Customer";

will create a new Invoice assigned to the most recently added Customer 
(assuming no deletions).

or:

insert into "Invoice" (CustomerID) select ID from "Customer" where "Name" = 
'Tom';

will create a new invoice assigned to the customer whose name is 'Tom'.

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

[sqlite] Updating key on related table

[Jeff Matthews]

Customer Table

ID

Name

 

Invoice Table

 

CustomerID

InvoiceNumber

 

When I create a new invoice record for the selected customer, does
Invoice.CustomerID update with Customer.ID automatically, or do I need to do
this manually?  If it's automatic, can someone explain how it does it
automatically?

 

 

 

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

Re: [sqlite] Returning other columns of an aggregate result

[Igor Tandetnik]

On 12/14/2011 9:38 PM, Dilip Ranganathan wrote:

Thanks. Along the same lines, I have another question. I have a table like
this:

timestamp | category | col1 | col2

xxCAT1  35
yyCAT1  56
zzCAT3  29
rr  CAT3  43

I wanted to find the latest entry for every category based on their
timestamp. The records in the table are always inserted in such a way that
the last record always has the latest timestamp.

In my first pass, I mistakenly wrote the query like this: (assume timestamp
is stored as julian days)

select timestamp, category, col, col2 from table group by category order by
timestamp desc

This query doesn't make sense since after the grouping I am not specifying
anywhere which record is be chosen out of the grouped lot. Yet the result
was like this:

yyCAT1  56
rr  CAT3  43

Is it that from the grouped records, sqlite automatically chooses the
latest inserted record?


It might happen to fall out of the implementation. Personally, I 
wouldn't rely on it.

--
Igor Tandetnik

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

Re: [sqlite] Returning other columns of an aggregate result

[Dilip Ranganathan]

On Wed, Dec 14, 2011 at 8:35 PM, Igor Tandetnik  wrote:

> On 12/14/2011 8:21 PM, Dilip Ranganathan wrote:
>
>> I am not an expert in SQL, so do bear with me if I am asking the obvious.
>>
>> Given:
>>
>> timestamp |  col1  |   col2
>> 
>> xxabc  5
>> yyabc  4
>> zzdef  7
>> rrdef  6
>>
>>
>> SELECT timestamp, col1, min(col2)
>> FROM table
>> GROUP BY col1
>> ORDER BY min(col2) ASC
>>
>> returns:
>>
>> xx   abc 4
>> zz   def 6
>>
>> It looks like the timestamp column is kind of random since it is not
>> part of the group by clause. Suppose I want the results to be:
>>
>> yy  abc 4
>>
>> rr  def 6
>>
>> what kind of SQL would I have to write?
>>
>
> select b.timestamp, a.col1, b.col2
> from (select distinct col1 from myTable) a join myTable b on
>b.rowid = (select rowid from myTable where col1 = a.col1 order by col2
> limit 1)
> order by b.col2;
>
> Igor
Thanks. Along the same lines, I have another question. I have a table like
this:

timestamp | category | col1 | col2

xxCAT1  35
yyCAT1  56
zzCAT3  29
rr  CAT3  43

I wanted to find the latest entry for every category based on their
timestamp. The records in the table are always inserted in such a way that
the last record always has the latest timestamp.

In my first pass, I mistakenly wrote the query like this: (assume timestamp
is stored as julian days)

select timestamp, category, col, col2 from table group by category order by
timestamp desc

This query doesn't make sense since after the grouping I am not specifying
anywhere which record is be chosen out of the grouped lot. Yet the result
was like this:

yyCAT1  56
rr  CAT3  43

Is it that from the grouped records, sqlite automatically chooses the
latest inserted record?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: Which version of SQLite?

[Nataraj S Narayan]

Hi

I am also an ex-clipper. I miss the old 'code blocks' days. I think
Ruby comes closest to Clipper with its own code blocks.

regards

Nataraj

On Thu, Dec 15, 2011 at 7:17 AM, Jeff Matthews  wrote:
>
>
> I want to use SQLite in a C# app I am developing.
>
>
>
> My database knowledge drops off around 1996, when I gave up Clipper
> programming.   I have since learned a little about some of the new methods
> used by database gurus.  But I remember Clipper like it was yesterday since
> I did so much of it.
>
>
>
> Here is a short snippet of the logic I recall using back when:
>
>
>
> Select (0)  //  Provides allocation for a new file handle for opening a
> database
>
> Use Customers   //  Will open Customers dbf, where in those days, each table
> was its own file, and thus, we had multiple dbf files in an app to use
> relational data.
>
> Set Index to Phone, LastName  //  The indexes were also stored in separate
> files.
>
> Set Order to 2  //  This would mean that our seeks would use the LastName
> index's sort order
>
>
>
> seek "MATTHEWS"  //   Try to find the first instance of search string in the
> index and move record pointer to the row, or if not found, eof()
>
>
>
> numrecs=0
>
> if found();
>
>                do while trim(upper(LastName))="MATTHEWS" .and. !eof()   //
> cycle through the records and stop if eof() is hit
>
>                                delete   // delete the entire row
>
>                                numrecs=numrecs+1
>
>                                skip  // go to next record
>
>                enddo
>
> endif
>
> showMessage(ltrim(str(numrecs,0))+" records deleted.")
>
>
>
> That's it.
>
>
>
> I am not wanting to have to deal with excitingly new ways to do all this,
> such as DataSets or Entities, unless someone says, "But, you must!" or
> "You're crazy not to."
>
>
>
> So, which is the best SQLite download for me to use in C# using the closest
> syntax and logic flow as set forth above?  I am ready to download and start
> hacking.
>
>
>
> Thanks.
>
> ___
> 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] Returning other columns of an aggregate result

[Igor Tandetnik]

On 12/14/2011 8:45 PM, Jeff Matthews wrote:

Did you get my response?  I thought of something that you should try, which
is adding a where clause.

SELECT timestamp, col1, min(col2)
FROM table
WHERE col2=min(col2)<-   Here
GROUP BY col1
ORDER BY min(col2) ASC


This would produce an error during prepare, "misuse of aggregate". You 
can't use aggregates in a WHERE clause. I'll leave it as an exercise for 
the reader to figure out why.

--
Igor Tandetnik

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

Re: [sqlite] Which version of SQLite?

[Simon Slavin]

On 15 Dec 2011, at 1:47am, Jeff Matthews wrote:

> Select (0)  //  Provides allocation for a new file handle for opening a
> database
> 
> Use Customers   //  Will open Customers dbf, where in those days, each table
> was its own file, and thus, we had multiple dbf files in an app to use
> relational data.
> 
> Set Index to Phone, LastName  //  The indexes were also stored in separate
> files.
> 
> Set Order to 2  //  This would mean that our seeks would use the LastName
> index's sort order
> 
> 
> 
> seek "MATTHEWS"  //   Try to find the first instance of search string in the
> index and move record pointer to the row, or if not found, eof()

That is not how SQL works.  After you've opened your database file you probably 
want something like

DELETE FROM Customers WHERE LastName = 'MATTHEWS'

That's all it is: just that one line.

> So, which is the best SQLite download for me to use in C# using the closest
> syntax and logic flow as set forth above?  I am ready to download and start
> hacking.

For checking your understanding of SQL and making up your database file you 
want the shell tool for your OS.  For building SQL commands into your 
application I'll leave it up to someone who uses C# which I don't.  You can 
find the shell tool as 'Precompiled Binaries' here:



and documentation for it here:



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

[sqlite] FW: Which version of SQLite?

[Jeff Matthews]

 

I want to use SQLite in a C# app I am developing.

 

My database knowledge drops off around 1996, when I gave up Clipper
programming.   I have since learned a little about some of the new methods
used by database gurus.  But I remember Clipper like it was yesterday since
I did so much of it.

 

Here is a short snippet of the logic I recall using back when:

 

Select (0)  //  Provides allocation for a new file handle for opening a
database

Use Customers   //  Will open Customers dbf, where in those days, each table
was its own file, and thus, we had multiple dbf files in an app to use
relational data.

Set Index to Phone, LastName  //  The indexes were also stored in separate
files.

Set Order to 2  //  This would mean that our seeks would use the LastName
index's sort order

 

seek "MATTHEWS"  //   Try to find the first instance of search string in the
index and move record pointer to the row, or if not found, eof()

 

numrecs=0

if found();

do while trim(upper(LastName))="MATTHEWS" .and. !eof()   //
cycle through the records and stop if eof() is hit

delete   // delete the entire row

numrecs=numrecs+1

skip  // go to next record

enddo

endif

showMessage(ltrim(str(numrecs,0))+" records deleted.")

 

That's it.

 

I am not wanting to have to deal with excitingly new ways to do all this,
such as DataSets or Entities, unless someone says, "But, you must!" or
"You're crazy not to."

 

So, which is the best SQLite download for me to use in C# using the closest
syntax and logic flow as set forth above?  I am ready to download and start
hacking.

 

Thanks.

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

Re: [sqlite] Returning other columns of an aggregate result

[Jeff Matthews]

Igor,

Did you get my response?  I thought of something that you should try, which
is adding a where clause.

SELECT timestamp, col1, min(col2)
FROM table
WHERE col2=min(col2)   <-   Here
GROUP BY col1
ORDER BY min(col2) ASC

This is why I am thinking you should use the where clause:

The min() is just giving you the smallest value.   It is not moving your
record pointer to the corresponding timestamp.  Grouping is just pulling the
first instance where it finds a unique value for col1.

The "where," I am thinking, will filter your results to make sure that the
returned set will include whichever record where col2 = the min(col2).





-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Wednesday, December 14, 2011 7:36 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Returning other columns of an aggregate result

On 12/14/2011 8:21 PM, Dilip Ranganathan wrote:
> I am not an expert in SQL, so do bear with me if I am asking the obvious.
>
> Given:
>
> timestamp |  col1  |   col2
> 
> xxabc  5
> yyabc  4
> zzdef  7
> rrdef  6
>
>
> SELECT timestamp, col1, min(col2)
> FROM table
> GROUP BY col1
> ORDER BY min(col2) ASC
>
> returns:
>
> xx   abc 4
> zz   def 6
>
> It looks like the timestamp column is kind of random since it is not
> part of the group by clause. Suppose I want the results to be:
>
> yy  abc 4
>
> rr  def 6
>
> what kind of SQL would I have to write?

select b.timestamp, a.col1, b.col2
from (select distinct col1 from myTable) a join myTable b on
 b.rowid = (select rowid from myTable where col1 = a.col1 order by 
col2 limit 1)
order by b.col2;

-- 
Igor Tandetnik

___
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] Returning other columns of an aggregate result

[Igor Tandetnik]

On 12/14/2011 8:21 PM, Dilip Ranganathan wrote:

I am not an expert in SQL, so do bear with me if I am asking the obvious.

Given:

timestamp |  col1  |   col2

xxabc  5
yyabc  4
zzdef  7
rrdef  6


SELECT timestamp, col1, min(col2)
FROM table
GROUP BY col1
ORDER BY min(col2) ASC

returns:

xx   abc 4
zz   def 6

It looks like the timestamp column is kind of random since it is not
part of the group by clause. Suppose I want the results to be:

yy  abc 4

rr  def 6

what kind of SQL would I have to write?


select b.timestamp, a.col1, b.col2
from (select distinct col1 from myTable) a join myTable b on
b.rowid = (select rowid from myTable where col1 = a.col1 order by 
col2 limit 1)

order by b.col2;

--
Igor Tandetnik

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

Re: [sqlite] Returning other columns of an aggregate result

[Jeff Matthews]

SELECT timestamp, col1, min(col2)
FROM table
GROUP BY col1
ORDER BY min(col2) ASC

The min() is just giving you the smallest value.   It is not moving your
record pointer to the corresponding timestamp.

You will have to do that by yourself or specify better what the purpose of
your logic is.

That's why you get grouping like you did.   It is giving you the first
record where col1 meets the criteria and reporting the min value of col2
across the set.  You will note that the timestamp you are getting back is
simply the first instance of each of the groups (col1).








-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dilip Ranganathan
Sent: Wednesday, December 14, 2011 7:21 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Returning other columns of an aggregate result

I am not an expert in SQL, so do bear with me if I am asking the obvious.

Given:

timestamp |  col1  |   col2

xxabc  5
yyabc  4
zzdef  7
rrdef  6


SELECT timestamp, col1, min(col2)
FROM table
GROUP BY col1
ORDER BY min(col2) ASC

returns:

xx   abc 4
zz   def 6

It looks like the timestamp column is kind of random since it is not
part of the group by clause. Suppose I want the results to be:

yy  abc 4

rr  def 6

what kind of SQL would I have to write?
___
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

[sqlite] Returning other columns of an aggregate result

[Dilip Ranganathan]

I am not an expert in SQL, so do bear with me if I am asking the obvious.

Given:

timestamp |  col1  |   col2

xxabc  5
yyabc  4
zzdef  7
rrdef  6


SELECT timestamp, col1, min(col2)
FROM table
GROUP BY col1
ORDER BY min(col2) ASC

returns:

xx   abc 4
zz   def 6

It looks like the timestamp column is kind of random since it is not
part of the group by clause. Suppose I want the results to be:

yy  abc 4

rr  def 6

what kind of SQL would I have to write?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with Vis. Studio C# Entity Framework 4.1

[Don V Nielsen]

While I love working with C# and Sqlite, I'm quite an amateur at it even
though I'm doing some sophisticated programming for my employer.  And even
less at exploiting the capabilities of Visual Studio in helping me.  Below
is a very typical routine for me.  I use string.Format a lot to assemble
the sql commands necessary to get the job done, as in the following
example.  DbWork is a property that contains the sqlite connection passed
to the class.  Its joining multiple tables and views (which are unions of
tables) in order to calculate a wgt'd average and apply the results back to
the input table.

private static void calculate_rtwgt(Segment seg, string lvl, Int32
depth)
{
DbTransaction trans = DbWork.BeginTransaction();

// TODO - I need to be sensitive to pool rank in the sequencing
// for each depth, calculate rte weights
for (int i = 1; i <= depth; i++)
{
using (SQLiteCommand cmd = DbWork.CreateCommand())
{
cmd.Parameters.Add(SegmentController.SqlprmDepth);
cmd.CommandText = String.Format(
@"update {0} set {11} =
(
  select avg(rowid) from
  (
select pr.rowid
from {1} as m1
inner join {2} as p on p.{5} = m1.{5} and p.{6} = m1.{6}
inner join {3} as pr on pr.{7} = p.{7} and pr.{8} = p.{9}
where m1.{4} = {0}.{4}
order by pr.rowid
limit {12}
  )
)
where {0}.{4} in
(
  select m2.{4} from {1} as m2 where m2.{10} = {12}
);
",
seg.TblNameMatches,
seg.ViewNameMatches,
seg.ViewNamePools,
AddPoolController.POOL_PRIORITIES_TABLE_NAME,
SegmentController.SEG_COL_NEEDID,
TblZipRoute.ZR_COL_ZIP,
TblZipRoute.ZR_COL_CRRT,
TblZipRoute.ZR_COL_PRTY,
AddPoolController.POOL_COL_POOLID,
TblZipRoute.ZR_COL_ID,
lvl,
SegmentController.SEG_COL_ROUTE_WEIGHT,
SegmentController.SqlprmDepth.ParameterName
);

SegmentController.SqlprmDepth.Value = i;
cmd.ExecuteNonQuery();

}
}

trans.Commit();
trans.Dispose();
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pointer alignment bug on sparc64

[Richard Hipp]

On Wed, Dec 14, 2011 at 2:59 AM, Tod McQuillin  wrote:

> Hi all,
>
> I've found what appears to be a bug in SQLite 3.7.9 (as found in the
> DBD::SQLite perl module version 1.35).
>

Already fixed.  http://www.sqlite.org/src/info/54cc119811


>
> Running the tests included with DBD::SQLite, I saw test failures and perl
> crashing with a bus error while creating an index.
>
> Running the failing test under dbx, I observed the following:
>
> (dbx) run  t/27_metadata.t
> Running: perl t/27_metadata.t
> (process id 12132)
> Reading libc_psr.so.1
> 1..21
> Reading DBI.so
> Reading Util.so
> Reading SQLite.so
> ok 1 - The object isa DBI::db
> ok 2 - Create table meta1
> ok 3 - Create table meta2
> ok 4 - Create table meta3
> ok 5 - Get primary_key_info for meta1
> ok 6 - Correct primary_key_info returned for meta1
> ok 7 - Get primary_key_info for meta2
> ok 8 - Correct primary_key_info returned for meta2
> ok 9 - Get primary_key_info for meta3
> ok 10 - Correct primary_key_info returned for meta3
> t@1 (l@1) signal BUS (invalid address alignment) in sqlite3CreateIndex at
> line 82187 in file "sqlite3.c"
> 82187   pIndex->azColl[i] = zColl;
>
> Examining the data in more detail showed:
>
> (dbx) print *pIndex
> *pIndex = {
>zName  = 0x10074d966 "sqlite_autoindex_meta4_1"
>nColumn= 2
>aiColumn   = 0x10074d95c
>aiRowEst   = 0x10074d940
>pTable = 0x100779558
>tnum   = 0
>onError= 'c'
>autoIndex  = '\001'
>bUnordered = '\0'
>zColAff= (nil)
>pNext  = (nil)
>pSchema= 0x1007390d8
>aSortOrder = 0x10074d964 ""
>azColl = 0x10074d94c
> }
>
> Note that pIndex->azColl is not aligned to an 8-byte boundary as required
> by the sparc64 architecture.
>
> The following (ugly) patch resolves the problem for me, allowing
> DBD::SQLite to pass all tests.  Hopefuly an sqlite developer can make this
> into something a bit prettier:
>
> --- DBD-SQLite-1.35/sqlite3.c~  2011-11-28 18:05:51.0 -0600
> +++ DBD-SQLite-1.35/sqlite3.c   2011-12-14 01:29:41.523322000 -0600
> @@ -82103,7 +82103,7 @@
>   nCol = pList->nExpr;
>   pIndex = sqlite3DbMallocZero(db,
>   sizeof(Index) +  /* Index structure  */
> -  sizeof(tRowcnt)*(nCol+1) +   /* Index.aiRowEst   */
> +  sizeof(tRowcnt)*(nCol+1+((**nCol+1)%2)) +   /* Index.aiRowEst   */
>   sizeof(int)*nCol +   /* Index.aiColumn   */
>   sizeof(char *)*nCol +/* Index.azColl */
>   sizeof(u8)*nCol +/* Index.aSortOrder */
> @@ -82114,7 +82114,7 @@
> goto exit_create_index;
>   }
>   pIndex->aiRowEst = (tRowcnt*)([1]);
> -  pIndex->azColl = (char**)(>aiRowEst[**nCol+1]);
> +  pIndex->azColl = (char**)(>aiRowEst[**nCol+1+((nCol+1)%2)]);
>   pIndex->aiColumn = (int *)(>azColl[nCol]);
>   pIndex->aSortOrder = (u8 *)(>aiColumn[nCol]);
>   pIndex->zName = (char *)(>aSortOrder[nCol]);
>
> Thanks and regards,
> --
> Tod McQuillin
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



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

Re: [sqlite] Is CEROD support offered for Android?

[Richard Hipp]

On Tue, Dec 13, 2011 at 1:58 PM, steven harris  wrote:

> Just curious...
>

CEROD works fine on Android.  Note, however, that to use any SQLite other
than the one that comes built into Android, you have to make arrangements
to do your own Java bindings.  The standard Java bindings only work for the
(non-CEROD) SQLite that is built into Android.


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



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

[sqlite] Pointer alignment bug on sparc64

[Tod McQuillin]

Hi all,

I've found what appears to be a bug in SQLite 3.7.9 (as found in 
the DBD::SQLite perl module version 1.35).


Running the tests included with DBD::SQLite, I saw test failures and perl 
crashing with a bus error while creating an index.


Running the failing test under dbx, I observed the following:

(dbx) run  t/27_metadata.t
Running: perl t/27_metadata.t
(process id 12132)
Reading libc_psr.so.1
1..21
Reading DBI.so
Reading Util.so
Reading SQLite.so
ok 1 - The object isa DBI::db
ok 2 - Create table meta1
ok 3 - Create table meta2
ok 4 - Create table meta3
ok 5 - Get primary_key_info for meta1
ok 6 - Correct primary_key_info returned for meta1
ok 7 - Get primary_key_info for meta2
ok 8 - Correct primary_key_info returned for meta2
ok 9 - Get primary_key_info for meta3
ok 10 - Correct primary_key_info returned for meta3
t@1 (l@1) signal BUS (invalid address alignment) in sqlite3CreateIndex at 
line 82187 in file "sqlite3.c"

82187   pIndex->azColl[i] = zColl;

Examining the data in more detail showed:

(dbx) print *pIndex
*pIndex = {
zName  = 0x10074d966 "sqlite_autoindex_meta4_1"
nColumn= 2
aiColumn   = 0x10074d95c
aiRowEst   = 0x10074d940
pTable = 0x100779558
tnum   = 0
onError= 'c'
autoIndex  = '\001'
bUnordered = '\0'
zColAff= (nil)
pNext  = (nil)
pSchema= 0x1007390d8
aSortOrder = 0x10074d964 ""
azColl = 0x10074d94c
}

Note that pIndex->azColl is not aligned to an 8-byte boundary as required 
by the sparc64 architecture.


The following (ugly) patch resolves the problem for me, allowing 
DBD::SQLite to pass all tests.  Hopefuly an sqlite developer can make this 
into something a bit prettier:


--- DBD-SQLite-1.35/sqlite3.c~  2011-11-28 18:05:51.0 -0600
+++ DBD-SQLite-1.35/sqlite3.c   2011-12-14 01:29:41.523322000 -0600
@@ -82103,7 +82103,7 @@
   nCol = pList->nExpr;
   pIndex = sqlite3DbMallocZero(db,
   sizeof(Index) +  /* Index structure  */
-  sizeof(tRowcnt)*(nCol+1) +   /* Index.aiRowEst   */
+  sizeof(tRowcnt)*(nCol+1+((nCol+1)%2)) +   /* Index.aiRowEst   */
   sizeof(int)*nCol +   /* Index.aiColumn   */
   sizeof(char *)*nCol +/* Index.azColl */
   sizeof(u8)*nCol +/* Index.aSortOrder */
@@ -82114,7 +82114,7 @@
 goto exit_create_index;
   }
   pIndex->aiRowEst = (tRowcnt*)([1]);
-  pIndex->azColl = (char**)(>aiRowEst[nCol+1]);
+  pIndex->azColl = (char**)(>aiRowEst[nCol+1+((nCol+1)%2)]);
   pIndex->aiColumn = (int *)(>azColl[nCol]);
   pIndex->aSortOrder = (u8 *)(>aiColumn[nCol]);
   pIndex->zName = (char *)(>aSortOrder[nCol]);

Thanks and regards,
--
Tod McQuillin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is CEROD support offered for Android?

[steven harris]

Just curious...

Thanks!

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

Re: [sqlite] Database usage across processes

[Richard Hipp]

On Wed, Dec 14, 2011 at 6:02 AM, Sreekumar TP wrote:

> Hi,
>
> If I share a database across two process , both have sqlite threading mode
> to serialized, do I still need to serialize the access to the database ?
>

No.  SQLite does that automatically using file locking.


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



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

[sqlite] Database usage across processes

[Sreekumar TP]

Hi,

If I share a database across two process , both have sqlite threading mode
to serialized, do I still need to serialize the access to the database ?

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