Voilà j'ai coder ma petite base de données pour Android. Je voudrais savoir ce que vous en pensez et comment remplir les données car pas mal de relation. Es que j'ai bien codé ma base de données pour que les relations fonctionnent correctement fin voici un petit schemas de ma bd et le code bien sur.
Le shémas et le code de la construction de la bd.
Merci à vous
public class DataBaseStockHandler extends SQLiteOpenHelper {
/***
* Table avec prefixe etc
*
*/
/**
* TODO creer logique
*/
public static final String MULTIPLE_KEY =" Id_multiple ";
public static final String MULTIPLE_TABLE_NAME =" Multiple ";
public static final String MULTIPLE_PREFIX =" Prefix ";
public static final String MULTIPLE_POWER =" Power_of_ten ";
public static final String MULTIPLE_SYMBOL =" Symbol ";
private static final String MULTIPLE_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ MULTIPLE_TABLE_NAME + " (" +
MULTIPLE_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
MULTIPLE_PREFIX + " TEXT NOT NULL , " +
MULTIPLE_POWER + " INTEGER NOT NULL , " +
MULTIPLE_SYMBOL + " TEXT NOT NULL ) ; ";
/***
* unit table
*/
public static final String UNIT_TABLE_NAME =" Units ";
public static final String UNIT_KEY =" Id_unit ";
public static final String UNIT_NAME =" Unit_name ";
public static final String UNIT_MULTIPLE_ID =" Unit_multiple ";
public static final String UNIT_DESCRIPTION =" Unit_description ";
private static final String UNIT_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ UNIT_TABLE_NAME + " (" +
UNIT_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
UNIT_NAME + " TEXT UNIQUE, " +
UNIT_MULTIPLE_ID + " TEXT , " +
UNIT_DESCRIPTION + " TEXT ," +
" FOREIGN KEY("+ UNIT_MULTIPLE_ID +") REFERENCES "+MULTIPLE_TABLE_NAME+"("+ MULTIPLE_KEY +") ) ; ";
/***
* Market table
*/
public static final String MARKET_TABLE_NAME =" Markets ";
public static final String MARKET_KEY =" Id_market ";
public static final String MARKET_NAME = " Market_name ";
public static final String MARKET_ADDRESS =" Market_address ";
public static final String MARKET_COUNTRY = " Market_Country ";
public static final String MARKET_PHONE = " Market_Phone ";
public static final String MARKET_MAIL = " Market_E_mail ";
private static final String MARKET_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ MARKET_TABLE_NAME + " (" +
MARKET_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
MARKET_NAME + " TEXT UNIQUE, " +
MARKET_ADDRESS + " TEXT , " +
MARKET_COUNTRY + " TEXT , " +
MARKET_PHONE + " TEXT , "+
MARKET_MAIL+ " TEXT ) ; ";
/***
* Stock Table
*/
public static final String STOCK_TABLE_NAME =" Stocks ";
public static final String STOCK_KEY =" Id_stock ";
public static final String STOCK_TYPE = " Type_stock ";
public static final String STOCK_DESCRIPTION =" Description ";
public static final String STOCK_MARKET_ID = " Id_market ";
private static final String STOCK_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ STOCK_TABLE_NAME + " (" +
STOCK_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
STOCK_TYPE + " TEXT , " +
STOCK_DESCRIPTION + " TEXT , "+
STOCK_MARKET_ID+ " INTEGER ," +
" FOREIGN KEY("+ STOCK_MARKET_ID +") REFERENCES "+MARKET_TABLE_NAME+"("+ MARKET_KEY +") ) ; ";
/***
* provider Table
*/
public static final String PROVIDER_TABLE_NAME =" Providers ";
public static final String PROVIDER_KEY =" Id_provider ";
public static final String PROVIDER_NAME = " Name ";
public static final String PROVIDER_ADDRESS =" Address ";
public static final String PROVIDER_COUNTRY = " Country ";
public static final String PROVIDER_PHONE = " Phone ";
public static final String PROVIDER_MAIL = " E_mail ";
public static final String PROVIDER_ID_STOCK = " Id_stock ";
private static final String PROVIDER_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ PROVIDER_TABLE_NAME + " (" +
PROVIDER_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
PROVIDER_NAME + " TEXT UNIQUE, " +
PROVIDER_ADDRESS + " TEXT , " +
PROVIDER_COUNTRY + " TEXT , " +
PROVIDER_PHONE + " TEXT , "+
PROVIDER_MAIL+ " TEXT ," +
PROVIDER_ID_STOCK+ " INTEGER ,"+
" FOREIGN KEY("+ PROVIDER_ID_STOCK +") REFERENCES "+STOCK_TABLE_NAME+"("+ STOCK_KEY +") ) ; ";
/***
* Articles table
*/
public static final String ARTICLE_TABLE_NAME =" Articles ";
public static final String ARTICLE_KEY = " Id_articles ";
public static final String ARTICLE_NAME =" Article_name ";
public static final String ARTICLE_REFERENCE = " Article_reference ";
public static final String ARTICLE_SOLD =" Sold ";
public static final String ARTICLE_AVAILABLE =" Available ";
public static final String ARTICLE_RESERVED = " Reserved ";
public static final String ARTICLE_MINIMUM = " Minimum ";
public static final String ARTICLE_LOCALITY = " Locality ";
public static final String ARTICLE_LOCATION = " Location ";
public static final String ARTICLE_UNIT_PRICE= " Unit_price ";
public static final String ARTICLE_UNIT_ID = " Id_unit ";
public static final String ARTICLE_ID_STOCK = " Id_stock ";
public static final String ARTICLE_ID_PROVIDER = " Id_provider ";
public static final String ARTICLE_CATEGORY = " Category ";
public static final String ARTICLE_SALES_UNIT = " Sales_unit ";
public static final String ARTICLE_PURCHASED = " Purchased ";
private static final String ARTICLE_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ ARTICLE_TABLE_NAME + " (" +
ARTICLE_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
ARTICLE_NAME + " TEXT , " +
ARTICLE_REFERENCE + " TEXT UNIQUE, " +
ARTICLE_CATEGORY + " TEXT , " +
ARTICLE_SALES_UNIT + " INTEGER , " +
ARTICLE_PURCHASED + " INTEGER , "+
ARTICLE_SOLD + " INTEGER , " +
ARTICLE_AVAILABLE + " INTEGER , " +
ARTICLE_RESERVED + " INTEGER , "+
ARTICLE_MINIMUM + " INTEGER , "+
ARTICLE_LOCALITY + " TEXT , "+
ARTICLE_LOCATION + " TEXT , "+
ARTICLE_UNIT_PRICE + " REAL , "+
ARTICLE_UNIT_ID + " INTEGER , "+
ARTICLE_ID_STOCK + " INTEGER , "+
ARTICLE_ID_PROVIDER+ " INTEGER ," +
" FOREIGN KEY("+ ARTICLE_UNIT_ID +") REFERENCES "+UNIT_NAME+"("+ UNIT_KEY +")," +
" FOREIGN KEY("+ ARTICLE_ID_STOCK +") REFERENCES "+STOCK_TABLE_NAME+"("+ STOCK_KEY +") ) ; ";
/***
* Category Table
*/
public static final String CATEGORY_TABLE_NAME =" Categories ";
public static final String CATEGORY_KEY =" Id_category ";
public static final String CATEGORY_NAME = " Name ";
public static final String CATEGORY_KIND = " Kind ";
public static final String CATEGORY_DESCRIPTION =" Description ";
public static final String CATEGORY_ARTICLE_ID = " Id_article ";
public static final String CATEGORY_STOCK_ID = " Id_stock ";
private static final String CATEGORY_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ CATEGORY_TABLE_NAME + " (" +
CATEGORY_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
CATEGORY_NAME + " TEXT UNIQUE, " +
CATEGORY_KIND + " TEXT UNIQUE, " +
CATEGORY_DESCRIPTION + " TEXT , " +
CATEGORY_ARTICLE_ID + " INTEGER , " +
CATEGORY_STOCK_ID+ " INTEGER ," +
" FOREIGN KEY("+ CATEGORY_ARTICLE_ID +") REFERENCES "+ARTICLE_TABLE_NAME+"("+ ARTICLE_KEY +")," +
" FOREIGN KEY("+ CATEGORY_STOCK_ID +") REFERENCES "+STOCK_TABLE_NAME+"("+ STOCK_KEY +") ) ; ";
/**
* Client Table
*/
public static final String CLIENT_TABLE_NAME =" Clients ";
public static final String CLIENT_KEY =" Id_client ";
public static final String CLIENT_NAME = " Nom ";
public static final String CLIENT_PRENOM = " Prenom ";
public static final String CLIENT_MAIL = " E_mail ";
public static final String CLIENT_PHONE = " Phone_number ";
public static final String CLIENT_ADDRESS = " Address ";
public static final String CLIENT_NATIONALITY = " Nationality ";
private static final String CLIENT_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ CLIENT_TABLE_NAME + " (" +
CLIENT_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
CLIENT_NAME + " TEXT , " +
CLIENT_PRENOM + " TEXT , " +
CLIENT_MAIL + " TEXT , " +
CLIENT_PHONE + " TEXT , " +
CLIENT_ADDRESS + " TEXT," +
CLIENT_NATIONALITY + " TEXT," +
"UNIQUE ("+CLIENT_PRENOM+","+CLIENT_NAME+","+CLIENT_NATIONALITY+") ) ; ";
/***
* Indent table
*/
public static final String INDENT_TABLE_NAME =" Indents ";
public static final String INDENT_KEY =" Id_indent ";
public static final String INDENT_NUMBER = " Number ";
public static final String INDENT_PRICE =" Description ";
private static final String INTEND_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ INDENT_TABLE_NAME + " (" +
INDENT_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
INDENT_NUMBER + " INTEGER UNIQUE , "+
INDENT_PRICE + " REAL ) ; ";
/***
* Estimate table
*/
public static final String ESTIMATE_TABLE_NAME =" Estimates ";
public static final String ESTIMATE_KEY =" Id_estimate ";
public static final String ESTIMATE_REFERENCE=" Reference ";
public static final String ESTIMATE_CREATION_DATE = " Creation_date ";
public static final String ESTIMATE_EXPIRATION_DATE =" Expiration_date ";
public static final String ESTIMATE_PRICE = " Price ";
public static final String ESTIMATE_INTENT_ID = " Id_indent ";
public static final String ESTIMATE_CLIENT_ID = " Id_client ";
private static final String ESTIMATE_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ ESTIMATE_TABLE_NAME + " (" +
ESTIMATE_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
ESTIMATE_REFERENCE + " TEXT UNIQUE, " +
ESTIMATE_CREATION_DATE + " TEXT , " +
ESTIMATE_EXPIRATION_DATE + " TEXT , " +
ESTIMATE_PRICE + " REAL , " +
ESTIMATE_INTENT_ID+ " INTEGER ,"+
ESTIMATE_CLIENT_ID+ " INTEGER ,"+
" FOREIGN KEY("+ ESTIMATE_CLIENT_ID +") REFERENCES "+CLIENT_TABLE_NAME+"("+ CLIENT_KEY +")," +
" FOREIGN KEY("+ ESTIMATE_INTENT_ID +") REFERENCES "+INDENT_TABLE_NAME+"("+ INDENT_KEY +") ) ; ";
/**
* n to n Table article devis
*/
public static final String ARTICLE_ESTIMATE_TABLE_NAME =" Article_estimates ";
public static final String ARTICLE_ESTIMATE_KEY =" Id_article_estimate ";
public static final String ARTICLE_ESTIMATE_ARTICLE_ID = " Article_estimate_article_id ";
public static final String ARTICLE_ESTIMATE_ESTIMATE_ID =" Article_estimate_estimate_id ";
private static final String ARTICLE_ESTIMATE_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ ARTICLE_ESTIMATE_TABLE_NAME + " (" +
ARTICLE_ESTIMATE_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
ARTICLE_ESTIMATE_ARTICLE_ID + " INTEGER , " +
ARTICLE_ESTIMATE_ESTIMATE_ID+ " INTEGER , " +
" FOREIGN KEY("+ ARTICLE_ESTIMATE_ARTICLE_ID +") REFERENCES "+ARTICLE_TABLE_NAME+"("+ ARTICLE_KEY +")," +
" FOREIGN KEY("+ ARTICLE_ESTIMATE_ESTIMATE_ID +") REFERENCES "+ESTIMATE_TABLE_NAME+"("+ ESTIMATE_KEY +") ) ; ";
/**
* n to n Table fournisseur article
*/
public static final String PROVIDER_ARTICLE_TABLE_NAME =" Provider_article ";
public static final String PROVIDER_ARTICLE_KEY =" Id_provider ";
public static final String PROVIDER_ARTICLE_ARTICLE_ID = " Provider_article_article_id ";
public static final String PROVIDER_ARTICLE_PROVIDER_ID =" Provider_article_provider_id ";
private static final String PROVIDER_ARTICLE_TABLE_CREATE =
"CREATE TABLE IF NOT EXISTS "+ PROVIDER_ARTICLE_TABLE_NAME + " (" +
PROVIDER_ARTICLE_KEY + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
PROVIDER_ARTICLE_ARTICLE_ID + " INTEGER , " +
PROVIDER_ARTICLE_PROVIDER_ID+ " INTEGER , " +
" FOREIGN KEY("+ PROVIDER_ARTICLE_ARTICLE_ID +") REFERENCES "+ARTICLE_TABLE_NAME+"("+ ARTICLE_KEY +")," +
" FOREIGN KEY("+ PROVIDER_ARTICLE_PROVIDER_ID +") REFERENCES "+PROVIDER_TABLE_NAME+"("+ PROVIDER_KEY +") ) ; ";
public DataBaseStockHandler( Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("PRAGMA foreign_keys=ON;");
db.execSQL(MULTIPLE_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout MULTIPLE");
db.execSQL(MARKET_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout market");
db.execSQL(STOCK_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout stock");
db.execSQL(PROVIDER_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout provider");
db.execSQL(ARTICLE_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout article");
db.execSQL(CATEGORY_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout categorie");
db.execSQL(ESTIMATE_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout estimate");
db.execSQL(CLIENT_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout client");
db.execSQL(UNIT_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout unit");
db.execSQL(INTEND_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout indent");
db.execSQL(ARTICLE_ESTIMATE_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout dual article devis");
db.execSQL(PROVIDER_ARTICLE_TABLE_CREATE);
Log.i("DATABASESTOCk","Ajout dual fournisseur article");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
private void delete(String tableName ,String fieldName, String nameDelete , SQLiteDatabase db) {
String sql =
" DELETE " +
" FROM " +
tableName +
" WHERE " +
fieldName +" LIKE '%"+nameDelete+"%';";
db.execSQL(sql);
}
private void insert(String tableName ,String fieldName, String nameInsert , SQLiteDatabase db){
String sql = "INSERT INTO "+ tableName+" ("+ fieldName+")" +
"VALUES " +
"("+ nameInsert +");";
db.execSQL(sql);
}
}
Construit les relations au sain de la base de données?
Implémentation d'un base de données sqlite
× Après avoir cliqué sur "Répondre" vous serez invité à vous connecter pour que votre message soit publié.
× Attention, ce sujet est très ancien. Le déterrer n'est pas forcément approprié. Nous te conseillons de créer un nouveau sujet pour poser ta question.
