0

Good afternoon ,

  • I'm thinking about how it would be best to keep posts sqlite table 1 or more than 1,

  • To take an example, a chat , which is better to keep it all in 1 - same table (if you can ) or better in different ?

(Example 1 table )

http://cmanios.wordpress.com/2013/10/29/read-sms-directly-from-sqlite-database-in-android/

  • I think if we have all the information in a table will be easier information overflow ( stack overflow )

Limitations on android sqlite tables: (related topics)

Maximum SQLite Database Size in Android Application

SQLite database limits in Android

  • I mean I can include more message all in one table . From my point of view , as a programmer , I want to create more than one table.

  • Example 1 and x tables :

    • ( 1 table ) (Where user = "select " ) Only need see specific user, for example "messages of user 1 and 2)

Table : (User 1 + user 2 + user3 ... userx)

+-----------------------------------------------------------------------------------------------------------------+
| date                | date_sent             | person    |  body                         |
|------------------------------------------------------------------------------------------------------------------
| 2013-10-20 13:48:18 | 2013-10-20 13:48:16   |   User1   | Hello Christos! How are you?              |
| 2013-10-20 16:34:03 | 1970-01-01 02:00:00   |   User2   | Fine, thanks ! I configure the left MFD of a F-16 jet |
| 2013-10-20 16:40:02 | 2013-10-20 16:40:01   |   User3   | Awesome! I am throwing a party tomorrow at 21:45!     |
| 2013-10-20 17:15:15 | 1970-01-01 02:00:00   |   Userx   | Thanks! I will be there!                  |
+-----------------------------------------------------------------------------------------------------------------+

- (2 or more tables) easy select all table

+-----------------------------------------------------------------------------------------------------------------+
| date                | date_sent             | person    |  body                         |
|------------------------------------------------------------------------------------------------------------------
| 2013-10-20 13:48:18 | 2013-10-20 13:48:16   |   User1   | Hello Christos! How are you?              |
| 2013-10-20 16:34:03 | 1970-01-01 02:00:00   |   User2   | Fine, thanks ! I configure the left MFD of a F-16 jet |
| 2013-10-20 16:40:02 | 2013-10-20 16:40:01   |   User1   | Awesome! I am throwing a party tomorrow at 21:45!     |
| 2013-10-20 17:15:15 | 1970-01-01 02:00:00   |   User2   | Thanks! I will be there!                  |
+-----------------------------------------------------------------------------------------------------------------+

+-----------------------------------------------------------------------------------------------------------------+
| date                | date_sent             | person    |  body                         |
|------------------------------------------------------------------------------------------------------------------
| 2013-10-20 13:48:18 | 2013-10-20 13:48:16   |   User1   | Hello Christos! How are you?              |
| 2013-10-20 16:34:03 | 1970-01-01 02:00:00   |   User3   | Fine, thanks ! I configure the left MFD of a F-16 jet |
| 2013-10-20 16:40:02 | 2013-10-20 16:40:01   |   User1   | Awesome! I am throwing a party tomorrow at 21:45!     |
| 2013-10-20 17:15:15 | 1970-01-01 02:00:00   |   User3   | Thanks! I will be there!                  |
+-----------------------------------------------------------------------------------------------------------------+

. . . .

  • I believe appropriate, create more than one table , we will not have storage problems , all in a single table.

  • Someone could tell me which is the best way? or if I'm wrong concepts

Community
  • 1
  • 1

1 Answers1

0

The links you provide for size limits in sqlite both indicate that this shouldn't really be an issue.

If you really think you're going to reach:

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

The theoretical maximum number of rows in a table is 264 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 140 terabytes will be reached first. A 140 terabytes database can hold no more than approximately 1e+13 rows, and then only if there are no indices and if each row contains very little data.

On an android device...well I think maybe you need to reconsider the topic a bit.

The next question:

Should you be creating/dropping tables on the fly? This is a pretty bad idea - it's prone to causing weird problems. I'm not saying you CAN'T do it, but it's not a normal way to do this kind of thing.

Personally - one database. If you really feel like you need to break it up, a rough way to do it might be something like:

MyDatabase:

  tables:               fields

      user         UserUuid,name,

      message      UserUuid,MessageId,MessageText,to, from, timestamp

      attachments  MessageId,AttacmentId,AttachmentContent

This would allow you to create new users and link them to messages and attachments while keeping things organized better than all being in one table, and would avoid you creating/destroying tables on the fly. You can probably find an organizational chart which better suits your specific needs, but this should get you rolling.

Nathaniel D. Waggoner
  • 2,856
  • 2
  • 19
  • 41
  • Nathaniel you are great, now I have a problem in query sql : String selectQuery="" +"SELECT tm."+UsersDatabase.COLUMN_MESSAGE_TEXT+" FROM "+UsersDatabase.TABLE_MESSAGES+" as tm WHERE tm."+UsersDatabase.COLUMN_USER_UID+ " = '"+userUid+"' and tm."+UsersDatabase.COLUMN_FROM +" = '"+from+"' and tm."+UsersDatabase.COLUMN_TO+" = '"+to+"'"; *"and I get error"* : (1) near "from": syntax error –  Mar 22 '14 at 19:06
  • Please make a new question for this - it's really hard to read in the comment. – Nathaniel D. Waggoner Mar 23 '14 at 19:03